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

50133c11d45f127bf313640ba1c6a596.png

二、执行如下查询

Q1:

b3bfd5fcb23bf79b3cce861aa94ac8cf.png

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

8c60a47b80847d8e3204f5f4b3c88f78.png

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

三、实际运行结果

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

449e635c5b22ed004cd3ab67be1b39c9.png

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

四、探索原因

第一招:察看执行计划

7cec59701e9693db994b9aea29dbcedf.png

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

第二招:察看IO

706a81a0e878c3bc736d50fd40a4c503.png

a19927cf552023abcc5093f13757dd20.png

071de36d7b16775ee751105a5b77ed85.png

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技巧的使用。查询结果作了形式的调整,便于阅读。

1456ce4d7a25a06397d1ae23a6b1b934.png

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

1666141be6f6ac62d0726b6c5cf00148.png

2d39388fca56d5a44ee6f60ddc70a8eb.png

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

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

问题:半连接优化

六、继续探索

41bd2a8b22a6a358f4b3294a1b056bac.png

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

8e84c450d307cd1a72e10e7368499aed.png

在禁止了半连接操作之后,执行速度一下子坐上了飞机,有了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原理》 。

8570589aa79a463b5a85fe9b72fb973a.png

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值