背景
本文重点讲述MySQL中的预编译语句并从MySQL的Connector/J源码出发讲述其在Java语言中相关使用。
注意：文中的描述与结论基于MySQL 5.7.16以及Connect/J 5.1.42版本。
预编译语句是什么
通常我们的一条sql在db接收到最终执行完毕返回可以分为下面三个过程：
词法和语义解析
优化sql语句，制定执行计划
执行并返回结果
我们把这种普通语句称作Immediate Statements。
但是很多情况，我们的一条sql语句可能会反复执行，或者每次执行的时候只有个别的值不同（比如query的where子句值不同，update的set子句值不同,insert的values值不同）。
如果每次都需要经过上面的词法语义解析、语句优化、制定执行计划等，则效率就明显不行了。
所谓预编译语句就是将这类语句中的值用占位符替代，可以视为将sql语句模板化或者说参数化，一般称这类语句叫Prepared Statements或者Parameterized Statements
预编译语句的优势在于归纳为：一次编译、多次运行，省去了解析优化等过程；此外预编译语句能防止sql注入。
当然就优化来说，很多时候最优的执行计划不是光靠知道sql语句的模板就能决定了，往往就是需要通过具体值来预估出成本代价。
MySQL服务端预编译
注意MySQL的老版本（4.1之前）是不支持服务端预编译的，但基于目前业界生产环境普遍情况，基本可以认为MySQL支持服务端预编译。
下面我们来看一下MySQL中预编译语句的使用。
首先我们有一张测试表t，结构如下所示：
mysql> show create table t\G*************************** 1. row *************************** Table: tCreate 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)
编译
我们接下来通过 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
执行
我们通过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: 0mysql> 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会报如上所示的错误。
释放
如果我们想要释放一条预编译语句，则可以使用{DEALLOCATE | DROP} PREPARE stmt_name的语法进行操作:
mysql> deallocate prepare ins; Query OK, 0 rows affected (0.00 sec)
客户端预编译
以上所描述的我们称之为服务端预编译，也就是MySQL服务端做了预编译+缓存sql语句这件事情。接下来我们以MySQL Java驱动Connector/J（版本5.1.42）为例来介绍客户端预编译。
首先，简要提一下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_timeout2017-07-04T16:39:17.642476Z 19 Query SET character_set_results = NULL2017-07-04T16:39:17.643212Z 19 Query SET autocommit=12017-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的类继承关系图如下所示：
以上介绍的是默认不开启服务预编译及缓存的情况。
通过服务端预编译的情况
接下来，将上述程序中的连接串改为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_timeout2017-07-04T16:42:23.261345Z 22 Query SET character_set_results = NULL2017-07-04T16:42:23.262127Z 22 Query SET autocommit=12017-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_timeout2017-07-05T16:04:45.834672Z 76 Query SET character_set_results = NULL2017-07-05T16:04:45.835183Z 76 Query SET autocommit=12017-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 stmt2017-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 stmt2017-07-05T16:04:45.904312Z 76 Quit
使用缓存的情况
在类似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这样。
服务端预编译+缓存
接下来，将测试程序中的连接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);
http://www.cnblogs.com/micrari/p/7112781.html延伸阅读
- 告别“老顽固” 2023-04-18 11:15:46.437
- 时光飞逝 2024-03-26 11:25:53.823
- 会当凌绝顶，一览众山小 2023-04-18 10:38:34.08
- 自豪 2023-04-18 10:31:27.963
- 绽放 2023-04-18 10:30:19.243
- 毕业有感 2023-04-18 10:27:54.643
- 投入六个月的经历，收获了整个人生 2023-04-18 10:18:55.813
- 非常响亮的名字 2023-04-18 10:10:46.923
- 不可磨灭的力量 2023-04-18 10:06:02.717
- 困难是把双刃剑 2023-04-18 10:04:30.883
告别“老顽固”