MySQL深度分页(千万级数据量怎样快速分页)

显示全部楼层

前言
案例
优化
小结

前言

后端开发中为了防止一次性加载太多数据导致内存、磁盘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的话，就不会有那么多中心件产生了。

到此这篇关于MySQL深度分页(万万级数据量如何快速分页)的文章就先容到这了,更多干系MySQL 深度分页内容请搜索草根技能分享以前的文章或继承浏览下面的干系文章希望大家以后多多支持草根技能分享！

工作时间

工作时间

MySQL深度分页(千万级数据量怎样快速分页)

本帖子中包含更多资源

火星云矿 | 预约S19Pro，享500抵1000！