当前位置:   article > 正文

MySql优化-预编译和批处理(详解举例)_java mysql预编译进行哪些处理

java 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();
  • 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

 
 

使用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();
        }
    }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

日志结果:(未进行预编译)
在这里插入图片描述

 
 

打开预编译功能

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();
        }
    }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

可以看到,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();
        }
    }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

一次编译,多次执行,成功!:
在这里插入图片描述

设置了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();
        }
    }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23

输出日志:
在这里插入图片描述

可以看到,不开启缓存,使用不同的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();
        }
    }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

输出日志:
在这里插入图片描述

可以看到,开启缓存,使用不同的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();
        }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

输出日志:(预编译两次!)
在这里插入图片描述

我的理解是,每个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 (); //批处理

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

打开批处理

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

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/凡人多烦事01/article/detail/425510
推荐阅读
相关标签
  

闽ICP备14008679号