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

　 短视频，自媒体，达人种草一站处事

这篇文章首要先容了MySQL limit分页大偏移量慢的缘故起因及优化方案，辅佐各人更好的领略和行使MySQL数据库，感乐趣的伴侣可以相识下

在 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<=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;

可以看到，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 以及执行时刻如下表。

公然，执行服从获得了明显晋升。

前提过滤

虽然尚有一种有缺陷的要领是基于排序做前提过滤。

好比像上面的示例 user 表，我要行使 limit 分页获得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，可是条件是这个字段是成立了索引的。

总之，行使前提过滤的方法来优化 limit 是有诸多限定的，一样平常照旧保举行使包围索引的方法来优化。

小结

首要说明白 limit 分页大偏移量慢的缘故起因，同时也提出了相应的优化方案，保举行使包围索引的方法来优化 limit 分页大偏移执行时刻久的题目。

但愿能辅佐到各人。

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!