您现在的位置是：主页 > news > 哪些行业做网站多/seo网站内部优化方案

哪些行业做网站多/seo网站内部优化方案

admin2025/6/21 23:19:23【news】

简介哪些行业做网站多,seo网站内部优化方案,能否设置网站做局域网,出口贸易公司网站怎么做系列文章目录系列文章目录（兼容Oracle与MySQL） 文章目录系列文章目录前言一、MyBatis提供的RowBounds参数二、MyBatis提供的databaseId方式三、Mybatis-PageHelper解决方案1. 加入依赖2. 数据层编码3. 源码分析4. 开启动态数据源支持四、MyBatis-P…

哪些行业做网站多,seo网站内部优化方案,能否设置网站做局域网,出口贸易公司网站怎么做系列文章目录系列文章目录（兼容Oracle与MySQL） 文章目录系列文章目录前言一、MyBatis提供的RowBounds参数二、MyBatis提供的databaseId方式三、Mybatis-PageHelper解决方案1. 加入依赖2. 数据层编码3. 源码分析4. 开启动态数据源支持四、MyBatis-P…

系列文章目录

系列文章目录（兼容Oracle与MySQL）

文章目录

系列文章目录
前言
一、MyBatis提供的RowBounds参数
二、MyBatis提供的databaseId方式
三、Mybatis-PageHelper解决方案
- 1. 加入依赖
- 2. 数据层编码
- 3. 源码分析
- 4. 开启动态数据源支持
四、MyBatis-Plus解决方案
- 1. 首先加入项目依赖
- 2. 添加配置和Bean
- 3. 数据层编码
- 4. 业务层编码
- 5. 源码分析
总结

前言

分页对于一个系统来说通常都是不可回避的问题，本文倒不是仔细分析其中的性能问题（索引优化、查询方式）。主要是探讨在兼容Oracle与MySQL时优雅解决分页的问题。
通常来说MySQL分页语法非常简单，而且在排序与不排序时格式一致、如下所示

-- 从第0行开始 一共5条数据
SELECT * FROM TTRD_TEST_INSTRUMENT WHERE IS_NONSTD = 1 LIMIT 0,5;

如果需要排序的话

-- 如果需要排序的话 直接在Limit前面添加条件即可
SELECT * FROM TTRD_TEST_INSTRUMENT WHERE IS_NONSTD = 1 ORDER BY I_CODE,A_TYPE,M_TYPE LIMIT 0,5;

而在Oracle当中，如果不需要排序，格式如下

SELECT * FROM
(SELECT ROWNUM AS rowno,t.* FROM TTRD_TEST_INSTRUMENT t WHERE ROWNUM <= 10) table_alias
WHERE table_alias.rowno > 5;

而在Oracle当中，如果要进行分页，需要采用ROW_NUMBER函数，否则需要在最内层嵌套一层分页逻辑（整个查询语句三层SELECT），采用ROW_NUMBER函数的方式如下

-- 查询 二三名
SELECT * from (SELECT t.*,ROW_NUMBER() OVER (ORDER BY I_CODE,A_TYPE,M_TYPE) rn FROM TTRD_TEST_INSTRUMENT t WHERE t.IS_NONSTD = 1) WHERE rn > 1 AND rn <= 3;

我们不难发现，Oracle与MySQL的格式相差非常大，在项目中兼容二者时该如何处理呢？

提示：以下是本篇文章正文内容，下面案例可供参考

一、MyBatis提供的RowBounds参数

对于分页MyBatis其实也提供了自己的方式，通过在参数中传递RowBounds设置查询的参数。对应代码如下

package org.apache.ibatis.session;/*** @author Clinton Begin*/
public class RowBounds {public static final int NO_ROW_OFFSET = 0;public static final int NO_ROW_LIMIT = Integer.MAX_VALUE;// 默认使用public static final RowBounds DEFAULT = new RowBounds();private final int offset;private final int limit;public RowBounds() {this.offset = NO_ROW_OFFSET;this.limit = NO_ROW_LIMIT;}public RowBounds(int offset, int limit) {this.offset = offset;this.limit = limit;}public int getOffset() {return offset;}public int getLimit() {return limit;}
}

接口com.example.durid.demo.mapper.TtrdTestInstrumentMapper#selectSplitByMybatis定义如下

// 通过rowBounds传递分页信息
List<TtrdTestInstrument> selectSplitByMybatis(@Param("isNonstd") Integer isNonstd, RowBounds rowBounds);

xml文件如下

<select id="selectSplitByMybatis" resultMap="BaseResultMap">select I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_IDfrom TTRD_TEST_INSTRUMENT WHERE IS_NONSTD=#{isNonstd} ORDER BY I_CODE,A_TYPE,M_TYPE
</select>

假设在业务层按照如下调用（其中0为offset，limit为5），其实也就是从第0条查询，一共查询5条。

@Override
public List<TtrdTestInstrument> splitListByMyBatis(Integer isNonstd) {RowBounds rowBounds = new RowBounds(0,5);return ttrdInstrumentMapper.selectSplitByMybatis(isNonstd,rowBounds);
}

发起请求，并查看日志
在这里插入图片描述
所以，我们完成了分页。如果这么简单的话，那么我们就大错特错了。

c.e.d.d.m.T.selectSplitByMybatis         : ==>  Preparing: select I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID from TTRD_TEST_INSTRUMENT WHERE IS_NONSTD=? ORDER BY I_CODE,A_TYPE,M_TYPE 
c.e.d.d.m.T.selectSplitByMybatis         : ==>  Parameters: 0(Integer)

仔细查看日志中执行的查询语句（不包括分页条件），并在数据库执行，返回结果其实为9条
在这里插入图片描述
如果理解JDBC规范的话，此时就会有疑问了。其实MyBatis的分页是一种逻辑分页，而非物理分页。什么是逻辑分页呢？就是按照查询语句（不包含分页条件）去数据库查询了满足条件的所有结果，然后返回到客户端（数据库服务器为服务端），然后从ResultSet中按照RowBounds中的条件取出部分的结果，然后将其他结果都扔掉，我们上面通过日志看到的5条结果其实是经过MyBatis处理之后的结果。这样导致的问题就是当数据量非常大的时候，带来了不必要的带宽损失以及内存损失。主要参考源码：

执行数据库查询
org.apache.ibatis.executor.statement.PreparedStatementHandler#query

@Override
public <E> List<E> query(Statement statement, ResultHandler resultHandler) throws SQLException {PreparedStatement ps = (PreparedStatement) statement;// 执行查询ps.execute();// 返回结果的处理return resultSetHandler.handleResultSets(ps);
}

处理返回结果
org.apache.ibatis.executor.resultset.DefaultResultSetHandler#handleResultSets
处理返回结果无非就是从ResultSet当中获取返回的结果映射为目标对象，如果在xml中配置有ResultMap还会处理成org.apache.ibatis.mapping.ResultMap对象。创建一个multipleResults列表用于接收最后的结果。

@Override
public List<Object> handleResultSets(Statement stmt) throws SQLException {ErrorContext.instance().activity("handling results").object(mappedStatement.getId());final List<Object> multipleResults = new ArrayList<>();int resultSetCount = 0;// 获取结果并包装为ResultSetWrapper对象ResultSetWrapper rsw = getFirstResultSet(stmt);// 获取配置的ResultMap映射关系 一般一个xml中只有一个 但也有可能配置有多个List<ResultMap> resultMaps = mappedStatement.getResultMaps();// 获取配置的ResultMap映射关系的个数int resultMapCount = resultMaps.size();// 如果rsw有值而resultMapCount小于0的话则报错validateResultMapsCount(rsw, resultMapCount);// 绝大多数情况下resultMapCount=1，进入到这里 while (rsw != null && resultMapCount > resultSetCount) {ResultMap resultMap = resultMaps.get(resultSetCount);// 根据查询结果和映射处理返回结果集合handleResultSet(rsw, resultMap, multipleResults, null);// 获取下一个结果集 一般也为nullrsw = getNextResultSet(stmt);cleanUpAfterHandlingResultSet();resultSetCount++;}// 一般这里为null String[] resultSets = mappedStatement.getResultSets();if (resultSets != null) {while (rsw != null && resultSetCount < resultSets.length) {ResultMapping parentMapping = nextResultMaps.get(resultSets[resultSetCount]);if (parentMapping != null) {String nestedResultMapId = parentMapping.getNestedResultMapId();ResultMap resultMap = configuration.getResultMap(nestedResultMapId);handleResultSet(rsw, resultMap, null, parentMapping);}rsw = getNextResultSet(stmt);cleanUpAfterHandlingResultSet();resultSetCount++;}}// 合并结果返回 这里是最后查询的结果return collapseSingleResultList(multipleResults);
}

首先根据JDBC规范获取结果的逻辑主要是在getFirstResultSet方法当中。

private ResultSetWrapper getFirstResultSet(Statement stmt) throws SQLException {// 获取结果	ResultSet rs = stmt.getResultSet();while (rs == null) {// move forward to get the first resultset in case the driver// doesn't return the resultset as the first result (HSQLDB 2.1)if (stmt.getMoreResults()) {rs = stmt.getResultSet();} else {if (stmt.getUpdateCount() == -1) {// no more results. Must be no resultsetbreak;}}}return rs != null ? new ResultSetWrapper(rs, configuration) : null;
}

根据ResultSet结果构造ResultSetWrapper对象。这里面会根据返回的元数据集合的大小一个一个的进行处理。也就是说这里的元数据个数应该是数据库查询结果集的真实大小。
org.apache.ibatis.executor.resultset.ResultSetWrapper#ResultSetWrapper.

public ResultSetWrapper(ResultSet rs, Configuration configuration) throws SQLException {super();this.typeHandlerRegistry = configuration.getTypeHandlerRegistry();this.resultSet = rs;// 获取返回的元数据final ResultSetMetaData metaData = rs.getMetaData();// 计算个数final int columnCount = metaData.getColumnCount();// 一个一个处理for (int i = 1; i <= columnCount; i++) {// 获取数据库表列名称columnNames.add(configuration.isUseColumnLabel() ? metaData.getColumnLabel(i) : metaData.getColumnName(i));// 添加jdbcTypejdbcTypes.add(JdbcType.forCode(metaData.getColumnType(i)));// 添加类名称classNames.add(metaData.getColumnClassName(i));}
}

在这里插入图片描述

处理返回结果

private void handleResultSet(ResultSetWrapper rsw, ResultMap resultMap, List<Object> multipleResults, ResultMapping parentMapping) throws SQLException {try {// 处理父映射if (parentMapping != null) {handleRowValues(rsw, resultMap, null, RowBounds.DEFAULT, parentMapping);} else {// 这个参数一般为nullif (resultHandler == null) {// 这个默认的ResultHandler内部存放的为List<Object>,用于存放返回结果DefaultResultHandler defaultResultHandler = new DefaultResultHandler(objectFactory);// 一行一行返回结果的映射handleRowValues(rsw, resultMap, defaultResultHandler, rowBounds, null);// 添加到结果列表当中multipleResults.add(defaultResultHandler.getResultList());} else {handleRowValues(rsw, resultMap, resultHandler, rowBounds, null);}}} finally {// issue #228 (close resultsets)closeResultSet(rsw.getResultSet());}
}

处理返回结果映射handleRowValues

public void handleRowValues(ResultSetWrapper rsw, ResultMap resultMap, ResultHandler<?> resultHandler, RowBounds rowBounds, ResultMapping parentMapping) throws SQLException {if (resultMap.hasNestedResultMaps()) {// 处理嵌套查询情况ensureNoRowBounds();checkResultHandler();// 处理嵌套查询 主要逻辑handleRowValuesForNestedResultMap(rsw, resultMap, resultHandler, rowBounds, parentMapping);} else {// 处理简单映射 主要逻辑handleRowValuesForSimpleResultMap(rsw, resultMap, resultHandler, rowBounds, parentMapping);}
}private void handleRowValuesForSimpleResultMap(ResultSetWrapper rsw, ResultMap resultMap, ResultHandler<?> resultHandler, RowBounds rowBounds, ResultMapping parentMapping)throws SQLException {DefaultResultContext<Object> resultContext = new DefaultResultContext<>();//再次取出JDBC的Result对象ResultSet resultSet = rsw.getResultSet();// 根据rowBounds过滤行skipRows(resultSet, rowBounds);while (shouldProcessMoreRows(resultContext, rowBounds) && !resultSet.isClosed() && resultSet.next()) {// 处理鉴别器 一般情况下不包含 直接返回ResultMap信息 ResultMap discriminatedResultMap = resolveDiscriminatedResultMap(resultSet, resultMap, null);// 创建目标对象 并完成值的映射Object rowValue = getRowValue(rsw, discriminatedResultMap, null);storeObject(resultHandler, resultContext, rowValue, parentMapping, resultSet);}
}

在上面的skipRows和shouldProcessMoreRows的逻辑就是来实现假分页（逻辑分页）的。在skipRows方法中根据用户设置的rowBounds属性offset过滤数据，而在shouldProcessMoreRows方法中提取索引不大于limit属性的数据。
在这里插入图片描述

从以上的结果当中我们不难看出，这是个假分页，会造成非常严重的性能问题，实际项目中肯定不会使用的。另外如果对日志打印为什么是5条感兴趣的可以查看源码（每次调用结果集的next方法才会打印日志）org.apache.ibatis.logging.jdbc.ResultSetLogger#invoke以及org.apache.ibatis.logging.jdbc.ResultSetLogger#printColumnValues方法(动态代理模式)。
在这里插入图片描述

二、MyBatis提供的databaseId方式

在兼容Oracle与MySQL的那些事中我们详细探讨了这种模式，此处仅给出最后的结果

/*** 根据分页条件查询结果* @param start 开始行数* @param offset 查询行数* @param isNonstd 是否非标* @return 分页结果*/
List<TtrdTestInstrument> selectSplit(@Param("start") Integer start, @Param("offset") Integer offset, @Param("isNonstd") Integer isNonstd);

  <select id="selectSplit" resultMap="BaseResultMap">SELECT I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID<if test="_databaseId == 'mysql'">FROM TTRD_TEST_INSTRUMENT WHERE IS_NONSTD=#{isNonstd}ORDER BY I_CODE,A_TYPE,M_TYPE<!--mysql从0开始计数-->LIMIT #{start},#{offset}</if><if test="_databaseId == 'oracle'">FROM (SELECT t.*,ROW_NUMBER() OVER (ORDER BY I_CODE,A_TYPE,M_TYPE) rn FROM TTRD_TEST_INSTRUMENT t WHERE t.IS_NONSTD = #{isNonstd})<!--oracle从1开始计数 兼容mysql 业务从0开始计数-->WHERE rn &gt;= #{start} + 1  AND rn &lt; #{start} + 1 + #{offset}</if></select>

测试查询结果如下

2020-11-09 14:31:19.375  INFO 11048 --- [nio-8083-exec-1] c.e.durid.demo.config.DynamicDataSource  : 当前数据源为oracle
2020-11-09 14:31:19.378 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Bound value [org.springframework.jdbc.datasource.ConnectionHolder@4400c2c0] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] to thread [http-nio-8083-exec-1]
2020-11-09 14:31:19.378 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Initializing transaction synchronization
2020-11-09 14:31:19.378 TRACE 11048 --- [nio-8083-exec-1] o.s.t.i.TransactionInterceptor           : Getting transaction for [com.example.durid.demo.service.impl.InstrumentServiceImpl.splitList]
2020-11-09 14:31:19.411 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Bound value [org.mybatis.spring.SqlSessionHolder@67f1aeb4] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@5e137cf4] to thread [http-nio-8083-exec-1]
2020-11-09 14:31:19.457 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@4400c2c0] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] bound to thread [http-nio-8083-exec-1]
2020-11-09 14:31:19.457 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@4400c2c0] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] bound to thread [http-nio-8083-exec-1]
2020-11-09 14:31:19.463 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@4400c2c0] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] bound to thread [http-nio-8083-exec-1]
2020-11-09 14:31:19.465 DEBUG 11048 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplit                  : ==>  Preparing: SELECT I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID FROM (SELECT t.*,ROW_NUMBER() OVER (ORDER BY I_CODE,A_TYPE,M_TYPE) rn FROM TTRD_TEST_INSTRUMENT t WHERE t.IS_NONSTD = ?) WHERE rn >= ? + 1 AND rn < ? + 1 + ? 
2020-11-09 14:31:19.590 DEBUG 11048 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplit                  : ==> Parameters: 1(Integer), 1(Integer), 1(Integer), 3(Integer)
2020-11-09 14:31:19.622 TRACE 11048 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplit                  : <==    Columns: I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID
2020-11-09 14:31:19.628 TRACE 11048 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplit                  : <==        Row: LLXXMTEST01, SPT_LBS, X_CNBD, 2020-08-03, 7D, 56838, 1, 1, 6024559232602456024560144602446
2020-11-09 14:31:19.632 TRACE 11048 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplit                  : <==        Row: LYtest001, SPT_LBS, X_CNBD, 2020-08-27, 23D, 60245, 1, 1, 6024559232602456024560144602446
2020-11-09 14:31:19.633 TRACE 11048 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplit                  : <==        Row: LYtest001(temp), SPT_LBS, X_CNBD, 2020-08-27, 23D, 60245, 1, 1, 6024559232602456024560144602446
2020-11-09 14:31:19.634 DEBUG 11048 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplit                  : <==      Total: 3
2020-11-09 14:31:19.635 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.mybatis.spring.SqlSessionHolder@67f1aeb4] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@5e137cf4] bound to thread [http-nio-8083-exec-1]
2020-11-09 14:31:19.635 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.mybatis.spring.SqlSessionHolder@67f1aeb4] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@5e137cf4] bound to thread [http-nio-8083-exec-1]
2020-11-09 14:31:19.636 TRACE 11048 --- [nio-8083-exec-1] o.s.t.i.TransactionInterceptor           : Completing transaction for [com.example.durid.demo.service.impl.InstrumentServiceImpl.splitList]
2020-11-09 14:31:19.638 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Removed value [org.mybatis.spring.SqlSessionHolder@67f1aeb4] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@5e137cf4] from thread [http-nio-8083-exec-1]
2020-11-09 14:31:19.639 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@4400c2c0] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] bound to thread [http-nio-8083-exec-1]
2020-11-09 14:31:19.642 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Clearing transaction synchronization
2020-11-09 14:31:19.643 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Removed value [org.springframework.jdbc.datasource.ConnectionHolder@4400c2c0] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] from thread [http-nio-8083-exec-1]
2020-11-09 14:32:32.643  INFO 11048 --- [nio-8083-exec-4] c.e.durid.demo.config.DynamicDataSource  : 当前数据源为mysql
2020-11-09 14:32:32.654 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Bound value [org.springframework.jdbc.datasource.ConnectionHolder@2306b7a5] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] to thread [http-nio-8083-exec-4]
2020-11-09 14:32:32.654 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Initializing transaction synchronization
2020-11-09 14:32:32.654 TRACE 11048 --- [nio-8083-exec-4] o.s.t.i.TransactionInterceptor           : Getting transaction for [com.example.durid.demo.service.impl.InstrumentServiceImpl.splitList]
2020-11-09 14:32:32.655 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Bound value [org.mybatis.spring.SqlSessionHolder@23f3ae62] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@5e137cf4] to thread [http-nio-8083-exec-4]
2020-11-09 14:32:32.655 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@2306b7a5] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] bound to thread [http-nio-8083-exec-4]
2020-11-09 14:32:32.655 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@2306b7a5] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] bound to thread [http-nio-8083-exec-4]
2020-11-09 14:32:32.655 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@2306b7a5] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] bound to thread [http-nio-8083-exec-4]
2020-11-09 14:32:32.655 DEBUG 11048 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplit                  : ==>  Preparing: SELECT I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID FROM TTRD_TEST_INSTRUMENT WHERE IS_NONSTD=? ORDER BY I_CODE,A_TYPE,M_TYPE LIMIT ?,? 
2020-11-09 14:32:32.665 DEBUG 11048 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplit                  : ==> Parameters: 1(Integer), 1(Integer), 3(Integer)
2020-11-09 14:32:32.670 TRACE 11048 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplit                  : <==    Columns: I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID
2020-11-09 14:32:32.670 TRACE 11048 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplit                  : <==        Row: gaegaeg(temp), SPT_LBS, X_CNBD, 2020-08-29, 19D, <<BLOB>>, 1.00000000, 1, 6024559232602456024560144602446
2020-11-09 14:32:32.672 TRACE 11048 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplit                  : <==        Row: glrllxzc0817, SPT_LBS, X_CNBD, 2020-07-29, 1D, <<BLOB>>, 1.00000000, 1, 6024559232602456024560144602446
2020-11-09 14:32:32.672 TRACE 11048 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplit                  : <==        Row: glrllxzc081702, SPT_LBS, X_CNBD, 2020-07-30, 1, <<BLOB>>, 1.00000000, 1, 6024559232602456024560144602446
2020-11-09 14:32:32.673 DEBUG 11048 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplit                  : <==      Total: 3
2020-11-09 14:32:32.673 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.mybatis.spring.SqlSessionHolder@23f3ae62] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@5e137cf4] bound to thread [http-nio-8083-exec-4]
2020-11-09 14:32:32.674 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.mybatis.spring.SqlSessionHolder@23f3ae62] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@5e137cf4] bound to thread [http-nio-8083-exec-4]
2020-11-09 14:32:32.674 TRACE 11048 --- [nio-8083-exec-4] o.s.t.i.TransactionInterceptor           : Completing transaction for [com.example.durid.demo.service.impl.InstrumentServiceImpl.splitList]
2020-11-09 14:32:32.674 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Removed value [org.mybatis.spring.SqlSessionHolder@23f3ae62] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@5e137cf4] from thread [http-nio-8083-exec-4]
2020-11-09 14:32:32.674 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@2306b7a5] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] bound to thread [http-nio-8083-exec-4]
2020-11-09 14:32:32.675 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Clearing transaction synchronization
2020-11-09 14:32:32.675 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Removed value [org.springframework.jdbc.datasource.ConnectionHolder@2306b7a5] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] from thread [http-nio-8083-exec-4]

通过这种方式比较直观简单，而且不用担心副作用。个人比较推荐的方式。

三、Mybatis-PageHelper解决方案

针对于Mybatis-PageHelper的官方文章参考如下（必须对MyBatis拦截器有足够的理解）：
对应项目的地址：https://github.com/pagehelper/Mybatis-PageHelper
使用手册：https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md
高级教程：Executor 拦截器高级教程

1. 加入依赖

由于当前项目为SpringBoot项目，直接引入pom依赖

<dependency><groupId>com.github.pagehelper</groupId><artifactId>pagehelper-spring-boot-starter</artifactId><version>1.3.0</version>
</dependency>

2. 数据层编码

PageHelper有多种模式进行分页配置，其中对项目侵入最小的就是支持MyBatis的RowBounds参数模式，在这种情况下接口以及xml配置与第一种方式一模一样。

List<TtrdTestInstrument> selectSplitByMybatis(@Param("isNonstd") Integer isNonstd, RowBounds rowBounds);

<select id="selectSplitByMybatis" resultMap="BaseResultMap">select I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_IDfrom TTRD_TEST_INSTRUMENT WHERE IS_NONSTD=#{isNonstd} ORDER BY I_CODE,A_TYPE,M_TYPE
</select>

3. 源码分析

Mybatis-PageHelper作用的逻辑就是拦截查询语句，然后根据分页条件（RowBounds）拼接最后完整的查询语句（拼接完成之后，将RowBounds修改为默认的配置，防止原生MyBatis的逻辑分页导致数据过滤问题），真正实现了物理分页。
比如以下为Oracle的拼接结果以及查询日志
在这里插入图片描述

2020-11-09 15:31:00.177  INFO 8464 --- [nio-8083-exec-4] c.e.durid.demo.config.DynamicDataSource  : 当前数据源为oracle
2020-11-09 15:41:14.131 TRACE 8464 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@56f6adae] for key [com.example.durid.demo.config.DynamicDataSource@1e00bfe2] bound to thread [http-nio-8083-exec-4]
2020-11-09 15:41:14.131 TRACE 8464 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@56f6adae] for key [com.example.durid.demo.config.DynamicDataSource@1e00bfe2] bound to thread [http-nio-8083-exec-4]
2020-11-09 15:41:14.132 TRACE 8464 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@56f6adae] for key [com.example.durid.demo.config.DynamicDataSource@1e00bfe2] bound to thread [http-nio-8083-exec-4]
2020-11-09 15:41:14.132 DEBUG 8464 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplitByMybatis         : ==>  Preparing: SELECT * FROM ( SELECT TMP_PAGE.*, ROWNUM PAGEHELPER_ROW_ID FROM ( select I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID from TTRD_TEST_INSTRUMENT WHERE IS_NONSTD=? ORDER BY I_CODE,A_TYPE,M_TYPE ) TMP_PAGE) WHERE PAGEHELPER_ROW_ID <= ? AND PAGEHELPER_ROW_ID > ? 
2020-11-09 15:41:14.221 DEBUG 8464 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplitByMybatis         : ==> Parameters: 0(Integer), 5(Long), 0(Long)
2020-11-09 15:41:14.251 TRACE 8464 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplitByMybatis         : <==    Columns: I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID, PAGEHELPER_ROW_ID
2020-11-09 15:41:14.256 TRACE 8464 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplitByMybatis         : <==        Row: 42dc0034-0ffe-4ef2-8edc-ae54a9595001, SPT_LBS, X_CNBD, 2020-08-03, 7D, 60245, 2, 0, 6024559232602456024560144602446, 1
2020-11-09 15:41:14.258 TRACE 8464 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplitByMybatis         : <==        Row: 8b124d35-7cce-4e91-8bbb-e2a0b8bcd203, SPT_LBS, X_CNBD, 2020-08-03, 7D, 60245, 2, 0, 6024559232602456024560144602446, 2
2020-11-09 15:41:14.259 TRACE 8464 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplitByMybatis         : <==        Row: CFTYTEST01, SPT_LBS, X_CNBD, 2020-08-03, 7, 59868, 1, 0, 6024559232602456024560144602446, 3
2020-11-09 15:41:14.259 TRACE 8464 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplitByMybatis         : <==        Row: SYXZGJH01, SPT_LBS, X_CNBD, 2020-08-03, 7D, 60245, 1, 0, 6024559232602456024560144602446, 4
2020-11-09 15:41:14.259 TRACE 8464 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplitByMybatis         : <==        Row: a7b29f59-b01f-4bf4-ad31-28dc4ca5150d, SPT_LBS, X_CNBD, 2020-08-03, 7D, 60245, 2, 0, 6024559232602456024560144602446, 5
2020-11-09 15:41:14.260 DEBUG 8464 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplitByMybatis         : <==      Total: 5
2020-11-09 15:41:16.333 TRACE 8464 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.mybatis.spring.SqlSessionHolder@71f93091] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@28e8888d] bound to thread [http-nio-8083-exec-4]
2020-11-09 15:41:16.333 TRACE 8464 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.mybatis.spring.SqlSessionHolder@71f93091] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@28e8888d] bound to thread [http-nio-8083-exec-4]
2020-11-09 15:41:16.333 TRACE 8464 --- [nio-8083-exec-4] o.s.t.i.TransactionInterceptor           : Completing transaction for [com.example.durid.demo.service.impl.InstrumentServiceImpl.splitListByMyBatis]
2020-11-09 15:41:16.333 TRACE 8464 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Removed value [org.mybatis.spring.SqlSessionHolder@71f93091] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@28e8888d] from thread [http-nio-8083-exec-4]
2020-11-09 15:41:16.333 TRACE 8464 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@56f6adae] for key [com.example.durid.demo.config.DynamicDataSource@1e00bfe2] bound to thread [http-nio-8083-exec-4]
2020-11-09 15:41:16.337 TRACE 8464 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Clearing transaction synchronization
2020-11-09 15:41:16.337 TRACE 8464 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Removed value [org.springframework.jdbc.datasource.ConnectionHolder@56f6adae] for key [com.example.durid.demo.config.DynamicDataSource@1e00bfe2] from thread [http-nio-8083-exec-4]

以下为MySQL的拼接结果以及查询日志
在这里插入图片描述

2020-11-09 15:53:29.530  INFO 9328 --- [nio-8083-exec-1] c.e.durid.demo.config.DynamicDataSource  : 当前数据源为mysql
2020-11-09 15:53:29.559 TRACE 9328 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@7ad5e3ef] for key [com.example.durid.demo.config.DynamicDataSource@1625789b] bound to thread [http-nio-8083-exec-1]
2020-11-09 15:53:29.559 TRACE 9328 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@7ad5e3ef] for key [com.example.durid.demo.config.DynamicDataSource@1625789b] bound to thread [http-nio-8083-exec-1]
2020-11-09 15:53:29.564 TRACE 9328 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@7ad5e3ef] for key [com.example.durid.demo.config.DynamicDataSource@1625789b] bound to thread [http-nio-8083-exec-1]
2020-11-09 15:53:29.566 DEBUG 9328 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplitByMybatis         : ==>  Preparing: select I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID from TTRD_TEST_INSTRUMENT WHERE IS_NONSTD=? ORDER BY I_CODE,A_TYPE,M_TYPE LIMIT ? 
2020-11-09 15:53:29.589 DEBUG 9328 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplitByMybatis         : ==> Parameters: 0(Integer), 5(Integer)
2020-11-09 15:53:29.605 TRACE 9328 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplitByMybatis         : <==    Columns: I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID
2020-11-09 15:53:29.606 TRACE 9328 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplitByMybatis         : <==        Row: 04cf1a29-db93-4443-b520-b8d7260afa45, SPT_LBS, X_CNBD, 2020-08-03, 7D, <<BLOB>>, 2.00000000, 0, 6024559232602456024560144602446
2020-11-09 15:53:29.608 TRACE 9328 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplitByMybatis         : <==        Row: c0145797-720f-4291-ab41-97fb223fe32e, SPT_LBS, X_CNBD, 2020-08-03, 7D, <<BLOB>>, 2.00000000, 0, 6024559232602456024560144602446
2020-11-09 15:53:29.608 TRACE 9328 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplitByMybatis         : <==        Row: CFTYTEST01, SPT_LBS, X_CNBD, 2020-08-03, 7, <<BLOB>>, 1.00000000, 0, 6024559232602456024560144602446
2020-11-09 15:53:29.609 TRACE 9328 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplitByMybatis         : <==        Row: gyxty001, SPT_LBS, X_CNBD, 2020-09-29, 57, <<BLOB>>, 1.00000000, 0, 6024559232602456024560144602446
2020-11-09 15:53:29.610 TRACE 9328 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplitByMybatis         : <==        Row: gyxty002, SPT_LBS, X_CNBD, 2020-09-30, 58, <<BLOB>>, 1.00000000, 0, 6024559232602456024560144602446
2020-11-09 15:53:29.610 DEBUG 9328 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplitByMybatis         : <==      Total: 5

Mybatis-PageHelper完美的利用了MyBatis的拦截器针对sql语句进行了增强（拼接），实现了物理分页。

4. 开启动态数据源支持

如果需要开启动态数据源的支持，需要配置参数pagehelper.auto-runtime-dialect=true

参考源码com.github.pagehelper.page.PageAutoDialect#getDialect.

private AbstractHelperDialect getDialect(MappedStatement ms) {DataSource dataSource = ms.getConfiguration().getEnvironment().getDataSource();String url = getUrl(dataSource);if (urlDialectMap.containsKey(url)) {return urlDialectMap.get(url);}try {lock.lock();if (urlDialectMap.containsKey(url)) {return urlDialectMap.get(url);}if (StringUtil.isEmpty(url)) {throw new PageException("无法自动获取jdbcUrl，请在分页插件中配置dialect参数!");}String dialectStr = fromJdbcUrl(url);if (dialectStr == null) {throw new PageException("无法自动获取数据库类型，请通过 helperDialect 参数指定!");}AbstractHelperDialect dialect = initDialect(dialectStr, properties);urlDialectMap.put(url, dialect);return dialect;} finally {lock.unlock();}
}

四、MyBatis-Plus解决方案

MyBatis-Plus作为一个针对MyBatis进行增强的项目，也针对分页提供了自己的支持（https://baomidou.com/guide/page.html）。

1. 首先加入项目依赖

<!--添加mybaits-plus依赖-->
<dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.4.0</version>
</dependency>

2. 添加配置和Bean

# 必须添加这个配置 否则mybatis-plus无法查找对数据层接口的实现
mybatis-plus.mapper-locations=classpath*:sqlmapper/**/*.xml

package com.example.durid.demo.config;import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.autoconfigure.ConfigurationCustomizer;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;@Configuration
public class MybatisPlusConfig {/*** 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题*/@Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();interceptor.addInnerInterceptor(new PaginationInnerInterceptor());return interceptor;}@Beanpublic ConfigurationCustomizer configurationCustomizer() {return configuration -> configuration.setUseDeprecatedExecutor(false);}
}

3. 数据层编码

此处使用MyBatis-Plus的IPage分页工具类，另外此处数据层接口不需要是继承MyBatis-Plus的BaseMapper接口

import com.baomidou.mybatisplus.core.metadata.IPage;List<TtrdTestInstrument> iPageSelect(IPage<TtrdTestInstrument> myPage, @Param("isNonstd") Integer isNonstd);

<select id="iPageSelect" resultMap="BaseResultMap">SELECT I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_IDFROM TTRD_TEST_INSTRUMENT WHERE IS_NONSTD=#{isNonstd}ORDER BY I_CODE,A_TYPE,M_TYPE
</select>

4. 业务层编码

@Override
public List<TtrdTestInstrument> splitListByPlus(Integer isNonstd) {Page<TtrdTestInstrument> page = new Page<>(0, 5);return ttrdInstrumentMapper.iPageSelect(page, isNonstd);
}

5. 源码分析

MyBatis-Plus针对分页的支持与PageHelper大同小异，都是通过MyBatis提供的拦截器进行增强来实现的。
在这里插入图片描述
类com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor作为MyBatis的一个拦截器，在内部再管理多个MyBatis-Plus自己的拦截器。

@Setter
private List<InnerInterceptor> interceptors = new ArrayList<>();

在这里插入图片描述
其中针对分页的为com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor，需要自己添加到interceptors列表中才会起作用。主要是两个逻辑，在willDoQuery判断是否需要执行查询，而在beforeQuery（MyBatis数据库查询之前）进行一些加强处理。

if (!query.willDoQuery(executor, ms, parameter, rowBounds, resultHandler, boundSql)) {return Collections.emptyList();
}
query.beforeQuery(executor, ms, parameter, rowBounds, resultHandler, boundSql);

首先在willDoQuery中进行一些条件的判断，比如查询数据库数据的总条数，如果总条数为0，那么就直接返回了，没必要分页考虑。

com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor#buildAutoCountMappedStatement

/*** 构建 mp 自用自动的 MappedStatement** @param ms MappedStatement* @return MappedStatement*/
protected MappedStatement buildAutoCountMappedStatement(MappedStatement ms) {final String countId = ms.getId() + "_mpCount";final Configuration configuration = ms.getConfiguration();return CollectionUtils.computeIfAbsent(countMsCache, countId, key -> {MappedStatement.Builder builder = new MappedStatement.Builder(configuration, key, ms.getSqlSource(), ms.getSqlCommandType());builder.resource(ms.getResource());builder.fetchSize(ms.getFetchSize());builder.statementType(ms.getStatementType());builder.timeout(ms.getTimeout());builder.parameterMap(ms.getParameterMap());builder.resultMaps(Collections.singletonList(new ResultMap.Builder(configuration, Constants.MYBATIS_PLUS, Long.class, Collections.emptyList()).build()));builder.resultSetType(ms.getResultSetType());builder.cache(ms.getCache());builder.flushCacheRequired(ms.isFlushCacheRequired());builder.useCache(ms.isUseCache());return builder.build();});
}

在这里插入图片描述

然后在beforeQuery中根据获取的方言拼接分页语句

@Override
public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {IPage<?> page = ParameterUtils.findPage(parameter).orElse(null);if (null == page) {return;}// 处理 orderBy 拼接boolean addOrdered = false;String buildSql = boundSql.getSql();List<OrderItem> orders = page.orders();if (!CollectionUtils.isEmpty(orders)) {addOrdered = true;buildSql = this.concatOrderBy(buildSql, orders);}// size 小于 0 不构造分页sqlif (page.getSize() < 0) {if (addOrdered) {PluginUtils.mpBoundSql(boundSql).sql(buildSql);}return;}handlerLimit(page);// 获取方言IDialect dialect = findIDialect(executor);final Configuration configuration = ms.getConfiguration();// 根据方言拼接查询语句DialectModel model = dialect.buildPaginationSql(buildSql, page.offset(), page.getSize());PluginUtils.MPBoundSql mpBoundSql = PluginUtils.mpBoundSql(boundSql);List<ParameterMapping> mappings = mpBoundSql.parameterMappings();Map<String, Object> additionalParameter = mpBoundSql.additionalParameters();model.consumers(mappings, configuration, additionalParameter);mpBoundSql.sql(model.getDialectSql());mpBoundSql.parameterMappings(mappings);
}

在这里插入图片描述
实现类如下

public class OracleDialect implements IDialect {@Overridepublic 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();}
}

日志如下

2020-11-10 11:00:31.077  INFO 8820 --- [nio-8083-exec-1] c.e.durid.demo.config.DynamicDataSource  : 当前数据源为oracle
2020-11-10 11:00:31.080 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Bound value [org.springframework.jdbc.datasource.ConnectionHolder@68de9421] for key [com.example.durid.demo.config.DynamicDataSource@4844930a] to thread [http-nio-8083-exec-1]
2020-11-10 11:00:31.080 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Initializing transaction synchronization
2020-11-10 11:00:31.080 TRACE 8820 --- [nio-8083-exec-1] o.s.t.i.TransactionInterceptor           : Getting transaction for [com.example.durid.demo.service.impl.InstrumentServiceImpl.splitListByPlus]
2020-11-10 11:00:41.785 DEBUG 8820 --- [nio-8083-exec-1] org.mybatis.spring.SqlSessionUtils       : Creating a new SqlSession
2020-11-10 11:00:41.804 DEBUG 8820 --- [nio-8083-exec-1] org.mybatis.spring.SqlSessionUtils       : Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46b442d]
2020-11-10 11:00:41.805 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Bound value [org.mybatis.spring.SqlSessionHolder@3426ea2] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@1f71194d] to thread [http-nio-8083-exec-1]
2020-11-10 11:00:45.993 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@68de9421] for key [com.example.durid.demo.config.DynamicDataSource@4844930a] bound to thread [http-nio-8083-exec-1]
2020-11-10 11:00:45.993 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@68de9421] for key [com.example.durid.demo.config.DynamicDataSource@4844930a] bound to thread [http-nio-8083-exec-1]
2020-11-10 11:00:45.998 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@68de9421] for key [com.example.durid.demo.config.DynamicDataSource@4844930a] bound to thread [http-nio-8083-exec-1]
2020-11-10 11:00:45.999 DEBUG 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect_mpCount          : ==>  Preparing: SELECT COUNT(1) FROM TTRD_TEST_INSTRUMENT WHERE IS_NONSTD = ? 
2020-11-10 11:00:46.109 DEBUG 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect_mpCount          : ==> Parameters: 0(Integer)
2020-11-10 11:00:46.124 TRACE 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect_mpCount          : <==    Columns: COUNT(1)
2020-11-10 11:00:46.128 TRACE 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect_mpCount          : <==        Row: 10
2020-11-10 11:00:46.130 DEBUG 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect_mpCount          : <==      Total: 1
2020-11-10 11:03:37.614 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@68de9421] for key [com.example.durid.demo.config.DynamicDataSource@4844930a] bound to thread [http-nio-8083-exec-1]
2020-11-10 11:03:37.615 DEBUG 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect                  : ==>  Preparing: SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( SELECT I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID FROM TTRD_TEST_INSTRUMENT WHERE IS_NONSTD=? ORDER BY I_CODE,A_TYPE,M_TYPE ) TMP WHERE ROWNUM <=?) WHERE ROW_ID > ? 
2020-11-10 11:03:37.615 DEBUG 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect                  : ==> Parameters: 0(Integer), 5(Long), 0(Long)
2020-11-10 11:03:37.673 TRACE 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect                  : <==    Columns: I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID, ROW_ID
2020-11-10 11:03:37.673 TRACE 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect                  : <==        Row: 42dc0034-0ffe-4ef2-8edc-ae54a9595001, SPT_LBS, X_CNBD, 2020-08-03, 7D, 60245, 2, 0, 6024559232602456024560144602446, 1
2020-11-10 11:03:37.674 TRACE 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect                  : <==        Row: 8b124d35-7cce-4e91-8bbb-e2a0b8bcd203, SPT_LBS, X_CNBD, 2020-08-03, 7D, 60245, 2, 0, 6024559232602456024560144602446, 2
2020-11-10 11:03:37.675 TRACE 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect                  : <==        Row: CFTYTEST01, SPT_LBS, X_CNBD, 2020-08-03, 7, 59868, 1, 0, 6024559232602456024560144602446, 3
2020-11-10 11:03:37.676 TRACE 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect                  : <==        Row: SYXZGJH01, SPT_LBS, X_CNBD, 2020-08-03, 7D, 60245, 1, 0, 6024559232602456024560144602446, 4
2020-11-10 11:03:37.677 TRACE 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect                  : <==        Row: a7b29f59-b01f-4bf4-ad31-28dc4ca5150d, SPT_LBS, X_CNBD, 2020-08-03, 7D, 60245, 2, 0, 6024559232602456024560144602446, 5
2020-11-10 11:03:37.677 DEBUG 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect                  : <==      Total: 5
2020-11-10 11:03:52.080 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.mybatis.spring.SqlSessionHolder@3426ea2] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@1f71194d] bound to thread [http-nio-8083-exec-1]
2020-11-10 11:03:52.081 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.mybatis.spring.SqlSessionHolder@3426ea2] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@1f71194d] bound to thread [http-nio-8083-exec-1]
2020-11-10 11:03:52.081 DEBUG 8820 --- [nio-8083-exec-1] org.mybatis.spring.SqlSessionUtils       : Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46b442d]
2020-11-10 11:03:52.082 TRACE 8820 --- [nio-8083-exec-1] o.s.t.i.TransactionInterceptor           : Completing transaction for [com.example.durid.demo.service.impl.InstrumentServiceImpl.splitListByPlus]
2020-11-10 11:03:52.083 DEBUG 8820 --- [nio-8083-exec-1] org.mybatis.spring.SqlSessionUtils       : Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46b442d]
2020-11-10 11:03:52.091 DEBUG 8820 --- [nio-8083-exec-1] org.mybatis.spring.SqlSessionUtils       : Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46b442d]
2020-11-10 11:03:52.092 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Removed value [org.mybatis.spring.SqlSessionHolder@3426ea2] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@1f71194d] from thread [http-nio-8083-exec-1]
2020-11-10 11:03:52.092 DEBUG 8820 --- [nio-8083-exec-1] org.mybatis.spring.SqlSessionUtils       : Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46b442d]
2020-11-10 11:03:52.092 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@68de9421] for key [com.example.durid.demo.config.DynamicDataSource@4844930a] bound to thread [http-nio-8083-exec-1]
2020-11-10 11:03:52.095 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Clearing transaction synchronization
2020-11-10 11:03:52.095 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Removed value [org.springframework.jdbc.datasource.ConnectionHolder@68de9421] for key [com.example.durid.demo.config.DynamicDataSource@4844930a] from thread [http-nio-8083-exec-1]

总结

由于各种数据库厂商提供了不同的分页语句，为了解决这个问题，MyBatis提供了两种方案，通过RowBounds参数其实是一个假的分页，也就是逻辑分页，而不是物理分页，在数据量非常多的时候，不但导致了带宽损失、时效性问题，甚至大量占用内存导致宕机等。第二种方式是采用databaseId方式，这种方式可以解决问题，但是需要程序猿对各种分页语句非常的熟悉，而且在各种xml中充斥大量相同的代码。因此行业内出现了MyBatis-PageHelper和MyBatis-Plus这样的项目，通过拦截器对分页（根据数据库拼接分页查询语句）进行了解决，某种程度上来说，二者大同小异，都是针对Mybaits的拦截器的扩展，同时也考虑兼容RowBounds的模式。反过来，我们也不得不佩服MyBatis设计者的厉害之处，虽然自己没有解决分页的问题，但是为分页留下了扩展的口子，值得学习啊！