怎样解决mysql深分页问题

---

本篇文章给大家带来了关于mysql的相关知识，主要介绍了优雅地解决mysql深分页问题，本文将会讨论当mysql表大数据量的情况，如何优化深分页问题，并附上最近的优化慢sql问题的案例伪代码，希望对大家有帮助。

推荐学习：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，是不是快了不少。

一览妙笔

自媒体、编剧、营销人员写作工具

50 查看详情

标签记录法

标签记录法： 其实标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。类似书签的作用

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进行查询，查询结果也是一样。速度更快。

总结

1、当业务需要从表中查出大数据量时，而又项目架构没上ES时，可考虑使用标签记录法的方式，对查询效率进行优化。

2、从需求上也应该尽可能避免，在大数据量的情况下，分页查询最后一页的功能。或者限制成只能一页一页往后划的场景。

推荐学习：mysql视频教程

以上就是怎样解决mysql深分页问题的详细内容，更多请关注其它相关文章！

# 通话记录  # 大米电影网站建设  # 视频免费网站推广  # seo 大站策略  # 营销推广常用的转场  # 肥财猫网站建设  # 查竞品关键词软件排名  # 深圳地产网站优化查询  # 前端网页seo  # 扬州网站建设门户  # 软文营销网站推广  # mysql  # 持续时间  # 会先  # 这条  # 将会  # 查询结果  # 的是  # 主键  # 镜像  # 分页 

相关栏目： 【 Google疑问12 】 【 Facebook疑问10 】 【 优化推广96088 】 【 技术知识133117 】 【 IDC资讯59369 】 【 网络运营7196 】 【 IT资讯61894 】

相关推荐： 谷歌学术论文搜索引擎 谷歌学术官网入口论坛永久链接  《海底捞》点外卖方法  火狐浏览器如何刷新修复浏览器 火狐浏览器“重置Firefox”功能详解  如何用mysql实现客户反馈管理_mysql客户反馈数据库方法  快手缓存清理方法  Win11怎么开启HDR_Windows 11显示器画质增强设置  学习通网页版个人登录_学习通网页版个人账户登录入口  uc浏览器官网网页版使用 uc浏览器官网免费在线首页  PDF如何批量加注释_PDF多文件批注高亮操作教程  sublime text 4如何安装_最新版sublime下载与汉化教程  小米倒班助手添加日历提醒  《糖豆》添加舞曲方法  excel怎么制作考勤表 excel考勤模板与函数公式讲解  《书耽》更换手机号方法  Sublime怎么自动添加CSS前缀_Sublime安装Autoprefixer插件  J*aScript大数运算_BigInt使用指南  《kimi智能助手》制作ppt教程  《幻兽帕鲁》手游帕鲁捕捉技巧分享  TikTok视频播放中断怎么办 TikTok播放异常修复方法  AO3官方镜像链接 | 最新防走失网址永久收藏  Golang如何初始化module项目_Golang module init使用说明  解决PHP MySQL数据库更新无响应：SQL查询语法错误解析  使用TinyButStrong生成HTML并结合Dompdf创建PDF教程  在React中正确处理HTML input type="number"的数值类型  CSS过渡如何实现按钮悬停效果_transition属性控制背景颜色变化  鲁班大师乓乓皮肤获取方法  热血江湖归来医师加点攻略  Three.js中动态更换3D模型纹理的教程  邮编号码查询app有哪些_邮编号码查询推荐app及使用体验  响应式设计中动态背景颜色条的实现指南  Yandex浏览器官方入口_Yandex搜索引擎中文版  顺丰快递在线查询系统 顺丰快递官方查单入口  盲鳗善于分泌黏液猜猜主要用来做什么  CSS过渡与滚动滚动事件结合应用_scroll与transition动画  泰拉瑞亚水晶无法放置问题  吃完饭就犯困是什么原因 餐后嗜睡如何缓解  Python csv 模块处理非字符串数据：列表写入 CSV 文件的机制解析  word文档行距怎么调？word文档调行距的操作步骤  React应用中Commerce.js数据加载与状态管理最佳实践  抖音火山版如何进行提现  汽水音乐在线听歌网页版 汽水音乐在线听歌网页版入口  邮政快递寄件查询入口 邮政快递收件查询入口  如何在mysql中设计餐饮点餐系统_mysql点餐系统项目实战  QQ邮箱官方登录页_腾讯出品安全稳定的邮箱服务  苹果电脑如何快速查看电池状态 苹果电脑电池信息快捷方法  优化Google Charts Gauge：在数据库无数据时显示默认值  poki官网最新入口 poki小游戏大全入口  雨课堂官网在线登录 网页版雨课堂登录链接  J*aScript桌面应用_Electron多进程架构实战  谷歌浏览器如何查找和删除恶意软件 谷歌浏览器内置安全清理工具使用教程 

 2022-07-26