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列可能出现的值及含义:

  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 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快速分页的资料请关注www.887551.com其它相关文章!

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐