线上采用 IBatis 逻辑分页导致 SQL 慢查询问题排查

周三不加班

发布于 2019-06-02 19:56:04

1.4K0

ibatis一词来源于 internet和 abatis的组合，是一个由 ClintonBegin在 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、 localtransaction management和 globaltransaction
简单的 XML配置文档
支持 Map, Collection, List和简单类型包装(如 Integer, String)
支持 JavaBeans类( get/set 方法)
支持复杂的对象映射(如 populating lists, complexobjectmodels)
对象模型从不完美(不需要修改)
数据模型从不完美(不需要修改)
你已经知道 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执行MappedStatementpublic 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的分页完全依赖于 JDBCResultSet的 next方法或 absolute方法来实现。所以分页还是要考虑采用直接操作 sql语句来完成。当然，小批量的可以采用 ibatis的分页模式。一般分页的 sql语句与数据库的具体实现有关。

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

需求分析

框架自身问题有两个：

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

代码分析

分析代码可知主要执行器 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

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-04-06，如有侵权请联系 cloudcommunity@tencent.com 删除

编程算法

sql

数据库