mysql如何进行分页查询？这几种方法及优化技巧你必须掌握

做后台管理系统或者前端列表页，几乎都绕不开分页。MySQL里实现分页最常用的就是LIMIT加OFFSET的写法，比如LIMIT 10 OFFSET 20或者简写的LIMIT 20, 10。这条语句看似简单，但很多人在数据量涨到百万级之后会发现：前几页秒开，翻到几万页之后接口响应从几十毫秒飙到好几秒。要真正用好分页查询，需要理解LIMIT的工作机制，掌握深分页优化的常用手段。

一、LIMIT分页的基本用法与工作原理

先看最基础的写法。假设有一张用户表t_user，我们要查询第3页的数据，每页10条，SQL如下：

-- 写法一：LIMIT offset, count
SELECT id, name, created_at FROM t_user ORDER BY id LIMIT 20, 10;

-- 写法二：LIMIT count OFFSET offset，语义更直观
SELECT id, name, created_at FROM t_user ORDER BY id LIMIT 10 OFFSET 20;

这两种写法完全等价，都是跳过前20条，返回第21到第30条。注意OFFSET是从0开始的，所以第3页（每页10条）对应的偏移量是20，计算公式为offset = (page - 1) * pageSize。

关键在于理解LIMIT背后发生了什么。当执行LIMIT 100000, 10时，MySQL并不是直接跳到第100001行，而是把前100010行都读取出来（如果在索引上遍历就是逐条回表），然后丢弃前100000行，只返回最后10行。也就是说，OFFSET越大，被浪费掉的扫描量越大，这就是深分页变慢的根本原因。扫描行数线性增长，响应时间也线性恶化，这是LIMIT分页先天无法回避的问题。

另外要注意，如果ORDER BY的列没有索引，MySQL还需要先对全表结果排序（可能是文件排序filesort），再截取分页片段，代价更高。所以无论用哪种分页方案，保证排序列上有合适的索引都是第一步。

二、深分页的四种优化方案

1. 子查询定位法：先找ID再回表

既然OFFSET慢在大量回表，可以先用覆盖索引只查主键，拿到目标起始ID后再关联回原表：

-- 优化前：100000次回表
SELECT id, name, created_at FROM t_user ORDER BY id LIMIT 100000, 10;

-- 优化后：子查询只在主键索引上定位，最后只回表10次
SELECT id, name, created_at FROM t_user
WHERE id >= (
    SELECT id FROM t_user ORDER BY id LIMIT 100000, 1
)
ORDER BY id LIMIT 10;

原理是子查询只扫描主键索引（覆盖索引，无需回表），定位到第100001条记录的主键值后，外层查询用WHERE id >=从该位置开始取10条。回表次数从十万级降到10次，提升非常明显。缺点是OFFSET部分的索引扫描依然存在，只是扫描代价大幅降低，极限深分页时仍会变慢。

2. 延迟关联：JOIN写法效果相同

SELECT t.id, t.name, t.created_at
FROM t_user t
INNER JOIN (
    SELECT id FROM t_user ORDER BY id LIMIT 100000, 10
) tmp ON t.id = tmp.id;

这和子查询定位法本质相同，都是利用覆盖索引减少回表。子查询在索引上完成分页截取，JOIN阶段只对最终的10条记录回表取字段。如果SELECT的列较多（比如文章表要取大字段content），延迟关联的收益会更明显。实际测试中，百万级表深分页通常能把耗时从秒级压到几十毫秒。

3. 覆盖索引：让分页完全走索引

如果查询需要的列都能放进一个联合索引，MySQL可以直接在索引上完成整条查询，完全避免回表：

-- 建联合索引，包含查询所需的所有列
ALTER TABLE t_user ADD INDEX idx_created_id_name (created_at, id, name);

-- 该查询全程走索引，不再回表
SELECT id, name, created_at FROM t_user
ORDER BY created_at, id LIMIT 100000, 10;

注意索引列的顺序要和ORDER BY一致，否则索引用不上排序还得额外filesort。这种方式的局限是索引不能包含太长的列（如TEXT大字段），且索引变宽会影响写入性能和空间占用，适合查询列较少的固定场景。

4. 游标分页（书签法）：从根本上消灭OFFSET

如果业务允许，最推荐的方式是不用OFFSET，而是记住上一页最后一条记录的位置，下一页直接从它之后取：

-- 第一页
SELECT id, name, created_at FROM t_user ORDER BY id DESC LIMIT 10;

-- 下一页：以上一页最后一条的id作为游标
SELECT id, name, created_at FROM t_user
WHERE id < #{lastId}
ORDER BY id DESC LIMIT 10;

这种写法每一页都是WHERE id < 某个值 LIMIT 10，走索引范围定位，无论翻到第几页耗时都恒定在毫秒级，彻底解决了深分页问题。抖音、微博这类信息流的下拉加载基本都是这个方案。

它的代价是：不能直接跳转到任意页，只能连续翻页；排序字段必须唯一且单调（通常是主键或加上次级排序字段保证唯一），否则可能漏数据或重复。对于普通的后台管理列表，用户确实有跳页需求时，通常把游标分页留给前端无限滚动场景，后台仍用延迟关联兜底。

三、分页方案的选择与常见坑

方案没有绝对的好坏，按场景选择即可。下面这张表总结了各方案的适用情况：

除了选型，还有几个常见的坑值得注意。第一，ORDER BY不加唯一字段可能导致分页数据重复或丢失，比如按created_at排序时同一秒有多条记录，不同页之间可能出现同一条记录。稳妥的写法是加上一个唯一列做次级排序：ORDER BY created_at, id。

第二，统计总条数的COUNT(*)在大表上本身就很慢，MyISAM有缓存尚可，InnoDB的COUNT需要扫描。如果分页组件只用来展示"上一页/下一页"，可以完全不查总数；如果必须显示总数，可以考虑单独维护计数字段、用Redis缓存，或者用EXPLAIN的估算行数做近似展示。

第三，记得用EXPLAIN验证执行计划，确认分页查询确实走了索引，关键看Extra列是否出现Using index（覆盖索引）以及rows估算值是否合理。如果发现filesort或者全表扫描，先检查排序列上的索引再谈其他优化。

总结一下：小数据量直接LIMIT就够了；数据量大且要跳页，用延迟关联或子查询定位；能改造产品形态接受连续翻页，就上游标分页，这是唯一能做到翻页性能与页码无关的方案。理解OFFSET的扫描机制，比记住任何一条优化SQL都更重要。

mysql分页查询LIMIT优化深分页修改时间：2026-09-04 13:14:40