引言：从“知其然”到“知其所以然”

大家好，我是BiggerBoy。在之前的文章《MySQL 深分页性能优化终极指南：告别慢查询的 5 大方案》中，我们总结了MySQL深分页的五大解决方案（延迟关联、游标分页、预计算、索引覆盖索引、业务妥协）。今天，我们聚焦其中最经典的延迟关联（Deferred Join），深入剖析它的原理、适用场景和性能极限，让你彻底掌握这个“低调的优化杀手”！

一、延迟关联的本质：少干活，多偷懒

1.1 传统分页的“苦力模式”

假设有一个订单表 orders（500万数据），执行以下深分页查询：

SELECT * FROM orders ORDER BY create_time DESC LIMIT 100000, 10;

MySQL的真实操作：

1. 1. 按索引排序：如果 create_time 有索引，先扫描索引树，找到前 100010 行的create_time值。
2. 2. 疯狂回表：根据索引中的主键ID，逐行回表查询主键索引，获取所有字段（SELECT *）。
3. 3. 丢弃数据：保留最后10行，丢弃前100000行。

性能瓶颈：

• • 回表次数 = Offset + Limit（100010次磁盘I/O）；
• • 数据排序和传输量大，内存和CPU双重压力。

1.2 延迟关联的“聪明模式”

优化后的SQL：

SELECT * FROM orders  
INNER JOIN (  
  SELECT id FROM orders  
  ORDER BY create_time DESC  
  LIMIT 100000, 10  
) AS tmp USING(id);  

分两步走：

1. 1. 子查询（轻量级）：

• • 只查主键id，利用覆盖索引（create_time + id）直接完成排序和筛选，无需回表。
• • 扫描索引树，快速定位到100010行，但只保留最后10个id。

• 2. 外层查询（精准打击）：
• • 用10个id回表查询，直接走主键索引，10次快速磁盘I/O。

本质：将 “先拿数据再筛选” 变为 “先筛选再拿数据”，避免无效数据搬运！

---

二、性能提升的关键：四个核心优化点

2.1 覆盖索引：拒绝回表

• • 理想索引：若子查询的字段（id、create_time）全部在某个二级索引中，MySQL可直接通过索引完成查询，无需访问主键索引。
• • 示例：联合索引 idx_create_time_id(create_time, id)。
• • 效果：子查询的EXPLAIN结果中会出现 Using index（索引覆盖）。

2.2 主键索引：精准定位

• • 外层查询通过id（主键）访问数据，主键索引的B+树高度最低，查询速度最快。
• • 主键的优势：天然有序、无碎片、无需二次查找。

2.3 减少排序：索引的有序性

• • 如果ORDER BY字段有索引，MySQL直接按索引顺序读取数据，无需内存排序（Filesort）。
• • 对比：无索引时，MySQL需将全部数据加载到内存排序，深分页场景下直接“内存爆炸”。

2.4 数据传输：化繁为简

• • 子查询仅传递10个id到外层查询，而非100010行完整数据，减少临时表和网络传输开销。

---

三、延迟关联的“性能天花板”

3.1 极限测试：100万Offset的对比

-- 传统分页（自杀式）  
SELECT * FROM orders ORDERBY create_time LIMIT 1000000, 10;  
-- 平均耗时：15.8秒  

-- 延迟关联（优雅版）  
SELECT * FROM orders  
INNERJOIN (  
SELECT id FROM orders  
ORDERBY create_time LIMIT 1000000, 10
) AS tmp USING(id);  
-- 平均耗时：0.12秒  

性能差距：130倍！

3.2 性能公式

• • 传统分页：耗时 ≈ (Offset + Limit) * 单次回表时间
• • 延迟关联：耗时 ≈ Limit * 单次回表时间
• • 结论：Offset越大，延迟关联的优势越明显！

四、延迟关联的“使用禁区”

4.1 不适用场景

1. 1. 无排序索引：若ORDER BY字段无索引，子查询需全表扫描 + 内存排序，性能可能更差。
2. 2. 非覆盖索引：若子查询字段不在索引中，仍需回表，优化效果打折扣。
3. 3. 超高频写入：索引页频繁分裂，影响子查询的索引扫描效率。

4.2 如何判断是否生效？

执行EXPLAIN：

• • 子查询的Extra列应有 Using index；
• • 外层查询的type应为 eq_ref（主键查询）。

五、实战进阶：复杂查询如何用延迟关联？

5.1 带WHERE条件的分页

SELECT *FROM orders  
INNERJOIN (  
SELECT id FROM orders  
WHERE user_id =123          -- 过滤条件  
ORDERBY create_time DESC
  LIMIT 100000, 10
) AS tmp USING(id);  

关键点：

• • 为user_id和create_time建立联合索引 idx_user_create_time(user_id, create_time, id)。
• • 确保过滤条件（user_id）和排序字段（create_time）都在索引中。

5.2 多字段排序

SELECT * FROM orders  
INNER JOIN (  
  SELECT id FROM orders  
  ORDER BY create_time DESC, id DESC  -- 多字段排序  
  LIMIT 100000, 10  
) AS tmp USING(id);  

索引设计：联合索引 idx_create_time_id(create_time, id)。

六、总结：延迟关联的哲学

• • 核心思想：化“大海捞针”为“按图索骥”。
• • 适用场景：深分页（Offset大）、排序字段有索引、查询列不全在索引中。
• • 终极建议：

1. 1. 索引设计：为排序和过滤字段建立联合索引，包含id实现覆盖；
2. 2. 监控Offset：Offset超过1000时优先考虑延迟关联；
3. 3. 业务妥协：禁止随意跳页（如仅允许“上一页/下一页”）。