MySql优化-预编译和批处理（详解举例）

预编译

预编译的好处

Mysql数据库有预编译功能。什么是预编译功能呢？它有什么好处呢？

当客户发送一条SQL语句给服务器后，服务器总是需要校验SQL语句的语法格式是否正确，然后把SQL语句编译成可执行的函数，最后才是执行SQL语句。其中校验语法，和编译所花的时间可能比执行SQL语句花的时间还要多。
 
如果我们需要执行多次insert语句，但只是每次插入的值不同，MySQL服务器也是需要每次都去校验SQL语句的语法格式，以及编译，这就浪费了太多的时间。如果使用预编译功能，那么只对SQL语句进行一次语法校验和编译，之后更换变量的值会直接执行，所以效率要高。

 

MySQL界面执行预编译

MySQL执行预编译分为如三步：
执行预编译语句
例如：

• prepare myfun from 'select * from t_book where bid=?'
• 设置变量，例如：set @str='b1'
• 执行语句，例如：execute myfun using @str

如果需要再次执行myfun，那么就不再需要第一步，即不需要再编译语句了：

• 设置变量，例如：set @str='b2'
• 执行语句，例如：execute myfun using @str
   
   

JDBC驱动执行预编译

使用Statement执行预编译（了解）

使用Statement执行预编译就是把上面的SQL语句执行一次。

Connection con = JdbcUtils.getConnection();
Statement stmt = con.createStatement();
stmt.executeUpdate("prepare myfun from 'select * from t_book where bid=?'");
stmt.executeUpdate("set @str='b1'");
ResultSet rs = stmt.executeQuery("execute myfun using @str");
while(rs.next()) {
    System.out.print(rs.getString(1) + ", ");
    System.out.print(rs.getString(2) + ", ");
    System.out.print(rs.getString(3) + ", ");
    System.out.println(rs.getString(4));
}

stmt.executeUpdate("set @str='b2'");
rs = stmt.executeQuery("execute myfun using @str");

while(rs.next()) {
    System.out.print(rs.getString(1) + ", ");
    System.out.print(rs.getString(2) + ", ");
    System.out.print(rs.getString(3) + ", ");
    System.out.println(rs.getString(4));
}

rs.close();
stmt.close();
con.close();

 
 

使用PreparedStatement执行预编译(重点掌握)

PreparedStatement会根据是否打开预编译功能和缓存来自动进行对应操作。

未开启预编译

首先打开mysql日志功能SET GLOBAL general_log = 'ON';便于观察实验结果

测试代码（插入一条数据，未开启预编译功能）

 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", "123")) {
            String sql = "insert into t select ?,?";
            PreparedStatement statement = con.prepareStatement(sql);

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

            statement.close();
        }
    }

日志结果：（未进行预编译）

 
 

打开预编译功能

useServerPrepStmts参数

默认使用PreparedStatement是不能执行预编译的，这需要在url中给出useServerPrepStmts=true参数（MySQL Server 4.1之前的版本是不支持预编译的，而Connector/J在5.0.5以后的版本，默认是没有开启预编译功能的）。
例如：jdbc:mysql://localhost:3306/test?useServerPrepStmts=true
　　这样才能保证mysql驱动会先把SQL语句发送给服务器进行预编译，然后在执行executeQuery()时只是把参数发送给服务器。

 public static void main(String[] args) throws Throwable {
        Class.forName("com.mysql.jdbc.Driver");
														更改了url参数
        String url = "jdbc:mysql://localhost/test?useServerPrepStmts=true";
        try (Connection con = DriverManager.getConnection(url, "root", "123")) {
            String sql = "insert into t select ?,?";
            PreparedStatement statement = con.prepareStatement(sql);

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

            statement.close();
        }
    }

可以看到，Prepare、Execute表示已经成功开启预编译

同一PreparedStatement插入多条语句测试：

public static void main(String[] args) throws Throwable {
        Class.forName("com.mysql.jdbc.Driver");

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

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

            statement.setInt(1, 5555);
            statement.setString(2, "eeee");
            statement.executeUpdate();

            statement.close();
        }
    }

一次编译，多次执行，成功！：

设置了useServerPrepStmts参数后同一PreparedStatement对象使用同意sql语句模板只需编译一次！

 
 

打开缓存功能

cachePrepStmts参数

当使用不同的PreparedStatement对象来执行相同的SQL语句时，还是会出现编译两次的现象，这是因为驱动没有缓存编译后的函数key，导致二次编译。如果希望缓存编译后函数的key，那么就要设置cachePrepStmts参数为true。
例如：
jdbc:mysql://localhost:3306/test?useServerPrepStmts=true&cachePrepStmts=true

两个PreparedStatement对象使用相同sql模板insert into t select ?,?
 

不开启缓存----测试代码：

public static void main(String[] args) throws Throwable {
        Class.forName("com.mysql.jdbc.Driver");

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

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

            PreparedStatement statement2 = con.prepareStatement(sql);
            statement2.setInt(1, 5555);
            statement2.setString(2, "eeee");
            statement2.executeUpdate();

            
            statement2.close();
        }
    }

输出日志：

可以看到，不开启缓存，使用不同的PreparedStatement对象来执行相同的SQL语句时，还是会出现编译两次的现象。

开启缓存----测试代码：

public static void main(String[] args) throws Throwable {
        Class.forName("com.mysql.jdbc.Driver");
																	再次改动url参数
   		String url = "jdbc:mysql://localhost/test?useServerPrepStmts=true&cachePrepStmts=true";
        try (Connection con = DriverManager.getConnection(url, "root", "123")) {
            String sql = "insert into t select ?,?";
            PreparedStatement statement1 = con.prepareStatement(sql);

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

            PreparedStatement statement2 = con.prepareStatement(sql);
            statement2.setInt(1, 5555);
            statement2.setString(2, "eeee");
            statement2.executeUpdate();

            statement2.close();
        }
    }

输出日志：

可以看到，开启缓存，使用不同的PreparedStatement对象来执行相同的SQL语句时，只会编译一次。

 
 

注意

实验中发现：
 
使用不同PreparedStatement对象来执行相同的SQL语句时，PreparedStatement对象是要顺序关闭的，也就是一个连接Connection同时只能有一个PreparedStatement对象是有效的如上述例子中一样。
 
如果不是顺序关闭，而是同时打开两个PreparedStatement对象，执行相同的SQL语句，那么依然会编译两次（打开了缓存和预编译）。

例如：

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", "123")) {
            String sql = "insert into t select ?,?";
            PreparedStatement statement1 = con.prepareStatement(sql);

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


            PreparedStatement statement2 = con.prepareStatement(sql);
            statement2.setInt(1, 5555);
            statement2.setString(2, "eeee");
            statement2.executeUpdate();

			//最后统一关闭！！！
            statement1.close();
            statement2.close();
        }

输出日志：（预编译两次！）

我的理解是，每个PreparedStatement对象都绑定一条SQL模板，当开启预编译和缓存后，缓存区内会保存有预编译后的SQL模板，当我们关闭一个PreparedStatement对象后，它所对应的SQL模板就空了出来，新建的PreparedStatement会根据sql语句在缓冲区中寻找空闲的可匹配的模板，匹配到就不会进行二次编译，而如果我们保持第一个PreparedStatement不关闭，则它对应的Sql模板不会空闲，再创建第二个PreparedStatement即使使用相同模板，但由于没有空闲，所以也会二次编译。
 
这就要求我们不会同时打开两个PreparedStatement而使用相同的模板，很明显同时打开两个PreparedStatement却使用相同的模板是不合乎情理的。

批处理

Statement批处理（了解）

批处理就是一批一批的处理，而不是一个一个的处理！
当你有10条SQL语句要执行时，一次向服务器发送一条SQL语句，这么做效率上很差！处理的方案是使用批处理，即一次向服务器发送多条SQL语句，然后由服务器一次性处理。
批处理只针对更新（增、删、改）语句，批处理没有查询什么事儿！

可以多次调用Statement类的addBatch(String sql)方法，把需要执行的所有SQL语句添加到一个“批”中，然后调用Statement类的executeBatch()方法来执行当前“批”中的语句。

• void addBatch(String sql)：添加一条语句到“批”中；
• int[] executeBatch()：执行“批”中所有语句。返回值表示每条语句所影响的行数据；
• void clearBatch()：清空“批”中的所有语句。

当执行了“批”之后，“批”中的SQL语句就会被清空！也就是说，连续两次调用executeBatch()相当于调用一次！因为第二次调用时，“批”中已经没有SQL语句了。

PreparedStatement批处理

PreparedStatement的批处理有所不同，因为每个PreparedStatement对象都绑定一条SQL模板。所以向PreparedStatement中添加的不是SQL语句，而是给“?”赋值。

			String sql = "insert into stu values(?,?,?,?)";
			pstmt = con.prepareStatement(sql);
			for(int i = 0; i < 10; i++) {
				pstmt.setString(1, "S_10" + i);
				pstmt.setString(2, "stu" + i);
				pstmt.setInt(3, 20 + i);
				pstmt.setString(4, i % 2 == 0 ? "male" : "female");
				pstmt.addBatch() ; //添加进批
			}
			pstmt.executeBatch (); //批处理

打开批处理

MySQL的批处理也需要通过参数来打开：rewriteBatchedStatements=true