MySQL Prepared Statement

MySQL Prepared Statement
一、简介

1. 解释
   Prepared Statement 指带有参数占位符的预编译语句，优势：
   每次执行时解析语句的开销更少。通常，数据库应用程序处理大量几乎相同的语句，仅更改子句中的文字或变量值，例如WHERE查询和删除、SET更新和 VALUES插入。
   防止 SQL 注入攻击。参数值可以包含未转义的 SQL 引号和分隔符。
   二进制包协议让 SQL 预处理更加高效。
   预编译 SQL 语法基于三个 SQL 语句：
   PREPARE准备
   EXECUTE执行
   DEALLOCATE PREPARE释放

2. Case
   客户端：DBeaver:21.0.5，驱动版本：mysql-connector-java:8.0.17，MySQL服务端版本：5.7.34
   准备工作
   创建表：
   – test.t_users definition
   use test;
   CREATE TABLE t_users (
   id bigint(20) NOT NULL AUTO_INCREMENT,
   user_name varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
   age int(5) NOT NULL,
   PRIMARY KEY (id)
   ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

打开通用日志:
show variables like ‘%general_log%’;
set global general_log = ‘ON’;

准备和执行（逐行执行）
prepare ins from ‘insert into test.t_users (user_name ,age) values (?,?);’;
set @a=‘tom’,@b=‘18’;
#第一次执行
execute ins using @a,@b;
#第二次直接执行
execute ins using @a,@b;

通用日志：
2021-05-31T08:03:36.133451Z 4 Query PREPARE ins FROM …
2021-05-31T08:03:36.134365Z 4 Prepare insert into test.t_users (user_name ,age) values (?,?)
2021-05-31T08:03:39.201959Z 4 Query set @a=‘tom’,@b=‘18’
2021-05-31T08:03:41.785453Z 4 Query execute ins using @a,@b
2021-05-31T08:03:41.785471Z 4 Execute insert into test.t_users (user_name ,age) values (‘tom’,‘18’)
2021-05-31T08:08:28.899228Z 4 Execute insert into test.t_users (user_name ,age) values (‘tom’,‘18’)
插入两条记录成功。
删除
DEALLOCATE PREPARE ins;
Prepared Statement 生命周期是 session，在session终止后会自动删除。
为了防止同时创建过多的 Prepared Statement ，可以设置系统变量 max_prepared_stmt_count 来限制，设置为0相当于disable。

3. ProtocolText vs. ProtocolBinary
   MySQL Pocket
   ProtocolText::Resultset
   A packet containing a Protocol::LengthEncodedInteger column_count
   column_count * Protocol::ColumnDefinition packets
   If the CLIENT_DEPRECATE_EOF client capability flag is not set, EOF_Packet
   One or more ProtocolText::ResultsetRow packets, each containing column_count values
   ERR_Packet in case of error. Otherwise: If the CLIENT_DEPRECATE_EOF client capability flag is set, OK_Packet; else EOF_Packet.

ProtocolBinary::Resultset:
lenenc_int column_count > 0
column_count * Protocol::ColumnDefinition
none or many ProtocolBinary::ResultsetRow
EOF_Packet

二、JDBC中的预编译

mysql
mysql-connector-java
5.1.42

1. useServerPrepStmts
   useServerPrepStmts=true，开启服务端预编译功能
   if (this.useServerPreparedStmts && canServerPrepare) {
   // 服务端预编译
   …
   } else {
   // 客户端预编译
   clientPrepareStatement()
   }
   From: com.mysql.jdbc.ConnectionImpl#prepareStatement(java.lang.String, int, int)
   canServerPrepare是根据SQL类型判断MySQL服务器是否可以预编译该SQL。
   判断 MySQL 版本，是否是增删改查，是否带有 ? 等条件。

2. cachePrepStmts
   cachePrepStmts=true，开启缓存功能
   客户端预编译场景下的缓存（未配置useServerPrepStmts=true）

if (getCachePreparedStatements()) {
PreparedStatement.ParseInfo pStmtInfo = this.cachedPreparedStatementParams.get(nativeSql);

  if (pStmtInfo == null) {
      pStmt = com.mysql.jdbc.PreparedStatement.getInstance(getMultiHostSafeProxy(), nativeSql, this.database);

      this.cachedPreparedStatementParams.put(nativeSql, pStmt.getParseInfo());
  } else {
      pStmt = com.mysql.jdbc.PreparedStatement.getInstance(getMultiHostSafeProxy(), nativeSql, this.database, pStmtInfo);
  }

} else {
pStmt = com.mysql.jdbc.PreparedStatement.getInstance(getMultiHostSafeProxy(), nativeSql, this.database);
}
From： com.mysql.jdbc.ConnectionImpl#prepareStatement(java.lang.String, int, int)

缓存 PreparedStatement 的内部类 ParseInfo，用 LRUCache 实现（相关参数：prepStmtCacheSize、prepStmtCacheSqlLimit）
开启缓存，调用 :
com.mysql.jdbc.PreparedStatement.getInstance(getMultiHostSafeProxy(), nativeSql, this.database, pStmtInfo);
创建 PreparedStatement 对象。
若未开启，则调用:
pStmt = com.mysql.jdbc.PreparedStatement.getInstance(getMultiHostSafeProxy(), nativeSql, this.database);
来创建。缓存可以减少一次创建 ParseInfo 对象的过程
ParseInfo JavaDoc：
/**

• Represents the “parsed” state of a client-side prepared statement, with the statement broken up into it’s static and dynamic (where parameters are
• bound) parts.
  */

服务端预编译场景下的缓存（配置useServerPrepStmts=true）
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 {
              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 {
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;
      }
  }

}
From： com.mysql.jdbc.ConnectionImpl#prepareStatement(java.lang.String, int, int)

用 serverSideStatementCache 缓存 ServerPreparedStatement 对象。放入缓存的动作发生在 ServerPreparedStatement 对象 close 的时候：
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;
      }

      realClose(true, true);
  }

}
From：com.mysql.jdbc.ServerPreparedStatement#close
realClose() 方法中才会发送 COM_CLOSE_STATEMENT 到服务端。
如果不进行缓存，则MySQL服务端预编译也好，本地预编译也好，都会对同一种语句重复预编译。因此为了提升效率，往往我们需要启用缓存，通过设置连接中 cachePrepStmts 参数就可以控制是否启用缓存。此外通过 prepStmtCacheSize 参数可以控制缓存的条数，MySQL驱动默认是25，通常实践中都在250-500左右；通过 prepStmtCacheSqlLimit 可以控制长度多大的sql可以被缓存，MySQL驱动默认是256，通常实践中往往设置为2048这样。

3. prepStmtCacheSize
   设置为每个连接缓存预准备的语句数。默认值为 25，建议设置为250~500。

4. prepStmtCacheSqlLimit
   缓存预准备语句的最大长度。默认值为256，建议设置为2048。

5. 测试
   5.1. 功能测试
   JDBC PrepareStatement()，使用连续两次执行 SQL：
   select * from t_users where id = 1

useServerPrepStmts=false&&cachePrepStmts=false 客户端预编译，关闭缓存。
进入 clientPrepareStatement() 方法，不缓存 ParseInfo 对象，调用
pStmt = com.mysql.jdbc.PreparedStatement.getInstance(getMultiHostSafeProxy(), nativeSql, this.database);
生成 PreparedStatement 对象。
通用日志：
2021-07-31T12:35:09.249335Z 11 Query SET autocommit=1
2021-07-31T12:35:09.264835Z 11 Query select * from t_users where id = 1
2021-07-31T12:35:09.292627Z 11 Query select * from t_users where id = 1

useServerPrepStmts=false&&cachePrepStmts=true
客户端预编译，开启缓存。
进入 clientPrepareStatement() 方法，缓存 ParseInfo 对象，
this.cachedPreparedStatementParams.put(nativeSql, pStmt.getParseInfo());
调用
com.mysql.jdbc.PreparedStatement.getInstance(getMultiHostSafeProxy(), nativeSql, this.database, pStmtInfo);
生成 PreparedStatement 对象。
通用日志：
2021-07-31T12:36:11.157688Z 12 Query SET autocommit=1
2021-07-31T12:36:11.175747Z 12 Query select * from t_users where id = 1
2021-07-31T12:36:11.179129Z 12 Query select * from t_users where id = 1
可以看到 useServerPrepStmts=false 的情况下，客户端只会发送普通的Query语句，而不采用预编译的模式。

useServerPrepStmts=true&&cachePrepStmts=false 服务端预编译，关闭缓存。
不缓存 PreparedStatement 对象，通用日志：
2021-07-31T12:36:47.112334Z 13 Query SET autocommit=1
2021-07-31T12:36:47.156932Z 13 Prepare select * from t_users where id = ?
2021-07-31T12:36:47.158941Z 13 Execute select * from t_users where id = 1
2021-07-31T12:36:47.185316Z 13 Close stmt
2021-07-31T12:36:47.186107Z 13 Prepare select * from t_users where id = ?
2021-07-31T12:36:47.187092Z 13 Execute select * from t_users where id = 1
2021-07-31T12:36:47.188297Z 13 Close stmt

useServerPrepStmts=true&&cachePrepStmts=true
服务端预编译，开启缓存。
缓存 ServerPreparedStatement 对象，通用日志：
2021-07-31T12:38:00.393156Z 14 Query SET autocommit=1
2021-07-31T12:38:00.427601Z 14 Prepare select * from t_users where id = ?
2021-07-31T12:38:00.430182Z 14 Execute select * from t_users where id = 1
2021-07-31T12:38:00.456413Z 14 Execute select * from t_users where id = 1
注意，这里并没有 close stmt，使用下面 SQL 可查询 stmt Prepare、Excute 和 Close 的次数：
SHOW GLOBAL STATUS LIKE ‘com_stmt%’;

5.2. 性能测试
CREATE TABLE IF NOT EXISTS sbtest (
id BIGINT(20) NOT NULL AUTO_INCREMENT,
k INT(11) NOT NULL DEFAULT 0,
c CHAR(120) NOT NULL DEFAULT ‘’,
pad CHAR(60) NOT NULL DEFAULT ‘’,
PRIMARY KEY (id),
KEY k_1 (k))
插入10w 条数据，4 种模式执行 10w 次查询：
select * from (select * from ((select * from (select * from sbtest) a) b)) c where k = ? and 1+1=2 and 2+2=4
以下几种情况，经过3测试取平均值：
本地预编译：20,558 ms
本地预编译+缓存：20,802 ms
服务端预编译：37,596‬ ms
服务端预编译+缓存：17,696‬ ms
三、中间件中的预编译

1. MyCat
   Mycat也实现了mysql的预处理协议，可以接收预处理命令的处理。当使用预处理查询，也可以返回正确的二进制结果集包。Mycat预处理的实现是一种取巧的设计，查询走到后端mysql实际上不是发送了预处理命令，而是普通的COM_QUERY命令，后端mysql返回给Mycat的结果集包也是文本协议包，只是在Mycat将结果集包发送往客户端的中间过程，将普通的文本协议结果集包包装成为二进制协议结果集包，然后再返回给客户端。
   Mycat预处理的处理流程：
   （1）Mycat接收到客户端发送的COM_STMT_PREPARE命令后，解析协议包的内容得到预处理sql语句，eg：insert into user(id, name)value(?, ?),将这些预处理语句缓存在Mycat里面；
   （2）当Mycat再次接收到客户端发送的COM_STMT_EXECUTE命令，就把相应的问号替换为实际传递过来的参数值，这时候已经得到了完整的sql语句。
   （3）接下来，直接把这个语句丢给Mycat sql查询处理器去执行，中间会经过sql解析模块，路由解析模块以及最后的执行。
   （4）最后，当收到后端mysql传递给Mycat的数据准备发往客户端的时候，做一个协议转换，将普通文本结构集协议包转换为二进制结果集协议包并发往客户端。

2. ShardingSphere-Proxy
   

debug.

四、Error 1461

报错原因？com_stmt_prepare - com_stmt_close > max_prepared_stmt_count ？