解决MySQL分页优化的实现_数据库_开发者

在后端开发中，分页查询是高频需求，但当数据量达到百万级、分页页码翻到数千页后，你可能会遇到一个棘手问题：limit 5000000,10 这类深分页查询，响应时间突然从几十毫秒飙升到几秒甚至更久。今天就来拆解这个问题的根源，以及如何用 “覆盖索引 + 子查询” 的方案实现性能跃迁。

一、深分页查询：为什么越往后越慢？

先搞懂一个核心问题：同样是查 10 条数据，limit 10,10 很快，limit 5000000,10 却很慢，差别到底在哪？

这要从 mysql 处理 limit offset, size 的逻辑说起：

当执行 limit 5000000,10 时，MySQL 会先扫描并排序前 5000010 条数据（offset + size）；
然后丢弃前 5000000 条数据，只返回剩下的 10 条；
如果查询语句是 select *，且没有适配的索引，MySQL 还需要从磁盘读取全表数据，再进行排序 —— 这个 “扫描 + 排序 + 丢弃” 的过程，会消耗大量 CPU 和 IO 资源，数据量越大，耗时越夸张。

举个真实案例：一张 1000 万数据的商品表 tb_sku，执行 select * from tb_sku order by id limit 5000000,10，在没有优化的情况下，响应时间高达 4.8 秒；而优化后，耗时直接降到 0.08 秒，性能提升 60 倍。

二、优化核心思路：减少 “无效工作”

既然慢的根源是 “扫描了太多不需要的数据”，那优化方向就很明确：让 MySQL 只处理 “真正需要的那部分数据”，减少无效扫描和排序。

这里的关键是利用 “覆盖索引” 和 “子查询” 组合：

覆盖索引：如果索引包含http://www.devze.com查询所需的所有字段，MySQL 无需回表查主数据，直接从索引获取数据即可 —— 这里我们用主键索引 id（主键默认是聚簇索引，本身有序，还能定位到主数据）；
子查询优先定位主键：先用子查询 select id from tb_sku order by id limit 5000000,10，通过主键索引快速找到 “目标 10 条数据的 id”（因为主键索引有序，无需额外排序，直接定位 offset 位置）；
关联主表查详情：再用找到的 id 关联主表 tb_sku，精准获取这 10 条数据的完整信息 —— 此时 MySQL 只需读取 10 条python主数据，无需扫描百万级数据。

三、实操方案：优化后的 SQL 与索引配置

1. 优化后的 SQL 语句

直接上代码，核心就是 “子查询查 id + 关联查详情”：

select t.* 
from tb_sku t
inner join (
    -- 子查询：通过主键索引快速定位目标 10 条数据的 id
    select id http://www.devze.com
    from tb_sku 
    order by id  -- 主键索引本身有序，无需额外排序
    limit 5000000, 10
) a on t.id = a.id;  -- 用 id 关联主表，精准获取详情

2. 必须配置的索引

这个方案能生效，前提是 id 是主键（或有基于 id 的索引）—— 主键默认是聚簇索引，本身就包含排序属性，所以无需额外创建索引。如果排序字段不是主键（比如按 create_time 排序），则需要创建联合索引：

-- 若按 create_time 分页，创建覆盖索引（包含排序字段和主键）
create index idx_sku_create_time on tb_sku(create_time, id);

此时子查询可以改为：

select t.* 
from tb_sku t
inner join (
    select id 
    from tb_sku 
    order by create_time 
    limit 5000000, 10
) a on t.id = a.id;

四、原理拆解：为什么这个方案这么快？

对比优化前后的执行逻辑，就能明白性能提升的关键：

阶段	优化前（直接 limit 5000000,10）	优化后（子查询 + 关联）
数据扫描范围	扫描前 5000010 条全表数据	仅扫描子查询中 10 条数据的 id（索引）
排序操作	对 5000010 条数据排序	主键 / 索引本身有序，无需排序
回表操作	可能回表 5000010 次（若无覆盖索引）	仅回表 10 次（精准关联 id）
无效数据丢弃	丢弃 5000000 条数据	无丢弃操作，直接获取目标数据

简单说：优化前 MySQL 在 “做无用功”（扫描、排序、丢弃大量数据），优化后只做 “必要工作”（定位 id、查 10 条详情），自然速度更快。

五、注意事项：避免踩坑

排序字段必须在索引中：如果子查询的 order by 字段不在索引里，MySQL 还是会全表排序，优化失效。比如按 mSYpIprice 排序，就必须创建包含 price 和 id 的索引；
关联字段用主键 / 唯一键：关联主表时，要用 id 这类主键或唯一键 —— 主键是聚簇索引，查询速度最快，避免用普通字段关联导致全表扫描；
offset 过大仍有瓶颈：如果 offset 达到千万级（比如 limit 10000000,10），子查询定位 id 仍会有轻微耗时，此时建议用 “游标分页”（比如 where id > 上一页最大id limit 10），彻底避免 offset 问题；
验证执行计划：优化后用 explain 查看执行计划，确保子查询的 type 是 range 或 ref，Extra 没有 Using filesort（排序）和 Using temporary（临时表）—— 这两个关键字出现，说明索引没生效。