数据库内核月报－ 2018/04 - MySQL · 特性分析 · MySQL的预编译功能 - 《数据库内核月报》

利用MySQL JDBC进行预编译
小结

对SQL语句进行词法和语义解析，生成抽象语法树
优化语法树，生成执行计划
按照执行计划执行，并返回结果

绝大部分的常用SQL语句都可以被分解成静态部分和动态部分。静态部分主要包括sql语句的关键字（如DML,DDL等）以及数据库的对象及其相关信息（如表名，视图名，字段名等）。动态部分主要是由数据里的存储的数据构成。一个稳定运行的数据库中执行的所有sql语句，如果我们只关注静态部分，而忽略动态部分（以问号或者占位符对动态部分进行替换）。那么将会发现该系统执行的SQL语句的数量非常有限，只是相同的sql被反复的执行。这些反复执行的sql语句都用相同的执行计划。如果能让sql语句共享执行计划，将极大的提高执行的效率。很多主流的关系型数据库都支持sql语句以绑定变量的方式来共享执行计划。即编译一次，执行多次。遗憾的是mysql并不支持绑定变量。但是MySQL的预编译功能可以达到和绑定变量相同的效果。

1.先建一张下面的表名为mytab

2.通过PREPARE stmt_name FROM preparable_stmt 对下面的sql进行预编译


mysql> prepare sqltpl from 'insert into mytab select ?,?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @a=999,@b='hello';
Query OK, 0 rows affected (0.00 sec)
mysql> execute ins using @a,@b;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> select * from mytab;
+------+-------+
| a    | b     |
+------+-------+
|  999 | hello |
+------+-------+
1 row in set (0.00 sec)

在MySQL中预编译语句作用域是session级，参数max_prepared_stmt_count可以控制全局最大的存储的预编译语句的数量。

当预编译条数已经达到阈值时可以看到MySQL会报错，如下。

上面介绍了直接在MySQL上通过sql命令进行预编译/缓存sql语句。接下来我们以MySQL Java驱动Connector/J（版本5.1.45）测试通过MySQL驱动进行预编译。 ###开启服务端预编译和客户端本地缓存 JDBC的连接串如下

jdbc:mysql://localhost/test?useServerPrepStmts=true&cachePrepStmts=true,

public class PreparedStatementTest {
    public static void main(String[] args) throws Throwable {
        Class.forName("com.mysql.jdbc.Driver");
        try (Connection con = DriverManager.getConnection(url, "root", null)) {
            insert(con, 123, "abc");
            insert(con, 321, "def");
        }
    private static void insert(Connection con, int arg1, String arg2) throws SQLException {
        String sql = "insert into mytab select ?,?";
        try (PreparedStatement statement = con.prepareStatement(sql)) {
            statement.setInt(1, arg1);
            statement.setString(2, arg2);
            statement.executeUpdate();
        }
    }
}

将会在mysql的后台日志中发现以下内容

我们来做一个简易的性能测试。首先写个存储过程向表中初始化大约50万条数据，然后使用同一个连接做select查询(查询条件走索引)。

CREATE PROCEDURE init(cnt INT)
  BEGIN
    DECLARE i INT DEFAULT 1;
    TRUNCATE t;
    INSERT INTO mytab SELECT 1, 'stmt 1';
    WHILE i <= cnt DO
      BEGIN
        INSERT INTO t SELECT a+i, concat('stmt ',a+i) FROM mytab;
        SET i = i << 1;
      END;
    END WHILE;
  END;
mysql> call init(1<<18);
Query OK, 262144 rows affected (3.60 sec)
+----------+
+----------+
|   524288 |
+----------+
1 row in set (0.14 sec)


public static void main(String[] args) throws Throwable {
    Class.forName("com.mysql.jdbc.Driver");
    String url = "";
    long start = System.currentTimeMillis();
    try (Connection con = DriverManager.getConnection(url, "root", null)) {
        for (int i = 1; i <= (1<<19); i++) {
            query(con, i, "stmt " + i);
        }
    }
    long end = System.currentTimeMillis();
    System.out.println(end - start);
}
private static void query(Connection con, int arg1, String arg2) throws SQLException {
    String sql = "select a,b from t where a=? and b=?";
    try (PreparedStatement statement = con.prepareStatement(sql)) {
        statement.setInt(1, arg1);
        statement.setString(2, arg2);
        statement.executeQuery();
    }

以下几种情况，经过3测试取平均值，情况如下：

经过实际测试，对于频繁使用的语句，使用服务端预编译+缓存效率还是能够得到可观的提升的。但是对于不频繁使用的语句，服务端预编译本身会增加额外的round-trip，因此在实际开发中可以视情况定夺使用本地预编译还是服务端预编译以及哪些sql语句不需要开启预编译等。