后台管理系统采用IBatis逻辑分页导致SQL慢查询问题

最新推荐文章于 2022-09-06 09:11:43 发布

shang_xs

最新推荐文章于 2022-09-06 09:11:43 发布

阅读量973

点赞数 1

分类专栏： java 文章标签： IBatis物理分页实现 IBatis逻辑分页bug IBatis扩展 ORM框架持久层框架优化

本文链接：https://blog.csdn.net/shang_xs/article/details/89003833

版权

java 专栏收录该内容

36 篇文章 1 订阅

订阅专栏

后台管理系统采用IBatis逻辑分页导致SQL慢查询问题

ibatis一词来源于internet和abatis的组合，是一个由Clinton Begin在2001年发起的开放源代码项目。于2010年6月16号被谷歌托管，改名为MyBatis。是一个基于SQL映射支持Java和.NET的持久层框架。

ibatis优点

半自动化

“半自动化”的ibatis，却刚好解决了这个问题。这里的“半自动化”，是相对Hibernate等提供了全面的数据库封装机制的“全自动化”ORM实现而言，“全自动”ORM实现了 POJO 和数据库表之间的映射，以及 SQL 的自动生成和执行。而ibatis 的着力点，则在于POJO 与 SQL之间的映射关系。也就是说，ibatis并不会为程序员在运行期自动生成 SQL 执行。具体的 SQL需要程序员编写，然后通过映射配置文件，将SQL所需的参数，以及返回的结果字段映射到指定POJO。
通常在如下场景和条件下，选择ibatis, 将更有助于发挥ibatis在持久层的优越性：

知道怎样操作10种以上的数据库
可配置的caching(包括从属)
支持DataSource、local transaction management和global transaction
简单的XML配置文档
支持Map, Collection, List和简单类型包装(如Integer, String)
支持JavaBeans类(get/set 方法)
支持复杂的对象映射(如populating lists, complex object models)
对象模型从不完美(不需要修改)
数据模型从不完美(不需要修改)
你已经知道SQL，为什么还要学习其他东西

全自动化

使用ibatis提供的ORM机制，对业务逻辑实现人员而言，面对的是纯粹的 Java对象，
这一层与通过Hibernate 实现 ORM 而言基本一致，而对于具体的数据操作，Hibernate
会自动生成SQL 语句，而ibatis 则要求开发者编写具体的SQL 语句。相对Hibernate等
“全自动”ORM机制而言，ibatis以 SQL开发的工作量大和数据库移植性上差为代价，为系统
设计提供了更大的自由空间。作为“全自动”ORM实现的一种有益补充，ibatis 的出现显
得别具意义。

ibatis不足

public class SqlMapClientImpl implements SqlMapClient, ExtendedSqlMapClient {

// 查询对象方法
 public Object queryForObject(String id, Object paramObject, Object resultObject) throws SQLException {
    return getLocalSqlMapSession().queryForObject(id, paramObject, resultObject);
  }
// 查询列表方法
  public List queryForList(String id, Object paramObject) throws SQLException {
    return getLocalSqlMapSession().queryForList(id, paramObject);
  }
}

实际调用链路

在这里插入图片描述

执行SQL拼接和调用执行

// MappedStatement#executeQueryForObject执行MappedStatement
public Object executeQueryForObject(StatementScope statementScope, Transaction trans, Object parameterObject, Object resultObject)
      throws SQLException {
    try {
      Object object = null;

      DefaultRowHandler rowHandler = new DefaultRowHandler();
      // 实际执行调用方法
      executeQueryWithCallback(statementScope, trans.getConnection(), parameterObject, resultObject, rowHandler, SqlExecutor.NO_SKIPPED_RESULTS, SqlExecutor.NO_MAXIMUM_RESULTS);
	...
    } catch (TransactionException e) {
    ...
    }
  }

调用SQL执行

protected void executeQueryWithCallback(StatementScope statementScope, Connection conn, Object parameterObject, Object resultObject, RowHandler rowHandler, int skipResults, int maxResults)
     throws SQLException {
   try {
   ...
    // 校验参数
     parameterObject = validateParameter(parameterObject);
    // 获取SQL
     Sql sql = getSql();
    // 获取parameterMap 
     ParameterMap parameterMap = sql.getParameterMap(statementScope, parameterObject);
    // 执行SQL调用
     sqlExecuteQuery(statementScope, conn, sqlString, parameters, skipResults, maxResults, callback);
   } catch (SQLException e) {
    ...
 }

结果集映射

private ResultSet handleMultipleResults(PreparedStatement ps, StatementScope statementScope, int skipResults, int maxResults, RowHandlerCallback callback) throws SQLException {
    ResultSet rs;
    // 获取调用结果
    rs = getFirstResultSet(statementScope, ps);
    if (rs != null) {
    // 处理结果集
      handleResults(statementScope, rs, skipResults, maxResults, callback);
    }
	...
    return rs;
  }

private void handleResults(StatementScope statementScope, ResultSet rs, int skipResults, int maxResults, RowHandlerCallback callback) throws SQLException {
   try {
     statementScope.setResultSet(rs);
     ResultMap resultMap = statementScope.getResultMap();
     if (resultMap != null) {
       // 跳过处理部分结果
       if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) {
         if (skipResults > 0) {
           rs.absolute(skipResults);
         }
       } else {
         for (int i = 0; i < skipResults; i++) {
           if (!rs.next()) {
             return;
           }
         }
       }
       // 获取最终结果集
       int resultsFetched = 0;
       while ((maxResults == SqlExecutor.NO_MAXIMUM_RESULTS || resultsFetched < maxResults) && rs.next()) {
         Object[] columnValues = resultMap.resolveSubMap(statementScope, rs).getResults(statementScope, rs);
         callback.handleResultObject(statementScope, columnValues, rs);
         resultsFetched++;
       }
     }
   } finally {
     statementScope.setResultSet(null);
   }
 }

ibatis存在的逻辑分页问题

从代码中可以看出ibatis分页查询的逻辑是首先判断ResulteSet的类型，如果ResultSet的类型是ResultSet.TYPE_FORWARD_ONLY，则使用ResultSet对象的next()方法，一步一步地移动游标到要取的第一条记录的位置，然后再采用next()方法取出一页的数据；如果ResultSet的类型不是ResultSet.TYPE_FORWARD_ONLY，则采用 ResultSet对象的absolute()方法，移动游标到要取的第一条记录的位置，然后再采用next()方法取出一页的数据。
其中resultSetType的可选值为FORWARD_ONLY | SCROLL_INSENSITIVE | SCROLL_SENSITIVE，如果没有配置，默认值为FORWARD_ONLY，FORWARD_ONLY类型的ResultSet不支持absolute方法，所以是通过next方法定位的。一般情况下，我们都使用FORWARD_ONLY类型的ResultSet，SCROLL类型ResultSet的优点是可向前，向后滚动，并支持精确定位（absolute）,但缺点是把结果集全部加载进缓存（如果查询是从1000000条开始取100条，会把前100万条数据也加载进缓存），容易造成内存溢出，性能也很差，除非必要，一般不使用。
由于，ibatis的分页完全依赖于JDBC ResultSet的next方法或absolute方法来实现。
所以分页还是要考虑采用直接操作sql语句来完成。当然，小批量的可以采用ibatis的分页模式。一般分页的sql语句与数据库的具体实现有关。

在框架基础上实现物理分页

需求分析

框架自身问题有两个：

在于分页实现是在结果集返回之后，所以我们面对的问题是，在SQL执行之前实现分页SQL的分页拼接。
在执行器中执行后，告诉结果集处理器不在进行逻辑分页处理，直接采用SQL查询结果，作为最终的结果集。
由于系统本身所采用的的是MySql数据库，本次采用针对MySql的分页实现。

代码分析

分析代码可知主要执行器com.ibatis.sqlmap.engine.execution.SqlExecutor，由于没有采用接口实现的方式，所以实现接口是不可能的
如果想要完成SQL拦截，可以有两种方式，采用拦截器，动态代理；或者采用反射方式实现自定义处理器的注入

代码实现

自定义Executor

/**
 * @ClassName: ExtSqlExecutor
 * @Description: 自定义处理器
 * @Author: 尚先生
 * @CreateDate: 2019/4/3 19:28
 * @Version: 1.0
 */
@Component("extSqlExecutor")
public class ExtSqlExecutor extends SqlExecutor {

    private static final Logger logger = LoggerFactory.getLogger(ExtSqlExecutor.class);

    private static final String SQL_END_DELIMITER = ";";

    /**
     * 不跳过结果
     */
    public static final int NO_SKIPPED_RESULTS = 0;
    /**
     * 查询所有结果
     */
    public static final int NO_MAXIMUM_RESULTS = -999999;

    public void executeQuery(StatementScope statementScope, Connection conn, String sql, Object[] parameters, int skipResults, int maxResults, RowHandlerCallback callback) throws SQLException {
        if (skipResults != NO_SKIPPED_RESULTS || maxResults != NO_MAXIMUM_RESULTS){
            sql = getLimitSql(sql,skipResults,maxResults);
        }
        logger.info("自定义执行器，查询SQL：", new Object[]{sql});

    }

    /**
     * 拼接SQL
     * @param sql
     * @param offset
     * @param limit
     * @return
     */
    public String getLimitSql(String sql, int offset, int limit) {
        sql = trim(sql);
        StringBuffer sb = new StringBuffer(sql.length() + 20);
        sb.append(sql);
        if (offset > 0) {
            sb.append(" limit ").append(offset).append(',').append(limit)
                    .append(SQL_END_DELIMITER);
        } else {
            sb.append(" limit ").append(limit).append(SQL_END_DELIMITER);
        }
        return sb.toString();
    }

    /**
     * 根据结束符号截取SQL
     * @param sql
     * @return
     */
    private String trim(String sql) {
        sql = sql.trim();
        if (sql.endsWith(SQL_END_DELIMITER)) {
            sql = sql.substring(0, sql.length() - 1
                    - SQL_END_DELIMITER.length());
        }
        return sql;
    }
}

反射设置自定义处理器

/**
 * @ClassName: ReflectUtils
 * @Description: 反射设置自定义处理器
 * @Author: 尚先生
 * @CreateDate: 2019/4/3 19:38
 * @Version: 1.0
 */
public class ReflectUtils {

    private static final Logger logger = LoggerFactory.getLogger(ReflectUtils.class);

    /**
     * 执行 set方法
     * @param target
     * @param name
     * @param type
     * @param value
     */
    public static void setFieldValue(Object target, String name, Class type,
                                     Object value) {
        if (target == null || name == null || StringUtils.isEmpty(name) || StringUtils.isEmpty(name)
        || (value != null && !type.isAssignableFrom(value.getClass()))){

            logger.error("设置自定义处理器异常，原因是存在参数值为空");
            return;
        }
        Class clazz = target.getClass();
        try {
            Field field = clazz.getDeclaredField(name);
            if (!Modifier.isPublic(field.getModifiers())) {
                // 设置属性可获取
                field.setAccessible(true);
            }
            field.set(target, value);
        } catch (Exception e) {
            logger.error("设置自定义处理器异常，异常信息：" ,new Object[]{e});
        }
    }
}

封装数据库执行类

/**
 * @ClassName: BaseDao
 * @Description: 封装数据库执行类
 * @Author: 尚先生
 * @CreateDate: 2019/4/3 19:41
 * @Version: 1.0
 */
public class BaseDao {

    @Autowired
    private SqlMapClient sqlMapClient;

    @Autowired
    private DataSource dataSource;

    @Autowired
    @Qualifier("extSqlExecutor")
    private SqlExecutor sqlExecutor;

    // 容器启动完成，执行设置自定义executor
    @PostConstruct
    public void initalizeExtexecutor(){
        if (null != sqlExecutor){
            if (sqlMapClient instanceof SqlMapClientImpl){
                SqlMapClientImpl client = (SqlMapClientImpl) this.sqlMapClient;
                ReflectUtils.setFieldValue(client.getDelegate(), "sqlExecutor",SqlExecutor.class,sqlExecutor);
            }
        }
    }
}
// 整合 SqlSession、DataSource
...

执行结果分析

预制测试环境100000条数据，分页查询200条数据，

select * from tb_cust order by create_time desc limit 90000, 200 ;

改造前

cost:35261ms

改造后

cost:1087ms

总结

实现物理分页之后由于节省查询结果内存，在有大量数据关联查询或者排序的情况下，效果会十分明显

源码地址

https://github.com/dwyanewede/project-learn/tree/master/src/main/java/com/learn/demo/ibatisExt

MyBatis 相关文章推荐

在之前分享的文章中有许多关于MyBatis的深入使用及扩展，详情可参考如下：

MyBatis深入理解和使用-MyBatis缓存体系:https://blog.csdn.net/shang_xs/article/details/86656353
MyBatis深入理解和使用-MyBatis事务管理:https://blog.csdn.net/shang_xs/article/details/86656649
MyBatis深入理解和使用-TypeHandler:https://blog.csdn.net/shang_xs/article/details/86656173

shang_xs

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
后台管理系统采用IBatis逻辑分页导致SQL慢查询问题

后台管理系统采用IBatis逻辑分页导致SQL慢查询问题ibatis一词来源于internet和abatis的组合，是一个由Clinton Begin在2001年发起的开放源代码项目。于2010年6月16号被谷歌托管，改名为MyBatis。是一个基于SQL映射支持Java和.NET的持久层框架。ibatis优点半自动化“半自动化”的ibatis，却刚好解决了这个问题。这里的“半自动化”，是...
复制链接

扫一扫