一、什么是MySQL的预编译？

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

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

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

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

二、 如何使用预编译？

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=?';

• 设置参数值：

set @id=1;

• 执行预编译SQL语句：

execute statement_1 using @id;

• 释放预编译SQL语句：

deallocate prepare statement_1;

三、使用PreparedStatement进行预编译

3.1 开启查询日志

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

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

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

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

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

net stop mysql

net start 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.1 预编译性能测试

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

• 示例：

jdbc:mysql://localhost:3306/test?&useServerPrepStmts=true

• 预编译性能测试：

package com.dfbz.demo;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @author lscl
 * @version 1.0
 * @intro: 预编译性能测试
 */
public class Demo13_PreparedStatement_预编译 {

    /**
     * 预编译性能测试
     * @throws SQLException
     */
    @Test
    public void test1() throws SQLException {
        
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useServerPrepStmts=true", "root", "admin");
        
        long startTime = System.currentTimeMillis();

        PreparedStatement ps =  connection.prepareStatement("select * from user where id = ?");
        
        for (int i = 0; i < 20000; i++) {

            ps.setString(1, "1");
            // 执行查询
            ps.executeQuery();
        }

        long endTime = System.currentTimeMillis();
        System.out.println(endTime - startTime);

        ps.close();
    }

}

• 下面是测试报告（2W次查询）：

次数	开启预编译	关闭预编译
第一次	1131ms	1240ms
第二次	1158ms	1244ms
第三次	1132ms	1300ms
第四次	1142ms	1216ms

通过上面表格可以看出，开启预编译之后查询性能能够得到一定的提升；

4.2 预编译的参数

另外，MySQL中的 max_prepared_stmt_count 变量用来控制全局最大存储的预编译语句数：

show variables like '%max_prepared_stmt_count%';

4.3 预编译的优缺点

对于频繁使用的语句，使用服务端 “预编译” 还是能够得到提升的。但是对于不频繁使用的语句，服务端预编译本身会增加额外的耗时，**还会增加MySQL的使用内存。**因此在实际开发中具体是否要开启预编译要根据情况而定；

例如下面测试案例：

@Test
public void test2() throws SQLException {

    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useServerPrepStmts=true", "root", "admin");

    long startTime = System.currentTimeMillis();
    PreparedStatement ps = null;

    for (int i = 0; i < 10000; i++) {
        // 每次都预编译
        ps = connection.prepareStatement("select * from user where id = ?");
        
        ps.setString(1, "1");
        // 执行查询
        ps.executeQuery();
    }

    long endTime = System.currentTimeMillis();
    System.out.println(endTime - startTime);

    ps.close();
}

• 测试报告（1W次查询）：

次数	开启预编译	关闭预编译
第一次	2120ms	1340ms
第二次	2183ms	1333ms
第三次	2160ms	1310ms
第四次	2130ms	1310ms

可以看出如果每次都是发送独立的SQL语句，开启预编译之后性能反而性能有所下降；

五、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查询日志：

六、总结

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

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

3）MySQL的预编译语句数量是有限制的，max_prepared_stmt_count 变量用来控制全局最大存储的预编译语句数，默认为16382。

4）对于频繁使用的语句，使用服务端 “预编译” 还是能够得到提升的。但是对于不频繁使用的语句，服务端预编译本身会增加额外的耗时，**还会增加MySQL的使用内存。**因此在实际开发中具体是否要开启预编译要根据情况而定；

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

---

References：

https://www.iteye.com/blog/cs-css-1847772

https://blog.csdn.net/Marvel__Dead/article/details/69486947