目录
一. 索引使用注意点,与索引失效
什么情况下会出现索引失效
- 最左前缀法则: 联合索引时,执行sql中第一个开头的不能丢失,丢失索引失效(假如创建abc三个字段的联合索引,可以理解为分别创建了"a",“ab”,"abc"三个索引),顺序可以不同,但是要存在最左
- 索引列上存在计算,函数计算,类型转换等,不管手动还是自动,都会造成索引失效
- 索引字段使用不等于(!=或< >)时会造成索引失效
- 字符串不加单引号(原因是触发了自动的类型转换)
- 索引字段中包含null值: MySQL中对于对于索引值为null时会进行特殊处理,索引中null认为相等,当发现为null的数据过多时会执行全表扫描
- 使用or条件时,必须or条件中的每个列都加上索引,否则无效
- like是以%开头的,则该列上的索引不会被使用
解决like查询以"%"开头不使用索引问题: 使用覆盖索引
-
对索引列同时使用了ASC和DESC
-
通过where语句将order by中索引列转为常量
-
where与order by使用了不同的索引
-
order by中加入了非索引列,且非索引列不在where中
-
order by或者它与where组合没有满足索引最左前列
-
当使用left join,使用右边的表字段排序
-
注意重点一:如果查询sql中存在order by, group by,order by与group by的列尽量建立索引或与联合索引的列的个数,顺序,保持一致,否则可能出现文件内排序,与临时表问题,具体查看EXPLAIN 中Extra(原因,我们创建了索引,索引会对数据进行排序,而实际查询时,使用索引条件不满足,mysql内部自己进行了排序,优化造成)
-
注意重点二:多个索引情况下如果中间带范围查询,范围查询后的索引是无效的,原因: 假设where中的多个条件都存在索引,MySql执行时按照BTree树工作原理,首先会拿where后的第一个索引条件进行排序,比对,当找到与第一个相同的时候,会拿第二个进行排序比对以此类推,但是当遇到范围查询的索引列时MySql就无法再利用索引对范围查询后面的索引列进行检索,索引范围查询后的索引无效
-
观察下图:对tese03表中的index_c1_c2_c3_c4四个字段添加联合索引,在sql1中由于where的条件存在范围查询"c3>‘a3’ ", c3字段又是索引的中间部分,在实际使用时只能命中3个索引, 再看图2,虽然where中的范围查询"c3>‘a4’"在"c3=‘a3’ "前面,但是MySql在执行时会进行排序优化,最终四个索引都会命中
-
注意点三:对索引字段is null 与 is not null是否走索引问题: 当表中对应该字段的null值占多数时is not null 和!=走索引 ,is null不走索引了,当null值占少数时刚好相反 is null 走索引,is not null与!=不走索引,这关系到一个成本问题: 索引(二级索引)扫描成本:
- 读取索引记录成本
- 反查主键索引查找完整数据成本即回表
- 如果查询读取的二级索引越多那么需要回表查询的次数就会越多,达到一定的比例就会变成全部查询了,也就是上面null 查询时索引有时不生效的原因。综上MySQL中决定使不使用某个索引执行查询的依据是成本大小。而不是在WHERE子句中用了IS NULL、IS NOT NULL、!=这些条件
小总结
MySQL中的Null
- MySQL中对Null处理比较分裂, 有三种情况
- 认为是没有意义的空,例如count(字段)统计一个存在null值的字段时,是不统计null的
- 认为null表示没有,例如通过一个存在null值的字段构建索引时,所有为null的算一份
- 认为是一个不确定的值,每个null都是独一份的,任何值与null做比对都为false包括null==null, null!=null两种判断
二. 表连接优化案例
两表连接优化案例
- class 表
- book 表
案例1: 输出所有表中所有数据与book表中book.card=class.card的数据
#左连接
select * from class left join book on class.card=bookcard;
-
查看执行计划:type为ALL走了全表扫描,key为NULL没有用到索引
-
优化1:“class left join book” 左连接时对连接条件"class.card=book.card"添加索引,将索引添加到左表"class"时查看执行计划发现:table列中对应class表的,type达到了index级别,kye显示使用到了索引,但是观察rows列,总共读取了40行
-
优化2:“class left join book” 左连接时对连接条件"class.card=book.card"添加索引,将索引添加到右表"book"时查看执行计划发现:table列中book右表相关的 type达ref级别,key中显示用到了索引,rows显示读取的行数为21
-
总结:索引条件左连接加右表,右连接加左表,原因:左连接输出的是左表全部数据,就算左表加上索引,MySql也要扫描全部行,同理右连接是输出右表全部数据,就算右表加上索引,MySql也要扫描全部行
多表连接优化案例
- phone 表3(加上两表优化案例里的class表与book表)
- 执行sql
select * from class left join book on class.card=book.card left join phone on book.card=phone.card
- 查看执行计划:type都显示为ALL说明全走了全表扫描,key都显示NULL说明没用到索引
- 根据前面两表连接优化总结: 左连接索引添加右表,右连接索引添加左表,对上面执行的sql进行优化,对两张右边添加索引
- 再次查看执行计划:type中显示有两个执行达到了ref级别, key列显示用掉了索引,rows显示一共扫描了22行数据
表连接优化总结
- 左连接索引添加右表,右连接索引添加左表
- 尽量减少join语句中的NestedLoop循环总次数,意思是说尽量减少多表的join
- 永远用小结果集驱动大结果集
- 优先优化NestedLoop的内层循环
- 保证join语句中被驱动的表上join条件添加索引: 是不是用根据上面添加索引优化案例,左连接时输出左表全部数据,右连接时输出右表所有数据,总要有一个输出全部数据,加索引后可以减少一个表的全部扫描,大表作为减少的这个表
- 在无法保证被驱动表的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;
- 查看执行计划发现: type为ALL说明走了全表扫描, key为NULL说明没使用到索引,Extra中出现Using filesort说明用到了临时表存放中间结果
- 优化分析,where条件中使用了 category_id与comments两个字段,order by排序中使用了views字段,按照顺序建立联合索引
- 再次查看执行计划:type达到了range级别解决了全表扫描问题, key中显示使用了联合索引,但是Extra中出现Using filesort还存在使用临时表存放中间结果的操作,原因: 虽然创建的联合索引顺序与where+order by中的条件顺序保持一致,由于"commets>1"是范围查询,索引直到范围查询,简单来说就是多个索引,都满足的情况下,只要中间有范围查询,范围查询后的索引是无效的,所以"order by views"并没有用到索引
- 再次优化: 删除前面创建的索引重新建立满足需求的category_id+views的联合索引,只使用where后的"category_id=1"等值查询与"order by views"排序字段,去除了前面中间的comments范围查询字段,这样就可以防止范围查询后的索引列失效,查看执行计划:type为ref非全表扫描,key中显示用到了索引,Extra中也没了中间表的问题