MySQL优化案例：半连接（semi join）优化方式导致的查询性能低下（转载）

 

以下是来自DBA+社群MySQL领域原创专家李海翔分享的MySQL优化案例，关于MySQL V5.6.x/5.7.x SQL查询性能问题。

 

一、简单创建一表，并使用存储过程插入一部分数据

 

 

二、执行如下查询

 

Q1：

 

 

Q2：Q2比Q1只多了一个使用OR子句连接的条件，数据中没有满足此条件的数据

 

 

问题: Q1和Q2哪个查询快？快者比慢者能快出几倍？为什么？

 

三、实际运行结果

 

对Q1和Q2稍加改造，目的是避免有大量的查询结果输出。目标列使用COUNT()函数替换。

 

 

看红色字体，所耗费的时间，Q1是Q2的近乎40倍。为什么？

 

四、探索原因

 

第一招：察看执行计划

 

 

对比执行计划，发现Q1使用了“MATERIALIZED”物化方式存储子查询的临时结果，是不是物化导致了Q1慢呢？

 

第二招：察看IO

 

 

Q2和Q1不一致之处在于Q2的“Handler_read_key”值20002远远比比Q1的2高，这说明Q2更多地利用了索引。

 

且看MySQL官方解释如下：

 

Handler_read_key

The number of requests to read a row based on a key. If this value is high, it is a good indication that your tables are properly indexed for your queries.

 

问题：

 

为什么Q2会有更多的索引读？索引是从哪里来的？

 

Q1被物化，意味着Q1使用了临时表；而Q2子查询是否被物化是否使用了临时表呢？

 

五、新的疑问，再次探索

 

之下如下操作，注意show warnings技巧的使用。查询结果作了形式的调整，便于阅读。

 

 

可以看出，Q1的子查询被物化后，又作了半连接优化，意味着子查询被上拉方式优化。

 

 

Q2表明，首先使用了临时表，但是和Q1不同的是，子查询没有被上拉优化。

 

但是，MySQL对于临时表的使用，会自动创建索引，所以我们能看到在“auto_key”上执行了“primary_index_lookup”。这就是Q2快于Q1的原因。也是为什么Q2的索引读计数器的值较大的原因。

 

问题：半连接优化

 

六、继续探索

 

 

执行计划似乎改变不大，但类似了Q2的执行计划。（哈哈，可执行show warnings；命令看看，获取更详细的信息才能得出更靠谱的结论）

 

 

在禁止了半连接操作之后，执行速度一下子坐上了飞机，有了40余倍的提升。

 

七、结论

 

1. Q1使用了物化+半连接优化，Q2是子查询，但没有使用半连接优化，可见MySQL中半连接优化的效率未必高。

 

2. 似乎物化的子查询用半连接上拉，MySQL的判断条件还是存在一点儿问题。