社区编辑申请
注册/登录
大表分页查询非常慢,怎么办?
数据库 其他数据库
本文主要围绕大表分页查询性能问题,以及对应的解决方案做了简单的介绍。

一、问题复现

在实际的软件系统开发过程中,随着使用的用户群体越来越多,表数据也会随着时间的推移,单表的数据量会越来越大。

以订单表为例,假如每天的订单量在 4 万左右,那么一个月的订单量就是 120 多万,一年就是 1400 多万,随着年数的增加和单日下单量的增加,订单表的数据量会越来越庞大,订单数据的查询不会像最初那样简单快速,如果查询关键字段没有走索引,会直接影响到用户体验,甚至会影响到服务是否能正常运行!

下面我以某个电商系统的客户表为例,数据库是 Mysql,数据体量在 100 万以上,详细介绍分页查询下,不同阶段的查询效率情况(订单表的情况也是类似的,只不过它的数据体量比客户表更大)。

图片图片

下面我们一起来测试一下,每次查询客户表时最多返回 100 条数据,不同的起始下,数据库查询性能的差异。

  • 当起点位置在 0 的时候,仅耗时:18 ms

图片

  • 当起点位置在 1000 的时候,仅耗时:23 ms

图片

  • 当起点位置在 10000 的时候,仅耗时:54 ms

图片图片

  • 当起点位置在 100000 的时候,仅耗时:268 ms

图片图片

  • 当起点位置在 500000 的时候,仅耗时:1.16 s

图片

  • 当起点位置在 1000000 的时候,仅耗时:2.35 s

图片

可以非常清晰的看出,随着起点位置越大,分页查询效率成倍的下降,当起点位置在 1000000 以上的时候,对于百万级数据体量的单表,查询耗时基本上以秒为单位。

而事实上,一般查询耗时超过 1 秒的 SQL 都被称为慢 SQL,有的公司运维组要求的可能更加严格,比如小编我所在的公司,如果 SQL 的执行耗时超过 0.2s,也被称为慢 SQL,必须在限定的时间内尽快优化,不然可能会影响服务的正常运行和用户体验。

对于千万级的单表数据查询,小编我刚刚也使用了一下分页查询,起点位置在 10000000,也截图给大家看看,查询耗时结果:39 秒!

图片图片

没有接触过这么大数据体量的同学,可能多少对这种查询结果会感到吃惊,事实上,这还只是数据库层面的耗时,还没有算后端服务的处理链路时间,以及返回给前端的数据渲染时间,以百万级的单表查询为例,如果数据库查询耗时 1 秒,再经过后端的数据封装处理,前端的数据渲染处理,以及网络传输时间,没有异常的情况下,差不多在 3~4 秒之间,可能有些同学对这个请求时长数值还不太敏感。

据互联网软件用户体验报告,当平均请求耗时在1秒之内,用户体验是最佳的,此时的软件也是用户留存度最高的;2 秒之内,还勉强过的去,用户能接受;当超过 3 秒,体验会稍差;超过 5 秒,基本上会卸载当前软件。

有的公司为了提升用户体验,会严格控制请求时长,当请求时长超过 3 秒,自动放弃请求,从而倒逼技术优化调整 SQL 语句查询逻辑,甚至调整后端整体架构,比如引入缓存中间件 redis,搜索引擎 elasticSearch 等等。

继续回到我们本文所需要探讨的问题,当单表数据量到达百万级的时候,查询效率急剧下降,如何优化提升呢?

二、解决方案

下面我们一起来看看具体的解决办法。

1. 方案一:查询的时候,只返回主键 ID

我们继续回到上文给大家介绍的客户表查询,将select *改成select id,简化返回的字段,我们再来观察一下查询耗时。

  • 当起点位置在 100000 的时候,仅耗时:73 ms

图片

  • 当起点位置在 500000 的时候,仅耗时:274 ms

图片图片

  • 当起点位置在 1000000 的时候,仅耗时:471 ms

图片

可以很清晰的看到,通过简化返回的字段,可以很显著的成倍提升查询效率。

实际的操作思路就是先通过分页查询满足条件的主键 ID,然后通过主键 ID 查询部分数据,可以显著提升查询效果。

-- 先分页查询满足条件的主键ID
select id from bizuser order by id limit 100000,10;

-- 再通过分页查询返回的ID,批量查询数据
select * from bizuser where id in (1,2,3,4,.....);

2. 方案二:查询的时候,通过主键 ID 过滤

这种方案有一个要求就是主键ID,必须是数字类型,实践的思路就是取上一次查询结果的 ID 最大值,作为过滤条件,而且排序字段必须是主键 ID,不然分页排序顺序会错乱。

  • 查询 100000~1000100 区间段的数据,仅耗时:18 ms

图片

  • 查询 500000~5000100 区间段的数据,仅耗时:18 ms

图片

  • 查询 1000000~1000100 区间段的数据,仅耗时:18 ms

图片

可以很清晰的看到,带上主键 ID 作为过滤条件,查询性能非常的稳定,基本上在20 ms内可以返回。

这种方案还是非常可行的,如果当前业务对排序要求不多,可以采用这种方案,性能也非常杠!

但是如果当前业务对排序有要求,比如通过客户最后修改时间、客户最后下单时间、客户最后下单金额等字段来排序,那么上面介绍的【方案一】,比【方案二】查询效率更高!

3. 方案三:采用 elasticSearch 作为搜索引擎

当数据量越来越大的时候,尤其是出现分库分表的数据库,以上通过主键 ID 进行过滤查询,效果可能会不尽人意,例如订单数据的查询,这个时候比较好的解决办法就是将订单数据存储到 elasticSearch 中,通过 elasticSearch 实现快速分页和搜索,效果提升也是非常明显。

关于 elasticSearch 的玩法,之前有给大家介绍过具体的实践,这里不在过多撰书。

三、小结

不知道大家有没有发现,上文中介绍的表主键 ID 都是数值类型的,之所以采用数字类型作为主键,是因为数字类型的字段能很好的进行排序。

但如果当前表的主键 ID 是字符串类型,比如 uuid 这种,就没办法实现这种排序特性,而且搜索性能也非常差,因此不建议大家采用 uuid 作为主键ID,具体的数值类型主键 ID 的生成方案有很多种,比如自增、雪花算法等等,都能很好的满足我们的需求。

责任编辑:赵宁宁 来源: Java极客技术
相关推荐

2020-10-21 18:42:39

数据库数据库查询分页查询

2022-06-06 11:31:31

MySQL数据查询

2022-02-28 08:09:14

sql分页查询

2019-09-11 10:40:49

2021-10-09 06:45:42

2021-05-27 12:46:51

MySQL数据库索引

2011-08-19 09:30:42

分页查询SQL ServerMySQL

2022-04-27 22:07:32

SQL数据库分页查询

2019-11-15 10:01:07

MySQL数据库数据

2009-05-15 10:11:55

数据库查询查询性能分页浏览

2018-09-06 16:46:33

2017-07-25 15:35:07

MysqlMysql优化LIMIT分页

2012-07-23 14:30:33

2010-11-25 14:21:16

2021-09-27 13:33:03

2020-08-06 08:00:51

数据分页优化

2021-09-26 06:43:07

2010-04-16 11:31:29

Oracle分页存储过

2011-03-24 14:15:27

2021-03-11 10:55:41

MySQL数据库索引

同话题下的热门内容

每秒上千次高并发访问,HDFS优雅的抗住了微服务治理热门技术揭秘:动态读写分离我们一起聊聊数据库的可观测性D-SMART如何利用数据库的可观测性能力的图数据库和图计算的区别,终于有人讲明白了

编辑推荐

几款开源的图形化Redis客户端管理软件推荐NoSQL数据库概览及其与SQL语法的比较为什么MongoDB敢说“做以前你从未能做的事”Python操作MongoDB看这一篇就够了一文看懂分布式数据库原理和 PostgreSQL 分布式架构
我收藏的内容
点赞
收藏

51CTO技术栈公众号