MySQL快速插入百万行测试数据

遇到需要大量数据进行业务测试的场景，产生数据有很多种方式，下面介绍一种方式。

环境：

• windows10 64bit
• i5 4210M 2.6GHz
• 8G 1600MHz *2
• SSD
• MYSQL 5.0

整体思路:

• 1、如何产生大量数据？
  可以利用循环，重复制造大量数据

• 2、如何快速将数据存储（插入）数据表？
  直接使用循环也可以做到，但是速度可能会相对慢一点。可以利用MYSQL MEMORY引擎，加速数据的插入。

其中一种实现方式，详细过程:

-- 1、创建内存表
-- 使用MEMORY数据库引擎，创建内存表。
--将数据直接插入内存表，再将数据从内存表复制到普通表，效率优于直接插入普通表
CREATE TABLE `table_in_memory` (
  `id` INT(6) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` VARCHAR(50) NOT NULL COMMENT '名称',
  `pwd` VARCHAR(32) NOT NULL COMMENT '密码',
  `create_time` DATETIME DEFAULT NULL COMMENT '创建日期',
  PRIMARY KEY (`id`)
) ENGINE=MEMORY AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8 COMMENT='内存临时表';

-- 2、创建使用MYISAM引擎的普通表，存放最终数据的表
-- 针对大量测试数据的这个场景，可以不考虑事务，因此可以使用MYISAM引擎
CREATE TABLE `test_data` (
  `id` INT(6) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` VARCHAR(50) NOT NULL COMMENT '名称',
  `create_time` DATETIME DEFAULT NULL COMMENT '创建日期',
  PRIMARY KEY (`id`)
) ENGINE=MYISAM AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8 COMMENT='测试数据表';

-- 3、创建一个函数，用于生成随机数据
-- 尽管是测试数据，但是100W行一模一样的数据也就没有什么测试业务的价值。在创建测试数据的时候，使用一些算法，
--将数据尽量模拟真实一点。
DROP FUNCTION IF EXISTS rand_string;
DELIMITER $$
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyz
    ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str = CONCAT(return_str,SUBSTRING(chars_str , FLOOR(1 + RAND()*62 ),1));
        SET i = i +1;
    END WHILE;
    RETURN return_str;
END $$
DELIMITER ;

-- 4、创建存储过程，批量调用函数，生成大量随机数，并插入临时表（内存表）
DROP PROCEDURE IF EXISTS `add_data`;
DELIMITER $$
CREATE PROCEDURE `add_data`(IN n INT)
BEGIN
    DECLARE i INT UNSIGNED DEFAULT 0;
    WHILE i < n DO
        INSERT INTO `table_in_memory`(NAME,pwd,create_time) 
        VALUES (rand_string(15),MD5(123456),NOW());
        SET i = i+1;
    END WHILE;
END $$
DELIMITER ;

-- 5、调用存储过程,执行100W次的数据生成和插入。
--这里的时间依赖上面步骤中生成随机数的算法的效率，如果生成随机数的算法比较复杂，在100W次的执行中，
--也会比较耗时。另外和机器性能也有关。本机使用以上相对简单的随机数算法，执行了3次，平均1min 10sec左右
--完成100W行数据生成并插入内存表 
CALL add_data(1000000);

-- 6、调用存储过程时，如果出现TABLE IS FULL ……错误提示，说明 mysql默认的max_heap_table_size 太小，需要扩容
-- 找到my.ini 文件，将max_heap_table_size 修改，或者此参数不存在时，直接添加 max_heap_table_size = 1024M
-- 使用以下命令查看数据库的默认参数大小
show variables like '%table_size'

在MYSQL安装路径下，找到my.ini配置文件，修改max_heap_table_size 参数的大小

如果不存在这个参数，直接添加

max_heap_table_size = 1024M

重启数据库实例

-- 7、重新调用存储过程
CALL add_data(1000000);

--8、将临时表中的数据复制到目标表中,约耗时2sec
insert into test_data select * from table_in_memory

注意事项:

• 执行效率受多方面因素影响，如机器性能，生成随机数算法的复杂度、效率，插入的数据行数，使用的数据库引擎。大家可以尝试使用innodb引擎，测试下速度。