前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >浅谈MySQL分页查询

浅谈MySQL分页查询

作者头像
创译科技
发布2019-10-29 15:46:24
3.7K0
发布2019-10-29 15:46:24
举报
文章被收录于专栏:Node开发

MySQL系列文章到目前已经更新十几篇,从数据类型谈到了备份恢复再到主从同步分库分表,从本篇开始,会花几篇重点谈谈MySQL基础部分,而本篇我们重点来讲讲我们日常开发中最常见的一种查询:分页查询。

首先我们先创建一个订单表,只携带一个主键,其他全部字段都不携带索引。然后使用存储过程插入1000000条数据到数据表中:

说到分页呢?我们都知道使用limit关键字来进行分页,比如我们需要查询id为900000到900100的数据,我们可能会很熟练的这么进行分页:

可以看到我们直接使用limit查询id处于900000到900100范围的数据响应时间为3.51秒,如果再加上业务处理逻辑的操作时间,基本上整个分页查询操作得耗时4秒以上,我们可以使用explain关键字查看刚才这个sql语句的运行属性:

可以看到我们实际上查询只需要100行数据,但是数据库会从第一行开始扫描,一直扫描到我们需要的数据才会停下来。在一个系统中每次查询都扫描几十万行数据,肯定性能大打折扣,因为前面900000行数据对我们来说是无效数据,所以我们首先可以先使用子查询来优化,先使用子查询实现覆盖索引查询返回主键id,再使用主键id和原表做一个关联操作,可以减少回表次数并且减少Mysql扫描那些无效的行数:

我多次测试发现使用子查询优化后,想用的查询结果只需要0.58秒左右。为什么使用子查询能提供这么高的效率呢?因为我们数据表有20多个字段,我们子查询使用select id效率在大数据的情况下大概是select *的3倍性能,而且id是主键可以利用索引优化查询速度,然后外层的查询利用id也可以使用索引加快查询效率,所以显而易见使用子查询进行分页效率比直接使用limit分页效率高很多。

那既然可以利用id主键的性质来提升效率,其实我们可以进一步去掉子查询利用id区间范围去查询数据:

可以看到利用between...and将id局限在指定范围查询分页只需要0.06秒,为什么会这么快呢,我们可以利用explain测试一下运行属性:

可以看到只扫描了100行数据,所以效率自然而然提高了。但是id限制查询都存在一个条件:就是你数据库数据必须按照id连续并且不能中断,这是为什么呢?其实很好理解,数据库比如有100条数据分别id为1---100,如果我要查询第50--60条的数据,我可以使用下列sql语句:

代码语言:javascript
复制
select * from aok_score_info where id between 50 and 60 limit 10;

但是如果中间有一部分无效数据,我需要删除,我将id为30--40的数据删除,这时候我要查询第50--60条的数据,如果还使用上面的sql语句,查出来的一样是id为50--60的数据,但是实际上数据库第50--60条的数据应该id为60--70.所以说id限制分页查询效率很高但是有局限性,局限性就在于不能删除数据以保证id的连续性,而子查询不能使用where子句,因为使用where子句会筛选条件会导致id失去连续性。所以在id能保证连续性的情况下我们可以选择使用限制id的方法提高分页效率。接下来我们看下如果id无法保证绝对连续除了子查询还能如何处理。

首先我们还是以刚才这100万条数据来测试,id目前是连续的从1--100000,我们现在使用inner join在子查询中只查询索引列id,然后通过id去读取需要的列:

可以看到只需要0.735秒,但是很明显我们现在id是连续的。我们现在删除id为10001--13000的3000条数据,然后再测试:

很明显我们可以看到查询出的记录顺延了3000条,也就可以表示我们在id不连续的时候可以使用只读索引方法来优化limit语句,可以大幅度提高分页的效率。而我开发中经常使用一种方法:客户端传id和size表示当前页数id最大值以及每页条数,然后直接使用id查询顺推的size条数据,举个例子比如我们第八页返回id为701--800的数据,然后查询第九页时只需要将第七页最大id800传过来,就可以顺推得到接下来100条数据:

比如客户端查询第一页,id传0,则后端可以使用下列sql语句:

代码语言:javascript
复制
select * from aok_score_info limit 0, 10;

由于查询时第一页的数据,所以扫描速度很快,之后页数查询传当前页数id最大值,比如传参id为9999,表示上一页的最大id为9999,那么我们就从9999开始顺推查询,可以看到中间删掉的3000条记录都会直接跳过,所以不会影响我们查询的正确性,最关键的是id是主键上面有索引,这个sql语句执行时间0.04秒,可以说在高并发的情况下也基本可以满足要求了。这是我个人开发中所理解的几种分页的方案,有不同见解的可以提出来一起讨论讨论

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-10-23,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 程序猿周先森 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档