分页查询越往后越慢？延迟关联帮你搞定大偏移量难题-优快云博客

我们来深入探讨一下 “延迟关联”（Lazy Joining）这个数据库查询优化技术，以及它如何解决大偏移量分页查询的性能问题。

一、什么是延迟关联？

延迟关联并不是数据库的一种特殊语法或功能，而是一种查询优化的设计模式。

它的核心思想是：将 “获取数据” 和 “关联查询” 这两个步骤分开，先通过一个轻量级的查询筛选出目标数据的唯一标识符（ID），然后再用这些 ID 去关联其他表以获取完整的数据。

换句话说，它 “延迟” 了对大表或不必要字段的关联操作，直到最后一刻才进行，从而减少了中间过程中处理的数据量。

举个不恰当的生活例子：

想象一下你在一个巨大的图书馆里（数据库），需要找几本书（数据），并且需要知道每本书的详细信息，包括它的作者简介（关联表）。

普通查询（Eager Joining）：你把所有书都从书架上取下来（全表扫描或大范围扫描），然后逐一翻阅每本书的版权页和作者简介（Join），最后挑出你想要的几本。这个过程中，你搬运和处理了大量无关的书籍。
延迟关联（Lazy Joining）：你先去电脑查询系统（索引），根据书名、ISBN 等信息，快速找到了你想要的那几本书的位置编号（ID 列表）。然后，你根据这些位置编号，直接走到书架前，精准地取出那几本书（通过 ID 查询主表）。最后，如果你需要作者简介，再根据书的信息去查阅作者档案（关联查询）。这个过程效率极高，因为你只处理了必要的书籍。

二、大偏移量分页查询的性能问题

我们先看看传统的分页查询是怎么做的。假设我们有一个 orders 订单表和一个 users 用户表，我们想查询第 1000 页的订单数据，每页 10 条，并显示下单用户的信息。

传统分页查询（性能较差）：

SELECT 
    o.id, o.order_no, o.amount, o.create_time,
    u.id AS user_id, u.username, u.email
FROM 
    orders o
JOIN 
    users u ON o.user_id = u.id
ORDER BY 
    o.create_time DESC
LIMIT 10 OFFSET 9990; -- 大偏移量

为什么这个查询在偏移量（OFFSET）很大时会变慢？

全量数据处理：数据库执行时，需要先执行 JOIN 操作。这意味着它会将 orders 表和 users 表中满足 JOIN 条件的所有数据行都组合起来，形成一个可能非常巨大的临时结果集。
全量排序：接着，数据库需要对这个巨大的临时结果集按照 o.create_time DESC 进行全局排序。排序是一个成本很高的操作，尤其是在数据量巨大时。
低效的偏移：LIMIT ... OFFSET ... 的工作方式是，数据库在排好序的完整结果集中，跳过前面 OFFSET 条记录，然后取 LIMIT 条。当 OFFSET 很大时（比如 9990），数据库不得不扫描和排序前 10000 条记录，然后只返回最后 10 条，前面的 9990 条记录的处理都白费了。

简而言之，传统分页查询在偏移量增大时，会导致数据库进行大量的 I/O 和 CPU 运算，而且这些运算中的绝大部分都是为了 “跳过” 数据，而非 “获取” 数据。

三、延迟关联如何优化大偏移量分页？

延迟关联正是针对上述问题的解决方案。它通过先定位 ID，再关联数据的策略，巧妙地避开了处理大量无关数据的步骤。

使用延迟关联优化后的查询：

-- 步骤 1: 先从主表（或索引）中高效地筛选出目标页的ID列表
-- 这个子查询只涉及 orders 表，并且可以利用索引进行排序和分页
SELECT 
    o.id
FROM 
    orders o
ORDER BY 
    o.create_time DESC
LIMIT 10 OFFSET 9990;

-- 步骤 2: 使用上一步得到的ID列表，关联其他表获取完整数据
-- 这里的 IN 子查询可以很高效地利用主键索引
SELECT 
    o.id, o.order_no, o.amount, o.create_time,
    u.id AS user_id, u.username, u.email
FROM 
    orders o
JOIN 
    users u ON o.user_id = u.id
WHERE 
    o.id IN (
        SELECT id FROM orders ORDER BY create_time DESC LIMIT 10 OFFSET 9990
    )
ORDER BY 
    o.create_time DESC;

注意: 某些数据库版本对 IN (subquery) 的优化可能不如 JOIN。一个更稳妥且性能往往更好的写法是使用 JOIN 代替 IN：

sql
SELECT 
    o.id, o.order_no, o.amount, o.create_time,
    u.id AS user_id, u.username, u.email
FROM 
    (SELECT id FROM orders ORDER BY create_time DESC LIMIT 10 OFFSET 9990) AS page_ids
JOIN 
    orders o ON page_ids.id = o.id
JOIN 
    users u ON o.user_id = u.id
ORDER BY 
    o.create_time DESC;

优化原理分析：

聚焦主键，减少数据量：
- 优化后的查询将 LIMIT ... OFFSET ... 操作限制在了 orders 表的主键 id 上。子查询 SELECT id FROM orders ... LIMIT 10 OFFSET 9990 的目标非常明确：只找出第 1000 页数据的 ID。
- 这个子查询不涉及任何 JOIN，处理的数据量就是 orders 表本身。如果 orders 表的 create_time 字段上有索引，数据库甚至可以直接利用索引的有序性来完成排序和分页，避免了对整个 orders 表的扫描（这被称为 “索引覆盖扫描” 或 “书签查找”）。
避免全局排序大结果集：
- 关键的排序操作 ORDER BY o.create_time DESC 现在只在 orders 表上进行，而不是在 JOIN 后的大结果集上进行。orders 表的数据量通常远小于 JOIN 后的结果集（尤其是在多表关联时），排序的成本大大降低。
精准关联，效率极高：
- 一旦获取了目标页的 10 个 id，后续的 JOIN 操作就变得非常高效。数据库可以利用 orders.id（主键，天然有索引）和 users.id（主键）进行快速查找。
- WHERE o.id IN (...) 或 JOIN (subquery) 的执行计划通常是：先执行子查询得到 10 个 ID，然后逐一在 orders 表和 users 表中通过索引定位到对应的数据行。这个过程是 O (1) * 10 次，速度极快。

总结

对比项	传统分页查询	延迟关联优化
数据处理范围	`JOIN` 后的全量数据	先处理主表，再精准关联
排序成本	对巨大的 `JOIN` 结果集排序	仅对主表（或索引）排序
`OFFSET` 影响	随着 `OFFSET` 增大，性能急剧下降	`OFFSET` 只影响主表的小范围查询
核心思想	先关联，再分页	先分页（找 ID），再关联