MySQL高效分页-mybatis插件PageHelper改进

MySQL分页在表比较大的时候，分页就会出现性能问题，MySQL的分页逻辑如下：比如select * from user limit 100000,10

它是先执行select * from user 扫描满足这个SQL语句，拿到执行结果后， 一页一页的找到行号为100000的行，返回接下来的10行数据，出现性能问题的原因有两个，1：它先全表扫描了，整个表，而不是扫描到了满足条件的数据就不扫描了，比如select * from user limit 1,10 这个，它不是扫描到满足条件的10行数据就完事了，而是扫描了整个表，然后从这个结果集中从上往下扫描，只到找到行号为1的后面10行数据，这里出现性能问题的原因2就在于MySQL的寻找行号的逻辑是怎么寻找的，是不是像如果是像数组那样通过下标一步定位行号就不存在页码大小的问题了，但是MySQL不是一步到位的找到这个页码的，具体是怎么找到页码的感兴趣的可以去看MySQL的源码，我们能做的就是将MySQL的逻辑转换为直接定位数据的位置。

 

 

比如Mybatis 上的SQL语句为 

   <select id="queryUserListLikeName" parameterType="java.lang.String" resultType="com.entity.user">

    select 
    <include refid="Base_Column_List" />
    from user t
    WHERE t.name LIKE '%${name}%' 
     order by id desc

  </select>

mybatis的 PageHelper 插件会在上面直接加上 limit 语句，源码如下

 

public class MySqlDialect extends AbstractHelperDialect {
    @Override
    public String getPageSql(String sql, Page page, CacheKey pageKey) {
        StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14);
        if (page.getStartRow() == 0) {
        sqlBuilder.append(sql);
            sqlBuilder.append(" LIMIT ");
            sqlBuilder.append(page.getPageSize());
        } else{
        sqlBuilder.append(sql);
            sqlBuilder.append(" LIMIT ");
            sqlBuilder.append(page.getStartRow());
            sqlBuilder.append(",");
            sqlBuilder.append(page.getPageSize());
            pageKey.update(page.getStartRow()); 
        }
        pageKey.update(page.getPageSize());
        return sqlBuilder.toString();
    }

 

就是直接调用MySQL的分页limit函数。

如何mybatis的PageHelper插件能将我们的SQL语句改成如下，那就大大提高大表的翻页查询效率，我本人亲七万行数据的表分页到最后一页这种方式比直接limit的方式快10倍，更大的表效率更大，其实原理很简单，我们给查询结果集加一个行号，查询出ID，和行号，再和原表通过ID关联，因为关联走了索引，索引速度很快，然后直接通过行号定位数据，速度大大提高

select id, name from 

(Select id as id2,(@rowNum:=@rowNum+1) as rowNo From user,(Select (@rowNum :=0) ) b) r ,

user t 

where r.id2= t.id and r.rowNo> 100000  and t.name like '%小明%' order by id desc LIMIT 10

 

我们来修改mybatis的源码：其实非常简单。如下

很多人可能mybaits的分页插件都没用过，我这里也将其全部使用过程。

我用的springboot 

在pom.xml中引入：

  <!-- 分页插件pagehelper -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.0.0</version>
        </dependency>