MySQL快速创建千万测试数据

1. mysql版本 8.0.21

~ #  mysqladmin -V 
mysqladmin  Ver 8.0.21 for Linux on x86_64 (MySQL Community Server - GPL)

2. 创建基础表

CREATE TABLE `person` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `score` int(11) NOT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `create_time` (`create_time`),
  KEY `name_score` (`name`,`score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. 使用存储过程造数据(不推荐)

CREATE PROCEDURE insert_person()
begin
    declare c_id integer default 1;
    while c_id<=100000 do
    insert into person values(c_id, concat('name',c_id), c_id+100, date_sub(NOW(), interval c_id second));
    set c_id=c_id+1;
    end while;
end
CALL insert_person();

4. 采用临时表

• 4.1 创建临时表

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

• 4.2 生成id

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

• 4.3 导入id到临时表中

使用 show variables like '%secure%'; 找到secure_file_priv文件位置，
然后将base.txt移到该目录下。如下：/var/lib/mysql-files/base.txt，
执行 load data infile '/var/lib/mysql-files/base.txt' replace into table tmp_table;
Tips：win上需要将/改为\，如
load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/base.txt' replace into table tmp_table;

图1 图2

• 4.3 以临时表为基础数据，插入数据到person中，100W数据插入需要42.32 sec

INSERT INTO person SELECT id,CONCAT( 'userName', id ),FLOOR( Rand( ) * 100 ),NOW() FROM tmp_table;

图3

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

UPDATE person SET create_time=date_add(create_time, interval FLOOR(1 + (RAND() * 7)) year);

5. 1000万数据实测

   
   图4

6. 去掉索引，1000万数据实测

   
   图5

参考：https://www.jianshu.com/p/9ea7ae51d01a
参考：https://www.jb51.net/article/161712.htm