mysql查询限制1000,10是否与限制10一样快？如果我想分页怎么办？

我报名了金石计划的第一个挑战——瓜分10万奖池，这是我的第n篇文章， 点击查看活动详情

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

例如下图中红框内的翻页功能。

我们很容易认为用mysql就可以实现。

假设我们的表sql是这样的

建表sql不用扣细节，只需要知道 id为主键，在user_name上建立非主键索引 够了，其他都不重要了。

实现分页。

很容易想到下面的sql语句。

 select * from page order by id limit offset, size;  
 复制代码

例如，一页有 10 条数据。

第一页是下面的sql语句。

 select * from page order by id limit 0, 10;  
 复制代码

第一百页是

 select * from page order by id limit 990, 10;  
 复制代码

所以问题来了。

用这种方法， 同样是取10条数据。第一页和百页的查询速度一样吗？为什么？

两个限制的执行过程

以上两种查询方式。对应 限制偏移量，大小 和 限制大小 两种方式。

实际上 限制大小 ， 相当于 限制 0，大小 .即从0开始取size个数据。

也就是两种方式 区别在于偏移量是否为0。

我们先来看看limit sql的内部执行逻辑。

mysql分为 服务器层 和 存储引擎层 .一般来说，存储引擎使用innodb。

服务器层有很多模块，需要注意的有 执行器 是用来处理存储引擎的组件。

执行器可以通过调用存储引擎提供的接口逐行获取数据。当数据完全满足要求（如满足其他 where 条件）时，将被放入 结果集 ，最后回到调用mysql 客户端（用 go、java 编写的应用程序） .

我们可以先执行下面的sql 解释 .

 解释 select * from page order by id limit 0, 10;  
 复制代码

可以看到，explain里面的key就在那里，执行是 基本的 ，也就是去 主键索引 .

主键索引本质上是一棵B+树，是放在innodb中的数据结构。

我们可以回忆起 B+ 树看起来像这样。

在这个树形结构中，我们需要注意的是最底层的节点，即 叶节点 .并且这个叶子节点中的信息会基于当前索引 主键或非主键 不同的。

• 如果是 主键索引 ，其叶子节点将存储完整的行数据信息。
• 如果是 非主键索引 ，则其叶子节点将存储主键。如果要获取行数据信息，需要到主键索引处再次获取数据，称为 退货表格 .

比如执行

 select * from page where user_name = "小白10";  
 复制代码

会通过非主键索引查询 用户名 为了” 小白10 “数据，然后在叶子节点中找到” 小白10 对应的数据“ 主键是 10 .

这个时候回表 主键索引 中间做查询，最后定位到 主键为 10 的行数据 .

但是无论是主键还是非主键索引，它们的叶子节点数据都是 已订购 .比如在主键索引中，数据按照主键id的大小，从小到大排序。

基于主键索引限制执行流程

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

当我们删除解释时，执行这条sql。

 select * from page order by id limit 0, 10;  
 复制代码

上面的选择后面跟着 星号 * ，即获取行数据的请求 所有字段信息。

服务器层会调用innodb的接口，获取innodb主键索引中的第0到第10个条目 全行数据 ，依次返回给服务器层，放入服务器层的结果集中，返回给客户端。

例如，当我们使偏移量变得离谱时，执行是

 select * from page order by id limit 6000000, 10;  
 复制代码

服务器层会调用innodb的接口。由于这次offset=6000000，所以会在innodb的主键索引中获取到第(6000000 + 10)项 全行数据 , 回到服务器层后，根据offset值一个一个丢弃，最后只剩下最后一个size bar。 ，即10条数据，放在服务器层的结果集中，返回给客户端。

可以看出，当offset不为0时，server层会从engine层获取 很多无用的数据 ，而获取这些无用的数据非常耗时。

所以我们知道了文章开头问题的答案， 在 mysql 查询中，limit 1000,10 会比 limit 10 慢。原因是limit 1000,10会取出1000+10条数据，丢弃前1000条，耗时较多。

有没有办法优化这种情况？

可以看出，当offset不为0的时候，server层会从engine层获取大量无用的数据，而select后面跟一个*的时候，需要复制完整的行信息。 复制完整数据 和 仅复制行数据中的一或两列字段 耗时不一样，让原本就耗时的操作更加离谱。

因为最后不需要前面的offset数据，复制所有完整的字段有什么用，所以我们可以修改sql语句如下。

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

上面的sql语句，先执行子查询 按 id 限制从页面顺序中选择 id 6000000, 1 ,其实这个操作也会在innodb中的主键索引中获取 6000000+1 条数据，然后server层会丢弃前600万条数据，只保留最后一条数据的id。

但不同的是，在返回服务器层的过程中，只复制了数据行中的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的操作。

这个······

这是真的，它是杯水车薪，有点摩擦，它是无处可逃的出路。

基于非主键索引限制执行流程

上面说的是主键索引的执行过程，我们来看看 非主键索引 限制执行过程。

例如下面的sql语句

 select * from page order by user_name limit 0, 10;  
 复制代码

服务器层会调用innodb的接口，在获取到innodb中非主键索引中第0条数据对应的主键id后， 退货表格 在主键索引中找到对应的完整行数据，然后返回给服务器层。服务器层将其放入结果集中并返回给客户端。

当offset>0时，offset的值很小，逻辑类似，不同的是当offset>0时，之前的offset数据会被丢弃。

也就是说 非主键索引的limit进程比主键索引的limit进程消耗更多的表。

但是当offset变得很大的时候，比如600万，这个时候就执行explain。

可以看到type列显示ALL，也就是 全表扫描 .

这是因为服务器层 优化器 ，它将在执行器执行sql语句之前确定哪个执行计划成本更低。

很明显，优化器看到非主键索引的600w次回表后摇了摇头。

所以， 当限制偏移量过大时，非主键索引查询很容易变成全表扫描。真正的性能杀手 .

这种情况也可以通过某些方式进行优化。例如

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

经过 按用户名从页面顺序中选择 id 限制 6000000, 100 .先去innodb层的user_name非主键索引获取id，因为只取主键id， 无需退货 ，所以这个性能会稍微快一些。回到server层后，前600w条数据也被丢弃，保留后100条id。然后用这100个id来匹配t1表的id。此时使用主键索引，返回匹配的100行数据。这绕过了之前600w数据的返回形式。

当然，和上面的案例一样，对于白拿600w条数据然后丢弃的问题仍然没有解决办法，这也是一个非常令人沮丧的优化。

像这样，当偏移量变得非常大，比如几百万的量级时，问题就突然变得严重了。

这里出现了一个特殊的术语，叫做 深度分页 .

深度分页问题

深度分页的问题是一个非常恶心的问题。恶心就是恶心。这个问题，其实是 没有解决方案 .

无论你使用mysql还是es，都只能通过某种方式“减轻”问题的严重性。

当我们遇到这个问题时，我们应该回头想想。

为什么我们的代码有很深的分页问题？

它背后的原始需求是什么 ，我们可以基于此做一些规避。

如果要获取整个表的数据

有的需求是这样的，我们有一个数据库表，但是我们想把这个数据库表里面的数据都取出来，异构到es，或者hive中。这时候，如果我们直接执行

 从页面中选择 *；  
 复制代码

这条sql一执行，狗看到就摇头。

由于数据量大，mysql不能一次获取所有数据，正常 超时错误 .

这么多mysql新手会过 限制偏移大小 最好以分页的形式分批获取。一开始是好的。当某一天数据表变得非常大时，就有可能出现上述情况。 深度分页 问题。

这种情况是最好的解决方案。

我们可以把所有的数据 按 id 主键排序 ，然后分批取，查询当前batch的最大id作为下一个过滤条件。

可以看到伪代码

对于这个操作，可以每次使用主键索引定位id，以后再遍历100条数据，这样无论多少万条数据，查询性能都非常稳定。

如果是向用户显示分页

如果深度分页背后的原始需求只是产品经理想做一个展示页面的功能，比如产品展示页面，那么我们应该和产品经理好好对战。

10万多之后需要翻什么样的翻页，这显然是一个不合理的要求。

是否可以改变需求，使其更贴近用户的使用行为？

例如，我们在使用 Google 搜索时看到的翻页功能。

一般来说，谷歌搜索基本在20页以内，作为用户，我很少翻到第10页之后。

参考。

如果我们要搜索或者过滤页面，不要用mysql，用es，还需要控制显示的结果个数，比如10000以内，这样分页才不会太深。

如果由于各种原因，必须使用mysql。同样的，也需要控制返回结果的个数，比如1k以内的个数。

这样就可以勉强支持各种翻页和页面跳转（比如突然跳到第6页，然后又跳到第106页）。

但是如果能做成不支持页面跳转的产品形式就更好了，比如 仅支持上一页或下一页 .

这样，我们就可以使用上面提到的start_id方法批量获取，每批数据都以start_id为起始位置。这种方案最大的优点是，无论翻多少页，查询速度始终是稳定的。

听起来令人沮丧？

怎么可能，包装一下这个函数。

变成了抖音，只能上下滑动，专业叫 瀑布流 .

会不会很沮丧？

总结

• 限制偏移量，大小 相比 限制大小 应该比较慢，offset的值越大，sql的执行速度越慢。
• 当偏移量过大时，会导致 深度分页 问题，目前mysql和es都没有很好的办法解决这个问题。只能通过限制查询数量或批量获取来规避。
• 遇到深度分页的问题时，多想想它原来的需求。大多数时候，不应该出现深度分页的场景，必要时对产品经理的影响更大。
• 如果数据量很小，比如1k的量级，从长远来看不太可能有巨大的增长，最好使用 限制偏移量，大小 方案不错，可以用。

最后

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

这个问题是我的无能！

告别！ ！

最近原创更新的阅读量一直在下降，想了想，晚上辗转反侧。

我有一个不成熟的要求。

离开广东很久了，很久没有人叫我美男了。

每个人都可以 评论区 李，叫我帅哥？

我这样一个善良而简单的愿望能实现吗？

如果实在说不出来，能不能帮我点一下右下角 喜欢并观看 ?

别说话，一起哽咽在知识的海洋里

版权声明：本文为博主原创文章，遵循CC 4.0 BY-SA版权协议。转载请附上原文出处链接和本声明。

这篇文章的链接： https://homecpp.art/1204/6435/0955

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明

本文链接：https://www.qanswer.top/12564/10090413