Oracle分页优化

项目升级MybatisPlus后发现MybatisPlus的Oracle分页好像有问题,然后查询效率也很慢,然后排查之后发现是他的分页SQL写的有问题

public class OracleDialect implements IDialect {

    @Override
    public DialectModel buildPaginationSql(String originalSql, long offset, long limit) {
        limit = (offset >= 1) ? (offset + limit) : limit;
        String sql = "SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( " +
            originalSql + " ) TMP WHERE ROWNUM <=" + FIRST_MARK + ") WHERE ROW_ID > " + SECOND_MARK;
        return new DialectModel(sql, limit, offset).setConsumerChain();
    }
}

这样的分页是全部查询出来之后再通过ROWNUMROW_ID来取其中的部分数据,效率很低,于是去github查看MybatisPlus的issues问题, github issues, 然后通过所描述的getDialect(DbType dbType, String dialectClazz);方法重写一个,如下

public class OracleNewDialect implements IDialect {

    @Override
    public DialectModel buildPaginationSql(String originalSql, long offset, long limit) {
        limit = (offset >= 1) ? (offset + limit) : limit;
        String sql = originalSql + " OFFSET " + FIRST_MARK + " ROWS FETCH NEXT " + SECOND_MARK + " ROWS ONLY ";
        return new DialectModel(sql, offset, limit).setConsumerChain();
    }
}

这样分页的好处就是提前告诉Oracle的偏移量,直接查出需要的条数,不再查全表

# SQL 
Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×