mysql 百万条数据分页优化

小浪SEO博客 专注SEO优化思维、SEO技术的实战分享类博客~

很多程序朋友在写分页是特别是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. classPageSupport{
  7. //属性
  8. var$sql;//所要显示数据的SQL查询语句
  9. var$page_size;//每页显示最多行数
  10. var$start_index;//所要显示记录的首行序号
  11. var$total_records;//记录总数
  12. var$current_records;//本页读取的记录数
  13. var$result;//读出的结果
  14. var$total_pages;//总页数
  15. var$current_page;//当前页数
  16. var$display_count=30;//显示的前几页和后几页数
  17. var$arr_page_query;//数组,包含分页显示需要传递的参数
  18. var$first;
  19. var$prev;
  20. var$next;
  21. var$last;
  22. //方法
  23. /*********************************************
  24. 构造函数:__construct()
  25. 输入参数:
  26. $ppage_size:每页显示最多行数
  27. ***********************************************/
  28. functionPageSupport($ppage_size)
  29. {
  30. $this->page_size=$ppage_size;
  31. $this->start_index=0;
  32. }
  33. /*********************************************
  34. 构造函数:__destruct()
  35. 输入参数:
  36. ***********************************************/
  37. function__destruct()
  38. {
  39. }
  40. /*********************************************
  41. get函数:__get()
  42. ***********************************************/
  43. function__get($property_name)
  44. {
  45. if(isset($this->$property_name))
  46. {
  47. return($this->$property_name);
  48. }
  49. else
  50. {
  51. return(NULL);
  52. }
  53. }
  54. /*********************************************
  55. set函数:__set()
  56. ***********************************************/
  57. function__set($property_name,$value)
  58. {
  59. $this->$property_name=$value;
  60. }
  61. /*********************************************
  62. 函数名:read_data
  63. 功能:根据SQL查询语句从表中读取相应的记录
  64. 返回值:属性二维数组result[记录号][字段名]
  65. ***********************************************/
  66. functionread_data()
  67. {
  68. $psql=$this->sql;
  69. //查询数据,数据库链接等信息应在类调用的外部实现
  70. $result=mysql_query($psql)ordie(mysql_error());
  71. $this->total_records=mysql_num_rows($result);
  72. //利用LIMIT关键字获取本页所要显示的记录
  73. if($this->total_records>0)
  74. {
  75. $this->start_index=($this->current_page-1)*$this->page_size;
  76. $psql=$psql."LIMIT".$this->start_index.",".$this->page_size;
  77. $result=mysql_query($psql)ordie(mysql_error());
  78. $this->current_records=mysql_num_rows($result);
  79. //将查询结果放在result数组中
  80. $i=0;
  81. while($row=mysql_fetch_Array($result))
  82. {
  83. $this->result[$i]=$row;
  84. $i++;
  85. }
  86. }
  87. //获取总页数、当前页信息
  88. $this->total_pages=ceil($this->total_records/$this->page_size);
  89. $this->first=1;
  90. $this->prev=$this->current_page-1;
  91. $this->next=$this->current_page+1;
  92. $this->last=$this->total_pages;
  93. }
  94. /*********************************************
  95. 函数名:standard_navigate()
  96. 功能:显示首页、下页、上页、未页
  97. ***********************************************/
  98. functionstandard_navigate()
  99. {
  100. echo"<divalign=center>";
  101. echo"<formaction=".$_SERVER['PHP_SELF']."method="get">";
  102. echo"<fontcolor=redsize='4'>第".$this->current_page."页/共".$this->total_pages."页</font>";
  103. echo"";
  104. echo"跳到<inputtype="text"size=?"name="current_page"value='".$this->current_page."'/>页";
  105. echo"<inputtype="submit"value="提交"/>";
  106. //生成导航链接
  107. if($this->current_page>1){
  108. echo"<Ahref=".$_SERVER['PHP_SELF']."?current_page=".$this->first.">首页</A>|";
  109. echo"<Ahref=".$_SERVER['PHP_SELF']."?current_page=".$this->prev.">上一页</A>|";
  110. }
  111. if($this->current_page<$this->total_pages){
  112. echo"<Ahref=".$_SERVER['PHP_SELF']."?current_page=".$this->next.">下一页</A>|";
  113. echo"<Ahref=".$_SERVER['PHP_SELF']."?current_page=".$this->last.">末页</A>";
  114. }
  115. echo"</form>";
  116. echo"</div>";
  117. }
  118. /*********************************************
  119. 函数名:full_navigate()
  120. 功能:显示首页、下页、上页、未页
  121. 生成导航链接如123...1011
  122. ***********************************************/
  123. functionfull_navigate()
  124. {
  125. echo"<divalign=center>";
  126. echo"<formaction=".$_SERVER['PHP_SELF']."method="get">";
  127. echo"<fontcolor=redsize='4'>第".$this->current_page."页/共".$this->total_pages."页</font>";
  128. echo"";
  129. echo"跳到<inputtype="text"size=?"name="current_page"value='".$this->current_page."'/>页";
  130. echo"<inputtype="submit"value="提交"/>";
  131. //生成导航链接如123...1011
  132. $front_start=1;
  133. if($this->current_page>$this->display_count){
  134. $front_start=$this->current_page-$this->display_count;
  135. }
  136. for($i=$front_start;$i<$this->current_page;$i++){
  137. echo"<ahref=".$_SERVER['PHP_SELF']."?page=".$i.">[".$i."]</a>";
  138. }
  139. echo"[".$this->current_page."]";
  140. $displayCount=$this->display_count;
  141. if($this->total_pages>$displayCount&&($this->current_page+$displayCount)<$this->total_pages){
  142. $displayCount=$this->current_page+$displayCount;
  143. }else{
  144. $displayCount=$this->total_pages;
  145. }
  146. for($i=$this->current_page+1;$i<=$displayCount;$i++){
  147. echo"<ahref=".$_SERVER['PHP_SELF']."?current_page=".$i.">[".$i."]</a>";
  148. }
  149. //生成导航链接
  150. if($this->current_page>1){
  151. echo"<Ahref=".$_SERVER['PHP_SELF']."?current_page=".$this->first.">首页</A>|";
  152. echo"<Ahref=".$_SERVER['PHP_SELF']."?current_page=".$this->prev.">上一页</A>|";
  153. }
  154. if($this->current_page<$this->total_pages){
  155. echo"<Ahref=".$_SERVER['PHP_SELF']."?current_page=".$this->next.">下一页</A>|";
  156. echo"<Ahref=".$_SERVER['PHP_SELF']."?current_page=".$this->last.">末页</A>";//phpfensi.com
  157. }
  158. echo"</form>";
  159. echo"</div>";
  160. }
  161. }
  162. ?>

调用代码如下:

  1. <?php
  2. include_once("../config_jj/sys_conf.inc");
  3. include_once("../PageSupportClass.php");//分页类
  4. include_once('../Smarty_JsnhClass.php');
  5. $smarty=newSmarty_Jsnh();
  6. include_once("../include/Smarty_changed_dir.php");
  7. $smarty->assign('title',"Smarty新闻分页测试");
  8. <?php
  9. $pageSupport=newPageSupport($PAGE_SIZE);//实例化PageSupport对象
  10. $current_page=$_GET["current_page"];//分页当前页数
  11. if(isset($current_page)){
  12. $pageSupport->__set("current_page",$current_page);
  13. }else{
  14. $pageSupport->__set("current_page",1);
  15. }
  16. ?>
  17. $pageSupport->__set("sql","select*fromnews");
  18. $pageSupport->read_data();//读数据
  19. if($pageSupport->current_records>0)//如果数据不为空,则组装数据
  20. {
  21. for($i=0;$i<$pageSupport->current_records;$i++)
  22. {
  23. $title=$pageSupport->result[$i]["title"];
  24. $id=$pageSupport->result[$i]["id"];
  25. $news_arr[$i]=array('news'=>array('id'=>$id,'title'=>$title));
  26. }
  27. }
  28. //关闭数据库
  29. mysql_close($db);
  30. $pageinfo_arr=array(
  31. 'total_records'=>$pageSupport->total_records,
  32. 'current_page'=>$pageSupport->current_page,
  33. 'total_pages'=>$pageSupport->total_pages,
  34. 'first'=>$pageSupport->first,
  35. 'prev'=>$pageSupport->prev,
  36. 'next'=>$pageSupport->next,
  37. 'last'=>$pageSupport->last
  38. );
  39. $smarty->assign('results',$news_arr);
  40. $smarty->assign('pageSupport',$pageinfo_arr);
  41. $smarty->display('news/list.tpl');
  42. ?>

模板list.tpl,代码如下:

  1. {*IamaSmartycomment,Idon'texistinthecompiledoutput*}
  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. <metahttp-equiv="Content-Type"content="text/html;charset=gbk"/>
  14. <title>{$title}</title>
  15. </head>
  16. <body>
  17. {foreachitem=ofrom=$results}
  18. {$o.news.id}{$o.news.title}
  19. <br>
  20. {foreachelse}
  21. 没有您要查看的数据!
  22. {/foreach}
  23. <br/>
  24. {if($pageSupport.total_records>0)}
  25. <formaction=""method="get">
  26. 共{$pageSupport.total_records}记录
  27. 第{$pageSupport.current_page}页/共{$pageSupport.total_pages}页
  28. {if($pageSupport.current_page>1)}
  29. <Ahref=?current_page={$pageSupport.first}>首页</A>
  30. <Ahref=?current_page={$pageSupport.prev}>上一页</A>
  31. {/if}
  32. {if($pageSupport.current_page<$pageSupport.total_pages)}
  33. <Ahref=?current_page={$pageSupport.next}>下一页</A>
  34. <Ahref=?current_page={$pageSupport.last}>末页</A>
  35. {/if}
  36. 跳到<inputtype="text"size="4"name="current_page"value="{$pageSupport.current_page}"/>
  37. <inputtype="submit"value="GO"/>
  38. </form>
  39. {/if}
  40. </body>
  41. </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. selectidfromtwherenum=10
  2. unionall
  3. selectidfromtwherenum=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=”selectidfromcollectwherevtype=$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(“selectid,title,url,sTime,gTime,vtype,tagfromcollectwhereidin($strid)”);
  12. <?phpwhile($rs=$db->fetch_array()):?>
  13. <tr>
  14. <td>&nbsp;<?phpecho$rs['id'];?></td>
  15. <td>&nbsp;<?phpecho$rs['url'];?></td>
  16. <td>&nbsp;<?phpecho$rs['sTime'];?></td>
  17. <td>&nbsp;<?phpecho$rs['gTime'];?></td>
  18. <td>&nbsp;<?phpecho$rs['vtype'];?></td>
  19. <td>&nbsp;<ahref=”?act=show&id=<?phpecho$rs['id'];?>”target=”_blank”><?phpecho$rs['title'];?></a></td>
  20. <td>&nbsp;<?phpecho$rs['tag'];?></td>
  21. </tr>
  22. <?phpendwhile;?>
  23. </table>
  24. <?php
  25. echo$strpage;
  26. ?>

相关广告
  • mysql 百万条数据分页优化 mysql 百万条数据分页优化 mysql 百万条数据分页优化
相关阅读

mysql 百万条数据分页优化

2019/10/10 17:33:16 | 谷歌SEO算法 | 发外链