MySQL 索引失效的 10 大原因及深度解析

MySQL 索引失效（未使用索引）是性能优化的核心痛点。以下是 索引失效的 10 大原因及深度解析，结合原理与解决方案：

---

一、违反最左前缀匹配原则（组合索引失效）

• 场景：组合索引 (col1, col2, col3)
• 失效查询：

  WHERE col2 = 'value' 
  WHERE col3 = 'value' 
  WHERE col2 = 'value' AND col3 = 'value'
  

• 原因：B+Tree 索引按定义顺序构建，缺失最左列无法定位索引树入口。
• 解决方案：
  • 重写查询条件，包含最左列（即使用 IS NOT NULL）。
  • 调整索引顺序：高频查询列前置（如 (col2, col3, col1)）。

---

二、对索引列进行计算、函数或类型转换

• 失效场景：

  WHERE YEAR(create_time) = 2023        -- 函数
  WHERE amount * 2 > 100               -- 计算
  WHERE phone = 13800138000             -- 字符串列 vs 数字（隐式类型转换）
  

• 原因：索引存储原始值，计算/转换后的值无法匹配索引结构。
• 解决方案：
  • 重写条件：WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'
  • 避免隐式转换：WHERE phone = '13800138000'

---

三、使用 OR 连接非索引列

• 失效场景：

  WHERE indexed_col = 'A' OR non_indexed_col = 'B' -- 非索引列导致全表扫描
  

• 原因：MySQL 通常对 OR 使用全表扫描（除非所有列都有独立索引 → 触发索引合并）。
• 解决方案：
  • 改写为 UNION ALL：

    SELECT * FROM t WHERE indexed_col = 'A'
    UNION ALL
    SELECT * FROM t WHERE non_indexed_col = 'B';
    

  • 为 non_indexed_col 添加索引（触发 index_merge）。

---

四、模糊查询以 % 开头

• 失效场景：

  WHERE name LIKE '%小明%'   -- 全表扫描
  WHERE name LIKE '%小明'    -- 全表扫描
  

• 原因：B+Tree 按前缀排序，% 开头无法定位起始点。
• 解决方案：
  • 改用右模糊：WHERE name LIKE '小明%'（可用索引）。
  • 使用 全文索引（MATCH(name) AGAINST('+小明' IN BOOLEAN MODE)）。

---

五、优化器放弃索引（成本估算）

• 场景：表数据量少、索引区分度低、回表代价高。
• 原因：优化器认为全表扫描比索引+回表更快。
• 验证与解决：

  EXPLAIN SELECT * FROM table WHERE low_selectivity_col = 'value'; -- 观察 type 为 ALL
  

  • 强制使用索引：SELECT * FROM table FORCE INDEX(idx_name) WHERE ...
  • 更新统计信息：ANALYZE TABLE table;
  • 提高索引效率：优化查询语句或索引设计。

---

六、索引列参与负向查询

• 失效场景：

  WHERE status != 'active'
  WHERE id NOT IN (1, 2, 3)
  WHERE name NOT LIKE 'A%'
  

• 原因：负向查询需扫描大部分数据，优化器倾向全表扫描。
• 解决方案：
  • 改写为正向查询：WHERE status = 'inactive'。
  • 结合业务逻辑使用 UNION / EXISTS。

---

七、JOIN 字段类型或字符集不匹配

• 失效场景：

  -- 表1: utf8mb4，表2: latin1
  SELECT * FROM t1 JOIN t2 ON t1.name = t2.name; -- 字符集不同导致索引失效
  

• 原因：类型/字符集不一致需隐式转换，破坏索引有效性。
• 解决方案：统一字符集和数据类型：

  ALTER TABLE t2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  

---

八、索引合并（Index Merge）效率低下

• 场景：单表多个单列索引，查询使用 OR/AND 组合。
• 风险：索引合并可能比单个索引更慢（如 AND 合并需取交集）。
• 解决方案：
  • 创建组合索引覆盖查询条件。
  • 关闭索引合并：SET optimizer_switch='index_merge=off';

---

九、数据分布不均导致优化器误判

• 场景：索引列值严重倾斜（如 status 列 99% 为 1）。
• 现象：查询 WHERE status = 0 走索引，WHERE status = 1 走全表。
• 解决方案：
  • 使用 FORCE INDEX 提示优化器。
  • 定期更新统计信息：ANALYZE TABLE table;

---

十、索引物理损坏或统计信息过时

• 异常现象：索引存在但查询始终全表扫描。
• 排查：

  SHOW INDEX FROM table;    -- 检查索引状态
  CHECK TABLE table;        -- 检查表损坏
  

• 修复：

  REPAIR TABLE table;       -- MyISAM 表
  ALTER TABLE table ENGINE=InnoDB; -- InnoDB 重建表
  ANALYZE TABLE table;      -- 刷新统计信息
  

---

终极诊断工具：EXPLAIN 执行计划分析

关注关键字段：

字段	索引失效标志	原因分析
type	ALL（全表扫描）	未使用任何索引
key	NULL	优化器未选择索引
key_len	远小于索引定义长度	未使用完整组合索引
Extra	Using where; Using filesort	索引失效导致额外排序/过滤

---

索引失效排查流程图

通过精准定位失效原因，结合索引设计与 SQL 优化，可显著提升查询性能！