:facepunch:
大数据之数据库mysql优化实战(一)
首先你要有数据,不然怎么测试,几百条就算了,还没跑就完了。
- 本章主题如何向数据库插入10万条数据
创建数据库
CREATE TABLE `etcblacklist11` (
`ID` int(24) NOT NULL AUTO_INCREMENT,
`IssuerID` varchar(16) DEFAULT NULL,
`NetNo` varchar(4) DEFAULT NULL,
`CARDID` varchar(16) DEFAULT NULL,
`ObuID` varchar(16) DEFAULT NULL,
`License` varchar(25) DEFAULT NULL,
`strID` varchar(2) DEFAULT NULL,
`strTmp1` varchar(3) DEFAULT NULL,
`strtime1` datetime DEFAULT NULL,
`strTmp2` varchar(3) DEFAULT NULL,
`strtime2` datetime DEFAULT NULL,
`strTmp3` varchar(3) DEFAULT NULL,
`strTmp4` varchar(3) DEFAULT NULL,
`LicenseName` varchar(4) DEFAULT NULL,
`LicenseNameA` varchar(4) DEFAULT NULL,
`LicenseNameB` varchar(4) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
书写存储过程(create procedure procefureName())
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
DECLARE IDs INT(11);
DECLARE i INT;
DECLARE CARDID VARCHAR(16);
DECLARE IssuerID VARCHAR(16);
DECLARE ObuID VARCHAR(16);
DECLARE License VARCHAR(8);
DECLARE strID VARCHAR(4);
DECLARE strTmp1 VARCHAR(4);
DECLARE strTmp2 VARCHAR(4);
DECLARE strTmp3 VARCHAR(4);
DECLARE NetNo VARCHAR(4);
DECLARE LicenseName VARCHAR(2);
DECLARE LicenseNameA VARCHAR(2);
DECLARE LicenseNameB VARCHAR(2);
SET IDs =11;
SET LicenseName = '京';
SET strID = RIGHT(CONCAT('00',IDs),2);
SET i=0;
set strTmp1 = '1';
SET strTmp1 = CONCAT(strID,'1');
SET strTmp2 = CONCAT(strID,'2');
SET strTmp3 = CONCAT(strID,'3');
SET NetNo = CONCAT(strID,'01');
SET LicenseNameA = CONCAT(LicenseName,'A');
SET LicenseNameB= CONCAT(LicenseName,'B');
WHILE i<24315 DO
SET CARDID = CONCAT(strTmp1,RIGHT(CONCAT('0000000000000',cast(i as char(16))),13));
SET IssuerID = CONCAT(strTmp2,RIGHT(CONCAT('0000000000000',cast(i as char(16))),13));
SET ObuID = CONCAT(strTmp3,RIGHT(CONCAT('0000000000000',cast(i as char(16))),13));
SET License = CONCAT(LicenseNameA,RIGHT(CONCAT('00000',cast(i as char(16))),5));
#SET CARDID = CONCAT('0847',cast(i as char(12)));
INSERT INTO `ETCBlackList11`(IssuerID, NetNo, CARDID, ObuID, License,strID,strTmp1,strtime1,strTmp2,strtime2,strTmp3,strTmp4,LicenseName,LicenseNameA,LicenseNameB) VALUES (IssuerID, NetNo, CARDID, ObuID, License, '0', '0', '2000-01-01 00:00:00', '1', '2000-01-01 00:00:00', '测试', '16019', '0', '0', '0');
SET i = i + 1;
END WHILE;
END
存储过程的优点
- 可编写逻辑比较复杂的数据库操作语句,并具有可重用性
- 存储过程是已经预编后的sql语句,速度更快
- 减轻网络的负载,客户计算机上调用该存储过程时,网络中传送的只是该调用语句
- 存储过程可被作为一种安全机制来充分利用
创建数据库注意事项
- 尽量保证字段的存储大小,不要设置的太大,带来内存消耗
- 存储过程的变量名的大小也是如此
插入10W条数据分析
1: 总容量13.5M ,插入1W条数据 300s(随着表数据量越大花费时间越长), 每条数据占1.7kb.
若有兴趣,加群交流:181666459