mysql 百万条数据分页优化

很多程序朋友在写分页是特别是mysql有了limit n,m;这样的写法，分页从此简单了，但方不知道这种分页几万数据没有问题，但在百万千万级时就无法使用了，今天我们来介绍这两种分页的优化方法。

PHP写功能时，只要用的还是MySQL，基本都是两步走

1、取得总数，算页数。SQL语句自然是

代码如下	复制代码
SELECT count(*) FROM tablename;

2、根据指定的页码号，取得相应的数据。对应的SQL语句，在网上随便查，都是一样的：

SELECT　f1,f2 FROM table LIMIT offset,length

实例分页类

代码如下

复制代码

/********************************************* 类名： 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() { $ql=$this->sql; //查询数据，数据库链接等信息应在类调用的外部实现 $result=_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 " "; echo " "; echo "第".$this->current_page."页/共".$this->total_pages."页"; echo " "; echo "跳到页"; echo ""; //生成导航链接 if ($this->current_page > 1) { echo "first.">首页|"; echo "prev.">上一页|"; } if( $this->current_page total_pages) { echo "next.">下一页|"; echo "last.">末页"; } echo " "; echo " "; } /********************************************* 函数名：full_navigate() 功能： 显示首页、下页、上页、未页  生成导航链接 如1 2 3 ... 10 11 ***********************************************/ function full_navigate() { echo " "; echo " "; echo "第".$this->current_page."页/共".$this->total_pages."页"; echo " "; echo "跳到页"; echo ""; //生成导航链接 如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;$icurrent_page;$i++){ echo "[".$i ."] "; } echo "[".$this->current_page."]"; $displayCount = $this->display_count; if($this->total_pages > $displayCount&&($this->current_page+$displayCount)total_pages){ $displayCount = $this->current_page+$displayCount; }else{ $displayCount = $this->total_pages; } for($i=$this->current_page+1;$i echo "[".$i ."] "; } //生成导航链接 if ($this->current_page > 1) { echo "first.">首页|"; echo "prev.">上一页|"; } if( $this->current_page total_pages) { echo "next.">下一页|"; echo "last.">末页"; } echo " "; echo " "; } } ?> 调用: 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新闻分页测试"); $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"," * from news "); $pageSupport->read_data();//读数据 if ($pageSupport->current_records > 0) //如果数据不为空，则组装数据 { for ($i=0; $icurrent_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} {$pageSupport.current_page} {$pageSupport.total_pages} {$pageSupport.first} {$pageSupport.prev} {$pageSupport.next} {$pageSupport.last} *} {foreach item=o from=$results} {$o.news.id} {$o.news.title}   {foreachelse} 娌℃湁鎮ㄨ