MySQL索引失效15个场景及解决方案

场景一：对索引列使用函数或表达式  

发生原因：MySQL 优化器无法直接使用索引匹配计算后的值

文档依据：https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html 

解决方案：

        1、MySQL 5.7：使用生成列（Generated Column）并为其创建索引。  

        2、MySQL 8.0+：直接创建函数索引（Functional Index）。  

场景二：隐式类型转换  

发生原因：类型不匹配导致隐式转换，等价于对列应用函数

文档依据：https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html 

解决方案：确保查询条件与列类型严格一致，例如字符串加引号。

场景三：联合索引未遵循最左前缀原则

发生原因：B+树索引按最左列排序，缺失最左列时无法定位数据

文档依据：https://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html

解决方案：

        1、调整联合索引顺序或补充缺失的最左列条件

        2、MySQL 8.0+：使用索引跳跃扫描（Index Skip Scan）特性（需开启优化器选项 `optimizer_switch='skip_scan=on'`）。

场景四：左模糊查询（`LIKE '%abc'`）  

发生原因：B+树索引无法反向匹配前缀

文档依据：https://dev.mysql.com/doc/refman/5.7/en/index-btree-hash.html 

解决方案：

        1、改用右模糊（`LIKE 'abc%'`），或结合倒序存储+右模糊。

        2、MySQL 8.0+：使用全文索引（FULLTEXT Index）优化模糊查询。

场景五：OR条件中部分列无索引  

发生原因：优化器无法合并索引范围扫描

文档依据：(https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html  

解决方案：拆分为 `UNION ALL` 查询，或为所有OR涉及的列创建索引。

场景六：使用不等于操作符（`!=` 或 `<>`）  

发生原因：不等于操作符通常匹配大量数据，优化器选择全表扫描

文档依据：https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html  

解决方案：结合覆盖索引或改写为 `NOT IN` / `NOT EXISTS`

场景七：索引列参与运算  

发生原因：运算后的值无法匹配索引原始值

文档依据：https://dev.mysql.com/doc/refman/5.7/en/create-index.html  

解决方案：重写查询条件，避免对列直接运算。

场景八：数据分布不均匀  

发生原因：优化器基于统计信息估算成本，可能跳过索引

文档依据：https://dev.mysql.com/doc/refman/5.7/en/optimizer-statistics.html  

解决方案：使用 `FORCE INDEX` 提示或更新统计信息（`ANALYZE TABLE`）

场景九：隐式字符集转换  

发生原因：字符集不一致导致联表查询时索引失效

文档依据：https://dev.mysql.com/doc/refman/5.7/en/charset-conversion.html  

解决方案：统一表的字符集（如 `utf8mb4`）

场景十：范围查询后的索引列失效

发生原因：范围查询导致后续索引列无法筛选

文档依据：https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html

解决方案：调整索引顺序，将等值查询列放在范围列前。

场景十一：使用 `IS NULL` 或 `IS NOT NULL`

发生原因：`NULL` 值的处理依赖索引类型

文档依据：https://dev.mysql.com/doc/refman/5.7/en/is-null-optimization.html  

解决方案：使用 `NOT NULL` 约束或覆盖索引

场景十二：ORDER BY 排序与索引不匹配  

发生原因：排序方向不一致时无法利用索引

文档依据：https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

解决方案：创建与排序完全一致的索引（如 `(a ASC, b DESC)`）。

场景十三：JOIN 条件字符集/类型不匹配  

发生原因：同场景九，隐式转换导致索引失效。  

解决方案：统一联表字段的类型和字符集。

场景十四：统计信息不准确  

发生原因：过时的统计信息误导优化器

文档依据：https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html  

解决方案：定期执行 `ANALYZE TABLE`

场景十五：索引自身问题  

发生原因：索引损坏或优化器禁用 

文档依据：https://dev.mysql.com/doc/refman/5.7/en/index-hints.html 

解决方案：重建索引（`ALTER TABLE ... DROP/ADD INDEX`）

PS1：版本差异说明  

1、函数索引：仅 MySQL 8.0+ 支持直接创建，5.7 需通过生成列实现。  

2、索引跳跃扫描：8.0+ 支持，可缓解联合索引最左前缀缺失问题。  

3、优化器改进：8.0+ 对范围查询、`OR` 条件等有更智能的优化。