mysql 快速生成100w条测试数据
主要思路：
1.使用函数+存储过程快速插入 数据库引擎为【memory】 的表中
2.复制memory数据到目标表中
① 修改mysql配置
因为数据库默认配置缓存数据很小，且 innodb_flush_log_at_trx_commit 默认为1，插入效率最慢
查看默认配置:
SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log%';
SHOW GLOBAL VARIABLES LIKE 'max_heap_table_size%';
SHOW GLOBAL VARIABLES LIKE 'tmp_table_size%';
配置 innodb_flush_log_at_trx_commit 级别
SET GLOBAL innodb_flush_log_at_trx_commit = 0;
max_heap_table_size和 tmp_table_size
到my.ini[windows]/my.cnf[linux] 进行修改，用sql设置貌似没有起作用
打开my.ini 到 [mysqld] 下面添加
tmp_table_size = 256M
max_heap_table_size = 256M
②创建函数和存储过程
CREATE TABLE `vote_record_memory` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`user_id` VARCHAR (20) NOT NULL,
`vote_id` INT (11) NOT NULL,
`group_id` INT (11) NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `index_id` (`user_id`) USING HASH
) ENGINE = MEMORY AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8
CREATE TABLE `vote_record` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`user_id` VARCHAR (20) NOT NULL,
`vote_id` INT (11) NOT NULL,
`group_id` INT (11) NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `index_user_id` (`user_id`) USING HASH
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8
CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET latin1
BEGIN
DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
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
CREATE PROCEDURE `add_vote_memory`(IN n int)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i <= n ) DO
INSERT into vote_record_memory (user_id,vote_id,group_id,create_time ) VALUEs (rand_string(20),FLOOR(RAND() * 1000),FLOOR(RAND() * 100) ,now() );
set i=i+1;
END WHILE;
END
-- 调用方法插入数据到mysql内存表中，100w条
CALL add_vote_memory(1000000);
--插入目标表从内存表中
INSERT into vote_record SELECT * from vote_record_memory
总结
插入到内存大约用了10分钟，从内存表复制数据到目标表只用了8秒，实现插入数据大约 10分钟左右，已经很快了，这里主要归功于 ‘innodb_flush_log_at_trx_commit’ mysql 这个参数的设置
