MySQL限制分页大偏移的原因分析及优化方案

在MySQL中，我们通常使用limit来完成分页的分页功能，但是当数据量达到一个很大的值时，分页越远，界面响应速度较慢。

本文主要讨论大偏移量限制分页速度慢的原因以及优化方案。为了模拟这种情况，下面首先表示SQL中的表结构。

场景模拟

建表语句

用户表的结构比较简单，ID、性别和姓名。为了使 SQL 执行时的变化更加明显，有 9 个名称列。

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;
复制代码

填充数据

这里创建了一个存储过程来填充数据。总共有9,000,000条数据。执行该函数后，执行SQL语句来更改性别字段。

ps：该函数执行时间较长。我运行了 617,284 秒。

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 和执行时间

可见，limit的偏移量越大，执行时间越长。

原因分析

首先我们来分析一下这条SQL语句的执行过程。以上表第一行为例。

因为性别列是索引列，MySQL会遍历性别索引树，命中性别=1的数据。

那么因为非聚集索引存储的是主键ID的值，而查询语句需要查询所有的列，所以这里会返回一张表。命中性别索引树中值为1的数据后，取其叶子节点上的值，即主键ID的值，去主键索引树中查询其他列的值(name , sex) 这一行，最后返回结果集，这样第一行数据就查询成功了。

最后一条SQL语句需要限制100，10，即101。到110.请求数据，但是MySQL会拒绝10第一行1 0，然后第一行，0和0，最后结果集中只剩下101到110行，并执行超过。

总结一下，在上述执行过程中，偏移量较大的limit执行时间较长的原因有：

• 查询所有列结果返回表
• limit a, b a + b 条数据，然后去掉第一条数据

基于以上两个原因，MySQL 花费了大量的时间来返回表，并且返回的表的结果有一段时间没有出现在结果中set，导致查询时间越来越长。

优化计划

覆盖索引

由于无效的表移动是查询慢的主要原因，因此优化计划主要从减少表移动次数开始。假设限制a,b，在中，我们首先获取a+1到a+b的数据的ID，然后返回表获取其他列数据。这样就减少了返表操作的次数，速度肯定会快很多。

这里是覆盖指数。所谓覆盖索引是指可以从非主聚集索引中找到想要的数据，而不必通过表从主键索引返回其他列，这样可以显着提高性能。 。

基于这个思路，优化方案是先查询得到主键ID，然后根据主键ID查询其他列数据。优化后的SQL和执行时间如下。

果然执行效率明显提升。

条件过滤

当然，还有一种基于排序来制作条件过滤器的有缺陷的方法。

比如上面的示例用户表，我想使用限制分页来获取1000001到1000010条数据。我可以这样写SQL：

select * from user where sex = 1 and id > (select id from user where sex = 1 limit 1000000, 1) limit 10;
复制代码

但是这样优化是有条件的：主键ID必须是有序的。在有序条件下，除了创建时间之外的字段也可以用来替换主键ID，但前提是该字段已建立索引。

总之，使用条件过滤器来优化限制有很多限制。一般建议使用覆盖索引进行优化。

总结

主要分析限制大偏移量寻呼缓慢的原因，同时也提出了响应优化方案。建议使用覆盖索引的方法来优化大偏移量的限制分页执行时间长的问题。

作者：Planswalker23
来源：掘金