第 2 章 索引优化与失效场景面试精讲

📖 本篇简介：上一篇讲清了索引是什么，这一篇解决面试里更狠的问题——"索引为什么失效"与"SQL 怎么写才能让索引生效"。本篇用 16 道高频题覆盖：函数包裹、隐式类型转换、LIKE/OR/IN 边界、范围查询后的列失序、ORDER BY 与 filesort、深分页优化、ICP、前缀索引、统计信息过期与大表在线 DDL。学完本篇，你能现场分析一条慢 SQL 的索引问题并给出改写方案。前置知识：第 1 章索引基础；本系列示例基于 MySQL 8.0。

目录

• 1 索引失效全景地图
• 题目 1 索引失效的常见场景有哪些
• 题目 2 函数包裹索引列为什么失效
• 题目 3 隐式类型转换如何导致索引失效
• 题目 4 LIKE 模糊查询的索引边界是什么
• 题目 5 OR 连接条件什么时候导致索引失效
• 题目 6 联合索引跳过前导列会怎样
• 题目 7 范围查询之后的列为什么用不上
• 题目 8 ORDER BY 什么时候走索引什么时候排序
• 题目 9 不等值条件与 NOT IN 一定失效吗
• 题目 10 优化器为什么会主动放弃索引
• 题目 11 深分页 LIMIT 大偏移如何优化
• 题目 12 覆盖索引怎么设计才能免回表
• 题目 13 索引下推在实际优化中怎么用
• 题目 14 前缀索引有哪些代价与陷阱
• 题目 15 统计信息过期导致选错索引怎么办
• 题目 16 大表加索引怎么不锁表
• 复习清单
• 练习题

1 索引失效全景地图

先记住"索引失效"的本质：只要 SQL 的写法让优化器无法按索引的排序顺序做定位/跳点，索引就用不上。高频失效场景按发生位置分类：

| 位置 | 失效写法 | 原因本质 |

|---|---|---|

| 列上 | WHERE YEAR(created_at)=2026 | 函数包裹，破坏了列的原始值顺序 |

| 类型 | WHERE phone=13800138000（列是 VARCHAR） | 隐式转换，比较前先改类型 |

| 通配 | LIKE '%关键字' | 前缀未知，无法跳点 |

| 连接 | a=1 OR b=2（两列各自有索引） | 单索引无法同时满足（可用索引合并） |

| 前缀 | WHERE b=1（联合索引(a,b)） | 跳过前导列 |

| 范围 | a>1 AND b=2 | 范围后失序 |

| 代价 | 低选择性列、回表比例过高 | 优化器算过账，全扫更便宜 |

下面逐题拆解。

题目 1：索引失效的常见场景有哪些？

考点

• 系统性地列举失效场景；
• 区分"真失效"与"优化器不选"。

参考答案

标准清单（能说出 5 条以上就算过关）：

1. 函数/运算包裹索引列：WHERE LEFT(name,3)='张'；

2. 隐式类型转换：字符串列与数字比较；

3. LIKE 左模糊：LIKE '%x' / LIKE '%x%'；

4. OR 连接非索引列：WHERE a=1 OR c=2（c 无索引时整条退化）；

5. 联合索引跳过前导列；

6. 范围查询之后的其他列（定位失序，ICP 可部分缓解）；

7. 对索引列做算术/拼接：WHERE age+1=20；

8. NOT IN / != / NOT LIKE（值分布广时优化器常弃用）；

9. NULL 语义：IS NULL 在旧版本不好使（8.0 已优化），= NULL 永不成立；

10. 优化器主动放弃：选择性差、回表成本高、统计信息过期。

答题加分点：把"真失效"（写法导致无法定位）和"优化器不选"（能走但不划算）分开讲，面试官会另眼相看。

追问

• 问：失效场景这么多，怎么系统排查？答：EXPLAIN 看 type 是否 ALL、key 是否为 NULL，再按上表逐条对照 WHERE 写法。
• 问：有没有"失效但可以救"的场景？答：有——左模糊可用覆盖索引救；OR 可改写 UNION ALL；函数包裹可改成范围条件（见各题）。

题目 2：函数包裹索引列为什么失效？

考点

• B+ 树按"原始列值"排序；
• 函数结果不是索引里的键。

参考答案

• B+ 树索引里存的键是列的原始值，按原始值排序；
• WHERE YEAR(created_at)=2026 需要对每一行的函数结果做比较，无法用索引的排序做跳点，只能全索引扫描/全表扫描；
• 正确改写：把函数作用在常量侧，或改成范围条件：

-- ❌ 失效：函数在列上
EXPLAIN SELECT * FROM t_order WHERE YEAR(created_at) = 2026;
-- ✅ 改写：函数作用于常量
EXPLAIN SELECT * FROM t_order
WHERE created_at >= '2026-01-01 00:00:00' AND created_at < '2027-01-01 00:00:00';

• 同理：WHERE age + 1 = 20 改为 WHERE age = 19；WHERE LOWER(name)='abc' 尽量保证数据大小写一致后直接等值。

追问

• 问：8.0 有函数索引吗？答：有，8.0 支持表达式索引（如 KEY ((lower(name)))），但会增大写入成本，能用改写就用改写。
• 问：DATE_FORMAT(created_at,'%Y-%m') 能走索引吗？答：不能，同上；按月统计应改写为范围条件或建月度冗余列。

题目 3：隐式类型转换如何导致索引失效？

考点

• 列类型与常量类型不一致时 MySQL 的转换规则；
• 转换发生在列上还是常量上。

参考答案

• 当列是 VARCHAR、条件值是数字时（如 WHERE phone = 13800138000），MySQL 会把列转成数字再比较——转换包在列上，索引失效；
• 反过来列是 INT、条件值是字符串（WHERE id = '1'），转换作用在常量侧，索引仍然可用；
• 记忆规则：别让类型转换落在列上。

-- ❌ 失效：phone 是 VARCHAR，条件写数字
EXPLAIN SELECT * FROM t_user WHERE phone = 13800138000;
-- ✅ 有效：条件写成字符串
EXPLAIN SELECT * FROM t_user WHERE phone = '13800138000';
-- ✅ 有效：id 是 INT，常量是字符串（转换在常量侧）
EXPLAIN SELECT * FROM t_user WHERE id = '1';

追问

• 问：怎么发现隐式转换？答：EXPLAIN 里 type=ALL 且写法"看着没问题"时，用 EXPLAIN FORMAT=JSON 或对比 SHOW WARNINGS 能看到转换提示；也可用 EXPLAIN ANALYZE 看实际过滤行数。
• 问：WHERE phone = '13800138000' 和 WHERE phone = 13800138000 结果会一样吗？答：绝大多数情况一样，但数字写法会触发全表扫描，结果一样不等于性能一样。

题目 4：LIKE 模糊查询的索引边界是什么？

考点

• 右模糊可走索引（前缀匹配）；
• 左模糊/全模糊失效；
• 覆盖索引与 ICP 的补救。

参考答案

• LIKE 'abc%'：前缀确定，可走索引（相当于范围 a<=x<ab'）；
• LIKE '%abc'、LIKE '%abc%'：前缀未知，B+ 树无法跳点，通常全表扫描；
• 补救手段：

1. 查询列被覆盖索引覆盖时，%abc% 也可用索引做索引扫描（Extra=Using index），但仍是全索引扫描；

2. 加 ICP（8.0 默认）在索引内过滤，减少回表；

3. 真需要中间模糊搜索，用全文索引或搜索引擎。

-- ✅ 右模糊走索引（ref/range）
EXPLAIN SELECT * FROM t_user WHERE name LIKE '张%';
-- ⚠️ 全模糊：通常 ALL；若查询列被覆盖，可见 Using index（仍是全索引扫）
EXPLAIN SELECT id, name FROM t_user WHERE name LIKE '%三%';

追问

• 问：LIKE '张%' 和 LEFT(name,1)='张' 性能差异？答：前者走索引，后者函数包裹失效——同样语义，两种写法性能天差地别，面试常考。
• 问：覆盖索引能解决全模糊吗？答：能让它从"全表扫描"变成"全索引扫描"，索引比表小、顺序读更快，但本质还是扫描，量大了仍然慢。

题目 5：OR 连接条件什么时候导致索引失效？

考点

• OR 两侧都能命中索引时的索引合并；
• 一侧无索引时的退化。

参考答案

• WHERE a=1 OR b=2：如果 a、b 都有索引，MySQL 可用索引合并（Index Merge）取并集再去回表；
• 如果一侧没有索引（或优化器不合并），整条 SQL 退化为全表扫描——因为 OR 只要一侧成立就命中，优化器无法只走单索引；
• 改写建议：两侧都有索引且结果集大时，改成 UNION ALL 两段等值查询更可控。

-- ❌ 一侧无索引 → ALL
CREATE TABLE t (id BIGINT PRIMARY KEY, a INT, b INT, KEY idx_a (a));
EXPLAIN SELECT * FROM t WHERE a = 1 OR b = 2;   -- b 无索引，type=ALL
-- ✅ 改写为 UNION ALL（各走各的索引）
EXPLAIN SELECT * FROM t WHERE a = 1
UNION ALL
SELECT * FROM t WHERE b = 2;

追问

• 问：UNION ALL 和 OR 结果一定等价吗？答：等价（UNION ALL 不去重，OR 语义就是并集）；若担心 a=1 与 b=2 命中同一行重复返回，用 UNION（去重）但更慢。
• 问：什么时候 OR 比 UNION ALL 快？答：结果集很小时优化器合并更优；工程上默认"能改则改"，以 EXPLAIN 实测为准。

题目 6：联合索引跳过前导列会怎样？

考点

• 最左前缀的严格性；
• 优化器对条件顺序的重排能力。

参考答案

• 联合索引 (a,b,c) 要求查询从第一列开始连续命中；WHERE b=1 跳过 a，索引直接失效（全表扫描）；
• 注意：优化器会重排等值条件，WHERE b=1 AND a=2 会被重排成 a=2 AND b=1，依然走索引；
• 只有范围/函数等无法重排的情况才会真正卡住。

CREATE TABLE t_log (id BIGINT PRIMARY KEY, a INT, b INT, c INT, KEY idx_abc (a,b,c));
EXPLAIN SELECT * FROM t_log WHERE b = 2;               -- ❌ ALL
EXPLAIN SELECT * FROM t_log WHERE b = 2 AND a = 1;     -- ✅ 重排后走 a
EXPLAIN SELECT * FROM t_log WHERE c = 3 AND a = 1;     -- ✅ 用 a，c 由 ICP 过滤

追问

• 问：跳过前导列有没有办法救？答：8.0 的跳跃扫描（Skip Scan）在少数场景可让 WHERE b=1 走 (a,b) 索引（对 a 的每个值跳点扫描），但适用条件苛刻（a 基数小），别依赖。
• 问：联合索引第一列放什么？答：放等值高频列 + 选择性高的列，兼顾 ORDER BY。

题目 7：范围查询之后的列为什么用不上？

考点

• 索引有序性的中断；
• ICP 的"用不上定位但能过滤"。

参考答案

• 索引 (a,b) 先按 a 排、再按 b 排；WHERE a>1 AND b=2 时，a 是范围，b 的排序被打乱——无法用 b 做等值跳点，b 只能"看着过滤"；
• 8.0 的 ICP 可以在回表前用 b 过滤索引记录，减少回表，但不能改变"b 无法用于定位"的事实；
• 设计建议：范围列放联合索引最后面，等值列放前面。

-- a 范围 + b 等值：b 无法定位，ICP 辅助过滤
EXPLAIN SELECT * FROM t_log WHERE a > 100 AND b = 5;
-- Extra 通常出现 Using index condition
-- 若换成 WHERE a = 100 AND b > 5：a 等值定位，b 范围，性能更好

追问

• 问：a BETWEEN 1 AND 100 AND b=2 呢？答：BETWEEN 等价范围，同样中断；b 仅 ICP 过滤。
• 问：那 (a,b) 索引里 a、b 都是范围怎么写？答：两条范围并列时只能定位一个，另一个靠过滤；索引设计时尽量避免两个范围列同时出现在高频查询里。

题目 8：ORDER BY 什么时候走索引什么时候排序？

考点

• filesort 的触发；
• 索引天然有序如何省排序；
• 8.0 的优化排序算法。

参考答案

• 查询按索引顺序扫描时天然有序，ORDER BY 直接复用，Extra 无 filesort；
• 出现 Using filesort 表示 MySQL 自己额外排序（内存/磁盘排序，量大会很慢）；
• 让 ORDER BY 走索引的条件：排序列与联合索引顺序一致、方向一致（或都 DESC），且不受前面范围列中断；WHERE 等值列可作为联合索引前导，ORDER BY 列放后面。

CREATE TABLE t_order (id BIGINT PRIMARY KEY, uid INT, created_at DATETIME,
  KEY idx_uid_created (uid, created_at));

-- ✅ 等值 uid + ORDER BY created_at：完全走索引，无 filesort
EXPLAIN SELECT * FROM t_order WHERE uid = 1 ORDER BY created_at DESC;
-- ❌ 只按 created_at 排序（跳过 uid）：filesort
EXPLAIN SELECT * FROM t_order ORDER BY created_at DESC LIMIT 10;

追问

• 问：ORDER BY a DESC, b DESC 和索引 (a ASC, b ASC) 兼容吗？答：8.0 支持降序索引（(a DESC, b DESC)），方向一致才免排序；a ASC, b DESC 混合方向一般无法复用。
• 问：filesort 一定慢吗？答：数据量小时内存排序很快，量大了才明显；优化方向是加对联合索引而不是关掉排序。

题目 9：不等值条件与 NOT IN 一定失效吗？

考点

• != / <> / NOT IN 的可走索引场景；
• 优化器的分布估算。

参考答案

• 不是"一定失效"：!= 等反向条件，如果值分布使其命中比例小，优化器可能走范围扫描（range）；命中比例大则弃用索引；
• 一句话规律：反查条件 + 值占比高 = 全表扫描；值占比低（罕见值）才可能走索引；
• NOT IN 同理，且要注意 NULL 陷阱：NOT IN (1, NULL) 结果恒为空集。

-- 反查是否走索引，取决于命中比例
EXPLAIN SELECT * FROM t_user WHERE status != 2;      -- 若 status=2 极少，可能走 range
EXPLAIN SELECT * FROM t_user WHERE id NOT IN (1,2,3); -- 反查主键，常走 range

追问

• 问：业务上"查所有不等于某状态"怎么写更好？答：改成"查等于其他状态"的正向等值（status IN (0,1)），让优化器好估算；逻辑等价但计划更稳。
• 问：NOT EXISTS 和 NOT IN 谁更稳？答：子查询里 NOT EXISTS 通常更可控（避免 NULL 陷阱），量级大时更推荐。

题目 10：优化器为什么会主动放弃索引？

考点

• 回表成本模型；
• 选择性阈值经验；
• 统计信息的影响。

参考答案

• 走二级索引需要：索引定位 + 逐个回表（随机 IO）；全表扫描是顺序 IO。当预估回表行数占比高（经验值 > 15%~20%）时，优化器选全表扫描更便宜；
• 典型：status 列 99% 的行都是 1，WHERE status=1 必被弃用；
• 统计信息（cardinality）过期会放大误判；ANALYZE TABLE 刷新后再 EXPLAIN。

-- 观察优化器是否放弃索引
EXPLAIN SELECT * FROM t_order WHERE status = 1;
-- type=ALL、key=NULL 即为放弃；即使有 idx_status

-- 刷新统计信息后再看
ANALYZE TABLE t_order;
EXPLAIN SELECT * FROM t_order WHERE status = 1;

追问

• 问：怎么逼优化器用索引？答：FORCE INDEX 可强制，但通常结果更差（强制回表更多）；正确做法是缩小结果集（加条件、改索引结构）。
• 问：覆盖索引能让优化器不放弃吗？答：能——查询列都在索引里免回表，扫描索引的代价远低于全表，优化器更愿意选它。

题目 11：深分页 LIMIT 大偏移如何优化？

考点

• 大 OFFSET 的扫描浪费；
• 游标（keyset）分页；
• 延迟关联。

参考答案

• LIMIT 100000, 20 会扫描并丢弃前 10 万行，OFFSET 越大越慢；
• 方案一（推荐）游标分页：记住上一页最后一条的主键/排序键，用 WHERE id > 上页最后id ORDER BY id LIMIT 20，翻页是稳定的索引跳点；
• 方案二 延迟关联：先只查主键（小结果集），再 JOIN 回原表取整行，减少回表；
• 方案三：业务上用"加载更多"替代页码跳转，天然避免深翻页。

-- ❌ 深翻页：OFFSET 巨大，扫描大量行
SELECT * FROM t_order ORDER BY id LIMIT 100000, 20;
-- ✅ 游标分页：记住上一页最后一条 id = 99999
SELECT * FROM t_order WHERE id > 99999 ORDER BY id LIMIT 20;
-- ✅ 延迟关联：先取 id 再回表
SELECT t.* FROM t_order t
JOIN (SELECT id FROM t_order ORDER BY id LIMIT 100000, 20) tmp ON tmp.id = t.id;

追问

• 问：游标分页的缺点？答：无法任意跳页（只能顺序翻），且排序键必须唯一稳定（一般用主键）。
• 问：OFFSET 太大时为什么内存也会涨？答：排序缓存要装下前 10 万行候选；8.0 会尽量用临时文件，但依然慢。

题目 12：覆盖索引怎么设计才能免回表？

考点

• 覆盖索引 = 索引包含查询全部所需列；
• 设计取舍：别为覆盖塞太多列。

参考答案

• 让二级索引包含"SELECT 列 + WHERE 列 + ORDER BY 列"，查询即可 Extra=Using index 免回表；
• 适用场景：高频、结果集大、回表代价高的查询；
• 取舍：覆盖列越多索引越宽，写入越慢；只覆盖高频稳定查询，别把整表都塞进去（那等于复制了一份表）。

-- 高频查询：按 uid 查订单号和金额
CREATE TABLE t_order (id BIGINT PRIMARY KEY, uid INT, order_no VARCHAR(32),
  amount DECIMAL(10,2), created_at DATETIME,
  KEY idx_uid (uid, order_no, amount));     -- 覆盖 uid/order_no/amount

EXPLAIN SELECT order_no, amount FROM t_order WHERE uid = 100;
-- Extra=Using index：免回表

追问

• 问：覆盖索引能同时服务 GROUP BY / 排序吗？答：能，只要所需列和顺序都在索引里，GROUP BY 也可免临时表。
• 问：怎么发现"该建覆盖索引"？答：慢日志里高频 SQL 的 Extra 没有 Using index、且有大量回表（Handler_read_* 指标高）时考虑。

题目 13：索引下推在实际优化中怎么用？

考点

• ICP 的原理回顾；
• 与"改写 SQL"的关系；
• 识别 Extra=Using index condition。

参考答案

• ICP 让索引内的非定位条件在存储引擎层先过滤，减少回表行数；
• 实际用法：联合索引 (a,b)，WHERE a=1 AND b LIKE 'x%'——b 无法定位但 ICP 先过滤 b，回表量显著下降；
• 注意 ICP 是自动生效的（5.6+，8.0 默认），不用也不能手动开关；你要做的是设计好联合索引让更多过滤条件落在索引列上；
• 判断：EXPLAIN 出现 Using index condition。

-- ICP 典型：过滤列在索引内
EXPLAIN SELECT * FROM t_user WHERE name = '张' AND age BETWEEN 20 AND 30;
-- (name,age) 联合索引：name 定位，age 由 ICP 在索引内过滤，再回表

追问

• 问：ICP 和覆盖索引能同时出现吗？答：能，Extra 可能同时显示 Using index 与 Using index condition（少回表 + 覆盖）。
• 问：为什么用了 ICP 还是要回表？答：ICP 只过滤、不取数；要查的列不在索引里就必须回表。

题目 14：前缀索引有哪些代价与陷阱？

考点

• 前缀索引的建立方式；
• 无法覆盖、可能误判；
• 选择性平衡。

参考答案

• 前缀索引对长字符串列（如 URL、日志内容）建 KEY idx(col(10))，只索引前 N 字符，索引更小更省内存；
• 代价与陷阱：

1. 无法做覆盖索引：索引里存的不是完整值，Extra 永远不是 Using index；

2. 排序/分组不准：按前缀排序与全值排序可能不一致；

3. 可能误判：两条记录前缀相同但全值不同——只能作为过滤加速，不能保证唯一（唯一前缀索引更危险）；

• 建法：先统计不同前缀长度的选择性，选"选择性衰减可接受"的最短长度。

-- 统计前缀选择性：n=10 与 n=20 的 distinct 比
SELECT COUNT(DISTINCT LEFT(url, 10)) / COUNT(*) AS sel_10,
       COUNT(DISTINCT LEFT(url, 20)) / COUNT(*) AS sel_20
FROM t_access_log;

-- 选择性衰减小就建 10 前缀
CREATE TABLE t_access_log (
  id BIGINT PRIMARY KEY,
  url VARCHAR(500),
  KEY idx_url (url(10))
);

追问

• 问：什么时候该用前缀索引？答：列很长、查询只有等值/前缀匹配、且内存紧张时；能少建就少建。
• 问：8.0 对超长索引列还有限制吗？答：8.0 支持 innodb_large_prefix 已默认放宽（3072 字节），但长键依然占内存，前缀索引仍是省内存的有效手段。

题目 15：统计信息过期导致选错索引怎么办？

考点

• 统计信息与 cardinality；
• ANALYZE TABLE / 直方图；
• 执行计划缓存问题。

参考答案

• 优化器依据 information_schema.STATISTICS.cardinality 估算成本；频繁增删改会让统计信息失真，导致选错索引（明明有索引却全表扫，或选了个次优索引）；
• 处理手段：

1. ANALYZE TABLE t 刷新统计信息（8.0 也支持 ANALYZE TABLE ... UPDATE HISTOGRAM ON 列 建直方图辅助估算）；

2. 大表用 --analyze 定期维护或系统自动采样（innodb_stats_auto_recalc 默认开启，仍有滞后）；

3. 治本：索引设计合理 + 关键查询用 EXPLAIN 复核。

-- 刷新统计信息
ANALYZE TABLE t_order;
-- 查看当前估算基数
SELECT table_name, index_name, cardinality
FROM information_schema.STATISTICS
WHERE table_schema = 'demo' AND table_name = 't_order';

追问

• 问：执行计划会缓存吗？答：8.0 无 plan cache（5.7 有 query cache 已被移除），每次执行都重新估算，但连接内的 prepared statement 会复用计划，需注意参数化后计划漂移。
• 问：直方图什么时候有用？答：列值分布极度不均（如 99% 是 1、1% 是 2）时，直方图让优化器对 WHERE col=2 做出正确选择。

题目 16：大表加索引怎么不锁表？

考点

• 在线 DDL 与 ALGORITHM/ LOCK 子句；
• 8.0 的 INSTANT/INPLACE；
• 业务侧降噪手段。

参考答案

• MySQL 8.0 ALTER TABLE ... ADD INDEX 默认 ALGORITHM=INPLACE：只加索引时允许并发 DML（不影响读写，但 DDL 期间会有短暂元数据锁 MDL 获取）；
• 原理：INPLACE 重建二级索引时，原表可继续读，写入会被记录/合并，最后短暂锁表切换；
• 8.0 的 INSTANT 算法（8.0.12+）支持部分加列瞬间完成（只改元数据），加索引仍主要 INPLACE；
• 大表（亿级）实操注意：控制 DDL 时长（低峰执行）、监控复制延迟（若在主库执行，从库同步 DDL 也会长时间锁）、必要时用 pt-online-schema-change 分片拷贝。

-- 8.0 显式指定在线方式加索引（默认即 INPLACE）
ALTER TABLE t_big ADD INDEX idx_uid (uid), ALGORITHM=INPLACE, LOCK=NONE;
-- LOCK=NONE：允许并发读写；LOCK=SHARED：允许读不允许写；LOCK=EXCLUSIVE：锁全表

追问

• 问：为什么说"加索引也要短暂锁表"？答：DDL 结束时需要拿排他元数据锁做表定义切换，时间极短但存在；长事务会放大这个窗口。
• 问：pt-online-schema-change 原理？答：建影子表 → 触发器同步增量 → 分批拷贝 → 原子换表，DDL 期间业务无感，适合超大批次。

复习清单

| 题目 | 核心结论 | 易错点 |

|---|---|---|

| 失效场景 | 分类记忆：列上/类型/通配/连接/前缀/范围/代价 | 别漏"优化器主动放弃" |

| 函数包裹 | 函数作用在常量侧 | 别答"8.0 全支持函数索引" |

| 隐式转换 | 转换落在列上才失效 | 列是 INT 时常量字符串可用 |

| LIKE | 右模糊走索引 | %x% 需覆盖索引/全文索引 |

| OR | 两侧有索引才可能合并 | 一侧无索引整条退化 |

| 前导列 | 等值可重排，范围不可 | 别忽略优化器重排 |

| 范围后列 | 只能 ICP 过滤不能定位 | 范围列放联合索引最后 |

| ORDER BY | 索引顺序一致免 filesort | 混合方向 8.0 需降序索引 |

| 深分页 | 游标/延迟关联 | 别答"加大 LIMIT 缓冲" |

| 在线 DDL | INPLACE + LOCK=NONE | 元数据锁窗口仍存在 |

练习题

练 1：把 WHERE DATE(created_at) = '2026-10-07' AND status = 1 改写为可走索引的版本，并说明原理。

<details>

<summary>参考答案</summary>

WHERE created_at >= '2026-10-07 00:00:00'
  AND created_at < '2026-10-08 00:00:00'
  AND status = 1;

原理：范围条件落在列上可定位（配合 (status, created_at) 联合索引）；DATE() 包裹列必然失效。

</details>

练 2：线上一条 ORDER BY created_at DESC LIMIT 100000, 20 很慢，给出两种优化方案。

<details>

<summary>参考答案</summary>

方案一：游标分页 WHERE id < 上页最后id ORDER BY id DESC LIMIT 20（排序键改主键）；方案二：延迟关联，先 SELECT id ... LIMIT 100000,20 再 JOIN 回原表取整行。两者都可避免大 OFFSET 的全量扫描与回表。

</details>

下一篇预告：第 3 章 事务与ACID面试精讲