• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

Mysql高级调优篇——第五章:Sql调优在面试中深度剖析

互联网 diligentman 2小时前 4次浏览

        上节讲了Sql调优实战,本章聊聊面试中Sql调优深度的剖析场景!

        在讲之前我们先做一些准备工作,建立一些需要用到的表:

Mysql高级调优篇表补充——建表SQL_风清扬逍遥子的博客-CSDN博客⭐️tbl_emp⭐️CREATE TABLE `tbl_emp` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,`deptId` int(11) DEFAULT NULL,PRIMARY KEY (`id`) ,KEY `fk_dept_id`(`deptId`))ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;⭐️tbl_dMysql高级调优篇——第五章:Sql调优在面试中深度剖析https://blog.csdn.net/qq_31821733/article/details/120886389?spm=1001.2014.3001.5501        建立了test03表后,我们继续看。Mysql高级调优篇——第五章:Sql调优在面试中深度剖析

        建立索引create index idx_test03_c1234 on test03(c1,c2,c3,c4);后

        我们创建了复合索引,根据一下sql分析索引使用情况?

        explain select * from test03 where c1 = 'a1'; 全值匹配我最爱 

Mysql高级调优篇——第五章:Sql调优在面试中深度剖析

        explain select * from test03 where c1 = 'a1' and c2='a2';

Mysql高级调优篇——第五章:Sql调优在面试中深度剖析

        explain select * from test03 where c1 = 'a1' and c2='a2' and c3='a3';

Mysql高级调优篇——第五章:Sql调优在面试中深度剖析

        explain select * from test03 where c1 = 'a1' and c2='a2' and c3='a3' and c4='a4';

Mysql高级调优篇——第五章:Sql调优在面试中深度剖析

        这四个场景,我就不多说了,越精细,开销的key_len越大。

        接着看:

        explain select * from test03 where c1 = 'a1' and c2='a2' and c4='a4' and c3='a3'; 现在是1243,很明显,Mysql优化器会帮我们把Sql自我优化分析,会把sql命令自动调整优化到最佳效果。 where后面的查询,在常量级别的范围,1+2+3 = 3+2+1,注意是常量级别的范围内!!

Mysql高级调优篇——第五章:Sql调优在面试中深度剖析

        对于explain select * from test03 where c1 = 'a1' and c2='a2' and c3>'a3' and c4='a4';

到了c3是范围查询,口诀是范围之后全失效! 1楼2楼都是确切的门牌号,可以直接到3楼,但是3楼后是个范围,Mysql并不知道3到4楼怎么查询,于是要一个一个房间找到4楼的通道,于是范围之后全失效!

Mysql高级调优篇——第五章:Sql调优在面试中深度剖析

        再看这种explain select * from test03 where c1 = 'a1' and c2='a2' and c4>'a4' and c3='a3'; 首先Mysql会帮你优化顺序1234,所以查询一定是4个都会用到。

Mysql高级调优篇——第五章:Sql调优在面试中深度剖析

        再看这个:

        explain select * from test03 where c1 = 'a1' and c2='a2' and c4='a4' order by c3; 首先,索引用到了两个列,c1和c2,严格来说c3也是用到了,别忘了索引是查找+排序,只是c3没有用到查找,用到了排序,而排序走不走索引,在执行计划中不会统计。

Mysql高级调优篇——第五章:Sql调优在面试中深度剖析

         explain select * from test03 where c1 = 'a1' and c2='a2' order by c3; 这个和上面相比,少了个c4,为啥呢?因为找了1,2后,3直接用来排序不用来查找了,和4就没啥关系了,所以有没有c4,索引列都不会变。Mysql高级调优篇——第五章:Sql调优在面试中深度剖析

         那么这个呢explain select * from test03 where c1 = 'a1' and c2='a2' order by c4; 按照c4排序,那不好意思,出现了filesort,原因是你让我按照1楼2楼我给你找到了,但是你没有到3楼,让我直接按照4楼排序,我只能按照自己内部自己进行排序,所以出现了filesort中间兄弟不能断!!

Mysql高级调优篇——第五章:Sql调优在面试中深度剖析

        再看这个:

        explain select * from test03 where c1 = 'a1' and c5='a5' order by c2, c3; 查c1,c5,按照c2和c3进行排序。c2和c3不参与查找,但是作用于排序,无filesort

Mysql高级调优篇——第五章:Sql调优在面试中深度剖析

         explain select * from test03 where c1 = 'a1' and c5='a5' order by c3, c2; 这个和上面的不同,就是在排序的时候我颠倒了位置变成c3,c2;不好意思,你不让我按照顺序排序,我只能按照我内部的排序排一次,出现filesort;(order by大部分找麻烦的,就是filesort

Mysql高级调优篇——第五章:Sql调优在面试中深度剖析

        再看这个:

        explain select * from test03 where c1 = 'a1' and c2='a2' order by c2, c3; 查c1, c2,按照c2开始排序,这个很明显;

Mysql高级调优篇——第五章:Sql调优在面试中深度剖析

         而explain select * from test03 where c1 = 'a1' and c2='a2' and c5 = 'c5' order by c2, c3; 和上面不同的是查询多了个c5,并没有什么影响,因为从c2开始就排序了,c5的索引自然走不到。

Mysql高级调优篇——第五章:Sql调优在面试中深度剖析

         explain select * from test03 where c1 = 'a1' and c2='a2' and c5 = 'c5' order by c3, c2; 这个和上面的区别是排序的时候按照c3, c2排序。这个时候就有疑问,为什么不产生filesort??难道说错了?其实并不是,仔细看下,这个地方有个c2,因为c2用于了查找,并且是常量,而刚好排序从c2开始排,Mysql并不会说找不到索引而产生内部filesort,只有不连续的时候,会产生filesort。

Mysql高级调优篇——第五章:Sql调优在面试中深度剖析

        explain select * from test03 where c1 = 'a1' and c5 = 'c5' order by c3, c2; 和上面的区别在于,这里查询没有用到c2,只用到了c1,排序的话,并没有顺序用到c2,也就是说,是断层的。这里要清楚,查询1 2 3,等于查询3 2 1,都是这些数;但是order by,Mysql不可能帮你优化从order by 1 2优化成order by 2 1,因为两个查询的结果是不一样的!

Mysql高级调优篇——第五章:Sql调优在面试中深度剖析

         explain select * from test03 where c1 = 'a1' and c4 = 'c4' group by c2, c3; 这里注意,是group by。

        对比这个explain select * from test03 where c1 = 'a1' and c4 = 'c4' group by c3, c2; 这里重点强调下,Mysql5.7之后,默认情况下GROUP BY隐式排序(即缺少GROUP BY列的ASC或DESC指示符),也就是group by默认排序,但是Mysql不推荐你采用默认的排序,应该显式的声明order by xxx,而在Mysql8之后,GROUP BY字段不再支持隐式排序。

        所以这里和order by的效果一样,且会产生临时表。

Mysql高级调优篇——第五章:Sql调优在面试中深度剖析

        总结:

        一般性建议,对于单值索引,尽量选择针对当前query过滤性更好的索引。

        在选择组合索引的时候,当前query中过滤性最好的字段在索引字段的顺序中,位置越靠前越好。

        在选择组合索引的时候,尽量选择可以能够包含当前query中的where子句中更多的字段索引

        尽可能通过分析统计信息和调整query的写法达到选择合适索引的目的。

        面试的坑点以及实战案例我在这里就差不多告一段落了,后面我们讲一些Mysql的其他知识点!!


喜欢 (0)