MySQL索引失效的10种场景

1 MySQL索引

        索引就类似一本字典的目录，可以方便查找某一个字在一本字典的什么位置。MySQL的索引就是加速数据查询的效率。

        在使用索引时，为了提高查询效率，要尽可能少的从磁盘中读取数据，同时要保证读取数据足够有效。不可能一口气将全量数据都加载进内存，所以要分块读取。在考虑分块读取时，磁盘在和内存交互时是以页（16KB）为单位的，进行读取数据都是页的整数倍。

        聚簇索引和非聚簇索引

                索引列：默认是主键，没有主键选择唯一键，如果也没有唯一键，MySQL会自动生成一个rowid
                索引列和数据绑定在一起的索引称之为聚簇索引
                索引列没有和数据绑定在一起的索引称之为聚簇索引

                InnoDB存储引擎中既有聚簇索引也有非聚簇索引，而MyISAM存储引擎只有非聚簇索引

2 MySQL索引实现原理

        数据结构：B+树

        B+树是MySQL索引结构中最主要的一种结构，除了B+树还有hash索引，它们针对的存储引擎是不同的，大部分情况下是使用B+树。

        为什么要用B+树数据结构？（而不是使用二叉树、红黑树、B树）

        不管是使用二叉树，还是AVL树、红黑树，它们都会有一个问题，树的分支有且仅有2个。当想在树上插入更多的数据时，会造成树的深度很深，树的深度变深会导致查询次数变多，查询效率自然就变慢了。所以要考虑将二叉树变成多叉树，同时要依托有序的特点，就想到了多叉有序树即B树。在数据检索时，B树的数据和key值是放在一起的，就意味着每次数据读取时，一页16KB上有索引+实际数据，这样就会占用大量的存储空间，这样插入更多数据深度会增加。

        非叶子节点只存储key值，叶子节点存储实际数据，这就有了B+树。MySQL还对B+树进行了改造，将叶子节点通过双向指针关联，这样范围查询效率就会大大提升。

3 MySQL索引失效场景        

alter table `user` add index idx_name_age(`name`,`age`);

        3.1 组合索引不遵循最左匹配原则

                按照索引规则查询

explain SELECT * from user where name = 'lisi' and age = 22 and `desc` = '22';

                不遵守最左匹配原则，索引失效

explain SELECT * from user where  age = 22 and `desc` = '22';

        3.2 组合索引的前面索引列使用范围查询（<、>、like），会导致后续的索引失效

                范围查询（>、<、BETWEEN）后的联合索引字段无法继续使用索引‌

                解决：调整索引顺序或拆分查询条件‌

        3.3 不要再索引上做任何操作（计算、函数、类型转换）

EXPLAIN SELECT * from user where name = 1;

        3.4 is null和is not null无法使用索引

                索引默认不存储NULL值，导致无法快速定位‌

        3.5 尽量少使用or操作符，否则连接时索引失效

explain SELECT * from user where name = 'lisi' or age = 22  and  `desc` = '22';

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

EXPLAIN SELECT * from user where name = 1122;

        3.7 两表关联使用的条件字段中字段的长度、编码不一致会导致索引失效

                字符集和排序规则不一致是索引失效的‌主要原因‌，而字段长度不一致通常影响较小，但可能引发数据截断或隐式转换风险。

        3.8 like语句中，以%开头的模糊查询

                like查询，不以%开头，使用索引

explain SELECT * from user where name like 'li%';

                like查询，以%开头，索引失效

explain SELECT * from user where name like '%li%';

        3.9 如果MySQL中使用全表扫描比使用索引快，也会导致索引失效

                MySQL架构中有一个查询优化器，可能他内部认为全表扫描更快，就不会使用索引

                解决：强制使用索引（如FORCE INDEX）

        3.10 参数化查询的值分布不均

                当查询条件的值分布极端不均时，优化器可能认为全表扫描更快‌

                比如：索引列status的值为0（占比99%）和1（占比1%），查询WHERE status=1可能失效

                解决：强制使用索引（如FORCE INDEX）或优化数据分布‌