問題描述：

由於測試需要，需要將生產庫的一些數據導入到測試庫進行開發測試。一些表的數據量有些多，達到6W+條，從生產庫導出的文件達到10.6M，內容是每行一條插入語句（INSERT INTO TableName VALUES(…);）。

將這個導出的.sql文件通過工具載入到測試庫後，點擊了“運行”，只見信息欄一條條SQL語句“瘋狂”的執行著，心想，應該很快應該執行完了吧，但是等了好一會兒，見到信息欄還在一條條努力的輸出著插入的消息。

仔細看了下控制檯，發現平均每條語句執行0.03s以上，多的甚至達到了0.09s，算了一下，如果按每條0.03s的速度執行，插入完這批數據一共需要花費0.03s/條*66176條=1985.28s，也就是大概33.088分鐘！

這個時候，SQL已經執行了993.598s，也就是16多分鐘，還要再執行16多分鐘，才能將66176條數據插入完畢，這顯然是不能忍的！

查詢時間

分析解決嘗試中。。。

為什麼會這麼慢呢？後來分析了一下，每個insert語句，大體都要經過發送數據到服務器端、解析SQL、預處理、優化SQL執行計劃等步驟，有沒有哪個步驟是可以優化或減少的呢？

順著這個思路，後來把SQL語句改造了一下：

SQL改造

只保留一個INSERT INTO tableName，剩下的改為逗號相隔。這樣做的好處，一是減少文件大小（改造前.sql文件10.6M，改造後，文件大小為8.09M），減少發送到服務器的時間；二是減少SQL語句多餘的解析操作，改造後，只需要解析一次就可以進行數據插入操作。

後來通過工具的“狀態”欄也可以證明這一點：

改造前後對比

狀態

在尋求解決方案過程中，曾想到多線程，想找到一種像oracle中的並行操作技術parallel

insert /*+parallel(t 16)*/ into tableName(...)

但是沒有找到，但無意中發現了一種更快的方法：加上事務！

START TRANSACTION;

INSERT INTO TABLE_NAME VALUES(...), (...), (...), ...;

COMMIT;

原因是：在進行insert操作時，mysql內部會建立一個事務，在事務內進行真正的處理。主動加上事務語句，可以減少創建事務的操作，減少執行時間。

通過這種方法，我做了下嘗試，結果如下：

執行結果

相比於一條條插入語句，效率提高了100多倍！

總結：

1. 通過改造SQL語句可以減少網絡傳輸IO，減少解析時間，提高批量插入速度。通過主動創建事務，進一步減少執行時間。
   

2. 這裡需要注意的是，發送到服務器端的數據大小是有默認限制的，有的是1M，有的是4M，可以通過改動max_allowed_packet配置跳過該限制：

   方法一（具有linux權限）：改動服務器的mysql配置文件my.cnf，增加：

   max_allowed_packet=20M, 然後重啟mysql服務

   方法二（具有mysql修改配置權限）：執行SQL語句，set global max_allowed_packet=20*1024*1024

閱讀更多 京京肚肚擼代碼 的文章

關鍵字: Linux SQL 導入到