mysql 百萬條數據分頁優化

很多程序朋友在寫分頁是特別是mysql有了limit n,m;這樣的寫法，分頁從此簡單了，但方不知道這種分頁幾萬數據沒有問題，但在百萬千萬級時就無法使用了，今天我們來介紹這兩種分頁的優化方法。

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> ";
}

//生成導航鏈接
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>";

}

}
?>

調用:

<?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>&nbsp;<?php echo $rs['id'];?></td>
    <td>&nbsp;<?php echo $rs['url'];?></td>
    <td>&nbsp;<?php echo $rs['sTime'];?></td>
    <td>&nbsp;<?php echo $rs['gTime'];?></td>
    <td>&nbsp;<?php echo $rs['vtype'];?></td>
    <td>&nbsp;<a href=”?act=show&id=<?php echo $rs['id'];?>” target=”_blank”><?php echo $rs['title'];?></a></td>
    <td>&nbsp;<?php echo $rs['tag'];?></td>
</tr>
<?php endwhile; ?>
</table>
<?php
echo $strpage;