本文主要介绍如何快速对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搜索引擎，深度分页的问题仍然可能出现。那我们该怎么办？答案是通过光标滚动。这一点我在这里不深究，有兴趣的可以做研究。
小结
写这篇博客是因为前段时间在开发中确实经历过，之前在字节面试中也确实和面试官讨论过。知道极限的极限和优化，在面试中提一下是加分项。如果不能说MySQL优化就是建立索引和调整SQL(其实这两种优化方案在实际开发中作用不大)。毕竟如果MySQL优化这么牛逼，就不会有那么多中间件了。
我是少侠路飞，热爱技术和分享。
以上是MySQL千万级数据如何快速分页的细节。更多关于MySQL快速分页的信息，请关注我们的其他相关文章！
郑重声明：本文由网友发布，不代表盛行IT的观点，版权归原作者所有，仅为传播更多信息之目的，如有侵权请联系，我们将第一时间修改或删除，多谢。