千万级别的表分页查询非常慢,怎么办?
一、问题复现
在实际的软件系统开发过程中,随着使用的用户群体越来越多,表数据也会随着时间的推移,单表的数据量会越来越大。
以订单表为例,假如每天的订单量在 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 等等。
继续回到我们本文所需要探讨的问题,当单表数据量到达百万级的时候,查询效率急剧下降,如何优化提升呢?
二、解决方案
下面我们一起来看看具体的解决办法。
2.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.2、方案二:查询的时候,通过主键 ID 过滤
这种方案有一个要求就是主键ID,必须是数字类型,实践的思路就是取上一次查询结果的 ID 最大值,作为过滤条件,而且排序字段必须是主键 ID,不然分页排序顺序会错乱。
- 查询 100000~1000100 区间段的数据,仅耗时:18 ms
- 查询 500000~5000100 区间段的数据,仅耗时:18 ms
- 查询 1000000~1000100 区间段的数据,仅耗时:18 ms
可以很清晰的看到,带上主键 ID 作为过滤条件,查询性能非常的稳定,基本上在20 ms
内可以返回。
这种方案还是非常可行的,如果当前业务对排序要求不多,可以采用这种方案,性能也非常杠!
但是如果当前业务对排序有要求,比如通过客户最后修改时间、客户最后下单时间、客户最后下单金额等字段来排序,那么上面介绍的【方案一】,比【方案二】查询效率更高!
2.3、方案三:采用 elasticSearch 作为搜索引擎
当数据量越来越大的时候,尤其是出现分库分表的数据库,以上通过主键 ID 进行过滤查询,效果可能会不尽人意,例如订单数据的查询,这个时候比较好的解决办法就是将订单数据存储到 elasticSearch 中,通过 elasticSearch 实现快速分页和搜索,效果提升也是非常明显。
关于 elasticSearch 的玩法,之前有给大家介绍过具体的实践,这里不在过多撰书。
三、小结
不知道大家有没有发现,上文中介绍的表主键 ID 都是数值类型的,之所以采用数字类型作为主键,是因为数字类型的字段能很好的进行排序。
但如果当前表的主键 ID 是字符串类型,比如 uuid 这种,就没办法实现这种排序特性,而且搜索性能也非常差,因此不建议大家采用 uuid 作为主键ID,具体的数值类型主键 ID 的生成方案有很多种,比如自增、雪花算法等等,都能很好的满足我们的需求。
本文主要围绕大表分页查询性能问题,以及对应的解决方案做了简单的介绍,如果有异议的地方,欢迎网友留言,一起讨论学习!
如果想获取更多的大数据库相关的资料,可以关注下方二维码,后台回复 【cccc】有我准备的一线程序必备计算机书籍、大厂面试资料和免费电子书,希望可以帮助大家提升技术和能力。
相关文章
- 从本体论开始说起——运营商关系图谱的构建及应用
- 如何成为一名数据科学家?
- 从未见过的堂兄杀了人,你的DNA是关键证据
- 20个安全可靠的免费数据源,各领域数据任你挑
- 20个安全可靠的免费数据源,各领域数据任你挑
- 阿里云李飞飞:All in Cloud时代,云原生数据库优势明显
- 基于Hadoop生态系统的一高性能数据存储格式CarbonData(性能篇)
- 大数据告诉你:10年漫威,到底有多少角色
- TigerGraph:实时图数据库助力金融风控升级
- Splunk利用Splunk Connected Experiences和Splunk Business Flow 扩大数据访问
- 大数据开发常见的9种数据分析手段
- 以免在景区看人,我爬了5W条全国景点门票数据...
- 【实战解析】基于HBase的大数据存储在京东的应用场景
- 数据科学家告诉你哪些计算机科学书籍是你应该看的
- Kafka作为大数据的核心技术,你了解多少?
- Spring Boot 整合 Redis 实现缓存操作
- 大数据学习必须掌握的五大核心技术有哪些?
- 基于Antlr在Apache Flink中实现监控规则DSL化的探索实践
- 甲骨文再次被Gartner评为分析型数据管理解决方案魔力象限领导者
- 爬取吴亦凡微博102118条转发数据,扒一扒流量的真假