PHP+MySQL高效的分页方法,如何优化LIMIT,OFFSET进行的分页?

很多应用往往只展示最新或最热门的几条记录，但为了旧记录仍然可访问，所以就需要个分页的导航栏。然而，如何通过MySQL更好的实现分页，始终是比较令人头疼的问题。虽然没有拿来就能用的解决办法，但了解数据库的底层或多或少有助于优化分页查询。

我们先从一个常用但性能很差的查询来看一看。

SELECT

FROM city

ORDER BY id DESC

LIMIT 0, 15

这个查询耗时000sec。So，这个查询有什么问题呢？实际上，这个查询语句和参数都没有问题，因为它用到了下面表的主键，而且只读取15条记录。

CREATE TABLE city (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

city varchar(128) NOT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB;

真正的问题在于offset(分页偏移量)很大的时候，像下面这样：

SELECT

FROM city

ORDER BY id DESC

LIMIT 100000, 15;

上面的查询在有2M行记录时需要022sec，通过EXPLAIN查看SQL的执行计划可以发现该SQL检索了100015行，但最后只需要15行。大的分页偏移量会增加使用的数据，MySQL会将大量最终不会使用的数据加载到内存中。就算我们假设大部分网站的用户只访问前几页数据，但少量的大的分页偏移量的请求也会对整个系统造成危害。Facebook意识到了这一点，但Facebook并没有为了每秒可以处理更多的请求而去优化数据库，而是将重心放在将请求响应时间的方差变小。

对于分页请求，还有一个信息也很重要，就是总共的记录数。我们可以通过下面的查询很容易的获取总的记录数。

SELECT COUNT()

FROM city;

然而，上面的SQL在采用InnoDB为存储引擎时需要耗费928sec。一个不正确的优化是采用 SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS 可以在能够在分页查询时事先准备好符合条件的记录数，随后只要执行一句 select FOUND_ROWS(); 就能获得总记录数。但是在大多数情况下，查询语句简短并不意味着性能的提高。不幸的是，这种分页查询方式在许多主流框架中都有用到，下面看看这个语句的查询性能。

SELECT SQL_CALC_FOUND_ROWS

FROM city

ORDER BY id DESC

LIMIT 100000, 15;

这个语句耗时2002sec，是上一个的两倍。事实证明使用 SQL_CALC_FOUND_ROWS 做分页是很糟糕的想法。

下面来看看到底如何优化。文章分为两部分，第一部分是如何获取记录的总数目，第二部分是获取真正的记录。

高效的计算行数

如果采用的引擎是MyISAM，可以直接执行COUNT()去获取行数即可。相似的，在堆表中也会将行数存储到表的元信息中。但如果引擎是InnoDB情况就会复杂一些，因为InnoDB不保存表的具体行数。

我们可以将行数缓存起来，然后可以通过一个守护进程定期更新或者用户的某些操作导致缓存失效时，执行下面的语句：

SELECT COUNT()

FROM city

USE INDEX(PRIMARY);

获取记录

下面进入这篇文章最重要的部分，获取分页要展示的记录。上面已经说过了，大的偏移量会影响性能，所以我们要重写查询语句。为了演示，我们创建一个新的表“news”，按照时事性排序(最新发布的在最前面)，实现一个高性能的分页。为了简单，我们就假设最新发布的新闻的Id也是最大的。

CREATE TABLE news(

id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

title VARCHAR(128) NOT NULL

) ENGINE=InnoDB;

一个比较高效的方式是基于用户展示的最后一个新闻Id。查询下一页的语句如下，需要传入当前页面展示的最后一个Id。

SELECT

FROM news WHERE id < $last_id

ORDER BY id DESC

LIMIT $perpage

查询上一页的语句类似，只不过需要传入当前页的第一个Id，并且要逆序。

SELECT

FROM news WHERE id > $last_id

ORDER BY id ASC

LIMIT $perpage

上面的查询方式适合实现简易的分页，即不显示具体的页数导航，只显示“上一页”和“下一页”，例如博客中页脚显示“上一页”，“下一页”的按钮。但如果要实现真正的页面导航还是很难的,下面看看另一种方式。

SELECT id

FROM (

SELECT id, ((@cnt:= @cnt + 1) + $perpage - 1) % $perpage cnt

FROM news

JOIN (SELECT @cnt:= 0)T

WHERE id < $last_id

ORDER BY id DESC

LIMIT $perpage $buttons

)C

WHERE cnt = 0;

通过上面的语句可以为每一个分页的按钮计算出一个offset对应的id。这种方法还有一个好处。假设，网站上正在发布一片新的文章，那么所有文章的位置都会往后移一位，所以如果用户在发布文章时换页，那么他会看见一篇文章两次。如果固定了每个按钮的offset Id，这个问题就迎刃而解了。Mark Callaghan发表过一篇类似的博客，利用了组合索引和两个位置变量，但是基本思想是一致的。

如果表中的记录很少被删除、修改，还可以将记录对应的页码存储到表中，并在该列上创建合适的索引。采用这种方式，当新增一个记录的时候，需要执行下面的查询重新生成对应的页号。

SET p:= 0;

UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;

当然，也可以新增一个专用于分页的表，可以用个后台程序来维护。

UPDATE pagination T

JOIN (

SELECT id, CEIL((p:= p + 1) / $perpage) page

FROM news

ORDER BY id

)C

ON Cid = Tid

SET Tpage = Cpage;

现在想获取任意一页的元素就很简单了：

SELECT

FROM news A

JOIN pagination B ON Aid=BID

WHERE page=$offset;

还有另外一种与上种方法比较相似的方法来做分页，这种方式比较试用于数据集相对小，并且没有可用的索引的情况下—比如处理搜索结果时。在一个普通的服务器上执行下面的查询，当有2M条记录时，要耗费2sec左右。这种方式比较简单，创建一个用来存储所有Id的临时表即可(这也是最耗费性能的地方)。

CREATE TEMPORARY TABLE _tmp (KEY SORT(random))

SELECT id, FLOOR(RAND() 0x8000000) random

FROM city;

ALTER TABLE _tmp ADD OFFSET INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, DROP INDEX SORT,ORDER BY random;

接下来就可以向下面一样执行分页查询了。

SELECT

FROM _tmp

WHERE OFFSET >= $offset

ORDER BY OFFSET

LIMIT $perpage;

简单来说，对于分页的优化就是。。。避免数据量大时扫描过多的记录。

sleep不占用系统资源，它会把cpu的控制权交换给内核，内核会再schedule到另一个进程。

这种简单的事情，你写个while (true) sleep(1)的程序然后top一下就能判断的。

Nodejs发布于2009年5月，由Ryan Dahl开发，是一个基于Chrome V8引擎的JavaScript运行环境，使用了一个事件驱动、非阻塞式I/O模型， 让JavaScript 运行在服务端的开发平台，它让JavaScript成为与PHP、Python、Perl、Ruby等服务端语言平起平坐的脚本语言

PHP即“超文本预处理器”，是一种通用开源脚本语言。PHP是在服务器端执行的脚本语言，与C语言类似，是常用的网站编程语言。PHP独特的语法混合了C、Java、Perl以及 PHP 自创的语法。利于学习，使用广泛，主要适用于Web开发领域。

Nodejs遵循事件驱动的非阻塞I/O模型，PHP遵循一个阻塞模型；

PHP应用程序可以在任何服务器上运行，Node不行。

1、性能方面：

由于 Nodejs 遵循事件驱动的非阻塞I/O模型，与传统的后端技术相比，Nodejs 能够处理大量的服务器请求。PHP 遵循一个阻塞模型，其中在服务器端创建多个线程来处理多个客户端请求，如果代码没有被优化，它会导致高服务器负载和响应时间。

2、托管和部署：

PHP 应用程序可以在包括 Nginx 和 Apache 以及 Windows 和 Linux 平台在内的任何服务器上运行，这使得部署过程变得更容易。

而 Nodejs，则需要具有 SSH 访问权限的虚拟服务器。因此，在没有任何控制台命令和 Secure Shell(SSH)知识的情况下，用 PHP 部署小型企业和个人应用会更好。

3、外部依赖：

Nodejs 依赖关系较少，用几行代码就可以设置一个 Web 服务器。但运行 PHP 应用程序，就需要外部服务器软件。用 Nodejs 的开发人员只需要 NPM(节点包管理器)就可以下载 Node 模块，这些模块可以轻松地集成到应用程序中，提供额外的功能。

4、CPU 扩展任务：

Nodejs 可能在高吞吐量方面表现优异，但是当应用程序繁重，需要 CPU 做大量工作时，Nodejs 肯定会有所欠缺。在这种情况下，PHP 最适合做 Web 应用程序的后端。

5、单页应用程序：

如果您使用过 Gmail，那么您可能知道什么是单页应用程序(Single Page Application)。Web 应用程序在初始客户端请求时加载，之后根据客户端请求刷新的网页的特定部分。Nodejs 与 AngularJS 的联合是开发单页应用程序的完美方案。

6、开发环境：

Nodejs 可以用于桌面应用程序开发(Windows，Linux和Mac)，移动应用程序开发(混合和跨平台应用程序)，甚至构建基于控制台的应用程序。虽然有一些也可以使用 PHP 来开发，但是大多数开发人员不会这样做。

7、社区支持：

由于 PHP 比 Nodejs 早诞生将近20年，所以在文档，API 和代码库等在线资源方面更为丰富。所以很有可能其他人也尝试了你想要用代码完成的事情并从中获得了帮助。

另一方面，Nodejs 虽然已经得到了开发者和组织的广泛关注，但是它支持开发者开发的资源缺相当少。

8、学习曲线：

编写 Nodejs 代码对于新手甚至是专业的 JavaScript 开发人员来说都不是件容易的事情，因为他们需要在入门之前理解一些复杂的概念，比如 web worker 和回调函数。相反，PHP是一门非常友好的编程语言对于开发 Web 应用程序的新手来说。

Nodejs 和 PHP是相辅相成的，因为这两种技术都有各自的优点和缺点。这大多是相似的情况在比较或选择用于Web，桌面和移动开发的其他技术时。Nodejs有前途，并且肯定会占据PHP市场的一部分，但是不能完全取代 PHP。

php的执行是一定要触发的，不会自动去执行。

你可以在程序里加入sleep(10);这句罢。这句会让程序挂起10秒，然后继续，理论上可以。

不过好像响应时间太久的话，连接会中断的吧。

你可以把数据库的内容全部读出来并赋给一个javascript的数组，再用javascript的setTimeout方法进行输出。

当然也可以用ajax来完成这样就不用一次性全部取出来放在javascript的数组中了，可以用到时用ajax去读取一条。