索引失效常见问题排查手册

### 索引失效常见问题排查手册

在数据库性能优化的浩瀚工程中，索引失效往往是导致系统响应迟缓、CPU飙升的“头号嫌疑人”。索引本应是数据的高速公路，但在特定场景下，它却会退化为羊肠小道，甚至迫使数据库进行全表扫描。本手册旨在系统化梳理索引失效的常见原因，并提供可落地的排查与解决方案，助你快速定位性能瓶颈。

#### 第一阶段：快速诊断——让执行计划说话

在盲目猜测之前，必须依赖数据说话。`EXPLAIN` 是数据库提供的核心诊断工具，它能展示SQL语句的执行计划，揭示优化器的决策过程。

**执行分析命令**

在你的查询语句前加上 `EXPLAIN` 关键字(MySQL中推荐使用 `EXPLAIN FORMAT=JSON` 以获取更详细信息)。

**关键指标解读**

- **type**：这是判断索引是否生效的最直观指标。

- **ALL**：全表扫描。这是最糟糕的情况，意味着索引完全失效。

- **index**：全索引扫描。虽然比全表扫描快，但依然效率低下。

- **range / ref / eq_ref**：索引有效，分别代表范围扫描、非唯一索引扫描和唯一索引扫描。

- **key**：显示数据库实际决定使用的索引。如果为 `NULL`，说明没有使用任何索引。

- **rows**：预估需要扫描的行数。数值越大，性能开销越大。

- **filtered**：表示存储引擎返回的数据在经过过滤后剩余的比例。

#### 第二阶段：核心失效场景与破解之道

通过执行计划定位到问题SQL后，我们需要深入代码层面，排查是否触犯了以下索引失效的“天条”。

**违背最左前缀原则**

这是复合索引(联合索引)失效最常见的原因。复合索引遵循“最左前缀”原则，即查询必须从索引的最左列开始，且不能跳过中间的列。

- **失效场景**：假设建立了联合索引 `(a, b, c)`。

- 查询 `WHERE b = 1` 或 `WHERE c = 1`：直接跳过首列，索引失效。

- 查询 `WHERE a = 1 AND c = 1`：跳过了中间列 `b`，只有列 `a` 能用到索引，列 `c` 失效。

- **特殊注意**：范围查询(`>`, `<`, `BETWEEN`)右侧的列索引也会失效。例如 `WHERE a = 1 AND b > 2 AND c = 3`，此时 `a` 和 `b` 用到索引，但 `c` 无法使用索引。

- **解决方案**：调整查询条件顺序以匹配索引，或根据查询频率调整索引列的顺序。

**索引列“动了手脚”：函数、运算与类型转换**

索引存储的是列的原始值。一旦在查询条件中对索引列进行了操作，数据库就无法直接匹配索引树中的值，从而导致失效。

- **函数运算**：

- **失效**：`WHERE DATE(create_time) = '2023-01-01'`

- **修正**：将函数应用在值上，而非列上。`WHERE create_time >= '2023-01-01 00:00:00' AND create_time < '2023-01-02 00:00:00'`

- **算术运算**：

- **失效**：`WHERE id + 1 = 100`

- **修正**：`WHERE id = 99`

- **隐式类型转换**：

- **失效**：当 `user_id` 字段为字符串类型(VARCHAR)时，执行 `WHERE user_id = 123`。数据库会自动执行 `WHERE CAST(user_id AS INT) = 123`，导致索引失效。

- **修正**：保持类型严格一致，`WHERE user_id = '123'`。

**模糊查询的陷阱**

LIKE 查询并非完全不能用索引，关键在于通配符的位置。

- **失效场景**：`WHERE name LIKE '%abc'` 或 `WHERE name LIKE '%abc%'`。前导通配符(%在开头)使得数据库无法确定查找的起始位置，只能全表扫描。

- **有效场景**：`WHERE name LIKE 'abc%'`。这符合最左匹配原则。

- **解决方案**：如果必须进行前导模糊查询，考虑使用全文索引(Fulltext Index)或搜索引擎(如Elasticsearch)。

**优化器的“负向”选择**

并非所有写法错误都会导致索引失效，有时是优化器经过成本计算后，认为“不走索引”更快。

- **OR 条件**：如果 `OR` 连接的两个字段中，有一个没有索引，或者两个都有索引但数据区分度不高，优化器可能会放弃索引。

- **修正**：尽量确保 `OR` 两边的字段都有索引，或改写为 `UNION ALL`。

- **NOT 操作符**：使用 `!=`, `<>`, `NOT IN`, `IS NOT NULL` 等否定操作时，数据库通常需要扫描全表来排除数据。

- **数据区分度低**：如果某列(如“性别”)只有极少量的唯一值，且查询匹配了大量行(例如超过全表数据的20%)，优化器会认为全表扫描比回表查询更划算，从而放弃索引。

#### 第三阶段：进阶优化——拒绝“回表”

有时候索引生效了，但查询依然很慢，这可能是因为发生了大量的“回表”操作。

**回表原理**

在InnoDB引擎中，二级索引(非主键索引)的叶子节点存储的是索引列的值和主键ID。当查询需要的字段(如 `SELECT *`)不完全包含在二级索引中时，数据库拿到主键ID后，必须再次回到聚簇索引(主键索引)中查找完整的行数据。这个过程就是回表。

**解决方案：覆盖索引**

覆盖索引是指一个索引包含了查询所需的所有字段，从而避免了回表。

- **场景**：`SELECT id, name, age FROM user WHERE name = 'Alice'`。

- **优化**：建立联合索引 `(name, age, id)`。此时，索引树上直接包含了查询所需的所有数据，数据库无需再访问主键索引，性能将大幅提升。

#### 总结

索引失效的排查是一场与数据库优化器的博弈。通过 `EXPLAIN` 锁定嫌疑SQL，对照上述五大失效场景逐一排查，并善用覆盖索引减少回表，你就能构建出健壮的数据库查询体系。记住，最好的索引不是建得最多，而是建得最准。