关于java:MySQL深度分页的问题及优化方案千万级数据量如何快速分页

60次阅读

共计 2268 个字符，预计需要花费 6 分钟才能阅读完成。

后端开发中为了避免一次性加载太多数据导致内存、磁盘 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,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 LIMIT 6;

如上代码所示，同样也是分页，然而有个 maxId 的限度条件，这个是什么意思呢，maxId 就是上一页中的最大主键 Id。所以采纳此形式的前提：1）主键必须自增不能是 UUID 并且前端除了传根本分页参数 pageNo,pageSize 外，还必须把每次上一页的最大 Id 带过去，2）该形式不反对随机跳页，也就是说只能高低翻页。如下图所示是某出名电商中的理论页面。

二是通过 Elastic Search 搜索引擎优化（基于倒排索引），实际上相似于淘宝这样的电商基本上都是把所有商品放进 ES 搜索引擎里的（那么海量的数据，放进 MySQL 是不可能的，放进 Redis 也不事实）。但即应用了 ES 搜索引擎，也还是有可能产生深度分页的问题的，这时怎么办呢？答案是通过游标 scroll。对于此点这里不做深刻，感兴趣的能够做钻研。

写这篇博客是因为前段时间在开发中实在经验到了，并且之前在字节面试中的确也和面试官探讨了一番。晓得 LIMIT 的限度以及优化，在面试中能提到是加分项，不能说到 MySQL 优化就是建索引，调整 SQL（实际上在实在开发中这两种优化计划的功效微不足道）。毕竟 MySQL 优化那么牛 X 的话，就不会有那么多中间件产生了。
我是少侠露飞，爱技术，爱分享。

正文完