1. 为什么分页查询会成为性能瓶颈
但凡做过几年业务开发的，几乎都绕不开一个场景：列表页翻到后面几页，接口响应时间从几十毫秒飙到几秒甚至十几秒，数据库 CPU 直接拉满。这个问题在数据量上百万行之后会变得极其明显，而罪魁祸首往往就是那句看起来人畜无害的LIMIT offset, size。
我最早遇到这个问题是在一个订单管理后台，单表数据量大概 800 万行，运营同事反馈说翻到第 50 页之后页面基本卡死。当时第一反应是加索引，但索引早就加好了，EXPLAIN一看，type=ALL，全表扫描，rows显示扫描了几百万行。那一刻我才真正理解分页查询的代价到底在哪里。
1.1 深分页的本质代价
LIMIT 1000000, 20这个语句，MySQL 的执行逻辑并不是"跳过前 100 万行然后取 20 行"，而是"先取出前 100 万零 20 行，然后丢弃前 100 万行，返回最后 20 行"。这意味着偏移量越大，需要扫描和丢弃的数据就越多，I/O 和 CPU 开销线性增长。
用生活化的类比：这就像你要从一本 1000 页的书里找到第 950 页的内容，但你的方法是从第 1 页开始一页一页翻，翻到第 950 页才停下来。书越厚，翻的时间越长。而实际上你完全可以直接翻到第 950 页，这就是优化要解决的核心问题。
1.2 哪些场景最容易踩坑
不是所有分页都会出问题。小表、浅分页、有覆盖索引的场景，性能都还能接受。真正容易出问题的是这几类：
- 单表数据量超过百万行，且分页深度经常超过几十页
- 排序字段没有索引，导致每次分页都要做 filesort
- SELECT 返回大量列，回表次数多，随机 I/O 严重
- 后台管理系统，用户习惯性点击最后一页或者跳页
- API 接口被爬虫或脚本遍历，offset 一路递增到几十万
我见过最夸张的一个案例，某接口被外部脚本从 offset=0 一直翻到 offset=5000000，直接把主库拖垮，整个业务线受影响。所以分页优化不只是"让页面快一点"，它直接关系到系统的稳定性。
1.3 优化的整体思路框架
分页优化的核心思路可以归纳为三条主线：
- 减少扫描行数：让数据库只扫描真正需要返回的那部分数据，而不是从头扫到尾
- 减少回表次数：尽量利用覆盖索引，避免每行都去主键索引里捞完整数据
- 改变分页方式：用游标（cursor）替代偏移量（offset），从根本上消除深分页问题
这三条主线对应着不同的技术手段，适用场景也不同。下面我会逐一拆解每种方案的原理、实现和取舍。
2. 核心优化方案逐一拆解
2.1 延迟关联：先用索引拿主键再回表
延迟关联（Deferred Join）是我最推荐的中等改造成本方案。它的核心思想是：先在索引上完成分页，拿到主键 ID，再用这些 ID 去回表取完整数据。
原始写法：
SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 20;优化后写法：
SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY created_at DESC LIMIT 1000000, 20 ) AS t ON o.id = t.id;为什么这样能快？因为子查询里的SELECT id如果走了覆盖索引（比如idx_created_at包含created_at和主键id），那么整个分页过程只在索引树上进行，不需要回表。索引树比数据行小得多，扫描 100 万行索引和扫描 100 万行完整数据的代价差距可能是十倍以上。
实测数据：在 800 万行的订单表上，原始语句耗时约 4.2 秒，延迟关联优化后降到 0.8 秒左右。提升明显，但注意它并没有从根本上消除扫描 100 万行的问题，只是让扫描变得更便宜。
注意：延迟关联的效果高度依赖索引设计。如果排序字段没有索引，子查询本身就要 filesort，优化效果会大打折扣。
2.2 游标分页：用 WHERE 替代 OFFSET
游标分页（Cursor-based Pagination）是解决深分页的终极方案。它不再使用OFFSET，而是用上一页最后一条记录的排序字段值作为起点。
-- 第一页 SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 20; -- 下一页（假设上一页最后一条的 created_at='2024-01-15 10:30:00', id=88888） SELECT * FROM orders WHERE (created_at, id) < ('2024-01-15 10:30:00', 88888) ORDER BY created_at DESC, id DESC LIMIT 20;这个写法的执行路径是：直接在索引上定位到('2024-01-15 10:30:00', 88888)这个位置，然后往后取 20 行。无论翻到第几页，扫描行数都是 20 行左右，性能恒定。
这里用到了 MySQL 的行值比较（Row Value Comparison），(a, b) < (x, y)等价于a < x OR (a = x AND b < y)。用行值比较的好处是能充分利用复合索引，而且写法简洁。
为什么必须带上id作为第二排序字段？因为created_at可能重复。如果只按created_at排序，当多条记录时间相同时，分页边界会不稳定，可能出现漏数据或重复数据。加上唯一的id作为 tie-breaker，排序就变成全序，分页结果才可靠。
游标分页的代价是：不支持跳页。用户不能直接从第 1 页跳到第 100 页，只能一页一页往后翻。这在移动端无限滚动的场景下完全没问题，但在后台管理系统里可能是个硬伤。
2.3 覆盖索引：让查询不回头
覆盖索引（Covering Index）是指一个索引包含了查询所需的所有列，这样数据库只需要读索引，不需要回表读数据行。
假设有这样一个查询：
SELECT id, user_id, amount, created_at FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 20;如果只建了idx_created_at(created_at)，那么每次都要回表取user_id、amount、status。但如果建一个复合索引：
ALTER TABLE orders ADD INDEX idx_status_created_cover(status, created_at, user_id, amount);这个索引包含了status（用于 WHERE）、created_at（用于 ORDER BY）、user_id和amount（用于 SELECT），再加上主键id（InnoDB 二级索引自动包含主键），整个查询就可以在索引上完成，完全不用回表。
覆盖索引的代价是索引体积变大，写入性能下降，占用更多磁盘空间。所以不能无脑把所有列都塞进索引，要根据实际查询模式来设计。
2.4 方案对比与选型建议
| 方案 | 改造成本 | 性能提升 | 支持跳页 | 适用场景 |
|---|---|---|---|---|
| 延迟关联 | 低 | 中高 | 支持 | 已有索引，不想改接口 |
| 游标分页 | 中 | 极高 | 不支持 | 无限滚动、API 分页 |
| 覆盖索引 | 低 | 中 | 支持 | 查询列少且固定 |
| 预计算/缓存 | 高 | 极高 | 支持 | 数据变化不频繁 |
选型的核心判断依据是：业务是否真的需要跳页。如果不需要，游标分页是首选；如果需要，延迟关联加覆盖索引组合使用。
3. 实操过程与关键细节
3.1 定位问题：先用 EXPLAIN 看清楚
优化之前必须先诊断。我习惯用这套流程：
-- 1. 查看执行计划 EXPLAIN SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 20; -- 2. 查看实际执行统计（MySQL 8.0.18+） EXPLAIN ANALYZE SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 20; -- 3. 查看慢查询日志 SHOW VARIABLES LIKE 'slow_query_log%';重点看几个字段：
type：如果是ALL，说明全表扫描，必须优化key：实际使用的索引，如果是NULL，说明没走索引rows：预估扫描行数，深分页时这个值会非常大Extra：出现Using filesort说明排序没走索引
EXPLAIN ANALYZE比普通EXPLAIN更有价值，因为它会实际执行查询并给出真实耗时。我一般会对比优化前后的actual time和rows两个指标。
3.2 索引设计的几个关键原则
索引设计是分页优化的地基。我总结了几个实战中反复验证的原则：
最左前缀原则：复合索引(a, b, c)能支持WHERE a=?、WHERE a=? AND b=?、WHERE a=? AND b=? AND c=?，但不支持WHERE b=?。所以字段顺序很关键，等值查询字段放前面，范围查询和排序字段放后面。
排序字段要放在索引末尾：WHERE status=1 ORDER BY created_at DESC对应的索引应该是(status, created_at)，而不是(created_at, status)。因为等值条件能快速定位，排序字段在最后才能保证有序。
区分度高的字段优先：如果一个字段只有几个不同值（比如性别、状态），放在索引最前面效果不好。但如果它经常作为等值查询条件，又必须放前面。这是个权衡，需要结合具体查询模式。
避免冗余索引：(a, b)和(a)是冗余的，因为前者已经能覆盖后者的功能。冗余索引浪费空间还拖慢写入。
3.3 游标分页的完整实现
以订单列表为例，完整实现如下：
-- 建表（简化版） CREATE TABLE orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_status_created_id (status, created_at DESC, id DESC) ) ENGINE=InnoDB; -- 第一页 SELECT id, user_id, amount, created_at FROM orders WHERE status = 1 ORDER BY created_at DESC, id DESC LIMIT 20; -- 后续页（cursor_created_at 和 cursor_id 来自上一页最后一条） SELECT id, user_id, amount, created_at FROM orders WHERE status = 1 AND (created_at, id) < ('2024-01-15 10:30:00', 88888) ORDER BY created_at DESC, id DESC LIMIT 20;接口层面，返回给前端的数据里要带上next_cursor：
{ "data": [...], "next_cursor": "2024-01-15 10:30:00_88888", "has_more": true }前端下次请求时把next_cursor原样传回来，后端解析出created_at和id拼进 SQL。
注意：cursor 要做编码处理，不能直接把时间戳和 ID 明文暴露。可以用 Base64 编码，或者签名防篡改。我一般用 Base64 加一个简单的 HMAC 签名，防止用户伪造 cursor 遍历数据。
3.4 延迟关联的实操细节
延迟关联的写法有个容易踩的坑：子查询里的ORDER BY和LIMIT必须和外面一致，否则结果会错乱。
-- 正确写法 SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 1000000, 20 ) AS t ON o.id = t.id ORDER BY o.created_at DESC;注意最外层的ORDER BY不能省。因为INNER JOIN之后行的顺序不保证和子查询一致，必须显式排序。但这次排序只针对 20 行数据，代价可以忽略。
还有一个细节：如果子查询里用了WHERE status = 1，那么外层也应该带上同样的条件，否则可能 JOIN 出不符合条件的数据。不过在这个写法里，子查询已经过滤了，外层 JOIN 只匹配这 20 个 ID，所以不会出问题。
3.5 参数调优与配置检查
除了 SQL 层面的优化，几个数据库配置也值得检查：
-- 查看排序缓冲区大小，太小会导致磁盘临时文件排序 SHOW VARIABLES LIKE 'sort_buffer_size'; -- 查看是否开启了慢查询日志 SHOW VARIABLES LIKE 'slow_query_log'; SHOW VARIABLES LIKE 'long_query_time'; -- 查看 InnoDB 缓冲池大小，越大越好（在内存允许范围内） SHOW VARIABLES LIKE 'innodb_buffer_pool_size';sort_buffer_size默认值通常是 256KB，对于大结果集排序可能不够。但也不能设太大，因为它是每个连接独占的，设太大会导致内存爆炸。我一般设在 2MB 到 8MB 之间，具体看并发量。
innodb_buffer_pool_size是最重要的参数之一，建议设为物理内存的 50% 到 70%。缓冲池越大，索引和数据越可能命中内存，分页查询的 I/O 开销就越小。
4. 常见问题与排查技巧实录
4.1 为什么加了索引还是慢
这是最常见的问题。加了索引但查询还是慢，通常有这几个原因：
索引没被用上：可能是字段类型不匹配（比如字符串字段用数字查询）、使用了函数（WHERE DATE(created_at) = '2024-01-15'）、或者隐式类型转换。用EXPLAIN看key字段确认。
回表次数太多：即使走了索引，如果 SELECT 的列不在索引里，每一行都要回表。深分页时回表 100 万次，代价巨大。这时候要么用覆盖索引，要么用延迟关联。
排序字段和索引顺序不一致：ORDER BY created_at DESC, id ASC和索引(created_at DESC, id DESC)不匹配，会导致 filesort。排序方向必须和索引定义完全一致。
统计信息过期：MySQL 的优化器依赖统计信息选择执行计划。如果统计信息过期，可能选错索引。可以用ANALYZE TABLE orders;更新统计信息。
4.2 深分页导致主从延迟加剧
这个问题比较隐蔽。深分页查询会扫描大量数据，产生大量的读 I/O，如果从库承担了这些查询，可能导致从库的 SQL 线程跟不上主库的 binlog，主从延迟增大。
解决办法是把深分页查询路由到专门的只读实例，或者用缓存挡住大部分请求。我一般会在接口层加一个限制：offset 超过某个阈值（比如 10000）就直接返回错误，引导用户用其他方式筛选数据。
4.3 常见问题速查表
| 现象 | 可能原因 | 排查方法 | 解决方案 |
|---|---|---|---|
| 翻页越翻越慢 | 深分页扫描行数线性增长 | EXPLAIN 看 rows | 游标分页或延迟关联 |
| 加了索引没效果 | 索引未命中或回表过多 | EXPLAIN 看 key 和 Extra | 调整索引或覆盖索引 |
| 排序慢 | filesort | Extra 出现 Using filesort | 排序字段加索引 |
| 结果不稳定 | 排序字段有重复值 | 检查排序字段唯一性 | 加 id 作为 tie-breaker |
| 主从延迟 | 从库读压力大 | 监控 Seconds_Behind_Master | 读写分离或加缓存 |
4.4 几个我踩过的坑
坑一：用LIMIT做总数统计。SELECT COUNT(*) FROM orders WHERE status=1在千万级表上可能耗时几秒。如果前端只需要显示"共 N 条"，可以考虑用近似值，或者维护一个计数器表，或者用SHOW TABLE STATUS的Rows字段（不精确但快）。
坑二：游标分页的 cursor 没做防篡改。有次上线后发现有人伪造 cursor 遍历全表，因为 cursor 就是明文的时间戳和 ID。后来加了 HMAC 签名才解决。
坑三：复合索引字段顺序搞反。把created_at放在status前面，导致WHERE status=1 ORDER BY created_at用不上索引。记住：等值条件在前，排序字段在后。
坑四：忽略了ORDER BY的方向。MySQL 8.0 之前不支持降序索引，ORDER BY created_at DESC在(created_at ASC)索引上会走反向扫描，性能略差。8.0 之后可以建(created_at DESC)索引，但要注意查询方向和索引方向一致。
4.5 监控与持续优化
优化不是一次性的工作。数据量在增长，查询模式在变化，今天有效的方案明天可能就不行了。我一般会做这几件事：
- 开启慢查询日志，设置
long_query_time=1，定期分析慢查询 - 用
performance_schema监控高频 SQL，找出 TOP 10 耗时语句 - 定期
ANALYZE TABLE更新统计信息 - 在接口层埋点，记录每个分页请求的 offset 和耗时，发现异常及时告警
这套组合拳下来，基本上能在问题爆发之前就发现苗头。
5. 不同数据量级的策略选择
分页优化没有银弹，不同数据量级、不同业务场景，最优解是不一样的。我按数据量分几个档位来说。
5.1 百万级以下：索引基本够用
单表百万行以下，只要索引设计合理，普通的LIMIT offset, size通常都能在几百毫秒内返回。这个阶段重点是把索引建对，别让查询走全表扫描。
我一般会检查这几个点：WHERE 条件字段有没有索引、ORDER BY 字段有没有索引、SELECT 的列能不能被覆盖。这三点做到，百万级数据的分页基本不会有大问题。
5.2 百万到千万级：延迟关联加游标
这个量级是深分页问题的高发区。我的建议是：能改游标就改游标，改不了就用延迟关联兜底。
具体做法是：新接口一律用游标分页，老接口如果改造成本高，先用延迟关联顶住。同时在前端做引导，比如把"跳页"功能改成"加载更多"，减少深分页请求。
这个阶段还要考虑加缓存。列表页的数据通常变化不频繁，可以用 Redis 缓存前几页的结果，TTL 设短一点（比如 30 秒），能挡掉大部分重复请求。
5.3 千万级以上：架构层面解决
单表超过千万行，光靠 SQL 优化已经不够了，需要从架构层面考虑：
分库分表：按时间或用户 ID 分片，每个分片的数据量控制在百万级，分页查询在单个分片内完成。但跨分片分页是个难题，通常需要引入中间件或者自己做归并排序。
冷热分离：把历史数据归档到冷存储，热表只保留最近几个月的数据。大部分分页查询只关心近期数据，冷数据很少被翻到。
搜索引擎：把列表查询交给 Elasticsearch 这类搜索引擎，它们对深分页有更好的支持（比如search_after参数）。MySQL 只负责事务性写入。
预计算：对于固定的列表页，可以定时预计算好结果存到缓存或单独的表中，查询时直接读预计算结果。
这个阶段的优化已经不是单纯的 SQL 问题了，需要结合业务特点做架构设计。我的经验是：先做冷热分离，成本最低效果最明显；再做分库分表，但要做好跨片查询的准备；搜索引擎是最后的选择，因为引入了额外的运维复杂度。
5.4 一个真实的优化案例复盘
某后台系统的订单列表，单表 1200 万行，深分页查询平均耗时 6 秒。优化过程分三步：
第一步，加复合索引(status, created_at DESC, id DESC)，耗时降到 3 秒。原因是原来只有(created_at)单列索引，WHERE status 条件用不上。
第二步，改成延迟关联，耗时降到 1.2 秒。子查询走覆盖索引，只扫描索引不回表。
第三步，接口改成游标分页，耗时稳定在 50 毫秒以内。同时前端把跳页改成加载更多，用户体验反而更好了。
这个案例说明：优化是渐进的，每一步都有收益，但最终解决问题还是要靠分页方式的根本改变。
6. 写在最后的一些个人体会
分页优化这件事，我做了这么多年，最大的体会是：不要等到出问题了才优化。很多团队都是在页面卡死、数据库告警之后才想起来看 SQL，这时候往往已经很被动了。
我的习惯是在设计阶段就把分页方案定下来。新项目一律用游标分页，老项目逐步改造。索引设计纳入 Code Review 流程，每一条涉及分页的 SQL 都要过一遍 EXPLAIN。
还有一个容易被忽略的点：分页优化不只是后端的事。前端的交互设计直接影响分页模式。如果前端坚持要"跳页"功能，后端就只能用 offset 分页，优化空间受限。所以推动游标分页的时候，一定要拉上前端和产品一起聊，把技术约束翻译成产品语言，大家一起找平衡点。
最后分享一个小技巧：在接口层加一个 offset 上限，比如超过 10000 就返回错误提示"请使用筛选条件缩小范围"。这个简单的限制能挡掉 90% 的恶意深分页请求，成本极低，效果立竿见影。我几乎在每个项目里都会加这个保护。
