深度分页优化

面试高频考点：LIMIT 深分页问题原理、子查询优化、延迟关联优化、标记位/游标分页

一、分页查询基础

LIMIT 语法

-- 基础语法
SELECT * FROM table_name LIMIT offset, count;
SELECT * FROM table_name LIMIT count OFFSET offset;
 
-- 示例
SELECT * FROM user LIMIT 10;           -- 前 10 条
SELECT * FROM user LIMIT 10, 10;       -- 第 11-20 条
SELECT * FROM user LIMIT 10 OFFSET 10; -- 第 11-20 条

LIMIT 执行原理

-- 查询第 1000001-1000010 条数据
SELECT * FROM user ORDER BY id LIMIT 1000000, 10;

执行过程：

1. 读取前 1000000 条数据（全部读取）
2. 抛弃前 1000000 条
3. 取第 1000001-1000010 条返回

问题：偏移量越大，需要扫描和丢弃的数据越多，性能越差。

二、深度分页问题

性能测试

-- 创建测试表
CREATE TABLE `order` (
    `id` bigint PRIMARY KEY AUTO_INCREMENT,
    `user_id` int NOT NULL,
    `amount` decimal(10,2),
    `create_time` datetime,
    INDEX `idx_userid` (`user_id`),
    INDEX `idx_createtime` (`create_time`)
) ENGINE=InnoDB;
 
-- 插入 100 万条测试数据
-- ...
 
-- 分页性能对比
SELECT * FROM `order` ORDER BY id LIMIT 10;                    -- 0.001s
SELECT * FROM `order` ORDER BY id LIMIT 1000, 10;              -- 0.005s
SELECT * FROM `order` ORDER BY id LIMIT 10000, 10;             -- 0.015s
SELECT * FROM `order` ORDER BY id LIMIT 100000, 10;            -- 0.080s
SELECT * FROM `order` ORDER BY id LIMIT 1000000, 10;           -- 0.600s
SELECT * FROM `order` ORDER BY id LIMIT 10000000, 10;          -- 6.000s

结论：偏移量每增加 10 倍，查询时间大约增加 10 倍。

问题原因分析

EXPLAIN SELECT * FROM `order` ORDER BY id LIMIT 1000000, 10;

+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | order | NULL       | index | NULL          | PRIMARY | 8       | NULL | 1000010 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------+

分析：

type: index：全索引扫描
rows: 1000010：需要扫描 100 万+ 行
虽然使用了主键索引，但仍然需要扫描并抛弃前 100 万条数据

三、优化方案

方案一：子查询优化

原理：先通过子查询获取起始位置的主键，再关联查询数据。

-- 原始 SQL（慢）
SELECT * FROM `order` ORDER BY id LIMIT 1000000, 10;
 
-- 优化后 SQL
SELECT * FROM `order` 
WHERE id >= (SELECT id FROM `order` ORDER BY id LIMIT 1000000, 1)
ORDER BY id LIMIT 10;

性能对比：

原始 SQL:    0.600s
优化后 SQL:  0.150s
性能提升:    4 倍

执行计划分析：

EXPLAIN SELECT * FROM `order` 
WHERE id >= (SELECT id FROM `order` ORDER BY id LIMIT 1000000, 1)
ORDER BY id LIMIT 10;

+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | PRIMARY     | order | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL | 10      |   100.00 | Using where |
|  2 | SUBQUERY    | order | NULL       | index | NULL          | PRIMARY | 8       | NULL | 1000001 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+

优化原理：

子查询只查询主键 id，使用覆盖索引（Using index）
主查询通过 WHERE id >= ? 直接定位，只需扫描 10 行

注意事项：

子查询仍然需要扫描 offset 条记录
适合有序主键场景
如果排序字段不是主键，需要调整写法

方案二：延迟关联优化（推荐）

原理：先通过子查询获取 id 列表，再 JOIN 查询完整数据。

-- 原始 SQL（慢）
SELECT * FROM `order` ORDER BY id LIMIT 1000000, 10;
 
-- 延迟关联优化
SELECT o.* FROM `order` o
INNER JOIN (SELECT id FROM `order` ORDER BY id LIMIT 1000000, 10) t
ON o.id = t.id;

性能对比：

原始 SQL:    0.600s
优化后 SQL:  0.120s
性能提升:    5 倍

执行计划分析：

EXPLAIN SELECT o.* FROM `order` o
INNER JOIN (SELECT id FROM `order` ORDER BY id LIMIT 1000000, 10) t
ON o.id = t.id;

+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 1000010 |   100.00 | NULL        |
|  1 | PRIMARY     | o          | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | t.id | 1       |   100.00 | NULL        |
|  2 | DERIVED     | order      | NULL       | index  | NULL          | PRIMARY | 8       | NULL | 1000010 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+

优化原理：

子查询只查询 id，使用覆盖索引
JOIN 时通过主键快速定位，每次只需 1 次回表
子查询和主查询都可以利用索引优化

通用写法（适用于任意排序列）：

-- 按创建时间排序
SELECT o.* FROM `order` o
INNER JOIN (SELECT id FROM `order` ORDER BY create_time LIMIT 1000000, 10) t
ON o.id = t.id;
 
-- 如果 create_time 有索引
CREATE INDEX idx_createtime ON `order`(create_time);
-- 子查询会使用覆盖索引

方案三：标记位/游标分页（最优）

原理：记录上一页最后一条数据的 id，下次查询直接从该 id 开始。

-- 第一页
SELECT * FROM `order` ORDER BY id LIMIT 10;
-- 假设最后一条 id = 10
 
-- 第二页（从 id > 10 开始）
SELECT * FROM `order` WHERE id > 10 ORDER BY id LIMIT 10;
-- 假设最后一条 id = 20
 
-- 第 N 页（传入上一页最后的 id）
SELECT * FROM `order` WHERE id > ? ORDER BY id LIMIT 10;

性能对比：

原始 SQL:    0.600s
优化后 SQL:  0.001s
性能提升:    600 倍

执行计划：

EXPLAIN SELECT * FROM `order` WHERE id > 1000000 ORDER BY id LIMIT 10;

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | order | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL | 10   |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+

分析：

type: range：范围扫描
rows: 10：只扫描 10 行
无论翻到第几页，性能都是 O(1)

前端实现：

// 分页参数
let lastId = 0;
const pageSize = 10;
 
// 首次加载
async function loadFirstPage() {
    const res = await fetch(`/api/orders?limit=${pageSize}`);
    const data = await res.json();
    lastId = data[data.length - 1].id;
    return data;
}
 
// 加载下一页
async function loadNextPage() {
    const res = await fetch(`/api/orders?lastId=${lastId}&limit=${pageSize}`);
    const data = await res.json();
    if (data.length > 0) {
        lastId = data[data.length - 1].id;
    }
    return data;
}

后端实现：

@RestController
@RequestMapping("/api/orders")
public class OrderController {
    
    @GetMapping
    public List<Order> list(
            @RequestParam(required = false) Long lastId,
            @RequestParam(defaultValue = "10") int limit) {
        
        LambdaQueryWrapper<Order> wrapper = new LambdaQueryWrapper<>();
        if (lastId != null) {
            wrapper.gt(Order::getId, lastId);
        }
        wrapper.orderByAsc(Order::getId)
               .last("LIMIT " + limit);
        
        return orderService.list(wrapper);
    }
}

注意事项：

只能按主键或唯一索引排序
不支持跳页（无法直接跳到第 N 页）
如果排序字段有重复值，需要额外处理

方案四：复合标记位分页

当排序字段不是主键时，使用复合标记位：

-- 按 create_time 排序
-- 标记位：(create_time, id)
 
-- 第一页
SELECT * FROM `order` ORDER BY create_time, id LIMIT 10;
-- 记录最后一条：(create_time = '2024-01-01 10:00:00', id = 100)
 
-- 第二页
SELECT * FROM `order` 
WHERE (create_time > '2024-01-01 10:00:00')
   OR (create_time = '2024-01-01 10:00:00' AND id > 100)
ORDER BY create_time, id LIMIT 10;
 
-- 简化写法（MySQL 8.0+）
SELECT * FROM `order`
WHERE (create_time, id) > ('2024-01-01 10:00:00', 100)
ORDER BY create_time, id LIMIT 10;

建立联合索引：

CREATE INDEX idx_createtime_id ON `order`(create_time, id);

四、各方案对比

方案	性能	支持跳页	实现复杂度	适用场景
原始 LIMIT	差	是	简单	小数据量
子查询优化	中	是	中等	中等数据量
延迟关联	良	是	中等	大数据量，需要跳页
游标分页	优	否	简单	大数据量，无限滚动
复合游标	优	否	中等	大数据量，非主键排序

五、实战案例

案例 1：订单列表分页

-- 需求：订单列表，按创建时间倒序分页
 
-- 原始 SQL
SELECT * FROM `order` ORDER BY create_time DESC LIMIT 1000000, 10;
-- 耗时：0.8s
 
-- 方案一：子查询优化
SELECT * FROM `order`
WHERE create_time <= (
    SELECT create_time FROM `order` 
    ORDER BY create_time DESC LIMIT 1000000, 1
)
ORDER BY create_time DESC LIMIT 10;
-- 耗时：0.3s
 
-- 方案二：延迟关联（推荐）
SELECT o.* FROM `order` o
INNER JOIN (
    SELECT id FROM `order` 
    ORDER BY create_time DESC LIMIT 1000000, 10
) t ON o.id = t.id
ORDER BY create_time DESC;
-- 耗时：0.2s
 
-- 方案三：游标分页（最优，但需前端配合）
SELECT * FROM `order`
WHERE create_time < ? OR (create_time = ? AND id < ?)
ORDER BY create_time DESC, id DESC
LIMIT 10;
-- 耗时：0.001s

案例 2：用户动态流

// 微博/朋友圈风格的动态流
// 使用游标分页，因为用户只会往下刷，不会跳页
 
@GetMapping("/feed")
public List<Feed> getFeed(
        @RequestParam(required = false) Long lastId,
        @RequestParam(defaultValue = "20") int size) {
    
    return feedMapper.selectList(new LambdaQueryWrapper<Feed>()
        .gt(lastId != null, Feed::getId, lastId)
        .orderByDesc(Feed::getId)
        .last("LIMIT " + size));
}

案例 3：后台管理系统

// 后台管理系统需要支持跳页
// 使用延迟关联优化
 
@GetMapping("/admin/orders")
public PageResult<Order> listOrders(
        @RequestParam(defaultValue = "1") int page,
        @RequestParam(defaultValue = "10") int size) {
    
    int offset = (page - 1) * size;
    
    // 使用延迟关联
    List<Order> orders = orderMapper.selectByPage(offset, size);
    long total = orderMapper.selectCount(null);
    
    return new PageResult<>(orders, total);
}
 
// Mapper
@Select("""
    SELECT o.* FROM `order` o
    INNER JOIN (SELECT id FROM `order` ORDER BY id LIMIT #{offset}, #{size}) t
    ON o.id = t.id
    """)
List<Order> selectByPage(@Param("offset") int offset, @Param("size") int size);

六、面试要点

Q1: LIMIT 深分页为什么慢？

回答要点：

LIMIT offset, count 的执行过程是先扫描 offset + count 条数据
然后抛弃前 offset 条，只返回后 count 条
偏移量越大，需要扫描和丢弃的数据越多，性能越差

Q2: 深分页有哪些优化方案？

回答要点：

子查询优化：先查主键，再关联查询
延迟关联：先查 id 列表，再 JOIN 获取完整数据
游标分页：记录上一页最后的 id，用 WHERE id > ? 直接定位
游标分页性能最优，但不支持跳页

Q3: 延迟关联为什么比普通 LIMIT 快？

回答要点：

子查询只查询主键 id，可以使用覆盖索引，无需回表
子查询结果只有 count 条 id
通过主键 JOIN 时，每次只需一次主键查找
避免了扫描和抛弃大量数据行

Q4: 游标分页的优缺点？

回答要点：

优点：性能最优，无论第几页都是 O(1)
缺点：不支持跳页，只能顺序翻页
适用场景：无限滚动加载、动态流

Q5: 排序字段不是主键时如何优化？

回答要点：

建立联合索引 (排序字段, id)
使用复合标记位：(排序字段值, id)
WHERE (排序字段, id) > (上一页最后的值)
延迟关联仍然可用，子查询用排序字段索引

小结

LIMIT 深分页性能问题：偏移量越大越慢
子查询优化：先查主键再关联，减少回表
延迟关联：覆盖索引 + JOIN，推荐用于跳页场景
游标分页：性能最优，适合无限滚动场景
根据业务场景选择合适的优化方案

SQL 索引优化 JVM 调优