最近做的專案，有個需求(從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（示例跳過totoal_flow_size 和 direction）
（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年內把代碼寫好，技術博客字字推敲，堅持零拷貝和原創
- 寫博客的意義在于打磨文筆，訓練邏輯條理性，加深對知識的系統性理解；如果恰好又對別人有點幫助，那真是一件令人開心的事
*******************************************************************************************
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/22850.html
標籤：MySQL
上一篇：MySQL 查詢陳述句執行程序