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"的許可權支持
mysqlcur.execute("SET GLOBAL local_infile = 1") （2）需要對mysql文件目錄（筆者： “/var/lib/my-files/”）具有管理員的許可權（查看mysql路徑，用“locate mysql”） 如果沒有的話，可以指定本地路徑（速度大概要慢%20），需要加上關鍵字"local"即：LOAD DATA LOCAL
（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年內把代碼寫好，技術博客字字推敲，堅持零拷貝和原創
- 寫博客的意義在於打磨文筆，訓練邏輯條理性，加深對知識的系統性理解；如果恰好又對別人有點幫助，那真是一件令人開心的事
*******************************************************************************************
