关于分页优化

从我第一个web项目开始，分页就给我带来了很多的思考，记得第一个项目，我分页方法是最老土也是效率最低的，把数据一次性全部查找出来，然后再分页显示。

后来，后来，知道了分页可以用 limit（mysql）里面的，可是如果用这个方法分页，还是不是很完美，大家想想，每次查看一次，是否都要执行2次数据库查询，一次是count(*)查询，查出来统计页数，再一次就是limit查询返回list了。我试过每次请求不count，但是会出现一个问题，如果每次不count  ，别人加了新闻， 你就看不到了。要关了从新查才行，所以，最后得出结论，每次请求还是老老实实count，不过count（某个字段）比count（*）速度快。

 

 

 

 

最近看到一篇文章，就是关于分页查询优化的，原文如下：

 

 

 

 

在实际开发中，分页显示是我们最常遇到的优化问题之一。例如搜索结果、积分列表、排行榜等。分页的一般模型:在一个排序的结果集合(较大)中我们要 显示其中连续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页的链接都给出来，很多时候用户根本不会直接去点击某一页。我们考虑的 思路是指把最重要的部分先展示出来。

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

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