MySQL 数据库压力测试：批量生成百万级数据的存储过程

Ryan

发布时间：2026-10-14 | 3人浏览过

原创

在进行 MySQL 数据库的性能调优或负载评估时，通常需要构建包含大量数据的测试环境。本文介绍如何通过编写 SQL 存储过程和自定义函数，快速在 dept、emp 和 salgrade 三张关联表中生成分布式的百万级测试数据，为后续的压力测试（如 Sysbench）做准备。

结果与目标

完成上述操作后，数据库中将生成结构完整且含有大量随机数据的测试集。具体表现为：

dept 表包含指定数量的部门记录；
emp 表包含指定数量的雇员记录（如 180 万条），字段数据具有随机性以避免索引偏差；
salgrade 表可根据需要进行扩展填充。

准备条件

开始前请确保拥有数据库的写入权限。由于涉及大量的重复写操作（Commit），建议在执行前备份相关库数据，以防止意外覆盖或产生不可逆的业务数据污染。

真实操作顺序

第 1 步：创建基础测试表结构

目的：建立压力测试所需的数据载体。

首先创建部门表、员工表和工资级别表。注意字符集设为 utf8，引擎默认即可。

-- 创建部门表 DEPT
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;

-- 创建雇员表 EMP
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;

-- 创建工资级别表 SALGRADE
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;

第 2 步：初始化静态参考数据

目的：向 salgrade 表填入标准的薪资区间等级。

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

第 3 步：定义随机数据生成函数

目的：为后续插入海量数据提供随机的字符串（如姓名、职位）和数值（如编号、薪资）。

由于 MySQL 存储过程中没有内置随机字符串生成的快捷方式，需自定义函数。
在此步骤中需要更改命令结束符（默认是 ;），改为 $$，直到函数定义结束。

3.1 创建随机字符串函数 `rand_string`

该函数接收一个整数参数 n，返回长度为 n 的随机字母串。

DELIMITER $$
DROP FUNCTION IF EXISTS rand_string$$
CREATE FUNCTION rand_string(n INT)
RETURNS varchar(255)
BEGIN 
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    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 ;

验证方法：SELECT rand_string(6);

3.2 创建随机数值函数 `rand_num`

生成 10 到 500 之间的随机整数，适用于非主键的唯一性要求不严格的字段。

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 ;

验证方法：SELECT rand_num();

第 4 步：编写并执行批量插入存储过程

目的：通过循环批量 INSERT 记录，绕过单次插入的性能瓶颈，实现海量数据生成。

4.1 向 `emp` 表插入海量数据

本示例演示插入 1,800,000 条记录。开启 autocommit = 0 可以在循环结束后统一提交事务，极大提升写入速度。

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',0001,CURDATE(),2000,400,rand_num());
    UNTIL i = max_num
    END REPEAT;
    COMMIT; -- 事务提交
END $$
DELIMITER ;

执行调用：从编号 100,001 开始，插入 1,800,000 条记录。

CALL insert_emp(100001, 1800000);

4.2 向 `dept` 表插入数据

根据需要插入少量测试部门数据。

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 ;

执行调用（例如插入 10 条）：

CALL insert_dept(100, 10);

第 5 步：处理 `salgrade` 表（可选扩展）

如果需要针对 salgrade 进行测试，可使用类似逻辑。注意若 emp 表有外键关联或需要导入 Key，应在大批量插入前考虑性能优化（如禁用/启用 Keys）。

DELIMITER $$
DROP PROCEDURE IF EXISTS insert_salgrade$$
CREATE PROCEDURE insert_salgrade(IN start INT(10), IN max_num INT(10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    -- 如果有索引，批量插入前可临时禁用以提升速度
    ALTER TABLE emp DISABLE KEYS; 
    REPEAT
        SET i = i + 1;
        INSERT INTO salgrade VALUES ((start+i), (start+i), (start+i));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END $$
DELIMITER ;

完成判断

检查各表行数确认插入成功：

SELECT COUNT(*) FROM emp;
SELECT COUNT(*) FROM dept;

若 emp 表显示约 180 万行，则说明批量脚本运行无误，可直接接入压测工具进行查询或写入测试。

排错提示

语法错误：若在 DELIMITER $$ 之后直接使用分号 ; 结束代码块，会导致 MySQL 解析报错。务必确保过程体的最后以 END $$ 结尾。
内存溢出：一次性提交过大事务可能导致 Packet too large 错误或耗尽内存，建议在分批调用时控制单线程并发度，或使用 Sysbench 等标准化工具替代手动脚本。