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

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

比如下图的翻页功能。

我们很容易能联想到可以用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，也就是走的主键索引。这意味着查询操作利用了主键索引进行优化。

分页查询offest=0

在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。

非主键索引offset值超大时走全表扫描

可以看到执行计划会变成全表扫描（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变得超大时，比如到了百万千万的量级，问题就突然变得严肃了。

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

深度分页问题

深度分页问题，是个很恶心的问题，恶心就恶心在，这个问题，它其实无解。

无论是使用MySQL还是Elasticsearch等技术，都只能尝试通过各种手段来缓解问题的严重性，而不是彻底解决它。面对深度分页问题，我们需要重新考虑背后的业务需求，探索是否有可能通过调整需求或采取其他策略来避免这一问题的出现。

如果你是想取出全表的数据

有些需求是这样的，我们有一张数据库表，但我们希望将这个数据库表里的所有数据取出，异构到es，或者hive里，这时候如果直接执行

select * from page;

这个sql一执行，狗看了都摇头。

因为数据量较大，mysql根本没办法一次性获取到全部数据，妥妥超时报错。

一种常见的初级解决方案是采用LIMIT offset, size的方式进行分批次查询。这种方法在数据量不是特别大时似乎能行，但随着数据量的增加，很快就会陷入前文讨论过的深度分页问题，导致性能急剧下降。

针对这种全表数据迁移的场景，实际上有一个更加高效稳定的处理方法。

这个方法的核心思想是利用表的主键ID进行排序，并基于主键ID的范围来分批次顺序读取数据。通过记住每一批次读取的最后一个ID，可以在下一次查询时从这个ID继续向后读取，这样可以确保即便是面对数百万甚至更多的记录，查询性能也能保持稳定。

可以看下伪代码

batch获取数据

这个操作，可以通过主键索引，每次定位到id在哪，然后往后遍历100个数据，这样不管是多少万的数据，查询性能都很稳定。

batch分批获取user表

如果是给用户做分页展示

我们在使用谷歌搜索时看到的翻页功能。

它们通常将搜索结果限制在前20页以内。这种做法基于一个现实的观察：大多数用户很少浏览到第10页之后的内容。这为我们提供了一个重要的设计原则，也就是在实现分页功能时，应该考虑用户的实际使用习惯来相应地调整我们的技术选择和设计策略。

对于需要实现复杂搜索或筛选功能的应用，Elasticsearch（ES）是一个更合适的选择，因为它专门为处理大量数据的搜索、筛选和排序任务而设计。使用ES时，我们应该设定一个合理的结果数量上限，比如最多显示一万条结果，以防止用户遇到过深的分页问题。

如果有特定的情况使得我们必须依赖MySQL来实现这一功能，那么同样需要通过限制返回的结果数量来避免性能问题，例如最多允许访问前1000条记录。这样做虽然可以支持基本的翻页和跳页需求，但可能会牺牲一些用户体验。

为了优化体验并提高性能，我们可以考虑设计一个不支持直接跳页的界面，而是仅允许用户通过“上一页”或“下一页”的方式进行浏览。这种设计可以让我们采用基于start_id的分批获取方法，每次查询都从上一批次的最后一个ID继续，无论用户浏览到哪一页，这种方法都能确保查询速度的稳定性。

总结

• limit offset, size 比 limit size 要慢，且offset的值越大，sql的执行速度越慢。
• 当offset过大，会引发深度分页问题，目前不管是mysql还是es都没有很好的方法去解决这个问题。只能通过限制查询数量或分批获取的方式进行规避。
• 遇到深度分页的问题，多思考其原始需求，大部分时候是不应该出现深度分页的场景的，必要时多去影响产品经理。
• 如果数据量很少，比如1k的量级，且长期不太可能有巨大的增长，使用传统的LIMIT offset, size方法仍然是可行的。

最后

关于深度分页，如果大家有更好的想法，欢迎评论区说出来。

告辞！！