项目升级MybatisPlus后发现MybatisPlus的Oracle分页好像有问题,然后查询效率也很慢,然后排查之后发现是他的分页SQL写的有问题
1 2 3 4 5 6 7 8 9 10
| 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(); } }
|
这样的分页是全部查询出来之后再通过ROWNUM
和ROW_ID
来取其中的部分数据,效率很低,于是去github查看MybatisPlus的issues问题,
github issues,
然后通过所描述的getDialect(DbType dbType, String dialectClazz);
方法重写一个,如下
1 2 3 4 5 6 7 8 9
| 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的偏移量,直接查出需要的条数,不再查全表