本文系统解析MySQL在百万级数据下分页慢的根本原因（尤其是LIMIT offset, size在高偏移量时的I/O与排序开销），并提供游标分页、索引优化、查询重构等生产级优化方案，附可直接落地的SQL示例与工程注意事项。
本文系统解析mysql在百万级数据下分页慢的根本原因（尤其是`limit offset, size`在高偏移量时的i/o与排序开销），并提供游标分页、索引优化、查询重构等生产级优化方案，附可直接落地的sql示例与工程注意事项。
在Web应用中，分页是商品列表、后台日志、订单管理等场景的刚需。但当数据量达百万级（如100万订单），用户点击“最后一页”触发 SELECT * FROM orders WHERE customer_name LIKE '%Henry%' ORDER BY customer_name DESC LIMIT 10 OFFSET 100000 时，响应时间骤升至数秒甚至超时——这并非数据库能力不足，而是传统分页模式与InnoDB物理存储机制冲突所致。
? 为什么 OFFSET 越大越慢？
MySQL执行 LIMIT offset, size 时，并不具备“跳过N行”的物理能力。其真实执行流程为：
- 先按
ORDER BY customer_name DESC扫描所有满足WHERE customer_name LIKE '%Henry%'的行（注意：%Henry%是前导通配符，导致无法使用索引进行范围扫描，只能全索引遍历或全表扫描）； - 对扫描结果排序（若
customer_name无高效索引，还会触发Using filesort和临时表）； -
顺序读取前
offset + size = 100010行，再丢弃前100000行，仅返回最后10条。
这意味着：即使只取10条数据，MySQL仍需处理10万+行的I/O、内存排序和CPU计算——而OFFSET 100000的本质，是让数据库做大量“无用功”。
✅ 正确解法：三层优化策略
① 根治搜索性能：替换低效LIKE，构建前缀匹配
LIKE '%Henry%' 是性能杀手。应推动前端/产品侧优化交互逻辑：
- ✅ 改为
LIKE 'Henry%'（后缀通配），配合INDEX(customer_name)实现索引快速定位； - ✅ 或引入全文索引（
FULLTEXT(customer_name)）+MATCH ... AGAINST，支持更灵活的模糊检索； - ❌ 避免
'%Henry'或'%Henry%'—— 它们强制全扫描，任何分页优化都难救。
-- 优化后（假设用户输入“Henry”开头） SELECT * FROM orders WHERE customer_name >= 'Henry' AND customer_name < 'Henrys' ORDER BY customer_name DESC LIMIT 10;
② 彻底替代OFFSET：采用游标分页（Keyset Pagination）
这是最推荐、最稳定的大数据分页方案。核心思想：用上一页最后一条记录的排序键值作为下一页查询起点，避免跳过大量中间行。
✅ 前提条件：
- 排序字段必须有高效索引（主键最优，或
INDEX(customer_name, id)联合索引防重复）； - 排序字段需严格非空且唯一性高（若用
customer_name，建议追加主键id作为第二排序项：ORDER BY customer_name DESC, id DESC）。
✅ 下一页查询示例（假设上一页最后一条记录 customer_name = 'Henry Smith', id = 88721）：
SELECT * FROM orders WHERE customer_name < 'Henry Smith' OR (customer_name = 'Henry Smith' AND id < 88721) ORDER BY customer_name DESC, id DESC LIMIT 10;
? 性能优势：无论翻到第1万页还是第100万页，执行计划始终基于索引范围扫描（
range），耗时稳定在毫秒级。
③ 支持跳页场景：预计算页索引表
若业务强依赖“输入页码跳转”（如后台管理系统的页码框），不可硬扛OFFSET。推荐构建轻量级页索引表：
-- 创建页索引辅助表（按主键id分片，每1000行为一页） CREATE TABLE orders_page_index ( page_num INT PRIMARY KEY, min_id BIGINT NOT NULL, max_id BIGINT NOT NULL, row_count INT DEFAULT 1000 ); -- 定时任务或写入触发更新（示例：每千条记录生成一页元数据） INSERT INTO orders_page_index (page_num, min_id, max_id) SELECT FLOOR((id - 1) / 1000) + 1 AS page_num, MIN(id) AS min_id, MAX(id) AS max_id FROM orders GROUP BY FLOOR((id - 1) / 1000);
查第N页时：
-- 步骤1：快速查索引表获取ID范围 SELECT min_id, max_id FROM orders_page_index WHERE page_num = 1000; -- 步骤2：精准范围查询（配合LIMIT防超量） SELECT * FROM orders WHERE id BETWEEN ? AND ? ORDER BY id DESC LIMIT 10;
⚠️ 关键注意事项
-
禁止在事务中执行大OFFSET查询：尤其避免
UPDATE ... LIMIT offset, 1，易引发长事务与锁竞争； -
索引设计必须匹配排序+过滤：如
ORDER BY customer_name DESC+WHERE customer_name LIKE 'Henry%'，则索引应为INDEX(customer_name)；若含多条件，优先建立覆盖索引（如INDEX(customer_name, status, created_at)）； -
警惕“伪优化”陷阱：网上流传的“子查询优化法”（如
(SELECT id FROM orders ORDER BY id LIMIT 100000, 1)）在高并发下仍会重复扫描，且无法解决LIKE '%...%'问题； - 前端协同：对百万级数据，应默认禁用页码输入框，改用“加载更多”或“回到顶部”滚动交互，从源头规避跳页需求。
分页不是功能终点，而是性能设计的起点。真正健壮的分页系统，不依赖数据库的OFFSET原语，而在于将“跳转逻辑”前置到应用层与索引设计中。掌握游标分页，你就能让百万数据下的每一次翻页，都如呼吸般自然流畅。
