MySQL 寫入壓測幾種方式

最近跟在粉絲群先聊到一個問題，資料庫的寫入方式，最多能寫入多少行資料。經過一些網路搜尋和查詢，據悉 MySQL 單表插入極限是 3w~5w。

席間大家也都說了幾種寫入方式，其中有一個非壓測的方式在可查閱的資料中是最厲害的，據悉是程式寫入的 20 倍以上效能，就是 load data。

MySQL 的 LOAD DATA 語句是一種用於高效匯入大量資料到資料庫表中的命令。它從一個純文字檔案（通常是 CSV 檔案）中讀取資料，然後將其批次插入到指定的資料庫表中。這種方式比逐行插入效率更高，特別適合於匯入大資料集。

其中使用場景如下：

• 批次資料匯入：特別適合於從外部系統遷移資料到 MySQL。
• 資料備份與恢復：可以快速匯入匯出的資料檔案。
• 資料初始化：在應用部署初期，從預定義的資料檔案中載入初始資料。

這種開掛的方式暫時不列入本次實驗範圍了，主要無法使用壓測方式控制壓力大小，不太適合做寫入的效能測試。

下面我列舉幾種常見的 MySQL 寫入方式，並簡單測試寫入效能。本文只分享單執行緒的方案，至於效能

測試準備

首先本地建立一個 MySQL 服務，預設引數，沒有任何最佳化。其次建立一張表，建表語句如下：

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '使用者ID',
  `name` varchar(32) DEFAULT 'FunTester' COMMENT '使用者名稱',
  `age` int NOT NULL COMMENT '使用者年齡',
  `level` int DEFAULT NULL COMMENT '使用者等級',
  `region` varchar(32) DEFAULT '小八超市' COMMENT '使用者所在地區',
  `address` varchar(512) DEFAULT NULL COMMENT '使用者地址',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

其次測試方法，就是使用 MySQL 語句統計每秒的寫入行數。看最大每秒的寫入數量。

SELECT COUNT(*),create_time FROM user group by create_time;

每次測試結束之後，清理表：

TRUNCATE TABLE user;

while 迴圈

使用 JDBC 連線 MySQL 服務，然後使用單執行緒 while 迴圈往資料庫裡面寫入資料。這應該是最常見、最簡單的一種方式，理論上也是效能最差的一種方式，雖然我使用預編譯的功能，但實際上也是提升了客戶端的效能，並不能對服務端的效能造成影響。

指令碼如下：

package com.funtest.mysql

import com.funtester.db.mysql.FunMySql
import com.funtester.frame.SourceCode
import com.funtester.utils.StringUtil
/**
 * 透過 JDBC 向 MySQL 資料庫寫入資料
 */
class MysqlWriteWhile extends SourceCode {

    public static void main(String[] args) {
        String sqlFormat = "insert into user (name, age, level, region, address) values (?, ?, ?, ?, ?)";
        String ipPort = "127.0.0.1:3306";// 服務端地址
        String database = "funtester"// 服務端地址
        String user = "root";// 使用者名稱
        String password = "funtester";// 密碼
        def base = new FunMySql(ipPort, database, user, password);// 建立資料庫操作基礎類
        def preparedStatement = base.connection.prepareStatement(sqlFormat);// 預編譯 SQL 語句
        while (true) {
            String name = StringUtil.getString(16);// 隨機生成姓名
            int age = getRandomInt(100);// 隨機生成年齡
            int level = getRandomInt(10);// 隨機生成等級
            String region = StringUtil.getString(32);// 隨機生成地區
            String address = StringUtil.getString(512);// 隨機生成地址
            preparedStatement.setString(1, name);// 設定引數
            preparedStatement.setInt(2, age);// 設定引數
            preparedStatement.setInt(3, level);// 設定引數
            preparedStatement.setString(4, region);// 設定引數
            preparedStatement.setString(5, address);// 設定引數
            preparedStatement.executeUpdate();// 執行插入語句
        }
        preparedStatement.close();// 關閉資源
        base.close();// 關閉資源
    }
}

測試結果如下：

行數	分鐘	秒
6374	12	0
6197	12	1
6156	12	2
6176	12	3
6332	12	4
6545	12	5
7088	12	6
7309	12	7
7408	12	8
6099	12	9

看來 7k 多一些，這一點比我之前的一次結果好很多。

JDBC 批處理

一開始我也覺得 JDBC 批處理也是可以大幅提升效能的，如果有同樣的想法小夥伴，可以先不用著急，所以我們先來看看測試程式。

package com.funtest.mysql  

import com.funtester.db.mysql.FunMySql  
import com.funtester.frame.SourceCode  
import com.funtester.utils.StringUtil  
/**  
 * 透過 JDBC 向 MySQL 資料庫寫入資料  
 */  
class MysqlWriteBatch extends SourceCode {  

    public static void main(String[] args) {  
        String sqlFormat = "insert into user (name, age, level, region, address) values (?, ?, ?, ?, ?)";  
        String ipPort = "127.0.0.1:3306";// 服務端地址  
        String database = "funtester"// 服務端地址  
        String user = "root";// 使用者名稱  
        String password = "funtester";// 密碼  
        def base = new FunMySql(ipPort, database, user, password);// 建立資料庫操作基礎類  
        def preparedStatement = base.connection.prepareStatement(sqlFormat);// 預編譯 SQL 語句  
        while (true) {  
            for (int j = 0; j < 10; j++) {  
                String name = StringUtil.getString(16);// 隨機生成姓名  
                int age = getRandomInt(100);// 隨機生成年齡  
                int level = getRandomInt(10);// 隨機生成等級  
                String region = StringUtil.getString(32);// 隨機生成地區  
                String address = StringUtil.getString(512);// 隨機生成地址  
                preparedStatement.setString(1, name);// 設定引數  
                preparedStatement.setInt(2, age);// 設定引數  
                preparedStatement.setInt(3, level);// 設定引數  
                preparedStatement.setString(4, region);// 設定引數  
                preparedStatement.setString(5, address);// 設定引數  
                preparedStatement.addBatch();// 新增到批處理  
            }  
            preparedStatement.executeBatch();// 批次執行  

        }  
        preparedStatement.close();// 關閉資源  
        base.close();// 關閉資源  
    }  
}

測試結果如下：

行數	分鐘	秒
7308	27	17
6817	27	18
6871	27	19
6367	27	20
6631	27	21
7310	27	22
6903	27	23
7258	27	24
7180	27	25
7309	27	26
7208	27	27
6640	27	28

跟 while 迴圈結果也差不多。下面分享一下我查到的資料結果。批處理只是減少了往伺服器來回傳送資料的效率，僅此而已。對於伺服器實際處理 MySQL 操作，並沒有很大提升。

多行插入

這裡的批次插入指的是一條 MySQL 語句包含 N 行 MySQL 資料，這與批處理不一樣。批處理是一次性將很多條 MySQL 傳送給服務端，而多行插入一條 MySQL 插入 N 行資料。

下面是測試指令碼：

測試結果如下：

行數	分鐘	秒
12360	46	28
11460	46	29
14800	46	30
22110	46	31
23950	46	32
24750	46	33
24030	46	34
15230	46	35
12360	46	28
11460	46	29
14800	46	30
22110	46	31

效能確實有所提升，但是很不穩定。最高和最低也得兩倍差距了。

單執行緒的已經測試完了。相信各位已經有所瞭解，其實把這些單執行緒方式擴充成多執行緒就變成了更高效能的 MySQL 資料寫入功能了。而且接入效能測試框架之後，這個寫入行數也會變得更加穩定。

• 2021 年原創合集
• 2022 年原創合集
• 2023 年原創合集
• 服務端功能測試
• 效能測試專題
• Java、Groovy、Go、Python
• 單元&白盒&工具合集
• 測試方案&BUG&爬蟲&UI 自動化
• 測試理論雞湯
• 社群風采&影片合集