mysql 千万级数据量如何快速分页排序,mysql 百万数据分页

mysql 千万级数据量如何快速分页排序,mysql 百万数据分页,MySQL 千万级数据量如何快速分页

本文主要介绍如何快速对MySQL上千万的数据进行分页，帮助你提高MySQL数据库的性能。感兴趣的朋友可以了解一下。

前言

在后端开发中，为了防止一次加载太多数据导致内存和磁盘IO开销过大，往往需要显示页面。这时候就需要使用MySQL的LIMIT关键字了。但是你认为使用有限分页就万事大吉了吗？太年轻，太单纯。当有大量数据时，LIMIT带来的一个问题是深度分页。

案例

在这里，我以展示电商订单明细为背景，给大家举个例子。新表格如下：

创建表` cps_user_order_detail `(

` id ` bigint(20)unsigned not null auto _ increment注释 primary key ，

` user _ ID ` varchar(32)not null default 注释用户id ，

` order _ id` bigint (20)默认空注释订单id ，

` SKU _ ID ` Bigint(20)unsigned not null注释商品ID ，

` order _ time` datetime默认空注释订单时间，格式yyyy-MM-dd HH:mm:ss ，

主键(` id `)，

使用BTREE键入` idx_time_user` (`order_time `，` user_id `)

)engine=innodb default charset=ut F8 MB 4 collate=ut F8 MB 4 _ bin comment=用户订单详情；

然后手动将120W条数据插入表中。

现在有一个需求：页面显示用户订单的详细信息，按照订单时间倒序显示。

表格结构简化，要求简单。于是我慌慌张张的写了代码，测试上线了。前期一切运行良好，但随着订单量越来越大，系统越来越慢，时不时报出几个慢的查询。

这时候你就要想到极限偏移的问题了。没错，要么是你的SQL不够漂亮，要么是MySQL自己的机制。

这里我就简单拿两段SQL作为例子，如下图所示。分页分别偏移了100和100W的位置，可以看到时间相差很大。这还不算其他数据操作和处理的时间。单个SQL的查询需要一秒以上，这在提供给用户的功能中是不能容忍的(电商公司往往要求一个界面的RT不能超过200ms)。

我们再来看一下实现计划，如下图所示：

这里，我们先介绍一下执行计划的额外列的可能值和含义：

Using where:表示优化器需要通过索引回表来查询数据。

使用索引：覆盖索引，即直接访问索引就足以获得所需的数据，而不需要通过索引返回表，通常通过建立待查询字段的联合索引来实现。

使用索引条件：5.6版本后增加的一个新特性，即著名的索引下推，是MySQL在减少表返回次数上的一个重大优化。

使用文件排序：文件排序。一般ORDER BY，数据量太大，MySQL会把所有数据回调到内存进行排序，会消耗更多的资源。

看上图，同样的语句，只是因为偏移量不一样，导致执行计划差别很大(也允许我夸张一点)。在第一个语句中，LIMIT 100，6type列的值是range，表示范围扫描。性能比ref差一个级别，但是也去掉了索引，也应用了索引下推：也就是说在WHERE之后排序的时候删除并选择了索引，后续的ORDER BY也是根据索引下推进行优化，并在过滤WHERE条件时进行同步(不返回表)。

第二个语句，LIMIT 1000000，6，根本不取索引。type列的值是ALL，显然是全表扫描。在额外的列字段中使用where表示返回表，使用filesort表示当order by时对文件进行排序。所以这里有两个慢：一是文件排序时间太长；第二，按照条件过滤完相关数据后，需要按照偏移量把所有的值取回到表中。无论以上几点，极限偏移量都太大，所以实际开发环境往往满足非统计表量级不能超过一百万的要求。

优化

分析原因后，实际开发中如何优化极限深度分页？在此，少侠给出两个解决方案。

首先，通过主键索引进行优化。你什么意思？将上面的语句修改为：

SELECT * FROM CPS _ user _ ORDER _ detail d其中d.id #{maxId}和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)这种方法不支持随机跳转页面，也就是说只能上下翻页。下图是某知名电商的实际页面。

第二，通过弹性搜索搜索引擎(基于倒排索引)，其实像淘宝这样的电商基本上是把所有的商品都放到es搜索引擎里(这么海量的数据，不可能放到MySQL，放到Redis也不现实)。但是，即使使用ES搜索引擎，深度分页的问题仍然可能出现。那我们该怎么办？答案是通过光标滚动。这一点我在这里不深究，有兴趣的可以做研究。