该方案实现使用ZipKin实现MySQL查询监控,记录慢查询sql,方便后续对查询进行优化
使用的依赖
<dependency>
<groupId>io.zipkin.brave</groupId>
<artifactId>brave-instrumentation-mysql</artifactId>
<version>5.1.3</version>
</dependency>
MySQL连接信息修改
spring:
application:
datasource:
url: jdbc:mysql://localhost:3306/test01?serverTimezone=GMT%2B8&statementInterceptors=com.shengsheng.interceptor.ZipkinMysqlStatementInterceptor
username: root
password: 123456
server:
port: 8088
在url后面添加了&statementInterceptors=com.shengsheng.interceptor.ZipkinMysqlStatementInterceptor用于监控SQL执行
核心代码com.shengsheng.interceptor.ZipkinMysqlStatementInterceptor实现
package com.shengsheng.interceptor;
import brave.mysql.TracingStatementInterceptor;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSetInternalMethods;
import com.mysql.jdbc.Statement;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;
import java.sql.SQLException;
/**
* description:使用zipkin实现mysql执行跟踪
*
* @author shengshenglalala
* @date 2021/2/1 17:18
*/
public class ZipkinMysqlStatementInterceptor extends TracingStatementInterceptor {
protected final Logger logger = LoggerFactory.getLogger(getClass());
/**
* 拦截sql的时间,超过该时间的SQL即进行预警等操作 单位 毫秒
*/
public static final Integer MAX_SQL_EXECUTE_TIME = 1;
/**
* 执行sql的开始时间
*/
private final ThreadLocal<Long> startTimestamp = new ThreadLocal<>();
@Override
public ResultSetInternalMethods preProcess(String sql, Statement interceptedStatement, Connection connection) {
startTimestamp.set(System.currentTimeMillis());
return super.preProcess(sql, interceptedStatement, connection);
}
@Override
public ResultSetInternalMethods postProcess(String sql, Statement interceptedStatement,
ResultSetInternalMethods originalResultSet, Connection connection,
int warningCount, boolean noIndexUsed,
boolean noGoodIndexUsed, SQLException statementException) {
//收集慢SQL
Long startTime = startTimestamp.get();
startTimestamp.remove();
if (startTime != null && check(interceptedStatement)) {
//SQL执行总时长
long time = System.currentTimeMillis() - startTime;
if (time >= MAX_SQL_EXECUTE_TIME) {
//TODO 此处可以针对慢SQL进行报警等操作
logger.info("总耗时:{}毫秒,执行的SQL:{}",time, getSqlFromStatement(interceptedStatement));
}
}
return super.postProcess(sql, interceptedStatement, originalResultSet, connection, warningCount, noIndexUsed,
noGoodIndexUsed, statementException);
}
/**
* 从Statement中获取完整的sql语句
* @param statement 执行的Statement
* @return 具体的sql
*/
private static String getSqlFromStatement(Statement statement) {
if (statement == null) {
return "";
}
//sql = com.mysql.jdbc.JDBC42PreparedStatement@20f92c59: SELECT id,name FROM user
String sql = statement.toString();
if (!StringUtils.isEmpty(sql) && sql.startsWith("com")) {
//截取空格之后的字符串 即 select * from ....
sql = sql.substring(sql.indexOf(" "));
}
return sql;
}
/**
* 校验sql是否是select update delete语句
* @param statement
* @return
*/
private static boolean check(Statement statement) {
if (statement == null) {
return false;
}
String sql = statement.toString();
if (!StringUtils.isEmpty(sql) && sql.length() > 30) {
sql = sql.toLowerCase();
return (sql.contains("select")
|| sql.contains("update")
|| sql.contains("delete"));
}
return false;
}
}
执行的输出结果
具体的实现逻辑
UserController
package com.shengsheng.controller;
import com.shengsheng.service.UserService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
/**
* description:
*
* @author shengshenglalala
* @date 2021/2/1 17:15
*/
@RestController
@RequestMapping("/user")
public class UserController {
private final Logger logger = LoggerFactory.getLogger(getClass());
@Autowired
private UserService userService;
@PostMapping(value ={ "/getList" })
public String getList(){
try {
return userService.getList();
} catch (Exception e) {
logger.error("UserController_getList_error", e);
return "failure";
}
}
}
UserService
package com.shengsheng.service;
import com.alibaba.fastjson.JSON;
import com.shengsheng.domain.User;
import com.shengsheng.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* description:
*
* @author shengshenglalala
* @date 2021/2/1 17:15
*/
@Service
public class UserService {
@Autowired
private UserMapper userMapper;
public String getList() {
List<User> users = userMapper.selectAll();
return JSON.toJSONString(users);
}
}
UserMapper
package com.shengsheng.mapper;
import com.shengsheng.domain.User;
import tk.mybatis.mapper.common.Mapper;
/**
* description:
*
* @author shengshenglalala
* @date 2021/2/1 17:15
*/
public interface UserMapper extends Mapper<User>{
}
User
package com.shengsheng.domain;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;
/**
* description:
*
* @author shengshenglalala
* @date 2021/2/1 17:14
*/
@Table(name="user")
public class User implements Serializable {
@Id
@GeneratedValue(strategy= GenerationType.IDENTITY)
private Long id;
private String name;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
ZipkinMysqlStatementInterceptor
package com.shengsheng.interceptor;
import brave.mysql.TracingStatementInterceptor;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSetInternalMethods;
import com.mysql.jdbc.Statement;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;
import java.sql.SQLException;
/**
* description:使用zipkin实现mysql执行跟踪
*
* @author shengshenglalala
* @date 2021/2/1 17:18
*/
public class ZipkinMysqlStatementInterceptor extends TracingStatementInterceptor {
protected final Logger logger = LoggerFactory.getLogger(getClass());
/**
* 拦截sql的时间,超过该时间的SQL即进行预警等操作 单位 毫秒
*/
public static final Integer MAX_SQL_EXECUTE_TIME = 1;
/**
* 执行sql的开始时间
*/
private final ThreadLocal<Long> startTimestamp = new ThreadLocal<>();
@Override
public ResultSetInternalMethods preProcess(String sql, Statement interceptedStatement, Connection connection) {
startTimestamp.set(System.currentTimeMillis());
return super.preProcess(sql, interceptedStatement, connection);
}
@Override
public ResultSetInternalMethods postProcess(String sql, Statement interceptedStatement,
ResultSetInternalMethods originalResultSet, Connection connection,
int warningCount, boolean noIndexUsed,
boolean noGoodIndexUsed, SQLException statementException) {
//收集慢SQL
Long startTime = startTimestamp.get();
startTimestamp.remove();
if (startTime != null && check(interceptedStatement)) {
//SQL执行总时长
long time = System.currentTimeMillis() - startTime;
if (time >= MAX_SQL_EXECUTE_TIME) {
//TODO 此处可以针对慢SQL进行报警等操作
logger.info("总耗时:{}毫秒,执行的SQL:{}",time, getSqlFromStatement(interceptedStatement));
}
}
return super.postProcess(sql, interceptedStatement, originalResultSet, connection, warningCount, noIndexUsed,
noGoodIndexUsed, statementException);
}
/**
* 从Statement中获取完整的sql语句
* @param statement 执行的Statement
* @return 具体的sql
*/
private static String getSqlFromStatement(Statement statement) {
if (statement == null) {
return "";
}
//sql = com.mysql.jdbc.JDBC42PreparedStatement@20f92c59: SELECT id,name FROM user
String sql = statement.toString();
if (!StringUtils.isEmpty(sql) && sql.startsWith("com")) {
//截取空格之后的字符串 即 select * from ....
sql = sql.substring(sql.indexOf(" "));
}
return sql;
}
/**
* 校验sql是否是select update delete语句
* @param statement
* @return
*/
private static boolean check(Statement statement) {
if (statement == null) {
return false;
}
String sql = statement.toString();
if (!StringUtils.isEmpty(sql) && sql.length() > 30) {
sql = sql.toLowerCase();
return (sql.contains("select")
|| sql.contains("update")
|| sql.contains("delete"));
}
return false;
}
}
UserApplication
package com.shengsheng;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import tk.mybatis.spring.annotation.MapperScan;
/**
* description:
*
* @author shengshenglalala
* @date 2021/2/1 17:15
*/
@SpringBootApplication
@MapperScan("com.shengsheng.mapper")
public class UserApplication {
public static void main(String[] args) {
SpringApplication.run(UserApplication.class,args);
}
}
application.yml
spring:
application:
datasource:
url: jdbc:mysql://localhost:3306/test01?serverTimezone=GMT%2B8&statementInterceptors=com.shengsheng.interceptor.ZipkinMysqlStatementInterceptor
username: root
password: 123456
server:
port: 8088
xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.shengsheng</groupId>
<artifactId>zipkin-mysql-trace</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.7.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.github.drtrang</groupId>
<artifactId>druid-spring-boot2-starter</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- mysql驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- mybatis的启动器 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.0</version>
</dependency>
<!-- 通用mapper启动器 -->
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>2.1.5</version>
</dependency>
<dependency>
<groupId>io.zipkin.brave</groupId>
<artifactId>brave-instrumentation-mysql</artifactId>
<version>5.1.3</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.47</version>
<scope>compile</scope>
</dependency>
</dependencies>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
</project>
具体的代码实现