Mysql优化系列之limit查询

原理分析

mysql查询中 limit 1000,10 会比 limit 10 更慢。原因是 limit 1000,10 会取出1000+10条数据，并抛弃前1000条，这部分耗时更大

优化

基于主键的limit

优化1：当select后面是*号时，就需要拷贝完整的行信息， 拷贝完整数据 跟 只拷贝行数据里的其中一两个列字段 耗时是不同的，这就让原本就耗时的操作变得更加离谱。所以优化1就是，只select必要的字段

优化2：
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这一列，而不会拷贝数据行的所有列，当数据量较大时，这部分的耗时还是比较明显的。

基于非主键索引的limit

比如下面的sql语句

select * from page order by user_name limit 0, 10;
server层会调用innodb的接口，在innodb里的非主键索引中获取到第0条数据对应的主键id后， 回表 到主键索引中找到对应的完整行数据，然后返回给server层，server层将其放到结果集中，返回给客户端。

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

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

但当offset变得非常大时，比如600万，此时执行explain。
非主键索引offset值超大时走全表扫描

可以看到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，你都只能通过一些手段去"减缓"问题的严重性。

遇到这个问题，我们就该回过头来想想。

为什么我们的代码会产生深度分页问题？

它背后的原始需求是什么，我们可以根据这个做一些规避。

脚本等情况

我们可以将所有的数据 根据id主键进行排序 （每次记住最大的id），然后分批次取，将当前批次的最大id作为下次筛选的条件进行查询。

用户场景

如果深度分页背后的原始需求只是产品经理希望做一个展示页的功能，比如商品展示页，那么我们就应该好好跟产品经理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 的方案吧，整挺好，能用就行。
————————————————
版权声明：本文为CSDN博主「java_beautiful」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/java_beautiful/article/details/125760762