MySQL 索引与 SQL 性能优化与深分页问题探讨
一、B+ 树、聚簇索引与回表
InnoDB 使用 B+ 树组织聚簇索引和二级索引。B+ 树的非叶子节点主要用于导航，叶子节点按键值排序，适合范围扫描。
聚簇索引：叶子节点保存整行记录；通常由主键组织。
二级索引：叶子节点保存索引列及主键值。
回表：利用二级索引获取主键后，再到聚簇索引读取所需的完整行。
覆盖索引：查询所需列都可以从一个索引直接获得，通常无须回表；不等于一定查询很快。
CREATE TABLE orders (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
status INT NOT NULL,
create_time DATETIME NOT NULL,
amount DECIMAL(10,2),
INDEX idx_user_status_time(user_id,status,create_time),
INDEX idx_create_time(create_time)
) ENGINE=InnoDB;
-- 覆盖索引：二级索引中也保存主键 id
SELECT id, user_id, status FROM orders WHERE user_id = 100;
-- 查询 amount 可能需要回表
SELECT * FROM orders WHERE user_id = 100;区别：通过主键查询直接取得完整行同样无须回表，但通常叫主键索引查询，不特意称作二级覆盖索引优化。
二、联合索引与最左前缀
索引 (a,b,c) 按 a、b、c 的顺序逐级排序；WHERE 中条件的书写顺序不影响优化器按索引定义匹配。
范围条件之后的索引列通常不能继续缩小连续扫描范围，却可能用于 ICP、覆盖索引等。不要笼统说“后面的列全部失效”。
三、ICP：索引条件下推
Index Condition Pushdown（ICP）：存储引擎在读取二级索引记录时，先用索引中已有的字段判断部分 WHERE 条件，过滤不匹配记录，降低回表次数。
-- 假定索引为 (a,b,c)
SELECT * FROM test WHERE a=1 AND c=3;如果 a=1 对应 100 条索引记录，只有 10 条满足 c=3：
不使用 ICP：可能先回表约 100 条，再判断 c。
使用 ICP：先在索引层过滤 c，可能只对约 10 条回表。
这是示意值；是否采用 ICP 看具体执行计划。MySQL 5.7 InnoDB 的 ICP 主要适用于需要取完整行的二级索引访问；覆盖索引通常无须 ICP。可在 EXPLAIN Extra 中看到 Using index condition。
四、EXPLAIN 执行计划
EXPLAIN SELECT * FROM orders WHERE user_id=100 AND status>1;MySQL 5.7 传统 EXPLAIN 的 12 个字段：
1. select_type 常见取值
SIMPLE 简单查询；PRIMARY 最外层查询；SUBQUERY 子查询；DEPENDENT SUBQUERY 依赖外层的子查询；DERIVED 派生表；UNION 后续 UNION 分支；DEPENDENT UNION 依赖外层的 UNION；UNION RESULT UNION 结果；MATERIALIZED 物化子查询；UNCACHEABLE SUBQUERY / UNCACHEABLE UNION 不可缓存的子查询/UNION 分支。
2. type 常见取值
index 和 Extra: Using index 不同：前者表示扫描方式，后者通常表示覆盖索引。index 即使覆盖，也可能扫描百万条记录。
3. Extra 常见取值
4. key_len / rows / filtered
假设索引 (a BIGINT NOT NULL,b INT NOT NULL,c DATETIME NOT NULL)：索引键前缀长度依次约为 8、12、17 字节。NULL 字段和 VARCHAR 要计算额外开销。key_len 只能辅助判断，不能单独证明所有列都用于范围定位。
rows 是估计检查数量，不等于返回行数；filtered 是估计过滤后留下的比例。如 rows=1000、filtered=20%，估计约 200 行继续参与后续步骤。
五、ORDER BY 和 Filesort
索引 (a,b,c) 中，c 只在 a、b 固定的分组内有序。
-- a、b 固定，通常可反向扫描索引，避免额外排序
SELECT * FROM test WHERE a=1 AND b=2 ORDER BY c DESC LIMIT 10;
-- b 未固定，c 跨 b 分组不是整体有序；通常需要 filesort
SELECT * FROM test WHERE a=1 ORDER BY c DESC LIMIT 10;
-- b 范围匹配多个值；跨分组 c 通常不全局有序
SELECT * FROM test WHERE a=1 AND b>2 ORDER BY c DESC LIMIT 10;能否避免 filesort，关键是索引扫描输出顺序能否满足 ORDER BY，而不是 ORDER BY 字段是否出现在索引里。
六、LIMIT 深分页
LIMIT 1000000,20：跳过前 100 万行，最多返回接下来的 20 行；必须配合稳定的 ORDER BY，否则翻页结果不可靠。
SELECT * FROM orders ORDER BY create_time DESC,id DESC LIMIT 1000000,20;慢的原因包括：需要处理大量前置记录；二级索引访问可能产生大量回表；排序路径不适用时可能 filesort。前一百万行不会传给 Java 再丢弃，而是在数据库端处理。
1. 覆盖索引 + 延迟关联
SELECT o.*
FROM orders o
JOIN (
SELECT id,create_time
FROM orders
ORDER BY create_time DESC,id DESC
LIMIT 1000000,20
) t ON o.id=t.id
ORDER BY t.create_time DESC,t.id DESC;利用 idx_create_time(create_time) 的有序性与 InnoDB 二级索引包含的主键：内层只扫描索引并取得 20 个 ID，外层再取得完整行。减少不必要的回表，但没有消除百万级 offset 扫描。
2. 游标分页
-- 第一页
SELECT * FROM orders ORDER BY id DESC LIMIT 20;
-- 上页末尾 id=981，下一页
SELECT * FROM orders WHERE id<981 ORDER BY id DESC LIMIT 20;游标可以利用主键范围直接继续查，不再从第一页跳过大量数据。适合连续翻页、无限滚动，不适合任意跳到第 5000 页。
按创建时间排序时，使用 (create_time,id) 复合游标，避免时间相同造成漏行：
SELECT * FROM orders
WHERE create_time < '2026-10-01 12:00:00'
OR (create_time = '2026-10-01 12:00:00' AND id < 981)
ORDER BY create_time DESC,id DESC LIMIT 20;使用场景：后台可跳页采用普通分页或延迟关联；消息流、日志流、批量扫描优先游标分页。要用 EXPLAIN 验证游标的条件能否被高效定位。
八、速记与避坑
覆盖索引一定快吗？ 不一定；可能仍然全索引扫描。
type=index 是什么？ 全索引扫描；与 Extra=Using index（覆盖索引）不是一回事。
ICP 解决什么？ 在二级索引层提前过滤，减少回表。
范围查询后面的字段完全失效吗？ 不准确；仍可参与 ICP / 覆盖索引等。
filesort 一定落盘吗？ 不一定，也可以在内存排序。
延迟关联和游标分页有何区别？ 前者减少回表，后者减少大 offset 扫描。
索引失效常见场景？ 索引列函数/表达式、
LIKE '%abc'、不满足联合索引前导列、隐式转换；但是否实际选索引仍由优化器决定。
核心总结： 范围定位减少扫描，ICP 减少回表，覆盖索引避免回表，ORDER BY 利用有序性避免额外排序；优化 LIMIT 时必须区分“减少回表”和“减少跳过的记录数”。