InnoDB 隔離模式對 MySQL 效能的影響

InnoDB 隔離模式對 MySQL 效能的影響

過去的幾個月我寫了兩篇文章，一篇是InnoDB 事務曆史相關的危險債務，另一篇是關於MVCC 可能導致MySQL嚴重的效能問題的真相。在這篇文章裡我將討論一個相關的主題 – InnoDB 事務隔離模式，還有它們與MVCC（多版本並發控制）的關係，以及它們是如何影響MySQL效能的。

MySQL手冊提供了一個關於MySQL支援的事務隔離模式的恰當描述 – 在這裡我並不會再重複，而是聚焦到對效能的影響上。

SERIALIZABLE – 這是最強的隔離模式，本質上打敗了在鎖管理（設定鎖是很昂貴的）的條件下，多版本控制對所有選擇進行鎖定造成大量的開銷，還有你得到的並發。這個模式僅在MySQL應用中非常特殊的情況下使用。

REPEATABLE READ – 這是預設的隔離等級，通常它是相當不錯的，對應用程式的便捷性來說也不錯。它在第一次的時候讀入所有資料 （假設使用標準的非鎖讀）。但是這有很高的代價 – InnoDB需要去維護事務記錄，從一開始就要記錄，它的代價是非常昂貴的。更為嚴重的情況是，程式頻繁地更新和hot rows – 你真的就不想InnoDB去處理rows了，它有成百上千個版本。

在效能上的影響， 讀和寫都能夠被影響。用select查詢遍曆多個行是代價高昂的，對於更新（update）也是，在MySQL 5.6中，尤其是版本控制看起來導致了嚴重的爭用問題。

下面是例子：完全在記憶體中的資料集中運行 sysbench，並啟動 transaction 、運行全表、掃描、查詢幾次，同時保持 transaction 是開著的：

sysbench  --num-threads=64 --report-interval=10 --max-time=0 --max-requests=0 --rand-type=pareto --oltp-table-size=80000000 --mysql-user=root --mysql-password= --mysql-db=sbinnodb  --test=/usr/share/doc/sysbench/tests/db/update_index.lua run

 

正如你可以看到的，寫（write ）操作的輸送量大幅下降，並且持續走低，這時transaction 是開著的，不僅是在查詢（query）操作啟動並執行時候。在可複讀的隔離模式下，當你已經選擇了之外的transaction ，緊接著就是一個long transaction ，這也許是我能找到的最糟糕情況了。當然了你也會在其他情況下看到迴歸演算法（regression ）。

如果有人想測試，可以重複下面我用的查詢集合：

select avg(length(c)) from sbtest1;
begin;
select avg(length(c)) from sbtest1;
select sleep(300);
commit;

不只是可複讀（Repeatable Read）的預設隔離等級，同樣也可以用於InnoDB 邏輯備份 –  mydumper 或者 mysqldump –single-transaction

這些結果顯示這個備份的方法恢複的時間太長而不能用於大型資料集合，同樣這個方法受到效能影響，也不能用於頻繁寫入（write ）的環境中。

 

READ COMMITTED 模式和REPEATABLE READ模式很相似，本質區別在於哪個版本都不在transaction中從頭開始讀取，取而代之的從當前語句開始讀取。因此使用這種模式允許InnoDB少維護很多版本，特別是你沒有很長的statements要允運行。如果你有很長的select要運行，如報表查詢對效能的影響仍然很嚴重。

 

通常我認為好的做法是把READ COMITTED隔離模式做為預設，對於應用程式或者transactions 有必要就改成REPEATABLE READ。

READ UNCOMMITTED – 我覺得這是最難理解的隔離模式（悲催的只有2條文檔），只描述了它的邏輯觀點。如果你使用了這種隔離模式，你會看到資料控中所有發生的變化，即使是那些還沒被提交的transactions 。這種隔離模式一種好的用例是：你能“watch”到大規模的有髒讀（dirty reads）的UPDATE 語句，顯示了哪行被改變了，哪些沒有改變。

如果transaction 事務在啟動並執行時候出錯了，那麼這個聲明會顯示還沒被提交的和可能沒被提交的變化，所以使用這個模式要小心為妙。有一些用例雖然不需要我們100%準確的資料，在這種情況下，這種模式就變得非常方便。

那麼，從效能角度來看，如何體現READ UNCOMMITTED？理論上，InnoDB 可以清除行版本，在READ UNCOMMITTED模式下即便是該語句已經開始執行之後，也可以建立。在實踐中，由於一個bug或者一些複雜實現的細節做不到，語句開始仍然是行版本。所以，如果你在READ UNCOMMITTED聲明中運行很長的SELECT，你會得到大量的行版本建立資訊，就像你用了READ COMMITTED。No win here。

從SELECT方面還有一個重要的win - READ UNCOMMITTED隔離模式意味著InnoDB 不需要去檢查舊的行版本 - 最後一行總是對的，這會使得效能有明顯的改善，尤其是當undo空間已經在磁碟上溢出，尋找舊的行版本會造成大量的IO讀寫。

也許上面這個select avg(k) from sbtest1;是我能找到的最好的查詢例子了，能與之類似的更新工作量。假使READ UNCOMMITTED隔離模式在一分鐘左右完成，我認為在READ COMMITTED隔離模式下沒有完成過，因為新索引條目插入的速度要比掃描速度快。

最後思考：正確的使用InnoDB 隔離模式，能夠讓您的應用程式得到最佳效能。你得到的好處可能不同，在某些情況下，也可能沒什麼區別。關係到InnoDB 的曆史版本，似乎好有好多工作要做，我希望在未來的MySQL中能解決。

MySQL InnoDB儲存引擎鎖機制實驗

InnoDB儲存引擎的啟動、關閉與恢複

MySQL InnoDB獨立資料表空間的配置

MySQL Server 層和 InnoDB 引擎層 體繫結構圖

InnoDB 死結案例解析

MySQL Innodb獨立資料表空間的配置

英文原文：MySQL performance implications of InnoDB isolation modes

本文永久更新連結地址：