当前位置:首页 > Mysql教程 > 列表

mysql 百万条数据分页优化

发布:smiling 来源: PHP粉丝网  添加日期:2014-10-14 13:56:15 浏览: 评论:0 

很多程序朋友在写分页是特别是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. 构造函数:__destruct() 
  41. 输入参数:  
  42. ***********************************************/  
  43. function __destruct() 
  44.  
  45.  
  46. /*********************************************  
  47. get函数:__get() 
  48. ***********************************************/  
  49. function __get($property_name
  50. {  
  51. if(isset($this->$property_name))  
  52. {  
  53. return($this->$property_name);  
  54. }  
  55. else  
  56. {  
  57. return(NULL);  
  58. }  
  59.  
  60. /*********************************************  
  61. set函数:__set() 
  62. ***********************************************/  
  63. function __set($property_name$value)  
  64. {  
  65. $this->$property_name = $value;  
  66.  
  67. /*********************************************  
  68. 函数名:read_data 
  69. 功能: 根据SQL查询语句从表中读取相应的记录 
  70. 返回值:属性二维数组result[记录号][字段名] 
  71. ***********************************************/  
  72. function read_data() 
  73. {  
  74. $psql=$this->sql; 
  75.  
  76. //查询数据,数据库链接等信息应在类调用的外部实现 
  77. $result=mysql_query($psqlor die(mysql_error());  
  78. $this->total_records=mysql_num_rows($result); 
  79.  
  80. //利用LIMIT关键字获取本页所要显示的记录 
  81. if($this->total_records>0)  
  82. $this->start_index = ($this->current_page-1)*$this->page_size; 
  83. $psql=$psql" LIMIT ".$this->start_index." , ".$this->page_size; 
  84.  
  85. $result=mysql_query($psqlor die(mysql_error());  
  86. $this->current_records=mysql_num_rows($result); 
  87.  
  88. //将查询结果放在result数组中 
  89. $i=0;  
  90. while($row=mysql_fetch_Array($result)) 
  91. {  
  92. $this->result[$i]=$row;  
  93. $i++;  
  94. }  
  95.  
  96.  
  97. //获取总页数、当前页信息 
  98. $this->total_pages=ceil($this->total_records/$this->page_size);  
  99.  
  100. $this->first=1; 
  101. $this->prev=$this->current_page-1; 
  102. $this->next=$this->current_page+1; 
  103. $this->last=$this->total_pages; 
  104.  
  105. /*********************************************  
  106. 函数名:standard_navigate() 
  107. 功能: 显示首页、下页、上页、未页 
  108. ***********************************************/  
  109. function standard_navigate()  
  110. {  
  111. echo "<div align=center>"
  112. echo "<form action=".$_SERVER['PHP_SELF']." method="get">"
  113.  
  114. echo "<font color = red size ='4'>第".$this->current_page."页/共".$this->total_pages."页</font>";  
  115. echo " "
  116.  
  117. echo "跳到<input type="text" size=Ř" name="current_page" value='".$this->current_page."'/>页"; 
  118. echo "<input type="submit" value="提交"/>"
  119.  
  120.  
  121. //生成导航链接 
  122. if ($this->current_page > 1) { 
  123. echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->first.">首页</A>|";  
  124. echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->prev.">上一页</A>|";  
  125.  
  126. if$this->current_page < $this->total_pages) { 
  127. echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->next.">下一页</A>|"
  128. echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->last.">末页</A>";  
  129.  
  130. echo "</form>";  
  131. echo "</div>"
  132.  
  133.  
  134. /*********************************************  
  135. 函数名:full_navigate() 
  136. 功能: 显示首页、下页、上页、未页  
  137. 生成导航链接 如1 2 3 ... 10 11 
  138. ***********************************************/  
  139. function full_navigate()  
  140. {  
  141. echo "<div align=center>"
  142. echo "<form action=".$_SERVER['PHP_SELF']." method="get">"
  143.  
  144. echo "<font color = red size ='4'>第".$this->current_page."页/共".$this->total_pages."页</font>";  
  145. echo " "
  146.  
  147. echo "跳到<input type="text" size=Ř" name="current_page" value='".$this->current_page."'/>页"; 
  148. echo "<input type="submit" value="提交"/>"
  149.  
  150. //生成导航链接 如1 2 3 ... 10 11 
  151. $front_start = 1; 
  152. if($this->current_page > $this->display_count){ 
  153. $front_start = $this->current_page - $this->display_count; 
  154. for($i=$front_start;$i<$this->current_page;$i++){ 
  155. echo "<a href=".$_SERVER['PHP_SELF']."?page=".$i.">[".$i ."]</a> ";  
  156.  
  157. echo "[".$this->current_page."]"
  158.  
  159. $displayCount = $this->display_count; 
  160. if($this->total_pages > $displayCount&&($this->current_page+$displayCount)<$this->total_pages){ 
  161. $displayCount = $this->current_page+$displayCount
  162. }else
  163. $displayCount = $this->total_pages; 
  164.  
  165. for($i=$this->current_page+1;$i<=$displayCount;$i++){ 
  166. echo "<a href=".$_SERVER['PHP_SELF']."?current_page=".$i.">[".$i ."]</a> ";  
  167.  
  168. //生成导航链接 
  169. if ($this->current_page > 1) { 
  170. echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->first.">首页</A>|";  
  171. echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->prev.">上一页</A>|";  
  172.  
  173. if$this->current_page < $this->total_pages) { 
  174. echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->next.">下一页</A>|"
  175. echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->last.">末页</A>";   //phpfensi.com 
  176.  
  177. echo "</form>";  
  178. echo "</div>"
  179.  
  180.  
  181. }  
  182. ?> 

调用代码如下:

  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. $pageSupport->__set("sql","select * from news ");  
  28. $pageSupport->read_data();//读数据 
  29.  
  30. if ($pageSupport->current_records > 0) //如果数据不为空,则组装数据 
  31. for ($i=0; $i<$pageSupport->current_records; $i++) 
  32. $title = $pageSupport->result[$i]["title"]; 
  33. $id = $pageSupport->result[$i]["id"]; 
  34.  
  35. $news_arr[$i] = array('news' => array('id' => $id,'title' => $title)); 
  36.  
  37.  
  38. //关闭数据库 
  39. mysql_close($db); 
  40.  
  41. $pageinfo_arr = array
  42. 'total_records' => $pageSupport->total_records, 
  43. 'current_page' => $pageSupport->current_page, 
  44. 'total_pages' => $pageSupport->total_pages, 
  45. 'first' => $pageSupport->first, 
  46. 'prev' => $pageSupport->prev, 
  47. 'next' => $pageSupport->next, 
  48. 'last' => $pageSupport->last 
  49. ); 
  50.  
  51. $smarty->assign('results'$news_arr); 
  52. $smarty->assign('pageSupport'$pageinfo_arr); 
  53. $smarty->display('news/list.tpl'); 
  54.  
  55. ?>  

模板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. $strid=substr($strid,0,strlen($strid)-1); //构造出id字符串 
  9. $db->pagesize=0; //很关键,在不注销类的情况下,将分页清空,这样只需要用一次数据库连接,不需要再开; 
  10. $db->execute(“select id,title,url,sTime,gTime,vtype,tag from collect where id in ($strid)”); 
  11. <?php while($rs=$db->fetch_array()): ?> 
  12. <tr> 
  13.     <td>&nbsp;<?php echo $rs['id'];?></td> 
  14.     <td>&nbsp;<?php echo $rs['url'];?></td> 
  15.     <td>&nbsp;<?php echo $rs['sTime'];?></td> 
  16.     <td>&nbsp;<?php echo $rs['gTime'];?></td> 
  17.     <td>&nbsp;<?php echo $rs['vtype'];?></td> 
  18.     <td>&nbsp;<a href=”?act=show&id=<?php echo $rs['id'];?>” target=”_blank”><?php echo $rs['title'];?></a></td> 
  19.     <td>&nbsp;<?php echo $rs['tag'];?></td> 
  20. </tr> 
  21. <?php endwhile; ?> 
  22. </table> 
  23. <?php 
  24. echo $strpage
  25. ?>

Tags: mysql百万条数据 mysql分页优化

分享到:

相关文章