MySQL 壓力測試實戰：mysqlslap 參數詳解、海量測試數據生成存儲過程與性能基準建立

MySQL 壓力測試完整工具鏈：從數據準備到基準報告

官方工具：mysqlslap 參數速查

#!/bin/bash
# mysqlslap_benchmark.sh
DB="bench_db"
USER="bench_user"
PASS="BenchPass123!"
HOST="127.0.0.1"

# 1. 準備數據 (見下文存儲過程)
# 2. 基準測試
for concurrency in 10 50 100 200; do
  mysqlslap \
    --host=$HOST --user=$USER --password=$PASS \
    --concurrency=$concurrency \
    --iterations=5 \
    --number-of-queries=1000 \
    --create-schema=$DB \
    --query=./workload.sql \
    --engine=InnoDB \
    --verbose \
    --csv=/tmp/bench_${concurrency}.csv
done

# 3. 結果分析
# 關注: Average query time, Min/Max, Queries per second

自定義負載 SQL (workload.sql)

-- 1. 基礎表結構
CREATE TABLE dept (
  deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  dname VARCHAR(20) NOT NULL DEFAULT '',
  loc VARCHAR(13) NOT NULL DEFAULT '',
  PRIMARY KEY (deptno)
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE emp (
  empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  ename VARCHAR(20) NOT NULL DEFAULT '',
  job VARCHAR(9) NOT NULL DEFAULT '',
  mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  hiredate DATE NOT NULL,
  sal DECIMAL(7,2) NOT NULL,
  comm DECIMAL(7,2) NOT NULL,
  deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (empno),
  KEY idx_deptno (deptno)
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE salgrade (
  grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  losal DECIMAL(17,2) NOT NULL,
  hisal DECIMAL(17,2) NOT NULL,
  PRIMARY KEY (grade)
) ENGINE=InnoDB CHARSET=utf8mb4;

-- 2. 隨機字符串函數
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
  DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  DECLARE return_str VARCHAR(255) DEFAULT '';
  DECLARE i INT DEFAULT 0;
  WHILE i < n DO
    SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1+RAND()*52), 1));
    SET i = i + 1;
  END WHILE;
  RETURN return_str;
END$$

-- 3. 隨機部門號函數
CREATE FUNCTION rand_num() RETURNS INT
BEGIN
  RETURN FLOOR(10 + RAND()*500);
END$$

-- 4. 批量插入員工 (180萬行)
CREATE PROCEDURE insert_emp(IN start_id INT, IN max_num INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  SET autocommit = 0;
  REPEAT
    SET i = i + 1;
    INSERT INTO emp VALUES (start_id + i, rand_string(6), 'SALESMAN', 1, CURDATE(), 2000, 400, rand_num());
    IF i % 10000 = 0 THEN COMMIT; SET autocommit = 0; END IF;
  UNTIL i = max_num END REPEAT;
  COMMIT;
END$$

-- 5. 批量插入部門
CREATE PROCEDURE insert_dept(IN start_id INT, IN max_num INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  SET autocommit = 0;
  REPEAT
    SET i = i + 1;
    INSERT INTO dept VALUES (start_id + i, rand_string(10), rand_string(8));
  UNTIL i = max_num END REPEAT;
  COMMIT;
END$$

-- 6. 執行生成
CALL insert_emp(100001, 1800000);  -- 180萬員工
CALL insert_dept(100, 10);         -- 10個部門
DELIMITER ;

進階工具對比

關鍵指標解讀

• QPS/TPS：核心吞吐指標
• Avg/95%/99% Latency：響應時間分佈
• Error Rate：錯誤率 (應為 0)
• CPU/IO/Network：系統資源飽和度

測試前檢查單

1. 關閉 Query Cache (query_cache_type=0)
2. 預熱緩衝池 (全表掃描或 SELECT COUNT(*))
3. 確認 innodb_buffer_pool_size ≥ 數據熱點大小
4. 備份生產配置，測試用獨立實例
5. 記錄 SHOW GLOBAL STATUS 基線