第 11 章：索引失效与查询改写

zjc 于 2026-01-11 发布

这是《MySQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 “加了索引还是慢”是 MySQL 排障的高频问题。索引失效并不总是语法层面完全不能用，更多时候是优化器评估代价后放弃，或者索引只能使用很少前缀，导致扫描量仍然很大。

11.1 索引失效的判断方法

不要凭感觉判断，先比较执行计划：

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 1001;

重点检查：

key 是否为 NULL；
possible_keys 是否包含预期索引；
key_len 是否只用了部分列；
rows 是否明显过大；
Extra 是否有临时表或排序；
是否有隐式转换提示。

再用真实参数复现。同一条 SQL 不同参数可能选择不同执行计划，尤其是数据倾斜时。

11.2 函数或表达式包裹索引列

失效写法：

SELECT *
FROM orders
WHERE DATE(created_at) = '2026-08-25';

B+Tree 按 created_at 原值排序，DATE(created_at) 的结果在树上是跳跃分布的，无法直接定位。

改写：

SELECT *
FROM orders
WHERE created_at >= '2026-08-25 00:00:00'
  AND created_at < '2026-08-26 00:00:00';

常见类似问题：

失效写法	改写方向
`DATE(paid_at)='2026-08-25'`	半开区间
`SUBSTR(order_no,1,4)='2026'`	前缀范围或额外查询列
`CAST(user_id AS CHAR)='1001'`	使用数字参数
`amount + 0 > 100`	直接比较
`id - 1 = 99`	`id = 100`

如果业务必须使用表达式，MySQL 8.0.13+ 可以创建函数索引。

11.3 隐式类型转换

order_no 是 VARCHAR：

SELECT *
FROM orders
WHERE order_no = 20260825000001;

应改为：

SELECT *
FROM orders
WHERE order_no = '20260825000001';

数字与字符串比较时，MySQL 通常把字符串转换为数字，索引列被函数化后无法正常使用 B+Tree。

常见类型问题：

列类型	错误参数	正确参数
`VARCHAR`	数字	字符串
`BIGINT`	字符串数字通常仍可用	数字
`DATETIME`	非标准格式	标准时间或范围
`DECIMAL`	浮点字符串	精确数字
`BINARY`	字符集不一致	二进制或相同字符集

程序中必须使用参数绑定，并声明正确 JDBC 类型。

11.4 前导模糊匹配

可以走前缀索引：

SELECT *
FROM products
WHERE product_name LIKE 'MySQL%';

普通 B+Tree 难以使用：

SELECT *
FROM products
WHERE product_name LIKE '%Book%';

因为树按前缀有序，后缀匹配没有确定起点。

可选方案：

限定前缀；
使用全文索引；
同步 Elasticsearch；
搜索词单独表；
限制扫描范围后模糊过滤；
业务上引导用户选择筛选条件。

转义通配符：

SELECT *
FROM products
WHERE product_name LIKE '%50\\%%';

11.5 联合索引缺少前导列

索引：

KEY idx_user_status_created (user_id, status, created_at)

慢写法：

SELECT *
FROM orders
WHERE status = 'PAID'
  AND created_at >= '2026-08-01';

改法一：保留 user_id 条件：

SELECT *
FROM orders
WHERE user_id = 1001
  AND status = 'PAID'
  AND created_at >= '2026-08-01';

改法二：为后台查询建立新索引：

ALTER TABLE orders
  ADD INDEX idx_status_created (status, created_at);

MySQL 8.0 的 Skip Scan 对前导列取值很少的场景可能有帮助，但不是通用方案。

11.6 OR 与 AND 的差异

AND 通常能组合使用同一联合索引的多个列；OR 连接不同索引列时需要分别访问：

SELECT *
FROM orders
WHERE user_id = 1001
   OR order_no = 'NO001';

可能使用 index_merge，也可能退化为全表扫描。拆分为 UNION ALL 往往更可控：

SELECT *
FROM orders
WHERE user_id = 1001

UNION ALL

SELECT *
FROM orders
WHERE order_no = 'NO001'
  AND NOT (user_id = 1001);

如果 OR 两边是同一列，用 IN 表达：

SELECT *
FROM orders
WHERE user_id IN (1001, 1002)
  AND status = 'PAID';

11.7 范围条件后的列

索引：

KEY idx_status_created_amount (status, created_at, amount)

查询：

SELECT *
FROM orders
WHERE status = 'PAID'
  AND created_at >= '2026-08-01'
  AND amount > 100;

status 精确匹配，created_at 走范围，amount 在 B+Tree 上无法继续作为定位前缀，但仍可能被索引条件下推过滤。

改写方向：

缩小时间范围；
高频固定金额条件可调整索引顺序；
使用覆盖索引减少回表；
交给分析库；
增加预聚合或派生过滤字段。

11.8 字符集与排序规则不一致

JOIN 时如果两边字符集或排序规则不同，可能导致索引无法使用：

SELECT COUNT(*)
FROM orders o
JOIN channels c ON o.channel = c.channel_code;

检查：

SELECT
  TABLE_NAME,
  COLUMN_NAME,
  CHARACTER_SET_NAME,
  COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'shop'
  AND COLUMN_NAME IN ('channel', 'channel_code');

统一字符集：

ALTER TABLE orders
  MODIFY channel VARCHAR(32)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;

新旧表、临时表、跨库表都要统一，否则可能只在特定 SQL 上暴露。

11.9 NOT、!= 和 IN 的选择性

以下条件不是绝对不能用索引：

SELECT *
FROM orders
WHERE status <> 'CANCELED';

但 <> 常常需要扫描大量值。如果非取消状态占比 95%，全表扫描可能是正确选择。

改成 IN 并不一定更快，但语义更清晰：

SELECT *
FROM orders
WHERE status IN ('CREATED', 'PAID', 'SHIPPED');

核心是选择性：排除 5% 不如直接定位要的 5%。任务类查询应显式列出待处理状态。

11.10 ORDER BY 无法使用索引

索引：

KEY idx_user_created (user_id, created_at)

可用：

SELECT *
FROM orders
WHERE user_id = 1001
ORDER BY created_at;

不能用该索引完成排序：

SELECT *
FROM orders
ORDER BY created_at;

SELECT *
FROM orders
WHERE user_id = 1001
ORDER BY updated_at;

SELECT *
FROM orders
WHERE user_id > 1000
ORDER BY created_at, user_id;

排序优化原则：

ORDER BY 列顺序与联合索引一致；
前面的等值条件是索引前缀；
方向一致或使用降序索引；
范围条件可能破坏后续排序复用；
小结果集 filesort 未必是问题。

MySQL 8.0 支持降序索引：

ALTER TABLE orders
  ADD INDEX idx_user_created_desc (user_id, created_at DESC);

11.11 查询改写案例

案例一：按天统计

原写法：

SELECT DATE(created_at) AS dt, COUNT(*)
FROM orders
GROUP BY DATE(created_at);

可改为生成列：

ALTER TABLE orders
  ADD COLUMN created_date DATE
    GENERATED ALWAYS AS (DATE(created_at)) STORED,
  ADD INDEX idx_created_date (created_date);

SELECT created_date AS dt, COUNT(*)
FROM orders
GROUP BY created_date;

案例二：函数包裹状态

原写法：

SELECT *
FROM orders
WHERE UPPER(status) = 'PAID';

改写：

SELECT *
FROM orders
WHERE status = 'PAID';

并规范数据写入，统一大小写。

案例三：COUNT 多状态

多个子查询：

SELECT
  (SELECT COUNT(*) FROM orders WHERE status='CREATED') AS created_count,
  (SELECT COUNT(*) FROM orders WHERE status='PAID') AS paid_count,
  (SELECT COUNT(*) FROM orders WHERE status='CANCELED') AS canceled_count;

一次扫描：

SELECT
  SUM(status='CREATED') AS created_count,
  SUM(status='PAID') AS paid_count,
  SUM(status='CANCELED') AS canceled_count
FROM orders;

若表很大，实时 COUNT 本身仍可能是问题，应使用预聚合或近似统计。

案例四：深分页

原写法：

SELECT *
FROM orders
ORDER BY id
LIMIT 20 OFFSET 1000000;

游标分页：

SELECT *
FROM orders
WHERE id > 1000000
ORDER BY id
LIMIT 20;

复合排序游标：

SELECT *
FROM orders
WHERE user_id = 1001
  AND (created_at < '2026-08-25 10:00:00'
       OR (created_at = '2026-08-25 10:00:00' AND id < 123))
ORDER BY created_at DESC, id DESC
LIMIT 20;

必须使用唯一列做 tie-breaker，否则同一排序值可能重复或漏页。

11.12 索引提示

强制索引：

SELECT *
FROM orders FORCE INDEX (idx_user_created)
WHERE user_id = 1001;

建议索引：

SELECT *
FROM orders USE INDEX (idx_user_created)
WHERE user_id = 1001;

忽略索引：

SELECT *
FROM orders IGNORE INDEX (idx_status_created)
WHERE user_id = 1001;

使用原则：

先修复统计信息和 SQL；
只作为临时规避手段；
必须写注释说明原因；
设置到期复查；
数据分布变化后可能失效；
团队要知道哪些 SQL 使用了强制提示。

11.13 查询改写流程

保留原始 SQL、参数和执行计划；
明确业务语义，不改结果；
找出扫描量和排序临时表来源；
消除隐式转换和列函数；
匹配联合索引前缀；
缩小时间和数据范围；
拆分 OR、深分页和巨型子查询；
评估覆盖索引；
对比返回行数和抽样结果；
记录上线指标和回滚方案。

本章小结

索引失效的常见原因是列被函数化、隐式类型转换、前导列缺失、字符集不一致、条件选择性差和排序顺序不匹配。处理时应先看执行计划和真实参数，再改写 SQL 或调整索引。索引提示只能临时救急，长期方案仍是统计信息、表结构和查询路径的治理。

思考题

WHERE DATE(created_at)=... 为什么难用索引？如何改写？
VARCHAR 列用数字比较有什么风险？
OR 和 IN 在索引使用上有什么差异？
为什么 <> 可能不走索引但仍是合理执行计划？
把一条生产慢 SQL 从函数条件、深分页、SELECT * 三个角度完整改写一次。