别在用offset和limit分页了

原创
01/21 10:59
阅读数 1.3W

终于要对MySQL优化下手了,本文将对分页进行优化说明,希望可以得到一个合适你的方案

前言

分页这个话题已经是老生常谈了,但是有多少小伙伴一边是既希望优化的自己的系统,另一边在项目上还是保持自己独有的个性。

有个性

优化这件事是需要自己主动行动起来的,自己搞测试数据,只有在测试的路上才会发现更多你未知的事情。

本文咔咔也会针对分页优化这个话题进行解读。

一、表结构

这个数据库结构就是咔咔目前线上项目的表,只不过咔咔将字段名改了而已,还有将时间字段取消了。

数据库结构如下

CREATE TABLE `tp_statistics` (
  `ss_id` int(11NOT NULL AUTO_INCREMENT,
  `ss_field1` decimal(11,2NOT NULL DEFAULT '0.00',
  `ss_field2` decimal(11,2NOT NULL DEFAULT '0.00',
  `ss_field3` decimal(11,2NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`ss_id`)
ENGINE=InnoDB AUTO_INCREMENT=3499994 DEFAULT CHARSET=utf8 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPACT;

表结构

根据以上信息可以看到目前表里边的数据有350万记录,接下来就针对这350W条记录进行查询优化。

二、初探查询效率

先来写一个查询的SQL语句,先看一下查询耗费的时间。

根据下图可以看到查询时间基本忽略不计,但是要注意的是limit的偏移量值。

初次查询结果

于是我们要一步一步的加大这个偏移量然后进行测试,先将偏移量改为10000

可以看到查询时间还是非常理想的。

偏移量10000查询

为了节省时间咔咔将这个偏移量的值直接调整到340W。

这个时候就可以看到非常明显的变化了,查询时间猛增到了0.79s。

偏移量340w查询

出现了这样的情况,那肯定就需要进行优化了,拿起键盘就是干。

三、分析查询耗时的原因

提到分析SQL语句,必备的知识点就是explain,如果对这个工具不会使用的可以去看看MySQL的基础部分。

根据下图可以看到三条查询语句都进行了表扫描。

explain分析语句

都知道只要有关于分页就必存在排序,那么加一个排序再来看一下查询效率。

排序之后的查询时间

然后在进行对排序的语句进行分析查看。

通过这里看到当使用了排序时数据库扫描的行数就是偏移量加上需要查询的数量。

分许排序语句

此时就可以知道的是,在偏移量非常大的时候,就像上图案例中的limit  3400000,12这样的查询。

此时MySQL就需要查询3400012行数据,然后在返回最后12条数据。

前边查询的340W数据都将被抛弃,这样的执行结果可不是我们想要的。

咔咔之前看到相关文章说是解决这个问题的方案,要么直接限制分页的数量,要么就优化当偏移量非常大的时候的性能。

如果你都把本文看到了这里,那怎么会让你失望,肯定是优化大偏移量的性能问题。

四、优化

既然提到了优化,无非就那么俩点,加索引,使用其它的方案来代替这个方案。

咔咔提供的这条数据表结构信息,完全可以理解为就是图书馆的借阅记录,字段的什么都不要去关心就可以了。

对于排序来说,在这种场景下是不会给时间加排序的,而是给主键加排序,并且由于添加测试数据的原因将时间字段给取消了。

接下来使用覆盖索引加inner join的方式来进行优化。

select ss_id,ss_field1,ss_field2,ss_field3 from tp_statistics inner join ( select ss_id from tp_statistics order by ss_id limit 3000000,10) b using (ss_id);
优化方案一

从上图可以看到查询时间从0.8s优化到了0.4s,但是这样的效果还是不尽人意。

于是只能更换一下思路再进行优化。

思考片刻

既然优化最大偏移量这条路有点坎坷,能不能从其它方面进行入手。

估计有很多同学已经知道咔咔将要抛出什么话题了。

没错,就是使用where > id  然后使用limit。

先来测试一波结果,在写具体实现方案。

优化方案二

根据上图可以看到这种方式是十分可行的,分页在300W条数据以后的查询时间也基本忽略不计。

那么这种方案要怎么实现呢!

五、方案落地

其实这个方案真的很简单,只需要简单的转换一下思路即可。

是时候做出改变了

当客户端第一次获取数据的时候就正常传递offset、limit俩个参数。

首次返回的数据就使用客户端传递过来的offset、limit进行获取。

当第一次的数据返回成功后。

客户端第二次拉取数据时这个时候参数就发生改变了,就不能再是offset、limit了。

此时应该传递的参数就是第一次获取的数据最后一条数据的id。

此时的参数就为last_id、limit。

后台获取到last_id后就可以在sql语句中使用where条件 <  last_id

咔咔这里给的情况是数据在倒叙的情况下,如果正序就是大于last_id即可。

接下来咔咔使用一个案例给大家直接明了的说明。

实战案例

如下就是将要实战演示的案例,例如首次使用page、limit获取到了数据。

返回结果的最后一条数据的id就是3499984

第一次获取数据

此时如果在获取第二条记录就不是使用offset、limit了,就是传递last_id和limit了。

如下图

此时就是使用的where条件来进行直接过滤数据,条件就是id小于上次数据的最后一条id即可。

获取第二条数据

时间对比

假设现在要获取最后一条数据

没有优化之前

没有优化之前

优化之后可以明显的看到查询时间的变化

优化之后的查询

六、总结

关于limit优化简单几句话概述一下。

  • 数据量大的时候不能使用offset、limit来进行分页,因为offset越大,查询时间越久。
  • 当然不能说所有的分页都不可以,如果你的数据就那么几千、几万条,那就很无所谓,随便使用。
  • 落地方案就是咔咔上边的方案,首次使用offset、limit获取数据,第二次获取数据使用where条件到第一次数据最后一条id即可。

坚持学习、坚持写博、坚持分享是咔咔从业以来一直所秉持的信念。希望在偌大互联网中咔咔的文章能带给你一丝丝帮助。我是咔咔,下期见。


本文分享自微信公众号 - PHP初学者必看(PHP0022)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
打赏
6
31 收藏
分享
加载中
请问如果id不连续呢. 怎么准确定位id呢
02/03 15:09
回复
举报
这种算法有个专门的名词叫seek method pagination,这个早就是大数据量分页的套路了
01/25 15:14
回复
举报
是的,高性能书都几年了
01/25 22:53
回复
举报
对于排序来说,在这种场景下是不会给时间加排序的,而是给主键加排序 ===极其错误。
后台排序,所用字段,要使用用户能理解的字段。否则没有意义,用户会觉得,你的数据完全没有排序!!!
一般来说,数据量大的表,往往是随时间而线性增长的,应该有一个时间字段。查询时,应该在 where 条件中,增加一个时间范围查询。
通常的性能优化办法,是在时间字段上加索引,然后基于时间,逆向排序(绝大多数情况下,用户关注的是最新数据)。
SQL 举例:
select t.* from table_x as t where t.created_time > ... and t.created_time < ... and .... order by t.created_time desc;
然后将以上 SQL 改成 offset ... limit 分页的,或者使用 SQL 标准 row_number() OVER...。
01/25 15:11
回复
举报
根据时间跟根据主键排序有什么区别吗?主键索引不比一般索引快? 时间可以倒叙,id不可以倒叙了?
01/27 17:22
回复
举报
自增长主键,对于用户来说,并没有意义,按一个没有意思的字段排序,除了写程序的人,觉得爽,用户会觉得,数据莫名其妙地乱。 快,不是第一要考虑的。数据正确、用户友好,是第一个位的。 否则,我每个页面,只给用户显示一行数据,快不快?当然快。只是这样,没人愿意用了这个软件了。
01/31 17:09
回复
举报
迷惑行为,理解不了
02/01 00:30
回复
举报
根据 id 排序,同样是迷惑行为,没有实际意义的。 自己写着玩还可以,给用户那是要找骂的。
02/08 17:24
回复
举报
1、大数量分页场景就不应该提供。
2、在复杂排序条件下,就失去了一个可比较值的判断了。
01/25 09:53
回复
举报
刚刚得知DBA的同志是给offset有一个最大值,超过这个最大的值就返回空数据。
01/27 17:23
回复
举报
......实际业务比这复杂的多了,这种理想状态还是看看算了
01/25 09:51
回复
举报
能有多复杂,分页本身就是一个鸡肋问题,在完美的方案在大数据量面前就是个弟弟。刚刚得知DBA的同志是给offset有一个最大值,超过这个最大的值就返回空数据。
01/27 17:24
回复
举报
业务复杂,就以你说的这个例子,图书管理员想看到每一天每个用户的最后一条借书记录,..,在5亿条数据中..
01/29 09:21
回复
举报
那就按照这个方案啊!where < id 多少数据都不怕的
01/29 10:15
回复
举报
一个用户可不止一条借书记录啊
02/04 16:10
回复
举报
标题党
01/25 09:47
回复
举报
谢谢夸奖
01/25 22:52
回复
举报
只要不跳页,还是很实用的,实现简单。
01/24 19:50
回复
举报
对的
01/27 17:20
回复
举报
缺点是页面打开以后的新增数据看不到
01/24 18:36
回复
举报
为啥看不到呢
01/25 22:53
回复
举报
皮毛,有个问题是,我要是需要支持 field1,field2,field3 字段排序,也就类似于,当前页显示10000行,我需要这个三个字段都能分别进行排序,和支持下一页,或者其中某一页。你的还能行吗
01/23 23:36
回复
举报
回家洗洗睡啊
01/24 08:15
回复
举报
不过还是支持你坚持创作。
01/24 10:14
回复
举报
这样说话就喜欢,不喜勿喷,我们还是好朋友
01/27 17:24
回复
举报
问题主键不是自增而是uuid就不行了
01/23 20:15
回复
举报
对的嘞
01/24 08:16
回复
举报
更多评论
打赏
54 评论
31 收藏
6
分享
返回顶部
顶部