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 寫入的情況下, 反而會更慢。我沒有實際用過。
    最後, 遇到效能問題時, 一定要在自己的環境實測才準, 了解概念和實測同等重要。

    留言

    這個網誌中的熱門文章

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

    熟悉系統工具好處多多

    virtualbox 使用 USB 裝置