MySQL预编译语句过多告警排查

业务背景

在使用Spring Cloud Alibaba搭建的微服务架构中，项目采用ShardingSphere进行分库分表，MyBatis-Plus作为持久层。线上环境突发大量预编译语句过多的数据库告警，导致系统性能下降。

---

排查过程

1. 初步排查：联系云数据库厂商

首先，联系云数据库服务厂商协助排查，确认问题是由于预编译缓存未被释放，导致占用过多数据库资源。

2. 排查连接池配置

怀疑问题与连接池有关，特别是考虑到线上负载较高的高峰时段。经过检查，项目使用的是HikariCP连接池，相关配置如下：

特别关注两个参数：

• maxLifetime: 连接池最大生命周期
• idleTimeout: 连接池空闲超时时间

在进行断点调试后，由于项目使用了Sharding-JDBC，某些参数并未按照Hikari的默认值生效，而是被Sharding进行初始化配置。Sharding的相关代码如下：

此时可以初步排除连接池配置问题，因为Sharding已将idleTimeout配置为60秒。

3. 分析HikariCP源码与Statement Cache问题

深入分析HikariCP源码，查找与PreparedStatement缓存相关的内容，发现README.md关键描述：

Statement Cache

HikariCP与其他连接池（如Apache DBCP、Vibur、c3p0等）在处理PreparedStatement缓存时的区别：

• HikariCP: 不提供PreparedStatement缓存，原因是连接池层级缓存PreparedStatement只能按连接缓存，导致内存占用过大。
• 其他连接池: 许多连接池提供PreparedStatement缓存，但这会导致大量PreparedStatement对象及相关执行计划在内存中存储，影响性能。

HikariCP并不缓存PreparedStatement，因为多数数据库JDBC驱动已经内置缓存机制，可以跨连接共享执行计划，避免重复占用内存。

要点：

• 连接池层级的PreparedStatement缓存问题：在连接池层缓存会导致大量内存占用，且不支持跨连接共享。
• 数据库驱动缓存的优势：数据库驱动层提供的缓存更高效，能够共享执行计划，减少内存占用。
• 反模式：在连接池层进行缓存PreparedStatement是性能反模式。

4. MySQL驱动配置分析

进一步排查MySQL驱动，发现项目使用的mysql-connector-j:8.3.0驱动，关键配置useServerPrepStmts默认为true，即开启服务端的预编译缓存。而在同一项目中，其他服务使用的是mysql-connector-java:8.0.16，该版本的默认配置为false。
核心代码：

通过对比，确认开启服务端预编译缓存是导致告警的根本原因。

---

解决方案

通过排查，最终确定问题原因是服务端的预编译缓存未关闭。由于项目采用分库分表，并且在同一数据库实例中创建了多个Schema，默认开启的服务端预编译缓存容易导致资源占用过高。

解决步骤：

1. 在JDBC连接字符串中添加配置&useServerPrepStmts=false，关闭MySQL的服务端预编译缓存。

   例如，JDBC连接URL修改如下：

   jdbc:mysql://localhost:3306/dbname?useServerPrepStmts=false
   

2. 配置完成后，重新启动服务，观察效果。关闭服务端预编译缓存后，数据库告警明显减少，系统性能得到提升。

   

---

总结

通过排查，我们确认了预编译语句过多告警的根本原因是MySQL服务端开启了预编译缓存，导致过多的执行计划占用资源。解决方案是关闭服务端的PreparedStatement缓存，减少系统负载并提升性能。