MySQL优化案例:半连接(semi join)优化方式导致的查询性能低下

李海翔 2015-12-07 11:44:52

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

 

专家简介

 
 


李海翔

网名:那海蓝蓝

DBA+社群MySQL领域原创专家


从事数据库研发、数据库测试与技术管理等工作10余年,对数据库的内核有深入研究,擅长于PostgreSQL和MySQL等开源数据库的内核与架构。现任职于Oracle公司MySQL全球开发团队,从事查询优化技术的研究和MySQL查询优化器的开发工作。著有《数据库查询优化器的艺术》一书。

 

 

 

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



二、执行如下查询


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的判断条件还是存在一点儿问题。


本文由作者李海翔授权DBA+社群发布,选自作者网易博客

即日起,凡是推送在【DBAplus社群】平台的文章,阅读量超过1000,该文章作者可获得赠书一本。大家如有好的干货文章也可以向我们的订阅号投稿,投稿邮箱:1017465571@qq.com。近期赠书有:白鳝《思想的天空》、杨志洪《Oracle核心技术》……

 

小编精心为大家挑选了近日最受欢迎的几篇热文:

回复001,看丁俊的《【重磅干货】看了此文,Oracle SQL优化文章不必再看!》;

回复002,看《灾备故障上了红头文件,容灾技术到底哪家强?》;

回复003,看吕海波的《去不去O,谁说了算?》;

回复004,看胡怡文《PG,一道横跨oltp到olap的梦想之桥》;

回复005,看付新《达梦专家解读:国产数据库也疯狂》;

回复006,看郭耀龙《假事务之名,深入研究UNDO与REDO》;

回复007,看宋日杰《Oracle后台专家解决library cache锁争用的终极武器》;

回复008,看周俊《被埋没的SQL优化利器——Oracle SQL monitor》;

回复009,看楼方鑫《数据库中间层,这样定制可能更好》;

回复010,看朱贤文《数据库与储存系统》;

回复011,看袁伟翔《揭秘Oracle数据库truncate原理》 。

 

活动预告