MYSQL的大数据量情况下的分页查询优化

时间:2022-02-18 11:31:00

最近做的项目需要实现一个分页查询功能,自己先看了别人写的方法:

 1     <!-- 查询 -->
2 <select id="queryMonitorFolder" parameterType="monitorFolderQuery" resultMap="monitorFolderMap">
3 select
4 id, name, type, var_num, erp, createTime, modifyTime
5 from
6 monitor_folder
7 where
8 yn = 1
9 <if test="name != null">
10 and name like concat('%',#{name,jdbcType=VARCHAR},'%')
11 </if>
12 and <![CDATA[id <=]]>
13 ( select
14 id
15 from
16 monitor_folder
17 where
18 yn=1
19 <if test="name != null">
20 and name like concat('%',#{name,jdbcType=VARCHAR},'%')
21 </if>
22 order by id desc limit #{startRowNum},1)
23 order by id desc
24 limit #{pageSize}
25 </select>

当时我个人的思路是:在分页中使用了两次查询,效率比较低。直接使用limit就好了。

查过资料之后,不管是自己的想法还是这个代码都是有不足的。

limit的标准写法:

1 SELECT * FROM table  LIMIT [offset,] rows | rows OFFSET offset

比如要查第十个开始的十个,就是limit 10, 10

许多网站的分页功能的底层实现就是依赖前端给后端传来一些参数,比如起始页和页面大小,来结合limit控制分页。但是这个地方首先有一个问题:效率。

如果这么写:

1 SELECT * FROM student LIMIT 10000 , 10;

实际底层执行是从第一行开始找到10010行,再抛弃前面的一万行。所以当用户往后翻到很多页的时候,offset这个值可能就比较大,实际执行效率就会很慢。

一般有几种做法来弥补。

1,用方法计算出开始和结尾条件,用where语句对查询进行限制。

1 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控制行数

1 SELECT * FROM student WHERE ID >= 9990 LIMIT 10;

这样的好处就是不仅效率高了,而且能刚好拿十条。但是这里依然还是有一个问题:不适用于所有情况。具体不适用与什么情况呢?简单来说就是id和行数不能对应的情况。比如ID本身是无规律离散的,那么计算这个起始ID就不能简单的pageIndex * pageSize了。