搜尋

隨機推薦

22 十一月 2011

對 MySQL 的 INSERT 進行效能優化

MYSQL優化之加速 INSERT插入一條記錄花費的時間由以下幾個因素決定,後面的數字大致表示影響的比例:
  1. 連接:(3)
  2. 發送查詢給服務器:(2)
  3. 解析查詢:(2)
  4. 插入記錄:(1 x 記錄大小)
  5. 插入索引:(1 x 索引數量)
  6. 關閉:(1) 

這裡並沒有考慮初始化時打開數據表的開銷,因為每次運行查詢只會做這麼一次。

如果是 B-tree 索引的話,隨著索引數量的增加,插入記錄的速度以 log N 的比例下降。

 

廣告

可以使用以下幾種方法來提高插入速度: 

  • 如果要在同一個客戶端在同一時間內插入很多記錄,可以使用 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 讓數據表變得更小點。詳情查看"15.1.3.3 Compressed Table Characteristics"。
    • 運行 myisamchk -r -q /path/to/db/tbl_name 重建索引。創建的索引樹在寫入磁盤前先保存在內存中,這省去了磁盤搜索,因此速度快多了。重建後的索引樹分佈非常均衡。
    •       執行 FLUSH TABLES 語句或 mysqladmin flush-tables 命令。

注意,LOAD DATA INFILE 將數據插入一個空表時,也會做前接優化;主要的不同在於:運行 myisamchk 會分配更多的臨時內存用於創建索引,而執行 LOAD DATA INFILE 命令則是讓數據庫服務器分配內存用於重建索引。從 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,34),(4,33); 
INSERT INTO a VALUES (8,26),(6,29); 
UNLOCK TABLES;

 

這對性能提高的好處在於:直到所有的 INSERT 語句都完成之後,索引緩存一次性刷新到磁盤中。通常情況是,多有少次 INSERT 語句就會有多數次索引緩存刷新到磁盤中的開銷。如果能在一個語句中一次性插入多個值的話,顯示的鎖表操作也就沒必要了。對事務表而言,用 BEGIN/COMMIT 代替 LOCK TABLES 來提高速度。鎖表也回降低多次連接測試的總時間,儘管每個獨立連接為了等待鎖的最大等待時間也會增加。例如: 參考資料:www.svn8.com

 


Connection 1 does 1000 inserts 
Connections 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個插入或者更新時最好加鎖以得到更好的性能。如果要一次性做很多個插入,最好是在每個循環(大約1000次)的前後加上 LOCK TABLES 和 UNLOCK TABLES,從而讓其他進程也能訪問數據表;這麼做性能依然不錯。INSERT 總是比 LOAD DATA INFILE 插入數據來得慢,因為二者的實現策略有著分明的不同。

 

  • 想要讓 MyISAM 表更快,在 LOAD DATA

INFILE 和 INSERT 時都可以增加系統變量 key_buffer_size 的值。

廣告

无觅相关文章插件,快速提升流量

Please publish modules in offcanvas position.