mysql - 更高效地执行加入和更新

Question

我正在做一些数据库方面的工作，以使其更有条理。目前每条记录由 3 列的组合标识 - 因此它目前有一个由三项组成的唯一键，u1、u2 和 u3 。但是，我有另一个表将 u1、u2 和 u3 链接到另一个唯一 ID uid。具有三重唯一标识符的表具有每月值，而具有单个 ID 的表只是一个信息列。我想从信息表中取出 uid 并将其放入月度表中。所以我在月度表中创建了一个 uid 列并编写了这个查询。

解释输出如下:

UPDATE monthly_table
INNER JOIN information_table ON
monthly_table.u1 = information_table.u1 AND monthly_table.u2 = information_table.u2
AND monthly_table.u3 = information_table.u3
SET monthly_table.uid = information_table.uid;

不幸的是，我的月表中有大约 800 万行，当我运行此查询(从命令行)时，大约需要 1 小时才能完成。我已将 InnoDB 缓存设置为 3 或 4GB。我还在两个表中的键 u1、u2 和 u3 上放置了索引，并且 EXPLAIN 说会有使用这些键进行 2 次简单选择。

有没有办法让这个查询更有效率，让它更快？我不经常这样做，但我想知道我是否需要再次这样做，以便更好地理解 mySQL。

+------+-------------+-----------------------+------+---------------------------+----------+---------+-----------------------------------------------+---------+-------------+
| id   | select_type | table                 | type | possible_keys             | key      | key_len | ref                                           | rows    | Extra       |
+------+-------------+-----------------------+------+---------------------------+----------+---------+-----------------------------------------------+---------+-------------+
|    1 | SIMPLE      | monthly_table         | ALL  | u1,u2,u3                  | NULL     | NULL    | NULL                                          | 8284393 | Using where |
|    1 | SIMPLE      | information_table     | ref  | u2,u1,u3                  | u3       | 33      |dbname.monthly_table.u1                        |       2 | Using where |
+------+-------------+-----------------------+------+---------------------------+----------+---------+-----------------------------------------------+---------+-------------+

Answer 1

每个表都有三个索引，分别对应列 u1、u2 和 u3。这不会有太大帮助，因为任何给定的查询一次只能使用一个索引。

相反，对于每个表，您需要一个跨越组成主键或唯一键的所有列的索引:

CREATE UNIQUE INDEX monthly_table_my_index
ON monthly_table (u1, u2, u3);

CREATE UNIQUE INDEX information_table_my_index
ON information_table (u1, u2, u3);