一、背景

有实际的生产案例中，经常会出现应用开发等数据库用户认为的数据库行为和数据库的实际行为不一致的情况，这时通过网络抓包分析就是非常有效的方案。

本文用 tcpdump 和 wireshark 工具来复现和分析 Java 客户端的 prepare 操作在不同配置下与数据库交互过程中的不同行为。

二、操作过程

• JAVA DEMO 代码

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://172.16.201.122:4500/test?characterEncoding=utf8&useSSL=false&...&rewriteBatchedStatements=true");
        config.setUsername("user1");
        config.setPassword("pass1");
        config.setMaximumPoolSize(1);
        config.setPoolName("hikariCP");
        config.setConnectionTimeout(30000);
        config.setMaxLifetime(600000);
        config.setKeepaliveTime(120000);
        HikariDataSource dataSource = new HikariDataSource(config);

        try (Connection connection = dataSource.getConnection()) {
            for (int id = 1; id <= 10; id++) {
                String query = "SELECT * FROM t10 WHERE id = ?";
                try (PreparedStatement statement = connection.prepareStatement(query)) {
                    statement.setInt(1, id);
                    ResultSet resultSet = statement.executeQuery();
                    while (resultSet.next()) {
                System.out.println("id: " + resultSet.getInt("id") + ", name: " + resultSet.getString("name"));
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            dataSource.close();
        }
    }
}

• 数据库环境初始化

create user user1 identified by 'pass1';
grant all on test.* to user1;
create table test.t10 (id int primary key,name varchar(20));
insert into test.t10 values (1,'a'),(2,'a'),(3,'a'),(4,'a'),(5,'a'),(6,'a'),(7,'a'),(8,'a');

• 开始抓包并使用 -w 保存为文件，执行结束后使用 ctrl-c 退出。

在本例中，客户端 IP 固定为 host 172.16.201.85 ，数据库服务端端口固定为 port 4500，两者结合组成监听条件。

[root@vm172-16-201-122 ~]# sudo tcpdump -i eth0  host 172.16.201.85 and port 4500 -w 85_4500_3
tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 262144 bytes
^C47 packets captured
55 packets received by filter
0 packets dropped by kernel

• 为防止抓包无内容，可以再开一个窗口同时将 -w 换成 -vv 查看实时信息输出。

sudo tcpdump -i eth0  host 172.16.201.85 and port 4500 -vv

• 启用 Java 代码

[root@vm172-16-201-85 hikari]# pwd
/root/hikari
[root@vm172-16-201-85 hikari]# ls
HikariCP-4.0.3.jar  Main.class  Main.java  mysql-connector-java-5.1.49-bin.jar  slf4j-api-1.7.36.jar  slf4j-simple-1.7.36.jar
[root@vm172-16-201-85 hikari]# export CLASSPATH=./HikariCP-4.0.3.jar:./mysql-connector-java-5.1.49-bin.jar:./slf4j-api-1.7.36.jar:./slf4j-simple-1.7.36.jar:.;java -Xms512m  -Xmx512m Main

[root@vm172-16-201-85 hikari]# javac Main.java
[root@vm172-16-201-85 hikari]# java Main
[main] INFO com.zaxxer.hikari.HikariDataSource - hikariCP - Starting...
[main] INFO com.zaxxer.hikari.HikariDataSource - hikariCP - Start completed.
id: 1, name: a
id: 2, name: a
id: 3, name: a
id: 4, name: a
id: 5, name: a
id: 6, name: a
id: 7, name: a
id: 8, name: a
[main] INFO com.zaxxer.hikari.HikariDataSource - hikariCP - Shutdown initiated...
[main] INFO com.zaxxer.hikari.HikariDataSource - hikariCP - Shutdown completed.

• 使用 wireshark 分析结果

wireshare 打开文件后，选择 4500 即 数据库的端口的通讯条目，右键选择 decode as ...

将 4500 端口的通讯都解析为 mysql 协议，MySQL 协议内容转变为可见。

同时，可以用 tcpdump 查询语句 mysql.query contains "关键字"，查询关键 SQL 语句所在的位置。

三、分析结果

开启客户端句柄缓存

开启 useServerPrepStmts = true 后同时配置 cachePrepStmts = true，这会让客户端缓存预处理语句。

config.setJdbcUrl("jdbc:mysql://172.16.201.122:4500/test?characterEncoding=utf8&useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSqlLimit=10000&prepStmtCacheSize=1000&useConfigs=maxPerformance&rewriteBatchedStatements=true");

每次的执行过程是 Prepare statement + 多次 Execute statement。

• Prepare

客户端发送 prepare 语句请求

返回客户端 prepare 语句句柄和参数定义

• {Execute statement}*10

后续过程客户端发送语句句柄和参数

返回客户端 Execute 语句结果

关闭客户端句柄缓存

useServerPrepStmts = true 能让服务端执行预处理语句，但默认情况下客户端每次执行完后会 close 预处理语句，并不会复用。

删除 &cachePrepStmts=true&prepStmtCacheSqlLimit=10000&prepStmtCacheSize=1000&useConfigs=maxPerformance

注意：需要一并删除 useConfigs=maxPerformance，它会配置多个参数，其中也包括 cachePrepStmts = true。

config.setJdbcUrl("jdbc:mysql://172.16.201.122:4500/test?characterEncoding=utf8&useSSL=false&useServerPrepStmts=true&rewriteBatchedStatements=true");

每次的执行过程是 Prepare statement + Execute statement + Close statement。

• Prepare statement

• Execute statement

• Close statement

服务端行为对比

对比以上两种交互方式的数据库服务端监控与抓包分析的现象一致。

第一时间段 07:42 ，开启客户端缓存。第二时间段 07:45 ，关闭客户端缓存。

Prepare 阶段 第二时间段数量高

Execute 阶段 两个时间段数量一致

Close 阶段 第二时间段数量高

第二阶段未能使用 plan cache （默认情况下 plan cache 在执行 close 后会释放）

可用的改善方法：服务端启用 tidb_ignore_prepared_cache_close_stmt 保留缓存。

启用后会忽略关闭 Prepared Statement 的指令，包括 Binary 协议的 COM_STMT_CLOSE 信号和文本协议的 DEALLOCATE PREPARE 语句都会被忽略。

下图中，第一次未启用 tidb_ignore_prepared_cache_close_stmt，第二次已启用。启用后，能实现在收到 close 后，下一条 SQL 仍能够命中服务端缓存。

TiDB 内部基于 SQL 语句的文本计算 hash 值，在没有客户端句柄的情况下，通过匹配 hash 命中 Plan Cache。代码位置如：https://github.com/pingcap/tidb/blob/67edd7d8f73de399bd72490d449d1dede1ee637b/pkg/planner/core/plan_cache_utils.go#L261

关闭服务端缓存

一旦使用 useServerPrepStmts=false（无论客户端是否缓存句柄），客户端就会关闭使用 plan cache 的行为，所有语句使用 Query 接口。

config.setJdbcUrl("jdbc:mysql://172.16.201.122:4500/test?characterEncoding=utf8&useSSL=false&useServerPrepStmts=false");

• Query statement

• 服务端行为

使用 Query 无 plancache

四、总结

Java 的行为结果如下表：

同时可以参考文档： https://docs.pingcap.com/zh/tidb/stable/dev-guide-connection-parameters/

某 JAVA 应用的交易压测测试成绩：

通过本例，可以看出 tcpdump + wireshark 的过程可以有效地找出预期不一致时，实际双方是怎么交互的，对于故障分析有非常大的帮助。