Poison

关于 PreparedStatement 的缓存问题

之前查看 MyBatis 的代码时,发现默认使用的执行器类型为 SIMPLE,其源码位于 SimpleExecutor.java,而还有一种执行器类型为 REUSE,其源码位于 ReuseExecutor.java,如果比较源码可以发现主要的不同为 ReuseExecutor 含有如下的 HashMap 用于缓存之前创建的 PreparedStatement

1
private final Map<String, Statement> statementMap = new HashMap<>();

但是为何 MyBatis 默认未使用 REUSE 类型的 Executor 呢?从文档上没有发现相关的解释,我后续查询了 MySQL JDBC 驱动,发现 MySQL JDBC 驱动在连接字符串上配置了指定参数时会缓存 PreparedStatement,其中连接字符串上的参数配置可以参考:MySQL Configuration,缓存源码位于 ConnectionImpl.java,底层核心即为 HashMap 实现:

1
private LRUCache<CompoundCacheKey, ServerPreparedStatement> serverSideStatementCache;

即在配置了指定参数时在驱动层就能实现 PreparedStatement 的缓存,而无需在应用层另行实现。类似的问题在 HikariCP 的文档中已有提及,原文可参考:HikariCP - Statement Cache

Many connection pools, including Apache DBCP, Vibur, c3p0 and others offer PreparedStatement caching. HikariCP does not. Why?

At the connection pool layer PreparedStatements can only be cached per connection. If your application has 250 commonly executed queries and a pool of 20 connections you are asking your database to hold on to 5000 query execution plans – and similarly the pool must cache this many PreparedStatements and their related graph of objects.

Most major database JDBC drivers already have a Statement cache that can be configured, including PostgreSQL, Oracle, Derby, MySQL, DB2, and many others. JDBC drivers are in a unique position to exploit database specific features, and nearly all of the caching implementations are capable of sharing execution plans across connections. This means that instead of 5000 statements in memory and associated execution plans, your 250 commonly executed queries result in exactly 250 execution plans in the database. Clever implementations do not even retain PreparedStatement objects in memory at the driver-level but instead merely attach new instances to existing plan IDs.

Using a statement cache at the pooling layer is an anti-pattern, and will negatively impact your application performance compared to driver-provided caches.

Reference

PreparedStatement (Java Platform SE 8 )
MySQL :: MySQL 5.6 Reference Manual :: 8.10.4 Caching of Prepared Statements and Stored Programs
database - What’s the difference between cachePrepStmts and useServerPrepStmts in MySQL JDBC Driver - Stack Overflow