以下是来自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的判断条件还是存在一点儿问题。 即日起，凡是推送在【DBAplus社群】平台的文章，阅读量超过1000，该文章作者可获得赠书一本。大家如有好的干货文章也可以向我们的订阅号投稿，投稿邮箱：[email protected]。近期赠书有：白鳝《思想的天空》、杨志洪《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原理》 。 DBA 社群是全中国最大的涵盖各种数据库、中间件及架构师线条的微信社群！有100 专家发起人，建有15大城市微信群，6大专业产品群，多达10000 跨界DBA加入队伍。每天1个热议话题，每周2次线上技术分享，不定期线下聚会与原创专家团干货分享，更多精彩，欢迎关注dbaplus微信订阅号！ 扫码关注 DBAplus社群 超越DBA圈子，连接的不仅仅是DBA 本文转载自：微信公众账号 - DBAplus社群，版权归原作者所有！ |
|
声明：文章版权归原作者所有 部分文章转自互联网 如有侵权请联系
[邮箱地址] 删除
|
注册
登录
