分页查询优化

面试官问："分页查询怎么优化？"

小陈说："加索引。"

面试官追问："SELECT * FROM orders ORDER BY id LIMIT 1000000, 10，怎么优化？"

小陈说："...加索引？"

面试官继续追问："加什么索引？优化后还需要扫描 100 万行吗？"

小张开始支支吾吾。

深度分页是 MySQL 中最常见的性能问题之一。LIMIT 1000000, 10 看起来只返回 10 条数据，但 MySQL 实际上扫描了 1000010 行。SELECT * 的危害在分页场景下被无限放大。

一、深度分页的问题 🔴

1.1 分页查询的执行过程

-- 深度分页查询
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

执行过程：

Step 1: 按 id 索引扫描前 1000010 行
        （每行都需要访问，因为是 SELECT *）

Step 2: 按 id 排序

Step 3: 取第 1000001~1000010 行返回

扫描行数：1,000,010 行
返回行数：10 行
效率：0.000999%

1.2 性能影响

页码	扫描行数	耗时
第 1 页 (LIMIT 0, 10)	10	0.1ms
第 1000 页 (LIMIT 10000, 10)	10010	10ms
第 100000 页 (LIMIT 1000000, 10)	1000010	1s
第 1000000 页 (LIMIT 10000000, 10)	10000010	10s

深度分页的性能随页码线性下降。

1.3 ❌ 错误示范

候选人原话："分页查询加个索引就行了。"

问题诊断：只说对了一半。索引可以加速排序，但如果仍然用 SELECT * 且 OFFSET 很大，仍然需要扫描大量行。

候选人原话 2："用 OFFSET 查询第 N 页，N 越小越快。"

问题诊断：这是正确的，但不是优化方案。优化目标是避免 OFFSET 带来的全量扫描。

【面试官心理】这道题我会问具体的 SQL 执行计划。如果候选人能说出"EXPLAIN 中 rows=1000010，说明扫描了 100 万行"，说明他真正理解了这个问题的本质。

二、游标分页（推荐方案）🔴

2.1 游标分页原理

-- ❌ 传统 OFFSET 分页
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

-- ✅ 游标分页
-- 第一页
SELECT * FROM orders ORDER BY id LIMIT 10;
-- 返回最后一行的 id = 100000

-- 下一页：记住上一页最后一条的 id
SELECT * FROM orders
WHERE id > 100000
ORDER BY id
LIMIT 10;

-- 再下一页
SELECT * FROM orders
WHERE id > 1000010
ORDER BY id
LIMIT 10;

核心思想：不扫描已读过的数据，用上一页最后一条的 id 作为起点。

2.2 游标分页的适用条件

-- 游标分页要求：
-- 1. 必须有排序字段（通常是主键或时间字段）
-- 2. 排序字段必须唯一（或者用多个字段组合唯一）
-- 3. 用户不能跳页

-- 不适用场景：
-- 用户输入页码直接跳转
-- 需要跳到第 N 页

2.3 多字段游标

-- 如果排序字段不唯一，用组合游标
-- 原始查询：ORDER BY created_at DESC, id DESC

-- 第一页
SELECT * FROM orders
WHERE created_at = '2024-01-01 12:00:00' AND id < 100000
   OR created_at < '2024-01-01 12:00:00'
ORDER BY created_at DESC, id DESC
LIMIT 10;

三、覆盖索引优化 🟡

3.1 覆盖索引减少回表

-- ❌ 慢查询：SELECT * 需要回表
SELECT * FROM orders WHERE status = 1 ORDER BY id LIMIT 1000000, 10;
-- 索引：idx_status_id(status, id)
-- 扫描：按索引扫 1000010 行，每行回表
-- 回表：1000010 次

-- ✅ 优化：只查索引列
SELECT id FROM orders WHERE status = 1 ORDER BY id LIMIT 1000000, 10;
-- 索引：idx_status_id(status, id) 覆盖
-- 扫描：按索引扫 1000010 行，但不需要回表
-- 只需要一次查询拿到 1000010 个 id

3.2 先查 ID 再关联

-- Step 1: 查询分页的 ID 列表
SELECT id FROM orders WHERE status = 1 ORDER BY id LIMIT 1000000, 10;
-- 返回 10 个 ID

-- Step 2: 用 ID 列表查询完整数据
SELECT * FROM orders WHERE id IN (1000001, 1000002, ..., 1000010);

注意：两步查询需要字段值稳定。如果中间有数据被删除，可能查不全。

四、生产避坑 🟡

4.1 禁止深度分页的 API

// API 层限制分页深度
public PageResult<Order> getOrders(int page, int pageSize) {
    if (page > 100) {
        throw new BusinessException("不支持超过 100 页的分页查询，请使用游标分页");
    }
    // ...
}

4.2 统计查询的限制

-- ❌ 禁止在大表上 COUNT(*) 全表统计
SELECT COUNT(*) FROM orders;
-- 扫描全表

-- ✅ 限制时间范围
SELECT COUNT(*) FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2024-02-01';
-- 范围扫描，可以利用索引

-- ✅ 使用近似计数
SELECT TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_NAME = 'orders';
-- 近似值，可能有 5% 误差

4.3 百万级分页的极端优化

-- 极端场景：必须支持深度分页
-- 方案：延迟关联 + 覆盖索引

-- Step 1: 用覆盖索引查 ID
SELECT id FROM orders
WHERE status = 1
ORDER BY id
LIMIT 1000000, 10;

-- Step 2: JOIN 回原表
SELECT o.*
FROM orders o
INNER JOIN (
    SELECT id FROM orders
    WHERE status = 1
    ORDER BY id
    LIMIT 1000000, 10
) t ON o.id = t.id;

【面试官心理】分页优化是 MySQL 实战中的高频考点。能说清楚"为什么深度分页慢"和"游标分页怎么实现"的候选人，说明他有实际优化经验。

级别	考察重点	期望回答
P5	问题认知	深度分页性能问题、原因分析
P6	优化方案	游标分页、覆盖索引优化
P7	工程实践	API 层限制、延迟关联、极端场景优化

#分页查询优化

#一、深度分页的问题 🔴

#1.1 分页查询的执行过程

#1.2 性能影响

#1.3 ❌ 错误示范

#二、游标分页（推荐方案）🔴

#2.1 游标分页原理

#2.2 游标分页的适用条件

#2.3 多字段游标

#三、覆盖索引优化 🟡

#3.1 覆盖索引减少回表

#3.2 先查 ID 再关联

#四、生产避坑 🟡

#4.1 禁止深度分页的 API

#4.2 统计查询的限制

#4.3 百万级分页的极端优化