1、SQL语句在数据库中的执行过程
一条SQL语句从客户端（如: java 程序、navicat工具、cmd命令行）发送到数据库管理系统后，要经历以下过程：
- 词法和语义的解析
- 优化SQL语句，制定执行计划
- 执行并返回结果
2、预编译语句
形如 select * from user where id = 1，这种普通语句叫做 Immediate Statement，执行时要经历完整的SQL执行过程
形如select * from user where id = ?, 使用占位符代替语句中字段的值，这种语句叫做Prepared Statement,执行时，首先要进行预编译，之后每次执行都省去了解析优化等过程。
预编译语句适用场景：一条SQL语句需要反复执行多次并且只有字段的值可能存在改变（如query的where子句值不同，update的set子句值不同,insert的values值不同）
二、MySQ数据库的预编译功能
1、开启数据库日志功能
为了更好的了解SQL语句的执行，需要开启mysql的查询日志功能。本文通过设置mysql全局变量方式开启，mysql服务重启后配置失效。
第一步：通过命令行，进入mysql客户端，输入命令： show variables like "general_%"; general_log = OFF 表明日志功能没有开启。
第二步：开启日志功能，输入命令： set global general_log = "ON"; 修改保存日志的文件（非必要操作），输入命令： set global general_log_file = "D:\mysql\dataquery01.log";
第三步： 查看是否开启成功，输入命令 show variables like "general_%";
2、执行mysql预编译命令
在执行相关操作前，需要创建一张表，作为操作对象。建表语句如下：
create table user(
id int auto_increment primary key,
username varchar(50),
password int(11)
)
(1) 编译
语法：PREPARE stmt_name FROM preparable_stmt
stmt_name: 个人理解，它和编译后的语句建立了映射，通过stmt_name可以找到要执行的语句
preparable_stmt: 要进行预编译的SQL语句
输入命令： prepare insert_user from "insert into user(username, password) values(?,?)";
（2）设置变量
语法： SET @var_name value
输入命令： set @username ="明月几时有";
set @password=10010;
(3)执行
语法：EXECUTE stmt_name [USING @var_name [, @var_name] …]
输入命令： execute insert_user using @username, @password;
查看执行结果：
查看日志文件：
（4）释放
语法：{DEALLOCATE | DROP} PREPARE stmt_name
即删除预编译语句。
预编译语句是会话级别的，会话一般可以理解为一次连接，当连接关闭后，预编译语句也会被释放
二、MySQL 驱动程序的预编译功能
首先，我们要知道Mysql驱动程序实现预编译功能有两种方式：驱动程序直接对sql语句进行预编译和驱动程序使用mysql数据库提供的预编译功能。（注意MySQL的老版本（4.1之前）是不支持服务端预编译的，但基于目前业界生产环境普遍情况，基本可以认为MySQL支持服务端预编译）。接下来将使用图解和源码分析mysql驱动的预编译功能的实现
1、部分继承关系图
如上图所示，当使用驱动器直接对sql语句进行编译（下文称此方式为：客户端预编译），编译后的sql语句保存在ParseInfo内部类中；当驱动程序使用mysql数据库提供的预编译功能（下文称此方式为：服务器端预编译），将服务器端预编译语句的ID保存在statementID中（前文中：prepare stmt_name FROM preparable_stmt ， 这里的ID相当于stmt_name, 也就是通过ID可以映射到服务器端的预编译语句）
在mysql中，使用连接对象的prepareStatement()方法实际返回的对象时JDBC42ServerPreparedStatement.
二、客户端与服务器端预编译及缓存
两个超级重要的参数（用在数据库连接URL中）：
useServerPrepStmts ：控制是否使用服务器端预编译功能，默认值false,即不使用服务器端预编译。
cachePrepStmts : 控制是否缓存预编译的相关信息,默认值false,即不使用缓存。
使用上述参数不同取值，预编译对象的获取流程基本如下
测试参数的使用代码如下：
DBUtils.java 用于获取连接,根据getConnection的参数：boolean useServerPrepStmts,和boolean cachePrepStmts决定是否使用服务器端预编译和缓存
public class DBUtils {
private static final String driver = "com.mysql.jdbc.Driver";
private static final String url = "jdbc:mysql://localhost:3306/imooc";
private static final Properties props = new Properties();
static {
props.setProperty("user", "root");
props.setProperty("password", "password");
props.setProperty("useUnicode", "true");
props.setProperty("characterEncoding", "UTF-8");
}
public static Connection getConnection(boolean useServerPrepStmts, boolean cachePrepStmts) {
Connection conn = null;
try {
Class.forName(driver);
if (useServerPrepStmts)
props.setProperty("useServerPrepStmts", Boolean.toString(useServerPrepStmts));
if (cachePrepStmts)
props.setProperty("cachePrepStmts", Boolean.toString(cachePrepStmts));
conn = DriverManager.getConnection(url, props);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return conn;
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
DatabaseProgram.java 测试程序，向前文中提到的user用户表中插入两条数据
public class DatabaseProgram {
public static void main(String[] args) throws SQLException {
try (Connection conn = DBUtils.getConnection(true, true);) {
insert(conn, "中国移动", 10086);
insert(conn, "中国联通", 10010);
}
}
private static void insert(Connection conn, String username, int password) throws SQLException {
String sql = "insert into user(username, password)" +
"values(?,?)";
try (PreparedStatement statement = conn.prepareStatement(sql)) {
statement.setString(1, username);
statement.setInt(2, password);
statement.executeUpdate();
}
}
}
1、使用客户端预编译
useServerPrepStmts参数为false;cachePrepStmts参数为false;
查看mysql执行日志文件，如下图所示，可以看出没有出现我们在第二节mysql数据库预编译功能日志中的prepare和execute命令
2、使用服务器端预编译
useServerPrepStmts参数为true;cachePrepStmts参数为false;
查看mysql执行日志文件，如下图所示，可以看出使用了服务器端预编
并且，我们注意到，由于没有使用缓存，虽然是相同的sql语句，这里进行了两次prepare(编译）,增加了不必要的开销。这里的没有缓存不是指数据库没有缓存预编译的sql语句，只有不手动释放（删除）或者是会话不关闭，预编译的语句就存在。在服务器端，问题是一个PreparedStatement对象对应一条服务器端预编译语句（statementID建立的映射关系），这里不缓存是指，在使用完PreparedStatement对象后没有把它放入缓存对象中，而是关闭了（close stmt可以看出）。那么进行第二次插入时，必须创建新的PreparedStatement对象，因此也要重新去预编译（prepare）
3、使用服务器端预编译+缓存
useServerPrepStmts参数为true;cachePrepStmts参数为true;
查看mysql执行日志文件，如下图所示，解决了要重新预编译的问题。
四、效率测试
接下来要测试客户端、服务器端和缓存组合的预编译对象PreparedStatement以及Statement向数据库中插入1000条数据3次，使用的平均时间。
注：测试时最好关闭数据库日志功能，记录执行日志比较耗时。
测试代码如下：
public class Main {
public static final int executeCount = 3;
public static long test(int insertCount,boolean useServerPrepStmts, boolean cachePrepStmts) throws SQLException{
long result = 0;
String sql = "insert into user(username, password) values(?,?)";
try (Connection conn = DBUtils.getConnection(useServerPrepStmts,cachePrepStmts);
PreparedStatement pstat = conn.prepareStatement(sql)) {
long start = System.currentTimeMillis();
for (int i = 0; i < insertCount; i ++){
pstat.setString(1, "明月几时有");
pstat.setInt(2, 123456);
pstat.executeUpdate();
}
long end = System.currentTimeMillis();
result = Math.abs(end - start);
}
return result;
}
public static void main(String[] args) throws SQLException{
long totalTime = 0;
for (int i = 0; i < executeCount; i ++) {
totalTime = totalTime + test(1000,false,false);
}
System.out.println(totalTime/3);
}
}
| 情况 | 3次平均耗时（s） |
|---|---|
| 客户端 | 44.922 |
| 客户端+缓存 | 36.154 |
| 服务器端 | 39.543 |
| 服务器端+缓存 | 35.162 |
| statement语句 |
这个数据还是不准确，有太多影响因素，电脑环境等等，为降低影响每次执行程序后，在mysql客户端cmd命令行执行truncate user重新建表，然而在次运行它们的相对排序还是会变，但总体上来讲使用客户端时间最长，服务器端+缓存时间最少（忽略statement）