0%

SpringBoot-Mybatis-Explain

日常开发中,需要对sql进行。为了提高效率,曾经使用mybatis扩展输出查询计划。

慢SQL

从编码角度来优化数据层的话,我首先会去查一下项目中运行的sql语句,定位到瓶颈是否出现在这里,首先去优化sql语句,而慢sql就是其中的主要优化对象,对于慢sql,顾名思义就是花费较多执行时间的语句,它带来的影响也比较恶劣,首先是执行时间过长影响数据的返回速度,其次,慢sql的长时间执行也会消耗和占用mysql的系统资源,影响其他的sql语句执行,过多的慢sql极其影响性能,如果系统流量或者并发量较大的情况下,过多的执行慢sql很有可能造成mysql的死锁以致于mysql服务无法正常使用。

Explain

explain关键字一般放在SELECT查询语句的前面,用于描述MySQL如何执行查询操作、以及MySQL成功返回结果集需要执行的行数。explain 可以帮助我们分析 select 语句,让我们知道查询效率低下的原因,从而改进我们查询,让查询优化器能够更好的工作。

演示效果

1
2
3
4
➜  blog curl http://127.0.0.1:9023/mybatis/   # 新增测试数据
{"code":"200","data":{"id":3,"isEnable":true,"createTime":1643953933094,"updateTime":1643953933094,"phoneNumber":"13611707472","email":"chu@gmail.com","saltPassword":"Xe8mbdpS","salt":null,"usrName":"析丹楚"}}%
➜ blog curl http://127.0.0.1:9023/mybatis/list # 查询测试数据
{"code":"200","data":[{"id":1,"isEnable":true,"createTime":1643953924990,"updateTime":1643953924990,"phoneNumber":"13876877231","email":"wu@gmail.com","saltPassword":"rd7NHZlt","salt":"","usrName":"国富吴"},{"id":2,"isEnable":true,"createTime":1643953925710,"updateTime":1643953925710,"phoneNumber":"13971918631","email":"guohui.yang@qq.com","saltPassword":"ritHRn5L","salt":"","usrName":"国辉杨"},{"id":3,"isEnable":true,"createTime":1643953933094,"updateTime":1643953933094,"phoneNumber":"13611707472","email":"chu@gmail.com","saltPassword":"Xe8mbdpS","salt":"","usrName":"析丹楚"}]}%
  • Console
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[XNIO-1 task-1] [BaseJdbcLogger.java : 137] ==>  Preparing: SELECT id,is_enable,create_time,update_time,phone_number,email,salt_password,salt,usr_name FROM account
[XNIO-1 task-1] [BaseJdbcLogger.java : 137] ==> Parameters:
[XNIO-1 task-1] [BaseJdbcLogger.java : 137] <== Total: 2
[XNIO-1 task-1] [MybatisInterceptor.java : 80] [{"id":"1","selectType":"SIMPLE","table":"account","type":"ALL","rows":"2","filtered":"100.0"}]
[XNIO-1 task-1] [MybatisInterceptor.java : 82] SQL RunTime 15 ms
[XNIO-1 task-1] [BaseJdbcLogger.java : 137] ==> Preparing: INSERT INTO account ( is_enable, create_time, update_time, phone_number, email, salt_password, usr_name ) VALUES ( ?, ?, ?, ?, ?, ?, ? )
[XNIO-1 task-1] [BaseJdbcLogger.java : 137] ==> Parameters: true(Boolean), 1643953933094(Long), 1643953933094(Long), 13611707472(String), chu@gmail.com(String), Xe8mbdpS(String), 析丹楚(String)
[XNIO-1 task-1] [BaseJdbcLogger.java : 137] <== Updates: 1
[XNIO-1 task-1] [MybatisInterceptor.java : 54] EXPLAIN
SELECT
id,
is_enable,
create_time,
update_time,
phone_number,
email,
salt_password,
salt,
usr_name
FROM
account
[XNIO-1 task-1] [BaseJdbcLogger.java : 137] ==> Preparing: SELECT id,is_enable,create_time,update_time,phone_number,email,salt_password,salt,usr_name FROM account
[XNIO-1 task-1] [BaseJdbcLogger.java : 137] ==> Parameters:
[XNIO-1 task-1] [BaseJdbcLogger.java : 137] <== Total: 3
[XNIO-1 task-1] [MybatisInterceptor.java : 80] [{"id":"1","selectType":"SIMPLE","table":"account","type":"ALL","rows":"3","filtered":"100.0"}]
[XNIO-1 task-1] [MybatisInterceptor.java : 82] SQL RunTime 8 ms

演示代码

MybatisInterceptor

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
/**
* @author z201.coding@gmail.com
**/
@Slf4j
@Intercepts({
@Signature(type = Executor.class, method = "query",
args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
@Signature(type = Executor.class, method = "query",
args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class})
})
public class MybatisInterceptor implements Interceptor {

@Override
public Object intercept(Invocation invocation) throws Throwable {
Long startTime = Clock.systemDefaultZone().millis();
List<ExplainResultDto> explainResultList = new ArrayList<>();
try {
final Object[] args = invocation.getArgs();
final MappedStatement mappedStatement = (MappedStatement) args[0];
final Executor executor = (Executor) invocation.getTarget();
Object entity = args[1];
if (SqlCommandType.SELECT.name().equalsIgnoreCase(mappedStatement.getSqlCommandType().name())) {
BoundSql boundSql;
if (args.length == 4) {
boundSql = mappedStatement.getBoundSql(entity);
} else {
// 使用Executor的代理对象调用query[args[5]]
boundSql = (BoundSql) args[5];
}
String sql = getSql(boundSql, mappedStatement);
log.info("EXPLAIN \n {}", SqlUtil.formatSql(sql));
Statement stmt = executor.getTransaction().getConnection().createStatement();
stmt.execute("EXPLAIN " + sql + " ;");
ResultSet rs = stmt.getResultSet();
ExplainResultDto explainResultVo = null;
while (rs.next()) {
explainResultVo = new ExplainResultDto();
explainResultVo.setId(rs.getString("id"));
explainResultVo.setSelectType(rs.getString("select_type"));
explainResultVo.setTable(rs.getString("table"));
explainResultVo.setPartitions(rs.getString("partitions"));
explainResultVo.setType(rs.getString("type"));
explainResultVo.setPossibleKeys(rs.getString("possible_keys"));
explainResultVo.setKey(rs.getString("key"));
explainResultVo.setKeyLen(rs.getString("key_len"));
explainResultVo.setRef(rs.getString("ref"));
explainResultVo.setRows(rs.getString("rows"));
explainResultVo.setFiltered(rs.getString("filtered"));
explainResultVo.setExtra(rs.getString("Extra"));
explainResultList.add(explainResultVo);
}
}
return invocation.proceed();
} finally {
Long timeConsuming = Clock.systemDefaultZone().millis() - startTime;
if (!CollectionUtils.isEmpty(explainResultList)) {
log.info("{}", JsonTool.toString(explainResultList));
}
log.info("SQL RunTime {} ms", timeConsuming);
}
}


/**
* 生成要执行的SQL命令
*
* @param boundSql
* @param ms
* @return
*/
private String getSql(BoundSql boundSql, MappedStatement ms) {
String sql = boundSql.getSql();
Object parameterObject = boundSql.getParameterObject();
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
if (!CollectionUtils.isEmpty(parameterMappings) && parameterObject != null) {
for (int i = 0; i < parameterMappings.size(); i++) {
ParameterMapping parameterMapping = parameterMappings.get(i);
if (parameterMapping.getMode() != ParameterMode.OUT) {
//参数值
Object value;
//获取参数名称
String propertyName = parameterMapping.getProperty();
if (boundSql.hasAdditionalParameter(propertyName)) {
//获取参数值
value = boundSql.getAdditionalParameter(propertyName);
} else if (ms.getConfiguration().getTypeHandlerRegistry().hasTypeHandler(parameterObject.getClass())) {
//如果是单个值则直接赋值
value = parameterObject;
} else {
MetaObject metaObject = ms.getConfiguration().newMetaObject(parameterObject);
value = metaObject.getValue(propertyName);
}
sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameter(value)));
}
}

}
return sql;
}

public String getParameter(Object parameter) {
if (parameter instanceof String) {
return "'" + parameter + "'";
}
return parameter.toString();
}


}

启用插件

1
2
3
4
5
6
7
8
9

// 可以直接在插件上增加Component 也可以在此处声明
@Bean
public String localInterceptor(SqlSessionFactory sqlSessionFactory) {
//实例化插件
MybatisInterceptor sqlInterceptor = new MybatisInterceptor();
sqlSessionFactory.getConfiguration().addInterceptor(sqlInterceptor);
return "interceptor";
}

END