如何优化 MySQL 千万级数据分页查询的性能？

它的本质是**传统LIMIT offset, size在大数据量下性能急剧下降是因为 MySQL 必须扫描并丢弃前offset行数据。当offset很大时如LIMIT 1000000, 10MySQL 需要读取 1,000,010 行记录执行 1,000,010 次回表 (Row Lookup)操作最后只返回 10 行。这是一种O(N) 的线性扫描浪费。核心痛点“回表”。二级索引找到主键后必须去聚簇索引主键索引中查找完整行数据。Offset 越大无效的回表次数越多。优化目标减少回表次数或者避免扫描无效数据。核心逻辑别让 MySQL 做无用功。要么让它只查主键覆盖索引要么让它记住上次的位置游标/Seek Method要么让它去搜索引擎干这活。如果把分页比作在图书馆找书传统分页 (LIMIT 1000000, 10)图书管理员从第 1 本书开始数数到第 1,000,000 本然后把接下来的 10 本拿给你。代价管理员累得半死你只拿了 10 本。前面的 999,990 次计数都是浪费。覆盖索引优化管理员只看目录卡片二级索引找到第 1,000,000 个卡片的 ISBN主键然后直接去书架取这 10 本书。代价虽然还要去书架但不用翻阅每一本书的内容只在目录上快速定位。游标分页 (Seek Method)你告诉管理员“我上次看到的是 ISBN 为978-7-111的书。”管理员直接在目录中找到 978-7-111的第一本书拿接下来 10 本。代价极速。无需计数直接定位。搜索引擎 (Elasticsearch)图书馆太大专门建了一个电子检索系统。所有复杂查询和深分页都交给它。代价架构复杂度增加数据同步延迟。一、传统分页瓶颈为什么LIMIT慢1. 执行流程分析SELECT*FROMordersORDERBYcreate_timeDESCLIMIT1000000,10;排序如果create_time有索引利用索引有序性否则文件排序 (Filesort)。定位沿着索引树找到第 1,000,001 条记录的主键 ID。回表 (Key Lookup)拿着这 1,000,010 个主键 ID去聚簇索引中查找完整的行数据SELECT *。过滤丢弃前 1,000,000 行返回最后 10 行。2. 性能杀手随机 I/O回表操作通常是随机磁盘 I/O比顺序 I/O 慢几个数量级。CPU 浪费解析、组装、丢弃大量不需要的数据。Buffer Pool 污染大量无关数据页被加载到内存挤占热点数据空间。 核心洞察LIMIT的本质是“跳过”。跳过的越多浪费越大。优化的核心是“不跳过”或者“少回表”。二、三大优化方案SQL 层面的手术方案 1覆盖索引 子查询 (Late Row Lookups)原理先在二级索引中完成“跳过”和“限制”只拿到主键 ID然后再关联原表获取完整数据。将1,000,010 次回表减少为10 次回表。SQL 改写-- 原始慢查询SELECT*FROMordersORDERBYcreate_timeDESCLIMIT1000000,10;-- 优化后SELECTo.*FROMorders oINNERJOIN(-- 子查询只查主键利用覆盖索引不回表SELECTidFROMordersORDERBYcreate_timeDESCLIMIT1000000,10)AStmpONo.idtmp.id;前提条件create_time必须有索引。子查询(SELECT id ...)必须是覆盖索引即索引中包含id和create_time不需要回表。InnoDB 二级索引默认包含主键所以通常天然满足。效果子查询极快只在索引树上遍历。主查询通过主键精确查找 10 次Efficient Point Queries。性能提升10-100 倍。方案 2游标分页 / Seek Method (基于 ID 或唯一键)原理放弃OFFSET改用上一页最后一条记录的 ID作为起点。利用索引的范围扫描 (或)直接定位无需扫描前 N 条。SQL 改写-- 第一页SELECT*FROMordersORDERBYidDESCLIMIT10;-- 假设最后一条 ID 是 990-- 第二页 (传入 last_id 990)SELECT*FROMordersWHEREid990ORDERBYidDESCLIMIT10;-- 第三页 (传入新的 last_id)SELECT*FROMordersWHEREid980ORDERBYidDESCLIMIT10;优势时间复杂度 O(1)相对于 Offset 的 O(N)。无论翻到第几页速度几乎不变。完全避免扫描无效数据。劣势只能顺序翻页不能直接跳转到第 1000 页除非你知道第 999 页的最后一个 ID。适用场景移动端无限滚动 (Infinite Scroll)、日志查看、后台列表通常用户只关心最新或上一页。注意如果排序字段不是唯一键如create_time可能有重复需使用复合游标WHERE(create_time,id)(2023-01-01,100)ORDERBYcreate_timeDESC,idDESCLIMIT10;方案 3限制最大页数 (Business Constraint)原理从产品层面解决技术问题。Google 搜索也只展示前几页。策略禁止访问超过第 100 页的数据。SQL$pagemin($request-page,100);// 强制上限$offset($page-1)*$pageSize;价值绝大多数用户不会翻到 100 页以后。为极少数长尾需求牺牲整体性能是不划算的。三、架构级解决方案当 SQL 优化不够时1. 引入 Elasticsearch (ES) / Solr场景复杂筛选、模糊搜索、深度分页10,000 页。原理ES 使用倒排索引和Scroll/Search AfterAPI专为大规模数据检索设计。架构MySQL作为Source of Truth存储完整数据。ES作为Search Engine同步 MySQL 数据通过 Canal, Logstash, 或业务代码双写。查询流程前端请求 - ES 搜索 - 返回 ID 列表 - (可选) 回 MySQL 查详情。优势支持任意字段的复杂组合查询深分页性能远超 MySQL。2. 冗余统计表 / 预计算场景需要精确的总行数 (COUNT(*))这在千万级表中很慢。策略维护一个order_count_stat表实时或定时更新总数。前端显示“共 1000 万条”但不允许翻到最后几页。价值避免每次分页都执行昂贵的COUNT(*)。3. 冷热数据分离场景大部分查询集中在最近 3 个月的数据。策略热数据最近 3 个月存放在高性能 SSD / MySQL 主库。冷数据3 个月前归档到 HDD / 历史库 / Hive。分页查询默认只查热数据表。价值减小单表数据量提升索引效率。四、认知牢笼常见误区1. 误区“加索引就能解决所有分页问题。”真相索引能加速排序和定位但无法消除大 Offset 带来的回表开销。对策必须结合覆盖索引或游标分页。2. 误区“SELECT COUNT(*)很快。”真相在 InnoDB 中COUNT(*)需要扫描全表或最大的二级索引千万级数据可能需要几秒。对策使用近似值SHOW TABLE STATUS或冗余计数表。3. 误区“游标分页用户体验不好因为不能跳页。”真相在移动互联网时代无限滚动是主流。即使是在 PC 端用户也很少直接输入“第 5000 页”。对策如果必须跳页限制最大页码如 100 页内部使用传统分页超过 100 页提示“请细化搜索条件”。4. 误区“ES 是银弹可以完全替代 MySQL 查询。”真相ES 存在数据一致性延迟近实时 NRT。ES 不适合事务性操作和复杂聚合。对策MySQL 和 ES 各司其职。MySQL 存数据ES 搜数据。5. 误区“优化分页只需要改 SQL。”真相有时候产品需求才是瓶颈。对策与产品经理沟通确认“深分页”是否真的是刚需。很多时候导出功能或特定时间范围筛选更能解决问题。 总结原子化“千万级分页优化”全景图维度关键点本质减少无效扫描和回表开销核心瓶颈大 Offset 导致的线性扫描和随机 I/OSQL 优化覆盖索引子查询 (Late Lookup)、游标分页 (Seek Method)架构优化Elasticsearch、冷热分离、限制最大页数最佳实践优先游标分页其次覆盖索引最后考虑 ESPHP 隐喻Don’t Count to a Million. Jump to the Page Number.公式Performance (Index_Coverage × Seek_Method) ^ Data_Architecture终极心法分页优化的本质是“对扫描的零容忍”。别让数据库做无用功。能定位的绝不扫描能少回表的绝不多回。于索引中见捷径于游标见极速以架构为尺解全表之牛于海量数据中求精准之真。行动指令审计慢查询找出项目中OFFSET大于 1000 的分页查询。实施覆盖索引将SELECT *改写为JOIN (SELECT id ...)子查询模式。改造前端对于列表页尝试改为“加载更多”或“无限滚动”后端改用游标分页 (WHERE id last_id)。限制页码在后端代码中强制$page 100。评估 ES如果业务涉及复杂搜索和深分页规划引入 Elasticsearch。思维升级记住最好的分页是让用户不需要翻到第 1000 页。