JavaMybatis

前言

大家都知道，Mybatis内置参数，形如#{xxx}的，均采用了sql预编译的形式，大致知道Mybatis底层使用PreparedStatement，过程是先将带有占位符（即”?”）的sql模板发送至MySQL服务器，由服务器对此无参数的sql进行编译后，将编译结果缓存，然后直接执行带有真实参数的sql。如果基本结论也是如此，那就大错特错了。

目录

  1. MySQL是否默认开启了预编译功能？
  2. 预编译缓存是服务端还是客户端缓存？
  3. 开启预编译性能更高？
  4. 从源码中验证
  5. 总结

    1、MySQL是否默认开启了预编译功能？

    MySQL是否支持预编译有两层意思：
  • db是否支持预编译
  • 连接数据库的url是否指定了需要预编译，比如：jdbc:mysql://127.0.0.1:3306/user?useServerPrepStmts=true，useServerPrepStmts=true是非常非常重要的参数。如果不配置PreparedStatement 实际是个假的 PreparedStatement ```sql 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命令

  1. 注意：MySQL预编译功能有版本要求，包括server版本和mysql.jar包版本。以前的版本默认`useServerPrepStmts=true`，5.0.5以后的版本默认`useServerPrepStmts=false`
  2. <a name="UlCm7"></a>
  3. ## 2、预编译缓存是服务端还是客户端缓存？
  4. 开启缓存：`useServerPrepStmts=true&cachePrepStmts=true`，设置了`useServerPrepStmts=true`，虽然可以一次编译，多次执行它可以提高性能，但缓存是针对连接的，即每个连接的缓存都是独立的，并且缓存主要是由mysql-connector-java.jar实现的。<br />当手动调用`prepareStatement.close()`时`PrepareStatement`对象只会将关闭状态置为关闭，并不会向MySQL发送关闭请求，`prepareStatement`对象会被缓存起来，等下次使用的时候直接从缓存中取出来使用。没有开启缓存，则会向MySQL发送`closeStmt`的请求。
  5. <a name="kfjJu"></a>
  6. ## 3、开启预编译性能更高？
  7. 也就是说预编译比非预编译更好？其实不然，不行自己可试试看。
  8. ```java
  9. public class PreparedStatement_test {
  10.     private String url = "jdbc:mysql://localhost:3306/batch";
  11.     private String sql = "SELECT * FROM export_request WHERE id = ?";
  12.     private int maxTimes = 100000;
  14.     @Test
  15.     public void go_driver() throws SQLException, ClassNotFoundException {
  16.         Class.forName("com.mysql.jdbc.Driver");
  17.         Connection conn = (Connection) DriverManager.getConnection(url, "root", "123456");
  18.         // PreparedStatement
  19.         Stopwatch stopwatch = Stopwatch.createStarted();
  20.         for (int i = 0; i < maxTimes; i++) {
  21.             PreparedStatement stmt = conn.prepareStatement(sql);
  22.             stmt.setLong(1, Math.abs(new Random().nextLong()));
  23.             // execute
  24.             stmt.executeQuery();
  25.         }
  26.         System.out.println("go_driver:" + stopwatch);
  27.     }
  29.     @Test
  30.     public void go_setPre() throws SQLException, ClassNotFoundException {
  31.         Class.forName("com.mysql.jdbc.Driver");
  32.         Connection conn = (Connection) DriverManager.getConnection(url + "?useServerPrepStmts=true", "root", "123456");
  33.         // PreparedStatement
  34.         Stopwatch stopwatch = Stopwatch.createStarted();
  35.         for (int i = 0; i < maxTimes; i++) {
  36.             PreparedStatement stmt = conn.prepareStatement(sql);
  37.             stmt.setLong(1, Math.abs(new Random().nextLong()));
  38.             // execute
  39.             stmt.executeQuery();
  40.         }
  41.         System.out.println("go_setPre:" + stopwatch);
  42.     }
  44.     @Test
  45.     public void go_setPreCache() throws SQLException, ClassNotFoundException {
  46.         Class.forName("com.mysql.jdbc.Driver");
  47.         Connection conn = (Connection) DriverManager.getConnection(url + "?useServerPrepStmts=true&cachePrepStmts=true", "root", "123456");
  48.         // PreparedStatement
  49.         PreparedStatement stmt = conn.prepareStatement(sql);
  50.         stmt.setLong(1, Math.abs(new Random().nextLong()));
  51.         // execute
  52.         stmt.executeQuery();
  53.         stmt.close();//非常重要的，一定要调用才会缓存
  54.         Stopwatch stopwatch = Stopwatch.createStarted();
  55.         for (int i = 0; i < maxTimes; i++) {
  56.             stmt = conn.prepareStatement(sql);
  57.             stmt.setLong(1, Math.abs(new Random().nextLong()));
  58.             // execute
  59.             stmt.executeQuery();
  60.         }
  61.         System.out.println("go_setPreCache:" + stopwatch);
  62.     }
  63. }

基准为10w次单线程：

  • 非预编译：:23.78 s
  • 预编译：41.86 s
  • 预编译缓存：20.55 s

经过实践测试，对于频繁适用的语句，使用预编译+缓存确实能够得到可观的提升，但对于不频繁适用的语句，服务端编译会增加额外的round-trip。开发实践中要视情况而定。

4、从源码中验证

预编译原理（connection -> prepareStatement ）
预编译：JDBC42ServerPreparedStatement（需将对应占位符）
非预编译：JDBC42PreparedStatement（完整的SQL）

  1. //com.mysql.jdbc.ConnectionImpl中的代码片段
  2. /**
  3.  * JDBC 2.0 Same as prepareStatement() above, but allows the default result
  4.  * set type and result set concurrency type to be overridden.
  5.  *
  6.  * @param sql
  7.  *            the SQL query containing place holders
  8.  * @param resultSetType
  9.  *            a result set type, see ResultSet.TYPE_XXX
  10.  * @param resultSetConcurrency
  11.  *            a concurrency type, see ResultSet.CONCUR_XXX
  12.  * @return a new PreparedStatement object containing the pre-compiled SQL
  13.  *         statement
  14.  * @exception SQLException
  15.  *                if a database-access error occurs.
  16.  */
  17. public java.sql.PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
  18.     synchronized (getConnectionMutex()) {
  19.         checkClosed();
  20.         //
  21.         // FIXME: Create warnings if can't create results of the given type or concurrency
  22.         //当Client开启 useServerPreparedStmts 并且Server支持 ServerPrepare
  23.         PreparedStatement pStmt = null;
  24.         boolean canServerPrepare = true;
  25.         String nativeSql = getProcessEscapeCodesForPrepStmts() ? nativeSQL(sql) : sql;
  26.         if (this.useServerPreparedStmts && getEmulateUnsupportedPstmts()) {
  27.             canServerPrepare = canHandleAsServerPreparedStatement(nativeSql);
  28.         }
  30.         if (this.useServerPreparedStmts && canServerPrepare) {// 从缓存中获取 pStmt
  31.             if (this.getCachePreparedStatements()) {
  32.                 synchronized (this.serverSideStatementCache) {
  33.                     pStmt = (com.mysql.jdbc.ServerPreparedStatement) this.serverSideStatementCache
  34.                         .remove(makePreparedStatementCacheKey(this.database, sql));
  36.                     if (pStmt != null) {
  37.                         ((com.mysql.jdbc.ServerPreparedStatement) pStmt).setClosed(false);
  38.                         pStmt.clearParameters();// 清理上次留下的参数
  39.                     }
  41.                     if (pStmt == null) {
  42.                         try {// 向Server提交 SQL 预编译，实例是JDBC42ServerPreparedStatement
  43.                             pStmt = ServerPreparedStatement.getInstance(getMultiHostSafeProxy(), nativeSql, this.database, resultSetType,
  44.                                                                         resultSetConcurrency);
  45.                             if (sql.length() < getPreparedStatementCacheSqlLimit()) {
  46.                                 ((com.mysql.jdbc.ServerPreparedStatement) pStmt).isCached = true;
  47.                             }
  49.                             pStmt.setResultSetType(resultSetType);
  50.                             pStmt.setResultSetConcurrency(resultSetConcurrency);
  51.                         } catch (SQLException sqlEx) {
  52.                             // Punt, if necessary
  53.                             if (getEmulateUnsupportedPstmts()) {
  54.                                 pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
  56.                                 if (sql.length() < getPreparedStatementCacheSqlLimit()) {
  57.                                     this.serverSideStatementCheckCache.put(sql, Boolean.FALSE);
  58.                                 }
  59.                             } else {
  60.                                 throw sqlEx;
  61.                             }
  62.                         }
  63.                     }
  64.                 }
  65.             } else {
  66.                 try {    // 向Server提交 SQL 预编译。
  67.                     pStmt = ServerPreparedStatement.getInstance(getMultiHostSafeProxy(), nativeSql, this.database, resultSetType, resultSetConcurrency);
  69.                     pStmt.setResultSetType(resultSetType);
  70.                     pStmt.setResultSetConcurrency(resultSetConcurrency);
  71.                 } catch (SQLException sqlEx) {
  72.                     // Punt, if necessary
  73.                     if (getEmulateUnsupportedPstmts()) {
  74.                         pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
  75.                     } else {
  76.                         throw sqlEx;
  77.                     }
  78.                 }
  79.             }
  80.         } else {// Server不支持 ServerPrepare，实例是JDBC42PreparedStatement
  81.             pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
  82.         }
  83.         return pStmt;
  84.     }
  85. }

JDBC42ServerPreparedStatement->close，缓存

  1. //com.mysql.jdbc.ServerPreparedStatement中选取代码
  2. @Override
  3. public void close() throws SQLException {
  4.     MySQLConnection locallyScopedConn = this.connection;
  6.     if (locallyScopedConn == null) {
  7.         return; // already closed
  8.     }
  10.     synchronized (locallyScopedConn.getConnectionMutex()) {
  11.         if (this.isCached && isPoolable() && !this.isClosed) {
  12.             clearParameters();// 若开启缓存，则只会将状态位设为已关闭，并且刷新缓存
  13.             this.isClosed = true;
  14.             this.connection.recachePreparedStatement(this);
  15.             return;
  16.         }
  17.         //没有开启缓存，则会向mysql发送closeStmt的请求
  18.         realClose(true, true);
  19.     }
  20. }
  21. public void recachePreparedStatement(ServerPreparedStatement pstmt) throws SQLException {
  22.     synchronized (getConnectionMutex()) {
  23.         if (getCachePreparedStatements() && pstmt.isPoolable()) {
  24.             synchronized (this.serverSideStatementCache) {
  25.                 Object oldServerPrepStmt = this.serverSideStatementCache.put(makePreparedStatementCacheKey(pstmt.currentCatalog, pstmt.originalSql), pstmt);
  26.                 if (oldServerPrepStmt != null) {// 将sql语句作为key，reparedStatement对象作为value存放到缓存中
  27.                     ((ServerPreparedStatement) oldServerPrepStmt).isCached = false;
  28.                     ((ServerPreparedStatement) oldServerPrepStmt).realClose(true, true);
  29.                 }
  30.             }
  31.         }
  32.     }
  33. }

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并返回；
  • 缓存是针对链接的，每个链接都是独立的，不共享缓存。