MyBatis级联第四篇——N+1问题和全局延迟加载【推荐，MyBatis级联重要的一篇原创，建议认真读】

前面的三篇是我们介绍入门的章节，然而级联并非我们想象的那样简单。下面我们先描述一下N+1问题，再讨论全局设定的延迟加载。

1、N+1问题

在做完前面三篇入门的例子后，让我们运行一下以下：

package com.ykzhen2014.csdn.main;import org.apache.ibatis.session.SqlSession;import com.ykzhen2014.csdn.util.SqlSessionFactoryUtil;import com.ykzhen2015.csdn.mapper.EmployeeMapper;import com.ykzhen2015.csdn.pojo.Employee;public class CsdnMain {public static void main(String []args) {SqlSession sqlSession = null;try {sqlSession = SqlSessionFactoryUtil.openSqlSession();EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);Employee emp = employeeMapper.getEmployee(1);} finally {if (sqlSession != null) {    sqlSession.close();}}}}

让我们看看日志的情况：

DEBUG 2016-04-27 11:28:34,409 org.apache.ibatis.logging.LogFactory: Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.DEBUG 2016-04-27 11:28:34,629 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-04-27 11:28:34,629 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-04-27 11:28:34,629 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-04-27 11:28:34,629 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-04-27 11:28:34,629 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-04-27 11:28:34,869 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Opening JDBC ConnectionDEBUG 2016-04-27 11:28:35,170 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 1335298403.DEBUG 2016-04-27 11:28:35,200 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==>  Preparing: select id, emp_name as empName, sex from t_employee where id =? DEBUG 2016-04-27 11:28:35,220 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 1(Integer)DEBUG 2016-04-27 11:28:35,290 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ====>  Preparing: select prostate from t_healthy_male where emp_id = ? DEBUG 2016-04-27 11:28:35,290 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ====> Parameters: 1(Integer)DEBUG 2016-04-27 11:28:35,320 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <====      Total: 1DEBUG 2016-04-27 11:28:35,320 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ====>  Preparing: SELECT id, emp_id as empId, card_no as cardNo FROM t_employee_card WHERE emp_id = ? DEBUG 2016-04-27 11:28:35,320 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ====> Parameters: 1(Integer)DEBUG 2016-04-27 11:28:35,330 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <====      Total: 1DEBUG 2016-04-27 11:28:35,330 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ====>  Preparing: SELECT a.id, a.proj_name as projName FROM t_project a, t_employee_project b where a.id = b.proj_id and b.emp_id = ? DEBUG 2016-04-27 11:28:35,330 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ====> Parameters: 1(Integer)DEBUG 2016-04-27 11:28:35,370 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <====      Total: 2DEBUG 2016-04-27 11:28:35,370 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <==      Total: 1DEBUG 2016-04-27 11:28:35,370 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@4f970963]DEBUG 2016-04-27 11:28:35,370 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@4f970963]DEBUG 2016-04-27 11:28:35,370 org.apache.ibatis.datasource.pooled.PooledDataSource: Returned connection 1335298403 to pool.

我们发现级联是成功了，首先执行查询t_employee表，然后根据sex的结果，找到t_healthy_male的信息，跟着查询员工卡信息，最后查找工程信息。级联完全成功。但是......

假如我只想了解的信息只限于t_employee表，那么级联后，MyBatis会将所有相关数据查询，白白执行了好几个SQL，这显然不利于我们系统的性能。其次每当用t_employyee去级联别的查询，就会多执行一条SQL，这样就不断的消耗性能，这便是MyBatis的N+1问题，显然这不是一个结果，那么我们有什么好的办法呢?在MyBatis克服N+1问题的方法往往是延迟加载、

延迟加载分为全局性和非全局性的，我们这篇先研究全局性的延迟加载问题。

2 全局定义的延迟加载

延迟加载是MyBatis处理N+1问题的途径，我们有必要去研究一下延迟加载。在MyBatis中可以配置全局参数，控制延迟加载：

设置参数描述有效值默认值cacheEnabled该配置影响的所有映射器中配置的缓存的全局开关。true | falsetruelazyLoadingEnabled延迟加载的全局开关。当开启时，所有关联对象都会延迟加载。 特定关联关系中可通过设置fetchType属性来覆盖该项的开关状态。true | falsefalseaggressiveLazyLoading当启用时，对任意延迟属性的调用会使带有延迟加载属性的对象完整加载；反之，每种属性将会按需加载。true | falsetrue

这里截取的是官方的说明。但是我觉得还不够详细，我这里再加入一下论述，使其更加完善。

• cacheEnabled 是一个开关，代表你要不要使用缓存？
• lazyLoadingEnabled 也是一个开关，它来控制是否使用延迟加载的功能，但是延迟加载将采取默认规则，按树的层级加载，详细情况下篇我们在谈。
• aggressiveLazyLoading  如果设置它为false，则它不会按树的层级加载，具体的情况我们暂时不介绍，后面章节再谈。

好让我们现在修改这两个参数，看看运行的结果如何，现在我们修改MyBatis的配置文件：

  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"  "http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration>    <settings>     <setting name="cacheEnabled" value="true"/>     <setting name="lazyLoadingEnabled" value="true"/>     <setting name="aggressiveLazyLoading" value="false"/>    </settings><environments default="development">.......</configuration>

我们运行一下，上面的代码，得到日志：

DEBUG 2016-05-10 16:13:56,443 org.apache.ibatis.logging.LogFactory: Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.DEBUG 2016-05-10 16:13:56,582 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-05-10 16:13:56,584 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-05-10 16:13:56,584 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-05-10 16:13:56,584 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-05-10 16:13:56,584 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-05-10 16:13:56,921 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Opening JDBC ConnectionDEBUG 2016-05-10 16:13:57,183 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 1037324811.DEBUG 2016-05-10 16:13:57,199 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==>  Preparing: select id, emp_name as empName, sex from t_employee where id =? DEBUG 2016-05-10 16:13:57,220 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 1(Integer)DEBUG 2016-05-10 16:13:57,332 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <==      Total: 1DEBUG 2016-05-10 16:13:57,333 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@3dd4520b]DEBUG 2016-05-10 16:13:57,333 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@3dd4520b]DEBUG 2016-05-10 16:13:57,333 org.apache.ibatis.datasource.pooled.PooledDataSource: Returned connection 1037324811 to pool.

我们发现全部只是执行了一条SQL，仅仅查询出了t_employee表的信息，其他的信息并未加载，延迟加载的效果成功了。这时我们再把配置，修改为：

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE configuration  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"  "http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration>    <settings>     <setting name="cacheEnabled" value="true"/>     <setting name="lazyLoadingEnabled" value="true"/>     <setting name="aggressiveLazyLoading" value="true"/>    </settings><environments default="development">......</configuration>

这个时候就比较混乱了，读者可能摸不着头脑，不过这篇中我们只需要看看运行的结果，对于它们的论述，我们放到下篇去谈：

我们再运行一次代码，得到日志如下：

DEBUG 2016-05-10 16:18:56,910 org.apache.ibatis.logging.LogFactory: Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.DEBUG 2016-05-10 16:18:57,020 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-05-10 16:18:57,020 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-05-10 16:18:57,020 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-05-10 16:18:57,020 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-05-10 16:18:57,020 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-05-10 16:18:57,130 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Opening JDBC ConnectionDEBUG 2016-05-10 16:18:57,360 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 1037324811.DEBUG 2016-05-10 16:18:57,360 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==>  Preparing: select id, emp_name as empName, sex from t_employee where id =? DEBUG 2016-05-10 16:18:57,400 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 1(Integer)DEBUG 2016-05-10 16:18:57,482 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ====>  Preparing: select prostate from t_healthy_male where emp_id = ? DEBUG 2016-05-10 16:18:57,482 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ====> Parameters: 1(Integer)DEBUG 2016-05-10 16:18:57,532 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <====      Total: 1DEBUG 2016-05-10 16:18:57,532 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <==      Total: 1DEBUG 2016-05-10 16:18:57,532 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@3dd4520b]DEBUG 2016-05-10 16:18:57,532 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@3dd4520b]DEBUG 2016-05-10 16:18:57,532 org.apache.ibatis.datasource.pooled.PooledDataSource: Returned connection 1037324811 to pool.

显然健康情况的东西出来了，也就是说鉴别器的东西出来了。

好，我们再修改一下测试的代码：

public class CsdnMain {public static void main(String []args) {SqlSession sqlSession = null;try {sqlSession = SqlSessionFactoryUtil.openSqlSession();EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);Employee emp = employeeMapper.getEmployee(1);EmployeeCard card = emp.getEmployeeCard();//获取学生卡，效果如何呢？？} finally {if (sqlSession != null) {    sqlSession.close();}}}}

于是我们再次运行代码,得到如下日志：

DEBUG 2016-05-10 16:22:31,158 org.apache.ibatis.logging.LogFactory: Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.DEBUG 2016-05-10 16:22:31,248 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-05-10 16:22:31,248 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-05-10 16:22:31,248 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-05-10 16:22:31,248 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-05-10 16:22:31,248 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-05-10 16:22:31,358 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Opening JDBC ConnectionDEBUG 2016-05-10 16:22:31,588 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 1037324811.DEBUG 2016-05-10 16:22:31,588 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==>  Preparing: select id, emp_name as empName, sex from t_employee where id =? DEBUG 2016-05-10 16:22:31,628 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 1(Integer)DEBUG 2016-05-10 16:22:31,700 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ====>  Preparing: select prostate from t_healthy_male where emp_id = ? DEBUG 2016-05-10 16:22:31,700 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ====> Parameters: 1(Integer)DEBUG 2016-05-10 16:22:31,710 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <====      Total: 1DEBUG 2016-05-10 16:22:31,710 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <==      Total: 1DEBUG 2016-05-10 16:22:31,710 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==>  Preparing: SELECT a.id, a.proj_name as projName FROM t_project a, t_employee_project b where a.id = b.proj_id and b.emp_id = ? DEBUG 2016-05-10 16:22:31,710 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 1(Integer)DEBUG 2016-05-10 16:22:31,710 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <==      Total: 2DEBUG 2016-05-10 16:22:31,710 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==>  Preparing: SELECT id, emp_id as empId, card_no as cardNo FROM t_employee_card WHERE emp_id = ? DEBUG 2016-05-10 16:22:31,710 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 1(Integer)DEBUG 2016-05-10 16:22:31,710 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <==      Total: 1DEBUG 2016-05-10 16:22:31,710 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@3dd4520b]DEBUG 2016-05-10 16:22:31,710 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@3dd4520b]DEBUG 2016-05-10 16:22:31,710 org.apache.ibatis.datasource.pooled.PooledDataSource: Returned connection 1037324811 to pool.

于是一个结果出来了，除了员工卡信息外，我们发现连同员工项目表的信息也被加载了。

这便是笔者提到的树形层级加载，不过还是比较模糊，我们下章再谈，它的理论知识，这是全局参数的局限所在，为了克服这个问题我们下章还会讨论精确定义的加载策略。