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;

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

<php / PHP动态分页测试代码 / //代码开始↓ if(empty($page)) $page=0; //默认页码值为0(即第一页) $total=152; //整体要显示的条目总数 $pagenum=10; //每页显示条目数 //写各页的内容（可以处理其他事件） for($i=$page$pagenum;$i<$page$pagenum+$pagenum;$i++){ $tmpstr="第 " ($i+1) " 条记录"; if($i<$total) print "$tmpstr<br>"; } //end of for //得到总页数 $tpage 的值 if($total%$pagenum==0) { //若被整除 $tpage=$total/$pagenum; } else { //否则用floor函数取得最大整数然后+1 $tpage=floor($total/$pagenum)+1; } //智能化的前进后退+段页码方式实现超链 $id_pd=floor($page/5)5; //当前页页码数段初始id值 $startpagepre=$id_pd-5; $startpagenext=$id_pd+5; $lastpage=$tpage-1; print "<p>"; //处理首页（<<）和前五页（<） if($page==0){ print "<span style=\"color:red\"><<</span> "; }else{ print "<a title=\"首页\" href=\"$php_selfpage=0\"><<</a> "; } if($page<5){ print "<span style=\"color:red\"><</span> "; }else{ print "<a title=\"前五页\" href=\"$php_selfpage=$startpagepre\"><</a> "; } //写页段中的页码超链 for($i=$id_pd;$i<$id_pd+5;$i++){ $pg=$i+1; //页码数 if($i==$page){ if($i<$tpage) print "<span style=\"color:red\">$pg</span></a> "; }else{ if($i<$tpage) print "<a href=\"$php_selfpage=$i\">$pg</a> "; } } //处理后五页（>）和尾页（>>） if($page>$tpage-5){ print "<span style=\"color:red\">></span> "; }else{ print "<a title=\"后五页\" href=\"$php_selfpage=$startpagenext\">></a> "; } if($page==$lastpage){ print "<span style=\"color:red\">>></span> "; }else{ print "<a title=\"尾页\" href=\"$php_selfpage=$lastpage\">>></a> "; } print "总页数 <span style=\"color:red\">$tpage</span></p>"; >

//Page Code

$threadtotal=db_num_rows(db_query('SELECT tid FROM '$tablepre'threads WHERE closed<=1 AND readperm=0 AND price=0'$whereclause' AND fid='$fid));

$totalpage=ceil($threadtotal/$threadperpage);

$firstpagelink='/'$qmark'pagetype-bbsforum-and-fid-'$fid'-and-page-1html';

$lastpagelink='/'$qmark'pagetype-bbsforum-and-fid-'$fid'-and-page-'$totalpage'html';

$prepage= $page-1>0 $page-1 : 1;

$nextpage= $page+1<=$totalpage $page+1 : $totalpage;

$previouspagelink='/'$qmark'pagetype-bbsforum-and-fid-'$fid'-and-page-'$prepage'html';

$nextpagelink='/'$qmark'pagetype-bbsforum-and-fid-'$fid'-and-page-'$nextpage'html';

$pagenumarray=array();

for ($i=1;$i<=$totalpage;$i++) {

if ($i>=($page-$pagenumoffset) and $i<=($page+$pagenumoffset)) {

$id=$i-1;

$pagenumarray[$id]['link']='/'$qmark'pagetype-bbsforum-and-fid-'$fid'-and-page-'$i'html';

if ($i==$page) {

$pagenumarray[$id]['num']='<strong>'$i'</strong>';

}else{

$pagenumarray[$id]['num']=$i;

}

}

}

php本身是没有分页概念的，分页是URL传参，然后通过mysql查询语句到数据库获取数据，然后实现的分页，url上的参数，通过PHP的$_GET都是可以获取到的。

现在市面上的PHP框架基本都有PHP分页类，参照文档直接调用就好了，如果想看实现过程，可以去下载一个TP框架，然后打开里面的分页类查看里面的源代码。

我们已经了解了如何进行数据查询，今天我们来学习下如何对数据进行分页以及显示。

获取分页类

ThinkPHP提供了数据分页的扩展类库Page，可以在>

一个分页函数，赋好值就可以使用了

/

'PID 页面中的其它传参连接

'page 要显示的页码

'Vcount 总记录数

'Pnum 每页记录数

'className 超连接样式

/

function ThePage($Pid,$page=1,$Vcount,$Pnum,$className){

$Pcount = ceil($Vcount/$Pnum);

if($page>$Pcount) $page=$Pcount;

$StartPageNum=1;

while($StartPageNum+10<=$page){

$StartPageNum=$StartPageNum+10;

}

$EndPageNum=$StartPageNum+9 ;

if($EndPageNum>$Pcount) $EndPageNum=$Pcount;

$show = $show"<tr><td colspan='10' bgcolor='' align='right' style='padding-right:25px;padding-top:5px;' class='"$className"'>";

$show = $show"共<b>"$Pcount"</b>页 共有<b>"$Vcount"</b>记录 第<b>"$page"</b>页 跳到第 [ ";

if($StartPageNum>1) $show = $show"<a class='"$className"' href='"$Pid"page="($StartPageNum-1)"' title='前十页'><font face='webdings'>7</font></a>";

for($go=$StartPageNum;$go<=$EndPageNum;$go++){

if($go!=$page){

$show = $show"<a class='"$className"' href='"$Pid"page="$go"'>"$go"</a>";

}else{

$show = $show"<b>"$go"</b>";

}

$show = $show" ";

}

if($EndPageNum<$Pcount) $show = $show"<a class='"$className"' href='"$Pid"page="($EndPageNum+1)"' title='下十页'><font face='webdings'>8</font></a>";

$show = $show"] 页";

if($page!=1) $show = $show"<a class='"$className"' href='"$Pid"page=1'> 首页</a> <a class='"$className"' href='"$Pid"page="($page-1)"'>上一页</a>";

if($Pcount-$page!=0) $show = $show "<a class='"$className"' href='"$Pid"page="($page+1)"'> 下一页</a> <a class='"$className"' href='"$Pid"page="$Pcount"'>尾页</a>";

$show=$show"</tr></td>";

return $show;

}

========================附带一个使用例子=============================

<

//设置参数

$page = $_REQUEST["page"];

if($page=="") $page = 1;

$T = $_GET["T"];

if($T=="") $T = 1;

$Pid = "T="$T"&";

$wheresql = "where T = "$T;

$recordListTable = "zc_news";

$Pnum = 10;

$classname = "lwf";

//以下不用修改

$rs = mysql_query("select count() from `"$recordListTable"` $wheresql ");

$Vcount = @mysql_result($rs,0);

@mysql_free_result($rs);

$start = ($page - 1) $Pnum;

$lastNum = $Vcount - ($page - 1) $Pnum;

$limit = $lastNum > $Pnum $Pnum : $lastNum;

$rs = mysql_query("select from `"$recordListTable"` $wheresql order by orderid desc,ID desc LIMIT $start,$limit");

while($ary = mysql_fetch_array($rs)){

>

<tr>

<td>标题</td>

</tr>

<

}

@mysql_free_result($rs);

echo ThePage($Pid,$page,$Vcount,$Pnum,$classname);

>

以上就是关于PHP+MySQL高效的分页方法,如何优化LIMIT,OFFSET进行的分页全部的内容，包括:PHP+MySQL高效的分页方法,如何优化LIMIT,OFFSET进行的分页、PHP网页中内容太多如何让他自动分页、请教一个PHP分页问题等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！