MySQL 寫入壓測幾種方式

FunTester發表於2024-05-29
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 自動化
  • 測試理論雞湯
  • 社群風采&影片合集
如果覺得我的文章對您有用，請隨意打賞。您的支援將鼓勵我繼續創作！
打賞支援
暫無回覆。

相關文章