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 深度分页内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • mysql千万级数据分页查询性能优化

    mysql数据量大时使用limit分页,随着页码的增大,查询效率越低下. 实验 1.直接使用用limit start, count分页语句: select * from order limit start, count 当起始页较小时,查询没有性能问题,我们分别看下从10, 100, 1000, 10000开始分页的执行时间(每页取20条), 如下: select * from order limit 10, 20 0.016秒 select * from order limit 100, 20

  • MySQL 百万级分页优化(Mysql千万级快速分页)

    以下分享一点我的经验 一般刚开始学SQL的时候,会这样写 复制代码 代码如下: SELECT * FROM table ORDER BY id LIMIT 1000, 10; 但在数据达到百万级的时候,这样写会慢死 复制代码 代码如下: SELECT * FROM table ORDER BY id LIMIT 1000000, 10; 也许耗费几十秒 网上很多优化的方法是这样的 复制代码 代码如下: SELECT * FROM table WHERE id >= (SELECT id FROM

  • MySQL 千万级数据量如何快速分页

    前言 后端开发中为了防止一次性加载太多数据导致内存.磁盘IO都开销过大,经常需要分页展示,这个时候就需要用到MySQL的LIMIT关键字.但你以为LIMIT分页就万事大吉了么,Too young,too simple啊,LIMIT在数据量大的时候极可能造成的一个问题就是深度分页. 案例 这里我以显示电商订单详情为背景举个例子,新建表如下: CREATE TABLE `cps_user_order_detail` ( `id` bigint(20) unsigned NOT NULL AUTO_I

  • 如何优化Mysql千万级快速分页

    看例子: 数 据表 collect ( id, title ,info ,vtype) 就这4个字段,其中 title 用定长,info 用text, id 是逐渐,vtype是tinyint,vtype是索引.这是一个基本的新闻系统的简单模型.现在往里面填充数据,填充10万篇新闻. 最后collect 为 10万条记录,数据库表占用硬盘1.6G. OK ,看下面这条sql语句: select id,title from collect limit 1000,10; 很快:基本上0.01秒就OK

  • Mysql limit 优化，百万至千万级快速分页 复合索引的引用并应用于轻量级框架

    MySql 这个数据库绝对是适合dba级的高手去玩的,一般做一点1万篇新闻的小型系统怎么写都可以,用xx框架可以实现快速开发.可是数据量到了10万,百万至千万,他的性能还能那么高吗?一点小小的失误,可能造成整个系统的改写,甚至更本系统无法正常运行!好了,不那么多废话了.用事实说话,看例子: 数据表 collect ( id, title ,info ,vtype) 就这4个字段,其中 title 用定长,info 用text, id 是逐渐,vtype是tinyint,vtype是索引.这是一个

  • MySQL深度分页(千万级数据量如何快速分页)

    MySQL深度分页(千万级数据量如何快速分页)

    目录 前言 案例 优化 小结 前言 后端开发中为了防止一次性加载太多数据导致内存.磁盘IO都开销过大,经常需要分页展示,这个时候就需要用到MySQL的LIMIT关键字.但你以为LIMIT分页就万事大吉了么,Too young,too simple啊,LIMIT在数据量大的时候极可能造成的一个问题就是深度分页. 案例 这里我以显示电商订单详情为背景举个例子,新建表如下: CREATE TABLE `cps_user_order_detail` ( `id` bigint(20) unsigned

  • mysql千万级数据量根据索引优化查询速度的实现

    (一)索引的作用 索引通俗来讲就相当于书的目录,当我们根据条件查询的时候,没有索引,便需要全表扫描,数据量少还可以,一旦数据量超过百万甚至千万,一条查询sql执行往往需要几十秒甚至更多,5秒以上就已经让人难以忍受了. 能在软件上解决的,就不在硬件上解决,毕竟硬件提升代码昂贵,性价比太低.代价小且行之有效的解决方法就是合理的加索引.索引使用得当,能使查询速度提升上千倍,效果惊人. (二)mysql的索引类型: mysql的索引有5种:主键索引.普通索引.唯一索引.全文索引.聚合索引(多列索引).

  • 详解MySQL数据库千万级数据查询和存储

    百万级数据处理方案 数据存储结构设计 表字段设计 表字段 not null,因为 null 值很难查询优化且占用额外的索引空间,推荐默认数字 0. 数据状态类型的字段,比如 status, type 等等,尽量不要定义负数,如 -1.因为这样可以加上 UNSIGNED,数值容量就会扩大一倍. 可以的话用 TINYINT.SMALLINT 等代替 INT,尽量不使用 BIGINT,因为占的空间更小. 字符串类型的字段会比数字类型占的空间更大,所以尽量用整型代替字符串,很多场景是可以通过编码逻辑来实

  • MySQL循环插入千万级数据

    1.创建测试表 CREATE TABLE `mysql_genarate` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uuid` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5999001 DEFAULT CHARSET=utf8; 2.创建一个循环插入的存储过程 CREATE DEFINER=`root`@`localhost` PROCEDURE

  • MySQL百万级数据量分页查询方法及其优化建议

    数据库SQL优化是老生常谈的问题,在面对百万级数据量的分页查询,又有什么好的优化建议呢?下面将列举了一些常用的方法,供大家参考学习! 方法1: 直接使用数据库提供的SQL语句 语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N 适应场景: 适用于数据量较少的情况(元组百/千级) 原因/缺点: 全表扫描,速度会很慢 且 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3). Limit限制的是从结果集的M位置处取出N条输出,其余

  • MySQL千万级数据表的优化实战记录

    MySQL千万级数据表的优化实战记录

    前言 这里先说明一下,网上很多人说阿里规定500w数据就要分库分表.实际上,这个500w并不是定义死的,而是与MySQL的配置以及机器的硬件有关.MySQL为了提升性能,会将表的索引装载到内存中.但是当表的数据到达一定的量的时候,会导致内存无法存储这些索引,无法存储索引,就只能进行磁盘IO,从而导致性能下降. 实战调优 我这里有张表,数据有1000w,目前只有一个主键索引 CREATE TABLE `user` ( `id` int(10) NOT NULL AUTO_INCREMENT, `u

  • MySQL千万级数据的大表优化解决方案

    目录 1.数据库设计和表创建时就要考虑性能 设计表时要注意: 索引 简言之就是使用合适的数据类型,选择合适的索引 引擎 2.sql的编写需要注意优化 3.分区 分区的好处是: 分区的限制和缺点: 分区的类型: 4.分表 5.分库 mysql数据库中的表数据量几千万后,查询速度会很慢,日常各种卡慢,严重影响使用体验.在考虑升级数据库或者换用大数据解决方案前,必须优化现有mysql数据库表设计和sql语句. 1.数据库设计和表创建时就要考虑性能 mysql数据库本身高度灵活,造成性能不足,严重依赖开

随机推荐