MYSQL的大数据量情况下的分页查询优化
最近做的项目需要实现一个分页查询功能,自己先看了别人写的方法:
<!-- 查询 -->
<select id="queryMonitorFolder" parameterType="monitorFolderQuery" resultMap="monitorFolderMap">
select
id, name, type, var_num, erp, createTime, modifyTime
from
monitor_folder
where
yn = 1
<if test="name != null">
and name like concat('%',#{name,jdbcType=VARCHAR},'%')
</if>
and <![CDATA[id <=]]>
( select
id
from
monitor_folder
where
yn=1
<if test="name != null">
and name like concat('%',#{name,jdbcType=VARCHAR},'%')
</if>
order by id desc limit #{startRowNum},1)
order by id desc
limit #{pageSize}
</select>
当时我个人的思路是:在分页中使用了两次查询,效率比较低。直接使用limit就好了。
查过资料之后,不管是自己的想法还是这个代码都是有不足的。
limit的标准写法:
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
比如要查第十个开始的十个,就是limit 10, 10
许多网站的分页功能的底层实现就是依赖前端给后端传来一些参数,比如起始页和页面大小,来结合limit控制分页。但是这个地方首先有一个问题:效率。
如果这么写:
SELECT * FROM student LIMIT 10000 , 10;
实际底层执行是从第一行开始找到10010行,再抛弃前面的一万行。所以当用户往后翻到很多页的时候,offset这个值可能就比较大,实际执行效率就会很慢。
一般有几种做法来弥补。
1,用方法计算出开始和结尾条件,用where语句对查询进行限制。
SELECT * FROM student WHERE ID >= 9990 AND ID <= 9999;
比如前端传来:pageIndex = 1000, pageSize = 10。 也就是第一千页,每页十条。得到这两个参数之后,直接用方法计算得到需要查询的数据条件:WHERE id >= 9999 AND id <= 9999。
这样做的显著好处就是速度快并且也很好理解。而显著坏处就是使用限制。什么意思呢,就是说你计算出的这个id的范围不一定能全部命中。一般数据库的删除是修改某个字段表示删除,这样的话你计算的id范围可能就有的记录被删除了,同时,前端可能还传来别的限制条件,比如发帖日期在一个月之内,谁发的帖等等。所以问题的核心就是不能保证计算的范围都能匹配上。如果这里做的比较粗糙(比如后端这么做,前端也直接拿这些数据显示),实际的效果就是用户点开一页,明明显示说10条记录,结果只有5条,而且每一页的情况还不一致。
2,where语句限制一半,limit控制行数
SELECT * FROM student WHERE ID >= 9990 LIMIT 10;
这样的好处就是不仅效率高了,而且能刚好拿十条。但是这里依然还是有一个问题:不适用于所有情况。具体不适用与什么情况呢?简单来说就是id和行数不能对应的情况。比如ID本身是无规律离散的,那么计算这个起始ID就不能简单的pageIndex * pageSize了。
最新文章
- centos中安装mysql数据库
- 颠覆式前端UI开发框架:React
- 典型Event ID
- 基于jQuery动态创建html元素
- .net 类型源码下载地址
- 几乎每个文件里面都有 #ifdef __cplusplus extern ";C"; { #endif 可我没找到程序里那个地方定义了__cplusplus 啊?这又是怎么回事呢?
- [转]iOS之浅谈纯代码控制UIViewController视图控制器跳转界面的几种方法
- [简历] PHP 技能关键字列表
- BuguMongo是一个MongoDB Java开发框架,集成了DAO、Query、Lucene、GridFS等功能
- windows消息常量值
- JavaEE:Tomcat服务器常用配置和HTTP简介
- SQL语法语句总结
- InnoSetup 脚本打包及管理员权限设置
- 2018-2019-2 20165220 《网络对抗技术》Exp1 PC平台逆向破解
- Android Tools&;Cmd
- 前端 ----jQuery的动画效果
- es6阮一峰读后感
- Paper/ Overview | CNN(未完待续)
- PCH Warning: header stop cannot be in a macro or #if block.
- How to access business objects with their related ObjectSpaces (Multi-Database Environment)