索引失效会导致查询性能急剧下降，以下是索引失效的常见场景和排查方法：

---

一、索引失效的典型场景

1. 违反最左前缀原则

• 问题：
  复合索引(a, b, c)，但查询仅使用b或c字段，或未按顺序使用字段。
• 示例：

  SELECT * FROM table WHERE b = 1 AND c = 2;  -- 无法触发索引
  SELECT * FROM table WHERE a = 1 AND c = 2;  -- 仅命中a字段索引

2. 隐式类型转换

• 问题：
  字段类型与查询条件类型不匹配，如字符串字段用数字查询。
• 示例：

  -- user_id是VARCHAR，但用数字查询
  SELECT * FROM users WHERE user_id = 10086;  -- 隐式转为字符串，索引失效

3. 对索引列运算或函数

• 问题：
  对索引列进行运算、函数调用或表达式计算。
• 示例：

  SELECT * FROM orders WHERE YEAR(create_time) = 2023;  -- 索引失效
  SELECT * FROM products WHERE price * 2 > 100;         -- 索引失效

4. 使用LIKE左模糊查询

• 问题：
  LIKE以通配符开头（如%value），无法利用索引。
• 示例：

  SELECT * FROM articles WHERE title LIKE '%mysql%';  -- 全表扫描

5. 使用OR连接非索引列

• 问题：
  OR连接的字段中至少有一个无索引。
• 示例：

  -- status字段无索引
  SELECT * FROM orders WHERE order_id = 100 OR status = 1;  -- 索引失效

6. 数据分布不均导致优化器放弃索引

• 问题：
  当索引列数据重复率过高（如状态字段有大量相同值），优化器可能选择全表扫描。
• 示例：

  -- gender字段90%为'M'，优化器可能放弃索引
  SELECT * FROM users WHERE gender = 'M';

7. 索引未覆盖查询字段

• 问题：
  若查询字段超出索引范围，需回表查询数据页，可能被优化器认为代价过高。
• 示例：

  -- 索引(a)，但需回表查b字段
  SELECT a, b FROM table WHERE a = 1;  -- 可能选择全表扫描

---

二、索引失效排查方法

1. 执行EXPLAIN分析

• 观察key和type字段：

  EXPLAIN SELECT * FROM users WHERE user_id = '10086';

  • key：显示实际使用的索引。
  • type：ALL表示全表扫描，index表示全索引扫描，ref/range表示有效索引使用。
  • rows：扫描行数，值过大可能索引未生效。

2. 检查查询条件

• 确认字段类型是否匹配：
  使用SHOW CREATE TABLE检查字段类型。
• 验证是否违反最左前缀规则：
  核对复合索引字段顺序。

3. 分析数据分布

• 计算索引选择性：

  -- 计算字段的唯一性比例
  SELECT COUNT(DISTINCT status)/COUNT(*) FROM orders;

  选择性低于10%可能导致优化器放弃索引。

4. 检查索引统计信息

• 更新统计信息：

  ANALYZE TABLE orders;  -- 更新统计信息，避免优化器误判

5. 查看慢查询日志

• 启用慢查询日志：

  SET GLOBAL slow_query_log = ON;
  SET GLOBAL long_query_time = 2;  -- 记录超过2秒的查询

  分析日志中未使用索引的查询。

---

三、解决方案

1. 优化查询语句

• 按最左前缀规则编写条件：

  -- 复合索引(a, b, c)
  SELECT * FROM table WHERE a = 1 AND b = 2;  -- 有效

• 避免对索引列运算：

  -- 改为范围查询
  SELECT * FROM orders WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

2. 重建或新增索引

• 覆盖索引：
  包含查询所需的所有字段，避免回表。

  ALTER TABLE orders ADD INDEX idx_cover (a, b, c, d);

• 函数索引（MySQL 8.0+）：
  支持对表达式建索引。

  ALTER TABLE orders ADD INDEX idx_year ((YEAR(create_time)));

3. 强制使用索引

• 提示优化器：

  SELECT * FROM orders FORCE INDEX (idx_status) WHERE status = 1;

4. 数据归档

• 冷热分离：
  将历史数据归档到其他表，减少单表数据量，提升索引效率。

---

四、避坑指南

1. 避免过度索引：
   每个索引增加写操作开销，优先优化高频查询。
2. 监控索引使用率：

   -- 查看未使用的索引
   SELECT * FROM sys.schema_unused_indexes;

3. 定期维护索引：
   重建碎片化索引：

   ALTER TABLE orders ENGINE=InnoDB;  -- 重建表及索引

---

总结

索引失效的核心原因包括违反最左前缀、隐式类型转换、索引列运算、数据分布不均等。通过EXPLAIN分析执行计划、检查查询条件、更新统计信息等方法可快速定位问题。优化需结合业务场景，针对性调整索引设计或查询逻辑，最终实现高性能查询。