mysql 百万条数据分页优化

PHP写分页功能时，只要用的还是MySQL，基本都是两步走

1、取得总数，算页数。SQL语句自然是

代码如下	复制代码
SELECT count(*) FROM tablename;

2、根据指定的页码号，取得相应的数据。对应的SQL语句，在网上随便查，都是一样的：

SELECT　f1,f2 FROM table LIMIT offset,length

实例分页类

代码如下

复制代码

<?php
/*********************************************
类名： PageSupport
功能：分页显示MySQL数据库中的数据
***********************************************/
class PageSupport{
//属性
var $sql; //所要显示数据的SQL查询语句
var $page_size; //每页显示最多行数

var $start_index; //所要显示记录的首行序号
var $total_records; //记录总数
var $current_records; //本页读取的记录数
var $result; //读出的结果

var $total_pages; //总页数
var $current_page; //当前页数
var $display_count = 30; //显示的前几页和后几页数

var $arr_page_query; //数组，包含分页显示需要传递的参数

var $first;
var $prev;
var $next;
var $last;

//方法
/*********************************************
构造函数：__construct()
输入参数：
$ppage_size：每页显示最多行数
***********************************************/
function PageSupport($ppage_size)
{
$this->page_size=$ppage_size;
$this->start_index=0;
}

/*********************************************
构造函数：__destruct()
输入参数：
***********************************************/
function __destruct()
{

}

/*********************************************
get函数：__get()
***********************************************/
function __get($property_name)
{
if(isset($this->$property_name))
{
return($this->$property_name);
}
else
{
return(NULL);
}
}

/*********************************************
set函数：__set()
***********************************************/
function __set($property_name, $value)
{
$this->$property_name = $value;
}

/*********************************************
函数名：read_data
功能：根据SQL查询语句从表中读取相应的记录
返回值：属性二维数组result[记录号][字段名]
***********************************************/
function read_data()
{
$psql=$this->sql;

//查询数据，数据库链接等信息应在类调用的外部实现
$result=mysql_query($psql) or die(mysql_error());
$this->total_records=mysql_num_rows($result);

//利用LIMIT关键字获取本页所要显示的记录
if($this->total_records>0)
{
$this->start_index = ($this->current_page-1)*$this->page_size;
$psql=$psql. " LIMIT ".$this->start_index." , ".$this->page_size;

$result=mysql_query($psql) or die(mysql_error());
$this->current_records=mysql_num_rows($result);

//将查询结果放在result数组中
$i=0;
while($row=mysql_fetch_Array($result))
{
$this->result[$i]=$row;
$i++;
}
}

//获取总页数、当前页信息
$this->total_pages=ceil($this->total_records/$this->page_size);

$this->first=1;
$this->prev=$this->current_page-1;
$this->next=$this->current_page+1;
$this->last=$this->total_pages;
}

/*********************************************
函数名：standard_navigate()
功能：显示首页、下页、上页、未页
***********************************************/
function standard_navigate()
{
echo "<div align=center>";
echo "<form action=".$_SERVER['PHP_SELF']." method="get">";

echo "<font color = red size ='4'>第".$this->current_page."页/共".$this->total_pages."页</font>";
echo " ";

echo "跳到<input type="text" size=Ř" name="current_page" value='".$this->current_page."'/>页";
echo "<input type="submit" value="提交"/>";

//生成导航链接
if ($this->current_page > 1) {
echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->first.">首页</A>|";
echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->prev.">上一页</A>|";
}

if( $this->current_page < $this->total_pages) {
echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->next.">下一页</A>|";
echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->last.">末页</A>";
}

echo "</form>";
echo "</div>";

}

/*********************************************
函数名：full_navigate()
功能：显示首页、下页、上页、未页
生成导航链接如1 2 3 ... 10 11
***********************************************/
function full_navigate()
{
echo "<div align=center>";
echo "<form action=".$_SERVER['PHP_SELF']." method="get">";

echo "<font color = red size ='4'>第".$this->current_page."页/共".$this->total_pages."页</font>";
echo " ";

echo "跳到<input type="text" size=Ř" name="current_page" value='".$this->current_page."'/>页";
echo "<input type="submit" value="提交"/>";

//生成导航链接如1 2 3 ... 10 11
$front_start = 1;
if($this->current_page > $this->display_count){
$front_start = $this->current_page - $this->display_count;
}
for($i=$front_start;$i<$this->current_page;$i++){
echo "<a href=".$_SERVER['PHP_SELF']."?page=".$i.">[".$i ."]</a> ";
}

echo "[".$this->current_page."]";

$displayCount = $this->display_count;
if($this->total_pages > $displayCount&&($this->current_page+$displayCount)<$this->total_pages){
$displayCount = $this->current_page+$displayCount;
}else{
$displayCount = $this->total_pages;
}

for($i=$this->current_page+1;$i<=$displayCount;$i++){
echo "<a href=".$_SERVER['PHP_SELF']."?current_page=".$i.">[".$i ."]</a> ";
}

echo "</form>";
echo "</div>";

}

}
?>

调用:

<?php

include_once("../config_jj/sys_conf.inc");
include_once("../PageSupportClass.php");//分页类
include_once('../Smarty_JsnhClass.php');

$smarty = new Smarty_Jsnh();
include_once("../include/Smarty_changed_dir.php");
$smarty->assign('title', "Smarty新闻分页测试");

<?php

$pageSupport = new PageSupport($PAGE_SIZE); //实例化PageSupport对象

$current_page=$_GET["current_page"];//分页当前页数

if (isset($current_page)) {

$pageSupport->__set("current_page",$current_page);

} else {

$pageSupport->__set("current_page",1);

}

?>
$pageSupport->__set("sql","select * from news ");
$pageSupport->read_data();//读数据

if ($pageSupport->current_records > 0) //如果数据不为空，则组装数据
{
for ($i=0; $i<$pageSupport->current_records; $i++)
{
$title = $pageSupport->result[$i]["title"];
$id = $pageSupport->result[$i]["id"];

$news_arr[$i] = array('news' => array('id' => $id,'title' => $title));

}
}

//关闭数据库
mysql_close($db);

$pageinfo_arr = array(
'total_records' => $pageSupport->total_records,
'current_page' => $pageSupport->current_page,
'total_pages' => $pageSupport->total_pages,
'first' => $pageSupport->first,
'prev' => $pageSupport->prev,
'next' => $pageSupport->next,
'last' => $pageSupport->last
);

$smarty->assign('results', $news_arr);
$smarty->assign('pageSupport', $pageinfo_arr);
$smarty->display('news/list.tpl');

?>
模板list.tpl
{* I am a Smarty comment, I don't exist in the compiled output *}
{*
{$pageSupport.total_records}<br/>
{$pageSupport.current_page}<br/>
{$pageSupport.total_pages}<br/>
{$pageSupport.first}<br/>
{$pageSupport.prev}<br/>
{$pageSupport.next}<br/>
{$pageSupport.last}<br/>
*}
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gbk" />
<title>{$title}</title>
</head>
<body>

{foreach item=o from=$results}
{$o.news.id} {$o.news.title}
<br>
{foreachelse}
没有您要查看的数据！
{/foreach}

<br/>

{if ( $pageSupport.total_records > 0 )}

<form action="" method="get">
共{$pageSupport.total_records}记录
第{$pageSupport.current_page}页/共{$pageSupport.total_pages}页
{if ( $pageSupport.current_page > 1 )}
<A href=?current_page={$pageSupport.first}>首页</A>
<A href=?current_page={$pageSupport.prev}>上一页</A>
{/if}

{if ( $pageSupport.current_page < $pageSupport.total_pages )}
<A href=?current_page={$pageSupport.next}>下一页</A>
<A href=?current_page={$pageSupport.last}>末页</A>
{/if}

跳到<input type="text" size="4" name="current_page" value="{$pageSupport.current_page}"/>页
<input type="submit" value="GO"/>
</form>

{/if}

</body>
</html>

语法，不解释了,数据量小的时候，这么写，没事。

如果数据量大呢？不是一般大，上百万呢。

试着运行一下：

代码如下	复制代码
SELECT id FROM users LIMIT 1000000,10

在我的电脑上，第一次运行，显示如下：

10 rows in set (9.38 sec)

之后再运行，显示如下：

10 rows in set (0.38 sec)

这不奇怪。MySQL对已经运行的SQL语句有缓冲，可以很快把之前的数据拿出来。

无论如何，第一次的9秒多，我实在不能接受。

换个写法：

代码如下	复制代码
SELECT id FROM users WHERE id>1000000 LIMIT 10;

显示：10 rows in set (0.00 sec)

事实上，用phpMyAdmin去看，“显示行 0 - 9 (10 总计, 查询花费 0.0011 秒)”，之后再运行，基本都在0.0003秒左右。

百万级优化

.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

　　2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

代码如下	复制代码
select id from t where num is null

　　可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

代码如下	复制代码
select id from t where num=0

　　3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

　　4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

代码如下	复制代码
select id from t where num=10 or num=20

　　可以这样查询：

代码如下

复制代码

　　select id from t where num=10

　　union all

select id from t where num=20

　　5.in 和 not in 也要慎用，否则会导致全表扫描，如：

代码如下	复制代码
select id from t where num in(1,2,3)

　　对于连续的数值，能用 between 就不要用 in 了：

代码如下	复制代码
select id from t where num between 1 and 3

　　6.下面的查询也将导致全表扫描：

代码如下	复制代码
select id from t where name like '%abc%'

分类函数

代码如下

复制代码

$db=dblink();
$db->pagesize=20;
$sql=”select id from collect where vtype=$vtype”;
$db->execute($sql);
$strpage=$db->strpage(); //将分页字符串保存在临时变量，方便输出
while($rs=$db->fetch_array()){
   $strid.=$rs['id'].’,';
}
$strid=substr($strid,0,strlen($strid)-1); //构造出id字符串
$db->pagesize=0; //很关键，在不注销类的情况下，将分页清空，这样只需要用一次数据库连接，不需要再开；
$db->execute(“select id,title,url,sTime,gTime,vtype,tag from collect where id in ($strid)”);
<?php while($rs=$db->fetch_array()): ?>
<tr>
    <td> <?php echo $rs['id'];?></td>
    <td> <?php echo $rs['url'];?></td>
    <td> <?php echo $rs['sTime'];?></td>
    <td> <?php echo $rs['gTime'];?></td>
    <td> <?php echo $rs['vtype'];?></td>
    <td> <a href=”?act=show&id=<?php echo $rs['id'];?>” target=”_blank”><?php echo $rs['title'];?></a></td>
    <td> <?php echo $rs['tag'];?></td>
</tr>
<?php endwhile; ?>
</table>
<?php
echo $strpage;

补充：数据库,mysql教程