對于網(wǎng)站開發(fā)而言當你需要在 MySQL 數(shù)據(jù)庫中批量插入數(shù)百萬條數(shù)據(jù)時，你就會意識到，逐條發(fā)

INSERT 語句并不是一個可行的方法。

MySQL 文檔中有些值得一讀的 INSERT 優(yōu)化技巧。在這篇文章里，我將概述高效加載數(shù)據(jù)到 MySQL 數(shù)據(jù)庫的兩大技術。

LOAD DATA INFILE

如果你正在尋找提高原始性能的方案，這無疑是你的首選方案。LOAD DATA INFILE 是一個專門為 MySQL 高度優(yōu)化的語句，它直接將數(shù)據(jù)從 CSV / TSV 文件插入到表中。

有兩種方法可以使用 LOAD DATA INFILE。你可以把數(shù)據(jù)文件拷貝到服務端數(shù)據(jù)目錄（通常 /var/lib/mysql-files/），并且運行：

這個方法相當麻煩，因為你需要訪問服務器的文件系統(tǒng)，為數(shù)據(jù)文件設置合適的權限等。好消息是，你也能將數(shù)據(jù)文件存儲在客戶端，并且使用 LOCAL 關鍵詞：

在這種情況下，企業(yè)網(wǎng)站設計，品牌網(wǎng)站建設,從客戶端文件系統(tǒng)中讀取文件，將其透明地拷貝到服務端臨時目錄，然后從該目錄導入?？偠灾?，這幾乎與直接從服務器文件系統(tǒng)加載文件一樣快，不過，你需要確保服務器啟用了此 選項。

LOAD DATA INFILE 有很多可選項，主要與數(shù)據(jù)文件的結(jié)構(gòu)有關（字段分隔符、附件等）。請瀏覽 文檔 以查看全部內(nèi)容。

雖然從性能角度考慮， LOAD DATA INFILE 是最佳選項，但是這種方式需要你先將數(shù)據(jù)以逗號分隔的形式導出到文本文件中。如果你沒有這樣的文件，你就需要花費額外的資源來創(chuàng)建它們，并且可能會在一定程度上增加應用程序的復雜性。幸運的是，還有一種另外的選擇。

擴展的插入語句（Extended inserts）

一個典型的 INSERT SQL 語句是這樣的：

extended INSERT 將多條插入記錄聚合到一個查詢語句中：

關鍵在于找到每條語句中要插入的記錄的最佳數(shù)量。沒有一個放之四海而皆準的數(shù)字，因此，你需要對數(shù)據(jù)樣本做基準測試，以找到性能收益的最大值，或者在內(nèi)存使用和性能方面找到最佳折衷。

為了充分利用 extended insert，我們還建議：

1.使用預處理語句

2.在事務中運行該語句

基準測試

我要插入 120 萬條記錄，每條記錄由 6 個 混合類型數(shù)據(jù)組成，平均每條數(shù)據(jù)約 26 個字節(jié)大小。我使用了兩種常見的配置進行測試：

1. 客戶端和服務端在同一機器上，通過 UNIX 套接字進行通信

2.客戶端和服務端在不同的機器上，通過延遲非常低（小于 0.1 毫秒）的千兆網(wǎng)絡進行通信

作為比較的基礎，我使用 INSERT ... SELECT 復制了該表，這個操作的性能表現(xiàn)為每秒插入 313,000 條數(shù)據(jù)。

LOAD DATA INFILE

令我吃驚的是，測試結(jié)果證明 LOAD DATA INFILE 比拷貝表更快：

1.LOAD DATA INFILE：每秒 377,000 次插入

2.LOAD DATA LOCAL INFILE 通過網(wǎng)絡：每秒 322,000 次插入

這兩個數(shù)字的差異似乎與從客戶端到服務端傳輸數(shù)據(jù)的耗時有直接的關系：數(shù)據(jù)文件的大小為 53 MB，兩個基準測試的時間差了 543 ms，這表示傳輸速度為 780 mbps，接近千兆速度。

這意味著，很有可能，在完全傳輸文件之前，MySQL 服務器并沒有開始處理該文件：因此，插入的速度與客戶端和服務端之間的帶寬直接相關，如果它們不在同一臺機器上，考慮這一點則非常重要。

Extended inserts

我使用 BulkInserter 來測試插入的速度，BulkInserter 是我編寫的 開源庫 PHP 類的一部分，每個查詢最多插入 10,000 條記錄：

正如我們所看到的，隨著每條查詢插入數(shù)的增長，插入速度也會迅速提高。與逐條插入速度相比，我們在本地主機上性能提升了 6 倍，在網(wǎng)絡主機上性能提升了 17 倍：

1. 在本地主機上每秒插入數(shù)量從 40,000 提升至 247,000

2. 在網(wǎng)絡主機上每秒插入數(shù)量從 1,2000 提升至 201,000

這兩種情況都需要每個查詢大約 1,000 個插入來達到最大吞吐量。但是每條查詢 40 個插入就足以在本地主機上達到 90% 的吞吐量，這可能是一個很好的折衷。還需要注意的是，達到峰值之后，隨著每個查詢插入數(shù)量的增加，性能實際上是會下降。

extended insert 的優(yōu)勢在網(wǎng)絡連接的情況下更加明顯，因為連續(xù)插入的速度取決于你的網(wǎng)絡延遲。

客戶端和服務端之間的延遲越高，你從 extended insert 中獲益越多。

結(jié)論

不出所料，LOAD DATA INFILE 是在單個連接上提升性能的首選方案。它要求你準備格式正確的文件，如果你必須先生成這個文件，并/或?qū)⑵鋫鬏數(shù)綌?shù)據(jù)庫服務器，那么在測試插入速度時一定要把這個過程的時間消耗考慮進去。

另一方面，extended insert 不需要臨時的文本文件，并且可以達到相當于 LOAD DATA INFILE 65% 的吞吐量，這是非常合理的插入速度。有意思的是，無論是基于網(wǎng)絡還是本地主機，聚集多條插入到單個查詢總是能得到更好的性能。

如果你決定開始使用 extended insert，一定要先用生產(chǎn)環(huán)境的數(shù)據(jù)樣本和一些不同的插入數(shù)來測試你的環(huán)境，以找出最佳的數(shù)值。

在增加單個查詢的插入數(shù)的時候要小心，因此它可能需要：

1.在客戶端分配更多的內(nèi)存

2.增加 MySQL 服務器的 max_allowed_packet 參數(shù)配置。

最后，值得一提的是，根據(jù) Percona 的說法，你可以使用并發(fā)連接、分區(qū)以及多個緩沖池，以獲得更好的性能。

基準測試運行在裝有 Centos 7 和 MySQL 5.7 的裸服務器上，它的主要硬件配置有 Xeon E3 @3.8 GHz 處理器，32 GB RAM 和 NVMe SSD。MySQL 的基準表使用 InnoBD 存儲引擎。

基準測試的源代碼保存在 gist 上，結(jié)果圖保存在 plot.ly 上。