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

Sonder

2020-02-20

4356字

11分钟

浏览 (3.5k)

背景

在进行查询操作的性能测试或者 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)

上一篇 / CentOS7下安装redis和其扩展phpredis 下一篇 / 12306的搭建教程

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

背景

🎯 相关文章

💡 推荐文章

🕵️‍♂️ 评论 (0)