李锋镝的博客 - LiFengdi.Com

  • 首页
  • 时间轴
  • 留言
  • 左邻右舍
  • 我的日常
  • 关于我
青衿之志 履践致远
霁月光风 不萦于怀
  1. 首页
  2. 原创
  3. 正文

MySQL深度分页

2022年2月11日 7883点热度 1人点赞 0条评论

背景

mysql分页查询是我们常见的需求,但是随着页数的增加查询性能会逐渐下降,尤其是到深度分页的情况。我们可以把分页分为两个步骤:

  1. 定位偏移量
  2. 获取分页条数的数据

所以当数据较大页数较深时就涉及一次需要耗费较长时间的操作。所以mysql深度分页的问题该如何解决呢?

首先我们来看一个简单的查询:

SELECT * FROM events WHERE date > '2010-01-01T00:00:00-00:00' AND event = 'editstart' ORDER BY date LIMIT 50000 50;

其大致的页数查询性能曲线如下:

页数查询性能曲线

可以发现在一定页数后时间延时非常明显。结合相关文章我们的解决方式可以大致分为以下几种.

思路

思路:既然分页查询时,定位偏移量较慢,我们可不可以减少这个偏移量的定位,使其始终在曲线的前半部分,即在较少偏移量的场景。

方法一:

以结果作为条件,已查询条件的变化换取分页的不变。

分页查询我们一般都是逐渐往后翻页的,那么我们可以很清晰的知道,在当前查询页的最后一条数据的时间点,那么,以此时间点再查询20条,那么 我们当前的页数就同样还是0,以时间点的推移换取页数的不变,减少其偏移量的计算。

我们可以创建索引 index(date,id), id就是我们上一次的返回结果。

具体示例如下:

SELECT * FROM events WHERE (date,id) > ('2010-07-12T10:29:47-07:00',111866) AND event = 'editstart' ORDER BY date, id LIMIT 50000 50;

局限性:

  1. id最好是主键,是否有这样自增长的字段,或者说带顺序变化特性的列。
  2. 无法适应下一次分页页数与上一次相差较大,如由第一页突然跳转到50万页。

优点:

可以适合复杂查询条件查询的场景。不需要改变sql语句结构。

方法二:

采用子查询模式。其原理依赖于覆盖索引,当查询的列均是索引字段时,性能较快,因为其只用遍历索引本身。我们自己创建的非主键索引,都是非聚集索引,其不包含非索引字段,所以数据结构较小,系统能快速遍历。我们知道索引是b+树结构,系统能很容易的知道866613位于索引树的位置。

##查询语句
select id from product limit 866613, 20;
##优化方式一
SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20;
##优化方式二
SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id;

局限性:

  1. 依赖于主键的自增长特性。
  2. 不适合复杂查询条件的分页逻辑,复杂查询条件很难做到,索引包含全部查询字段,容易漏掉部分数据。

方法三:

复合索引:其原理同样是索引覆盖的思想,只不过是其以查询条件的一份作为索引,最终的索引字段是主键id。这种场景严格依赖于索引的顺序。查询的结果也不能包含非索引字段,需再走一次子查询。

最后

关于深度分页:

针对复杂的查询逻辑,一般从数据的偏移量着手,减少偏移量的定位时间。

简单的查询逻辑,可以从索引覆盖的思想着手,先确定查询数据的主键id,再由id找相关的数据,索引能解决的就不要加给业务逻辑了。

除非注明,否则均为李锋镝的博客 - LiFengdi.Com原创文章,转载必须以链接形式标明本文链接
本文链接:https://www.lifengdi.com/archives/article/3787
本作品采用 知识共享署名 4.0 国际许可协议 进行许可
标签: MySQL SQL 深度分页
最后更新:2022年2月11日

李锋镝

既然选择了远方,便只顾风雨兼程。

打赏 点赞
< 上一篇
下一篇 >
guest
您的姓名(必填)
您的邮箱(必填)
您的站点
guest
您的姓名(必填)
您的邮箱(必填)
您的站点
0 评论
Inline Feedbacks
查看所有评论
支付宝红包

天若有情天亦老,人间正道是沧桑。

最新 热点 随机
最新 热点 随机
hnswlib installation failed 一眨眼就三年了…… redis异常记录 今天,是我的第三十一个生日 回忆是一条没有尽头的路 这样的日子什么时候才是个头
今天,是我的第三十一个生日阳了...开工啦~RocketMQ的push消费方式实现详解国庆节过的也很累~~今天天气很好~心情也不差~
何谓重构?为何重构? 试了下壁挂炉供暖 MySQL数据库详解——执行SQL更新时,其底层经历了哪些操作? MySQL语法总结 今天天气很好~心情也不差~ Python3 操作 MySQL 插入一条数据并返回主键 id
最近评论
李锋镝 发布于 7 天前(05月22日) 兄弟开玩笑了~
王光卫博客 发布于 7 天前(05月22日) :douyin.19: 博主都在规划下个三年了,我当前还没活好
王光卫博客 发布于 2 周前(05月18日) :douyin.19: 我网站只要有人评论留言,就会出现超负荷,研究了半天也不敢就优化mysql
yeminli çeviri 发布于 1 个月前(04月16日) çeviri bürosu
tercüme 发布于 1 个月前(04月16日) çeviri bürosu
有情链接
  • 志文工作室
  • 临窗旋墨
  • 旧时繁华
  • 城南旧事
  • 强仔博客
  • 林三随笔
  • 徐艺扬的博客
  • 云辰博客
  • 韩小韩博客
  • 知向前端
  • 阿誉的博客
  • 林羽凡
  • 情侣头像
  • 哥斯拉
  • 博客录

COPYRIGHT © 2022 lifengdi.com. ALL RIGHTS RESERVED.

Theme Kratos Made By Seaton Jiang

豫ICP备16004681号-2