我們?cè)谧鲂阅苷{(diào)優(yōu)時(shí)，需要有大數(shù)據(jù)量的測(cè)試數(shù)據(jù)让腹，才能很好的反應(yīng)我們的數(shù)據(jù)庫(kù)及sql語(yǔ)句性能远剩，這就需要們生成百萬(wàn)級(jí)的測(cè)試數(shù)據(jù)骇窍，這里提供一個(gè)快速生成百萬(wàn)級(jí)測(cè)試數(shù)據(jù)的方法瓜晤，利用存儲(chǔ)過(guò)程快速生成像鸡，在我個(gè)人電腦實(shí)測(cè)百萬(wàn)級(jí)數(shù)據(jù)約10分鐘左右活鹰。
創(chuàng)建測(cè)試表

-- 用戶(hù)表
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

--用戶(hù)部門(mén)表
CREATE TABLE `department` (
  `id` bigint(20) unsigned NOT NULL,
  `department` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

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

創(chuàng)建存儲(chǔ)過(guò)程只估，用于批量添加測(cè)試數(shù)據(jù)

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 '行政技術(shù)研發(fā)財(cái)務(wù)人事開(kāi)發(fā)公關(guān)推廣營(yíng)銷(xiāo)咨詢(xún)客服運(yùn)營(yíng)測(cè)試';
DECLARE chars2 VARCHAR(100) DEFAULT '北京上海青島重慶成都安徽福建浙江杭州深圳溫州內(nèi)蒙古天津河北西安三期';

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 $$

為了提高速度，可以暫停事務(wù)蛔钙。測(cè)試添加100萬(wàn)隨機(jī)數(shù)據(jù)锌云，大概600s左右時(shí)間吁脱。

-- 停掉事務(wù)
set autocommit = 0;  
-- 調(diào)用存儲(chǔ)過(guò)程
call generate(1000000);
-- call genDepAdd(1000000);
-- 重啟事務(wù)
set autocommit = 1;  

image.png

對(duì)比MyIsam：當(dāng)創(chuàng)建表時(shí)選擇MyIsam格式桑涎，插入數(shù)據(jù)會(huì)很慢兼贡，僅僅3000條數(shù)據(jù)就需要2分鐘的時(shí)間攻冷，由此可見(jiàn)MyIsam和InnoDB的差距還是很大的。另外在執(zhí)行過(guò)程中可以發(fā)現(xiàn)MyIsam插入的數(shù)據(jù)可以在表中實(shí)時(shí)看到遍希，而InnoDB做了事務(wù)最終一次提交等曼，所以數(shù)據(jù)不能實(shí)時(shí)看到，只有存儲(chǔ)過(guò)程全部執(zhí)行完成后才可以看到數(shù)據(jù)。

image.png