使用batch insert解決MySQL的insert吞吐量問題

最近使用了一個非常簡單易用的方法解決了業務上的一個insert吞吐量的問題，在此總結一下。

首先我們明確一下，insert吞吐量其實並不是指的IPS（insert per second），而是指的RPS（effect rows per second）。

其次我們再說一下batch insert，其實顧名思義，就是批量插入。這種優化思想是很基本的，MySQL中最出名的應用就是group commit。

簡單的來說，就是將SQL A 變成 SQL B

SQL A : insert into table values （$values）;
SQL B : insert into table values ($values),($values)...($values);

下麵，我們來看看這種異常簡單的改動會帶來什麼樣子的變化。

測試環境交代：單id的表結構，10w個int values，本地使用socket連接MySQL server，使用shell單進程測試。

首先，我們看下使用SQL A將10w個int values插入到test表中所需的耗時，耗時1777秒。

real    29m37.090s
user    9m11.705s
sys     5m0.762s

然後，我們看下使用SQL B（每次insert，插入10 values）將10w個int values插入到test表中所需的耗時，耗時53秒

real    0m53.871s
user    0m19.455s
sys     0m6.285s

這是整整近33倍的時間提升。這部分性能提升的原因在於以下幾點：

1、每次和MySQL server建立連接都需要經過各種初始化、許可權認證，語法解析等等多個步驟，需要消耗一定的資源。

2、更新一個values和更新n個values耗時基本一致。（下麵對比一下insert 單values核insert 10 values的profile耗時）

單values：
+------------------------------+----------+
| Status                       | Duration |
+------------------------------+----------+
| starting                     | 0.000056 |
| checking permissions         | 0.000010 |
| Opening tables               | 0.000034 |
| System lock                  | 0.000010 |
| init                         | 0.000011 |
| update                       | 0.000061 |
| Waiting for query cache lock | 0.000003 |
| update                       | 0.000015 |
| end                          | 0.000003 |
| query end                    | 0.000053 |
| closing tables               | 0.000009 |
| freeing items                | 0.000021 |
| logging slow query           | 0.000002 |
| cleaning up                  | 0.000003 |
+------------------------------+----------+

10 values：
+------------------------------+----------+
| Status                       | Duration |
+------------------------------+----------+
| starting                     | 0.000061 |
| checking permissions         | 0.000008 |
| Opening tables               | 0.000027 |
| System lock                  | 0.000008 |
| init                         | 0.000012 |
| update                       | 0.000073 |
| Waiting for query cache lock | 0.000003 |
| update                       | 0.000010 |
| end                          | 0.000008 |
| query end                    | 0.000053 |
| closing tables               | 0.000010 |
| freeing items                | 0.000021 |
| logging slow query           | 0.000002 |
| cleaning up                  | 0.000003 |
+------------------------------+----------+

但是，是否values積攢的越多，效率越高嗎？ 答案自然是否定的，任何優化方案都不會是純線性的，肯定會在某個條件下出現拐點。

我們按照不同的values number進行測試，分別為1、10、50、100、200、500、1000、5000、10000.

從下圖我們可以看出，隨著values number的增加，耗時先是急劇下降，從1777s變成53s，然後在增加values number就不會有太大的變化，直到values number超過200，最後的10000個values number耗時達到了2分鐘。

從下圖我們可以看到隨著values numbers的增加，QPS（藍線）先是猛增，然後下降，最終小於1/s。而RPS（綠線）隨著增加猛增到一個高level，然後隨著增加逐步下降，超過5000個values number之後開始急劇下降。

另，最關鍵的是， QPS最高峰和RPS的最高峰並不在同一個values number下，也就是說QPS最高的時候並不代表著insert的吞吐量就最高 。

在我這個簡單測試場景中，values number最合適的值是50，和單values對比，耗時減少 97% ，insert吞吐量提升 36倍 。

而這個值和表結構和欄位類型及大小都有關係。需要根據不同的場景進行測試之後才可以得出，但是普遍來說，50-100是比較推薦的考慮值。

至於這個如何實現，只要前端寫入的時候加入隊列即可，可以按照2個條件進行合併

• 隊列中積攢到n個values number後在寫入資料庫，優點是性能最高，缺點是時間不可控，有可能等到第n個需要n秒，這時候業務已經不可接收了。
• 隊列中積攢1s之後，有多少個就寫入多少個，優點是時間可控，缺點就是values number數目不可能，高併發的情況，可能1s已經積攢上千個values了。
• 最優的方案其實是2個條件同時起作用，即進行個數效驗，也進行時間效驗，無論達到那個條件都觸發後續寫資料庫操作。

總結：

1、使用batch insert可以提高insert的吞吐量。

2、疊加的values number需要根據實際情況測試得出。

3、同時使用個數和時間控制閥值。

 附簡單測試的記錄值：