跳到主要內容

MySQL 大量寫入的加速技巧 (bulk insert)

官網 Speed of INSERT Statements 詳細地解釋各種加速方式和它背後做的運算。結合個人經驗, 在這裡摘要相關訊息:
  • INSERT 時會依序做 connect、send query、parse query、insert record、update index。雖然可以用 prepared statement 減少 parse query 的時間, 最耗時間的應該還是 update index, 省時的關鍵在於如何減少 update index。
  • 有兩個方向可以達到這個目的: 減少 insert 次數或是減少 update index 次數。
最佳解: 一次搞定
  • LOAD DATA INFILE 最快。這不難理解, 但我沒試過就是了。
  • INSERT ... SELECT ... 也是好方法, 類似 LOAD DATA INFILE, 只是資料來源是 MySQL 本身。讓 MySQL 自己一次搞定取資料和寫資料的操作, MySQL 有最多的彈性決定處理資料的順序。
次佳解: 減少 insert 次數
  • INSERT ... VALUES ... (ref: 在該頁搜 values): 一次寫入多筆資料自然能減少 update index 次數, 不過資料太多時可能會超出 SQL 長度限制, 看到相關錯誤訊息時, 記得改 my.cnf 調高長度。我自己覺得一次寫過多筆應該會出問題 (單次操作用過大的記憶體, 不會是好事), 通常都一次寫入個一萬筆或十萬筆。實測的感覺兩者沒差太多, 百筆以下會比較慢。
次佳解: 減少 update index 次數
  • LOCK TABLES 後, MySQL 就不會急著 update index, 而會等 UNLOCK TABLES 後再一次更新 index。若不在意其它 thread 會用到這個 table, 用 LOCK TABLES 可在多次 insert 的情況下省下不少 update index 的時間。
  • DISABLE KEYS 和 ENABLE KEYS (ref: 在該頁搜這兩個 keyword): 這是 MyISAM 才有的功能, 並且只能用在 non-unique index, 因為 insert 時需要檢查 unique constraint, 不能暫時關掉。實測後效果並不好, 雖然 insert 時有省下時間, 但是連同 enable keys 時重建 index 的時間, 整體來說反而變慢。
其它相關的選擇
  • INSERT ... ON DUPLICATE KEY UPDATE: 這個語法相當威, 寫入資料, 發覺違反 unique constraint 後, 再執行指定的更新操作 (合併資料) 。批次寫入常會遇到一些小狀況, 用這招省了不少事。若改拆成批次 insert 和批次 update (用 join update), 相對來說會慢上不少。
  • 先 drop unique constraint、drop indexes 再寫入, 寫完再重建 unique constraint、index: 這方法反而是最慢的。問題出在 MyISAM 在修改 schema 時會重建 data file 或 index file, 若有三個 index, 寫完資料後下三次 CREATE INDEX 的 SQL, 結果 MySQL 會重建三次 index file, 每次都會讀出全部資料, 加好新 index, 再寫入新的檔案, 移掉舊檔。附帶一提, 上面的 DISABLE KEYS 和 ENABLE KEYS 比這個作法快, 因為 ENABLE KEYS 後是一次重建全部 non-unique indexes, 不是有幾個 non-unique indexes 就重建幾次檔案。
多個 client 同時寫入的其它選擇
    • INSERT DELAYED 讓 client 送完資料立即結束操作, 可提高各 client 減少的時間, 也能集中寫入資料, 一次寫入到硬碟。但是, 整體來說 overhead 較高, 若是單一 client 寫入的情況下, 反而會更慢。我沒有實際用過。
    最後, 遇到效能問題時, 一定要在自己的環境實測才準, 了解概念和實測同等重要。

    留言

    這個網誌中的熱門文章

    virtualbox 使用 USB 裝置

    2012-12-16 更新 現在 (4.x 版) 似乎無需做任何設定, 只要有裝 Oracle VM VirtualBox Extension Pack, 在 VirtualBox 視窗右下角按 USB 的圖示, 再點目標裝置, 即可加入或移除該裝置 同一時間只有 host 或 guest 可擁有該裝置, 所以從 guest OS 移除, 相當於接回 host OS 目前 VirtualBox 只支援 USB 2.0 的插槽, 若偵測不到時, 注意一下是否為這個問題 有時拔拔插插, VirtualBox 會進入奇怪的狀態, 接上去 guest OS 無法連接且跳出 device is busy 的錯誤訊息。試看看拔除該裝置, 重開 guest OS (續上則) 若重開 guest OS 無效, 並且 host OS 已移除該裝置, VirtualBox 的 USB 清單卻仍顯示 "captured", 試看看拔除該裝置, 重開 host OS原文網路上搜一下, 比較多是 Ubuntu 當 host 的解法, 我的情況是 Win7 當 host, Ubuntu 當 guest。 這兩篇說明很詳細《Learn How to Set Up USB and Networking Options in VirtualBox》《幻影千瞳的部落格: VirtualBox 使用筆記(二):使用 USB 裝置》 現在的版本圖形介面很好用了, 不用像第二篇說的那樣用指令操作。這裡記下我的操作步驟: 關掉 guest OS 在 VirtualBox 選單, 選擇 guest OS -> Settings -> USB -> Enable USB 2.0 會出現訊息框, 說明要安裝 Oracle VM VirtualBox Extension Pack。下載後安裝它 host OS 插入 USB 隨身碟 在 VirtualBox 選單, 選擇 guest OS -> Settings -> USB, 點右邊有綠色 "+" 的 USB 頭的圖示, 選擇該 USB 隨身碟, 加入它的 filter 從 host OS 移除 USB 隨身碟 開啟 guest OS 插入 USB 隨身碟, 於是 guest OS 會自動偵測…

    (C/C++ ) 如何在 Linux 上使用自行編譯的第三方函式庫

    以使用 LevelDB 為例。 抓好並編好相關檔案,編譯方式見第三方函式庫附的說明:$ ls include/ # header files leveldb/ $ ls out-shared/libleveldb.so* # shared library out-shared/libleveldb.so@ out-shared/libleveldb.so.1@ out-shared/libleveldb.so.1.20* 下面的例子用 clang++ 編譯,這裡用到的參數和 g++ 一樣。 問題一:找不到 header$ clang++ sample.cpp sample.cpp:5:10: fatal error: 'leveldb/db.h' file not found #include "leveldb/db.h" ^ 1 error generated. 解法:用 -I 指定 header 位置 問題二:找不到 shared library$ clang++ sample.cpp -I include/ /tmp/sample-2e7dd8.o: In function `main': sample.cpp:(.text+0x1e): undefined reference to `leveldb::Options::Options()' sample.cpp:(.text+0x6f): undefined reference to `leveldb::DB::Open(leveldb::Options const&, std::string const&, leveldb::DB**)' sample.cpp:(.text+0x10c): undefined reference to `leveldb::Status::ToString() const' sample.cpp:(.text+0x7d0): undefined reference to `leveldb::Status::ToString() const' clang: error: linker command failed with exit code 1 (u…

    解決 undefined symbol / reference

    C++ 新手上路, 有錯還請幫忙指正。 基本觀念相較於 script language 或 Java 來說, C/C++ 有完整的「編譯 -> 連結 -> 執行」三個階段, 各階段都可能發生 undefined symbol。在解決惱人的 undefined symbol 前, 得先明白整個編譯流程: 編譯 .c / .cpp 為 .o (object file) 時, 需要提供 header 檔 (用到 gcc 參數 -I)。事實上, 在編譯單一檔案時, gcc/g++ 根本不在意真正的 symbol 是否存在, 反正有宣告它就信了, 所以有引對 header 即可。這也是可分散編譯的原因 (如 distcc ), 程式之間在編譯成 .o 檔時, 並沒有相依性。 用 linker (ld 或 gold) 將 *.o 連結成 dynamic library 或執行檔時, 需要提供要連結的 library (用到 gcc 參數 -L 指定目錄位置, 用 -l 指定要連什麼函式庫)。不同於前一步, 此時 symbol 一定要在。 執行的時候, 會再動態開啟 shared library 讀出 symbol。換句話說, 前一個步驟只是檢查是否有。檢查通過也連結成 executable 或 shared library 後, 若執行時對應的檔案不見了, 仍會在執行期間找不到 symbol。若位置沒設好, 可能需要用 LIB_LIBRARY_PATH 指定動態函式的位置, 但不建議這麼做, 最好在執行 linker 時就指定好位置。原因見《Why LD_LIBRARY_PATH is bad》。明白這點後, 就看 undefined symbol 發生在那個階段, 若是編 object file 時發生, 就是沒和編譯器說 header 檔在那, 記得用 -I 告訴它。若在 linking 時發生, 就要同時設好 -L 和 -l。不過難就難在要去那找 undefined symbol 的出處。 解決問題的流程首先是判斷 symbol 是不是自己用到的原始碼裡, 可配合 id-utils 找看看 (我是用 gj, 比較方便一點)。或是看有沒有 man page, 有 man page 的話, 裡面會記錄用到的 header 和該怎麼下連結參數。若在專案裡找不到, …