六. MySQL 索引失效与索引优化案例

一. 索引使用注意点,与索引失效

什么情况下会出现索引失效

1. 最左前缀法则: 联合索引时,执行sql中第一个开头的不能丢失,丢失索引失效(假如创建abc三个字段的联合索引,可以理解为分别创建了"a",“ab”,"abc"三个索引),顺序可以不同,但是要存在最左
2. 索引列上存在计算,函数计算,类型转换等,不管手动还是自动,都会造成索引失效
3. 索引字段使用不等于(!=或< >)时会造成索引失效
4. 字符串不加单引号(原因是触发了自动的类型转换)
5. 索引字段中包含null值: MySQL中对于对于索引值为null时会进行特殊处理,索引中null认为相等,当发现为null的数据过多时会执行全表扫描
6. 使用or条件时,必须or条件中的每个列都加上索引，否则无效
7. like是以%开头的，则该列上的索引不会被使用

解决like查询以"%"开头不使用索引问题: 使用覆盖索引

8. 对索引列同时使用了ASC和DESC

9. 通过where语句将order by中索引列转为常量

10. where与order by使用了不同的索引

11. order by中加入了非索引列,且非索引列不在where中

12. order by或者它与where组合没有满足索引最左前列

13. 当使用left join，使用右边的表字段排序

14. 注意重点一:如果查询sql中存在order by, group by,order by与group by的列尽量建立索引或与联合索引的列的个数,顺序,保持一致,否则可能出现文件内排序,与临时表问题,具体查看EXPLAIN 中Extra(原因,我们创建了索引,索引会对数据进行排序,而实际查询时,使用索引条件不满足,mysql内部自己进行了排序,优化造成)

15. 注意重点二:多个索引情况下如果中间带范围查询,范围查询后的索引是无效的,原因: 假设where中的多个条件都存在索引,MySql执行时按照BTree树工作原理,首先会拿where后的第一个索引条件进行排序,比对,当找到与第一个相同的时候,会拿第二个进行排序比对以此类推,但是当遇到范围查询的索引列时MySql就无法再利用索引对范围查询后面的索引列进行检索,索引范围查询后的索引无效

16. 观察下图:对tese03表中的index_c1_c2_c3_c4四个字段添加联合索引,在sql1中由于where的条件存在范围查询"c3>‘a3’ ", c3字段又是索引的中间部分,在实际使用时只能命中3个索引, 再看图2,虽然where中的范围查询"c3>‘a4’"在"c3=‘a3’ "前面,但是MySql在执行时会进行排序优化,最终四个索引都会命中
    

17. 注意点三:对索引字段is null 与 is not null是否走索引问题: 当表中对应该字段的null值占多数时is not null 和!=走索引 ,is null不走索引了，当null值占少数时刚好相反 is null 走索引,is not null与!=不走索引,这关系到一个成本问题: 索引(二级索引)扫描成本:

1. 读取索引记录成本
2. 反查主键索引查找完整数据成本即回表
3. 如果查询读取的二级索引越多那么需要回表查询的次数就会越多，达到一定的比例就会变成全部查询了，也就是上面null 查询时索引有时不生效的原因。综上MySQL中决定使不使用某个索引执行查询的依据是成本大小。而不是在WHERE子句中用了IS NULL、IS NOT NULL、!=这些条件

小总结

MySQL中的Null

1. MySQL中对Null处理比较分裂, 有三种情况

1. 认为是没有意义的空,例如count(字段)统计一个存在null值的字段时,是不统计null的
2. 认为null表示没有,例如通过一个存在null值的字段构建索引时,所有为null的算一份
3. 认为是一个不确定的值,每个null都是独一份的,任何值与null做比对都为false包括null==null, null!=null两种判断

二. 表连接优化案例

两表连接优化案例

1. class 表
   
2. book 表
   

案例1: 输出所有表中所有数据与book表中book.card=class.card的数据

#左连接
select * from class left join book on class.card=bookcard;

1. 查看执行计划:type为ALL走了全表扫描,key为NULL没有用到索引
   

2. 优化1:“class left join book” 左连接时对连接条件"class.card=book.card"添加索引,将索引添加到左表"class"时查看执行计划发现:table列中对应class表的,type达到了index级别,kye显示使用到了索引,但是观察rows列,总共读取了40行
   

3. 优化2:“class left join book” 左连接时对连接条件"class.card=book.card"添加索引,将索引添加到右表"book"时查看执行计划发现:table列中book右表相关的 type达ref级别,key中显示用到了索引,rows显示读取的行数为21
   

4. 总结:索引条件左连接加右表,右连接加左表,原因:左连接输出的是左表全部数据,就算左表加上索引,MySql也要扫描全部行,同理右连接是输出右表全部数据,就算右表加上索引,MySql也要扫描全部行

多表连接优化案例

1. phone 表3(加上两表优化案例里的class表与book表)
   
2. 执行sql

select * from class left join book on class.card=book.card left join phone on book.card=phone.card

3. 查看执行计划:type都显示为ALL说明全走了全表扫描,key都显示NULL说明没用到索引
   
4. 根据前面两表连接优化总结: 左连接索引添加右表,右连接索引添加左表,对上面执行的sql进行优化,对两张右边添加索引
   
5. 再次查看执行计划:type中显示有两个执行达到了ref级别, key列显示用掉了索引,rows显示一共扫描了22行数据
   

表连接优化总结

1. 左连接索引添加右表,右连接索引添加左表
2. 尽量减少join语句中的NestedLoop循环总次数,意思是说尽量减少多表的join
3. 永远用小结果集驱动大结果集
4. 优先优化NestedLoop的内层循环
5. 保证join语句中被驱动的表上join条件添加索引: 是不是用根据上面添加索引优化案例,左连接时输出左表全部数据,右连接时输出右表所有数据,总要有一个输出全部数据,加索引后可以减少一个表的全部扫描,大表作为减少的这个表
6. 在无法保证被驱动表的join条件添加索引的情况下,不要吝啬JoinBuffer的设置(也就是在在配置中加大缓存缓冲)

三. 索引优化案例一

表数据

案例1:查询category_id为1,且comments大于1,且views最大的数据的author_id值

select author_id from article where category_id = 1 and comments>1 order by views desc limit 1;

1. 查看执行计划发现: type为ALL说明走了全表扫描, key为NULL说明没使用到索引,Extra中出现Using filesort说明用到了临时表存放中间结果
   
2. 优化分析,where条件中使用了 category_id与comments两个字段,order by排序中使用了views字段,按照顺序建立联合索引
   
3. 再次查看执行计划:type达到了range级别解决了全表扫描问题, key中显示使用了联合索引,但是Extra中出现Using filesort还存在使用临时表存放中间结果的操作,原因: 虽然创建的联合索引顺序与where+order by中的条件顺序保持一致,由于"commets>1"是范围查询,索引直到范围查询,简单来说就是多个索引,都满足的情况下,只要中间有范围查询,范围查询后的索引是无效的,所以"order by views"并没有用到索引
   
4. 再次优化: 删除前面创建的索引重新建立满足需求的category_id+views的联合索引,只使用where后的"category_id=1"等值查询与"order by views"排序字段,去除了前面中间的comments范围查询字段,这样就可以防止范围查询后的索引列失效,查看执行计划:type为ref非全表扫描,key中显示用到了索引,Extra中也没了中间表的问题