在浏览网站的时候，我们经常会遇到需要分页查询的场景。我们很容易想到用mysql就可以实现。假设我们的建表sql是这样的，mysql建表sql和建表sql的细节就不用扣了。只需要知道id是主键，在user_name上建立一个非主键索引就可以了。其他的都不重要。从而实现分页。很容易想到下面的sql语句。select*frompageorderbyidlimitoffset,size;比如一页有10条数据。用户表数据库原始状态的第一页是如下SQL语句。select*frompageorderbyidlimit0,10;第一百页是select*frompageorderbyidlimit990,10;那么问题来了。这样一来，也取了10条数据。第一页和第一百页的查询速度一样吗？为什么？以上两种查询方法在执行过程中有两个限制。对应limitoffset,size和limitsize。其实limitsize相当于limit0,size。即从0开始取size条数据。换句话说，这两种方式的区别在于偏移量是否为0。我们先来看看limitsql的内部执行逻辑。Mysql架构Mysql分为服务器层和存储引擎层。一般来说，存储引擎使用innodb。Server层有很多模块，其中Executor是用来和存储引擎打交道的组件。执行器可以通过调用存储引擎提供的接口来获取一行数据。当数据完全满足要求时（比如满足其他where条件），就会放入结果集中，最后返回给调用mysql（go，java写的应用程序）的客户端。我们可以先对下面的sql执行explain。说明select*frompageorderbyidlimit0,10;可以看到在explainkey中，执行了PRIMARY，也就是主键索引。分页查询offset=0主键索引本质上是一个B+树，是放在InnoDB中的一种数据结构。我们可以回忆一下，B+树是这样的。B+树结构在这个树结构中，我们需要关注最底层的节点，也就是叶子节点。根据当前索引是主键还是非主键，放置在这个叶子节点中的信息会有所不同。如果是主键索引，它的叶子节点会存储完整的行数据信息。如果是非主键索引，其叶子节点将存储主键。如果要获取行数据信息，需要再去主键索引中获取数据，回调到表中。例如执行select*frompagewhereuser_name="小白10";会通过非主键索引查询user_name为“小白10”的数据，然后在叶子节点中找到“小白10”的数据对应的主键为10，此时再回到表在主键索引中查询，最终定位到主键为10的行数据。回到表中，但是无论是主键索引还是非主键索引，它们的叶子节点数据都是有序的。比如在主键索引中，这些数据是按照主键id的大小，从小到大排序的。基于主键索引的limit执行过程，再回到文章开头的问题。当我们去掉explain的时候，执行这个sql。select*frompageorderbyidlimit0,10;上面的select后面有一个星号*，即要求获取该行数据的所有字段信息。server层会调用innodb接口，获取innodb中主键索引中第0到第10条完整行数据，依次返回给server层，放入server层的结果集中，返回给客户。而当我们把offset弄得离谱的时候，比如我们执行select*frompageorderbyidlimit6000000,10;server层会调用innodb的接口，因为这个offset=6000000，所以会在innodb的主键索引中获取0到(6000000+10)的完整行数据，返回给server层，并一一丢弃根据偏移值。最后server层的结果集中只剩下最后一个size项，也就是10条数据。返回给客户端。可以看出，当offset不为0时，server层会从engine层获取大量无用数据，获取这些无用数据是比较耗时的。因此，我们知道文章开头问题的答案，limit1000,10在mysql查询中会比limit10慢。原因是limit1000,10会取出1000+10条数据，舍弃前1000条。这部分需要更多时间。有没有办法优化这种情况？可以看出，当offset不为0时，server层会从engine层获取很多无用的数据，而select后面带*的时候，需要复制完整的行信息，复制完整的数据和只复制行数据一两个列字段的耗时是不一样的，这让本就耗时的操作更加离谱。因为之前的offset数据到头来是不需要的，即使复制完整的字段也没有用，所以我们可以修改sql语句如下。select*frompagewhereid>=(selectidfrompageorderbyidlimit6000000,1)orderbyidlimit10;上面的sql语句首先执行了子查询selectidfrompageorderbyidlimit6000000,1，这个操作实际上会从InnoDB中的主键索引中得到6000000+1条数据，然后server层会丢弃前6000000条片，只保留最后一条数据的id。但不同的是，在返回到server层的过程中，只会复制数据行中的id列，不会复制数据行的所有列。当数据量很大时，这部分的时间消耗是相当明显的。.得到上面的id后，假设id恰好等于6000000，那么sql就变成了select*frompagewhereid>=(6000000)orderbyidlimit10;这样，innodb再次遍历主键索引，快速使用B+树定位到id=6000000的行数据，时间复杂度为lg(n)，然后向后取10条数据。这样一来，性能确实有所提升，亲测可以快一倍左右，属于那种耗时3s到1.5s的操作。这……是真的，有点杯水车薪，有点垃圾，而且是无中生有的出路。基于非主键索引的limit执行过程上面说的是主键索引的执行过程，我们来看一下基于非主键索引的limit执行过程。例如下面的sql语句select*frompageorderbyuser_namelimit0,10;server层会调用innodb接口，在innodb中获取非主键索引中第0条数据对应的主键id后，回表到主键索引中找到对应的完整数据行，然后返回给服务器层。服务器层将其放入结果集中返回给客户端。当offset>0，offset的值较小时，逻辑类似，不同的是当offset>0时，之前的offset数据会被丢弃。也就是说非主键索引的limit过程比主键索引的limit过程有更多的回表消耗。但是当offset变得很大的时候，比如600万，这时候就会执行explain。当非主键索引偏移值过大时，进行全表扫描，可以看到type列显示ALL，即全表扫描。这是因为server层的优化器会在executor执行SQL语句之前判断哪个执行计划的开销比较小。显然，优化器在看到600万的非主键索引返回表后摇了摇头。还不如对整个表一个一个判断，所以他选择了全表扫描。因此，当limitoffset过大时，非主键索引查询很容易变成全表扫描。这是一个真正的性能杀手。这种情况也可以通过一些方式进行优化。例如select*frompaget1,(selectidfrompageorderbyuser_namelimit6000000,100)t2WHEREt1.id=t2.id;通过selectidfrompageorderbyuser_namelimit6000000,100.先去innodb层的user_name非主键索引中提取id，因为只取主键id，不需要返回表，所以这块的性能会稍微快一些。回到server层后，前600w条数据也会被丢弃，后100条保留。ID。然后用这100个id去匹配t1表的id。此时使用主键索引返回匹配到的100行数据。这样就绕过了前面600万条数据的回表。当然，像上面的案例一样，600万条数据白拿来扔掉的问题还是没有解决。这也是一个非常令人沮丧的优化。这样，当offset变得很大，比如百万级的时候，问题就突然变得严重了。这里有一个特殊的术语，叫做深度分页。深度分页问题深度分页问题是一个很恶心的问题，恶心的东西就是恶心。这个问题实际上没有解决办法。不管你用的是mysql还是es，都只能通过一些手段来“减缓”问题的严重性。遇到这个问题，我们应该回过头来好好想想。为什么我们的代码存在严重的分页问题？其背后的原始需求是什么，我们可以基于此做一些规避。如果要提取整张表的数据，有些需求是这样的。我们有一个数据库表，但是我们想把这个数据库表中的所有数据，异构的提取到es，或者hive中。这时候如果直接执行select*frompage;这个sql一执行，狗看到就摇头。因为数据量大，mysql没有办法一次性获取到所有的数据，适当超时会报错。所以很多mysql新手都会通过limitoffsetsize分页来批量获取。一开始还好，但是慢慢的，有一天数据表变得特别大，上面说的深度可能会出现Pagination的问题。这种情况是最好的解决方案。我们可以把所有的数据按照id主键排序，然后分批取，将当前批次的最大id作为下一次查询的过滤条件。大家可以看看伪码批处理中获取数据的操作。可以每次通过主键索引定位到id，以后再遍历100条数据。这样无论几万条数据，查询性能都很稳定。batch批量获取用户表。如果用户表要分页展示，如果原来深度分页背后的需求只是产品经理要做展示页面的功能，比如产品展示页面，那我们就应该和产品好好战斗经理。什么样的翻页都需要10万以上，这显然是一个不合理的需求。是否可以改变需求，使其更贴近用户的使用行为？比如我们在使用谷歌搜索时看到的翻页功能。一般来说，谷歌搜索基本都在20页以内。作为用户，我很少翻到第10页。参考。如果我们要搜索或者过滤页面，不要用mysql，用es，还需要控制显示结果的个数，比如10000以内，以免分页太深。如果由于各种原因，必须使用mysql。同样的，也需要控制返回结果的个数，比如在1k以内。这样就可以勉强支持各种翻页跳转了（比如突然跳到第6页再跳到106页）。但是如果能做成不支持页面跳转的产品形式就更好了，比如只支持上一页或者下一页。上下页的形式，我们可以使用上面提到的start_id方法批量获取，每批数据以start_id开头。这种方案最大的好处是无论翻多少页，查询速度始终保持稳定。听起来令人沮丧？怎么来的，包装这个功能。只能向上或向下绘制，如抖音。说得专业点，叫瀑布流。这不令人沮丧吗？综上所述，limitoffset和size比limitsize慢，offset的值越大，SQL的执行速度越慢。当offset过大时，会造成deeppaging的问题。目前mysql和es都没有很好的办法解决这个问题。只能通过限制查询次数或批量获取来规避。遇到深度分页的问题时，多想想它原来的需求。很多时候深度分页的场景是不应该出现的，必要的时候对产品经理影响更大一些。如果数据量不大，比如1k量级，长期来看不太可能有巨大的增长，最好用limitoffset,size的方案，很好，只要可以使用。
