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了。