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

PHP寫分頁功能時,只要用的還是MySQL,基本都是兩步走.

1、取得總數,算頁數,SQL語句自然是如下代碼:

SELECT count(*) FROM tablename; 

2、根據指定的頁碼號,取得相應的數據,對應的SQL語句,在網上隨便查,都是一樣的:

SELECT　f1,f2 FROM table LIMIT offset,length

實例分頁類,代碼如下:

1. <?php 
2. /*********************************************  
3. 類名:PageSupport 
4. 功能:分頁顯示MySQL數據庫中的數據  
5. ***********************************************/  
6. class PageSupport{  
7. //屬性 
8. var $sql; //所要顯示數據的SQL查詢語句  
9. var $page_size; //每頁顯示最多行數 
10.  
11. var $start_index; //所要顯示記錄的首行序號 
12. var $total_records; //記錄總數  
13. var $current_records; //本頁讀取的記錄數  
14. var $result; //讀出的結果 
15.  
16. var $total_pages; //總頁數  
17. var $current_page; //當前頁數 
18. var $display_count = 30; //顯示的前幾頁和后幾頁數 
19.  
20. var $arr_page_query; //數組，包含分頁顯示需要傳遞的參數 
21.  
22. var $first; 
23. var $prev; 
24. var $next; 
25. var $last; 
26.  
27. //方法 
28. /*********************************************  
29. 構造函數：__construct() 
30. 輸入參數：  
31. $ppage_size：每頁顯示最多行數  
32. ***********************************************/  
33. function PageSupport($ppage_size) 
34. {  
35. $this->page_size=$ppage_size;  
36. $this->start_index=0; 
37. } 
38.  
39.  
40. /*********************************************  
41. 構造函數：__destruct() 
42. 輸入參數：  
43. ***********************************************/  
44. function __destruct() 
45. { 
46.  
47. } 
48.  
49. /*********************************************  
50. get函數：__get() 
51. ***********************************************/  
52. function __get($property_name) 
53. {  
54. if(isset($this->$property_name))  
55. {  
56. return($this->$property_name);  
57. }  
58. else  
59. {  
60. return(NULL);  
61. }  
62. } 
63.  
64. /*********************************************  
65. set函數：__set() 
66. ***********************************************/  
67. function __set($property_name, $value)  
68. {  
69. $this->$property_name = $value;  
70. } 
71.  
72. /*********************************************  
73. 函數名：read_data 
74. 功能： 根據SQL查詢語句從表中讀取相應的記錄 
75. 返回值：屬性二維數組result[記錄號][字段名] 
76. ***********************************************/  
77. function read_data() 
78. {  
79. $psql=$this->sql; 
80.  
81. //查詢數據，數據庫鏈接等信息應在類調用的外部實現 
82. $result=mysql_query($psql) or die(mysql_error());  
83. $this->total_records=mysql_num_rows($result); 
84.  
85. //利用LIMIT關鍵字獲取本頁所要顯示的記錄 
86. if($this->total_records>0)  
87. { 
88. $this->start_index = ($this->current_page-1)*$this->page_size; 
89. $psql=$psql. " LIMIT ".$this->start_index." , ".$this->page_size; 
90.  
91. $result=mysql_query($psql) or die(mysql_error());  
92. $this->current_records=mysql_num_rows($result); 
93.  
94. //將查詢結果放在result數組中 
95. $i=0;  
96. while($row=mysql_fetch_Array($result)) 
97. {  
98. $this->result[$i]=$row;  
99. $i++;  
100. }  
101. } 
102.  
103.  
104. //獲取總頁數、當前頁信息 
105. $this->total_pages=ceil($this->total_records/$this->page_size);  
106.  
107. $this->first=1; 
108. $this->prev=$this->current_page-1; 
109. $this->next=$this->current_page+1; 
110. $this->last=$this->total_pages; 
111. } 
112.  
113. /*********************************************  
114. 函數名：standard_navigate() 
115. 功能： 顯示首頁、下頁、上頁、未頁 
116. ***********************************************/  
117. function standard_navigate()  
118. {  
119. echo "<div align=center>"; 
120. echo "<form action=".$_SERVER['PHP_SELF']." method="get">"; 
121.  
122. echo "<font color = red size ='4'>第".$this->current_page."頁/共".$this->total_pages."頁</font>";  
123. echo " "; 
124.  
125. echo "跳到<input type="text" size=?" name="current_page" value='".$this->current_page."'/>頁"; 
126. echo "<input type="submit" value="提交"/>"; 
127.  
128.  
129. //生成導航鏈接 
130. if ($this->current_page > 1) { 
131. echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->first.">首頁</A>|";  
132. echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->prev.">上一頁</A>|";  
133. } 
134.  
135. if( $this->current_page < $this->total_pages) { 
136. echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->next.">下一頁</A>|"; 
137. echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->last.">末頁</A>";  
138. } 
139.  
140. echo "</form>";  
141. echo "</div>"; 
142.  
143. } 
144.  
145. /*********************************************  
146. 函數名：full_navigate() 
147. 功能： 顯示首頁、下頁、上頁、未頁  
148. 生成導航鏈接 如1 2 3 ... 10 11 
149. ***********************************************/  
150. function full_navigate()  
151. {  
152. echo "<div align=center>"; 
153. echo "<form action=".$_SERVER['PHP_SELF']." method="get">"; 
154.  
155. echo "<font color = red size ='4'>第".$this->current_page."頁/共".$this->total_pages."頁</font>";  
156. echo " "; 
157.  
158. echo "跳到<input type="text" size=?" name="current_page" value='".$this->current_page."'/>頁"; 
159. echo "<input type="submit" value="提交"/>"; 
160.  
161. //生成導航鏈接 如1 2 3 ... 10 11 
162. $front_start = 1; 
163. if($this->current_page > $this->display_count){ 
164. $front_start = $this->current_page - $this->display_count; 
165. } 
166. for($i=$front_start;$i<$this->current_page;$i++){ 
167. echo "<a href=".$_SERVER['PHP_SELF']."?page=".$i.">[".$i ."]</a> ";  
168. } 
169.  
170. echo "[".$this->current_page."]"; 
171.  
172. $displayCount = $this->display_count; 
173. if($this->total_pages > $displayCount&&($this->current_page+$displayCount)<$this->total_pages){ 
174. $displayCount = $this->current_page+$displayCount; 
175. }else{ 
176. $displayCount = $this->total_pages; 
177. } 
178.  
179. for($i=$this->current_page+1;$i<=$displayCount;$i++){ 
180. echo "<a href=".$_SERVER['PHP_SELF']."?current_page=".$i.">[".$i ."]</a> ";  
181. } 
182.  
183. //生成導航鏈接 
184. if ($this->current_page > 1) { 
185. echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->first.">首頁</A>|";  
186. echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->prev.">上一頁</A>|";  
187. } 
188.  
189. if( $this->current_page < $this->total_pages) { 
190. echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->next.">下一頁</A>|"; 
191. echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->last.">末頁</A>";   //Vevb.com 
192. } 
193.  
194. echo "</form>";  
195. echo "</div>"; 
196.  
197. } 
198.  
199. }  
200. ?> 

調用代碼如下:

1. <?php 
2.  
3. include_once("../config_jj/sys_conf.inc");  
4. include_once("../PageSupportClass.php");//分頁類 
5. include_once('../Smarty_JsnhClass.php'); 
6.  
7. $smarty = new Smarty_Jsnh(); 
8. include_once("../include/Smarty_changed_dir.php");  
9. $smarty->assign('title', "Smarty新聞分頁測試"); 
10.  
11. <?php 
12.  
13. $pageSupport = new PageSupport($PAGE_SIZE); //實例化PageSupport對象 
14.  
15. $current_page=$_GET["current_page"];//分頁當前頁數 
16.  
17. if (isset($current_page)) { 
18.  
19. $pageSupport->__set("current_page",$current_page); 
20.  
21. } else { 
22.  
23. $pageSupport->__set("current_page",1); 
24.  
25. } 
26.  
27. ?>  
28. $pageSupport->__set("sql","select * from news ");  
29. $pageSupport->read_data();//讀數據 
30.  
31. if ($pageSupport->current_records > 0) //如果數據不為空，則組裝數據 
32. { 
33. for ($i=0; $i<$pageSupport->current_records; $i++) 
34. { 
35. $title = $pageSupport->result[$i]["title"]; 
36. $id = $pageSupport->result[$i]["id"]; 
37.  
38. $news_arr[$i] = array('news' => array('id' => $id,'title' => $title)); 
39.  
40. } 
41. } 
42.  
43. //關閉數據庫 
44. mysql_close($db); 
45.  
46. $pageinfo_arr = array( 
47. 'total_records' => $pageSupport->total_records, 
48. 'current_page' => $pageSupport->current_page, 
49. 'total_pages' => $pageSupport->total_pages, 
50. 'first' => $pageSupport->first, 
51. 'prev' => $pageSupport->prev, 
52. 'next' => $pageSupport->next, 
53. 'last' => $pageSupport->last 
54. ); 
55.  
56. $smarty->assign('results', $news_arr); 
57. $smarty->assign('pageSupport', $pageinfo_arr); 
58. $smarty->display('news/list.tpl'); 
59.  
60. ?>  

模板list.tpl,代碼如下:

1. {* I am a Smarty comment, I don't exist in the compiled output *} 
2. {* 
3. {$pageSupport.total_records}<br/> 
4. {$pageSupport.current_page}<br/> 
5. {$pageSupport.total_pages}<br/> 
6. {$pageSupport.first}<br/> 
7. {$pageSupport.prev}<br/> 
8. {$pageSupport.next}<br/> 
9. {$pageSupport.last}<br/> 
10. *} 
11. <html> 
12. <head> 
13. <meta http-equiv="Content-Type" content="text/html; charset=gbk" /> 
14. <title>{$title}</title> 
15. </head> 
16. <body> 
17.  
18. {foreach item=o from=$results}  
19. {$o.news.id} {$o.news.title}  
20. <br>  
21. {foreachelse} 
22. 沒有您要查看的數據！ 
23. {/foreach}  
24.  
25. <br/> 
26.  
27.  
28. {if ( $pageSupport.total_records > 0 )} 
29.  
30. <form action="" method="get"> 
31. 共{$pageSupport.total_records}記錄 
32. 第{$pageSupport.current_page}頁/共{$pageSupport.total_pages}頁 
33. {if ( $pageSupport.current_page > 1 )} 
34. <A href=?current_page={$pageSupport.first}>首頁</A> 
35. <A href=?current_page={$pageSupport.prev}>上一頁</A> 
36. {/if} 
37.  
38. {if ( $pageSupport.current_page < $pageSupport.total_pages )} 
39. <A href=?current_page={$pageSupport.next}>下一頁</A> 
40. <A href=?current_page={$pageSupport.last}>末頁</A> 
41. {/if} 
42.  
43. 跳到<input type="text" size="4" name="current_page" value="{$pageSupport.current_page}"/>頁 
44. <input type="submit" value="GO"/> 
45. </form> 
46.  
47. {/if} 
48. </body> 
49. </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

可以這樣查詢,代碼如下:

1. select id from t where num=10 
2.  
3. union all 
4.  
5.      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%' 

分類函數,代碼如下:

1. $db=dblink(); 
2. $db->pagesize=20; 
3. $sql=”select id from collect where vtype=$vtype”; 
4. $db->execute($sql); 
5. $strpage=$db->strpage(); //將分頁字符串保存在臨時變量，方便輸出 
6. while($rs=$db->fetch_array()){ 
7.    $strid.=$rs['id'].’,'; 
8. } 
9. $strid=substr($strid,0,strlen($strid)-1); //構造出id字符串 
10. $db->pagesize=0; //很關鍵，在不注銷類的情況下，將分頁清空，這樣只需要用一次數據庫連接，不需要再開； 
11. $db->execute(“select id,title,url,sTime,gTime,vtype,tag from collect where id in ($strid)”); 
12. <?php while($rs=$db->fetch_array()): ?> 
13. <tr> 
14.     <td>&nbsp;<?php echo $rs['id'];?></td> 
15.     <td>&nbsp;<?php echo $rs['url'];?></td> 
16.     <td>&nbsp;<?php echo $rs['sTime'];?></td> 
17.     <td>&nbsp;<?php echo $rs['gTime'];?></td> 
18.     <td>&nbsp;<?php echo $rs['vtype'];?></td> 
19.     <td>&nbsp;<a href=”?act=show&id=<?php echo $rs['id'];?>” target=”_blank”><?php echo $rs['title'];?></a></td> 
20.     <td>&nbsp;<?php echo $rs['tag'];?></td> 
21. </tr> 
22. <?php endwhile; ?> 
23. </table> 
24. <?php 
25. echo $strpage; 
26. ?>