SpringBoot使用Zipkin实现MySQL慢查询记录

该方案实现使用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>

具体的代码实现

github代码实现