【转】存储过程创建百万级别数据

我们在做性能调优时，需要有大数据量的测试数据，才能很好的反应我们的数据库及sql语句性能，这就需要们生成百万级的测试数据，这里提供一个快速生成百万级测试数据的方法，利用存储过程快速生成，在我个人电脑实测百万级数据约10分钟左右。
创建测试表

-- 用户表
CREATE TABLE `person` (
  `id` bigint(20) unsigned NOT NULL,
  `fname` varchar(100) NOT NULL,
  `lname` varchar(100) NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  `sex` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

--用户部门表
CREATE TABLE `department` (
  `id` bigint(20) unsigned NOT NULL,
  `department` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 用户住址表
CREATE TABLE `address` (
  `id` bigint(20) unsigned NOT NULL,
  `address` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

创建存储过程，用于批量添加测试数据

delimiter $$
drop procedure if exists generate;
CREATE DEFINER=`root`@`localhost` PROCEDURE `generate`(IN num INT)
BEGIN   

DECLARE chars VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';

DECLARE fname VARCHAR(10) DEFAULT '';
DECLARE lname VARCHAR(25) DEFAULT '';
DECLARE id int UNSIGNED;
DECLARE len int;
set id=1;
DELETE from person;
WHILE id <= num DO
set len = FLOOR(1 + RAND()*10);
set fname = '';
WHILE len > 0 DO
SET fname = CONCAT(fname,substring(chars,FLOOR(1 + RAND()*62),1));
SET len = len - 1;
END WHILE;
set len = FLOOR(1+RAND()*25);
set lname = '';
WHILE len > 0 DO
SET lname = CONCAT(lname,SUBSTR(chars,FLOOR(1 + RAND()*62),1));
SET len = len - 1;
END WHILE;
INSERT into person VALUES (id,fname,lname, FLOOR(RAND()*100), FLOOR(RAND()*2));
set id = id + 1;
END WHILE;
END $$

delimiter $$
drop procedure if exists genDepAdd;
CREATE DEFINER=`root`@`localhost` PROCEDURE `genDepAdd`(IN num INT)
BEGIN   

DECLARE chars VARCHAR(100) DEFAULT '行政技术研发财务人事开发公关推广营销咨询客服运营测试';
DECLARE chars2 VARCHAR(100) DEFAULT '北京上海青岛重庆成都安徽福建浙江杭州深圳温州内蒙古天津河北西安三期';

DECLARE depart VARCHAR(10) DEFAULT '';
DECLARE address VARCHAR(25) DEFAULT '';
DECLARE id int UNSIGNED;
DECLARE len int;
set id=1;
WHILE id <= num DO
set len = FLOOR(2 + RAND()*2);
set depart = '';
WHILE len > 0 DO
SET depart = CONCAT(depart,substring(chars,FLOOR(1 + RAND()*26),1));
SET len = len - 1;
END WHILE;
set depart=CONCAT(depart,'部');
set len = FLOOR(6+RAND()*18);
set address = '';
WHILE len > 0 DO
SET address = CONCAT(address,SUBSTR(chars2,FLOOR(1 + RAND()*33),1));
SET len = len - 1;
END WHILE;

INSERT into department VALUES (id,depart);
INSERT into address VALUES (id,address);
set id = id + 1;
END WHILE;
END $$

为了提高速度，可以暂停事务。测试添加100万随机数据，大概600s左右时间。

-- 停掉事务
set autocommit = 0;  
-- 调用存储过程
call generate(1000000);
-- call genDepAdd(1000000);
-- 重启事务
set autocommit = 1;  

image

对比MyIsam：当创建表时选择MyIsam格式，插入数据会很慢，仅仅3000条数据就需要2分钟的时间，由此可见MyIsam和InnoDB的差距还是很大的。另外在执行过程中可以发现MyIsam插入的数据可以在表中实时看到，而InnoDB做了事务最终一次提交，所以数据不能实时看到，只有存储过程全部执行完成后才可以看到数据。

image

作者：山东大葱哥
链接：https://www.jianshu.com/p/edcd1607c55f
来源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。