前言

常规 LIMIT offset,size 在 offset 很大时（如 LIMIT 100000,10）查询极慢，全表扫描大量数据。本文讲解原生分页缺陷、3 套优化方案、实战 SQL。

---

一、常规分页写法（小数据可用，大数据卡顿）

-- 第1页：0偏移
SELECT * FROM `order` ORDER BY order_id DESC LIMIT 0,10;
-- 第10000页，偏移巨大，性能极差
SELECT * FROM `order` ORDER BY order_id DESC LIMIT 99990,10;

卡顿原因

MySQL 需要先扫描前 99990 条数据，丢弃后再取 10 条，IO 开销巨大。

---

二、优化方案 1：主键索引分页（推荐，业务首选）

利用自增主键有序，通过 WHERE 过滤缩小扫描范围，无需大量偏移。

-- 第一页
SELECT * FROM `order` ORDER BY order_id DESC LIMIT 10;

-- 下一页：传递上一页最大id
SELECT * FROM `order` WHERE order_id < 9990 ORDER BY order_id DESC LIMIT 10;

优势：直接通过主键索引定位起始位置，扫描行数极少，百万级数据分页无压力。 限制：仅适用于按主键排序分页，无法跳页（只能上一页 / 下一页）。

---

三、优化方案 2：子查询偏移优化（支持跳页）

先通过子查询只查主键，再关联查完整数据，减少回表开销：

SELECT o.* FROM `order` o
INNER JOIN (
    SELECT order_id FROM `order` ORDER BY order_id DESC LIMIT 99990,10
) t ON o.order_id = t.order_id;

原理：子查询仅走索引读取 id，不读取整行数据，大幅降低 IO。

---

四、优化方案 3：覆盖索引分页（极致性能）

查询字段全部建立索引，避免回表，适合列表仅展示少量字段：

-- 建立复合索引 idx_id_status(order_id,order_no,total_amount)
SELECT order_id,order_no,total_amount 
FROM `order` 
ORDER BY order_id DESC LIMIT 99990,10;

---

五、分页开发规范与避坑

1. 禁止使用 SELECT * 分页，只查询展示所需字段；
2. 分页排序字段必须建立索引，否则触发 Using filesort；
3. 前端尽量不支持跳大页码，仅提供上一页、下一页，使用主键分页；
4. 超过十万级偏移，禁止直接 LIMIT offset,size；
5. 超大数据量报表，使用游标分页或定时导出，不在线分页查询。

---

六、分页语法总结

• 千条以内小数据：普通 LIMIT 无压力；
• 万～十万级：子查询分页优化；
• 百万级、滚动列表：主键 WHERE 分页（上 / 下一页）；
• 报表导出：禁止分页，采用分批游标读取。