MySQL索引优化及索引失效情况分析

建立包含字段name,age,pos的复合索引

最佳左前缀法则

如果索引了多列,就要遵循最左前缀法则,指的是查询从索引的最左前列开始,并且不能跳过中间列 (必须完全按照索引建立顺序对索引进行访问,否则无法使用到索引)
​

如果跳过头部,则会导致索引完全失效,而如果跳过中间部分索引,则会导致索引部分失效(只用到前面有序的索引,而无法使用到后面的完整索引)
最左前缀法则产生的原因是,MySQL在建立联合索引时会按照顺序依次排序字段
​

这里先按照name排序,再按照age排序
若直接使用age而不使用name会导致内部无法找到有序的age段,必须先通过有序的name段才能定位到有序的age段,依次向右使用索引,依次定位.
​

​

只使用头部部情况(部分失效)

不带索引头部情况(完全失效)

中间间断情况(部分失效)

全值匹配情况(未失效)

mysql的SQL优化器,SQL中使用索引不按照顺序(但全部使用,并且索引没有前后依赖),可以重排序到索引相同顺序

不在索引列上做任何操作

在索引列上做操作(计算,函数,(自动/手动)类型转换等)都会导致索引失效进而转换为全表扫描

存储引擎不能使用索引中范围条件右边的列 (范围之后全失效)

此处与数据结构有关
在复合索引中第二个索引使用了范围条件(age>25),相当于age右边的索引(name索引)用于定位了一段具体的列
而age索引只定位了一个范围,导致的age索引右边的pos索引失效(同时此时的type变为了reang)
(like也属于范围查询)

尽量使用覆盖索引 (索引列与查询列一致)

索引列与查询列一致可以导致查询不需要回表,因为覆盖索引是直接从索引中取值,提高性能

在使用了覆盖索引后再使用范围条件,虽然也会导致右边失效,但是type将会变为ref 并且能够用到左边索引的值来进行匹配,同时还会降低索引使用的字节数

其他索引失效情况

mysql在使用不等于(!=)匹配时会导致索引失效,全表扫描

is null ,is not null 也会导致索引失效

like通配符开头(%abc)会导致索引失效

%abc会导致索引失效,而abc%不会
​

Q:解决like’%abc%'时索引不被使用的方法
使用覆盖索引能解决索引不被使用
​

​

字符串不加单引号会导致索引失效

原因:不加单引号的字符串会参加自动类型转换,导致索引失效

用or做连接会导致索引失效

​

索引规约

小表驱动大表

EXISTS