如何做闲置物品交换的网站,wordpress 社区插件,中国专门做统计网站,小程序在线制作模板PHPMySql千万级数据limit分页优化方案1年前阅读 2750评论 0喜欢 0### 原因徒弟突然有个需求#xff0c;就是他发现limit分页#xff0c;页数越大之后#xff0c;mysql的消耗越大#xff0c;查询时间越长#xff0c;当突破百万级数据之后#xff0c;一个简单的翻页都需要5…PHPMySql千万级数据limit分页优化方案1年前阅读 2750评论 0喜欢 0### 原因徒弟突然有个需求就是他发现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,) ENGINEInnoDB AUTO_INCREMENT4565068 DEFAULT CHARSETutf8mb4;ALTER TABLE video_infoADD PRIMARY KEY (id),ADD KEY channel_id (channel_id);ALTER TABLE video_infoMODIFY id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 自增ID,AUTO_INCREMENT1;上面数据库随机生成700W数据进行效率测试。### ThinkPHP5.1的分页代码phpnamespace 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//程序运行时间$starttime explode( ,microtime());# 设置html页面为UTF-8编码header(Content-type:text/html;charsetutf-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 11;$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. 秒;?test page搜索ID渠道ID?php echo $v[id];??php echo $v[channel_id];?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;?pagen;}/*,lang: {firstPageText: 首页,firstPageTipText: 首页,lastPageText: 尾页,lastPageTipText: 尾页,prePageText: 上一页,prePageTipText: 上一页,nextPageText: 下一页,nextPageTipText: 下一页,totalPageBeforeText: 共,totalPageAfterText: 页,currPageBeforeText: 当前第,currPageAfterText: 页,totalInfoSplitStr: /,totalRecordsBeforeText: 共,totalRecordsAfterText: 条数据,gopageBeforeText: 转到,gopageButtonOkText: 确定,gopageAfterText: 页,buttonTipBeforeText: 第,buttonTipAfterText: 页}*///,//mode : click,//默认值是link可选link或者click//click : function(n){//this.selectPage(n);// return false;//}});});### 最终效果在没优化之前正常的limit翻页到4.5W页最后一页时需要耗时22秒左右优化之后则只需要花费1.5秒提高了17倍左右的查询速度。### 原理和缺点原理很简单就是使用子查询获得max(id)然后进行当前分页。缺点也很明显那就是分页的关键参数id值只能为自增主键否则这个方案用不了。© 著作权归作者所有
