java 培训 | Mybatis 中的 PreparedStatement 预编译
前言
大家都知道，Mybatis 内置参数，形如 #{xxx}的，均采用了 sql 预编译的形式，大致知道 mybatis 底层使用 PreparedStatement，过程是先将带有占位符（即”?”）的 sql 模板发送至 mysql 服务器，由服务器对此无参数的 sql 进行编译后，将编译结果缓存，然后直接执行带有真实参数的 sql。如果你的基本结论也是如此，那你就大错特错了。
1. mysql 是否默认开启了预编译功能？
mysql 是否支持预编译有两层意思：
db 是否支持预编译
连接数据库的 url 是否指定了需要预编译，比如：jdbc:mysql://127.0.0.1:3306/user?useServerPrepStmts=true，useServerPrepStmts=true 是非常非常重要的参数。如果不配置 PreparedStatement 实际是个假的 PreparedStatement
SELECT VERSION(); // 5.6.24-log
SHOW GLOBAL STATUS LIKE '%prepare%'; //Com_stmt_prepare 4 代表被执行预编译次数
//开启 server 日志
SHOW VARIABLES LIKE '%general_log%';
SHOW VARIABLES LIKE 'log_output';
SET GLOBAL general_log = ON;
SET GLOBAL log_output='table';
TRUNCATE TABLE mysql.general_log;
SELECT * FROM mysql.general_log; // 有 Prepare 命令
注意：mysql 预编译功能有版本要求，包括 server 版本和 mysql.jar 包版本。以前的版本默认 useServerPrepStmts=true，5.0.5 以后的版本默认 useServerPrepStmts=false
2. 预编译缓存是服务端还是客户端缓存？
开启缓存：useServerPrepStmts=true&cachePrepStmts=true，设置了 useServerPrepStmts=true，虽然可以一次编译，多次执行
它可以提高性能，但缓存是针对连接的，即每个连接的缓存都是独立的，并且缓存主要是由 mysql-connector-java.jar 实现的_java培训。
当手动调用 prepareStatement.close()时 PrepareStatement 对象只会将关闭状态置为关闭，并不会向 mysql 发送关闭请求，prepareStatement 对象会被缓存起来，等下次使用的时候直接从缓存中取出来使用。没有开启缓存，则会向 mysql 发送 closeStmt 的请求。
3. 开启预编译性能更高？
也就是说预编译比非预编译更好？其实不然，不行自己可试试看。
public class PreparedStatement_test {
private String url = "jdbc:mysql://localhost:3306/batch";
private String sql = "SELECT * FROM export_request WHERE id = ?";
private int maxTimes = 100000;
@Test
public void go_driver() throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = (Connection) DriverManager.getConnection(url, "root", "123456");
// PreparedStatement
Stopwatch stopwatch = Stopwatch.createStarted();
for (int i = 0; i < maxTimes; i++) {
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setLong(1, Math.abs(new Random().nextLong()));
// execute
stmt.executeQuery();
}
System.out.println("go_driver:" + stopwatch);
}
@Test
public void go_setPre() throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = (Connection) DriverManager.getConnection(url + "?useServerPrepStmts=true", "root", "123456");
// PreparedStatement
Stopwatch stopwatch = Stopwatch.createStarted();
for (int i = 0; i < maxTimes; i++) {
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setLong(1, Math.abs(new Random().nextLong()));
// execute
stmt.executeQuery();
}
System.out.println("go_setPre:" + stopwatch);
}
@Test
public void go_setPreCache() throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = (Connection) DriverManager.getConnection(url + "?useServerPrepStmts=true&cachePrepStmts=true", "root", "123456");
// PreparedStatement
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setLong(1, Math.abs(new Random().nextLong()));
// execute
stmt.executeQuery();
stmt.close();//非常重要的，一定要调用才会缓存
Stopwatch stopwatch = Stopwatch.createStarted();
for (int i = 0; i < maxTimes; i++) {
stmt = conn.prepareStatement(sql);
stmt.setLong(1, Math.abs(new Random().nextLong()));
// execute
stmt.executeQuery();
}
System.out.println("go_setPreCache:" + stopwatch);
}
}
基准为 10w 次单线程：
非预编译：:23.78 s
预编译：41.86 s
预编译缓存：20.55 s
经过实践测试，对于频繁适用的语句，使用预编译+缓存确实能够得到可观的提升，但对于不频繁适用的语句，服务端编译会增加额外的 round-trip。开发实践中要视情况而定。
4. 从源码中验证
预编译原理（connection -> prepareStatement ）
预编译：JDBC42ServerPreparedStatement（需将对应占位符）
非预编译：JDBC42PreparedStatement（完整的 SQL）
//com.mysql.jdbc.ConnectionImpl 中的代码片段
/**
* JDBC 2.0 Same as prepareStatement() above, but allows the default result
* set type and result set concurrency type to be overridden.
*
* @param sql
* the SQL query containing place holders
* @param resultSetType
* a result set type, see ResultSet.TYPE_XXX
* @param resultSetConcurrency
* a concurrency type, see ResultSet.CONCUR_XXX
* @return a new PreparedStatement object containing the pre-compiled SQL
* statement
* @exception SQLException
* if a database-access error occurs.
*/
public java.sql.PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
synchronized (getConnectionMutex()) {
checkClosed();
//
// FIXME: Create warnings if can't create results of the given type or concurrency
//当 Client 开启 useServerPreparedStmts 并且 Server 支持 ServerPrepare
PreparedStatement pStmt = null;
boolean canServerPrepare = true;
String nativeSql = getProcessEscapeCodesForPrepStmts() ? nativeSQL(sql) : sql;
if (this.useServerPreparedStmts && getEmulateUnsupportedPstmts()) {
canServerPrepare = canHandleAsServerPreparedStatement(nativeSql);
}
if (this.useServerPreparedStmts && canServerPrepare) {// 从缓存中获取 pStmt
if (this.getCachePreparedStatements()) {
synchronized (this.serverSideStatementCache) {
pStmt = (com.mysql.jdbc.ServerPreparedStatement) this.serverSideStatementCache
.remove(makePreparedStatementCacheKey(this.database, sql));
if (pStmt != null) {
((com.mysql.jdbc.ServerPreparedStatement) pStmt).setClosed(false);
pStmt.clearParameters();// 清理上次留下的参数
}
if (pStmt == null) {
try {// 向 Server 提交 SQL 预编译，实例是 JDBC42ServerPreparedStatement
pStmt = ServerPreparedStatement.getInstance(getMultiHostSafeProxy(), nativeSql, this.database, resultSetType,
resultSetConcurrency);
if (sql.length() < getPreparedStatementCacheSqlLimit()) {
((com.mysql.jdbc.ServerPreparedStatement) pStmt).isCached = true;
}
pStmt.setResultSetType(resultSetType);
pStmt.setResultSetConcurrency(resultSetConcurrency);
} catch (SQLException sqlEx) {
// Punt, if necessary
if (getEmulateUnsupportedPstmts()) {
pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
if (sql.length() < getPreparedStatementCacheSqlLimit()) {
this.serverSideStatementCheckCache.put(sql, Boolean.FALSE);
}
} else {
throw sqlEx;
}
}
}
}
} else {
try { // 向 Server 提交 SQL 预编译。
pStmt = ServerPreparedStatement.getInstance(getMultiHostSafeProxy(), nativeSql, this.database, resultSetType, resultSetConcurrency);
pStmt.setResultSetType(resultSetType);
pStmt.setResultSetConcurrency(resultSetConcurrency);
} catch (SQLException sqlEx) {
// Punt, if necessary
if (getEmulateUnsupportedPstmts()) {
pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
} else {
throw sqlEx;
}
}
}
} else {// Server 不支持 ServerPrepare，实例是 JDBC42PreparedStatement
pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
}
return pStmt;
}
}
JDBC42ServerPreparedStatement->close，缓存
//com.mysql.jdbc.ServerPreparedStatement 中选取代码
@Override
public void close() throws SQLException {
MySQLConnection locallyScopedConn = this.connection;
if (locallyScopedConn == null) {
return; // already closed
}
synchronized (locallyScopedConn.getConnectionMutex()) {
if (this.isCached && isPoolable() && !this.isClosed) {
clearParameters();// 若开启缓存，则只会将状态位设为已关闭，并且刷新缓存
this.isClosed = true;
this.connection.recachePreparedStatement(this);
return;
}
//没有开启缓存，则会向 mysql 发送 closeStmt 的请求
realClose(true, true);
}
}
public void recachePreparedStatement(ServerPreparedStatement pstmt) throws SQLException {
synchronized (getConnectionMutex()) {
if (getCachePreparedStatements() && pstmt.isPoolable()) {
synchronized (this.serverSideStatementCache) {
Object oldServerPrepStmt = this.serverSideStatementCache.put(makePreparedStatementCacheKey(pstmt.currentCatalog, pstmt.originalSql), pstmt);
if (oldServerPrepStmt != null) {// 将 sql 语句作为 key，reparedStatement 对象作为 value 存放到缓存中
((ServerPreparedStatement) oldServerPrepStmt).isCached = false;
((ServerPreparedStatement) oldServerPrepStmt).realClose(true, true);
}
}
}
}
}
5. 总结
预编译显式开启（在 url 中指定 useServerPrepStmts=true），否则 PreparedStatement 不会向 mysql 发送预编译（Prepare 命令）的请求；
每次向 mysql 发送预编译请求，不管之前有没有执行过此 SQL 语句，只要请求的命令是 Prepare 或 Query，mysql 就会重新编译一次 SQL 语句，并返回此链接当前唯一的 Statement ID，后续执行 SQL 语句的时候，程序只需拿着 Statement ID 和参数就可以了；
当预编译的 SQL 语句有语法错误，则 mysql 的响应会携带错误信息，但此错误信息 JDBC 感知不到(或者说 mysql-connetor-java.jar 包里的实现将其忽略掉了)，此时还会继续往下执行代码，当执行到 executeXxx()方法时，由于没有 Statement ID（所以就会将拼接完整的 SQL 语句值已经将占位符(?)替换掉再次发给 mysql 请求执行，此时 mysql 响应有语法错误，这时 JDBC 就会抛出语法错误异常），所以检查语法那一步实在 mysql-server 中做的(通过抓包可以看到)；
PreparedStatement 对性能的提高是利用缓存实现的，需要显式开启（在 url 中指定 cachePrepStmts=true），此缓存是 mysql-connetor-java.jar 包里实现的（非 mysql-server 中的缓存），缓存的 key 是完整的 sql 语句，value 是 PreparedStatement 对象。放入缓存是 PreparedStatement.close()触发的，所以只要缓存 PreparedStatement 对象没有关闭，你不管调用多少次 connection.prapareStatement(sql)对相同的 sql 语句进行预编译，都会将预编译的请求发给 mysql，mysql 也会对每一个 sql 语句不管是否相同进行预编译，并生成一个唯一的 Statement ID 并返回；
缓存是针对链接的，每个链接都是独立的，不共享缓存
文章来源于 Java 知音
关注尚硅谷，轻松学IT 2021.11.23 加入
IT培训 www.atguigu.com
评论