如何优化MySQL中的深度分页查询_利用延迟关联技术-mysql教程-PHP中文网

延迟关联能减少回表次数，是因为它先用覆盖索引（如type+id联合索引）只查主键id，避免全量回表；再通过inner join仅对目标10个id精确回表，将回表次数从百万级降至10次。

如何优化mysql中的深度分页查询_利用延迟关联技术

为什么延迟关联能减少回表次数

MySQL执行 LIMIT 1000000, 10 时，即使有索引，也要先在索引树里“走”完前100万条记录对应的叶子节点，再逐条回表取完整行数据——而其中999990条都会被丢弃。延迟关联的核心思路是：**先用覆盖索引快速定位到需要的主键ID，再用这些ID去主表精确回表**，把回表次数从“上百万次”压到“最多10次”。

怎么写延迟关联SQL（含JOIN写法）

假设表 order_history 有主键 id，查询 type=8 的第100001–100010条记录：

SELECT t1.* FROM order_history t1
INNER JOIN (
  SELECT id FROM order_history WHERE type = 8 ORDER BY id LIMIT 100000, 10
) t2 ON t1.id = t2.id;

关键点：

t2 子查询只查 id，走的是覆盖索引（type + id 联合索引或 type 索引+主键），不回表
t1 只需对10个 id 做等值回表，I/O量极小
必须确保 ORDER BY 字段和 WHERE 条件能命中同一索引，否则子查询本身就会慢

延迟关联的硬性前提：索引必须支持覆盖

如果子查询里 SELECT id 无法走索引，整个优化就失效。常见踩坑点：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

没给 type 字段建索引 → 子查询全表扫描
索引是 (type) 单列，但 ORDER BY id 无法复用 → 触发 filesort，子查询变慢
正确做法：建联合索引 (type, id)，让 WHERE type = ? ORDER BY id 一步走完
若排序字段不是主键（比如按 created_at 分页），索引应为 (type, created_at, id)，末尾带上 id 才能覆盖

比延迟关联更轻量的替代方案：游标分页

延迟关联适合“跳转任意页”的场景（如后台管理页码输入框），但业务上更多是“下一页”连续翻页。这时直接用游标更稳：

SELECT * FROM order_history 
WHERE type = 8 AND id > 1234567 
ORDER BY id 
LIMIT 10;

上一页最后的 id 是1234567，下一页就从它之后开始。优势：

完全避免 LIMIT 偏移量，性能恒定
不依赖子查询、JOIN，语句简单，MySQL优化器不容易误判
唯一限制：不能跳页，且要求排序字段严格单调（主键最佳）

真正容易被忽略的是：游标值必须来自上一次查询结果的**实际返回值**，而不是“当前页码 × 每页条数”算出来的理论ID——因为中间可能有删改，ID不连续。