萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> mysql 百萬條數據分頁優化

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;

copyright © 萬盛學電腦網 all rights reserved