Mysql insert性能優化

Mysql優化之加速INSERT插入一條記錄花費的時間由以下幾個因素決定,數字表示影響的比例:
 
連接:(3)
 
發送查詢給服務器:(2)
 
解析查詢:(2)
 
插入記錄:(1*記錄大小)
 
插入索引:(1*索引數量)
 
關閉:(1)
 
此處沒有考慮初始化時打開數據表的開銷,因為每次運行查詢隻會做這麼一次。
 
如果是B-tree索引,隨著索引數量的增加,插入記錄的速度以logN的比例下降。
 
可以用以下幾種方法來提高插入速度:
 
如果要在同一個客戶端在同一時間內插入很多記錄,可以使用INSERT語句附帶有多個values值。這種做法比使用單一值的INSERT語句快多瞭(在一些情況下比較快)。如果是往一個非空數據表增加記錄,可以調整變量bulk_insert_buffer_size的值使其更快。
 
如果要從不用的客戶端插入大量記錄,使用INSERT DELAYED語句也可以提高速度。
 
對應MyISAM,可以在SELECT語句正在運行時插入記錄,隻要這時候沒有正在刪除記錄。
 
想要將一個文本文件加載到數據表中,可以使用LOAD DATA INFILE。這通常是使用大量INSERT語句的20倍。
 
通過一些額外工作,就可以讓LOAD DATA INFILE在數據表有大量索引的情況下運行更快。步驟如下:
 
用create table隨表建一個表
 
執行FLUSH TABLES語句或mysqladmin flush-tables命令
 
執行myisamchk –keys-used=0 -rq /path/to/db/tbl_name命令,刪除數據表所有索引。
 
執行LOAD DATA INFILE,數據插入到表中,由於無需更新表索引,因此這將非常快。
 
如果將來隻是讀取該表,運行myisampack讓數據表更小。
 
運行myisamchk -r -q /path/to/db/tbl_name重建索引。創建的索引樹在寫入磁盤前先保存在內存中,這省去瞭磁盤磁盤搜索,因此速度快很多。重建後的索引樹分佈非常均衡。
 
執行FLUSH TABLES語句或mysqladmin flush-tables命令
 
註意,在Mysql 4.0起,可以運行ALTER TABLE tbl_name DISABLE KEYS來代替myisamchk –keys-used=0 -rq /path/to/db/tbl_name.運行ALTER TABLE tbl_name ENABLE KEYS代替myisamchk -r -q /path/to/db/tbl_name.這麼做就可以省去FLUSH TABLES步驟。
 
 
 
可以在鎖表後,一起執行幾個語句來加速INSERT操作:
 
LOCK TABLES a WRITE;
 
INSERT INTO a VALUES(1,23),(2,23);
 
INSERT INTO a VALUES(8,7);
 
UNLOCK TABLES;
 
這對性能提高的好處在於:直到所有的INSERT語句都完成之後,索引緩存一次性刷新到磁盤中。通常情況下,有多少次INSERT語句就會有多少次索引緩存刷新到磁盤中的開銷。如果能在一個語句中一次性插入多個值的話,顯然鎖表操作也沒有必要瞭。對於事務表而言,用BEGIN/COMMIT代替LOCK TABLES來提高速度。鎖表也會降低多次連接測試的總時間,盡管每個獨立連接為瞭等待鎖的最大等待時間也會增加。
 
Connection 1 does 1000 inserts
 
Connection 2,3 and 4 do 1 insert
 
Connection 5 does 1000 inserts
 
如果沒有鎖表,則連接2,3,4會在1,5之前完成。如果鎖表瞭,則連接2,3,4可能在1,5之後才能完成,但總時間可能隻需要40%。Mysql的INSERT、UPDATE、DELETE操作都非常快,不過在一個語句中如果超過5個插入或者更新時最好加鎖以得到更好的性能。如果要一次性做很多次插入,最好在每個循環的前後加上LOCK TABLES和UNLOCK TABLES,從而讓其他進程也能訪問數據表;這麼做性能依然不錯。INSERT總比LOAD DATA INFILE插入數據慢,因為二者實現策略有分明的不同。
 
想要MyISAM表更快,在LOAD DATA INFILE和INSERT時都可以增加系統變量key_buffer_size的值。
 

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *