最近有同学面试字节三面的时候被问到“MySQL 分页有什么性能问题怎么优化”今天就主要围绕这个问题跟大家说一下 MySQL 分页问题和优化的思路。原文地址字节三面MySQL 分页怎么优化啊(opens new window)我们刷网站的时候我们经常会遇到需要分页查询的场景。比如下图红框里的翻页功能。我们很容易能联想到可以用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条数据查第一页和第一百页的查询速度是一样的吗为什么#两种limit的执行过程上面的两种查询方式。对应limit offset, size和limit size两种方式。而其实limit size相当于limit 0, size。也就是从0开始取size条数据。也就是说两种方式的区别在于offset是否为0。我们先来看下limit sql的内部执行逻辑。mysql内部分为server层和存储引擎层。一般情况下存储引擎都用innodb。server层有很多模块其中需要关注的是执行器是用于跟存储引擎打交道的组件。执行器可以通过调用存储引擎提供的接口将一行行数据取出当这些数据完全符合要求比如满足其他where条件则会放到结果集中最后返回给调用mysql的客户端go、java写的应用程序。我们可以对下面的sql先执行下explain。explain select * from page order by id limit 0, 10;可以看到explain中提示 key 那里执行的是PRIMARY也就是走的主键索引。主键索引本质是一棵B树它是放在innodb中的一个数据结构。我们可以回忆下B树大概长这样。在这个树状结构里我们需要关注的是最下面一层节点也就是叶子结点。而这个叶子结点里放的信息会根据当前的索引是主键还是非主键有所不同。如果是主键索引它的叶子节点会存放完整的行数据信息。如果是非主键索引那它的叶子节点则会存放主键如果想获得行数据信息则需要再跑到主键索引去拿一次数据这叫回表。比如执行select * from page where user_name 小白10;会通过非主键索引去查询user_name为小白10的数据然后在叶子结点里找到小白10的数据对应的主键为10。此时回表到主键索引中做查询最后定位到主键为10的行数据。但不管是主键还是非主键索引他们的叶子结点数据都是有序的。比如在主键索引中这些数据是根据主键id的大小从小到大进行排序的。#基于主键索引的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;server层会调用innodb的接口由于这次的offset6000000会在innodb里的主键索引中获取到第0到6000000 10条完整行数据返回给server层之后根据offset的值挨个抛弃最后只留下最后面的size条也就是10条数据放到server层的结果集中返回给客户端。可以看出当offset非0时server层会从引擎层获取到很多无用的数据而获取的这些无用数据都是要耗时的。因此我们就知道了文章开头的问题的答案mysql查询中 limit 1000,10 会比 limit 10 更慢。原因是 limit 1000,10 会取出100010条数据并抛弃前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中的主键索引中获取到60000001条数据然后server层会抛弃前6000000条只保留最后一条数据的id。但不同的地方在于在返回server层的过程中只会拷贝数据行内的id这一列而不会拷贝数据行的所有列当数据量较大时这部分的耗时还是比较明显的。在拿到了上面的id之后假设这个id正好等于6000000那sql就变成了select * from page where id (6000000) order by id limit 10;这样innodb再走一次主键索引通过B树快速定位到id6000000的行数据时间复杂度是lg(n)然后向后取10条数据。这样性能确实是提升了亲测能快一倍左右属于那种耗时从3s变成1.5s的操作。这······属实有些杯水车薪有点搓属于没办法中的办法。#基于非主键索引的limit执行过程上面提到的是主键索引的执行过程我们再来看下基于非主键索引的limit执行过程。比如下面的sql语句select * from page order by user_name limit 0, 10;server层会调用innodb的接口在innodb里的非主键索引中获取到第0条数据对应的主键id后回表到主键索引中找到对应的完整行数据然后返回给server层server层将其放到结果集中返回给客户端。而当offset0时且offset的值较小时逻辑也类似区别在于offset0时会丢弃前面的offset条数据。也就是说非主键索引的limit过程比主键索引的limit过程多了个回表的消耗。但当offset变得非常大时比如600万此时执行explain。可以看到type那一栏显示的是ALL也就是全表扫描。这是因为server层的优化器会在执行器执行sql语句前判断下哪种执行计划的代价更小。很明显优化器在看到非主键索引的600w次回表之后摇了摇头还不如全表一条条记录去判断算了于是选择了全表扫描。因此当limit offset过大时非主键索引查询非常容易变成全表扫描。是真·性能杀手。这种情况也能通过一些方式去优化。比如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还是es你都只能通过一些手段去减缓问题的严重性。遇到这个问题我们就该回过头来想想。为什么我们的代码会产生深度分页问题它背后的原始需求是什么我们可以根据这个做一些规避。#如果你是想取出全表的数据有些需求是这样的我们有一张数据库表但我们希望将这个数据库表里的所有数据取出异构到es或者hive里这时候如果直接执行select * from page;这个sql一执行狗看了都摇头。因为数据量较大mysql根本没办法一次性获取到全部数据妥妥超时报错。于是不少mysql小白会通过limit offset size分页的形式去分批获取刚开始都是好的等慢慢地哪天数据表变得奇大无比就有可能出现前面提到的深度分页问题。这种场景是最好解决的。我们可以将所有的数据根据id主键进行排序然后分批次取将当前批次的最大id作为下次筛选的条件进行查询。可以看下伪代码这个操作可以通过主键索引每次定位到id在哪然后往后遍历100个数据这样不管是多少万的数据查询性能都很稳定。#如果是给用户做分页展示如果深度分页背后的原始需求只是产品经理希望做一个展示页的功能比如商品展示页那么我们就应该好好跟产品经理battle一下了。什么样的翻页需要翻到10多万以后这明显是不合理的需求。是不是可以改一下需求让它更接近用户的使用行为比如我们在使用谷歌搜索时看到的翻页功能。一般来说谷歌搜索基本上都在20页以内作为一个用户我就很少会翻到第10页之后。作为参考。如果我们要做搜索或筛选类的页面的话就别用mysql了用es并且也需要控制展示的结果数比如一万以内这样不至于让分页过深。如果因为各种原因必须使用mysql。那同样也需要控制下返回结果数量比如数量1k以内。这样就能勉强支持各种翻页跳页比如突然跳到第6页然后再跳到第106页。但如果能从产品的形式上就做成不支持跳页会更好比如只支持上一页或下一页。这样我们就可以使用上面提到的start_id方式采用分批获取每批数据以start_id为起始位置。这个解法最大的好处是不管翻到多少页查询速度永远稳定。听起来很挫怎么会呢把这个功能包装一下。变成像抖音那样只能上划或下划专业点叫瀑布流。是不是就不挫了图片#总结limit offset, size比limit size要慢且offset的值越大sql的执行速度越慢。当offset过大会引发深度分页问题目前不管是mysql还是es都没有很好的方法去解决这个问题。只能通过限制查询数量或分批获取的方式进行规避。遇到深度分页的问题多思考其原始需求大部分时候是不应该出现深度分页的场景的必要时多去影响产品经理。如果数据量很少比如1k的量级且长期不太可能有巨大的增长还是用limit offset, size的方案吧整挺好能用就行。
原理与实战配置指南)
