一文說透什麼是MySQL的預編譯

2022-11-15 14:00:27

一、什麼是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

四、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查詢紀錄檔：