玖叶教程网

作者：IKNOW本尊
来源：https://juejin.im/post/5ce372c36fb9a07ef63fb191

备注：此文章的数据量在100W，如果想要千万级，调大数量即可,但是不要大量使用rand() 或者uuid() 会导致性能下降

背景

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

废话，总不能让我去线上去测试吧，会被DBA砍死的

创建测试数据的方式

1. 编写代码，通过代码批量插库（本人使用过，步骤太繁琐，性能不高，不推荐）
2. 编写存储过程和函数执行（本文实现方式1）
3. 临时数据表方式执行 （本文实现方式2，强烈推荐该方式，非常简单，数据插入快速，100W，只需几秒）
4. 一行一行手动插入，（WTF，去死吧）

创建基础表结构

不管用何种方式，我要插在那张表总要创建的吧

方式1：采用存储过程和内存表

创建内存表

利用 MySQL 内存表插入速度快的特点，我们先利用函数和存储过程在内存表中生成数据，然后再从内存表插入普通表中

创建函数和存储过程

调用存储过程

mysql> CALL add_t_user_memory(1000000);
ERROR 1114 (HY000): The table 't_user_memory' is full

出现内存已满时，修改 max_heap_table_size 参数的大小，我使用64M内存，插入了22W数据，看情况改，不过这个值不要太大，默认32M或者64M就好，生产环境不要乱尝试

从内存表插入普通表

mysql> INSERT INTO t_user SELECT * FROM t_user_memory;
Query OK, 218953 rows affected (1.70 sec)
Records: 218953 Duplicates: 0 Warnings: 0

方式2：采用临时表

创建临时数据表tmp_table

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

用 python或者bash 生成 100w 记录的数据文件（python瞬间就会生成完）

python(推荐):

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

导入数据到临时表tmp_table中

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

千万级数据 20秒插入完成

注意：导入数据时有可能会报错，原因是mysql默认没有开secure_file_priv（ 这个参数用来限制数据导入和导出操作的效果，例如执行LOAD DATA、SELECT … INTO OUTFILE语句和LOAD_FILE()函数。这些操作需要用户具有FILE权限。）

解决办法：在mysql的配置文件中（my.ini 或者 my.conf）中添加 secure_file_priv = /Users/LJTjintao/temp/`, 然后重启mysql 解决

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

更新创建时间字段让插入的数据的创建时间更加随机