文章目录
- 1、什么是预编译
- 1.1、 sql的执行过程
- 1.2. 预编译语句
- 1.3. 预编译
- 1.4. 参考博客
- 2、mysql的预编译功能
- 2.1. 预编译测试表
- 2.2. 创建预编译语句
- 2.3. 执行
- 2.4. 预编译缓存
- 2.5、释放预编译语句
- 3、MYSQL驱动编译——客户端预编译
- 3.1. 执行的jdbc连接代码
- 3.2. 查询sql执行的日志
- 3.3、查询源码寻找问题
- 4、MYSQL驱动编译——服务器端预编译
- 4.1. 将mysql的url连接开启useServerPrepStmts=true
- 4.2. 执行sql语句，查询mysql通用日志
- 4.3. 插入两次数据
- 4.4. 开启缓存之后
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注入。
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.002s3、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 Quit4.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
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
