浅谈MySQL索引失效的场景

在使用MySQL数据库时，合理地创建和使用索引是优化查询性能的关键。在MySQL中采用了B+树作为索引结构来减少磁盘IO次数去提高数据的检索性能。但是在某些场景下，由于查询语句设计不合理然而，即使在为表列创建了索引，也有一些情况下MySQL查询优化器可能决定不使用这些索引。

其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器？基于cost开销(CostBaseOptimizer)，它不是基于规则(Rule-BasedOptimizer)，也不是基于语义。怎么样开销小就怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。
当Mysql使用索引的要扫描行记录数超过全表的10%-30%时，优化器可能会放弃走索引

类型不匹配

索引列的数据类型与用于查询的值的数据类型不匹配时，索引可能不会被使用，既隐式类型转换会导致索引失效，比如当查询条件类型为数值时，将字符串类型转换为浮点型可能会将索引数据无效。

//假设id是整数类型
SELECT * FROM users WHERE id = '123';

索引列上使用函数

在WHERE子句中对索引列使用函数会导致MySQL无法直接利用索引。这是因为索引是针对列的原始数据构建的，而不是针对经过函数处理后的结果。因此，当使用函数时，MySQL必须对每一行数据应用该函数，然后再进行比较，这样就无法直接使用索引了。

//错误的使用方式，不会使用索引
SELECT * FROM users WHERE MONTH(birthdate) = 4;

非最左匹配

非最左匹配指的是查询不满足最左前缀原则中的最左边的匹配要求，即查询字段不能包含联合索引中的所有索引字段。最左前缀原则是MySQL中的最佳左前缀原则，通过使用联合索引可以避免最左边的匹配问题。因此，如果查询字段包含联合索引，则应优先选择使用最左前缀原则。
比如有一个索引

index(a,b,c)

SELECT * FROM table_name WHERE b='1' AND c='2';//不会命中索引
SELECT * FROM table_name WHERE c='2';//不会命中索引

查询过滤条件的字段，必须顺序的包含索引中的字段，一旦跳过某个字段，则索引后面的字段就会失效。如果过滤条件中没有使用联合索引中的第一个字段，则这个索引不会被使用到。

SELECT * FROM table_name WHERE a='1' AND c='2';//会命中索引
SELECT * FROM table_name WHERE b='1' AND b='2';//会命中索引

错误模糊查询

使用LIKE模糊查询时，如果通配符（%或_）位于模式的开始位置，索引通常不会被使用

SELECT * FROM users WHERE username LIKE '%admin';

如果username列有索引，上面的查询中索引不会被使用

IS NULL和索引，IS NOT NULL和索引

在大多数情况下，IS NULL条件可以有效地使用索引，尤其是在较新版本的MySQL中。这意味着如果你有一个列上的索引，并且你正在查询该列为NULL的行，MySQL查询优化器通常会选择使用该索引。这是因为索引通常包含NULL值的条目，使得这类查询能够快速定位到包含NULL值的行

IS NOT NULL条件也可以使用索引，但其效果可能取决于数据的分布。如果大部分行的该列值都是非NULL，并且只有少数行是NULL，那么使用索引可能不会带来很大的性能提升，因为查询优化器可能会选择进行全表扫描。然而，如果非NULL值的行只占少数，那么索引的使用可以显著提高性能。

注意事项

索引选择性：如果一个列的值大部分是NULL或者大部分是非NULL，索引的选择性较低，优化器可能会选择不使用索引。
数据库版本：不同版本的MySQL在索引优化方面可能有所不同。建议查看特定版本的MySQL文档和发布说明以了解最新的行为。
其他索引类型：对于哈希索引等其他类型的索引，它们可能不存储NULL值，因此对于IS NULL条件，哈希索引可能不会被使用。

OR条件和复合索引

使用or操作符会导致MySQL无法使用索引，因为索引是根据某个字段进行排序建立的，当使用or操作符时，只有满足其中一个条件才能成立，否则该条件都不成立，记录的索引也会失效。所以，尽量避免使用or操作符。

使用不等于（<> 或 !=）

不等于操作符通常会选择除了一个特定值之外的所有行。如果这个特定值的分布不是很集中（即该值在表中不是很常见），那么查询将返回表中大部分的行。在这种情况下，使用索引可能不会比全表扫描更有效，因为大量的随机IO读取可能比顺序读取整个表的成本更高。
即使在某些情况下，不等于查询只返回少量的行，MySQL优化器也需要评估使用索引的成本。如果优化器预计使用索引的成本高于其他方法（如全表扫描），它可能会选择不使用索引。
B树索引是按顺序存储的，非常适合查找范围值或者特定值。对于<>或!=操作，索引需要检查索引中的每个条目以确定是否符合条件，这可能导致大量的索引跳跃，降低了查询效率。

SELECT * FROM `user` WHERE `name` != 'jack';