MySQL深度分页(千万级数据量如何快速分页)
作者:少侠露飞 发布时间:2024-01-26 12:33:54
前言
后端开发中为了防止一次性加载太多数据导致内存、磁盘IO都开销过大,经常需要分页展示,这个时候就需要用到MySQL的LIMIT关键字。但你以为LIMIT分页就万事大吉了么,Too young,too simple啊,LIMIT在数据量大的时候极可能造成的一个问题就是深度分页。
案例
这里我以显示电商订单详情为背景举个例子,新建表如下:
CREATE TABLE `cps_user_order_detail` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_id` varchar(32) NOT NULL DEFAULT '' COMMENT '用户ID',
`order_id` bigint(20) DEFAULT NULL COMMENT '订单id',
`sku_id` bigint(20) unsigned NOT NULL COMMENT '商品ID',
`order_time` datetime DEFAULT NULL COMMENT '下单时间,格式yyyy-MM-dd HH:mm:ss',
PRIMARY KEY (`id`),
KEY `idx_time_user` (`order_time`,`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户订单详情';
然后手动向表里插入120W条数据。
现在有个需求:分页展示用户的订单详情,按照下单时间倒序。
表结构精简了,需求也简单。于是哗哗哗的写完代码,提测上线了。早期运行一切正常,可随着订单量的不断增大,发现系统越发的缓慢,还时不时报出几个慢查询
。
这个时候你就该想到是LIMIT偏移的问题了,没错,不是你的SQL不够优美,就是MySQL自身的机制。
这里我就简单以两条SQL为例,如下图,分别是从100和100W的位置偏移分页,可以看到时间相差很大。这还不算其它数据运算和处理的时间,单一条SQL的查询就耗时一秒以上,在对用户提供的功能里这是不能容忍的(电商里经常要求一个接口的RT不超过200ms)。
这里我们再看下执行计划,如下图所示:
在此先介绍一下执行计划Extra列可能出现的值及含义:
Using where:表示优化器需要通过索引回表查询数据。
Using index:即覆盖索引,表示直接访问索引就足够获取到所需要的数据,不需要通过索引回表,通常是通过将待查询字段建立联合索引实现。
Using index condition:在5.6版本后加入的新特性,即大名鼎鼎的索引下推,是MySQL关于
减少回表次数
的重大优化。Using filesort:文件排序,这个一般在ORDER BY时候,数据量过大,MySQL会将所有数据召回内存中排序,比较消耗资源。
再看看上图,同样的语句,只因为偏移量不同,就造成了执行计划的千差万别(且容我小小的夸张一下)。第一条语句LIMIT 100,6
type列的值是range
,表示范围扫描,性能比ref
差一个级别,但是也算走了索引,并且还应用了索引下推:就是说在WHERE之后的下单时间删选走了索引,并且之后的ORDER BY也是根据索引下推优化,在执行WHERE条件筛选时同步进行的(没有回表)。
而第二条语句LIMIT 1000000,6
压根就没走索引,type列的值是ALL
,显然是全表扫描。并且Extra列字段里的Using where表示发生了回表,Using filesort表示ORDER BY时发生了文件排序。所以这里慢在了两点:一是文件排序耗时过大,二是根据条件筛选了相关的数据之后,需要根据偏移量回表获取全部值。无论是上面的哪一点,都是LIMIT偏移量过大导致的,所以实际开发环境经常遇到非统计表量级不得超过一百万的要求。
优化
原因分析完了,那么LIMIT深度分页在实际开发中怎么优化呢?这里少侠给两点方案。
一是通过主键索引优化。什么意思呢?就是把上面的语句修改成:
SELECT * FROM cps_user_order_detail d WHERE d.id > #{maxId} AND d.order_time>'2020-8-5 00:00:00' ORDER BY d.order_time LIMIT 6;
如上代码所示,同样也是分页,但是有个maxId的限制条件,这个是什么意思呢,maxId就是上一页中的最大主键Id。所以采用此方式的前提:1)主键必须自增不能是UUID并且前端除了传基本分页参数pageNo,pageSize外,还必须把每次上一页的最大Id带过来,2)该方式不支持随机跳页,也就是说只能上下翻页。如下图所示是某知名电商中的实际页面。
二是通过Elastic Search搜索引擎优化(基于倒排索引),实际上类似于淘宝这样的电商基本上都是把所有商品放进ES搜索引擎里的(那么海量的数据,放进MySQL是不可能的,放进Redis也不现实)。但即使用了ES搜索引擎,也还是有可能发生深度分页的问题的,这时怎么办呢?答案是通过游标scroll。关于此点这里不做深入,感兴趣的可以做研究。
小结
写这篇博客是因为前段时间在开发中真实经历到了,并且之前在字节面试中确实也和面试官探讨了一番。知道LIMIT的限制以及优化,在面试中能提到是加分项,不能说到MySQL优化就是建索引,调整SQL(实际上在真实开发中这两种优化方案的成效微乎其微)。毕竟MySQL优化那么牛X的话,就不会有那么多中间件产生了。
来源:https://blog.csdn.net/Carson_Chu/article/details/108445426
猜你喜欢
- 本文实例讲述了Python使用xlrd模块操作Excel数据导入的方法。分享给大家供大家参考。具体分析如下:xlrd是一个基于python的
- 需求如下: 1.模板页右边包含了一个登陆div,想让没登陆的时候这个div显示,登陆后该div隐藏 2.显示一个欢迎用户的div,主要是想通
- 本文只考虑模板中的字符串,不考虑字符串中带标签的情况。模板中的字符串文字不会自动转义,因为这里默认模板的作者已经正确书写模板的内容。{{ d
- 我们不可能直接通过node命令来管理远程站点,这样无法保证网站的可持续运行。我们用Forever来解决这个问题,它可以将NodeJS应用以后
- 当然是可以的,而且非常简单,今天就教大家在ASP中不用模板生成HTML静态页的方法。这里假设有一个htmer.asp动态页面,你想把它生成为
- golang 的fmt 包实现了格式化I/O函数,类似于C的 printf 和 scanf定义示例类型和变量type Human struc
- 本文讲述了Python多进程同步简单实现代码。分享给大家供大家参考,具体如下:#encoding=utf8from multiprocess
- 例表:假如想要去掉表中的‘#',‘;'而且以‘#'和‘;'为分割线切割数据:#将dfxA_2的每一个分隔符之
- NumPy矩阵乘法矩阵乘法是将两个矩阵作为输入值,并将 A 矩阵的行与 B 矩阵的列对应位置相乘再相加,从而生成一个新矩阵,如下图所示:注意
- 首先:确认自己安装有python(没有安装的同学可自行安装,这里我们不过多赘述)确认安装有python之后,win+r打开cmd窗口,输入p
- Golang 的 1.13 版本 与 1.14 版本对 defer 进行了两次优化,使得 defer 的性能开销在大部分场景下都得到大幅降低
- 方法方法能给用户自定义的类型添加新的行为。它和函数的区别在于方法有一个接收者,给一个函数添加一个接收者,那么它就变成了方法。接收者可以是值接
- 本文实例为大家分享了JavaScript实现网页版计算器的具体代码,供大家参考,具体内容如下由于无聊看电脑上的系统软件翻到了计算器这个功能,
- 我们有时候为了测试会虚拟创建一些用户,这时候我们可能会通过写sql脚本的形式,但如果没有理清增加用户的注意事项等,往往造出来的数据不能用,比
- 何时使用单体 RESTful 服务对于很多初创公司来说,业务的早期我们更应该关注于业务价值的交付,而单体服务具有架构简单,部署简单,开发成本
- 一、join函数(一)参数使用说明描述Python join() 方法用于将序列中的元素以指定的字符连接生成一个新的字符串。语法join()
- 一、卷积神经网络Yann LeCun 和Yoshua Bengio在1995年引入了卷积神经网络,也称为卷积网络或CNN。CNN是一种特殊的
- 前几天看到某论坛有人提了这么个问题,Python这么火,为啥找工作这么难呢? 这两年因为第三波人工智能热潮让 Python火了一把
- 背景测试工具箱写到一半,今天遇到了一个前后端数据交互的问题,就一起做一下整理。环境-----------------------------
- 本文转自:https://blog.csdn.net/qq_42730750/article/details/108415551前言 各大