MySQL索引优化之不适合构建索引及索引失效的几种情况详解 - 主机乐

每张表的索引不建议超过6个（占用空间、降低表更新速度）
最终到底是否使用索引还是优化器进行决定的
优化器会根据数据量、数据库版本、数据选择读进行查询代价的比较，从而决定是否使用索引
建立索引的时候将需要范围匹配的字段建立在索引的尾部，避免失效
在建立表的时候将字段设置为not null同时设置默认值，当需要查找没有值的记录的时候就可以使用where xxx = 默认值，放置使用is not null导致索引失效
页面搜索的时候严谨左模糊或者全模糊(like ‘%abc’)
对于过滤性较好的字段建立在联合索引的前面，这样就可以优先过滤比较多的数据

不建议建立索引的场景

场景一：数据少的表

当数据比较少的时候，索引的优势就不明显了，因为数据库的存储引擎也是非常快的，相较于需要查询索引在进行回表操作，可能直接查询的性能会更高一些，所以数据相对较少的表不建议建立索引

场景二：有大量重复数据的字段

类似于性别字段，只有“男”和“女”两个不同的值，所以索引一半的数据是“男”一半的数据是“女”，那么建立索引并不能进行快速的查询等，所以不建议在有大量重复数据的列上建立索引

场景三：频繁更新的表（update/delete/insert）

因为表中更新数据的时候，索引也是需要进行对应的维护的，如果一个表近期需要频繁的进行增删改操作，那么就需要耗费大量的时间去维护索引，不建议建立索引，可以在需要进行频繁的更新操作的时候将索引删除，更新完毕之后重建索引

场景四：没有使用的字段（where/group by/order by）

不是where/group by/order by后面的字段没有必要建立索引，因为不会使用到该索引

场景五：不要定义冗余索引

场景二：使用不等于(where age != 18)

当使用等值运算，那么是可以在索引中进行查找的，但是如果是不等于，那么则需要遍历所有数据，所以所失效

从这里可以看出是没有使用索引的(key = null)，因为创建的索引是先按照age进行排序，在age相同的情况下按照classId和name排序，如果在查询的时候需要直接按照classId进行排序查找，那么就无法使用该索引，即索引失效。

如果需要使用使用索引，那么就一定需要到联合索引的第一个字段age，案例如下

场景五：类型转换导致索引失效

发生了类型转换，无法使用索引kye = null，索引失效

使用索引的时候一定需要保证数据类型是一致的，否则系统就需要进行转换，那么就无法使用索引

场景六：使用范围查询导致联合索引其他字段失效

使用的都是等值匹配，整个索引皆可用，有效索引长度为73

也就是在对于联合索引来说，如果在使用的时候是等值匹配，那么就可以重复的利用索引，如果不是等值匹配，那么该字段也是可以使用索引的，但是该字段右边的字段就将失效

建议在建立索引的时候将需要范围匹配的字段建立在索引的最后面

场景七：在使用like的时候，如果以%开头导致索引失效

key = null，索引失效

因为建立的索引实际上是按照整个字符串的从第一个开始进行比较排序的，所以在使用like的时候，也只能够重现进行比较，如果使用的是’%abc’，那么查询的就是以abc结尾的数据，无法使用索引

场景八：or前后出现非索引字段，索引失效

因为如果name不等于’xiao’的时候那么就会继续判断classId是否等于1001，那么实际上还是会进行全表扫描，所以索引失效（也就是进行name判断的时候可以使用索引，但是在判断classId的时候又要全表扫描，那么优化器就直接进行全表扫描），但是如果or前后的字段都有索引了，那么就就会使用索引