您的位置:首页 > 数据库 > > 正文

mysql limit越大越慢(MySQL limit分页大偏移量慢的原因及优化方案)

更多 时间:2022-03-30 09:25:27 类别:数据库 浏览量:2495

mysql limit越大越慢

MySQL limit分页大偏移量慢的原因及优化方案

在 MySQL 中通常我们使用 limit 来完成页面上的分页功能,但是当数据量达到一个很大的值之后,越往后翻页,接口的响应速度就越慢。

本文主要讨论 limit 分页大偏移量慢的原因及优化方案,为了模拟这种情况,下面首先介绍表结构和执行的 SQL。

场景模拟

建表语句

user 表的结构比较简单,id、sex 和 name,为了让 SQL 的执行时间变化更加明显,这里有9个姓名列。

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • CREATE TABLE `user` (
  •  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  •  `sex` tinyint(4) NULL DEFAULT NULL COMMENT '性别 0-男 1-女',
  •  `name1` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  •  `name2` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  •  `name3` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  •  `name4` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  •  `name5` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  •  `name6` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  •  `name7` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  •  `name8` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  •  `name9` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  •  PRIMARY KEY (`id`) USING BTREE,
  •  INDEX `sex`(`sex`) USING BTREE
  • ) ENGINE = InnoDB AUTO_INCREMENT = 9000001 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  • 数据填充

    这里建立了一个存储过程来进行数据的填充,一共9000000条数据,执行完函数后再执行一句SQL,修改性别字段。

    ps:这个函数执行的挺久的,我运行了617.284秒。

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • CREATE DEFINER=`root`@`localhost` PROCEDURE `data`()
  • begin
  •  declare i int;
  •  set i=1;
  •  while(i<=9000000)do
  •   insert into user values(i,0,i,i,i,i,i,i,i,i,i);
  •   set i=i+1;
  •  end while;
  • end
  •  
  • -- 将id为偶数的user设置性别为1-女
  • update user set sex=1 where id%2=0;
  • SQL与执行时间

    SQL 执行时间
    select * from user where sex = 1 limit 100, 10; OK, Time: 0.005000s
    select * from user where sex = 1 limit 1000, 10; OK, Time: 0.007000s
    select * from user where sex = 1 limit 10000, 10; OK, Time: 0.016000s
    select * from user where sex = 1 limit 100000, 10; OK, Time: 0.169000s
    select * from user where sex = 1 limit 1000000, 10; OK, Time: 5.892000s
    select * from user where sex = 1 limit 10000000, 10; OK, Time: 33.465000s

    可以看到,limit 的偏移量越大,执行时间越长。

    原因分析

    首先来分析一下这句 SQL 执行的过程,就拿上面表格中的第一行来举例。

    由于 sex 列是索引列,MySQL会走 sex 这棵索引树,命中 sex=1 的数据。

    然后又由于非聚簇索引中存储的是主键 id 的值,且查询语句要求查询所有列,所以这里会发生一个回表的情况,在命中 sex 索引树中值为1的数据后,拿着它叶子节点上的值也就是主键 id 的值去主键索引树上查询这一行其他列(name、sex)的值,最后返回到结果集中,这样第一行数据就查询成功了。

    最后这句 SQL 要求limit 100, 10,也就是查询第101到110个数据,但是 MySQL 会查询前110行,然后将前100行抛弃,最后结果集中就只剩下了第101到110行,执行结束。

    小结一下,在上述的执行过程中,造成 limit 大偏移量执行时间变久的原因有:

    • 查询所有列导致回表
    • limit a, b会查询前a+b条数据,然后丢弃前a条数据

    综合上述两个原因,MySQL 花费了大量时间在回表上,而其中a次回表的结果又不会出现在结果集中,这才导致查询时间变得越来越长。

    优化方案

    覆盖索引

    既然无效的回表是导致查询变慢的主要原因,那么优化方案就主要从减少回表次数方面入手,假设在limit a, b中我们首先得到了a+1到a+b条数据的id,然后再进行回表获取其他列数据,那么就减少了a次回表操作,速度肯定会快上不少。

    这里就涉及到覆盖索引了,所谓的覆盖索引就是从非主聚簇索引中就能查到的想要数据,而不需要通过回表从主键索引中查询其他列,能够显著提升性能。

    基于这样的思路,优化方案就是先查询得到主键id,然后再根据主键id查询其他列数据,优化后的 SQL 以及执行时间如下表。

    优化后的 SQL 执行时间
    select * from user a join (select id from user where sex = 1 limit 100, 10) b on a.id=b.id; OK, Time: 0.000000s
    select * from user a join (select id from user where sex = 1 limit 1000, 10) b on a.id=b.id; OK, Time: 0.00000s
    select * from user a join (select id from user where sex = 1 limit 10000, 10) b on a.id=b.id; OK, Time: 0.002000s
    select * from user a join (select id from user where sex = 1 limit 100000, 10) b on a.id=b.id; OK, Time: 0.015000s
    select * from user a join (select id from user where sex = 1 limit 1000000, 10) b on a.id=b.id; OK, Time: 0.151000s
    select * from user a join (select id from user where sex = 1 limit 10000000, 10) b on a.id=b.id; OK, Time: 1.161000s

    果然,执行效率得到了显著提升。

    条件过滤

    当然还有一种有缺陷的方法是基于排序做条件过滤。

    比如像上面的示例 user 表,我要使用 limit 分页得到1000001到1000010条数据,可以这样写 SQL:

  • ?
  • 1
  • select * from user where sex = 1 and id > (select id from user where sex = 1 limit 1000000, 1) limit 10;
  • 但是使用这样的方式优化是有条件的:主键id必须是有序的。在有序的条件下,也可以使用比如创建时间等其他字段来代替主键id,但是前提是这个字段是建立了索引的。

    总之,使用条件过滤的方式来优化 limit 是有诸多限制的,一般还是推荐使用覆盖索引的方式来优化。

    小结

    主要分析了 limit 分页大偏移量慢的原因,同时也提出了响应的优化方案,推荐使用覆盖索引的方式来优化 limit 分页大偏移执行时间久的问题。

    希望能帮助到大家。

    以上就是MySQL limit分页大偏移量慢的原因及优化方案的详细内容,更多关于MySQL limit 分页的资料请关注开心学习网其它相关文章!

    原文链接:https://javageekers.club/archives/mysql-limit

    标签:mysql 分页 limit
    您可能感兴趣