Mysql 深度分页问题及优化方案

一、为什么 MySQL 深度分页慢？

在数据量大时，深分页查询速度缓慢，主要原因是多次回表查询。

前言：N个条件为索引，id为主键

平常分页一般也是用的 PageHelper 插件，最终 SQL 就大致长这个样：

-- SELECT * FROM table_name WHERE N个条件 ORDER BY id LIMIT offset, limit;SELECT id, name FROM table_name WHERE N个条件 LIMIT 100000, 10;

它的执行流程：

• 先去二级索引过滤数据，然后找到主键ID
• 通过ID回表查询数据，取出需要的列
• 扫描满足条件的100010，丢弃前面100000条，返回

这里很明显的不足就是，明明只需要拿10条，确多回表了100000次

二、优化方案

前两种方式其核心点都是 优化回表次数 这个角度去进行优化，但是扫描的行却并没有减少，后面两种是从减少扫描行入手的方式，不过都有一定限制。

局限性：依赖于连续自增的字段（如果不连续，可以order by 一下 ）

1. 通过子查询优化

优化回表次数

SELECT id, name FROM table_name WHERE id >= (SELECT id FROM table_name WHERE update_time >= '2024-11-01 23:59:59' LIMIT 100000, 1) AND update_time >= '2024-11-01 23:59:59' LIMIT 10;

流程：根据条件在二级索引进行匹配，得出结果ID后，外层查询再根据结果ID向后查10个即可

2. 通过 INNER JOIN 优化

优化回表次数

SELECT t1.id, t1.name FROM table_name t1 INNER JOIN (SELECT t2.id FROM table_name t2 WHERE t2.update_time >= '2024-11-01 23:59:59' ORDER BY t2.update_time LIMIT 100000, 10) AS t3 ON t1.id = t3.id;

3. 标签记录法

记录上次查询的最大ID，再请求下一页的时候

select id, name FROM table_name where id > 100000 order by id limit 10;

4. between…and…

select id, name FROM table_name where id between 100000 and 100010 order by id;

三、补充

优化方案	是否可带条件	适用场景
子查询	是	后台系统多条件分页
INNER JOIN	是	后台系统多条件分页
标签记录法	否	滑动分页（如app商品列表、新闻资讯列表）
between…and…	否	滑动分页

在系统中采用标签记录法，根据条件快速定位到ID，然后再次根据条件向后扫描指定行数，前端也一并改造，禁止输入页数，仅允许点击下一页上一页【既然都出现深分页问题了，那业务也不需要支持使用者随意跳页，因为没有任何意义，他要跳到八千五百三十一页看什么呢？】

---

参考链接：https://www.jb51.net/database/329990tpg.htm