MySQL的LIMIT与分页优化

MySQL分页优化

最新推荐文章于 2023-03-13 00:04:46 发布

原创最新推荐文章于 2023-03-13 00:04:46 发布 · 3.8k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #性能优化 #LIMIT #分页

MySQL高级及性能优化专栏收录该内容

71 篇文章

订阅专栏

本文探讨了MySQL分页查询的性能优化方法，包括LIMIT指令的高效使用、子查询优化及多种分页策略。针对大数据量场景提供了实用技巧。

select * from table LIMIT 5,10; #返回第6-15行数据 
select * from table LIMIT 5; #返回前5行 
select * from table LIMIT 0,5; #返回前5行

性能优化：

基于MySQL5.0中limit的高性能,我对数据分页也重新有了新的认识.

1.
Select * From cyclopedia Where ID>=(
Select Max(ID) From (
 Select ID From cyclopedia Order By ID limit 90001
) As tmp
) limit 100;

2.
Select * From cyclopedia Where ID>=(
Select Max(ID) From (
 Select ID From cyclopedia Order By ID limit 90000,1
) As tmp
) limit 100;

同样是取90000条后100条记录,第1句快还是第2句快?
第1句是先取了前90001条记录,取其中最大一个ID值作为起始标识,然后利用它可以快速定位下100条记录
第2句择是仅仅取90000条记录后1条,然后取ID值作起始标识定位下100条记录
第1句执行结果.100 rows in set (0.23) sec
第2句执行结果.100 rows in set (0.19) sec

很明显第2句胜出.看来limit好像并不完全像我之前想象的那样做全表扫描返回limit offset+length条记录,这样看来limit比起MS-SQL的Top性能还是要提高不少的.

其实第2句完全可以简化成

Select * From cyclopedia Where ID>=(
Select ID From cyclopedia limit 90000,1
)limit 100;

直接利用第90000条记录的ID,不用经过Max运算,这样做理论上效率因该高一些,但在实际使用中几乎看不到效果,因为本身定位ID返回的就是1条记录,Max几乎不用运作就能得到结果,但这样写更清淅明朗,省去了画蛇那一足.

可是,既然MySQL有limit可以直接控制取出记录的位置,为什么不干脆用Select * From cyclopedia limit 90000,1呢?岂不更简洁?
这样想就错了,试了就知道,结果是:1 row in set (8.88) sec,怎么样,够吓人的吧,让我想起了昨天在4.1中比这还有过之的"高分".Select * 最好不要随便用,要本着用什么,选什么的原则, Select的字段越多,字段数据量越大,速度就越慢. 上面2种分页方式哪种都比单写这1句强多了,虽然看起来好像查询的次数更多一些,但实际上是以较小的代价换取了高效的性能,是非常值得的.

LIMIT偏移量越大，从磁盘IO读取的记录行数就越多，所以要尽可能少的从磁盘IO读取数据，总的来说有以下几种方式：

1.子查询优化法
先找出第一条数据，然后大于等于这条数据的id就是要获取的数据
缺点：数据必须是连续的，可以说不能有where条件，where条件会筛选数据，导致数据失去连续性

2.倒排表优化法
倒排表法类似建立索引，用一张表来维护页数，然后通过高效的连接得到数据
缺点：只适合数据数固定的情况，数据不能删除，维护页表困难

3.反向查找优化法
当偏移超过一半记录数的时候，先用排序，这样偏移就反转了
缺点：order by优化比较麻烦，要增加索引，索引影响数据的修改效率，并且要知道总记录数，偏移大于数据的一半

4.limit限制优化法
把limit偏移量限制低于某个数。。超过这个数等于没数据，我记得alibaba的dba说过他们是这样做的

总结：limit的优化限制都比较多，所以实际情况用或者不用只能具体情况具体分析了。页数那么后，基本很少人看的。。。

====================================================================================================================================

分页优化的四种方式

很久以前读了一篇关于分页的文章，后来越想越有道理，最近又重新找出来，并做了翻译，原文参考:Four ways to optimize paginated displays.

翻译背景：在大数据量的情况下，原本很简单的分页如果没有处理好，你会发现分页的请求会消耗你大量的数据库时间。如果你遇到了这个问题，文章给了你几个很好的解决的方案。当然，初学者若能看完这篇文章，那么它会指导你写出更具有扩展性的分页代码。

全文概述：文中提到了分页的办法总结如下：

全部缓存查询结果。把查询结果全部缓存起来(例如文件缓存、静态化结果页面等)。
不详细显示总共有多少分页。这里有两个优化的技巧。其一每次在计算总条目的时候，我就固定查询501条，然后将前500条分页显示好，如果第501条确实存在，那么给出按钮 “查看更多...”（这种情况会很少）。其二，在每次列表本页面的时候，比如第一页我要显示1-20条，那么我查询出1-21条。如果第21条真的存在，我就给出"下一页"按钮，依次类推。

事实上google就是这样做的。在查看第一页搜索结果的时候google只会显示前十页(共100个条目)，并不显示搜索结果条目总共有多少:

查看第二页的时候，仅仅会多显示一页
通过EXPLAIN的"row"列来估算结果总共有多少条目。文章中称google是这样估算结果集的：

全文译文：

在实际开发中，分页显示是我们最常遇到的优化问题之一。例如搜索结果、积分列表、排行榜等。分页的一般模型:在一个排序的结果集合(较大)中我们要显示其中连续20条目;并且需要显示 “下一页”、”上一页”的链接;有时候我们还需要显示，总共有多少个条目，一共分了多少页。

要给出这样一个完成显示，数据库的代价是很大的，有时候就为了显示这么一个分页，需要执行的SQL会比整个页面显示其他的全部SQL消耗还要大。
我曾遇到这样的案例：有一次在为我们的一个客户做Slow Query LOG分析的时候我们就发现:整个LOG 里面的SQL耗时6300s,其中两个主要的分页查询大约消耗了(2850 + 380)秒,占了整个Slow Query的50%。
分页没有处理好就是这么糟糕~.

我们来分析一下分页的一般情况:

#典型分页的SQL如下:
SELECT .... FROM ... ORDER BY .... LIMIT X, 20

如果ORDER BY部分没有能够用索引的话（这样的情况还是很多的），MYSQL就会做文件排序(filesort)；假想如果如果满足WHERE 条件的条目共有个百万的数量级的话，那么MYSQL就会取出这上百万的结果，临时存储、文件排序，然后再删除大大部分的数据保留其中的20个。当用户点击“下一页”的时候，上面的过程会完全重做一遍，只是取得结果向后偏了一点。要是你还想显示“总共有多少条目，共分多少页面”的话，一般是这样做(1)使用SQL_CALC_FOUND_ROWS (2)执行一个单独的SQL去计算行数。如果用户的每一次请求都执行以上的操作，可以想象当你的数据量越来越大的时候，情况会越来越糟。

事实上，有很多办法去优化上面的过程的。(关于这一点我之前我写过的一篇article on optimizing ranked data 。不过那篇文章里面介绍的办法实施起来比较困难。所以如果不是情况复杂和重要到一定程度，就不值得那样做。)那一般情况怎么办呢？除了索引、重组数据、SQL优化,我们还有两个大的方面可以考虑去做。其一，积极的把SQL的查询结果缓存起来，从而减少SQL执行;其二就是重新考虑一下你的分页就架构，在应用中，并不是每次都需要把分页的各个部分都完整显示出来的。例如你把从第1到50页的链接都给出来，很多时候用户根本不会直接去点击某一页。我们考虑的思路是指把最重要的部分先展示出来。

这样考虑的于是就有了下面四个优化的建议来提高性能

首次查询的时候缓存结果。这样情况就变得简单了，无论是结果条目的数量，总共的页面数量，还是取出其中的部分条目。
不显示总共有多少条目。Google搜索结果的分页显示就用了这个特性。很多时候你可能看了前几页，就够了。那么我可以这样，每次我都把结果限制在500条（这个数据越大资源消耗越大）然后你每次查询的时候，都查询501条记录，这样，如果结果真有501个，那么我们就显示链接 “显示下500条记录”。
不显示总页面数。只给出“下一页”的链接，如果有下一页的话。(如果用户想看上一页的话，他会通过浏览器来回到上一页的)。那你可能会问我“不显示总页面数”怎么知道是不是有下一页呢？这里有一个很好的小技巧：你在每次显示你当前页面条目的时候你都多查询一条，例如你要显示第11-20个条目时，你就取出11-21条记录（多取一条，并不显示这多取的内容），那么当你发现第21条存在的时候就显示“下一页的链接”，否则就是末页了。这样你就不用每次计算总页面数量了，特别是在做缓存很困难的时候这样做效率非常好。
估算总结果数。Google就是这么做的，事实证明效果很好。用EXPLAIN 来解释你的SQL，然后通过EXPLAIN的结果来估算。EXPLAIN结果有一列”row”会给你一个大概的结果。(这个办法不是处处都行，但是某些地方效果是很好的)这些办法可以很大程度上减轻数据库的压力，而且对用户体验不会有什么影响。

这些办法可以很大程度上减轻数据库的压力，而且对用户体验不会有什么影响。