189 8069 5689
直接用limit start, count分页语句， 也是我程序中用的方法：
网站设计制作过程拒绝使用模板建站；使用PHP+MYSQL原生开发可交付网站源代码;符合网站优化排名的后台管理系统；成都网站设计、成都网站建设收费合理；免费进行网站备案等企业网站建设一条龙服务.我们是一家持续稳定运营了十载的创新互联网站建设公司。
select * from product limit start, count
当起始页较小时，查询没有性能问题，我们分别看下从10， 100， 1000， 10000开始分页的执行时间（每页取20条）， 如下：
select * from product limit 10, 20 0.016秒
select * from product limit 100, 20 0.016秒
select * from product limit 1000, 20 0.047秒
select * from product limit 10000, 20 0.094秒
我们已经看出随着起始记录的增加，时间也随着增大， 这说明分页语句limit跟起始页码是有很大关系的，那么我们把起始记录改为40w看下（也就是记录的一般左右） select * from product limit 400000, 20 3.229秒
再看我们取最后一页记录的时间
select * from product limit 866613, 20 37.44秒
难怪搜索引擎抓取我们页面的时候经常会报超时，像这种分页最大的页码页显然这种时
间是无法忍受的。
从中我们也能总结出两件事情：
1）limit语句的查询时间与起始记录的位置成正比
2）mysql的limit语句是很方便，但是对记录很多的表并不适合直接使用。
用一个分页类。直接调用。
?php
/**
* 分页类
* 用DBMySQL类读取数据库
*/
class BreakPage
{
//属性开始
var $pageparam="pageno";
var $sql;//显示数据的sql
var $countsql;//显示总记录数的SQL
var $max_line;//最大记录数
var $begin_record;//开始记录数
var $current_page;//当前页
var $total_record;//总记录数
var $total_page;//总页数
var $conn_id;//数据库连接
var $query_id;//查询句柄
var $row_num;//当页记录的条数
var $url;//url
var $param="";
//属性结束
function setpageparam($pageparam)
{
$this-pageparam=$pageparam;
}
function getpageparam()
{
return $this-pageparam;
}
function setrows($rows)
{
$this-row_num=$rows;
}
function getrows()
{
return $this-row_num;
}
function setparam($param)
{
$this-param=$param;
}
function getparam()
{
return $this-param;
}
function seturl($url)
{
$this-url=$url;
}
function geturl()
{
return $this-url;
}
//属设置开始
function setcountsql($countsql)
{
$this-countsql="select count(*) as count from (".$countsql.") as temp1";
}
function getcountsql()
{
return $this-countsql;
}
function settotalpage($totalpage)
{
$this-total_page=$totalpage;
}
function gettotalpage()
{
return $this-total_page;
}
function settotalrecord($totalrecord)
{
$this-total_record=$totalrecord;
}
function gettotalrecord()
{
return $this-total_record;
}
/**
* 设置当前页，从1开始
*
* @param unknown_type $currentpage
*/
function setcurrentpage($currentpage)
{
$this-current_page=$currentpage1? 1:$currentpage;
}
function getcurrentpage()
{
return $this-current_page;
}
function setbeginline($begin)
{
$this-begin_record=$begin;
}
function getbeginline()
{
return $this-begin_record;
}
function setsql($sql)
{
$this-sql=$sql;
}
function getsql()
{
return $this-sql;
}
/**
* 设置最大记录数
* @param unknown_type $maxline
*/
function setmaxline($maxline)
{
$this-max_line=$maxline;
}
function getmaxline()
{
return $this-max_line;
}
//属性设置结束
function __construct()
{//连接数据库
$this-setbeginline(0);
$this-setmaxline(10);
}
function __destruct()
{
}
//方法开始
/**
* 主体函数，查询当前页的记录，同时返回信息
* @param string $sql:查询的SQL，如select * from table1;
* @param string $countsql:总数量的SQL，如select count(*) from table1;
* @return 当前页的记录
*/
function getrecords($db,$sql)
{
$this-setsql($sql);//设置查询的SQL
$this-setcountsql($sql);//设置查询总数量的SQL
//查询总记录数,可用gettotalrecord()方法返回总记录数
$db-query($this-getcountsql());
$db-seek(0);
//返总记录数
$this-settotalrecord($db-f(0));
$db-free();
//根据当前页和每页最大记录数计算开始和结束记录
//可用getbeginline()方法查询开始记录值，即limit的开始值
//可用getmaxline()方法查询每页记录数，即limit的第二个限制值
$this-setbeginline(($this-getcurrentpage()-1)*$this-getmaxline());//设置开始记录值，即limit的开始值
//计算结束
//计算总页数
//可用gettotalpage()查询总页数
$this-settotalpage((int)(($this-gettotalrecord()+$this-getmaxline()-1)/$this-getmaxline()));//(总记录数+每页记录数-1)/每页记录数
//计算结束
//返回当前记录
$temp_sql=$this-getsql()." limit ".$this-getbeginline().",".$this-getmaxline();//构造限制的SQL语句
$db-query($temp_sql);
$result=$db-getalldata();
return $result;
//结束
}
function paint()
{
$prepg=$this-getcurrentpage()=1? 1:$this-getcurrentpage()-1;//上一页
$nextpg=$this-getcurrentpage()=$this-gettotalpage()? $this-gettotalpage():$this-getcurrentpage()+1;//下一页
$otherparam=$this-getparam()!=""? "".$this-getparam():"";
$pagenav="tabletr";
if($this-getcurrentpage()==1)
{//第一页
$pagenav.="td nowrap='nowrap' style='font-size: 12px'a disabled='disabled'首页/a/tdtd nowrap='nowrap' style='font-size: 12px' /td";
}
else
{
$pagenav.="td nowrap='nowrap' style='font-size: 12px'a href='".$this-geturl()."?".$this-getpageparam()."=1$otherparam'首页/a/tdtd nowrap='nowrap' style='font-size: 12px' /td";
}
if($this-getcurrentpage()1)
{//有上一页
$pagenav.="td nowrap='nowrap' style='font-size: 12px'a href='".$this-geturl()."?".$this-getpageparam()."=$prepg$otherparam'上一页/a/td";
}
else
{//第一页
$pagenav.="td nowrap='nowrap' style='font-size: 12px'a disabled='disabled'上一页/a/td";
}
if($this-getcurrentpage()$this-gettotalpage())
{//有下一页
$pagenav.="td nowrap='nowrap' style='font-size: 12px' a href='".$this-geturl()."?".$this-getpageparam()."=$nextpg$otherparam'下一页/a/td";
}
else
{
$pagenav.="td nowrap='nowrap' style='font-size: 12px' a disabled='disabled'下一页/a/td";
}
if($this-getcurrentpage()=$this-gettotalpage())
{//没有下一页
$pagenav.="td nowrap='nowrap' style='font-size: 12px'/tdtd nowrap='nowrap' style='font-size: 12px'a disabled='disabled'尾页/a/td";
}
else
{
$pagenav.="td nowrap='nowrap' style='font-size: 12px'/tdtd nowrap='nowrap' style='font-size: 12px'a href='".$this-geturl()."?".$this-getpageparam()."=".$this-gettotalpage()."$otherparam'尾页/a/td";
}
//下拉跳转列表，循环列出所有页码：
$onchange=$otherparam!=""? 'window.location="'.$this-url.'?'.$this-getpageparam().'="+this.value+"'.$otherparam.'"':'window.location="'.$this-url.'?'.$this-getpageparam().'="+this.value';
$pagenav.="td nowrap='nowrap' style='font-size: 12px' 共font color=red".$this-gettotalpage()."/font页/tdtd nowrap='nowrap' style='font-size: 12px' 第font color=red".$this-getcurrentpage()."/font页/tdtd nowrap='nowrap' style='font-size: 12px' 转到第/tdtd nowrap='nowrap'select name='topage' size='1' onchange='$onchange'";
for($i=1;$i=$this-gettotalpage();$i++)
{
if($i==$this-getcurrentpage())
{
$pagenav.="option value='$i' selected$i/option\n";
}
else
{
$pagenav.="option value='$i'$i/option\n";
}
}
$pagenav.="/select/tdtd nowrap='nowrap' style='font-size: 12px'页/td";
$pagenav.="/tr/table";
return $pagenav;
}
//方法结束
}
?
分页查询一般 DBA 想到的办法是在某个（如ID，create_time)字段上加组合索引。这样条件排序都能有效的利用到索引，性能迅速提升。
因为如果当 LIMIT 子句变成 “LIMIT 1000000,10” 时，你会抱怨：我只取10条记录为什么还是慢？
要知道数据库也并不知道第1000000条记录从什么地方开始，即使有索引也需要从头计算一次。出现这种性能问题，多数情形下是程序员偷懒了。在前端数据浏览翻页，或者大数据分批导出等场景下，是可以将上一页的最大值当成参数作为查询条件的。SQL 重新设计如下：
SELECT *
FROM 表
WHERE create_time '2017-07-04 09:00:00'
ORDER BY create_time limit 10;
这样查询时间基本固定，不会随着数据量的增长而发生变化。
pageNow代表当前页面，第一页。
第一部分：看一下分页的基本原理：
对上面的mysql语句说明：limit 10000,20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行，问题就在这里，如果是limit 100000,100，需要扫描100100行，在一个高并发的应用里，每次查询需要扫描超过10W行，性能肯定大打折扣。文中还提到limit n性能是没问题的，因为只扫描n行。
第二部分：根据雅虎的几位工程师带来了一篇Efficient Pagination Using MySQL的报告内容扩展：在文中提到一种clue的做法，给翻页提供一些线索，比如还是SELECT * FROM message ORDER BY id DESC，按id降序分页，每页20条，当前是第10页，当前页条目id最大的是1020，最小的是1000，如果我们只提供上一页、下一页这样的跳转（不提供到第N页的跳转），那么在处理上一页的时候SQL语句可以是：
处理下一页的时候SQL语句可以是：
不管翻多少页，每次查询只扫描20行。
缺点是只能提供上一页、下一页的链接形式，但是我们的产品经理非常喜欢“上一页 1 2 3 4 5 6 7 8 9 下一页”这样的链接方式，怎么办呢？
如果LIMIT m,n不可避免的话，要优化效率，只有尽可能的让m小一下，我们扩展前面的clue做法，还是SELECT * FROM message ORDER BY id DESC，按id降序分页，每页20条，当前是第10页，当前页条目id最大的是2519，最小的是2500;
当是第10页的SQL如下：
比如要跳到第9页，SQL语句可以这样写：
比如要跳到第8页，SQL语句可以这样写：
原理还是一样，记录住当前页id的最大值和最小值，计算跳转页面和当前页相对偏移，由于页面相近，这个偏移量不会很大，这样的话m值相对较小，大大减少扫描的行数。其实传统的limit m,n，相对的偏移一直是第一页，这样的话越翻到后面，效率越差，而上面给出的方法就没有这样的问题。
很多应用往往只展示最新或最热门的几条记录，但为了旧记录仍然可访问，所以就需要个分页的导航栏。然而，如何通过MySQL更好的实现分页，始终是比较令人头疼的问题。虽然没有拿来就能用的解决办法，但了解数据库的底层或多或少有助于优化分页查询。
我们先从一个常用但性能很差的查询来看一看。
SELECT *
FROM city
ORDER BY id DESC
LIMIT 0, 15
这个查询耗时0.00sec。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行记录时需要0.22sec，通过EXPLAIN查看SQL的执行计划可以发现该SQL检索了100015行，但最后只需要15行。大的分页偏移量会增加使用的数据，MySQL会将大量最终不会使用的数据加载到内存中。就算我们假设大部分网站的用户只访问前几页数据，但少量的大的分页偏移量的请求也会对整个系统造成危害。Facebook意识到了这一点，但Facebook并没有为了每秒可以处理更多的请求而去优化数据库，而是将重心放在将请求响应时间的方差变小。
对于分页请求，还有一个信息也很重要，就是总共的记录数。我们可以通过下面的查询很容易的获取总的记录数。
SELECT COUNT(*)
FROM city;
然而，上面的SQL在采用InnoDB为存储引擎时需要耗费9.28sec。一个不正确的优化是采用 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;
这个语句耗时20.02sec，是上一个的两倍。事实证明使用 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 C.id = T.id
SET T.page = C.page;
现在想获取任意一页的元素就很简单了：
SELECT *
FROM news A
JOIN pagination B ON A.id=B.ID
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;
简单来说，对于分页的优化就是。。。避免数据量大时扫描过多的记录。
1、首先我们建立一个表表的数据，这个表里有25条数据，id从1到25。（下图是部分截图）
2、要分页数据，首先我们假设一页有10条数据，我们可以用mysql的 limit关键字来限定返回多少条数据。并且用order by来排序数据，这里用 id来排序。所以第一页的sql可以如图这样写。
3、执行后得到的数据如图，就是 id从1到10的前10条数据，因为我们是按id升序来排序的。
4、上面第一页的sql是简化的写法，完整的写法如图，得到的结果和上图的一模一样。代码里 limit 0, 10 的意思是从第一条数据开始，取10条数据。（注意的是第一条数据是从0开始的）
5、那么第二页的数据，关键是要知道是从哪一条数据开始，可以用这个公式得到： (页码-1) * 每页显示多少条，即 （2-1) * 10 = 10, 所以sql语句如图, limit 10, 10。
6、执行后，结果正确，得到id从11到20的10条数据。
7、同理第三页数据的sql如图，br/就是 limit 20, 10。
8、查询的结果如图，因为这页只剩下5条数据了，所以只显示5条数据。如果你有更多页的数据，后面的数据只需要按上面的公式，得到从哪行开始，就可以写对应的sql语句了。