探究MySQL中索引和提交頻率對InnoDB表寫入速度的影響
本次,我們來看看索引、提交頻率對InnoDB表寫入速度的影響,了解有哪些需要注意的。
先直接說幾個(gè)結(jié)論吧:
1、關(guān)于索引對寫入速度的影響:
a、如果有自增列做主鍵,相對完全沒索引的情況,寫入速度約提升 3.11%;
b、如果有自增列做主鍵,并且二級索引,相對完全沒索引的情況,寫入速度約降低 27.37%;
因此,InnoDB表最好總是有一個(gè)自增列做主鍵。
2、關(guān)于提交頻率對寫入速度的影響(以表中只有自增列做主鍵的場景,一次寫入數(shù)據(jù)30萬行數(shù)據(jù)為例):
a、等待全部數(shù)據(jù)寫入完成后,最后再執(zhí)行commit提交的效率最高;
b、每10萬行提交一次,相對一次性提交,約慢了1.17%;
c、每1萬行提交一次,相對一次性提交,約慢了3.01%;
d、每1千行提交一次,相對一次性提交,約慢了23.38%;
e、每100行提交一次,相對一次性提交,約慢了24.44%;
f、每10行提交一次,相對一次性提交,約慢了92.78%;
g、每行提交一次,相對一次性提交,約慢了546.78%,也就是慢了5倍;
因此,最好是等待所有事務(wù)結(jié)束后再批量提交,而不是每執(zhí)行完一個(gè)SQL就提交一次。
曾經(jīng)有一次對比測試mysqldump啟用extended-insert和未啟用導(dǎo)出的SQL腳本,后者比前者慢了不止5倍。
重要:這個(gè)建議并不是絕對成立的,要看具體的場景。如果是一個(gè)高并發(fā)的在線業(yè)務(wù),就需要盡快提交事務(wù),避免鎖范圍被擴(kuò)大。但如果是在非高并發(fā)的業(yè)務(wù)場景,尤其是做數(shù)據(jù)批量導(dǎo)入的場景下,就建議采用批量提交的方式。
下面是詳細(xì)的測試案例過程,有興趣的同學(xué)可以看看:
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; DELIMITER $$$ DROP PROCEDURE IF EXISTS `insert_mytab`; CREATE PROCEDURE `insert_mytab`(in rownum int, in commitrate int) BEGIN DECLARE i INT DEFAULT 0; SET AUTOCOMMIT = 0; 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; END WHILE;
/* 最終再提交一次,確保成功 */
COMMIT; SELECT ‘ALL COMMIT;'; END; $$$ #測試調(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萬次提交 call insert_mytab(300000, 100000); — 每10萬次一提交 call insert_mytab(300000, 0); — 一次性提交
測試耗時(shí)結(jié)果對比:
相關(guān)文章
mysql中l(wèi)imit查詢踩坑實(shí)戰(zhàn)記錄
在MySQL中我們常常用order by來進(jìn)行排序,使用limit來進(jìn)行分頁,下面這篇文章主要給大家介紹了關(guān)于mysql中l(wèi)imit查詢踩坑的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-03-03麒麟系統(tǒng)上安裝?MySQL?8.0.24的詳細(xì)步驟(避坑指南)
這篇文章主要介紹了麒麟系統(tǒng)上安裝MySQL8.0.24的詳細(xì)步驟,本文給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2023-08-08MySQL學(xué)習(xí)(七):Innodb存儲引擎索引的實(shí)現(xiàn)原理詳解
這篇文章主要介紹了Innodb存儲引擎索引的實(shí)現(xiàn),文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-04-04詳細(xì)聊一聊mysql的樹形結(jié)構(gòu)存儲以及查詢
由于mysql是關(guān)系型數(shù)據(jù)庫,因此對于類似組織架構(gòu),子任務(wù)等相關(guān)的樹形結(jié)構(gòu)的處理不是很友好,下面這篇文章主要給大家介紹了關(guān)于mysql樹形結(jié)構(gòu)存儲以及查詢的相關(guān)資料,需要的朋友可以參考下2022-04-04