程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 探究MySQL中索引和提交頻率對InnoDB表寫入速度的影響,mysqlinnodb

探究MySQL中索引和提交頻率對InnoDB表寫入速度的影響,mysqlinnodb

編輯:MySQL綜合教程

探究MySQL中索引和提交頻率對InnoDB表寫入速度的影響,mysqlinnodb


本次,我們來看看索引、提交頻率對InnoDB表寫入速度的影響,了解有哪些需要注意的。

先直接說幾個結論吧:

1、關於索引對寫入速度的影響:
a、如果有自增列做主鍵,相對完全沒索引的情況,寫入速度約提升 3.11%;
b、如果有自增列做主鍵,並且二級索引,相對完全沒索引的情況,寫入速度約降低 27.37%;

因此,InnoDB表最好總是有一個自增列做主鍵。

2、關於提交頻率對寫入速度的影響(以表中只有自增列做主鍵的場景,一次寫入數據30萬行數據為例):

a、等待全部數據寫入完成後,最後再執行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倍;

因此,最好是等待所有事務結束後再批量提交,而不是每執行完一個SQL就提交一次。
曾經有一次對比測試mysqldump啟用extended-insert和未啟用導出的SQL腳本,後者比前者慢了不止5倍。
重要:這個建議並不是絕對成立的,要看具體的場景。如果是一個高並發的在線業務,就需要盡快提交事務,避免鎖范圍被擴大。但如果是在非高並發的業務場景,尤其是做數據批量導入的場景下,就建議采用批量提交的方式。

下面是詳細的測試案例過程,有興趣的同學可以看看:

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; /* 達到每 COMMITRATE 頻率時提交一次 */ IF (commitrate > 0) AND (i % commitrate = 0) THEN
COMMIT;
SELECT CONCAT(‘commitrate: ‘, commitrate, ‘ in ‘, I);
END IF;

END WHILE;

/* 最終再提交一次,確保成功 */

COMMIT;
SELECT ‘ALL COMMIT;';

END; $$$

#測試調用
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); — 一次性提交

測試耗時結果對比:

201558110956408.png (455×173)

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved