使用 druid 连接池来优化分页语句
共 5927字,需浏览 12分钟
·
2020-11-06 09:14
一、前言
一个老系统随着数据量越来越大,我们察觉到部分分页语句拖慢了我们的速度。
鉴于老系统的使用方式,不打算使用pagehelper
和mybatis-plus
来处理,加上系统里使用的是druid
连接池,考虑直接使用druid
来优化。
二、老代码
老代码是使用的一个mybatis
插件进行的分页,分页的核心代码如下:
// 记录统计的 sql
String countSql = "select count(0) from (" + sql+ ") tmp_count";
PreparedStatement countStmt = connection.prepareStatement(countSql);
BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), parameterObject);
setParameters(countStmt, mappedStatement, countBS,parameterObject);
在原始的 sql 外面包装了一个 count sql,当然很多插件都是这样做的。
三、druid 的 PagerUtil
示例 sql(有比较复杂的坐标计算)
SELECT g.*
, ROUND(6378.138 * 2 * ASIN(SQRT(POW(SIN((? * PI() / 180 - t.latitude * PI() / 180) / 2), 2) + COS(? * PI() / 180) * COS(t.latitude * PI() / 180) * POW(SIN((? * PI() / 180 - t.longitude * PI() / 180) / 2), 2))), 2) AS distancecd
, t.agentname, t.agentlogo, t.compaddress
FROM t_bas_integral_goods g
LEFT JOIN t_bas_agent t ON g.agentid = t.AGENTID
WHERE t.AGENTTYPE = '2'
AND t.pass = '0'
AND t.dl_type = '4'
AND g.type = 0
ORDER BY distancecd ASC
使用 Druid 生成 count sql:
String countSql = PagerUtils.count(sql, DbType.mysql);
System.out.println(countSql);
输出:
SELECT COUNT(*)
FROM t_bas_integral_goods g
LEFT JOIN t_bas_agent t ON g.agentid = t.AGENTID
WHERE t.AGENTTYPE = '2'
AND t.pass = '0'
AND t.dl_type = '4'
AND g.type = 0
我们可以看到优化后的 count sql 变得十分简洁,坐标计算的都已经丢弃掉。注意:PagerUtil
还有limit
方法用来生成limit
语句,感兴趣的同学可以自行试验。
四、改造分页插件
4.1 踩坑之路
看到上面 druid PagerUtils count 的优化效果,立马开始改造起来,起初只改掉了countSql
,
String countSql = PagerUtils.count(sql, dbType);
PreparedStatement countStmt = connection.prepareStatement(countSql);
BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), parameterObject);
setParameters(countStmt, mappedStatement, countBS,parameterObject);
启动起来测试一番就发现报错了,因为原始 sql 中含有?
变量,优化后的 sql 已经没有变量了,插件还会继续给他设置变量。我们要怎么解决这个问题呢?
我们再回头看看pagehelper
和mybatis-plus
是怎么实现的!它俩都是基于jsqlparser
对 sql 进行解析,然后处理。
要多加一个jsqlparser
?没必要没必要,druid 的 sql 解析功能也是很强大的,我看了看PagerUtils.count
方法的源码,大不了用 druid 的 sql 解析实现一遍。
看了看源码之后我陷入了沉思,有必要搞这么复杂么?有没有更好的方法?我反复 debug 发现了,DynamicSqlSource
中有带#{xxx}
这样的原始 sql,
那么我是否可以使用 druid 先对这种 mybatis 占位符的 sql 进行优化呢?我们来试试:
示例 sql:
select * from xxx where type = #{type} order by xx
输出:
SELECT COUNT(*)
FROM xxx
WHERE type = #{type}
完美!!!
4.2 继续踩坑
然而直接在 Mapper 上注解的 sql 还是有问题,拿不到原始的 sql,debug 发现 RawSqlSource 在构造器里就将 sql 处理成了?
号挂参的形式。
@Select("select * from xxx where type = #{type} order by xx")
Object test(@Param("type") String type);
那么我只能看看能不能扩展它,我找到了它是在XMLLanguageDriver
里进行初始化,这下好办了,因为我之前扩展过XMLLanguageDriver
,它是可以自定义配置的。于是我重写了RawSqlSource
, 添加上了包含 mybatis 参数占位符(#{}
)的rawSql
字段。
/**
* 原始 sql,用于方便 druid 工具进行分页
*
* @author L.cm
*/
public class MicaRawSqlSource implements SqlSource {
private final String rawSql;
private final SqlSource sqlSource;
public MicaRawSqlSource(Configuration configuration, SqlNode rootSqlNode, Class> parameterType) {
this(configuration, getSql(configuration, rootSqlNode), parameterType);
}
public MicaRawSqlSource(Configuration configuration, String sql, Class> parameterType) {
SqlSourceBuilder sqlSourceParser = new SqlSourceBuilder(configuration);
Class> clazz = parameterType == null ? Object.class : parameterType;
this.rawSql = sql;
this.sqlSource = sqlSourceParser.parse(sql, clazz, new HashMap<>());
}
// ... ...
}
自此全部逻辑已经走通,我们再来看看我们的PagePlugin
核心代码:
// 进行分页
Configuration configuration = mappedStatement.getConfiguration();
SqlSourceBuilder sqlSourceParser = new SqlSourceBuilder(configuration);
Class> parameterType = parameterObject.getClass();
Connection connection = (Connection) invocation.getArgs()[0];
// 1. 对 sql 进行判断,如果没有 ? 号,则直接处理
String boundRawSql = boundSql.getSql();
if (boundRawSql.indexOf(CharPool.QUESTION_MARK) == -1) {
// 不包含 ? 号
String countSql = PagerUtils.count(boundRawSql, dbType);
SqlSource newSqlSource = sqlSourceParser.parse(countSql, parameterType, new HashMap<>());
BoundSql newBoundSql = newSqlSource.getBoundSql(parameterObject);
int count = getCount(connection, mappedStatement, parameterObject, newBoundSql);
StringBuilder sqlBuilder = new StringBuilder(boundRawSql);
Page page = getPageParam(parameterObject, sqlBuilder, count);
String pageSql = generatePageSql(sqlBuilder.toString(), dbType, page);
// 将分页sql语句反射回BoundSql.
setField(boundSql, "sql", pageSql);
return invocation.proceed();
}
// 2. 按 SqlSource 进行解析
SqlSource sqlSource = mappedStatement.getSqlSource();
// xml 中的动态 sql
int count;
if (sqlSource instanceof DynamicSqlSource) {
SqlNode rootSqlNode = PagePlugin.getField(sqlSource, "rootSqlNode");
DynamicContext context = new DynamicContext(configuration, parameterObject);
rootSqlNode.apply(context);
// 生成 count sql,带 #{xxx} 变量的 sql
String countSql = PagerUtils.count(context.getSql(), dbType);
SqlSource newSqlSource = sqlSourceParser.parse(countSql, parameterType, context.getBindings());
BoundSql newBoundSql = newSqlSource.getBoundSql(parameterObject);
count = getCount(connection, mappedStatement, parameterObject, newBoundSql);
} else if (sqlSource instanceof MicaRawSqlSource) {
String rawSql = ((MicaRawSqlSource) sqlSource).getRawSql();
DynamicContext context = new DynamicContext(configuration, parameterObject);
// 生成 count sql,带 #{xxx} 变量的 sql
String countSql = PagerUtils.count(rawSql, dbType);
SqlSource newSqlSource = sqlSourceParser.parse(countSql, parameterType, context.getBindings());
BoundSql newBoundSql = newSqlSource.getBoundSql(parameterObject);
count = getCount(connection, mappedStatement, parameterObject, newBoundSql);
} else {
throw new IllegalArgumentException("不支持的 sql 分页形式,请使用 xml 或者注解");
}
五、结论
整个老服务通过切换到 mica(深度定制)的微服务架构(演示环境仅仅在单服务低内存配置)之后速度提升效果明显,当然后面我们还会继续进行优化。
我是春哥(dreamlu),附 mica 开源版地址:https://gitee.com/596392912/mica ,欢迎 star 和一起交流。