MySQL索引失效十大场景分析与优化实践

数据库索引失效是导致查询性能急剧下降的常见原因。在实际生产环境中，我们观察到大量慢查询并非源于数据量本身，而是由于索引未被有效利用。本文系统梳理索引失效的十大典型场景，结合执行计划分析、B+树结构原理与优化器行为，给出可落地的 SQL 重写与设计策略。所有示例基于 MySQL 8.0（InnoDB 引擎），但核心原理适用于主流关系型数据库。

索引失效的本质是优化器判断使用索引的成本高于全表扫描。成本模型综合考虑 I/O 次数、CPU 计算、回表开销等因素。当查询条件无法有效缩小索引扫描范围，或需要对索引结果进行额外处理时，优化器倾向于放弃索引。理解 B+ 树索引的最左前缀匹配原则、覆盖索引优势及函数索引限制，是避免失效的前提。

1. 对索引列使用函数或表达式

场景描述：在 WHERE 子句中对索引列应用函数（如 DATE(), UPPER()）或算术表达式（如 col + 1），导致优化器无法直接定位索引键值。

-- 失效示例：orders 表有索引 idx_order_time(order_time)
SELECT * FROM orders WHERE DATE(order_time) = '2023-10-01';

执行计划显示 type: ALL，全表扫描。因为 DATE(order_time) 需要对每一行计算后才能比较，索引树无法直接用于范围查找。

优化策略：重写为范围查询，利用索引的有序性。

-- 优化后
SELECT * FROM orders 
WHERE order_time >= '2023-10-01 00:00:00' 
  AND order_time < '2023-10-02 00:00:00';

若业务必须使用函数，可考虑创建函数索引（MySQL 8.0+）：

CREATE INDEX idx_order_date ON orders ((DATE(order_time)));

2. 隐式类型转换

场景描述：当索引列与查询值类型不一致时，数据库会进行隐式转换，通常导致索引失效。

-- users 表有索引 idx_user_id(user_id VARCHAR(20))
SELECT * FROM users WHERE user_id = 12345; -- 传入整型

执行计划显示 Using where; Using filesort。因为 user_id 是字符串类型，而 12345 是整数，MySQL 会将 user_id 转换为数字再比较，相当于对索引列应用了函数。

优化策略：确保查询值与列定义类型一致。

-- 优化后
SELECT * FROM users WHERE user_id = '12345';

可通过 EXPLAIN FORMAT=JSON 查看是否发生类型转换：

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "users",
      "access_type": "ALL",
      "possible_keys": ["idx_user_id"],
      "rows_examined_per_scan": 1000000,
      "filtered": "100.00",
      "attached_condition": "((`test`.`users`.`user_id` = 12345))"
    }
  }
}

3. LIKE 以通配符开头

场景描述：LIKE '%keyword' 或 LIKE '%keyword%' 无法利用 B+ 树的前缀匹配特性。

-- products 表有索引 idx_name(name)
SELECT * FROM products WHERE name LIKE '%手机%';

执行计划为全表扫描。B+ 树索引按字典序存储，前导通配符使起始位置未知。

优化策略：

若业务允许，改为后缀匹配：name LIKE '手机%'
使用全文索引（FULLTEXT）处理模糊搜索：

ALTER TABLE products ADD FULLTEXT(name);
SELECT * FROM products WHERE MATCH(name) AGAINST('手机' IN NATURAL LANGUAGE MODE);

对于固定长度后缀查询，可冗余反向列并建索引：

ALTER TABLE products ADD COLUMN name_reverse VARCHAR(255) AS (REVERSE(name));
CREATE INDEX idx_name_rev ON products(name_reverse);
-- 查询 'xxx手机' 可转为
SELECT * FROM products WHERE name_reverse LIKE '机手%';

4. OR 条件未全部命中索引

场景描述：OR 连接的条件中，只要有一个未使用索引，可能导致整个查询放弃索引。

-- orders 表有索引 idx_status(status), 无索引 on user_id
SELECT * FROM orders WHERE status = 'paid' OR user_id = 1001;

执行计划显示全表扫描。因为 user_id = 1001 无索引，优化器认为合并结果集成本过高。

优化策略：

为 OR 中所有列添加索引（复合索引或单列索引）
改写为 UNION：

-- 优化后
SELECT * FROM orders WHERE status = 'paid'
UNION
SELECT * FROM orders WHERE user_id = 1001;

注意：UNION 会去重，若无需去重可用 UNION ALL 提升性能。

5. 复合索引未遵循最左前缀原则

场景描述：复合索引 (col1, col2, col3) 仅在查询条件包含 col1 时生效。若跳过 col1 直接查 col2，索引失效。

-- logs 表有复合索引 idx_app_level(app_id, log_level)
SELECT * FROM logs WHERE log_level = 'ERROR'; -- 未指定 app_id

执行计划为全表扫描。B+ 树先按 app_id 排序，再按 log_level 排序，单独 log_level 无序。

优化策略：

调整索引顺序，将高区分度或常用过滤字段放左侧
若 log_level 查询频繁，单独为其建索引
使用覆盖索引减少回表：

-- 若只需查询 log_level 和 id
CREATE INDEX idx_level_cover ON logs(log_level, id);
SELECT id FROM logs WHERE log_level = 'ERROR';

6. 索引列参与 NULL 值判断

场景描述：虽然 B+ 树索引包含 NULL 值，但 IS NULL 或 IS NOT NULL 的选择性通常较低，优化器可能认为全表扫描更高效。

-- users 表有索引 idx_email(email)
SELECT * FROM users WHERE email IS NULL;

若 email IS NULL 的记录占比超过 20%，优化器常选择全表扫描。

优化策略：

评估 NULL 值比例，若比例低（<5%），可强制使用索引：

SELECT * FROM users FORCE INDEX(idx_email) WHERE email IS NULL;

业务上避免 NULL，用默认值（如空字符串）替代
为 NULL 值创建专用索引（如函数索引）：

CREATE INDEX idx_email_null ON users ((email IS NULL));

7. 不等号（<> 或 !=）导致范围扫描失效

场景描述：col != value 通常导致全表扫描，因为需检查除该值外的所有记录。

-- products 表有索引 idx_category(category)
SELECT * FROM products WHERE category != 'electronics';

执行计划为全表扫描。即使 category = 'electronics' 只占 1%，优化器仍需扫描其余 99% 数据。

优化策略：

若排除值占比小，改用 NOT EXISTS 或 LEFT JOIN：

-- 假设 electronics 产品少
SELECT p.* FROM products p
LEFT JOIN (SELECT id FROM products WHERE category = 'electronics') e ON p.id = e.id
WHERE e.id IS NULL;

业务层面拆分为多个等值查询（若枚举值有限）

8. 字符串未加引号引发隐式转换

场景描述：与场景 2 类似，但特指字符串字面量未加单引号。

-- users 表有索引 idx_phone(phone VARCHAR(20))
SELECT * FROM users WHERE phone = 13800138000; -- 未加引号

优化器将 phone 转换为 BIGINT 比较，索引失效。

优化策略：严格使用引号包裹字符串值。

SELECT * FROM users WHERE phone = '13800138000';

在 Java 应用中，使用 PreparedStatement 可自动处理类型：

String sql = "SELECT * FROM users WHERE phone = ?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, "13800138000"); // 显式设置字符串类型

9. 覆盖索引缺失导致回表

场景描述：索引仅用于过滤，但 SELECT 列不在索引中，需回表查询聚簇索引，当结果集较大时，回表成本可能超过全表扫描。

-- orders 表有索引 idx_status(status)
SELECT order_id, amount FROM orders WHERE status = 'shipped';

若 status = 'shipped' 占比 30%，回表 30 万次（假设总 100 万行）可能比全表扫描更慢。

优化策略：创建覆盖索引，包含所有查询字段。

CREATE INDEX idx_status_cover ON orders(status, order_id, amount);

执行计划将显示 Using index，无需回表。

10. 统计信息过期导致优化器误判

场景描述：表数据分布变化后未更新统计信息，优化器基于陈旧数据估算行数，错误选择全表扫描。

-- 大批量删除后，status = 'archived' 实际只剩 10 行
-- 但统计信息仍显示 10 万行，优化器认为全表扫描更快
SELECT * FROM orders WHERE status = 'archived';

优化策略：

定期更新统计信息：

ANALYZE TABLE orders;

MySQL 8.0 默认开启 innodb_stats_auto_recalc，但大批量 DML 后建议手动触发
通过 SHOW INDEX FROM orders 查看 Cardinality 是否合理

索引失效诊断流程

当怀疑索引失效时，按以下步骤排查：

获取执行计划：
```
EXPLAIN FORMAT=JSON SELECT ...;
```
关注 access_type（应为 ref/range 而非 ALL）、key（是否使用预期索引）、rows（扫描行数是否合理）
验证索引存在性：
```
SHOW CREATE TABLE table_name;
```

检查数据分布：

SELECT COUNT(*) FROM table WHERE condition; -- 实际匹配行数
SELECT COUNT(*) FROM table; -- 总行数

对比强制索引效果：

SELECT * FROM table FORCE INDEX(idx_name) WHERE ...;

更新统计信息后重试：
```
ANALYZE TABLE table;
```

总结

索引失效的核心在于查询条件与索引结构的不匹配。通过避免函数操作、确保类型一致、遵循最左前缀、利用覆盖索引等策略，可显著提升索引命中率。在 Java 应用中，结合 PreparedStatement 防止隐式转换，并通过监控慢查询日志持续优化。最终，任何索引优化都需以执行计划和实际性能数据为依据，而非主观假设。