我刚工作的时候有个列表页做了分页前两页秒开翻到 100 页就卡死了。用户投诉说“你们这破网站翻页翻到第 100 页就转圈圈”DBA 帮我一看 SQLSELECT * FROM orders ORDER BY id LIMIT 1000000, 10;—— 扫描了 1000010 行然后丢弃前 1000000 行只返回 10 行。今天咱们就来聊聊 MySQL 分页查询的优化看完这篇你就能让列表页飞起来。传统分页的问题慢在哪里-- 第 1 页很快扫描 10 行SELECT*FROMordersORDERBYidLIMIT0,10;-- 第 1000 页开始变慢扫描 10000 行SELECT*FROMordersORDERBYidLIMIT10000,10;-- 第 100000 页慢得要命扫描 1000010 行SELECT*FROMordersORDERBYidLIMIT1000000,10;问题LIMIT 1000000, 10会扫描1000000 10 1000010行然后丢弃前 1000000 行只返回最后 10 行扫描的行数越多性能越差验证一下-- 看执行计划EXPLAINSELECT*FROMordersORDERBYidLIMIT1000000,10;输出------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------ | 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 20000000 | | ------------------------------------------------------------------------------------问题rows 20000000扫描全表type ALL全表扫描。优化方案 1用主键索引覆盖延迟关联思路先查主键 ID覆盖索引不需要回表再用 ID 关联查完整数据。优化前-- 扫描 1000010 行丢弃前 1000000 行SELECT*FROMordersORDERBYidLIMIT1000000,10;优化后-- 先查 ID覆盖索引扫描 10 行SELECTidFROMordersORDERBYidLIMIT1000000,10;-- 再用 ID 关联查完整数据只查 10 行SELECT*FROMorders aJOIN(SELECTidFROMordersORDERBYidLIMIT1000000,10)bONa.idb.id;为什么快子查询SELECT id FROM orders ORDER BY id LIMIT 1000000, 10是覆盖索引只查主键 ID不需要回表性能很好外层查询用JOIN只查 10 行完整数据回表 10 次验证一下EXPLAINSELECT*FROMorders aJOIN(SELECTidFROMordersORDERBYidLIMIT1000000,10)bONa.idb.id;输出---------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---------------------------------------------------------------------------------------------- | 1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | b.id | 1 | | | 2 | DERIVED | orders | index | NULL | PRIMARY | 4 | NULL | 1000010 | Using index | ----------------------------------------------------------------------------------------------优化效果derived2子查询的Extra Using index覆盖索引不需要回表外层查询的type eq_ref主键关联只查 1 行实际执行时间从 5 秒降到 0.1 秒50 倍提升优化方案 2用游标分页推荐思路记住上一页的最后一条记录的 ID下一页从这个 ID 开始查。优化前-- 第 100000 页扫描 1000010 行SELECT*FROMordersORDERBYidLIMIT1000000,10;优化后-- 第 1 页SELECT*FROMordersORDERBYidLIMIT10;-- 假设上一页最后一条记录的 id 1000000-- 第 100001 页只扫描 10 行SELECT*FROMordersWHEREid1000000ORDERBYidLIMIT10;为什么快WHERE id 1000000是范围查询走主键索引LIMIT 10只返回 10 行不管翻到第几页都是扫描 10 行性能恒定验证一下EXPLAINSELECT*FROMordersWHEREid1000000ORDERBYidLIMIT10;输出---------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders | range | PRIMARY | PRIMARY | 4 | NULL | 10 | Using where | ----------------------------------------------------------------------------------------------优化效果type range范围查询走主键索引rows 10只扫描 10 行实际执行时间从 5 秒降到 0.001 秒5000 倍提升缺点问题如果 ID 不连续比如删除了某些记录会漏数据。解决方案用创建时间代替 ID如果创建时间是递增的。-- 假设上一页最后一条记录的 created_at 2024-01-15 10:30:00SELECT*FROMordersWHEREcreated_at2024-01-15 10:30:00ORDERBYcreated_atLIMIT10;优化方案 3用 BETWEEN适合翻页不多的情况思路如果知道每一页的 ID 范围用BETWEEN代替LIMIT。优化前SELECT*FROMordersORDERBYidLIMIT1000000,10;优化后-- 假设第 100001 页的 ID 范围是 1000001 ~ 1000010SELECT*FROMordersWHEREidBETWEEN1000001AND1000010;为什么快BETWEEN是范围查询走主键索引只扫描 10 行缺点问题需要知道每一页的 ID 范围不适合动态翻页比如用户随便跳到第 100001 页。适用场景翻页不多比如最多翻 100 页或者前端能记住每一页的 ID 范围。优化方案 4用子查询类似延迟关联思路先查到起始 ID再用WHERE id 起始 ID限制范围。优化前SELECT*FROMordersORDERBYidLIMIT1000000,10;优化后-- 先查起始 IDSELECTidFROMordersORDERBYidLIMIT1000000,1;-- 再用 WHERE id 起始 ID 限制范围SELECT*FROMordersWHEREid(SELECTidFROMordersORDERBYidLIMIT1000000,1)ORDERBYidLIMIT10;为什么快子查询是覆盖索引只查主键 ID外层查询用WHERE id 起始 ID走主键索引只扫描 10 行实战优化一个慢分页假设有个订单表分页查询很慢SELECT*FROMordersORDERBYcreated_atDESCLIMIT1000000,10;第 1 步看执行计划EXPLAINSELECT*FROMordersORDERBYcreated_atDESCLIMIT1000000,10;输出--------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | --------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 20000000 | Using filesort | ---------------------------------------------------------------------------------------------问题type ALL全表扫描rows 20000000扫描全表Extra Using filesort文件排序第 2 步给 created_at 加索引CREATEINDEXidx_created_atONorders(created_atDESC);再看执行计划EXPLAINSELECT*FROMordersORDERBYcreated_atDESCLIMIT1000000,10;输出------------------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------------------ | 1 | SIMPLE | orders | index | NULL | idx_created_at | 5 | NULL | 1000010 | | ------------------------------------------------------------------------------------------------优化效果type index索引扫描rows 1000010扫描 1000010 行比全表扫描好点Extra里没有Using filesort了因为索引是有序的但还是慢因为扫描了 1000010 行。第 3 步用游标分页-- 假设上一页最后一条记录的 created_at 2024-01-15 10:30:00SELECT*FROMordersWHEREcreated_at2024-01-15 10:30:00ORDERBYcreated_atDESCLIMIT10;再看执行计划EXPLAINSELECT*FROMordersWHEREcreated_at2024-01-15 10:30:00ORDERBYcreated_atDESCLIMIT10;输出------------------------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------------------------ | 1 | SIMPLE | orders | range | idx_created_at | idx_created_at | 5 | NULL | 10 | Using where | ------------------------------------------------------------------------------------------------------优化效果type range范围查询rows 10只扫描 10 行实际执行时间从 5 秒降到 0.001 秒5000 倍提升实战建议1. 优先用游标分页如果业务允许比如移动端下拉加载更多优先用游标分页WHERE id 上一页最后一条 ID。好处性能最好不管翻到第几页都是扫描固定行数。2. 如果必须用传统分页用延迟关联如果业务必须用传统分页比如 PC 端翻页用户可能跳到第 100001 页用延迟关联优化。SELECT*FROMorders aJOIN(SELECTidFROMordersORDERBYidLIMIT1000000,10)bONa.idb.id;3. 给 ORDER BY 字段加索引如果 ORDER BY 没走索引会导致Using filesort性能很差。建议给ORDER BY字段加索引或者让ORDER BY用上联合索引的后缀。4. 限制最大翻页数如果表有 1 亿条数据用户翻到第 10000001 页性能还是会很差。建议限制最大翻页数比如最多翻 1000 页或者强制用户用搜索代替翻页。-- 如果 offset 10000报错或者重定向到搜索页if(offset10000){return请用搜索功能;}## 总结 - 传统分页LIMIToffset,size的问题是扫描offsetsize行然后丢弃前offset行性能极差 - - 优化方案 1**延迟关联**先查 ID再关联 - - 优化方案 2**游标分页**推荐用WHEREid上一页最后一条 ID限制范围--优化方案3**BETWEEN**适合翻页不多的情况--优化方案4**子查询**类似延迟关联--实战建议优先用游标分页如果必须用传统分页就用延迟关联给ORDERBY字段加索引限制最大翻页数 如果你能把这几种分页优化方案讲清楚面试官绝对觉得你有实战经验。---**实战代码都在我本地跑过你可以放心复制。**如果有问题欢迎评论区交流
MySQL 分页查询优化
发布时间：2026/5/24 1:05:50
我刚工作的时候有个列表页做了分页前两页秒开翻到 100 页就卡死了。用户投诉说“你们这破网站翻页翻到第 100 页就转圈圈”DBA 帮我一看 SQLSELECT * FROM orders ORDER BY id LIMIT 1000000, 10;—— 扫描了 1000010 行然后丢弃前 1000000 行只返回 10 行。今天咱们就来聊聊 MySQL 分页查询的优化看完这篇你就能让列表页飞起来。传统分页的问题慢在哪里-- 第 1 页很快扫描 10 行SELECT*FROMordersORDERBYidLIMIT0,10;-- 第 1000 页开始变慢扫描 10000 行SELECT*FROMordersORDERBYidLIMIT10000,10;-- 第 100000 页慢得要命扫描 1000010 行SELECT*FROMordersORDERBYidLIMIT1000000,10;问题LIMIT 1000000, 10会扫描1000000 10 1000010行然后丢弃前 1000000 行只返回最后 10 行扫描的行数越多性能越差验证一下-- 看执行计划EXPLAINSELECT*FROMordersORDERBYidLIMIT1000000,10;输出------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------ | 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 20000000 | | ------------------------------------------------------------------------------------问题rows 20000000扫描全表type ALL全表扫描。优化方案 1用主键索引覆盖延迟关联思路先查主键 ID覆盖索引不需要回表再用 ID 关联查完整数据。优化前-- 扫描 1000010 行丢弃前 1000000 行SELECT*FROMordersORDERBYidLIMIT1000000,10;优化后-- 先查 ID覆盖索引扫描 10 行SELECTidFROMordersORDERBYidLIMIT1000000,10;-- 再用 ID 关联查完整数据只查 10 行SELECT*FROMorders aJOIN(SELECTidFROMordersORDERBYidLIMIT1000000,10)bONa.idb.id;为什么快子查询SELECT id FROM orders ORDER BY id LIMIT 1000000, 10是覆盖索引只查主键 ID不需要回表性能很好外层查询用JOIN只查 10 行完整数据回表 10 次验证一下EXPLAINSELECT*FROMorders aJOIN(SELECTidFROMordersORDERBYidLIMIT1000000,10)bONa.idb.id;输出---------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---------------------------------------------------------------------------------------------- | 1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | b.id | 1 | | | 2 | DERIVED | orders | index | NULL | PRIMARY | 4 | NULL | 1000010 | Using index | ----------------------------------------------------------------------------------------------优化效果derived2子查询的Extra Using index覆盖索引不需要回表外层查询的type eq_ref主键关联只查 1 行实际执行时间从 5 秒降到 0.1 秒50 倍提升优化方案 2用游标分页推荐思路记住上一页的最后一条记录的 ID下一页从这个 ID 开始查。优化前-- 第 100000 页扫描 1000010 行SELECT*FROMordersORDERBYidLIMIT1000000,10;优化后-- 第 1 页SELECT*FROMordersORDERBYidLIMIT10;-- 假设上一页最后一条记录的 id 1000000-- 第 100001 页只扫描 10 行SELECT*FROMordersWHEREid1000000ORDERBYidLIMIT10;为什么快WHERE id 1000000是范围查询走主键索引LIMIT 10只返回 10 行不管翻到第几页都是扫描 10 行性能恒定验证一下EXPLAINSELECT*FROMordersWHEREid1000000ORDERBYidLIMIT10;输出---------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders | range | PRIMARY | PRIMARY | 4 | NULL | 10 | Using where | ----------------------------------------------------------------------------------------------优化效果type range范围查询走主键索引rows 10只扫描 10 行实际执行时间从 5 秒降到 0.001 秒5000 倍提升缺点问题如果 ID 不连续比如删除了某些记录会漏数据。解决方案用创建时间代替 ID如果创建时间是递增的。-- 假设上一页最后一条记录的 created_at 2024-01-15 10:30:00SELECT*FROMordersWHEREcreated_at2024-01-15 10:30:00ORDERBYcreated_atLIMIT10;优化方案 3用 BETWEEN适合翻页不多的情况思路如果知道每一页的 ID 范围用BETWEEN代替LIMIT。优化前SELECT*FROMordersORDERBYidLIMIT1000000,10;优化后-- 假设第 100001 页的 ID 范围是 1000001 ~ 1000010SELECT*FROMordersWHEREidBETWEEN1000001AND1000010;为什么快BETWEEN是范围查询走主键索引只扫描 10 行缺点问题需要知道每一页的 ID 范围不适合动态翻页比如用户随便跳到第 100001 页。适用场景翻页不多比如最多翻 100 页或者前端能记住每一页的 ID 范围。优化方案 4用子查询类似延迟关联思路先查到起始 ID再用WHERE id 起始 ID限制范围。优化前SELECT*FROMordersORDERBYidLIMIT1000000,10;优化后-- 先查起始 IDSELECTidFROMordersORDERBYidLIMIT1000000,1;-- 再用 WHERE id 起始 ID 限制范围SELECT*FROMordersWHEREid(SELECTidFROMordersORDERBYidLIMIT1000000,1)ORDERBYidLIMIT10;为什么快子查询是覆盖索引只查主键 ID外层查询用WHERE id 起始 ID走主键索引只扫描 10 行实战优化一个慢分页假设有个订单表分页查询很慢SELECT*FROMordersORDERBYcreated_atDESCLIMIT1000000,10;第 1 步看执行计划EXPLAINSELECT*FROMordersORDERBYcreated_atDESCLIMIT1000000,10;输出--------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | --------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 20000000 | Using filesort | ---------------------------------------------------------------------------------------------问题type ALL全表扫描rows 20000000扫描全表Extra Using filesort文件排序第 2 步给 created_at 加索引CREATEINDEXidx_created_atONorders(created_atDESC);再看执行计划EXPLAINSELECT*FROMordersORDERBYcreated_atDESCLIMIT1000000,10;输出------------------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------------------ | 1 | SIMPLE | orders | index | NULL | idx_created_at | 5 | NULL | 1000010 | | ------------------------------------------------------------------------------------------------优化效果type index索引扫描rows 1000010扫描 1000010 行比全表扫描好点Extra里没有Using filesort了因为索引是有序的但还是慢因为扫描了 1000010 行。第 3 步用游标分页-- 假设上一页最后一条记录的 created_at 2024-01-15 10:30:00SELECT*FROMordersWHEREcreated_at2024-01-15 10:30:00ORDERBYcreated_atDESCLIMIT10;再看执行计划EXPLAINSELECT*FROMordersWHEREcreated_at2024-01-15 10:30:00ORDERBYcreated_atDESCLIMIT10;输出------------------------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------------------------ | 1 | SIMPLE | orders | range | idx_created_at | idx_created_at | 5 | NULL | 10 | Using where | ------------------------------------------------------------------------------------------------------优化效果type range范围查询rows 10只扫描 10 行实际执行时间从 5 秒降到 0.001 秒5000 倍提升实战建议1. 优先用游标分页如果业务允许比如移动端下拉加载更多优先用游标分页WHERE id 上一页最后一条 ID。好处性能最好不管翻到第几页都是扫描固定行数。2. 如果必须用传统分页用延迟关联如果业务必须用传统分页比如 PC 端翻页用户可能跳到第 100001 页用延迟关联优化。SELECT*FROMorders aJOIN(SELECTidFROMordersORDERBYidLIMIT1000000,10)bONa.idb.id;3. 给 ORDER BY 字段加索引如果 ORDER BY 没走索引会导致Using filesort性能很差。建议给ORDER BY字段加索引或者让ORDER BY用上联合索引的后缀。4. 限制最大翻页数如果表有 1 亿条数据用户翻到第 10000001 页性能还是会很差。建议限制最大翻页数比如最多翻 1000 页或者强制用户用搜索代替翻页。-- 如果 offset 10000报错或者重定向到搜索页if(offset10000){return请用搜索功能;}## 总结 - 传统分页LIMIToffset,size的问题是扫描offsetsize行然后丢弃前offset行性能极差 - - 优化方案 1**延迟关联**先查 ID再关联 - - 优化方案 2**游标分页**推荐用WHEREid上一页最后一条 ID限制范围--优化方案3**BETWEEN**适合翻页不多的情况--优化方案4**子查询**类似延迟关联--实战建议优先用游标分页如果必须用传统分页就用延迟关联给ORDERBY字段加索引限制最大翻页数 如果你能把这几种分页优化方案讲清楚面试官绝对觉得你有实战经验。---**实战代码都在我本地跑过你可以放心复制。**如果有问题欢迎评论区交流
