我有 3 个表，如下所示:

CREATE TABLE big_table_1 (
 id   INT(11),
 col1 TINYINT(1),
 col2 TINYINT(1),
 col3 TINYINT(1),
 PRIMARY KEY (`id`)
)

big_table_2 和 big_table_3 依此类推。 col1、col2、col3 的值为 0、1 或 null。

我正在寻找每个表中 col1 值等于 1 的 ID。我按如下方式加入它们，使用我能想到的最简单的方法:

SELECT t1.id 
FROM big_table_1 AS t1
INNER JOIN big_table_2 AS t2 ON t2.id = t1.id
INNER JOIN big_table_3 AS t3 ON t3.id = t1.id
WHERE t1.col1 = 1
AND t2.col1 = 1
AND t3.col1 = 1;

每个表有 1000 万行，查询在我的机器上执行大约需要 40 秒:

407231 rows in set (37.19 sec)

解释结果:

+----+-------------+-------+--------+---------------+---------+---------+--------------+----------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref          | rows     | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+--------------+----------+-------------+
|  1 | SIMPLE      | t3    | ALL    | PRIMARY       | NULL    | NULL    | NULL         | 10999387 | Using where |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | testDB.t3.id |        1 | Using where |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | testDB.t3.id |        1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+--------------+----------+-------------+

如果我在 col1 上声明索引，结果会稍微慢一些:

407231 rows in set (40.84 sec)

我也试过以下查询:

SELECT t1.id
FROM (SELECT distinct ta1.id FROM big_table_1 ta1 WHERE ta1.col1=1) as t1
WHERE EXISTS (SELECT ta2.id FROM big_table_2 ta2 WHERE ta2.col1=1 AND ta2.id = t1.id)
AND EXISTS (SELECT ta3.id FROM big_table_3 ta3 WHERE ta3.col1=1 AND ta3.id = t1.id);

但速度较慢:

407231 rows in set (44.01 sec) [with index on col1]
407231 rows in set (1 min 36.52 sec) [without index on col1]

上述简单方法基本上是在 MySQL 中执行此操作的最快方法吗？是否有必要将表分片到多个服务器上以便更快地获得结果？

附录:根据要求解释 Andrew 代码的结果(我将表格缩减到仅 100 万行，索引位于 id 和 col1 上):

+----+-------------+-------------+-------+---------------+---------+---------+------+---------+--------------------------------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows    | Extra                          |
+----+-------------+-------------+-------+---------------+---------+---------+------+---------+--------------------------------+
|  1 | PRIMARY     | <derived3>  | ALL   | NULL          | NULL    | NULL    | NULL |  332814 |                                |
|  1 | PRIMARY     | <derived4>  | ALL   | NULL          | NULL    | NULL    | NULL |  333237 | Using where; Using join buffer |
|  1 | PRIMARY     | <derived2>  | ALL   | NULL          | NULL    | NULL    | NULL |  333505 | Using where; Using join buffer |
|  4 | DERIVED     | big_table_3 | index | NULL          | PRIMARY | 5       | NULL | 1000932 | Using where; Using index       |
|  3 | DERIVED     | big_table_2 | index | NULL          | PRIMARY | 5       | NULL | 1000507 | Using where; Using index       |
|  2 | DERIVED     | big_table_1 | index | NULL          | PRIMARY | 5       | NULL | 1000932 | Using where; Using index       |
+----+-------------+-------------+-------+---------------+---------+---------+------+---------+--------------------------------+

最佳答案

为了好玩，尝试使用覆盖索引(id、col1 的组合) 所以 1 个索引使它成为主复合。没有其他指标。

然后运行analyze table xxx(共3次，每个表一次)

然后关闭它，希望 mysql cbo 不会过于密集来解决它。

第二个想法是查看没有 where 子句的结果。在 join on 子句中全部转换

关于mysql - 在 MySQL 中连接多个匹配特定列值的表的最快方法是什么，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/30678961/