MySQL 预编译

1、什么是MySQL的预编译？

我们发送一条 SQL 语句给 MySQL 服务器时，MySQL 服务器每次都会对这条 SQL 语句进行校验、解析等操作。

有很多情况，一条 SQL 语句可能需要反复的执行，而 SQL 语句也只可能传递的参数不一样，这样的 SQL 语句如果每次都进行校验、解析等操作，有些太过于浪费性能了，因此提出了 SQL 语句的预编译。

所谓预编译就是将一些灵活的参数值以占位符 ? 的形式给代替掉，我们把参数值给抽取出来，把 SQL 语句进行模板化。让 MySQL 服务器执行相同的SQL语句时，不需要在校验、解析 SQL 语句上面花费重复的时间。

预编译其实就是来提高我们的查询速度的，并不是大家心里想的那个”预编译”

2、如何使用预编译？

2.1、MySQL 预编译的语法

准备数据：

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名称',
  `birthday` datetime(0) NULL DEFAULT NULL COMMENT '生日',
  `sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
  `address` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '小龙', '2019-02-27 17:47:08', '男', '南昌市西湖区');
INSERT INTO `user` VALUES (2, '小刚', '2019-03-02 15:09:37', '男', '南昌市东湖区');
INSERT INTO `user` VALUES (3, '小兰', '2019-03-04 11:34:34', '女', '南昌市青山湖区');
INSERT INTO `user` VALUES (4, '小红', '2019-03-04 12:04:06', '女', '南昌市青云谱区');
INSERT INTO `user` VALUES (5, '小丽', '2019-03-07 17:37:26', '女', '南昌市红谷滩区');
INSERT INTO `user` VALUES (6, '小明', '2019-03-08 11:44:00', '男', '南昌市新建区');
INSERT INTO `user` VALUES (7, '龙龙', '2019-04-08 11:44:00', '男', '南昌市西湖区');

定义预编译SQL语句：

-- 定义一个预编译语句
prepare name from statement; 

prepare statement_1 from 'select * from user where id=?';

设置参数值：
1
set @id=1;
执行预编译SQL语句：
1
execute statement_1 using @id;
释放预编译SQL语句：
1
deallocate prepare statement_1;

3、使用 PreparedStatement 进行预编译

3.1、开启查询日志

为了方便测试，我们打开 MySQL 的查询日志：

在MySQL配置文件中的 [mysqld] 下增加如下配置：

# 是否开启 mysql 日志 0:关闭(默认值) 1:开启
general-log=1

# mysql 日志的存放位置
general_log_file="D:/query.log"

重启 MySQL 服务（要以管理员身份运行）：

3.2、开启预编译功能

PreparedStatement 的预编译功能默认是关闭的，要让其生效，必须在 JDBC 连接的 URL 设置 useServerPrepStmts=true，让其打开。

如下所示：jdbc:mysql://localhost:3306/mybatis?&useServerPrepStmts=true

测试代码：

package com.lscl.test;
import org.junit.Test;
import java.sql.*;

public class Demo01 {
    @Test
    public void test1() throws Exception {
        // 获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?useServerPrepStmts=true", "root", "admin");
        String sql = "select * from user where id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, 1);
        // 执行查询,获取结果集
        ResultSet rs = ps.executeQuery();
        //遍历查询结果集
        while (rs.next()) {
            System.out.println(rs.getObject("id")+"---"+rs.getObject("username"));
        }
        rs.close();
        ps.close();
    }
}

查看 MySQL 的查询日志：

我们设置的是 MySQL 连接参数，目的是告诉 MySQL JDBC 的 PreparedStatement 使用预编译功能（5.0.5之后的 JDBC 驱动版本需要手动开启，而之前的默认是开启的）

3.3、cachePrepStmts 参数

当使用不同的 PreparedStatement 对象来执行相同的 SQL 语句时，还是会出现编译两次的现象，我们可以开启”预编译缓存”，来实现”一次编译，到处运行”（要是同一个Connection）

开启预编译缓存：cachePrepStmts=true;

url连接：jdbc:mysql://localhost:3306/mybatis?useServerPrepStmts=true&cachePrepStmts=true

测试代码（没有开启缓存）：

@Test
public void test1() throws Exception {

    // 获取连接
    // Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?useServerPrepStmts=true&cachePrepStmts=true", "root", "admin");
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?useServerPrepStmts=true", "root", "admin");

    String sql = "select * from user where id = ?";
    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setInt(1, 1);
    // 执行查询,获取结果集
    ResultSet rs = ps.executeQuery();
    //遍历查询结果集
    while (rs.next()) {
        System.out.println(rs.getObject("id")+"---"+rs.getObject("username"));
    }
    // 关闭对象连接
    rs.close();
    ps.close();
    ps = connection.prepareStatement(sql);
    ps.setInt(1, 1);
    // 执行查询,获取结果集
    rs = ps.executeQuery();
    //遍历查询结果集
    while (rs.next()) {
        System.out.println(rs.getObject("id")+"---"+rs.getObject("username"));
    }
    rs.close();
    ps.close();
}

查看查询日志：

开启预编译缓存测试（在url连接上加上 cachePrepStmts=true）：

jdbc:mysql://localhost:3306/mybatis?useServerPrepStmts=true&cachePrepStmts=true

4、Statement 是否具备预编译功能？

Statement 不具备预编译功能

测试代码：

@Test
public void test2() throws Exception {

    // 获取连接
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?useServerPrepStmts=true&cachePrepStmts=true", "root", "admin");
    String sql = "select * from user where id = 1";
    Statement statement = connection.createStatement();
    // 执行查询,获取结果集
    ResultSet rs = statement.executeQuery(sql);
    //遍历查询结果集
    while (rs.next()) {
        System.out.println(rs.getObject("id")+"---"+rs.getObject("username"));
    }
    rs.close();
    statement.close();
}

查看MySQL查询日志：

5、总结

预编译是用来提升 SQL 语句的响应速度的，将一段SQL语句定制成模板，把灵活的参数作为占位符让我们传递进去，达到多次执行相同的SQL语句必须要重复校验、解析等操作；

默认的情况下，PreparedStatement 是没有开启预编译的，需要我们在连接的 url 参数上指定 useServerPrepStmts=true 参数开启，并且预编译是支持”缓存”的，我们可以通过参数 cachePrepStmts=true 来设置；

statement 是不支持预编译的，即使设置了 useServerPrepStmts=true 也不管用；

6、注意

如果开启预编译功能的话，还要开启客户端缓存功能，否则有害无利。但是即使开了预编译功能也不会对程序带来多大改进，因为 MySQL 的服务端预编译是 session 级别的，也就是说除非你的单个 connection 进行了大量的重复某个 SQL 这个预编译功能才有意义。

5.7 用户手册里有写明

8.10.4 Caching of Prepared Statements and Stored Programs

......

The server maintains caches for prepared statements and stored programs on a per-session basis.

Statements cached for one session are not accessible to other sessions. When a session ends, the

server discards any statements cached for it .

....................

最后一句的意思大概就是当 session 结束，那么 server 会扔掉所有的 statements 缓存

MySQL

#MySQL

MySQL 预编译

https://flepeng.github.io/041-MySQL-41-底层原理-MySQL-预编译/

作者

Lepeng

发布于

2020年8月8日

许可协议

MySQL 隐式转换上一篇

SQL 语句执行过程下一篇