《MYSQL教程[MySQL優(yōu)化案例]系列 — 索引、提交頻率對(duì)InnoDB表寫(xiě)入速度的影響》要點(diǎn):
本文介紹了MYSQL教程[MySQL優(yōu)化案例]系列 — 索引、提交頻率對(duì)InnoDB表寫(xiě)入速度的影響,希望對(duì)您有用。如果有疑問(wèn),可以聯(lián)系我們。
先直接說(shuō)幾個(gè)結(jié)論吧:MYSQL數(shù)據(jù)庫(kù)
1、關(guān)于索引對(duì)寫(xiě)入速度的影響:
a、如果有自增列做主鍵,相對(duì)完全沒(méi)索引的情況,寫(xiě)入速度約提升 3.11%;
b、如果有自增列做主鍵,并且二級(jí)索引,相對(duì)完全沒(méi)索引的情況,寫(xiě)入速度約降低 27.37%;
因此,InnoDB表最好總是有一個(gè)自增列做主鍵.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
2、關(guān)于提交頻率對(duì)寫(xiě)入速度的影響(以表中只有自增列做主鍵的場(chǎng)景,一次寫(xiě)入數(shù)據(jù)30萬(wàn)行數(shù)據(jù)為例):MYSQL數(shù)據(jù)庫(kù)
a、等待全部數(shù)據(jù)寫(xiě)入完成后,最后再執(zhí)行commit提交的效率最高;
b、每10萬(wàn)行提交一次,相對(duì)一次性提交,約慢了1.17%;
c、每1萬(wàn)行提交一次,相對(duì)一次性提交,約慢了3.01%;
d、每1千行提交一次,相對(duì)一次性提交,約慢了23.38%;
e、每100行提交一次,相對(duì)一次性提交,約慢了24.44%;
f、每10行提交一次,相對(duì)一次性提交,約慢了92.78%;
g、每行提交一次,相對(duì)一次性提交,約慢了546.78%,也就是慢了5倍;
因此,最好是等待所有事務(wù)結(jié)束后再批量提交,而不是每執(zhí)行完一個(gè)SQL就提交一次.MYSQL數(shù)據(jù)庫(kù)
曾經(jīng)有一次對(duì)比測(cè)試mysqldump啟用extended-insert和未啟用導(dǎo)出的SQL腳本,后者比前者慢了不止5倍.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
下面是詳細(xì)的測(cè)試案例過(guò)程,有興趣的同學(xué)可以看看:MYSQL數(shù)據(jù)庫(kù)
DROP TABLE IF EXISTS `mytab`;
CREATE TABLE `mytab` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c1` int(11) NOT NULL DEFAULT '0',
`c2` int(11) NOT NULL DEFAULT '0',
`c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`c4` varchar(200) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB;MYSQL數(shù)據(jù)庫(kù)
DELIMITER $$$
DROP PROCEDURE IF EXISTS `insert_mytab`;MYSQL數(shù)據(jù)庫(kù)
CREATE PROCEDURE `insert_mytab`(in rownum int, in commitrate int)
BEGIN
DECLARE i INT DEFAULT 0;MYSQL數(shù)據(jù)庫(kù)
SET AUTOCOMMIT = 0;MYSQL數(shù)據(jù)庫(kù)
WHILE i < rownum DO INSERT INTO mytab(c1, c2, c3,c4) VALUES( FLOOR(RAND()*rownum),FLOOR(RAND()*rownum),NOW(), REPEAT(CHAR(ROUND(RAND()*255)),200)); SET i = i+1; /* 達(dá)到每 COMMITRATE 頻率時(shí)提交一次 */ IF (commitrate > 0) AND (i % commitrate = 0) THEN
COMMIT;
SELECT CONCAT('commitrate: ', commitrate, ' in ', I);
END IF;MYSQL數(shù)據(jù)庫(kù)
END WHILE;MYSQL數(shù)據(jù)庫(kù)
/* 最終再提交一次,確保成功 */
COMMIT;
SELECT 'ALL COMMIT;';MYSQL數(shù)據(jù)庫(kù)
END; $$$MYSQL數(shù)據(jù)庫(kù)
#測(cè)試調(diào)用
call insert_mytab(300000, 1); -- 每次一提交
call insert_mytab(300000, 10); -- 每10次一提交
call insert_mytab(300000, 100); -- 每100次一提交
call insert_mytab(300000, 1000); -- 每1千次一提交
call insert_mytab(300000, 10000); -- 每1萬(wàn)次提交
call insert_mytab(300000, 100000); -- 每10萬(wàn)次一提交
call insert_mytab(300000, 0); -- 一次性提交MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
測(cè)試耗時(shí)結(jié)果對(duì)比:MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址:
http://www.fzlkiss.com/jiaocheng/5805.html