MySQL 快速创建千万级测试数据

背景

在进行查询操作的性能测试或者 sql 优化时,我们经常需要在线下环境构建大量的基础数据供我们测试,这时需要大量的测试数据

创建测试数据的方式

1. 编写代码,通过代码批量插库(步骤太繁琐,性能不高,不推荐)
2. 编写存储过程和函数执行 (繁琐)
3. 临时数据表方式执行(强烈推荐, 本文用实例演示)
4. 一行一行手动插入 (当我没说~)
5. 从生产环境复制 (不现实)

下面我们用临时数据表方式实现快速创建千万级测试数据

1.创建基础表结构

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_user_id` varchar(36) NOT NULL DEFAULT '',
  `c_name` varchar(22) NOT NULL DEFAULT '',
  `c_province_id` int(11) NOT NULL,
  `c_city_id` int(11) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`c_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.创建临时数据表 tmp_table

后续会用临时表的数据填充到基础表里

CREATE TABLE tmp_table (
  id INT,
  PRIMARY KEY (id)
);

3.用 python 或者 bash 生成100w条数据的文件

python(推荐):

python -c "for i in range(1, 1+1000000): print(i)" > base.txt

bash(不推荐,会比较慢):

bash i=1; while [ $i -le 1000000 ]; do echo $i; let i+=1; done  > base.txt

4.登录 MySQL 导入数据到临时表 tmp_table

mysql> load data infile '/Users/Hoe/temp/base.txt' replace into table tmp_table;
Query OK, 1000000 rows affected (2.55 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

Windows 系统看这里, 绝对路径需要 \\ 双斜杠 如:C:\\Users\\Hoe\\base.txt

mysql> load data infile 'C:\\Users\\Hoe\\base.txt' replace into table tmp_table;

出坑记录

导入数据时有可能会报错
原因是 mysql 默认没有开 secure_file_priv(这个参数用来限制数据导入和导出操作这些操作需要用户具有FILE权限。 )

报错如下:

mysql> load data infile '/Users/Hoe/temp/base.txt' replace into table tmp_table;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

解决办法:在mysql的配置文件中(my.ini 或者 my.conf)中添加 secure_file_priv = /Users/Hoe/temp/, 然后重启 mysql
查看配置是否生效

mysql> show variables like '%secure%';
+------------------+------------------+
| Variable_name    |      Value       |
+------------------+------------------+
| secure_auth      | OFF              |
| secure_file_priv | /Users/Hoe/temp/ |
+------------------+------------------+
2 rows in set (0.00 sec)

5.插入数据到主表

以临时表为基础数据,插入数据到 t_user 中,100W数据插入需要10.37s

mysql> INSERT INTO t_user
    ->   SELECT
    ->     id,
    ->     uuid(),
    ->     CONCAT('userNickName', id),
    ->     FLOOR(Rand() * 1000),
    ->     FLOOR(Rand() * 100),
    ->     NOW()
    ->   FROM
    ->     tmp_table;
Query OK, 1000000 rows affected (10.37 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

更新创建时间字段, 让创建时间字段变成随机

mysql> UPDATE t_user SET create_time=date_add(create_time, interval FLOOR(1 + (RAND() * 7)) year);
Query OK, 1000000 rows affected (4.77 sec)
Rows matched: 1000000  Changed: 1000000  Warnings: 0

完成~! 100W条数据瞬间搞定(速度由电脑配置决定)

记得把之前生成的 base.txt 和临时表 tmp_table 删除

mysql> select * from t_user limit 10;
+----+--------------------------------------+----------------+---------------+-----------+---------------------+
| id | c_user_id                            | c_name         | c_province_id | c_city_id | create_time         |
+----+--------------------------------------+----------------+---------------+-----------+---------------------+
|  1 | 6ba472dc-7c78-11e9-9555-64006a06b70a | userNickName1  |           197 |         7 | 2028-05-22 18:00:25 |
|  2 | 6ba476ec-7c78-11e9-9555-64006a06b70a | userNickName2  |           793 |        73 | 2028-05-22 18:00:25 |
|  3 | 6ba47775-7c78-11e9-9555-64006a06b70a | userNickName3  |           297 |        28 | 2025-05-22 18:00:25 |
|  4 | 6ba477cc-7c78-11e9-9555-64006a06b70a | userNickName4  |           512 |        71 | 2033-05-22 18:00:25 |
|  5 | 6ba4781c-7c78-11e9-9555-64006a06b70a | userNickName5  |            53 |        11 | 2024-05-22 18:00:25 |
|  6 | 6ba47873-7c78-11e9-9555-64006a06b70a | userNickName6  |           410 |        70 | 2028-05-22 18:00:25 |
|  7 | 6ba478c0-7c78-11e9-9555-64006a06b70a | userNickName7  |           307 |        41 | 2026-05-22 18:00:25 |
|  8 | 6ba4790a-7c78-11e9-9555-64006a06b70a | userNickName8  |           149 |        50 | 2026-05-22 18:00:25 |
|  9 | 6ba47960-7c78-11e9-9555-64006a06b70a | userNickName9  |            76 |        86 | 2026-05-22 18:00:25 |
| 10 | 6ba479aa-7c78-11e9-9555-64006a06b70a | userNickName10 |           107 |        93 | 2024-05-22 18:00:25 |
+----+--------------------------------------+----------------+---------------+-----------+---------------------+
10 rows in set (0.00 sec)

本文参考传送门

我来吐槽

*

*

已有 8 条评论

  1. zgcwkj

    厉害啊,我的老哥

  2. repostone

    非技术的路过。

  3. 程志辉

    soga......Python确实是个利器。MySQL暂时我只会基础的增删改查语句.....老师说数据是最宝贵了,新人刚入职的几年里是绝对碰不到数据库的,就算碰到也会限制很多权限

    1. Hoe

      软件工程师少不了和数据打交道

  4. 格子老师

    羡慕技术帝!

    1. Hoe

      😂惭愧惭愧

      1. 格子老师

        我刚刚还没关你的博客 还想着一会再多翻几页学习一下