文章/答案/技术大牛

发布

社区首页 >专栏 >灵魂两问：MySQL分页有什么性能问题？怎么优化？

灵魂两问：MySQL分页有什么性能问题？怎么优化？

原创

xiao李

发布于 2024-02-03 08:25:45

84300

代码可运行

文章被收录于专栏：我吃香菜青椒不挑食我吃香菜青椒不挑食

运行总次数：0

代码可运行

我们刷网站的时候，我们经常会遇到需要分页查询的场景。

比如下图的翻页功能。

我们很容易能联想到可以用mysql实现。

假设我们的建表sql是这样的

CREATE TABLE `page` (
    `id` INT NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    `user_name` VARCHAR(255) NOT NULL COMMENT '用户名',
    `content` VARCHAR(255) NOT NULL COMMENT '文章内容',
    PRIMARY KEY (`id`),
    KEY `idx_user_name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

在这种建表语句中不用过度注重细节，只需要知道 id 是主键，并且在user_name建了一个非主键的索引就行了。

为了实现分页，很容易联想到下面这种语句：

select * from page order by id limit offcet, size;

如果使用这条sql语句的话，同样都是查询10条数据，那么查询第一页和第100页的查询速度是一样的吗？

第一页就是下面这样的sql语句。

select * from page order by id limit 0, 10;

第一百页就是

select * from page order by id limit 990, 10;

首先我们要知道，两种limit的执行过程

当我们谈论使用LIMIT offset, size进行分页查询时，实际上我们在讨论两种不同的查询模式：一种是LIMIT size（这实质上等同于LIMIT 0, size），另一种是带有非零偏移量的LIMIT offset, size。关键的区别在于这个offset的值是否为0。

两种查询方式的执行过程可以简单的这么说

`LIMIT size`的执行过程

当offset为0时，MySQL直接定位到表的开始位置。
然后，它按照指定的顺序（比如通过id）读取行，直到达到指定的数量size。
这个过程相对直接且高效，因为它从表的起始位置开始，并且只需要读取size数量的行。

`LIMIT offset, size`的执行过程

当offset非0时，MySQL首先需要跳过offset数量的行。
在跳过这些行之后，它才开始按照指定的顺序读取行，直到达到size数量的行。
这意味着如果offset很大，MySQL需要处理更多的行才能达到实际需要返回的数据区域，这将消耗更多的时间和资源。

我们再来看一下limit sql的内部执行逻辑：

在深入探讨MySQL的LIMIT语句的内部执行机制之前，我们需要先了解MySQL的架构。MySQL分为两个主要层次：服务器层和存储引擎层。在众多存储引擎中，InnoDB是最常用的一种，它提供了事务支持、行级锁定等高级功能。

服务器层包含了很多重要的模块，其中执行器的作用尤为关键。执行器负责与存储引擎层进行交互，通过调用存储引擎提供的API，逐行获取数据。只有当数据满足所有查询条件（例如WHERE子句中的条件）时，这些数据才会被加入到最终的结果集中，随后返回给客户端应用程序，比如使用Go或Java编写的应用。

为了更好地理解LIMIT语句的执行过程，我们可以运行一个带有EXPLAIN命令的查询示例：

explain select * from page order by id limit 0, 10;

可以看到，在explain中提示 key 那里，执行的是PRIMARY，也就是走的主键索引。这意味着查询操作利用了主键索引进行优化。

在InnoDB存储引擎中，主键索引是以B+树数据结构实现的。B+树是一种平衡树结构，它能够高效地支持范围查询和顺序访问操作，这对于执行排序和限制结果集大小的LIMIT查询是很重要的。

B+树大概就是这个样子：

在这个树状结构里，特别需要注意的是树的最底层，即叶子节点。叶子节点存储的内容会根据其对应的索引类型而有所区别。

对于主键索引来说，其叶子节点直接包含了完整的行记录信息。也就是说一旦通过主键索引找到了目标数据的叶子节点，我们就获取到了所需的全部数据，无需进一步的查找。

然而，对于非主键索引，情况就不一样了。非主键索引的叶子节点存储的是相应行的主键值，而不是完整的行记录。因此，当我们使用非主键索引进行查询时，首先会定位到包含目标主键值的叶子节点。然后，系统需要执行一个额外的查找步骤，也就是“回表”，通过这个主键值在主键索引中检索，以获取完整的行数据。

假如执行这条语句：

select * from page where user_name = "小白10";

假设user_name是一个非主键索引。在这种情况下，查询操作首先会在user_name索引中查找所有user_name等于"小白10"的记录，从而在相应的叶子节点中找到这些记录对应的主键值，假设是10。接下来，系统将进行“回表”操作，即利用这个主键值在主键索引中进行搜索，最终定位并返回主键为10的完整行数据。

无论是主键索引还是非主键索引，它们的叶子节点中的数据都是按照一定的顺序排列的。

对于主键索引，数据按照主键的值从小到大排序；

而对于非主键索引，则根据索引列的值进行排序。

基于主键索引的limit执行过程

那么回到文章开头的问题里。

当我们去掉explain，执行这条sql。

select * from page order by id limit 0, 10;

上面select后面带的是星号，也就是要求获得行数据的所有字段信息。*

server层会调用innodb的接口，在innodb里的主键索引中获取到第0到10条完整行数据，依次返回给server层，并放到server层的结果集中，返回给客户端。

而当我们把offset搞离谱点，比如执行的是

select * from page order by id limit 6000000, 10;

情况就变得复杂了。在这种情况下，服务器层同样会调用InnoDB的接口，但是由于偏移量为6000000，它需要从主键索引中检索出第0到第（6000000 + 10）条记录，然后根据偏移量丢弃前6000000条，仅保留最后的10条记录返回给客户端。

这也就意味着，尽管最终只需要10条记录，但系统却不得不处理和传输大量无用的数据，这无疑会增加查询的耗时。

因此，我们就知道了文章开头的问题的答案，mysql查询中 limit 1000,10 会比 limit 10 更慢。原因是 limit 1000,10 会取出1000+10条数据，并抛弃前1000条，这部分耗时更大。

那这种case有办法优化吗？

可以看出，当offset非0时，server层会从引擎层获取到很多无用的数据，而当select后面是*号时，就需要拷贝完整的行信息，拷贝完整数据跟只拷贝行数据里的其中一两个列字段耗时是不同的，这就让原本就耗时的操作变得更加离谱。

因为前面的offset条数据最后都是不要的，就算将完整字段都拷贝来了又有什么用呢，所以我们可以将sql语句修改成下面这样。

select * from page  where id >=(select id from page  order by id limit 6000000, 1) order by id limit 10;

上面这条sql语句，里面先执行子查询 select id from page order by id limit 6000000, 1, 这个操作，其实也是将在innodb中的主键索引中获取到6000000+1条数据，然后server层会抛弃前6000000条，只保留最后一条数据的id。

但不同的地方在于，在返回server层的过程中，只会拷贝数据行内的id这一列，而不会拷贝数据行的所有列，当数据量较大时，这部分的耗时还是比较明显的。

在拿到了上面的id之后，假设这个id正好等于6000000，那sql就变成了

select * from page  where id >=(6000000) order by id limit 10;

这样innodb再走一次主键索引，通过B+树快速定位到id=6000000的行数据，时间复杂度是lg(n)，然后向后取10条数据。

这样性能确实是提升了，亲测能快一倍左右，属于那种耗时从3s变成1.5s的操作。

这······

也就是没办法中的办法。

基于非主键索引的limit执行过程

上面提到的是主键索引的执行过程，我们再来看下基于非主键索引的limit执行过程。

比如下面的sql语句

select * from page order by user_name  limit 0, 10;

在这种情况下，服务器层首先通过InnoDB存储引擎的接口，在非主键索引中找到排序后的第一个用户名称对应的主键ID。接下来，它需要进行“回表”操作，即利用这个主键ID在主键索引中查找以获取完整的行数据。这些数据随后被加入到结果集中，并最终返回给客户端。

而当offset>0时，且offset的值较小时，逻辑也类似，区别在于，offset>0时会丢弃前面的offset条数据。

也就是说非主键索引的limit过程，比主键索引的limit过程，多了个回表的消耗。

但当offset变得非常大时，比如600万，此时执行explain。

可以看到执行计划会变成全表扫描（type显示为ALL），因为优化器认为这比执行大量的“回表”操作要高效。这种情况下，非主键索引的LIMIT查询很容易演变成性能的瓶颈。

这种情况也能通过一些方式去优化。比如

select * from page t1, (select id from page order by user_name limit 6000000, 100) t2  WHERE t1.id = t2.id;

通过select id from page order by user_name limit 6000000, 100。先走innodb层的user_name非主键索引取出id，因为只拿主键id，不需要回表，所以这块性能会稍微快点，在返回server层之后，同样抛弃前600w条数据，保留最后的100个id。然后再用这100个id去跟t1表做id匹配，此时走的是主键索引，将匹配到的100条行数据返回。这样就绕开了之前的600w条数据的回表。

当然，跟上面的case一样，还是没有解决要白拿600w条数据然后抛弃的问题，这也是非常挫的优化。

像这种，当offset变得超大时，比如到了百万千万的量级，问题就突然变得严肃了。

这里就产生了个专门的术语，叫深度分页。