Mybatis自定义Explain插件
场景:
最近有个需求:新建一个表的联合索引,让对表的查询走到这个联合索引上
目的:
每次执行sql查询的时候,都能对每一条sql拦截,通过explain分析其性能。看其Extra属性是否为Using index、其key属性是否为新建的索引。
举例:
根据省份name,查province 。explain分析是否,使用了索引。以及走到了ProName索引上
EXPLAIN select distinct
ProID
,ProName
from province p
WHERE p.ProName = ?
实现
自定义impl类实现Interceptor接口
import com.baomidou.mybatisplus.core.exceptions.MybatisPlusException;
import org.apache.ibatis.builder.StaticSqlSource;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Component;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
@Component
@Intercepts({
@Signature(
type = Executor.class,
method = "query",
args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class
}
)
})
public class ExplainInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
MappedStatement ms = (MappedStatement) invocation.getArgs()[0];
if (ms.getSqlCommandType() == SqlCommandType.SELECT) {
Executor executor = (Executor) invocation.getTarget();
Configuration configuration = ms.getConfiguration();
Object parameter = invocation.getArgs()[1];
BoundSql boundSql = ms.getBoundSql(parameter);
Connection connection = executor.getTransaction().getConnection();
sqlExplain(configuration, ms, boundSql, connection, parameter);
}
Object result = invocation.proceed();//target对象应执行的方法
return result;
}
private void sqlExplain(Configuration configuration, MappedStatement mappedStatement, BoundSql boundSql, Connection connection, Object parameter) {
// 这里注意:EXPLAIN后面必须要有空格,否则sql为: explainselect报错
StringBuilder explain = new StringBuilder("EXPLAIN ");
String sqlExplain = explain.append(boundSql.getSql()).toString();
System.out.println("============================================");
System.out.println(sqlExplain);
System.out.println("============================================");
StaticSqlSource sqlSource = new StaticSqlSource(configuration, sqlExplain, boundSql.getParameterMappings());
MappedStatement.Builder builder = new MappedStatement.Builder(configuration, "explain_sql", sqlSource, SqlCommandType.SELECT);
MappedStatement queryStatement = builder.build();
builder.resultMaps(mappedStatement.getResultMaps()).resultSetType(mappedStatement.getResultSetType())
.statementType(mappedStatement.getStatementType());
DefaultParameterHandler handler = new DefaultParameterHandler(queryStatement, parameter, boundSql);
try {
PreparedStatement stmt = connection.prepareStatement(sqlExplain);
handler.setParameters(stmt);
ResultSet rs = stmt.executeQuery();
while (rs.next()){
String extra = rs.getString("Extra");
int index = extra.indexOf("Using index");
//判断,是否走了索引。还是走的Using where
if (index == -1){
throw new MybatisPlusException("Error:Full table operator is prohibited. SQL:"+boundSql.getSql());
}
//判断,是否走到索引idx_ProName上
if (!"idx_ProName".equals(rs.getString("key"))){
throw new MybatisPlusException("Error:idx is not used. SQL:"+boundSql.getSql());
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
Interceptor.super.setProperties(properties);
}
}
将自定义插件加入拦截器链
参考我另一篇:Mybatis源码中的5.10.4章节:自定义插件