mysql 索引失效的场景

在mysql中，索引失效的场景有以下几种场景的情况  

• 不使用索引的列：如果查询的列没有被索引，或者索引并未包含查询的列，MySQL将不会使用索引进行查询，导致索引失效。

• LIKE查询以通配符开头：如果使用LIKE查询并且通配符（如%）出现在字符串的开头，索引无法使用，因为MySQL需要扫描整个索引来找到匹配项。

• 数据列类型不匹配：如果查询条件中的列与索引定义的数据类型不匹配，MySQL将无法使用索引。例如，如果查询条件中的字符串列使用了不匹配的字符集，则索引将失效。

• NULL值判断：如果查询条件中使用了IS NULL或IS NOT NULL操作符，MySQL通常无法使用索引进行优化。

• 对索引列进行函数操作：如果查询条件中对索引列进行函数操作（如LOWER、UPPER等），MySQL将无法使用索引进行优化。

• 数据量过小的表：对于非常小的表，MySQL可能认为全表扫描更快，而不使用索引。这是因为使用索引可能需要更多的I/O操作。

• 索引统计信息不准确：MySQL使用索引统计信息来估算查询成本和选择执行计划，如果统计信息过期或者不准确，MySQL可能会做出错误的决策导致索引失效。

具体例子

不使用索引列：
SELECT * FROM my_table WHERE non_indexed_column = 'value';
如果查询的列 non_indexed_column 没有被索引，MySQL将不使用索引进行查询，
而是执行全表扫描。

如果查询的列 non_indexed_column 没有被索引，MySQL将不使用索引进行查询，
而是执行全表扫描:
SELECT * FROM my_table WHERE YEAR(date_column) = 2022;
在查询条件中，如果对列 date_column 使用了函数 YEAR()，MySQL无法使用索引进行优化，索引失效。

LIKE查询以通配符开头：
SELECT * FROM my_table WHERE text_column LIKE '%value';
在查询条件中，如果使用 LIKE 进行模糊匹配，且通配符 % 出现在字符串的开头，
索引将失效，MySQL需要扫描整个索引进行匹配。

数据列类型不匹配：
SELECT * FROM my_table WHERE int_column = 'value';
如果查询条件中的 int_column 是整数类型的列，而查询时使用了字符串 'value'，
MySQL无法使用索引进行优化，因为数据类型不匹配。

对索引列进行函数操作：
SELECT * FROM my_table WHERE LOWER(text_column) = 'value';
在查询条件中对索引列 text_column 进行了函数操作 LOWER()，
MySQL无法使用索引进行优化，因为需要对每一行应用函数操作。

最佳左前缀： 联合索引 my_table (A,B,C)
SELECT * FROM my_table WHERE A= 10 and C = 'abb';
最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
对于联合索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，
索引后面的字段都无法被使用。

SELECT * FROM my_table WHERE D= 10 and C = 'abb';
如果查询条件中没有使用这些字段中第1个字段时，联合索引不会被使用。

范围条件右边的列索引失效： 联合索引 my_table (A,B,C)
SELECT * FROM my_table WHERE A= 10 and B>10  and C = 'abb';

结果：A字段上的索引还是失效了，因为创建联合索引时的顺序已经确定了，
是 A,B,C，当B字段使用范围条件时，位于B后面的C字段索引肯定失效。

不等或空值导致索引失效:
SELECT * FROM my_table WHERE A<>10;
SELECT * FROM my_table WHERE A != 'ccc';
SELECT * FROM my_table WHERE A is not null;

like通配符%在开头导致索引失效:
SELECT * FROM my_table WHERE A like 'abc%';
在使用LIKE关键字进行查询的查询语句中，
如果匹配字符串的第一个字符为“%”，索引就不会起作用。
只有“%"不在第一个位置，索引才会起作用

OR前后存在非索引的列导致索引失效:
SELECT * FROM my_table WHERE A ='aa' or  B=10;
如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，
那么索引会失效。也就是说，OR前后的两个条件中的列都是索引时，查询中才使用索引。
因为OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，
只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。

解决方案 

• 确保适当的索引被创建，并且列的数据类型与索引保持一致。
• 避免在查询中使用函数操作或表达式。
• 尽量避免在查询中使用LIKE以通配符开头的模式匹配。
• 更新索引统计信息并定期维护。
• 优化查询语句，确保使用索引的最佳执行计划。
• 对于单列索引，尽量选择针对当前查询过滤性更好的索引

• 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量在创建索引时把这个字段放在索引次序的最后面。