一:基础数据准备
DROP TABLE IF EXISTS `tbl_user`;
CREATE TABLE `tbl_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
INSERT INTO `tbl_user` VALUES
('1', 'admin', 'admin@126.com', '18', '1', '2018-07-09 11:08:57'),
('2', 'mengday', 'mengday@163.com', '31', '2', '2018-07-09 11:09:00'),
('3', 'mengdee', 'mengdee@163.com', '20', '2', '2018-07-09 11:09:04'),
('4', 'root', 'root@163.com', '31', '1', '2018-07-09 14:36:19'),
('5', 'zhangsan', 'zhangsan@126.com', '20', '1', '2018-07-09 14:37:28'),
('6', 'lisi', 'lisi@gmail.com', '20', '1', '2018-07-09 14:37:31'),
('7', 'wangwu', 'wangwu@163.com', '18', '1', '2018-07-09 14:37:34'),
('8', 'zhaoliu', 'zhaoliu@163.com', '22', '1', '2018-07-11 18:29:24'),
('9', 'fengqi', 'fengqi@163.com', '19', '1', '2018-07-11 18:29:32');
DROP TABLE IF EXISTS `tbl_userinfo`;
CREATE TABLE `tbl_userinfo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`address` varchar(255) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_userId` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `tbl_userinfo` VALUES
('1', '上海市', '1'),
('2', '北京市', '2'),
('3', '杭州', '3'),
('4', '深圳', '4'),
('5', '广州', '5'),
('6', '海南', '6');
二:五百万数据插入
上面插入几条测试数据,在使用索引时还需要插入更多的数据作为测试数据,下面就通过存储过程插入500W条数据作为测试数据
-- 修改mysql默认的结束符号,默认是分号;但是在函数和存储过程中会使用到分号导致解析不正确
delimiter $$
-- 随机生成一个指定长度的字符串
create function rand_string(n int) returns varchar(255)
begin
# 定义三个变量
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
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()*52), 1));
set i = i + 1;
end while;
return return_str;
end $$
-- 创建插入的存储过程
create procedure insert_user(in start int(10), in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into tbl_user values ((start+i) ,rand_string(8), concat(rand_string(6), '@random.com'), 1+FLOOR(RAND()*100), 3, now());
until i = max_num
end repeat;
commit;
end $$
-- 将命令结束符修改回来
delimiter ;
-- 调用存储过程,插入500万数据,需要等待一会时间,等待执行完成
call insert_user(100001,5000000);
-- Query OK, 0 rows affected (7 min 49.89 sec) 我的Macbook Pro i5 8G内存用了8分钟才执行完
select count(*) from tbl_user;