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> "; } //產生導航連結 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;