共计 3758 个字符，预计需要花费 10 分钟才能阅读完成。
今天丸趣 TV 小编给大家分享一下如何解决 mysql 深分页问题的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。
日常需求开发过程中，相信大家对于 limit 一定不会陌生，但是使用 limit 时，当偏移量（offset）非常大时，会发现查询效率越来越慢。一开始 limit 2000 时，可能 200ms, 就能查询出需要的到数据，但是当 limit 4000 offset 100000 时，会发现它的查询效率已经需要 1S 左右，那要是更大的时候呢，只会越来越慢。
概括
本文将会讨论当 mysql 表大数据量的情况，如何优化深分页问题，并附上最近的优化慢 sql 问题的案例伪代码。
1、limit 深分页问题描述
先看看表结构（随便举了个例子，表结构不全，无用字段就不进行展示了）
CREATE TABLE `p2p_detail_record` ( `id` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT COMMENT 主键 ,
`batch_num` int NOT NULL DEFAULT 0 COMMENT 上报数量 ,
`uptime` bigint NOT NULL DEFAULT 0 COMMENT 上报时间 ,
`uuid` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT COMMENT 会议 id ,
`start_time_stamp` bigint NOT NULL DEFAULT 0 COMMENT 开始时间 ,
`answer_time_stamp` bigint NOT NULL DEFAULT 0 COMMENT 应答时间 ,
`end_time_stamp` bigint NOT NULL DEFAULT 0 COMMENT 结束时间 ,
`duration` int NOT NULL DEFAULT 0 COMMENT 持续时间 ,
PRIMARY KEY (`id`),
KEY `idx_uuid` (`uuid`),
KEY `idx_start_time_stamp` (`start_time_stamp`) // 索引,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT= p2p 通话记录详情表 假设我们要查询的深分页 SQL 长这样
select *
from p2p_detail_record ppdr
where ppdr .start_time_stamp 1656666798000
limit 0,2000查询效率是 94ms，是不是很快？那如果我们 limit 100000，2000 呢，查询效率是 1.5S，已经非常慢，那如果更多呢？
2、sql 慢原因分析
让我们来看看这条 sql 的执行计划
也走到了索引，那为什么还是慢呢？我们先来回顾一下 mysql 的相关知识点。
聚簇索引和非聚簇索引
聚簇索引：叶子节点储存的是整行的数据。
非聚簇索引：叶子节点储存的是整行的数据对应的主键值。
使用非聚簇索引查询的流程
通过非聚簇索引树，找到对应的叶子节点，获取到主键的值。
再通过取到主键的值，回到聚簇索引树，找到对应的整行数据。（整个过程称为回表）
回到这条 sql 为什么慢的问题上，原因如下
1、limit 语句会先扫描 offset+ n 行，然后再丢弃掉前 offset 行，返回后 n 行数据。也就是说 limit 100000,10，就会扫描 100010 行，而 limit 0,10，只扫描 10 行。这里需要回表 100010 次，大量的时间都在回表这个上面。
方案核心思路：能不能事先知道要从哪个主键 ID 开始，减少回表的次数
常见解决方案通过子查询优化
select *
from p2p_detail_record ppdr
where id = (select id from p2p_detail_record ppdr2 where ppdr2 .start_time_stamp 1656666798000 limit 100000,1)
limit 2000相同的查询结果，也是 10W 条开始的第 2000 条，查询效率为 200ms，是不是快了不少。
标签记录法
标签记录法：其实标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。类似书签的作用
select * from p2p_detail_record ppdr
where ppdr.id bb9d67ee6eac4cab9909bad7c98f54d4
order by id
limit 2000
备注：bb9d67ee6eac4cab9909bad7c98f54d4 是上次查询结果的最后一条 ID使用标签记录法，性能都会不错的，因为命中了 id 索引。但是这种方式有几个缺点。
1、只能连续页查询，不能跨页查询。
2、需要一种类似连续自增的字段（可以使用 orber by id 的方式）。
方案对比
使用通过子查询优化的方式
优点：可跨页查询，想查哪一页的数据就查哪一页的数据。
缺点：效率不如标签记录法。原因：比如需要查 10W 条数据后，第 1000 条，也需要先查询出非聚簇索引对应的 10W1000 条数据，在取第 10W 开始的 ID，进行查询。
使用 标签记录法 的方式
优点：查询效率很稳定，非常快。
缺点：
不跨页查询，
需要一种类似连续自增的字段
关于第二点的说明：该点一般都好解决，可使用任意不重复的字段进行排序即可。若使用可能重复的字段进行排序的字段，由于 mysql 对于相同值的字段排序是无序，导致如果正好在分页时，上下页中可能存在相同的数据。
实战案例
需求：需要查询查询某一时间段的数据量，假设有几十万的数据量需要查询出来，进行某些操作。
需求分析 1、分批查询（分页查询），设计深分页问题，导致效率较慢。
CREATE TABLE `p2p_detail_record` ( `id` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT COMMENT 主键 ,
`batch_num` int NOT NULL DEFAULT 0 COMMENT 上报数量 ,
`uptime` bigint NOT NULL DEFAULT 0 COMMENT 上报时间 ,
`uuid` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT COMMENT 会议 id ,
`start_time_stamp` bigint NOT NULL DEFAULT 0 COMMENT 开始时间 ,
`answer_time_stamp` bigint NOT NULL DEFAULT 0 COMMENT 应答时间 ,
`end_time_stamp` bigint NOT NULL DEFAULT 0 COMMENT 结束时间 ,
`duration` int NOT NULL DEFAULT 0 COMMENT 持续时间 ,
PRIMARY KEY (`id`),
KEY `idx_uuid` (`uuid`),
KEY `idx_start_time_stamp` (`start_time_stamp`) // 索引,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT= p2p 通话记录详情表 伪代码实现：
// 最小 ID
String lastId = null;
// 一页的条数
Integer pageSize = 2000;
List P2pRecordVo list ;
do{
list = listP2pRecordByPage(lastId,pageSize); // 标签记录法，记录上次查询过的 Id
lastId = list.get(list.size()-1).getId(); // 获取上一次查询数据最后的 ID，用于记录
// 对数据的操作逻辑
XXXXX();
}while(isNotEmpty(list));
select id = listP2pRecordByPage
select *
from p2p_detail_record ppdr where 1=1
if test = lastId != null
and ppdr.id #{lastId}
/if
order by id asc
limit #{pageSize}
/select这里有个小优化点: 可能有的人会先对所有数据排序一遍，拿到最小 ID，但是这样对所有数据排序，然后去 min（id), 耗时也蛮长的，其实第一次查询，可不带 lastId 进行查询，查询结果也是一样。速度更快。
以上就是“如何解决 mysql 深分页问题”这篇文章的所有内容，感谢各位的阅读！相信大家阅读完这篇文章都有很大的收获，丸趣 TV 小编每天都会为大家更新不同的知识，如果还想学习更多的知识，请关注丸趣 TV 行业资讯频道。
