mysql索引失效原因和设计原则

sql语句是否使用索引本质上取决于执行方案是否开销更小（狭义上的话看回表次数是否更少），具体跟几个因素有关：是否建立索引、数据库版本、数据量、数据选择度区分度等等。

8.0版本可参考尚硅谷的宋红康老师的视频。141-数据准备与索引失效的11种情况1_哔哩哔哩_bilibili

注意，我这里标题的表述是“可能原因”，而不是一定。是否真正失效，应以参考explain计划为主，以实际执行时间为准，这篇博文只作为辅助参考。

explain执行语法：explain select sql_no_cache <这里输入select语句的完整语句>

示例：explain select sql_no_cache name from person where name='tom'

一、失效可能原因

1、违背最左匹配原则

没有使用复合索引中第一个索引的列

2、对列进行计算、使用函数

3、隐式类型转换

隐式类型转换：条件中使用字符型字段，但是赋值的时候使用数字类型的值。本质上是用了to_number函数，可归属于第2点

4、范围条件右边的列的索引可能失效

5、使用不等的条件(!=,<>)

6、is null或is not null有时候不走索引

如果需要回表次数多就不会走

7、使用like时%在前面

8、or前后存在使用非索引的列

9、数据库和表的字符集统一使用utf8mb4

  统一使用utf8mb4( 5.5.3 版本以上支持 ) 兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。

二、索引设计原则

可以从三个大的方面上来考虑：有效利用率、占用空间、性能。思考方面适用于所有索引数据库。

1、有效利用率

（1）经常作为查询条件、join、order by的列可以考虑建立索引

（2）数据量大的表才考虑建立索引
（3）正确使用索引，避开上文提到的让索引失效的情况

2、占用空间

（1）控制索引的数量。索引太多不仅占用空间，性能也没有太大提升。

（2）控制索引的长度。索引长度控制在10到20以内。

3、性能

（1）如果有几个字段经常一起作为条件查询，考虑创建组合索引。原则上查询越频繁、级别越高的列越靠前。

（2）不怎么频繁更新的列才考虑建索引。因为维护索引有性能损耗。

以下可能只适用于索引为btree结构的数据库引擎

（3）主键插入顺序尽量和主键在索引中的排序顺序保持一致。因为要防止索引树频繁的叶分裂。

（4）索引长度不宜太长，所以大字段（clob/blob）也不建索引。因为索引值太大，索引树高度变高,io次数变多，性能变慢。

（5）列值基数太小的不考虑建立索引，基数占总数的1/5以下才考虑。