分页优化的四种方式

2017-12-06 09:35:10

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

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

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

  1. 全部缓存查询结果。把查询结果全部缓存起来(例如文件缓存、静态化结果页面等)。

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

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

  3. 通过EXPLAIN的"row"列来估算结果总共有多少条目。文章中称google是这样估算结果集的: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,(注意这块的filesort,请参看:http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort-mean-in-mysql/);假想如果如果满足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”会给你一个大概的结果。(这个办法不是处处都行,但是某些地方效果是很好的)这些办法可以很大程度上减轻数据库的压力,而且对用户体验不会有什么影响。

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


  • 2020-12-16 23:06:05

    Rocket.Chat推送信息

    Rocket.Chat推送消息 Rocket.Chat是一个开源实时通讯平台, 支持Windows, Mac OS, Linux. 支持聊天, 文件上传, 视频通话, 语音通话功能. 向Rocket.Chat推送消息 以下示例可以转为别的语言的版本, 本示例使用Linux平台的curl测试, curl非常强大. 登陆 首先需要登陆Rocket.Chat服务器

  • 2020-12-17 09:01:23

    对BitTorrent Tracker源码分析

    tracker服务器是BT下载中必须的角色。一个BT client 在下载开始以及下载进行的过程中,要不停的与 tracker 服务器进行通信,以报告自己的信息,并获取其它下载client的信息。这种通信是通过 HTTP 协议进行的,又被称为 tracker HTTP 协议,它的过程是这样的: client 向 tracker 发一个HTTP 的GET请求,并把它自己的信息放在GET的参数中;这个请求的大致意思是:我是xxx(一个唯一的id),我想下载yyy文件,我的ip是aaa,我用的端口是bbb。。。

  • 2020-12-17 10:55:48

    html5 video p2p research

    节约带宽,减少缓冲时间,提升服务质量,处理峰值流量, 视频观看的人越多,播放越流畅。

  • 2020-12-17 10:57:34

    使用 MediaSource 搭建流式播放器

    Media Source Extensions(媒体源扩展)大大地扩展了浏览器的媒体播放功能,提供允许JavaScript 生成媒体流。这可以用于自适应流(adaptive streaming,也是我毕设的研究方向)及随时间变化的视频直播流(live streaming)等应用场景。

  • 2020-12-17 11:00:37

    H5流式播放(FMP4转封装与mediaSource)

    W3C上有明确关于mediaSource 扩展接口的文档。mediaSource 扩展文档中是这么定义的, 它允许JS脚本动态构建媒体流用于和,允许JS传送媒体块到H5媒体元素。这种接口的应用可以让h5播放器实现持续添加数据进行播放。做as的朋友都知道as中的appendBytes方法,一种添加二进制数据进行播放的方式。这两种接口在概念上是类似的。只是里面的定义和对媒体文件的要求有所不同。对于mediaSource扩展接口我只介绍我们主要应用的几个。

  • 2020-12-18 17:15:29

    coTurn stun服务器搭建,禁用turn

    https://github.com/coturn/coturn 在这里git clone 下来然后编译安装,一切默认即可。编译后,也可以不用安装。在编译目录下bin文件夹下有turnserver turnutils_stunclient turnutils_uclient 这三个等一下会用到的软件。