专栏首页程序猿杂货铺线上采用 IBatis 逻辑分页导致 SQL 慢查询问题排查

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

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

ibatis优点

半自动化

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

  1. 知道怎样操作 10种以上的数据库
  2. 可配置的 caching(包括从属)
  3. 支持 DataSourcelocaltransaction managementglobaltransaction
  4. 简单的 XML配置文档
  5. 支持 Map, Collection, List和简单类型包装(如 Integer, String)
  6. 支持 JavaBeans类( get/set 方法)
  7. 支持复杂的对象映射(如 populating lists, complexobjectmodels)
  8. 对象模型从不完美(不需要修改)
  9. 数据模型从不完美(不需要修改)
  10. 你已经知道 SQL,为什么还要学习其他东西

全自动化

使用 ibatis提供的 ORM机制,对业务逻辑实现人员而言,面对的是纯粹的 Java对象, 这一层与通过 Hibernate 实现 ORM 而言基本一致,而对于具体的数据操作, Hibernate 会自动生成 SQL 语句,而ibatis 则要求开发者编写具体的 SQL 语句。相对 Hibernate等 “全自动” ORM机制而言, ibatisSQL开发的工作量大和数据库移植性上差为代价,为系统 设计提供了更大的自由空间。作为“全自动” 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_ONLYFORWARD_ONLY类型的 ResultSet不支持 absolute方法,所以是通过next方法定位的。一般情况下,我们都使用 FORWARD_ONLY类型的 ResultSetSCROLL类 型 ResultSet的优点是可向前,向后滚动,并支持精确定位( absolute),但缺点是把结果集全部加载进缓存(如果查询是从 1000000条开 始取 100条,会把前 100万条数据也加载进缓存),容易造成内存溢出,性能也很差,除非必要,一般不使用。
  • 由于, ibatis的分页完全依赖于 JDBCResultSetnext方法或 absolute方法来实现。 所以分页还是要考虑采用直接操作 sql语句来完成。当然,小批量的可以采用 ibatis的分页模式。一般分页的 sql语句与数据库的具体实现有关。

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

需求分析

框架自身问题有两个:

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

代码分析

  1. 分析代码可知主要执行器 com.ibatis.sqlmap.engine.execution.SqlExecutor,由于没有采用接口实现的方式,所以实现接口是不可能的
  2. 如果想要完成 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

本文分享自微信公众号 - 程序猿杂货铺(zhoudl_l),作者:尚先生

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-04-06

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 【LeetCode题解---1】Two Sum

    “Bad programmers worry about the code. Good programmers worry about data structu...

    周三不加班
  • 【LeetCode - 015】三数之和

    每次从数组中选出一个数k。从剩下的数中求目标等于target-k的2sum问题。这里须要注意的是有个小技巧:当我们从数组中选出第i数时,我们仅仅须...

    周三不加班
  • 【LeetCode-191】Number of 1 bits

    Write a function that takes an unsigned integer and returns the number of '1' bi...

    周三不加班
  • 【回顾】2017年最受欢迎的十大机器学习Python库

    2017 年即将结束,又到了总结的时刻。本文作者把范围限定为机器学习,盘点了 2017 年以来最受欢迎的十大 Python 库;同时在这十个非常流行与强大的 P...

    机器人网
  • 「Python小例子」,我创建的github库,欢迎关注!

    Python语言使用率越来越高,基于Python的包更是枝繁叶茂,遍地开花,tiobe编程语言排行榜最新统计显示Python是增长最快的语言。

    double
  • 「首席看Event Hub」如何在您的Spring启动应用程序中使用Kafka

    在体系结构规划期间选择正确的消息传递系统始终是一个挑战,但这是需要确定的最重要的考虑因素之一。作为一名开发人员,我每天都要编写需要服务大量用户并实时处理大量数据...

    首席架构师智库
  • IDEA的Docker插件实战(Dockerfile篇)

    IntelliJ IDEA的Docker插件能帮助我们将当前工程制作成Docker镜像、运行在指定的远程机器上,是学习和开发阶段的好帮手,本文一起来实战此插件的...

    程序员欣宸
  • 疑难解答:ORA-01555的场景模拟和解决方案

    黄玮(Fuyuncat) 资深Oracle DBA,个人网站www.HelloDBA.com,致力于数据库底层技术的研究,其作品获得广大同行的高度评价. 前期...

    数据和云
  • 0748-5.14.4-Kafka的扩容和缩容

    在Kafka集群资源使用已超出系统配置的资源时,或者有大量资源闲置造成资源浪费的时候,需要分别通过扩容Kafka和缩容Kafka来进行调整。本篇文章Fayson...

    Fayson
  • Rabbitmq基本原理

    Exchange类似于数据通信网络中的交换机,提供消息路由策略。rabbitmq中,producer不是通过信道直接将消息发送给queue,而是先发送给Exch...

    于霆霖

扫码关注云+社区

领取腾讯云代金券