为什么我的SELECT使用子查询和连接如此慢？

Question

此查询需要10秒钟才能完成。但是，当我手动执行子查询并更改对该列表的t1.id限制时，只需0.00秒。我该怎么做才能让MySQL更快地执行查询？

SELECT t1.col1, t2.col2, t3.col3
FROM t1, t2, t3
WHERE  t1.t2id = t2.id AND t1.t3id = t3.id
AND t1.id IN ( SELECT id FROM t4 WHERE blah = 123 )

另外，为什么会发生这种情况？我认为MySQL在过滤t1.id之前以某种方式连接了所有这三个表。

t1、t2和t3分别包含3000、15和80行。子查询返回2-10行。

Answer 1

重写不带子查询的查询：

SELECT t1.col1, t2.col2, t3.col3
FROM t1, t2, t3, (SELECT id FROM t4 WHERE blah = 123) AS t4
WHERE  t1.t2id = t2.id AND t1.t3id = t3.id
AND t1.id=t4.id

确保在WHERE子句中使用的字段上有索引。

Answer 2

尝试使用"INNER JOIN“而不是"IN”函数。这样，您的sql指令将更加高效。

SELECT t1.col1, t2.col2, t3.col3
FROM ((t1 INNER JOIN t2 ON t1.t2id = t2.id) INNER JOIN t3 ON t1.t3id = t3.id) INNER JOIN t4 ON t1.id = t4.id
WHERE t4.blah = 123

Answer 3

如果在语句上运行EXPLAIN，您可能会看到MySql已经在磁盘上创建了一个临时表:如果内联select ( IN术语)中的数据足够大，则通常会发生这种情况。

简而言之：

a)使用EXPLAIN查看数据库中发生的情况(并查看此行为如何随着数据的增加而变化)

b)尽可能避免内联子查询

c)请记住，MySql只有嵌套循环连接算法可供使用(其他DB也使用散列连接和合并连接算法)，因此对于较小的数据集，您可能会看到“较少”的解析，但当您达到临界点时，解析会突然减少。