MYSQL開發效能研究——INSERT，REPLACE，INSERT-UPDATE效能比較

標籤：style   blog   http   使用   strong   資料   sp   2014   問題   

一、為什麼要有這個實驗

我們的系統是批處理系統，類似於管道的架構。而各個資料表就是管道的兩端，而我們的程式就類似於管道本身。我們所需要做的事情無非就是從A表抽取資料，經過一定過濾、匯總等操作放置到B表。如果出現了錯誤，那麼就從重新跑這一個管道。所以說，我們的系統其實根本就不要什麼事務性，無非就是掛了把表給TRUNCATE（或者有條件地DELETE）一下，然後重跑就行了。

這樣一來，對於select語句就相對比較容易，基本上不需要做JOIN操作。然而對於寫操作就有一些要求。比如說，需要處理主鍵重複（可能之前跑掛了，現在需要重跑，到底是提示錯誤呢，還是做個REPLACE或者UPDATE）等等問題。

在引入了MYSQL之後，我們發現MYSQL在SQL語句層面就提供了對於類似問題的解決。包括了INSERT，REPLACE，INSERT-ON-DUPLICATE的操作。具體的說明請查看這裡。唯一需要注意的是INSERT-ON-DUPLICATE這個操作，在UPDATE裡面的VALUES的含義是INSERT列表裡的那個固定值，如果需要引用資料表中原來的值，還是直接使用列名即可，無需用VALUES封裝一下。

 

 

二、實驗準備

我仍然是採用了在我們這裡可能用到的最大的表，該表有近200個欄位。實驗環境也和上一篇文章中的一樣。有了那篇文章中的比較，我就直接使用了10條多行插入的方法，也是每5000條提交一次。為了做個比較，我特意製作了一個傳統的INSERT-UPDATE操作。該操作先進行INSERT插入動作，然後檢查輸出，如果是出現了“主鍵重複”的錯誤，那麼直接調用UPDATE語句，用相同的資料替換那行（就是直接原值覆蓋）。注意，這種辦法是沒有辦法做到多行插入的。

同樣，為了讓情境更加真實。我在同一個MYSQL服務上建立了三個資料庫，其中都建立了該表。而且所有的操作都直接針對該三張表進行。我在代碼裡使用的工具是我自己寫的一個類庫。通過多線程串連到多庫（一庫一串連）然後主線程向所有線程發送一句INSERT/REPLACE/INSERT-UPDATE/INSERT-ON-DUPLICATE-KEY命令，等待所有線程都返回繼續向下。所有的COMMIT操作都是線程主動根據AFFECTED ROWS的累積量自己選擇做。

再強調一下，機器很爛，TPS沒有意義。只是看個趨勢。

 

三、實驗結果

說明：

• 多行INSERT空表——使用”INSERT INTO … VALUES (..), (..), (..), … “的方式往一張空表裡面插入資料。
• INSERT-UPDATE——在上一步驟的基礎上，該操作先進行INSERT插入動作（一條一條INSERT），然後檢查錯誤輸出，如果是出現了“主鍵重複”的錯誤，那麼直接調用UPDATE語句，用相同的資料替換那行（就是直接原值覆蓋）。
• 多行REPLACE空表——使用“REPLACE INSERT INTO … VALUES (..), (..), (..), … ”的方式往一張空表裡面插入資料。
• INSERT-DUPLICATE——使用 INSERT INTO .. VALUES (..), (..), (..), … ON DUPLICATE KEY UPDATE …”的文法在上一步驟的基礎上進行操作。

 

結論如下：

• 對於空表操作，REPLACE的效能和INSERT的差不多，但是他還有一個額外的好處，就是可以進行覆蓋操作。這就給了我們一點提示，如果我們真的不用去關心DUPLICATE KEY錯誤，而且希望做到覆蓋效果，那麼使用REPLACE真心不錯；如果不用關心DUPLICATE KEY錯誤，同時也不想要覆蓋，那麼INSERT IGNORE更好。
• 傳統的INSERT-UPDATE方式真心慢，理解起來也不複雜，送過去-返回來-再送過去-再返回來。還是改用INSERT-ON-DUPLICATE-KEY-UPDATE吧。

MYSQL開發效能研究——INSERT，REPLACE，INSERT-UPDATE效能比較