mysql 分页存储过程
网上关于mysql分页存储过程的资料很多,但内容大同小异。作为初学者,引用mysql存储过程如下:
<p> </p><p>DELIMITER $$</p><p>USE `database1`$$ -- 数据库名称</p><p>DROP PROCEDURE IF EXISTS `Query_Pagination`$$ -- 分页存储过程名称,存在则删除</p><p>CREATE <a target=_blank href="mailto:DEFINER=`root`@`%">DEFINER=`root`@`%</a>` PROCEDURE `Query_Pagination`( -- 创建新的分页存储过程 IN _fields VARCHAR (2000), -- 显示的字段 IN _tables TEXT, -- 表名 IN _where VARCHAR (2000), -- where条件,可为空 IN _orderby VARCHAR (200), -- 排序条件,可为空 IN _pageindex INT, -- 开始页 IN _pagesize INT, -- 每页大小 OUT _totalcount INT, -- 总共行数 OUT _pagecount INT -- 总共页数 ) BEGIN SET @startrow = _pagesize * (_pageindex - 1) ; SET @pagesize = _pagesize ; SET @rowindex = 0 ; SET @strsql = CONCAT( ' select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,', -- 显示每条的行号 _fields, ' from ', _tables, CASE IFNULL(_where, '') WHEN '' THEN '' ELSE CONCAT(' where ', _where) END, CASE IFNULL(_orderby, '') WHEN '' THEN '' ELSE CONCAT(' order by ', _orderby) END, ' limit ', @startRow, ',', @pageSize ) ; PREPARE strsql FROM @strsql ; EXECUTE strsql ; SET _totalcount = FOUND_ROWS() ; IF (_totalcount <= _pagesize) THEN SET _pagecount = 1 ; ELSE IF (_totalcount % _pagesize > 0) THEN SET _pagecount = _totalcount / _pageSize + 1 ; ELSE SET _pagecount = _totalcount / _pageSize ; END IF ; END IF ; END$$</p><p>DELIMITER ;</p><p> </p>
上述分页存储过程的确很好用,但是当数据量达到百万级时,发现速度就会下降,用explain执行以下语句:
EXPLAIN SELECT SQL_CALC_FOUND_ROWS @rowindex:=@rowindex+1 AS rownumber FROM view_visitregisterinfo WHERE CardType=‘1‘ ORDER BY tableid DESC LIMIT 0,1000;
执行结果如下:
可以看到,虽然用了limit 但是依然检索了整个表,导致查询缓慢。执行时间为16秒。
去掉SQL_CALC_FOUND_ROWS @rowindex:=@rowindex+1 AS rownumber ,查询语句改为EXPLAIN SELECT * FROM view_visitregisterinfo WHERE 1=1 AND CardType=‘1‘ ORDER BY tableid DESC LIMIT 0,1000; 执行结果如下:
可以看到,执行的行数为1000,执行时间为0.038s,时间加快了很多。
所以,修改存储过程为:
DELIMITER $$ USE `speednew`$$ -- 数据库名称 DROP PROCEDURE IF EXISTS `Query_Pagination`$$ -- 分页存储过程名称,存在则删除 CREATE DEFINER=`root`@`%` PROCEDURE `Query_Pagination`( -- 创建新的分页存储过程 IN _fields VARCHAR (2000), -- 显示的字段 IN _tables TEXT, -- 表名 IN _where VARCHAR (2000), -- where条件,可为空 IN _orderby VARCHAR (200), -- 排序条件,可为空 IN _pageindex INT, -- 开始页 IN _pagesize INT, -- 每页大小 OUT _totalcount INT, -- 总共行数 OUT _pagecount INT -- 总共页数 ) BEGIN SET @startrow = _pagesize * (_pageindex - 1) ; SET @pagesize = _pagesize ; SET @rowindex = 0 ; SET @strsql = CONCAT( ' select ', _fields, ' from ', _tables, CASE IFNULL(_where, '') WHEN '' THEN '' ELSE CONCAT(' where ', _where) END, CASE IFNULL(_orderby, '') WHEN '' THEN '' ELSE CONCAT(' order by ', _orderby) END, ' limit ', @startRow, ',', @pageSize ) ; PREPARE strsql FROM @strsql ; EXECUTE strsql ; SET _totalcount = FOUND_ROWS() ; IF (_totalcount <= _pagesize) THEN SET _pagecount = 1 ; ELSE IF (_totalcount % _pagesize > 0) THEN SET _pagecount = _totalcount / _pageSize + 1 ; ELSE SET _pagecount = _totalcount / _pageSize ; END IF ; END IF ; END$$ DELIMITER ;
总结经验:引用现有的东西时,一定要根据个人的情况进行二次修改,改为适合自己的,同时要明白别人原先那样写的原因。
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。