探究MySQL中索引和提交頻率對(duì)InnoDB表寫入速度的影響
本次,我們來(lái)看看索引、提交頻率對(duì)InnoDB表寫入速度的影響,了解有哪些需要注意的。
先直接說(shuō)幾個(gè)結(jié)論吧:
1、關(guān)于索引對(duì)寫入速度的影響:
a、如果有自增列做主鍵,相對(duì)完全沒(méi)索引的情況,寫入速度約提升 3.11%;
b、如果有自增列做主鍵,并且二級(jí)索引,相對(duì)完全沒(méi)索引的情況,寫入速度約降低 27.37%;
因此,InnoDB表最好總是有一個(gè)自增列做主鍵。
2、關(guān)于提交頻率對(duì)寫入速度的影響(以表中只有自增列做主鍵的場(chǎng)景,一次寫入數(shù)據(jù)30萬(wàn)行數(shù)據(jù)為例):
a、等待全部數(shù)據(jù)寫入完成后,最后再執(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就提交一次。
曾經(jīng)有一次對(duì)比測(cè)試mysqldump啟用extended-insert和未啟用導(dǎo)出的SQL腳本,后者比前者慢了不止5倍。
重要:這個(gè)建議并不是絕對(duì)成立的,要看具體的場(chǎng)景。如果是一個(gè)高并發(fā)的在線業(yè)務(wù),就需要盡快提交事務(wù),避免鎖范圍被擴(kuò)大。但如果是在非高并發(fā)的業(yè)務(wù)場(chǎng)景,尤其是做數(shù)據(jù)批量導(dǎo)入的場(chǎng)景下,就建議采用批量提交的方式。
下面是詳細(xì)的測(cè)試案例過(guò)程,有興趣的同學(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; $$$ #測(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); — 一次性提交
測(cè)試耗時(shí)結(jié)果對(duì)比:
- MySQL找出未提交事務(wù)的SQL實(shí)例淺析
- mysql實(shí)現(xiàn)事務(wù)的提交與回滾的實(shí)例詳解
- MySQL找出未提交事務(wù)信息的方法分享
- Mysql事務(wù)隔離級(jí)別之讀提交詳解
- php將textarea數(shù)據(jù)提交到mysql出現(xiàn)很多空格的解決方法
- mysql實(shí)現(xiàn)事務(wù)的提交和回滾實(shí)例
- python連接mysql并提交mysql事務(wù)示例
- JSP+ MySQL中文亂碼問(wèn)題post提交亂碼解決方案
- 詳解MySQL與Spring的自動(dòng)提交(autocommit)
相關(guān)文章
mysql中l(wèi)imit查詢踩坑實(shí)戰(zhàn)記錄
在MySQL中我們常常用order by來(lái)進(jìn)行排序,使用limit來(lái)進(jìn)行分頁(yè),下面這篇文章主要給大家介紹了關(guān)于mysql中l(wèi)imit查詢踩坑的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-03-03麒麟系統(tǒng)上安裝?MySQL?8.0.24的詳細(xì)步驟(避坑指南)
這篇文章主要介紹了麒麟系統(tǒng)上安裝MySQL8.0.24的詳細(xì)步驟,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2023-08-08MySQL學(xué)習(xí)(七):Innodb存儲(chǔ)引擎索引的實(shí)現(xiàn)原理詳解
這篇文章主要介紹了Innodb存儲(chǔ)引擎索引的實(shí)現(xiàn),文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-04-04詳細(xì)聊一聊mysql的樹(shù)形結(jié)構(gòu)存儲(chǔ)以及查詢
由于mysql是關(guān)系型數(shù)據(jù)庫(kù),因此對(duì)于類似組織架構(gòu),子任務(wù)等相關(guān)的樹(shù)形結(jié)構(gòu)的處理不是很友好,下面這篇文章主要給大家介紹了關(guān)于mysql樹(shù)形結(jié)構(gòu)存儲(chǔ)以及查詢的相關(guān)資料,需要的朋友可以參考下2022-04-04mysql事務(wù)的基本要素與事務(wù)隔離級(jí)別詳解
這篇文章主要介紹了mysql事務(wù)的基本要素與事務(wù)隔離級(jí)別詳解,事務(wù)是一種機(jī)制、一個(gè)操作序列,包含了一組數(shù)據(jù)庫(kù)操作命令,并且把所有的命令作為一個(gè)整體一起向系統(tǒng)提交或撤銷操作請(qǐng)求,需要的朋友可以參考下2023-08-08