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<=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:

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 分页的资料请关注我们其它相关文章!

(0)

相关推荐

  • Mysql排序和分页(order by&limit)及存在的坑

    排序查询(order by) 电商中:我们想查看今天所有成交的订单,按照交易额从高到低排序,此时我们可以使用数据库中的排序功能来完成. 排序语法: select 字段名 from 表名 order by 字段1 [asc|desc],字段2 [asc|desc]; 需要排序的字段跟在order by之后: asc|desc表示排序的规则,asc:升序,desc:降序,默认为asc: 支持多个字段进行排序,多字段排序之间用逗号隔开. 单字段排序 mysql> create table test2(

  • MySQL Limit性能优化及分页数据性能优化详解

    MySQL Limit可以分段查询数据库数据,主要应用在分页上.虽然现在写的网站数据都是千条级别,一些小的的优化起的作用不大,但是开发就要做到极致,追求完美性能.下面记录一些limit性能优化方法. Limit语法: SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset LIMIT子句可以被用于强制 SELECT 语句返回指定的记录数.LIMIT接受一个或两个数字参数.参数必须是一个整数常量. 如果给定两个参数,第一个参数指定

  • MySQL分页Limit的优化过程实战

    前言 在我们使用查询语句的时候,经常要返回前几条或者中间某几行数据,这个时候怎么办呢?不用担心,mysql已经为我们提供了这样一个功能. SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数.LIMIT 接受一个或两个数字参数.参数必须是一个整数常量.如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目.初始记录行的偏移

  • MYSQL分页limit速度太慢的优化方法

    在mysql中limit可以实现快速分页,但是如果数据到了几百万时我们的limit必须优化才能有效的合理的实现分页了,否则可能卡死你的服务器哦.    当一个表数据有几百万的数据的时候成了问题! 如 * from table limit 0,10 这个没有问题 当 limit 200000,10 的时候数据读取就很慢,可以按照一下方法解决     第一页会很快    PERCONA PERFORMANCE CONFERENCE 2009上,来自雅虎的几位工程师带来了一篇"EfficientPag

  • 浅谈mysql使用limit分页优化方案的实现

    Mysql limit分页语句用法 与Oracle和MS SqlServer相比,mysql的分页方法简单的让人想哭. --语法: SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset --举例: select * from table limit 5; --返回前5行 select * from table limit 0,5; --同上,返回前5行 select * from table limit 5,10; --返回6

  • MySQL limit使用方法以及超大分页问题解决

    前言 日常开发中,我们使用mysql来实现分页功能的时候,总是会用到mysql的limit语法.而怎么使用却很有讲究的,今天来总结一下. limit语法 limit语法支持两个参数,offset和limit,前者表示偏移量,后者表示取前limit条数据. 例如: ## 返回符合条件的前10条语句 select * from user limit 10 ## 返回符合条件的第11-20条数据 select * from user limit 10,20 从上面也可以看出来,limit n 等价于l

  • mysql limit 分页的用法及注意要点

    mysql limit 分页的用法及注意事项: 在我们使用查询语句的时候,经常要返回前几条或者中间某几行数据,这个时候怎么办呢?不用担心,mysql已经为我们提供了这样一个功能. SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数.LIMIT 接受一个或两个数字参数.参数必须 是一个整数常量.如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参

  • 详解MySQL的limit用法和分页查询语句的性能分析

    limit用法 在我们使用查询语句的时候,经常要返回前几条或者中间某几行数据,这个时候怎么办呢?不用担心,mysql已经为我们提供了这样一个功能. SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数.LIMIT 接受一个或两个数字参数.参数必须是一个整数常量.如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目.初始记

  • MySQL用limit方式实现分页的实例方法

    一.limit 基本实现方式 一般情况下,客户端通过传递 pageNo(页码).pageSize(每页条数)两个参数去分页查询数据库中的数据,在数据量较小(元组百/千级)时使用 MySQL自带的 limit 来解决这个问题: 收到客户端{pageNo:1,pagesize:10} select * from table limit (pageNo-1) * pageSize, pageSize; 收到客户端{pageNo:5,pageSize:30} select * from table li

  • 浅谈MySQL分页Limit的性能问题

    MySQL的分页查询通常通过limit来实现.limit接收1或2个整数型参数,如果是2个参数,第一个是指定第一个返回记录行的偏移量,第二个是返回记录行的最大数目.初始记录行的偏移量是0.为了与PostgreSQL兼容,limit也支持limit # offset #. 问题: 对于小的偏移量,直接使用limit来查询没有什么问题,但随着数据量的增大,越往后分页,limit语句的偏移量就会越大,速度也会明显变慢. 优化思想:避免数据量大时扫描过多的记录 解决:子查询的分页方式或者JOIN分页方式

随机推荐