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
实例分页类,代码如下:
- <?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>"; //phpfensi.com
- }
- 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> <?php echo $rs['id'];?></td>
- <td> <?php echo $rs['url'];?></td>
- <td> <?php echo $rs['sTime'];?></td>
- <td> <?php echo $rs['gTime'];?></td>
- <td> <?php echo $rs['vtype'];?></td>
- <td> <a href=”?act=show&id=<?php echo $rs['id'];?>” target=”_blank”><?php echo $rs['title'];?></a></td>
- <td> <?php echo $rs['tag'];?></td>
- </tr>
- <?php endwhile; ?>
- </table>
- <?php
- echo $strpage;
- ?>
Tags: mysql百万条数据 mysql分页优化
相关文章
- ·mysql常用分页优化例子(2014-09-23)
- ·mysql中SQL分页优化的例子(2014-09-24)
推荐文章
热门文章
最新评论文章
- 写给考虑创业的年轻程序员(10)
- PHP新手上路(一)(7)
- 惹恼程序员的十件事(5)
- PHP邮件发送例子,已测试成功(5)
- 致初学者:PHP比ASP优秀的七个理由(4)
- PHP会被淘汰吗?(4)
- PHP新手上路(四)(4)
- 如何去学习PHP?(2)
- 简单入门级php分页代码(2)
- php中邮箱email 电话等格式的验证(2)