MySQL千万级数据量如何快速分页

作者:admin 发布时间:2020-09-16 20:47:27 浏览:1296次
后端开发中为了防止一次性加载太多数据导致内存、磁盘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)。 图片.png 这里我们再看下执行计划,如下图所示: 图片.png
在此先介绍一下执行计划Extra列可能出现的值及含义:
  1. Using where:表示优化器需要通过索引回表查询数据。
  2. Using index:即覆盖索引,表示直接访问索引就足够获取到所需要的数据,不需要通过索引回表,通常是通过将待查询字段建立联合索引实现。
  3. Using index condition:在5.6版本后加入的新特性,即大名鼎鼎的索引下推,是MySQL关于减少回表次数的重大优化。
  4. Using filesort:文件排序,这个一般在ORDER BY时候,数据量过大,MySQL会将所有数据召回内存中排序,比较消耗资源。
再看看上图,同样的语句,只因为偏移量不同,就造成了执行计划的千差万别(且容我小小的夸张一下)。第一条语句LIMIT 100,6type列的值是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 desc LIMIT 6;
如上代码所示,同样也是分页,但是有个maxId的限制条件,这个是什么意思呢,maxId就是上一页中的最大主键Id。所以采用此方式的前提:1)主键必须自增不能是UUID并且前端除了传基本分页参数pageNo,pageSize外,还必须把每次上一页的最大Id带过来,2)该方式不支持随机跳页,也就是说只能上下翻页。如下图所示是某知名电商中的实际页面。 image.png 二是通过Elastic Search搜索引擎优化(基于倒排索引),实际上类似于淘宝这样的电商基本上都是把所有商品放进ES搜索引擎里的(那么海量的数据,放进MySQL是不可能的,放进Redis也不现实)。但即使用了ES搜索引擎,也还是有可能发生深度分页的问题的,这时怎么办呢?答案是通过游标scroll。关于此点这里不做深入,感兴趣的可以做研究。

如需转载请保留本文出处: https://www.zhe94.com/789.html

上一篇:快速建站之前言:如何建立一个自己的网站
下一篇:HTTPS 的实现原理是什么?精美详尽的HTTPS图解
[uni-app]微信小程序隐私保护指引用户同意窗口代码

[uni-app]微信小程序隐私保护指引用户同意窗口代码

为规范开发者的用户个人信息处理行为,保障用户合法权益,微信要求开发者主动同步微信当前用户已阅读并同意小程序的隐私政策等收集使用规则,方可调用微信提供的隐私接口。
vscode右键运行插件—code runner

vscode右键运行插件—code runner

在VS Code里,能有一种快捷的方式来运行各类的代码,甚至是代码片段。就好了,Code Runner插件支持了 Node.js, Python, C++, Java, PHP, Perl, Ruby, Go等超过40种的语言。下面,我们就来看看如何来玩转Code Runner,提高你的效率。
php中的两个冒号是什么意思?例如Request::header('token')

php中的两个冒号是什么意思?例如Request::header('token')

在PHP中,双冒号(::)被称为范围解析操作符(Scope Resolution Operator),也有时被称为静态调用运算符。它用于引用类中的静态属性、静态方法或常量。
php __construct是什么意思

php __construct是什么意思

在PHP中,__construct是一个魔术方法(Magic Method),用于类的构造函数。当使用new关键字实例化一个对象时,会自动调用该方法。
thinkphp6.*版本的无缝升级到thinkphp8.0版本实操方法

thinkphp6.*版本的无缝升级到thinkphp8.0版本实操方法

ThinkPHP是一个免费开源的,快速、简单的面向对象的轻量级PHP开发框架,是为了敏捷WEB应用开发和简化企业应用开发而诞生的。ThinkPHP诞生十七年来一直秉承简洁实用的设计原则,在保持出色的性能和至简代码的同时,更注重易用性。
php出现SSL certificate problem: unable to get local issuer certificate的解决办法

php出现SSL certificate problem: unable to get local issuer certificate的解决办法

在PHP中使用cURL或其他网络操作时,可能会遇到一个常见的SSL证书问题,错误信息为“SSL certificate problem: unable to get local issuer certificate”。翻译过来的意思是:“SSL证书问题:无法获取本地颁发者证书”,这是因为PHP试图验证SSL证书,但是无法找到用来验证这些证书的CA(证书颁发机构)证书。