where子句在MySQL中性能差异的原因

Question

• 这三个查询之间存在巨大的执行时间间隔。我不知道MySQL查询引擎的底层工作。

Query1:

select t2.* from (
    select id1, id2 
    from table t2
    where condition
) t3, table t2
where 
t2.id1 = t3.id1 AND
t2.id2 = t3.id2

• 要180秒才能跑。

Query2:

select t2.* from (
    select id1, id2 
    from table t2
    where condition
) t3 inner join table t2
on 
t2.id1 = t3.id1 AND
t2.id2 = t3.id2

• 要180秒才能跑。
• 解释结果。

id|select_type|table     |partitions|type|possible_keys|key        |key_len|ref                                                                                     |rows |filtered|Extra                                             |
--|-----------|----------|----------|----|-------------|-----------|-------|----------------------------------------------------------------------------------------|-----|--------|--------------------------------------------------|
 1|PRIMARY    |t2        |          |ALL |             |           |       |                                                                                        |95619|      19|Using where                                       |
 1|PRIMARY    |t2        |          |ALL |             |           |       |                                                                                        |95619|       1|Using where; Using join buffer (Block Nested Loop)|
 1|PRIMARY    |<derived3>|          |ref |<auto_key0>  |<auto_key0>|216    |t2.id1,t2.id2                                                                           |   10|     100|Using index                                       |
 3|DERIVED    |t3        |          |ALL |             |           |       |                                                                                        |95619|     100|Using temporary; Using filesort                   |

Query3:

select t2.* from (
    select id1, id2 
    from table t2
    where condition
) t3 left join table t2
on 
t2.id1 = t3.id1 AND
t2.id2 = t3.id2

• 运行时间为1.6秒。
• 解释结果。

id|select_type|table     |partitions|type|possible_keys|key        |key_len|ref                                                                                     |rows |filtered|Extra                                             |
--|-----------|----------|----------|----|-------------|-----------|-------|----------------------------------------------------------------------------------------|-----|--------|--------------------------------------------------|
 1|PRIMARY    |t2        |          |ALL |             |           |       |                                                                                        |95619|      19|Using where                                       |
 1|PRIMARY    |<derived3>|          |ref |<auto_key0>  |<auto_key0>|216    |t2.id1,t2.id2                                                                           |   10|     100|Using index                                       |
 1|PRIMARY    |t2        |          |ALL |             |           |       |                                                                                        |95619|     100|Using where; Using join buffer (Block Nested Loop)|
 3|DERIVED    |t3        |          |ALL |             |           |       |                                                                                        |95619|     100|Using temporary; Using filesort                   |

更新：--我已经用附加的查询编辑了这个问题。

UPDATE2为查询添加了Explain结果。编辑：关于表的一些统计数据。

子查询包含83 rows。Table t2有~97k rows.

Answer 1

我认为发生了这样的事情：

前两个查询是完全等价的--当与两个表相关的条件相同时，MySQL并不区分INNER JOIN和交叉积；不管条件是ON还是WHERE。因此，与之相关的区别就在于内部连接和外部连接。

在执行任何类型的连接时，MySQL必须决定哪个表是主表，哪个是依赖表。它将扫描主表，然后在相依表中找到匹配的行，以生成结果集。

外部联接强制对此排序；在LEFT JOIN中，左表始终是主表，因为结果必须为该表中的每一行至少包含一行。因此，在为t3生成中间表之后，它只需扫描这83行，并在t2中找到与连接条件相匹配的相应行。所匹配的列可能是索引的，所以这是非常快的。

但是有了一个内在的连接，它可以走任何一条路。查询优化器将尝试估计哪个表较小，并将其用作其扫描的主表。但是，当它是子查询的结果时，它不知道它将返回多少行。因此，它显然选择使用t2作为主表，而不是使用中间t3表。这意味着它正在扫描97K行，测试每个行以在t3中找到匹配的行。

智能优化器会注意到子查询只是对同一个表进行过滤，因此必须返回较少的行。很少有人会说MySQL有更好的查询计划者之一。我怀疑它选择使用正则表而不是中间表，因为它可以将扫描限制在索引上。

令人惊讶的是，它只需要180秒，而不是1800秒(30分钟)，接近97k和83之间的比例。

我并不是解释EXPLAIN结果的专家，但我认为您可以在第2行看到不同之处；在快速情况下是table = <derived3>, rows = 10，在慢情况下是table = t2, rows = 95619。