联系我们

Email:wanzhanqun@163.com

电话:155-7782-2416

QQ:663486249

地址:北京市朝阳区广渠路38号院411号楼

SQL优化实战|8个Case索引优化案例分享【附独家口诀】

  • 最新
  • 精选
  • 区块链
  • 汽车
  • 创意科技
  • 媒体达人
  • 电影音乐
  • 娱乐休闲
  • 生活旅行
  • 学习工具
  • 历史读书
  • 金融理财
  • 美食菜谱

SQL优化实战 | 8个Case索引优化案例分享【附独家口诀】

中国统计网 中国统计网 2020-05-26

- 点击上方“中国统计网”订阅我吧!-


 文末领取【62个SQL练习题




优化口诀




全值匹配我最爱,最左前缀要遵守,

带头大哥不能死,中间兄弟不能断。

索引列上少计算,范围之后全失效,

LIKE百分写最右,覆盖索引不写星,

不等空值还有or,索引失效要少用。


具体含义见下文实战。





准备





1.创建test表


drop table if exists test;create table test(id int primary key auto_increment,c1 varchar(10),c2 varchar(10),c3 varchar(10),c4 varchar(10),c5 varchar(10)) ENGINE=INNODB default CHARSET=utf8;
insert into test(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');insert into test(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');insert into test(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');insert into test(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');insert into test(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');


2.创建索引



此时索引数据结构如下:





分析Case索引使用情况




Case 1:



分析:


  • 创建复合索引的顺序为c1,c2,c3,c4。

  • 上述四组explain执行的结果都一样:type=ref,key_len=132,ref=const,const,const,const。


结论:在执行常量等值查询时,改变索引列的顺序并不会更改explain的执行结果,因为mysql底层优化器会进行优化,但是推荐按照索引顺序列编写sql语句。


Case 2:




分析:当出现范围的时候,type=range,key_len=99,比不用范围key_len=66增加了,说明使用上了索引,但对比Case1中执行结果,说明c4上索引失效。


结论:范围右边索引列失效,但是范围当前位置(c3)的索引是有效的,从key_len=99可证明。


数据结构分析:当索引走了 >c3后,此时在索引树形结构中c4已经无序了,没有办法走索引。


Case 2.1:



分析:与上面explain执行结果对比,key_len=132说明索引用到了4个,因为对此sql语句mysql底层优化器会进行优化:范围右边索引列失效(c4右边已经没有索引列了),注意索引的顺序(c1,c2,c3,c4),所以c4右边不会出现失效的索引列,因此4个索引全部用上。


结论:范围右边索引列失效,是有顺序的:c1,c2,c3,c4,如果c3有范围,则c4失效,因为c3范围查询之后,c4索引数将不是有序,c4当然失效;如果c4有范围,则没有失效的索引列,从而会使用全部索引,c4范围查询后,不影响索引数的顺序。


Case 2.2:



分析:如果在c1处使用范围,则type=ALL,key=Null,索引失效,全表扫描,这里违背了最佳左前缀法则,带头大哥已死,因为c1主要用于范围,而不是查询。


解决方式是使用覆盖索引。



结论:在最佳左前缀法则中,如果最左前列(带头大哥)的索引失效,则后面的索引都失效。


Case 3:



分析:


利用最佳左前缀法则:中间兄弟不能断,因此用到了c1和c2索引(查找),从key_len=66,ref=const,const,c3索引列用在排序过程中。


Case 3.1:



分析:从explain的执行结果来看:key_len=66,ref=const,const,从而查找只用到c1和c2索引,c3索引用于排序。


Case 3.2:



分析:从explain的执行结果来看:key_len=66,ref=const,const,查询使用了c1和c2索引,由于用了c4进行排序,跳过了c3,出现了Using filesort。


Case 4:



分析:查找只用到索引c1,c2和c3用于排序,无Using filesort。


Case 4.1:



分析:和Case 4中explain的执行结果一样,但是出现了Using filesort,因为索引的创建顺序为c1,c2,c3,c4,但是排序的时候c2和c3颠倒位置了。


Case 4.2:




分析:在查询时增加了c5,但是explain的执行结果一样,因为c5并未创建索引。


Case 4.3:



分析:与Case 4.1对比,在Extra中并未出现Using filesort,因为c2为常量,在排序中被优化,所以索引未颠倒,不会出现Using filesort。


Case 5:



分析:只用到c1上的索引,因为c4中间间断了,根据最佳左前缀法则,所以key_len=33,ref=const,表示只用到一个索引。


Case 5.1:



分析:对比Case 5,在group by时交换了c2和c3的位置,结果出现Using temporary和Using filesort,极度恶劣。原因:c3和c2与索引创建顺序相反。


Case 6:



分析:


  • 在c1,c2,c3,c4上创建了索引,直接在c1上使用范围,导致了索引失效,全表扫描:type=ALL,ref=Null。因为此时c1主要用于排序,并不是查询。

  • 使用c1进行排序,出现了Using filesort。

  • 解决方法:使用覆盖索引。


Case 7:



分析:虽然排序的字段列与索引顺序一样,且order by默认升序,这里c2 desc变成了降序,导致与索引的排序方式不同,从而产生Using filesort。


Case 8:



分析:对于排序来说,多个相等条件也是范围查询。




总结




  • MySQL支持两种方式的排序filesort和index,Using index是指MySQL扫描索引本身完成排序。index效率高,filesort效率低。

  • order by满足两种情况会使用Using index。

  • order by语句使用索引最左前列。

  • 使用where子句与order by子句条件列组合满足索引最左前列。

  • 量在索引列上完成排序,遵循索引建立(索引创建的顺序)时的最佳左前缀法则。

  • 如果order by的条件不在索引列上,就会产生Using filesort。

  • group by与order by很类似,其实质是先排序后分组,遵照索引创建顺序的最佳左前缀法则。注意where高于having,能写在where中的限定条件就不要去having限定了。


End.

作者:董二弯

来源:简书

本文为转载分享,如侵权请联系后台删除


扫码添加职场老师微信领取【62个SQL练习题

还可以获得职场老师生涯规划建议哦!


  零基础·入职数据分析就业班


就业班学员专享:
趣味月考测试、数据库模拟练习
1v1职场生涯规划班主任监督辅导
专业讲师团队答疑

加入我们你将拥有:
1. 熟练掌握SQL/Python/Excel/PPT等数据分析工具的能力
2. 真实数据库的实战项目经历
3. 数据职场讲师1v1生涯规划

5月班正在火热报名中!快来扫码咨询吧!


点击“阅读原文”,入职数据分析

    阅读原文

    前往看一看

    看一看入口已关闭

    在“设置”-“通用”-“发现页管理”打开“看一看”入口

    我知道了

    已发送

    发送到看一看

    发送中

    微信扫一扫
    使用小程序

    取消 允许

    取消 允许

    微信版本过低

    当前微信版本不支持该功能,请升级至最新版本。

    我知道了 前往更新

    确定删除回复吗?

    取消 删除

      知道了

      长按识别前往小程序

      本站仅按申请收录文章,版权归原作者所有
      如若侵权,请联系本站删除

      微信QQ空间新浪微博腾讯微博人人Twitter豆瓣百度贴吧

      觉得不错,分享给更多人看到

      中国统计网 微信二维码

      中国统计网 微信二维码

      中国统计网 最新文章

      SQL优化实战 | 8个Case索引优化案例分享【附独家口诀】  2020-05-26

      996变成了互联网人的宿命,流量才是“秃头”的始作俑者 !  2020-05-26

      不限专业也能入行互联网!流量运营真的不难!  2020-05-25

      SPSS聚类分析对比:2种聚类方法哪个更好用?  2020-05-25

      【内推107】数据分析师+字节跳动+北京  2020-05-25

      数据分析师最容易跳进去的5个大坑!原来就在我们身边!  2020-05-24

      统计学最重要的定理之一:中心极限定理  2020-05-24

      Python实战案例:我们对共享单车的需求有多大?  2020-05-23

      互联网巨头的用户增长都用了什么“套路”?!【推荐收藏】  2020-05-23

      数据分析报告正确使用指南:别让你手里的报告浪费了!  2020-05-22

      (function(){ var bp = document.createElement('script'); var curProtocol = window.location.protocol.split(':')[0]; if (curProtocol === 'https') { bp.src = 'https://zz.bdstatic.com/linksubmit/push.js'; } else { bp.src = 'http://push.zhanzhang.baidu.com/push.js'; } var s = document.getElementsByTagName("script")[0]; s.parentNode.insertBefore(bp, s); })(); (function(){ var src = (document.location.protocol == "http:") ? "http://js.passport.qihucdn.com/11.0.1.js?ba34c9f41d18b62312e960833b3cb4ae":"https://jspassport.ssl.qhimg.com/11.0.1.js?ba34c9f41d18b62312e960833b3cb4ae"; document.write(''); })();

      热门地区

      文昌 晋江 荆州 金坛 敦化 五指山 金华 朝阳 铁力 莱西 凌源 彭州 天门 五家渠 北票 安宁 厦门 鄂尔多斯 韶关 峨眉山 恩平 宁波 太仓 兴平 乐陵 潮州 兴城 辽阳 中卫 宣威 阳泉 临海 建德 四会