mysql 百万条数据分页优化

很多程序朋友在写分页是特别是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. ?>