MySQL 壓力測試腳本實戰：生成百萬級測試資料與存儲過程

測試表結構

-- 部門表
CREATE TABLE dept (
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    dname VARCHAR(20) NOT NULL DEFAULT '',
    loc VARCHAR(13) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 員工表
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
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 薪資等級表
CREATE TABLE salgrade (
    grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    losal DECIMAL(17,2) NOT NULL,
    hisal DECIMAL(17,2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO salgrade VALUES 
(1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);

隨機資料生成函數

隨機字串

DELIMITER $$
DROP FUNCTION IF EXISTS rand_string $$
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 $$
DELIMITER ;

隨機數字（部門編號）

DELIMITER $$
DROP FUNCTION IF EXISTS rand_num $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(10 + RAND()*500);
    RETURN i;
END $$
DELIMITER ;

批量插入存儲過程

員工表：180 萬筆

DELIMITER $$
DROP PROCEDURE IF EXISTS insert_emp $$
CREATE PROCEDURE insert_emp(IN start INT(10), IN max_num INT(10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
        SET i = i + 1;
        INSERT INTO emp VALUES ((start+i), rand_string(6), 'SALESMAN', 1, CURDATE(), 2000, 400, rand_num());
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END $$
DELIMITER ;

-- 執行：從 100001 開始插入 180 萬筆
CALL insert_emp(100001, 1800000);

部門表：10 筆

DELIMITER $$
DROP PROCEDURE IF EXISTS insert_dept $$
CREATE PROCEDURE insert_dept(IN start INT(10), IN max_num INT(10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
        SET i = i + 1;
        INSERT INTO dept VALUES ((start+i), rand_string(10), rand_string(8));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END $$
DELIMITER ;

CALL insert_dept(100, 10);

關鍵效能技巧

• SET autocommit = 0 + 批次 COMMIT：大幅減少 Redo Log 刷盤與 Binlog 同步開銷
• MyISAM 可搭配 ALTER TABLE emp DISABLE KEYS 禁用索引更新，載入完再 ENABLE KEYS 重建（InnoDB 不適用）
• 函數內使用 RAND() 產生分布資料，模擬真實場景
• 主鍵連續避免頁分裂，測試 B+ 樹填充因子

壓力測試工具推薦

• sysbench：標準 OLTP 基準測試，支援 Lua 腳本自定義場景
• mysqlslap：官方內建負載模擬工具
• tpcc-mysql：TPC-C 標準基準測試實作
• go-tpc / benchsql：現代化分散式基準測試框架

小結

自寫存儲過程生成測資適合快速驗證索引效果、SQL 計劃、鎖行為。正式壓測建議使用 sysbench 等標準工具，獲得可比較、可重現的指標。核心技巧：關閉自動提交、批次提交、禁用索引（MyISAM）、主鍵有序。