MySQL 8.0 和 5.7 快速生成测试数据

MySQL 5.7 和 8.0 生成百万级测试数据的方法

MySQL 5.7 和 8.0 都可以通过多种方式生成大量测试数据，以下是几种高效的方法：

方法一：使用存储过程批量插入

这是最通用的方法，适用于所有 MySQL 版本：

-- 创建测试表
CREATE TABLE test_data (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50),age INT,email VARCHAR(100),create_time DATETIME
);-- 创建存储过程
DELIMITER $$CREATE PROCEDURE generate_test_data(IN rows INT)
BEGINDECLARE i INT DEFAULT 1;START TRANSACTION;WHILE i <= rows DOINSERT INTO test_data (name, age, email, create_time)VALUES (CONCAT('user_', FLOOR(RAND() * 1000000)),FLOOR(RAND() * 100),CONCAT('user_', FLOOR(RAND() * 1000000), '@example.com'),NOW() - INTERVAL FLOOR(RAND() * 365) DAY);SET i = i + 1;END WHILE;COMMIT;
END$$DELIMITER ;-- 调用存储过程生成100万条数据（可能需要几分钟）
CALL generate_test_data(1000000);

方法二：使用 INSERT … SELECT 语句（MySQL 8.0 优化版）

MySQL 8.0 支持更高效的生成方式：

-- 创建测试表
CREATE TABLE test_data (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50),age INT,email VARCHAR(100),create_time DATETIME
);-- 关闭自动提交以提高性能
SET autocommit=0;-- 生成100万条数据（利用递归CTE）
WITH RECURSIVE generate_rows AS (SELECT 1 AS nUNION ALLSELECT n + 1 FROM generate_rows WHERE n < 1000000
)
INSERT INTO test_data (name, age, email, create_time)
SELECT CONCAT('user_', n),FLOOR(RAND() * 100),CONCAT('user_', n, '@example.com'),NOW() - INTERVAL FLOOR(RAND() * 365) DAY
FROM generate_rows;-- 提交事务
COMMIT;

方法三：使用 sysbench（推荐方案）

对于超大数据量，推荐使用专业工具如 sysbench：

# 安装sysbench（Ubuntu/Debian）
sudo apt-get install sysbench# 准备测试数据（100万行）
sysbench --db-driver=mysql --mysql-host=localhost --mysql-port=3306 \
--mysql-user=root --mysql-password=yourpassword --mysql-db=test \
--tables=1 --table-size=1000000 oltp_read_write prepare# 运行测试（可选）
sysbench --db-driver=mysql --mysql-host=localhost --mysql-port=3306 \
--mysql-user=root --mysql-password=yourpassword --mysql-db=test \
--tables=1 --table-size=1000000 --threads=16 --time=60 \
oltp_read_write run# 清理数据（可选）
sysbench --db-driver=mysql --mysql-host=localhost --mysql-port=3306 \
--mysql-user=root --mysql-password=yourpassword --mysql-db=test \
--tables=1 --table-size=1000000 oltp_read_write cleanup

性能优化建议

1. 关闭自动提交：在批量插入前执行 SET autocommit=0;，插入完成后执行 COMMIT;

2. 禁用索引：在导入前禁用非主键索引，导入后重新启用：

   ALTER TABLE test_data DISABLE KEYS;
   -- 插入数据
   ALTER TABLE test_data ENABLE KEYS;
   

3. 调整参数：

   SET GLOBAL innodb_buffer_pool_size = 2G;  -- 增大缓冲池
   SET GLOBAL sync_binlog = 0;              -- 减少磁盘IO
   SET GLOBAL innodb_flush_log_at_trx_commit = 2;
   

4. 分批插入：对于存储过程方法，建议每次插入10万行左右，避免事务过大

根据你的具体场景选择合适的方法，sysbench 方案通常是最高效的，而存储过程方法最灵活。