MySQL limit分页大偏移量的原因分析与优化方案

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

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

场景模拟

建表语句

user 表的结构比较简单，id、sex 和 name，为了让 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;
复制代码

数据填充

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

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

CREATE DEFINER=`root`@`localhost` PROCEDURE `data`()
begin 
  declare i int; 
  set i=1; 
  while(i (select id from user where sex = 1 limit 1000000, 1) limit 10;
复制代码

但是使用这样的方式优化是有条件的：主键id必须是有序的。在有序的条件下，也可以使用比如创建时间等其他字段来代替主键id，但是前提是这个字段是建立了索引的。

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

小结

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