前言
接口响应超时排查问题耗时久？
数据库连接池调优无从下手？
初中级开发SQL质量无法把控？
...
如果你有遇到过类似与SQL相关的问题，不妨花三五分钟看下本篇文章
演示页面：www.zzusp.asia:9080/d/nus8MIfSz…
监控项目：www.zzusp.asia
代码仓库（gitee）：gitee.com/peng6008/sl…
代码仓库（github）：github.com/zzusp/slow-…
📈效果演示
不多废话，直接上图
主要监控信息：
- 执行时长
- SQL语句
- SQL参数
- 结果集行数
- 结果集大小
- 数据源连接池（活跃线程数、空闲线程数、等待线程数、最大线程数）
- 等等
依据上面监控信息，可以直观的判断出SQL相关的性能问题（如：连接池线程不够用、SQL执行时间过长、结果集过大等问题），快速定位问题原因及优化方向
☝️使用说明
@SpringBootApplication
// 开启慢SQL监控
@EnableSlowSqlMonitor
public class SpringbootApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootApplication.class, args);
}
}
application.yml
slow-sql-monitor:
slow-millis: 1000 # 慢SQL的时长，单位毫秒
log-slow-sql: true # 是否开启慢SQL日志打印
elasticsearch:
enabled: false # 是否开启elasticsearch对接
uris:
username:
password:
sendMillis:
pom.xml
<dependencys>
<dependency>
<groupId>io.github.zzusp</groupId>
<artifactId>slow-sql-monitor-spring-boot-autoconfigure</artifactId>
<version>1.0.2</version>
</dependency>
<!-- 如果项目中有使用druid就引入，没有则可不引入 -->
<dependency>
<groupId>io.github.zzusp</groupId>
<artifactId>slow-sql-monitor-pool-druid</artifactId>
<version>1.0.2</version>
</dependency>
<!-- 如果项目中有使用hikari就引入，没有则可不引入 -->
<dependency>
<groupId>io.github.zzusp</groupId>
<artifactId>slow-sql-monitor-pool-hikari</artifactId>
<version>1.0.2</version>
</dependency>
</dependencys>
自定义拦截器（将拦截器注册为Bean对象，即可自动加载）
import org.springframework.stereotype.Component;
import com.slowsql.plugin.Interceptor;
import com.slowsql.stat.SlowSqlStat;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
@Component
public class CustomInterceptor implements Interceptor {
private final static Logger logger = LoggerFactory.getLogger(CustomInterceptor.class);
public CustomInterceptor() {
}
@Override
public void beforeExecute(SlowSqlStat slowSqlStat) {
// 在SQL执行之前，执行一些操作。为避免影响性能，请务必异步处理！！！
}
@Override
public void afterExecute(SlowSqlStat slowSqlStat) {
// 在SQL执行之后，执行一些操作。为避免影响性能，请务必异步处理！！！
}
@Override
public void closeExecute(SlowSqlStat slowSqlStat) {
// 在SQL执行结束，执行一些操作。为避免影响性能，请务必异步处理！！！
if (slowSqlStat.isSlowSql()) {
// 如果为慢SQL，执行一些操作。为避免影响性能，请务必异步处理！！！
}
}
}
🚿接入Elasticsearch
// 视情况关闭SpringBoot默认的Elasticsearch自动配置
@SpringBootApplication(exclude = {ElasticsearchRestClientAutoConfiguration.class})
// 开启慢SQL监控
@EnableSlowSqlMonitor
public class SpringbootApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootApplication.class, args);
}
}
application.yml
slow-sql-monitor:
slow-millis: 1000 # 慢SQL的时长，单位毫秒
log-slow-sql: true # 是否开启慢SQL日志打印
elasticsearch:
enabled: false # 是否开启elasticsearch对接
uris: https://127.0.0.1:9200
username: elastic
password: 123456
sendMillis: 0 # SQL执行时长大于等于多少毫秒时发送到elasticsearch。默认0，所有SQL都发送
pom.xml
<dependencys>
<dependency>
<groupId>io.github.zzusp</groupId>
<artifactId>slow-sql-monitor-spring-boot-autoconfigure</artifactId>
<version>1.0.2</version>
</dependency>
<!-- 如果项目中有使用druid就引入，没有则可不引入 -->
<dependency>
<groupId>io.github.zzusp</groupId>
<artifactId>slow-sql-monitor-pool-druid</artifactId>
<version>1.0.2</version>
</dependency>
<!-- 如果项目中有使用hikari就引入，没有则可不引入 -->
<dependency>
<groupId>io.github.zzusp</groupId>
<artifactId>slow-sql-monitor-pool-hikari</artifactId>
<version>1.0.2</version>
</dependency>
<dependency>
<groupId>io.github.zzusp</groupId>
<artifactId>slow-sql-monitor-spring-boot-elasticsearch</artifactId>
<version>1.0.2</version>
</dependency>
<dependency>
<groupId>org.elasticsearch.client</groupId>
<artifactId>elasticsearch-rest-client</artifactId>
<version>7.17.15</version>
</dependency>
<dependency>
<groupId>jakarta.json</groupId>
<artifactId>jakarta.json-api</artifactId>
<version>2.0.1</version>
</dependency>
</dependencys>
💻Grafana配置
Elasticsearch数据源配置
SQL Dashboard文件
展开查看代码
{
"annotations": {
"list": [
{
"builtIn": 1,
"datasource": {
"type": "grafana",
"uid": "-- Grafana --"
},
"enable": true,
"hide": true,
"iconColor": "rgba(0, 211, 255, 1)",
"name": "Annotations & Alerts",
"target": {
"limit": 100,
"matchAny": false,
"tags": [],
"type": "dashboard"
},
"type": "dashboard"
}
]
},
"editable": true,
"fiscalYearStartMonth": 0,
"graphTooltip": 0,
"id": 2,
"links": [],
"liveNow": false,
"panels": [
{
"datasource": {
"type": "elasticsearch",
"uid": "MrTE92fIk"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 7,
"w": 4,
"x": 0,
"y": 0
},
"id": 12,
"options": {
"colorMode": "value",
"graphMode": "area",
"justifyMode": "auto",
"orientation": "auto",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"textMode": "auto"
},
"pluginVersion": "8.5.4",
"targets": [
{
"alias": "",
"bucketAggs": [
{
"field": "slowSql",
"id": "2",
"settings": {
"min_doc_count": "1",
"order": "desc",
"orderBy": "_term",
"size": "0"
},
"type": "terms"
}
],
"datasource": {
"type": "elasticsearch",
"uid": "MrTE92fIk"
},
"hide": false,
"metrics": [
{
"id": "1",
"type": "count"
}
],
"query": "slowSql: true",
"refId": "A",
"timeField": "startTime"
}
],
"title": "慢SQL总数",
"transformations": [
{
"id": "organize",
"options": {
"excludeByName": {
"slowSql": true,
"startTime": true
},
"indexByName": {},
"renameByName": {}
}
}
],
"type": "stat"
},
{
"datasource": {
"type": "elasticsearch",
"uid": "MrTE92fIk"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"custom": {
"align": "auto",
"displayMode": "auto",
"filterable": false,
"inspect": false
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "duration"
},
"properties": [
{
"id": "unit",
"value": "ms"
},
{
"id": "custom.width",
"value": 93
}
]
},
{
"matcher": {
"id": "byName",
"options": "resultSize"
},
"properties": [
{
"id": "custom.inspect",
"value": false
},
{
"id": "unit",
"value": "bytes"
},
{
"id": "custom.width",
"value": 97
}
]
},
{
"matcher": {
"id": "byName",
"options": "activeCount"
},
"properties": [
{
"id": "custom.width",
"value": 103
}
]
},
{
"matcher": {
"id": "byName",
"options": "startTime"
},
"properties": [
{
"id": "custom.width",
"value": 189
}
]
},
{
"matcher": {
"id": "byName",
"options": "fetchRowCount"
},
"properties": [
{
"id": "custom.width",
"value": 123
}
]
},
{
"matcher": {
"id": "byName",
"options": "idleCount"
},
"properties": [
{
"id": "custom.width",
"value": 83
}
]
},
{
"matcher": {
"id": "byName",
"options": "maxPoolSize"
},
"properties": [
{
"id": "custom.width",
"value": 103
}
]
},
{
"matcher": {
"id": "byName",
"options": "params"
},
"properties": [
{
"id": "custom.width",
"value": 127
}
]
},
{
"matcher": {
"id": "byName",
"options": "slowSql"
},
"properties": [
{
"id": "custom.width",
"value": 73
}
]
},
{
"matcher": {
"id": "byName",
"options": "结果集行数"
},
"properties": [
{
"id": "custom.width",
"value": 94
}
]
},
{
"matcher": {
"id": "byName",
"options": "时长"
},
"properties": [
{
"id": "custom.width",
"value": 95
}
]
},
{
"matcher": {
"id": "byName",
"options": "空闲线程数"
},
"properties": [
{
"id": "custom.width",
"value": 98
}
]
},
{
"matcher": {
"id": "byName",
"options": "等待线程数"
},
"properties": [
{
"id": "custom.width",
"value": 112
}
]
}
]
},
"gridPos": {
"h": 7,
"w": 20,
"x": 4,
"y": 0
},
"id": 10,
"options": {
"footer": {
"enablePagination": false,
"fields": "",
"reducer": [
"sum"
],
"show": false
},
"showHeader": true,
"sortBy": [
{
"desc": true,
"displayName": "时间"
}
]
},
"pluginVersion": "8.5.4",
"targets": [
{
"alias": "",
"bucketAggs": [
{
"field": "startTime",
"id": "2",
"settings": {
"interval": "auto"
},
"type": "date_histogram"
}
],
"datasource": {
"type": "elasticsearch",
"uid": "MrTE92fIk"
},
"metrics": [
{
"id": "1",
"type": "logs"
}
],
"query": "",
"refId": "A",
"timeField": "startTime"
}
],
"title": "SQL Record",
"transformations": [
{
"id": "sortBy",
"options": {
"fields": {},
"sort": [
{
"desc": true,
"field": "startTime"
}
]
}
},
{
"id": "organize",
"options": {
"excludeByName": {
"_id": true,
"_index": true,
"_source": true,
"_type": true,
"highlight": true,
"slowSql": false,
"sort": true
},
"indexByName": {
"_id": 11,
"_index": 12,
"_source": 13,
"_type": 14,
"activeCount": 6,
"duration": 2,
"fetchRowCount": 4,
"highlight": 15,
"idleCount": 7,
"maxPoolSize": 9,
"params": 5,
"resultSize": 3,
"slowSql": 10,
"sort": 16,
"sql": 1,
"startTime": 0,
"waitCount": 8
},
"renameByName": {
"_id": "",
"activeCount": "活跃线程数",
"duration": "时长",
"fetchRowCount": "结果集行数",
"idleCount": "空闲线程数",
"maxPoolSize": "最大线程数",
"params": "参数",
"resultSize": "结果集大小",
"slowSql": "",
"sql": "SQL",
"startTime": "时间",
"waitCount": "等待线程数"
}
}
}
],
"type": "table"
},
{
"datasource": {
"type": "elasticsearch",
"uid": "MrTE92fIk"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisLabel": "",
"axisPlacement": "auto",
"barAlignment": 0,
"drawStyle": "line",
"fillOpacity": 0,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineInterpolation": "linear",
"lineWidth": 1,
"pointSize": 5,
"scaleDistribution": {
"type": "linear"
},
"showPoints": "auto",
"spanNulls": false,
"stacking": {
"group": "A",
"mode": "none"
},
"thresholdsStyle": {
"mode": "off"
}
},
"displayName": "执行时长",
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
},
"unit": "ms"
},
"overrides": [
{
"__systemRef": "hideSeriesFrom",
"matcher": {
"id": "byNames",
"options": {
"mode": "exclude",
"names": [
"duration"
],
"prefix": "All except:",
"readOnly": true
}
},
"properties": [
{
"id": "custom.hideFrom",
"value": {
"legend": false,
"tooltip": false,
"viz": true
}
}
]
}
]
},
"gridPos": {
"h": 8,
"w": 12,
"x": 0,
"y": 7
},
"id": 2,
"options": {
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom"
},
"tooltip": {
"mode": "single",
"sort": "none"
}
},
"targets": [
{
"alias": "",
"bucketAggs": [
{
"field": "startTime",
"id": "2",
"settings": {
"interval": "auto"
},
"type": "date_histogram"
}
],
"datasource": {
"type": "elasticsearch",
"uid": "MrTE92fIk"
},
"metrics": [
{
"id": "1",
"type": "logs"
}
],
"query": "",
"refId": "A",
"timeField": "startTime"
}
],
"title": "执行时长",
"transformations": [
{
"id": "filterFieldsByName",
"options": {
"include": {
"names": [
"startTime",
"duration"
]
}
}
}
],
"type": "timeseries"
},
{
"datasource": {
"type": "elasticsearch",
"uid": "MrTE92fIk"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisLabel": "",
"axisPlacement": "auto",
"barAlignment": 0,
"drawStyle": "line",
"fillOpacity": 0,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineInterpolation": "linear",
"lineWidth": 1,
"pointSize": 5,
"scaleDistribution": {
"type": "linear"
},
"showPoints": "auto",
"spanNulls": false,
"stacking": {
"group": "A",
"mode": "none"
},
"thresholdsStyle": {
"mode": "off"
}
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 8,
"w": 12,
"x": 12,
"y": 7
},
"id": 4,
"options": {
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom"
},
"tooltip": {
"mode": "single",
"sort": "none"
}
},
"targets": [
{
"alias": "",
"bucketAggs": [
{
"field": "startTime",
"id": "2",
"settings": {
"interval": "auto"
},
"type": "date_histogram"
}
],
"datasource": {
"type": "elasticsearch",
"uid": "MrTE92fIk"
},
"metrics": [
{
"id": "1",
"type": "logs"
}
],
"query": "",
"refId": "A",
"timeField": "startTime"
}
],
"title": "数据源连接池",
"transformations": [
{
"id": "filterFieldsByName",
"options": {
"include": {
"names": [
"startTime",
"activeCount",
"maxPoolSize",
"waitCount",
"idleCount"
]
}
}
}
],
"type": "timeseries"
},
{
"datasource": {
"type": "elasticsearch",
"uid": "MrTE92fIk"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisLabel": "",
"axisPlacement": "auto",
"barAlignment": 0,
"drawStyle": "line",
"fillOpacity": 0,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineInterpolation": "linear",
"lineWidth": 1,
"pointSize": 5,
"scaleDistribution": {
"type": "linear"
},
"showPoints": "auto",
"spanNulls": false,
"stacking": {
"group": "A",
"mode": "none"
},
"thresholdsStyle": {
"mode": "off"
}
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "fetchRowCount"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "blue",
"mode": "fixed"
}
}
]
}
]
},
"gridPos": {
"h": 8,
"w": 12,
"x": 0,
"y": 15
},
"id": 6,
"options": {
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom"
},
"tooltip": {
"mode": "single",
"sort": "none"
}
},
"targets": [
{
"alias": "",
"bucketAggs": [
{
"field": "startTime",
"id": "2",
"settings": {
"interval": "auto"
},
"type": "date_histogram"
}
],
"datasource": {
"type": "elasticsearch",
"uid": "MrTE92fIk"
},
"metrics": [
{
"id": "1",
"type": "logs"
}
],
"query": "",
"refId": "A",
"timeField": "startTime"
}
],
"title": "结果集行数",
"transformations": [
{
"id": "filterFieldsByName",
"options": {
"include": {
"names": [
"startTime",
"fetchRowCount"
]
}
}
}
],
"type": "timeseries"
},
{
"datasource": {
"type": "elasticsearch",
"uid": "MrTE92fIk"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisLabel": "",
"axisPlacement": "auto",
"barAlignment": 0,
"drawStyle": "line",
"fillOpacity": 0,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineInterpolation": "linear",
"lineWidth": 1,
"pointSize": 5,
"scaleDistribution": {
"type": "linear"
},
"showPoints": "auto",
"spanNulls": false,
"stacking": {
"group": "A",
"mode": "none"
},
"thresholdsStyle": {
"mode": "off"
}
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
},
"unit": "bytes"
},
"overrides": []
},
"gridPos": {
"h": 8,
"w": 12,
"x": 12,
"y": 15
},
"id": 8,
"options": {
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom"
},
"tooltip": {
"mode": "single",
"sort": "none"
}
},
"targets": [
{
"alias": "",
"bucketAggs": [
{
"field": "startTime",
"id": "2",
"settings": {
"interval": "auto"
},
"type": "date_histogram"
}
],
"datasource": {
"type": "elasticsearch",
"uid": "MrTE92fIk"
},
"metrics": [
{
"id": "1",
"type": "logs"
}
],
"query": "",
"refId": "A",
"timeField": "startTime"
}
],
"title": "结果集大小",
"transformations": [
{
"id": "filterFieldsByName",
"options": {
"include": {
"names": [
"startTime",
"resultSize"
]
}
}
}
],
"type": "timeseries"
}
],
"refresh": "",
"schemaVersion": 36,
"style": "dark",
"tags": [],
"templating": {
"list": []
},
"time": {
"from": "now-1h",
"to": "now"
},
"timepicker": {},
"timezone": "",
"title": "SQL Dashboard",
"uid": "nus8MIfSz",
"version": 8,
"weekStart": ""
}
💌最后
有问题或者想法的话，欢迎大家在评论区或者issue沟通📞
希望大家能点个star支持一下，灰常感谢💪
