MySQL LOAD DATA INFILE—批量從文件（csv、txt）導入數據

 最近做的項目，有個需求(從Elastic Search取數據，業務運算後），每次要向MySQL插入1300萬條數據左右。最初用MySQL的executemany()一次插入10000條數據，統計的時間如下：

 如上，插入時間由於系統的IO變化，會有波動，最快在4秒左右。

 後改為"load data infile"大概，10萬條數據平均1秒~1.5秒，實際的代碼示例如下：

query = "LOAD DATA INFILE '/var/lib/mysql-files/es.csv' INTO TABLE g_visit_relation_asset_temp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES \
                        (srcip, srcport, dstip, dstport, l7p, @dummy, cnt, @dummy, cnt_date)"
mysqlcur.execute(query)
mysqlconn.commit()

說明：

（1）MySQL需要開啟對"load data inflie"的許可權支持

（3）Concurrency 支持

 如果預設是 LOW_PRIORITY ，則LOAD DATA要等其它客戶端讀完了，才會開始寫入。加上“Concurrency ”可以，在讀的同時，同時支持寫入，不過速度會稍微下降一點，筆者測試環境影響不大

（4）IGNORE 1 LINES （跳過第一行）

 筆者通過python pandas to_csv()導出的csv是帶標題的，如下：

不需要標題導入到資料庫，就調過嘛

（5）@dummy ，通過占位符，跳過不需要的數據

導入到表的column順序必須和文件保持一致，通過@dummy可以跳過不需要的column

（6）character set 指定字元集

對於漢字，你需要加上 character set utf8

（8）分隔符及換行符

 以“，“作為分隔符，以“\n"作為換行符： FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'

其他性能優化相關（Only for MyISAM）：

通過設置隔離級別、去除索引檢查、唯一性檢查等提高速度（分session和global級別）提高寫入速度，插入之前，設置如下配置：

    mysqlcur.execute("SET SESSION FOREIGN_KEY_CHECKS = 0")
    mysqlcur.execute("SET SESSION UNIQUE_CHECKS = 0")
    mysqlcur.execute("SET SESSION tx_isolation='READ-UNCOMMITTED'")
    mysqlcur.execute("SET SESSION sql_log_bin = 0")

Loda data infile 完了再改回去，如下：

    mysqlcur.execute("SET SESSION FOREIGN_KEY_CHECKS = 1")
    mysqlcur.execute("SET SESSION UNIQUE_CHECKS = 1")
    mysqlcur.execute("SET SESSION tx_isolation='REPEATABLE-READ'")
    mysqlcur.execute("SET SESSION sql_log_bin = 1")

“DISABLE KEYS” 然後 “ENABLE KEYS”，筆者實際測試沒什麼用，只是導入數據更快，總的時間並沒有提升。區別在於：一個是插入一條，創建一個索引；一個是全部導入完了後，再一次創建所有索引。

引用：

•  如何導入5億條數據到mysql — https://derwiki.tumblr.com/post/24490758395/loading-half-a-billion-rows-into-mysql （自備梯子）
•  MySQL 官方文檔說明 — https://dev.mysql.com/doc/refman/8.0/en/load-data.html

*******************************************************************************************

精力有限，想法太多，專註做好一件事就行

• 我只是一個程式猿。5年內把代碼寫好，技術博客字字推敲，堅持零拷貝和原創
• 寫博客的意義在於打磨文筆，訓練邏輯條理性，加深對知識的系統性理解；如果恰好又對別人有點幫助，那真是一件令人開心的事

*******************************************************************************************