mysql分页sql语句优化,mysql 百万条数据分页优化

/*********************************************

类名： 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 < $this->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<=$displayCount;$i++){

echo "[".$i ."] ";

}

//生成导航链接

if ($this->current_page > 1) {

echo "first.">首页|";

echo "prev.">上一页|";

}

if( $this->current_page < $this->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}

*}

{$title}

{foreach item=o from=$results}

{$o.news.id} {$o.news.title}

{foreachelse}

没有您要查看的数据！

{/foreach}

{if ( $pageSupport.total_records > 0 )}

共{$pageSupport.total_records}记录

第{$pageSupport.current_page}页/共{$pageSupport.total_pages}页

{if ( $pageSupport.current_page > 1 )}

首页

上一页

{/if}

{if ( $pageSupport.current_page < $pageSupport.total_pages )}

下一页

末页

{/if}

跳到页

{/if}