sql 预编译语句

1. 背景

本文重点讲述MySQL中的预编译语句并从MySQL的Connector/J源码出发讲述其在Java语言中相关使用。
注意：文中的描述与结论基于MySQL 5.7.16以及Connect/J 5.1.42版本。

2. 预编译语句是什么

通常我们的一条sql在db接收到最终执行完毕返回可以分为下面三个过程：

1. 词法和语义解析
2. 优化sql语句，制定执行计划
3. 执行并返回结果

我们把这种普通语句称作Immediate Statements。

但是很多情况，我们的一条sql语句可能会反复执行，或者每次执行的时候只有个别的值不同（比如query的where子句值不同，update的set子句值不同,insert的values值不同）。
如果每次都需要经过上面的词法语义解析、语句优化、制定执行计划等，则效率就明显不行了。

所谓预编译语句就是将这类语句中的值用占位符替代，可以视为将sql语句模板化或者说参数化，一般称这类语句叫Prepared Statements或者

Parameterized Statements

预编译语句的优势在于归纳为：一次编译、多次运行，省去了解析优化等过程；此外预编译语句能防止sql注入。
当然就优化来说，很多时候最优的执行计划不是光靠知道sql语句的模板就能决定了，往往就是需要通过具体值来预估出成本代价。

3. MySQL的预编译功能

注意MySQL的老版本（4.1之前）是不支持服务端预编译的，但基于目前业界生产环境普遍情况，基本可以认为MySQL支持服务端预编译。

下面我们来看一下MySQL中预编译语句的使用。
首先我们有一张测试表t，结构如下所示：

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(20) DEFAULT NULL,
  UNIQUE KEY `ab` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3.1 编译

我们接下来通过 PREPARE stmt_name FROM preparable_stm的语法来预编译一条sql语句

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

3.2 执行

我们通过EXECUTE stmt_name [USING @var_name [, @var_name] …]的语法来执行预编译语句

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 t;
+------+-------+
| a    | b     |
+------+-------+
|  999 | hello |
+------+-------+
1 row in set (0.00 sec)

可以看到，数据已经被成功插入表中。

MySQL中的预编译语句作用域是session级，但我们可以通过max_prepared_stmt_count变量来控制全局最大的存储的预编译语句。

mysql> set @@global.max_prepared_stmt_count=1;
Query OK, 0 rows affected (0.00 sec)

mysql> prepare sel from 'select * from t';
ERROR 1461 (42000): Can't create more than max_prepared_stmt_count statements (current value: 1)

当预编译条数已经达到阈值时可以看到MySQL会报如上所示的错误。

3.3 释放

如果我们想要释放一条预编译语句，则可以使用{DEALLOCATE | DROP} PREPARE stmt_name的语法进行操作:

mysql> deallocate prepare ins;
Query OK, 0 rows affected (0.00 sec)

4. 通过MySQL驱动进行预编译

以上介绍了直接在MySQL上通过sql命令进行预编译/缓存sql语句。接下来我们以MySQL Java驱动Connector/J（版本5.1.42）为例来介绍通过MySQL驱动进行预编译。

4.1 客户端预编译

首先，简要提一下JDBC中java.sql.PreparedStatement是java.sql.Statement的子接口,它主要提供了无参数执行方法如executeQuery和executeUpdate等，以及大量形如set{Type}(int, {Type})形式的方法用于设置参数。

在Connector/J中，java.sql.connection的底层实现类为com.mysql.jdbc.JDBC4Connection，它的类层次结构如下图所示：

下面是我编写如下测试类，程序中做的事情很简单，就是往test.t表中插入一条记录。
test.t表的结构在上述服务端预编译语句中已经有展示，此处不再赘述。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

/**
 * Test for PreparedStatement.
 *
 * @author Robin Wang
 */
public class PreparedStatementTest {
    public static void main(String[] args) throws Throwable {
        Class.forName("com.mysql.jdbc.Driver");

        String url = "jdbc:mysql://localhost/test";
        try (Connection con = DriverManager.getConnection(url, "root", null)) {
            String sql = "insert into t select ?,?";
            PreparedStatement statement = con.prepareStatement(sql);

            statement.setInt(1, 123456);
            statement.setString(2, "abc");
            statement.executeUpdate();

            statement.close();
        }
    }
}

执行main方法后，通过MySQL通用日志查看到相关log：

2017-07-04T16:39:17.608548Z        19 Connect   root@localhost on test using SSL/TLS
2017-07-04T16:39:17.614299Z        19 Query     /* mysql-connector-java-5.1.42 ( Revision: 1f61b0b0270d9844b006572ba4e77f19c0f230d4 ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout
2017-07-04T16:39:17.642476Z        19 Query     SET character_set_results = NULL
2017-07-04T16:39:17.643212Z        19 Query     SET autocommit=1
2017-07-04T16:39:17.692708Z        19 Query     insert into t select 123456,'abc'
2017-07-04T16:39:17.724803Z        19 Quit      

从MySQL驱动源码中我们可以看到程序中对prepareStatement方法的调用最终会走到如下所示的代码段中：

上图截自com.mysql.jdbc.ConnectionImpl#prepareStatement(java.lang.String, int, int)

这里有两个很重要的参数useServerPrepStmts以及emulateUnsupportedPstmts用于控制是否使用服务端预编译语句。
由于上述程序中我们没有启用服务端预编译，因此MySQL驱动在上面的prepareStatement方法中会进入使用客户端本地预编译的分支进入如下所示的clientPrepareStatement方法。

上图截自com.mysql.jdbc.ConnectionImpl#clientPrepareStatement(java.lang.String, int, int, boolean)

而我们上面的程序中也没有通过cachePrepStmts参数启用缓存，因此会通过com.mysql.jdbc.JDBC42PreparedStatement的三参构造方法初始化出一个PreparedStatement对象。

上图截自com.mysql.jdbc.PreparedStatement#getInstance(com.mysql.jdbc.MySQLConnection, java.lang.String, java.lang.String)

com.mysql.jdbc.JDBC42PreparedStatement的类继承关系图如下所示：

以上介绍的是默认不开启服务预编译及缓存的情况。

4.2 通过服务端预编译的情况

接下来，将上述程序中的连接串改为jdbc:mysql://localhost/test?useServerPrepStmts=true,其余部分不作变化，清理表数据，重新执行上述程序，我们会在MySQL日志中看到如下信息：

2017-07-04T16:42:23.228297Z        22 Connect   root@localhost on test using SSL/TLS
2017-07-04T16:42:23.233854Z        22 Query     /* mysql-connector-java-5.1.42 ( Revision: 1f61b0b0270d9844b006572ba4e77f19c0f230d4 ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout
2017-07-04T16:42:23.261345Z        22 Query     SET character_set_results = NULL
2017-07-04T16:42:23.262127Z        22 Query     SET autocommit=1
2017-07-04T16:42:23.286449Z        22 Prepare   insert into t select ?,?
2017-07-04T16:42:23.288361Z        22 Execute   insert into t select 123456,'abc'
2017-07-04T16:42:23.301597Z        22 Close stmt        
2017-07-04T16:42:23.302188Z        22 Quit      

从上面的日志中，我们可以很清楚地看到Prepare, Execute, Close几个command，显然MySQL服务器为我们预编译了语句。

我们仅仅通过useServerPrepStmts开启了服务端预编译，由于未开启缓存，因此prepareStatement方法会向MySQL服务器请求对语句进行预编译。

上图截自com.mysql.jdbc.ConnectionImpl#prepareStatement(java.lang.String, int, int)

如果我们对代码稍作调整，在其中再向表中做对同一个sql模板语句进行prepare->set->execute->close操作，可以看到如下所示的日志，由于没有缓存后面即使对同一个模板的sql进行预编译，仍然会向MySQL服务器请求编译、执行、释放。

2017-07-05T16:04:45.801650Z	   76 Connect	root@localhost on test using SSL/TLS
2017-07-05T16:04:45.807448Z	   76 Query	/* mysql-connector-java-5.1.42 ( Revision: 1f61b0b0270d9844b006572ba4e77f19c0f230d4 ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout
2017-07-05T16:04:45.834672Z	   76 Query	SET character_set_results = NULL
2017-07-05T16:04:45.835183Z	   76 Query	SET autocommit=1
2017-07-05T16:04:45.868532Z	   76 Prepare	insert into t select ?,?
2017-07-05T16:04:45.869961Z	   76 Execute	insert into t select 1234546,'ab33c'
2017-07-05T16:04:45.891609Z	   76 Close stmt
2017-07-05T16:04:45.892015Z	   76 Prepare	insert into t select ?,?
2017-07-05T16:04:45.892454Z	   76 Execute	insert into t select 6541321,'de22f'
2017-07-05T16:04:45.904014Z	   76 Close stmt
2017-07-05T16:04:45.904312Z	   76 Quit     

4.3 使用缓存的情况

在类似MyBatis等ORM框架中，往往会大量用到预编译语句。例如MyBatis中语句的statementType默认为PREPARED，因此通常语句查询时都会委托connection调用prepareStatement来获取一个java.sql.PreparedStatement对象。

上图截自org.apache.ibatis.executor.statement.PreparedStatementHandler#instantiateStatement

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

4.3.1 使用缓存的情况

接下来，将测试程序中的连接url串改为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");

        String url = "jdbc:mysql://localhost/test?useServerPrepStmts=true&cachePrepStmts=true";
        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 t select ?,?";
        try (PreparedStatement statement = con.prepareStatement(sql)) {
            statement.setInt(1, arg1);
            statement.setString(2, arg2);
            statement.executeUpdate();
        }
    }
}

观察到此时的MySQL日志如下所示,可以看到由于启用了缓存，在MySQL服务端只会预编译一次，之后每次由驱动从本地缓存中读取：

2017-07-05T14:11:08.967038Z        45 Query     /* mysql-connector-java-5.1.42 ( Revision: 1f61b0b0270d9844b006572ba4e77f19c0f230d4 ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout
2017-07-05T14:11:09.014069Z        45 Query     SET character_set_results = NULL
2017-07-05T14:11:09.016009Z        45 Query     SET autocommit=1
2017-07-05T14:11:09.060693Z        45 Prepare   insert into t select ?,?
2017-07-05T14:11:09.061870Z        45 Execute   insert into t select 123,'abc'
2017-07-05T14:11:09.086018Z        45 Execute   insert into t select 321,'def'
2017-07-05T14:11:09.107963Z        45 Quit       

MySQL驱动里对于server预编译的情况维护了两个基于LinkedHashMap使用LRU策略的cache，分别是serverSideStatementCheckCache用于缓存sql语句是否可以由服务端来缓存以及serverSideStatementCache用于缓存服务端预编译sql语句，这两个缓存的大小由prepStmtCacheSize参数控制。

接下来，我们来看一下MySQL驱动是如何通过这样的缓存来实现预编译结果复用的。

上图截自com.mysql.jdbc.ConnectionImpl#prepareStatement(java.lang.String, int, int)

如上图所示，在启用服务端缓存的情况下，MySQL驱动会尝试从LRU缓存中读取预编译sql，如果命中缓存的话，则会置Statement对象的close状态为false，复用此对象；
而如果未命中缓存的话，则会根据sql长度是否小于prepStmtCacheSqlLimit参数的值来为设置是否需要缓存，可以理解为是打个缓存标记，并延迟到语句close时进行缓存。

而在Statement对象执行close方法时，MySQL驱动中的ServerPreparedStatement会根据isCached标记、是否可池化、是否已经关闭等来判断是否要把预编译语句放到缓存中以复用。

上图截自com.mysql.jdbc.ServerPreparedStatement#close

在连接初始化时，如果启用了useServerPrepStmts，则serverSideStatementCheckCache和serverSideStatementCache这两个LRU缓存也将随之初始化。

上图截自com.mysql.jdbc.ConnectionImpl#createPreparedStatementCaches

其中serverSideStatementCache对于被待移除元素有更进一步的处理：对于被缓存淘汰的预编译语句，给它缓存标记置为false，并且调用其close方法。

4.3.2 客户端预编译+缓存

接下来看看客户端本地预编译并且使用缓存的情况。
MySQL驱动源码中使用cachedPreparedStatementParams来缓存sql语句的ParseInfo，ParseInfo是com.mysql.jdbc.PreparedStatement的一个内部类，用于存储预编译语句的一些结构和状态基本信息。cachedPreparedStatementParams的类型是com.mysql.jdbc.CacheAdapter,这是MySQL驱动源码中的一个缓存适配器接口，在连接初始化的时候会通过parseInfoCacheFactory来初始化一个作用域为sql连接的缓存类（com.mysql.jdbc.PerConnectionLRUFactory）出来，其实就是对LRUCache和sql连接的一个封装组合。

上图截自com.mysql.jdbc.ConnectionImpl#clientPrepareStatement(java.lang.String, int, int, boolean)

在缓存未命中的情况下，驱动会本地prepare出来一个预编译语句，并且将parseInfo放入缓存中；而缓存命中的话，则会把缓存中的parseInfo带到四参构造方法中构造初始化。

5. 性能测试

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

CREATE PROCEDURE init(cnt INT)
  BEGIN
    DECLARE i INT DEFAULT 1;
    TRUNCATE t;
    INSERT INTO t SELECT 1, 'stmt 1';
    WHILE i <= cnt DO
      BEGIN
        INSERT INTO t SELECT a+i, concat('stmt ',a+i) FROM t;
        SET i = i << 1;
      END;
    END WHILE;
  END;

mysql> call init(1<<18);
Query OK, 262144 rows affected (3.60 sec)

mysql> select count(0) from t;
+----------+
| count(0) |
+----------+
|   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测试取平均值，情况如下：

• 本地预编译：65769 ms
• 本地预编译+缓存：63637 ms
• 服务端预编译：100985 ms
• 服务端预编译+缓存：57299 ms
  从中我们可以看出本地预编译加不加缓存其实差别不是太大，服务端预编译不加缓存性能明显会降低很多，但是服务端预编译加缓存的话性能还是会比本地好很多。
  主要原因是服务端预编译不加缓存的话本身prepare也是有开销的，另外多了大量的round-trip。

6. 总结

本文重点介绍了预编译语句的概念及其在MySQL中的使用，并以介绍了预编译语句在MySQL驱动源码中的一些实现细节。

在实际生产环境中，如MyBatis等ORM框架大量使用了预编译语句，最终底层调用都会走到MySQL驱动里，从驱动中了解相关实现细节有助于更好地理解预编译语句。

一些网上的文章称必须使用useServerPrepStmts才能开启预编译，这种说法是错误的。实际上JDBC规范里没有说过预编译语句这件事情由本地来做还是服务端来做。MySQL早期版本中由于不支持服务端预编译，因此当时主要是通过本地预编译。

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

7. 参考

MySQL官方手册预编译语句

MySQL Connector/J源码

---

1、什么是预编译

1.1、 sql的执行过程

① 词法和语义分析
② 优化sql语句，指定执行计划
③ 执行并返回结果
我们把这种普通语句称作Immediate Statements。

select colume from table where colume=1;
select colume from table where colume=2;

但是很多情况，我们的一条sql语句可能会反复执行，或者每次执行的时候只有个别的值不同，那么这个时候会对上面两条语句生成两个执行计划，一千次查询就需要一千个执行计划，生成执行计划非常耗费时间。

如果每次都需要经过上面的词法语义解析、语句优化、制定执行计划等，则效率就明显不行了，所以引出预编译功能。

1.2. 预编译语句

将这类语句中的值用占位符替代，可以视为将sql语句模板化或者说参数化，一般称这类语句叫Prepared Statements或者Parameterized Statements。

比如下面的语句

insert into category values(null,?,?)

1.3. 预编译

以java为例，预编译会使用preparestatement，会对预编译语句进行语法分析，编译。
那么这个时候会对上面的insert语句按照预编译语句生成一个执行计划，然后根据参数可以进行重用执行计划。

当处理批量SQL语句时，这个时候就可以体现PrepareStatement的优势，由于采用Cache机制，则预先编译的语句，就会放在Cache中，下次执行相同SQL语句时，则可以直接从Cache中取出来,效率要比statement高好几倍

当语句真正开始执行时，传过来的参数只会被看成纯文本，不会重新编译，不会被当做sql指令，所以可以防止注入。

很多时候最优的执行计划不是光靠知道sql语句的模板就能决定了，往往就是需要通过具体值来预估出成本代价。

• 注意MySQL的老版本（4.1之前）是不支持服务端预编译的，但基于目前业界生产环境普遍情况，基本可以认为MySQL支持服务端预编译。

一次编译、多次运行，省去了解析优化等过程；此外预编译语句能防止sql注入。

1.4. 参考博客

https://blog.csdn.net/weixin_41563161/article/details/110110761
https://zhuanlan.zhihu.com/p/42841510
https://blog.csdn.net/qq_43936524/article/details/115104527
https://blog.csdn.net/lbmydream/article/details/84516198

2、mysql的预编译功能

注意MySQL的老版本（4.1之前）是不支持服务端预编译的，但基于目前业界生产环境普遍情况，基本可以认为MySQL支持服务端预编译。

2.1. 预编译测试表

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1412240664143921156 DEFAULT CHARSET=utf8mb3;

2.2. 创建预编译语句

创建预编译模板，名字为ins
通过 PREPARE stmt_name FROM preparable_stm的语法来预编译一条sql语句

prepare ins from 'insert into `user` VALUE(?,?,?,?)';

2.3. 执行

我们通过EXECUTE stmt_name [USING @var_name [, @var_name] …]的语法来执行预编译语句

# 设置参数
set @id=111,@userName='张三',@age=22,@email='111@163.com'
> OK
> 时间: 0.101s

# 执行sql模板
execute ins using @id,@userName,@age,@email
> OK
> 时间: 0.517s

插入成功

2.4. 预编译缓存

MySQL中的预编译语句作用域是session级，但我们可以通过max_prepared_stmt_count变量来控制全局最大的存储的预编译语句。

mysql> set @@global.
=1;
Query OK, 0 rows affected (0.00 sec)

mysql> prepare sel from 'select * from t';
ERROR 1461 (42000): Can't create more than max_prepared_stmt_count statements (current value: 1)

当预编译条数已经达到阈值时可以看到MySQL会报如上所示的错误。

max_prepared_stmt_count参数限制了同一时间在mysqld上所有session中prepared 语句的上限。
它的取值范围为“0 - 1048576”，默认为16382。

2.5、释放预编译语句

如果我们想要释放一条预编译语句，则可以使用{DEALLOCATE | DROP} PREPARE stmt_name的语法进行操作:

deallocate prepare ins
> OK
> 时间: 0.002s

3、MYSQL驱动编译——客户端预编译

3.1. 执行的jdbc连接代码

public static void main(String[] args) {
        try {
            //1、获得驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //获取连接
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis_plus?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai","root","123456");
            String sql = "insert into `user` VALUE(?,?,?,?)";
            //创建statement
            statement= connection.prepareStatement(sql);
            statement.setInt(1, 222);
            statement.setString(2, "李四");
            statement.setInt(3, 32);
            statement.setString(4, "222@163.com");
            statement.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //....关闭相关连接
        }
    }

3.2. 查询sql执行的日志

发现只是直接insert进行而已（开启的Mysql的通用日志）

2021-07-09T08:06:26.788250Z	   20 Connect	root@localhost on mybatis_plus using TCP/IP
2021-07-09T08:06:26.894267Z	   20 Query	/* mysql-connector-java-8.0.19 (Revision: a0ca826f5cdf51a98356fdfb1bf251eb042f80bf) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2021-07-09T08:06:26.929547Z	   20 Query	SET NAMES utf8mb4
2021-07-09T08:06:26.929913Z	   20 Query	SET character_set_results = NULL
2021-07-09T08:06:26.930637Z	   20 Query	SET autocommit=1
2021-07-09T08:06:26.979683Z	   20 Query	insert into `user` VALUE(222,'李四',32,'222@163.com')

发现没有使用Prepare命令进行预编译等

3.3、查询源码寻找问题

发现调用 connection.prepareStatement(sql); 时，会调用到 ConnectionImpl 的 prepareStatement 方法

public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        try {
            synchronized(this.getConnectionMutex()) {
                this.checkClosed();
                ClientPreparedStatement pStmt = null;
                boolean canServerPrepare = true;
                // 不同的数据库系统对sql进行语法转换
                String nativeSql = (Boolean)this.processEscapeCodesForPrepStmts.getValue() ? this.nativeSQL(sql) : sql;
                // 判断是否可以进行服务器端预编译
                if ((Boolean)this.useServerPrepStmts.getValue() && (Boolean)this.emulateUnsupportedPstmts.getValue()) {
                    canServerPrepare = this.canHandleAsServerPreparedStatement(nativeSql);
                }
                // 如果可以进行服务器端预编译
                if ((Boolean)this.useServerPrepStmts.getValue() && canServerPrepare) {
                    // 是否缓存了PreparedStatement对象
                    if ((Boolean)this.cachePrepStmts.getValue()) {
                        ......
                    } else {
                        try {
                            // 未启用缓存时，直接调用服务器端进行预编译
                            pStmt = ServerPreparedStatement.getInstance(this.getMultiHostSafeProxy(), nativeSql, this.database, resultSetType, resultSetConcurrency);
                            // 设置返回类型以及并发类型
                            ((ClientPreparedStatement)pStmt).setResultSetType(resultSetType);
                            ((ClientPreparedStatement)pStmt).setResultSetConcurrency(resultSetConcurrency);
                        } catch (SQLException var13) {
                            if (!(Boolean)this.emulateUnsupportedPstmts.getValue()) {
                                throw var13;
                            }

                            pStmt = (ClientPreparedStatement)this.clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
                        }
                    }
                } else {
                    // 不支持服务器端预编译时调用客户端预编译（不需要数据库 connection ）
                    pStmt = (ClientPreparedStatement)this.clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
                }

                return (PreparedStatement)pStmt;
            }
        } catch (CJException var17) {
            throw SQLExceptionsMapping.translateException(var17, this.getExceptionInterceptor());
        }
    }

这里有两个很重要的参数 useServerPrepStmts 以及 emulateUnsupportedPstmts 用于控制是否使用服务端预编译语句。

由于上述程序中我们没有启用服务端预编译，因此MySQL驱动在上面的prepareStatement方法中会进入使用客户端本地预编译的分支进入如下所示的clientPrepareStatement方法。

上面就是使用客户端与编译的情况

4、MYSQL驱动编译——服务器端预编译

4.1. 将mysql的url连接开启useServerPrepStmts=true

 connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis_plus?useServerPrepStmts=true&useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai","root","123456");

4.2. 执行sql语句，查询mysql通用日志

2021-07-09T11:39:48.688976Z	   54 Connect	root@localhost on mybatis_plus using TCP/IP
2021-07-09T11:39:48.697950Z	   54 Query	/* mysql-connector-java-8.0.19 (Revision: a0ca826f5cdf51a98356fdfb1bf251eb042f80bf) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2021-07-09T11:39:48.737695Z	   54 Query	SET NAMES utf8mb4
2021-07-09T11:39:48.738128Z	   54 Query	SET character_set_results = NULL
2021-07-09T11:39:48.738997Z	   54 Query	SET autocommit=1
2021-07-09T11:39:48.788362Z	   54 Prepare	insert into `user`(id,name,age,email) VALUE(?,?,?,?)
2021-07-09T11:39:48.799870Z	   54 Execute	insert into `user`(id,name,age,email) VALUE(222,'李四',32,'222@163.com')

从上面的日志中，我们可以很清楚地看到Prepare, Execute, Close几个command，显然MySQL服务器为我们预编译了语句。

4.3. 插入两次数据

connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis_plus?useServerPrepStmts=true&useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai","root","123456");
String sql = "insert into `user`(id,name,age,email) VALUE(?,?,?,?)";
//创建statement
statement= connection.prepareStatement(sql);
statement.setInt(1, 222);
statement.setString(2, "李四");
statement.setInt(3, 32);
statement.setString(4, "222@163.com");
statement.executeUpdate();
statement= connection.prepareStatement(sql);
statement.setInt(1, 333);
statement.setString(2, "李四");
statement.setInt(3, 32);
statement.setString(4, "222@163.com");
statement.executeUpdate();

查询日志，发现由于没有开启缓存所以，每次都重新进行了预编译。

2021-07-09T11:42:27.946160Z	   55 Connect	root@localhost on mybatis_plus using TCP/IP
2021-07-09T11:42:27.955048Z	   55 Query	/* mysql-connector-java-8.0.19 (Revision: a0ca826f5cdf51a98356fdfb1bf251eb042f80bf) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2021-07-09T11:42:27.996971Z	   55 Query	SET NAMES utf8mb4
2021-07-09T11:42:27.997349Z	   55 Query	SET character_set_results = NULL
2021-07-09T11:42:27.998202Z	   55 Query	SET autocommit=1
2021-07-09T11:42:28.051949Z	   55 Prepare	insert into `user`(id,name,age,email) VALUE(?,?,?,?)
2021-07-09T11:42:28.061741Z	   55 Execute	insert into `user`(id,name,age,email) VALUE(222,'李四',32,'222@163.com')
2021-07-09T11:42:28.150331Z	   55 Prepare	insert into `user`(id,name,age,email) VALUE(?,?,?,?)
2021-07-09T11:42:28.150714Z	   55 Execute	insert into `user`(id,name,age,email) VALUE(333,'李四',32,'222@163.com')
2021-07-09T11:42:28.150720Z	   55 Quit

4.4. 开启缓存之后

开启缓存配置：cachePrepStmts=true
但是我这里不知道为啥还是进行了两次编译

//获取连接
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis_plus?useServerPrepStmts=true&cachePrepStmts=true&useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai","root","123456");
String sql = "insert into `user`(id,name,age,email) VALUE(?,?,?,?)";
//创建statement
statement= connection.prepareStatement(sql);
statement.setInt(1, 222);
statement.setString(2, "李四");
statement.setInt(3, 32);
statement.setString(4, "222@163.com");
statement.executeUpdate();
statement= connection.prepareStatement(sql);
statement.setInt(1, 333);
statement.setString(2, "李四");
statement.setInt(3, 32);
statement.setString(4, "222@163.com");
statement.executeUpdate();

查询日志

2021-07-09T11:47:55.777680Z	   60 Connect	root@localhost on mybatis_plus using TCP/IP
2021-07-09T11:47:55.782848Z	   60 Query	/* mysql-connector-java-8.0.19 (Revision: a0ca826f5cdf51a98356fdfb1bf251eb042f80bf) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2021-07-09T11:47:55.831666Z	   60 Query	SET NAMES utf8mb4
2021-07-09T11:47:55.832143Z	   60 Query	SET character_set_results = NULL
2021-07-09T11:47:55.833336Z	   60 Query	SET autocommit=1
2021-07-09T11:47:55.924786Z	   60 Prepare	insert into `user`(id,name,age,email) VALUE(?,?,?,?)
2021-07-09T11:47:55.940697Z	   60 Execute	insert into `user`(id,name,age,email) VALUE(222,'李四',32,'222@163.com')
2021-07-09T11:47:55.924799Z	   60 Prepare	insert into `user`(id,name,age,email) VALUE(?,?,?,?)
2021-07-09T11:47:56.112277Z	   60 Execute	insert into `user`(id,name,age,email) VALUE(333,'李四',32,'222@163.com')
2021-07-09T11:47:56.112279Z	   60 Quit

服务器编译优化记录

对项目编译优化过程中一些思路和脚本工具实现。对内存受限的编译环境有一些帮助。

工具：

https://github.com/wangxiaobai-dd/GccPrecompiledHeader

环境

32G内存，16核，Makefile，gcc9.2

效果

选择了代码比较多的三个目录进行预编译头优化， 进行下述操作。另外： 将小的编译单元合并成大的编译单元也是有效的，如 com.cpp 包含 a.cpp b.cpp , 在com.cpp 中首行也需要 #include "inc.h" ），编译时间从 24min30s左右 降到 14min 左右 ，节省 40%以上编译时间

方法

1. 梳理编译单元

编译时，我们每一个cpp文件将会生成对应的一个.o目标单元，将小的编译单元合并成大的编译单元对编译速度会有显著的提升（已有）。

类似上图，用n.cpp 包含 a.cpp，b.cpp，用m.cpp包含c.cpp，d.cpp，将 4 个编译单元变成 2 个编译单元。

注意，这里我认为应该将修改频率低的文件进行编译单元合并，如果将高修改频率的cpp文件放入合并后的编译单元，那么每次修改这个文件都会导致包含这个大编译单元重新编译。

另外，合并的编译单元尽可能地有一定关联度，可以按照业务功能来划分，或者通过一些数据统计来划分。

2. 应用gcc预编译头

按照图2，当a,cpp b.cpp c.cpp都包含temp.h头文件时，在编译的时候，temp.h会被解析三遍，分别与a，b，c结合生成.o文件。这样我们可以预先对头文件进行编译，生成temp.h.gch文件，这样只需要解析一次temp.h，原来的a.cpp,b.cpp,c.cpp文件仍然include temp.h就好了，只是要最先包含temp.h。

编译时 -H 可以看到cpp文件的依赖信息，应用gch后 !temp.h.gch

在应用gch时，我的方法是：

1. 先使用了include-what-you-use 这个开源工具，去除重复包含的头文件，前置声明替换（也是一种优化）
2. 分析路径下头文件的包含频率
3. 筛选包含频率高但是改动频率小的头文件，放在新的inc.h文件中
4. 剔除cpp文件包含的上一步的头文件，并在cpp中首行插入#include "inc.h"
5. 修改Makefile，修改依赖关系，编译时先对inc.h进行编译，生成最新的inc.h.gch
6. Makefile 可以加入 -Winvalid-pch，inc.h.gch 不可用时编译报错

工具

从上面第二步开始 ，以我的仓库里的代码举例 ，GchTool/TestTool/dirA.bak 是预编译头优化前， dirA 是优化后 ，大家可以对比参考。

一些脚本说明：

• 分析路径下头文件包含频率： CheckInclude.cpp  

使用：  ./CheckInclude TestTool/dirA  ，

生成 analyseInc.txt ， 供 ReplaceGCH.sh 脚本使用：

 analyseInc2.txt ，方便我们自己查看头文件包含频率 ：

• 可以根据 git 或者 svn 提交记录，手动从 analyseInc.txt 剔除改动频率大的头文件，（头文件包含应是递归包含的，当时没有考虑到嵌套问题）

• 使用预编译头替换脚本 ReplaceGch.sh  

使用：./ReplaceGCH.sh TestTool/dirA/ 3     取 analyseInc.txt  前 3 头文件，对 dirA 路径下，将这些头文件从 cpp 文件中删除，新建文件 inc.h 用于包含这些头文件，并且将 #include "inc.h" 插入刚才修改的 cpp 文件的首行

要处理的头文件数量，根据我们的实际项目规模来确定，这里举例是 3

向受到影响的 cpp 文件 插入 inc.h ，会调用 InsertInc 脚本：

自动创建 inc.h ：

• 修改 Makefile

修改 dirA/Makefile 依赖，保证每次编译先对 inc.h 进行编译 生成 inc.h.gch

修改 项目Makefile，支持多核编译（目录间多核，比如 dirA 对 inc.h 编译生成 inc.h.gch 时，其他目录仍然在编译）：

这里 targetA 是公共库目录 ，targetB、targetC 都要依赖 targetA，如 targetA 生成的静态库

对 inc.h 包含的头文件递归检查，刷新 inc.h 的时间戳，促使生成新的 inc.h.gch (比如inc.h 包含 common.h ，我们对 common.h 修改，gcc 并不认为 inc.h 有改动，因此我编写了脚本 gchcheck.py 做编译前检查)：

过程输出

进行编译优化后，发现在多核编译中， 目录A产生编译错误时，目录B不会停下来编译，会将错误信息刷屏，我们需要花很多时间向上滚屏翻记录，十分不友好；另外冗余的编译信息（如编译参数 链接参数 都可以在 Makefile 中查看）对我们用处不大。

于是编写脚本，在编译时收集编译信息，友好的展示出来： compliedisplay.py

代码与使用示例：

旧的编译信息输出：

修改后：

使用方法：

• 修改项目 Makefile

红框：调用脚本，我放在了targetA中调用，后台会fork子进程

绿框：ENTRY = ‘$@file’、DIR=$(DIRB)、  2>>$@error  将信息传给编译的子目录，编译信息会写入文件 targetAfile，错误信息写入文件 targetAerror ,  对于targetB 则是 targetBfile  targetBerror

黄框：表示 target 编译结束

• 修改子目录下 Makefile ，举例 dirA/Makefile

红框：开始编译文件

绿框：开始链接

注意在这些命令前加上@， 在 $(CXX) xxxx 前也加上，这样就不会打印这条执行语句了。

小结

现在3.16的cmake，可以对预编译头友好支持，无需手动处理预编译头文件中的依赖关系（target_precompile_headers）；也可以使用clang替换gcc；在内存足够、核心足够的机器上，预编译头可能是一种负担（如何筛选预编译的文件，标准库头文件）。

关于预编译机制：

第一次编译并保存这个预编译头状态比编译这些代码慢（时间代价20%-200%）；

重新加载已保存的状态很快；

足够内存的系统，预编译头文件机制速度比编译单个标准头文件快很多；

根据头文件使用频率和稳定性分层（结论一致）；

优化：(不行)

Plain Text incA.h :  // 变动频率最低，几乎不变的标准库 #include <iostream> #include <string> #include <vector> incB.h :  // 变动频率次低, 偶尔变化的头文件 #include <incA.h>  // 比如包含<iostream> <string> <vector> ，先生成 gchA 预编译头 #include <zCore.h> // 编译器从这里 先加载 gchA， 开始编译 incB.h 生产 gchB 预编译头 #include <zBase.h> main.cpp : #include <incB.h>  // 包含incB, 会导入 gchB