### 原因
徒弟突然有个需求，就是他发现limit分页，页数越大之后，mysql的消耗越大，查询时间越长，当突破百万级数据之后，一个简单的翻页都需要5-6秒，极其不方便。

### 测试数据库结构
```
CREATE TABLE IF NOT EXISTS `video_info` (
  `id` int(10) unsigned NOT NULL COMMENT '自增ID',
  `channel_id` varchar(30) DEFAULT NULL COMMENT '频道ID',
) ENGINE=InnoDB AUTO_INCREMENT=4565068 DEFAULT CHARSET=utf8mb4;

ALTER TABLE `video_info`
  ADD PRIMARY KEY (`id`),
  ADD KEY `channel_id` (`channel_id`);

ALTER TABLE `video_info`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',AUTO_INCREMENT=1;
```
上面数据库随机生成700W数据，进行效率测试。

### ThinkPHP5.1的分页代码：
```php
<?php
namespace app\index\controller;

use think\Controller;

class Index extends Controller
{

public function index() {
        $page = !empty($_GET['page'])  ? $_GET['page'] : 1;
        $limit = !empty($_GET['limit']) ? $_GET['limit'] : 10;
        $where = [];
        $param = '?';
        if (!empty($_GET['keys'])) {
            $where[] = ['channel_id', 'like', '%'.$_GET['keys'].'%'];
            $param .= 'keys='.$_GET['keys'];
        }

$total = \think\Db::name('video_info')->where($where)->count();
		// 取最后一条记录做翻页条件
        $sql = \think\Db::name('video_info')->where($where)->limit((($page-1)*$limit), 1)->field('id')->buildSql();

$list = \think\Db::name('video_info')->where($where)->where('id >= '.$sql.'')->limit($limit)->field('id, channel_id')->select();
        
        $this->assign('page', $page);
        $this->assign('limit', $limit);
        $this->assign('param', $param);
        $this->assign('total', $total);
        $this->assign('list', $list);
        // 渲染模板输出
        return $this->fetch();
    }

}
```

### 原生PHP的分页代码：
```php
<?php
 //程序运行时间
 $starttime = explode(' ',microtime());
# 设置html页面为UTF-8编码
header("Content-type:text/html;charset=utf-8");
# 使用MySqli连接数据库
$DB = mysqli_connect('127.0.0.1', 'localhost_db', 'localhost_db', 'localhost_db', 3306);
# 设置数据库为UTF-8编码
mysqli_query($DB, 'set names utf8');

$page = !empty($_GET['page'])  ? $_GET['page'] : 1;
$limit = !empty($_GET['limit']) ? $_GET['limit'] : 10;
$where = ' 1=1';
$param = '?';
if (!empty($_GET['keys'])) {
  $where .= ' AND channel_id like "%'.$_GET['keys'].'%"';
  $param .= 'keys='.$_GET['keys'];
}

$sql = 'SELECT COUNT(*) AS count FROM video_info where'.$where;
# 使用mysqli_query()执行SQL语句
$res = mysqli_query($DB, $sql);
# 判断是否执行成功
if ($res == false) {
    echo '查询失败'; exit;
}
$array= mysqli_fetch_array($res);
$total = $array['count'];

$sql = ' SELECT `id`,`channel_id` FROM `video_info` WHERE '.$where.' AND ( id >= ( SELECT `id` FROM `video_info` WHERE '.$where.' LIMIT '.(($page-1)*$limit).', 1 ) ) LIMIT '.$limit;
$res = mysqli_query($DB, $sql);
# 判断是否执行成功
if ($res == false) {
    echo '查询失败'; exit;
}
$list= mysqli_fetch_all($res, MYSQLI_ASSOC);
//程序运行时间
 $endtime = explode(' ',microtime());
 $thistime = $endtime[0]+$endtime[1]-($starttime[0]+$starttime[1]);
 $thistime = round($thistime,7);
$title = "本网页执行耗时：".$thistime." 秒";
?>
<!DOCTYPE html>
<html>
<head>
<title>test page</title>
<script type="text/javascript" src="/public/jquery-1.10.2.min.js"></script>
<script type="text/javascript" src="/public/kkpager.min.js"></script>
<link rel="stylesheet" type="text/css" href="/public/kkpager_orange.css" />
</head>
<body>
<div style="width:800px;margin:0 auto;">
  <form action="/1/index.php" method="GET">
	<input type="text" name="keys" placeholder="搜索渠道ID">
	<button>搜索</button> 
</form>
 <table>
	<tr>
		<td>ID</td>
		<td>渠道ID</td>
	</tr>
	<?php foreach ($list as $v){?>
	<tr>
		<td><?php echo $v['id'];?></td>
		<td><?php echo $v['channel_id'];?></td>
	</tr>
	<?php }?>
</table>
<div id="kkpager"></div>
  <div><?php echo $title;?></div>
</div>
<script type="text/javascript">
function getParameter(name) { 
	var reg = new RegExp("(^|&)"+ name +"=([^&]*)(&|$)"); 
	var r = window.location.search.substr(1).match(reg); 
	if (r!=null) return unescape(r[2]); return null;
}

//init
$(function(){
	var totalPage = <?php echo $total/$limit;?>;
	var totalRecords = <?php echo $total;?>;
	var pageNo = getParameter('page');
	if(!pageNo){
		pageNo = 1;
	}
	//生成分页
	//有些参数是可选的，比如lang，若不传有默认值
	kkpager.generPageHtml({
		pno : pageNo,
		//总页码
		total : totalPage,
		//总数据条数
		totalRecords : totalRecords,
		//链接前部
		hrefFormer : '/2/index.php',
		//链接尾部
		hrefLatter : '',
		getLink : function(n){
			return this.hrefFormer + this.hrefLatter +'<?php echo $param;?>'+"&page="+n;
		}
		/*
		,lang				: {
			firstPageText			: '首页',
			firstPageTipText		: '首页',
			lastPageText			: '尾页',
			lastPageTipText			: '尾页',
			prePageText				: '上一页',
			prePageTipText			: '上一页',
			nextPageText			: '下一页',
			nextPageTipText			: '下一页',
			totalPageBeforeText		: '共',
			totalPageAfterText		: '页',
			currPageBeforeText		: '当前第',
			currPageAfterText		: '页',
			totalInfoSplitStr		: '/',
			totalRecordsBeforeText	: '共',
			totalRecordsAfterText	: '条数据',
			gopageBeforeText		: '&nbsp;转到',
			gopageButtonOkText		: '确定',
			gopageAfterText			: '页',
			buttonTipBeforeText		: '第',
			buttonTipAfterText		: '页'
		}*/
		
		//,
		//mode : 'click',//默认值是link，可选link或者click
		//click : function(n){
		//	this.selectPage(n);
		//  return false;
		//}
	});
});
</script>
  
</body>
</html>
```

### 最终效果
在没优化之前，正常的limit翻页到4.5W页，最后一页时，需要耗时22秒左右，优化之后则只需要花费1.5秒，提高了17倍左右的查询速度。

### 原理和缺点：
原理很简单，就是使用子查询获得max(id)，然后进行当前分页。
缺点也很明显，那就是分页的关键参数，id值只能为自增主键，否则这个方案用不了。

已有0条评论

添加新评论☺

如果认为本文对您有所帮助请赞助本站

支付宝扫一扫赞助

已有0条评论

添加新评论☺