<dependency>
<groupId>io.github.huht123.sql-analysis</groupId>
<artifactId>sql-analysis</artifactId>
<version>1.0</version>
</dependency>
<plugin interceptor="com.jd.sql.analysis.core.SqlAnalysisAspect" >
<property name="analysisSwitch" value="true"/>
</plugin>
注意：如果使用了多个mybatis组件，建议把该组件放在最前面，防止其它组件对mybatis相关对象进行二次包装，无法获取对应的数据
mybatis.configuration.interceptors=com.github.pagehelper.PageInterceptor,com.jd.rd.intl.mybatis.plugin.condition.ConditionAdderPlugin
public class MyBatisInterceptorConfiguration {
@Bean
public Interceptor getSqlAnalysisAspect() {
SqlAnalysisAspect plugin = new SqlAnalysisAspect();
Properties properties = new Properties();
properties.setProperty("analysisSwitch", "true");
plugin.setProperties(properties);
return plugin;
}
}
| 属性 | 用途 | 是否必填 | 默认值 | 备注 |
|---|---|---|---|---|
| analysisSwitch | 是否开启分析功能 | 是 | false | |
| onlyCheckOnce | 是否对一个sqlid只分析一次 | 非 | true | |
| checkInterval | 每个sqlid分析间隔 | 非 | 300000毫秒 | onlyCheckOnce 为false才生效 |
| exceptSqlIds | 需要过滤不分析的sqlid | 非 | ||
| sqlType | 分析的sql类型 | 非 | 默认select、update | 支持 |
| scoreRuleLoadClass | 评分规则加载器，用于扩展自定义规则 | 非 | ||
| outModel | 默认输出方式 | 非 | 默认值：LOG | 支持LOG、MQ两种方式 |
| outputClass | 评分结果输出类，用于扩展自定义结果输出方式 | 非 | ||
| sqlReplaceModelSwitch | sql替换模块是否开启 | 非 | 默认 false |
classpath路径下添加：sql-analysis-rule-config.properties
rule1.condition=param.type == \"ALL\"
rule1.priority=1
rule1.score=40
rule1.reason="不走索引"
rule1.suggestion="建议创建索引"
rule1.strict=true
rule2.condition=param.extra contains(\"filesort\")
rule2.priority=2
rule2.score=20
rule2.reason="排序不走索引"
rule2.suggestion="建议优化索引或者优化sql"
rule2.strict=true
rule3.condition=param.filtered < 60
rule3.priority=3
rule3.score=20
rule3.reason="索引过滤效果不佳"
rule3.suggestion="建议优化索引或者优化sql"
rule3.strict=false
rule4.condition=param.rows > 50000
rule4.priority=4
rule4.score=40
rule4.reason="遍历行数过多"
rule4.suggestion="建议优化索引或者优化sql"
rule4.strict=false
rule5.condition=param.rows > 5000
rule5.priority=5
rule5.score=10
rule5.reason="遍历行数过多"
rule5.suggestion="建议优化索引或者优化sql"
rule5.strict=false
#测试模拟，临时改为1
rule6.condition=param.rows < 1
rule6.priority=6
rule6.score=-20
rule6.reason="遍历行数较少"
rule6.suggestion="暂不需要优化"
rule6.strict=false