什么是MySQL的预编译？

た入场券 2023-02-25 07:11 70阅读 0赞

# 一、什么是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', '男', '南昌市西湖区');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70]

*  **定义预编译SQL语句：**

-- 定义一个预编译语句
    prepare name from statement; 
    
    prepare statement_1 from 'select * from user where id=?';

*  **设置参数值：**

set @id=1;

*  **执行预编译SQL语句：**

execute statement_1 using @id;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 1]

*  **释放预编译SQL语句：**

deallocate prepare statement_1;

# 三、使用PreparedStatement进行预编译 #

## 3.1 开启查询日志 ##

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

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

# 是否开启mysql日志 0:关闭(默认值) 1:开启
    general-log=1
    
    # mysql 日志的存放位置
    general_log_file="D:/query.log"

![在这里插入图片描述][20200710152438219.png]  
2）重启MySQL服务（要以管理员身份运行）：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 2]

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 3]

> 我们设置的是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();
    
    }

*  查看查询日志：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 4]

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

`jdbc:mysql://localhost:3306/mybatis?useServerPrepStmts=true&cachePrepStmts=true`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 5]

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

![在这里插入图片描述][20200710215714759.png]

# 五、总结 #

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

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

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

--------------------

References：

[https://www.iteye.com/blog/cs-css-1847772][https_www.iteye.com_blog_cs-css-1847772]

[https://blog.csdn.net/Marvel\_\_Dead/article/details/69486947][https_blog.csdn.net_Marvel_Dead_article_details_69486947]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70]: /images/20230209/1e35084f07c3408fb8ca55d619cc6a2a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 1]: /images/20230209/ffa12a86aede46db9973bf7b51b0e727.png
[20200710152438219.png]: https://img-blog.csdnimg.cn/20200710152438219.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200710152244537.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200710214100169.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200710215033329.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200710215207137.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70
[20200710215714759.png]: https://img-blog.csdnimg.cn/20200710215714759.png
[https_www.iteye.com_blog_cs-css-1847772]: https://www.iteye.com/blog/cs-css-1847772
[https_blog.csdn.net_Marvel_Dead_article_details_69486947]: https://blog.csdn.net/Marvel__Dead/article/details/69486947