sql 预编译语句
1. 背景
本文重点讲述MySQL中的预编译语句并从MySQL的Connector/J源码出发讲述其在Java语言中相关使用。
注意：文中的描述与结论基于MySQL 5.7.16以及Connect/J 5.1.42版本。
2. 预编译语句是什么
通常我们的一条sql在db接收到最终执行完毕返回可以分为下面三个过程：
- 词法和语义解析
- 优化sql语句，制定执行计划
- 执行并返回结果
我们把这种普通语句称作Immediate Statements。
但是很多情况，我们的一条sql语句可能会反复执行，或者每次执行的时候只有个别的值不同（比如query的where子句值不同，update的set子句值不同,insert的values值不同）。
<mark>如果每次都需要经过上面的词法语义解析、语句优化、制定执行计划等，则效率就明显不行了。</mark>
所谓预编译语句就是将这类语句中的值用占位符替代，可以视为将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 编译
我们接下来通过 <mark>PREPARE stmt_name FROM preparable_stm</mark>的语法来预编译一条sql语句
mysql> prepare ins from 'insert into t select ?,?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
3.2 执行
我们通过<mark>EXECUTE stmt_name [USING @var_name [, @var_name] …]的</mark>语法来执行预编译语句
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 释放
如果我们想要释放一条预编译语句，则可以使<mark>用{DEALLOCATE | DROP} PREPARE stmt_name的</mark>语法进行操作:
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)
这里有两个很重要的参数<mark>useServerPrepStmts</mark>以及<mark>emulateUnsupportedPstmts</mark>用于控制是否使用服务端预编译语句。
由于上述程序中我们<mark>没有启用服务端预编译</mark>，因此MySQL驱动在上面的prepareStatement方法中会进入使用客户端本地预编译的分支进入如下所示的clientPrepareStatement方法。
上图截自com.mysql.jdbc.ConnectionImpl#clientPrepareStatement(java.lang.String, int, int, boolean)
而我们上面的程序中也没有通过<mark>cachePrepStmts</mark>参数启用缓存，因此会通过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 通过服务端预编译的情况
接下来，将上述程序中的连接串改为<mark>jdbc:mysql://localhost/test?useServerPrepStmts=true</mark>,其余部分不作变化，清理表数据，重新执行上述程序，我们会在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
从上面的日志中，我们可以很清楚地看到<mark>Prepare, Execute, Close</mark>几个command，显然MySQL服务器为我们预编译了语句。
我们仅仅通过<mark>useServerPrepStmts</mark>开启了服务端预编译，由于未开启缓存，因此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服务端预编译也好，本地预编译也好，都会对同一种语句重复预编译。因此为了提升效率，往往我们需要启用缓存，通过设置连接中<mark>cachePrepStmts</mark>参数就可以控制是否启用缓存。此外通过<mark>prepStmtCacheSize</mark>参数可以控制缓存的条数，MySQL驱动默认是25，通常实践中都在250-500左右；通过<mark>prepStmtCacheSqlLimit</mark>可以控制长度多大的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语句不需要开启预编译等。
京公网安备 11010502036488号