zipkin brave mysql_Zipkin原理学习--日志追踪 MySQL 执行语句

目前Zipkin官方提供了插件用于支持对MySQL语句执行过程的日志追踪，提供了对MySQL5、MySQL6和MySQL8的支持，官方地址：https://github.com/openzipkin/brave/tree/master/instrumentation

一、介绍及示例

配置示例：

1、引入相关jar包：

io.zipkin.brave

brave-instrumentation-mysql

5.4.3

2、在url中添加拦截器和服务名：statementInterceptors=brave.mysql.TracingStatementInterceptor&zipkinServiceName=myDatabaseService

public void mysql() throws Exception{

Class.forName("com.mysql.jdbc.Driver");

System.out.println("成功加载驱动");

Connection connection = null;

Statement statement = null;

ResultSet resultSet = null;

try {

String url = "jdbc:mysql://localhost:3306/db?user=root&password=root&useUnicode=true&characterEncoding=UTF8&statementInterceptors=brave.mysql.TracingStatementInterceptor&zipkinServiceName=myDatabaseService";

connection = DriverManager.getConnection(url);

System.out.println("成功获取连接");

statement = connection.createStatement();

String sql = "select * from tbl_user";

resultSet = statement.executeQuery(sql);

resultSet.beforeFirst();

while (resultSet.next()) {

System.out.println(resultSet.getString(1));

}

System.out.println("成功操作数据库");

} catch(Throwable t) {

// TODO 处理异常

t.printStackTrace();

} finally {

if (resultSet != null) {

resultSet.close();

}

if (statement != null) {

statement.close();

}

if (connection != null) {

connection.close();

}

System.out.println("成功关闭资源");

}

}

3、zipserver中结果示例：

二、实现原理

其实现原理也还是挺容易理解的，利用MySQL JDBC提供的拦截器机制，在sql语句执行前新建一个span调用，在sql语句执行后结束span调用，记录整个调用过程的耗时及sql语句信息。

public class TracingStatementInterceptor implements StatementInterceptorV2 {

/**

* Uses {@link ThreadLocalSpan} as there‘s no attribute namespace shared between callbacks, but

* all callbacks happen on the same thread.

*

*

Uses {@link ThreadLocalSpan#CURRENT_TRACER} and this interceptor initializes before

* tracing.

*/

@Override

public ResultSetInternalMethods preProcess(String sql, Statement interceptedStatement,

Connection connection) {

// Gets the next span (and places it in scope) so code between here and postProcess can read it

//新生成一个Span

Span span = ThreadLocalSpan.CURRENT_TRACER.next();

if (span == null || span.isNoop()) return null;

// When running a prepared statement, sql will be null and we must fetch the sql from the statement itself

if (interceptedStatement instanceof PreparedStatement) {

sql = ((PreparedStatement) interceptedStatement).getPreparedSql();

}

int spaceIndex = sql.indexOf(‘ ‘); // Allow span names of single-word statements like COMMIT

span.kind(Span.Kind.CLIENT).name(spaceIndex == -1 ? sql : sql.substring(0, spaceIndex));

span.tag("sql.query", sql);

parseServerIpAndPort(connection, span);

//记录启动时间

span.start();

return null;

}

@Override

public ResultSetInternalMethods postProcess(String sql, Statement interceptedStatement,

ResultSetInternalMethods originalResultSet, Connection connection, int warningCount,

boolean noIndexUsed, boolean noGoodIndexUsed, SQLException statementException) {

Span span = ThreadLocalSpan.CURRENT_TRACER.remove();

if (span == null || span.isNoop()) return null;

if (statementException != null) {

span.tag("error", Integer.toString(statementException.getErrorCode()));

}

//记录服务停止时间

span.finish();

return null;

}

/**

* MySQL exposes the host connecting to, but not the port. This attempts to get the port from the

* JDBC URL. Ex. 5555 from {@code jdbc:mysql://localhost:5555/database}, or 3306 if absent.

*/

static void parseServerIpAndPort(Connection connection, Span span) {

try {

URI url = URI.create(connection.getMetaData().getURL().substring(5)); // strip "jdbc:"

String remoteServiceName = connection.getProperties().getProperty("zipkinServiceName");

if (remoteServiceName == null || "".equals(remoteServiceName)) {

String databaseName = connection.getCatalog();

if (databaseName != null && !databaseName.isEmpty()) {

remoteServiceName = "mysql-" + databaseName;

} else {

remoteServiceName = "mysql";

}

}

//添加服务名

span.remoteServiceName(remoteServiceName);

String host = connection.getHost();

if (host != null) {

span.remoteIpAndPort(host, url.getPort() == -1 ? 3306 : url.getPort());

}

} catch (Exception e) {

// remote address is optional

}

}

@Override public boolean executeTopLevelOnly() {

return true; // True means that we don‘t get notified about queries that other interceptors issue

}

@Override public void init(Connection conn, Properties props) {

// Don‘t care

}

@Override public void destroy() {

// Don‘t care

}

}

思考：其实Zipkin官方给出的这种方案还是能给我们一些启发的，目前对于数据库官方只支持了mysql，对于Postgresql、Oracle 和 SQL Server 等可以基于技术方案有以下两种局限解决方案：

(1)利用mybatis的拦截器机制来实现，和上面的实现类似

(2)利用数据库池 Druid的过滤器同样可以实现。

以上两种方案的好处：对于数据库通用支持。

————————————————

原文链接：https://blog.csdn.net/qq924862077/article/details/88559499