PostgreSQL對決InnoDB 多版本並發控制大比拼

多版本並發控制技術已經成為未來資料庫的發展趨勢。目前，多版本並發控制被很多資料庫或儲存引擎採用，如Oracle，MS SQL Server 2005+, PostgreSQL, Firebird, InnoDB, Falcon, PBXT, Maria等等。新的資料庫儲存引擎，幾乎毫無例外的使用多版本而不是單版本加鎖的方法實現並發控制。

雖然都是多版本，但不同的資料庫系統的實現卻有很大不同。在開來源資料庫領域最負盛名的兩個系統PostgreSQL和InnoDB的多版本實現就可謂有天壤之別。

一、PostgreSQL的多版本實現(基於8.4.1版本)

PostgreSQL採用堆+B+樹索引忽視R樹、雜湊、GiST等不常用的索引）的儲存結構，堆與索引的儲存模式不同。

堆中記錄包含版本化資訊，PostgreSQL不區分記錄的最新版本或老版本，都儲存在堆中。簡單的說，堆中每條記錄頭上記錄t_xmin和t_xmax兩個屬性，分別表示建立與刪除這一版本的事務ID，另外記錄t_ctid屬性，表示該記錄下一個更新的版本的RID，即記錄的多個版本構成從最老到最新的單向鏈表見HeapTupleHeaderData結構）。DELETE一條記錄時，設定t_xmax，並不將記錄真正刪除；UPDATE一條記錄時，也不直接更新，而是插入一個新版本，對原來被更新的版本，將其t_xmax設為當前事務ID，設定其t_ctid指向新版本。

有了這些資訊還不夠，為了判斷版本的可見度，還需要兩個東西，一是事務提交日誌，二是事務快照。事務提交日誌對每個事務使用兩個bit，記錄事務是活躍、已提交還是已復原。事務快照在事務開始時分配，其中最重要的資訊是當時活躍事務的列表見SnapshotData結構）。

有了這些東西，系統可以判斷一個版本是否可見。判斷過程比較複雜，不過從簡單的原理上說，系統先通過判斷t_xmin是否在全域活躍事務列表中、是否在事務快照活躍事務列表中、根據事務提交日誌判斷事務是提交還是復原了等來判斷t_xmin事務是否在事務開始時已經提交；然後用類似的方法判斷t_xmax是否在事務開始時已經提交。如果t_xmin在事務開始時沒有提交則不可見；如果t_xmin在事務開始時已經提交而t_xmax沒有，則可見；如果t_xmin和t_xmax在事務開始時都已經提交了則不可見。詳細過程見HeapTupleSatisfiesMVCC、TransactionIdDidCommit、XidInMVCCSnapshot等函數）。

索引中則不包含版本資訊。一般情況下，記錄的所有版本都在索引中存在對應的索引項目。舉個例子，如果一個表有三個索引，更新一條記錄時，不但在堆中會插入一個新版本，新版本對應的索引項目也要插入到三個索引中，即使這次更新可能沒有更新某些索引的屬性見ExecUpdate函數）。在PostgreSQL 8.3中引入了HOTHeap-Only-Tuple）技術，如果新老版本在同一頁面，並且UPDATE沒有更新任何索引屬性，則不插入新版本對應的索引項目。

由於索引沒有版本資訊，進行索引掃描時，即使查詢所需所有屬性在索引中都存在，也需要從堆中取出對應的記錄判斷是否可見見index_getnext函數）。

事務提交或復原時操作簡單，除事務提交時要寫出事務外，只需要更新事務提交日誌中對應的事務狀態。也就是說復原時並不需要將事務所作的操作從物理上清理掉，只要將事務狀態設為已經復原，則該事務產生的版本對其它事務自然就不可見了。

老舊的不再需要的版本，即不會被將來的任何事務見到的版本的清理是通過VACUUM實現的。由於新老版本混雜在一起，進行VACUUM時本質上是需要掃描所有資料。8.4版中引入了Visibility Map技術，用來在VACUUM時跳過那些肯定不包含老舊版本的頁面，但如果系統更新頻繁且離散，這一技術就派不上大用場。線上的VACUUM只能清理頁面中的老舊版本，但不能縮減表佔用的空間，其實是產生片段。要縮減資料表空間時的VACUUM會鎖住表導致期間表不能被更新。

二、InnoDB的多版本實現基於MySQL 5.1.33版本帶的InnoDB）

InnoDB採用索引組織表的儲存結構，沒有堆，記錄儲存在主鍵索引中，其它索引稱為二級索引，其中每個索引項目都包含所對應記錄的主鍵。主鍵索引與二級索引的儲存格式也不同。

主鍵索引擁有版本化資訊，但與PostgreSQL不同，一般情況下InnoDB的主鍵索引中只儲存記錄的最新版本，舊版本的資訊則集中儲存在復原段中，只有主鍵被更新時才需要同時儲存多個版本在主鍵索引中。主鍵索引記錄的頭上包含有6位元組的事務ID與7位元組指向復原段中舊版本的指標見MySQL手冊）。DELETE時只是標記而不真正刪除。UPDATE時進行本地更新，並將前像寫到復原段中。

存在與PostgreSQL中事務快照類似讀視圖，也記錄了事務開始時的活躍事務列表見read_view_struct結構），但不需要PostgreSQL中的事務提交日誌。根據讀視圖和記錄頭上的事務ID，可以判斷出一個版本在事務開始時是否已經提交，即是否可見。如果儲存在主鍵索引中的記錄不可見，則根據指向復原段中舊版本的指標找到舊版本資訊，構造出舊的記錄。復原段採用的是append-only的日誌型儲存，記錄的舊版本資訊並不是一條完整的記錄，而只是被更新的屬性的前像。復原段中的舊版本資訊中也包含更舊的版本的位置，即版本鏈表是從新到舊的。

由於沒有交易記錄表示事務是否復原，在交易回復時必須清理該事務所進行的修改，插入的記錄要刪除，更新的記錄要更新回來見row_undo函數）。事務提交時則無需處理。

二級索引中的每個索引項目並沒有版本化資訊。但在頁面頭記錄了對該頁面操作的事務的ID的最大值，通過這一值可以判斷頁面中是否可能包含不可見的資料，如果是，則需要訪問主鍵索引判斷可見度。否則，可以直接從索引中擷取查詢所需屬性。二級索引中可能儲存一條記錄的多個版本對應的索引項目，如果UPDATE操作更新了某個索引的屬性，則類似於PostgreSQL，插入新索引項目到二級索引中，老索引項目並不刪除。但沒有被UPDATE操作更新的索引則不需要插入新索引項目。

系統使用一個後台線程不時處理復原段，在需要時清理由於DELETE、二級索引或主鍵索引中由於主鍵被更新而產生的老舊版本，這一過程稱這purge。如果UPDATE沒有更新索引，則不會帶來purge開銷。

三、評價與總結

PostgreSQL與InnoDB的多版本實現最大的區別在於最新版本和曆史版本是否分離儲存，PostgreSQL不分，InnoDB分。

PostgreSQL的這種設計被其最初的設計者Mike Stonebraker稱為no-overwrite的設計，在設計了PostgreSQL幾年之後他的一篇回顧性論文《The Implementation of Postgres》 PostgreSQL早期叫Postgres）中，Stonebraker指出當初這樣設計的主要原因是尋求與當時已經廣泛使用的WAL模式不同的儲存機制，有點為了創新而創新的意思。這一設計有兩大好處：一是交易回復時無需複雜處理，非常快；二是可以查詢以前的曆史資料。還有一個可能的好處是可以實現資料即日誌，即更新時只要更新資料就行了，不需要再寫日誌來描述做了什麼更新。但要使這個好處實現，需要有一種持久的，並且隨機寫具有與順序寫類似效能的儲存介質才行，因為為了保證事務提交後的持久性，需要寫出被事務更新的資料，而這些資料可能是離散的。WAL系統則不同，事務提交時只需要寫日誌就行了，而日誌是順序寫入的。當前的硬體環境並不是這樣，因此PostgreSQL中仍然還要寫日誌，只不過不需要寫UNDO日誌，只要REDO日誌就行了。

最新的PostgreSQL與當初Stonebraker的設計已經有了很大改進，比如HOT技術減少了索引中的版本數，Visibility Map技術加快了VACUUM，記錄頭部結構也更緊湊。但no-overwrite的設計原則仍然沒變。

相對於InnoDB，PostgreSQL的優勢似乎主要的只有一條：交易回復可以立即完成，無論事務進行了多少操作。查詢以前的曆史資料的功能並不常用，在目前的PostgreSQL中也並不實用。

PostgreSQL的主要劣勢在於：

1、最新版本和曆史版本不分離儲存，導致清理老舊版本需要作更多的掃描，代價更大；

2、UPDATE不是本地更新，會產生老舊版本需要清理。與之相對的是InnoDB只有在交易回復時才需要清理老的記錄資料。而交易回復是罕見的；

3、只要有一個索引屬性被更新，或者新版本的記錄與原版本不在同一頁面，就要插入所有索引的新版本索引項目；

4、堆佔用的空間不能通過線上的VACUUM回收，線上VACUUM會產生很多片段這也是由於使用了堆而不是索引組織表導致的）；

5、由於索引中完全沒有版本資訊，不能實現Coverage index scan，即查詢只掃描索引，直接從索引中返回所需的屬性。與之相對的是InnoDB中二級索引頁頭記錄的最近修改該頁的事務ID資訊可以在大部分情況下實現Coverage index scan。Coverage index scan是應用中經常使用的最佳化技巧，PostgreSQL不支援這個對提升系統效能帶來很大限制，因為索引掃描是順序訪問，去訪問堆則很可能變成亂序訪問，效能可能相差百倍；

6、判斷版本可見度更複雜，開銷更大。PostgreSQL比InnoDB在判斷可見度時，需要增加訪問事務提交日誌的操作，事務提交日誌每個事務需要分配兩個bit，對高更新負載的系統會佔用較大空間，這時要麼事務提交日誌回佔用大量記憶體，要麼判斷可見度時就可能產生額外的IO。對比PostgreSQL中判斷可見度的函數HeapTupleSatisfiesMVCC和InnoDB中判斷可見度的函數read_view_sees_trx_id，可以容易看出這兩者的複雜度不可同日而語。

InnoDB的主要劣勢在於交易回復時需要清理事務所作的所有修改，因此使用InnoDB時要避免使用超大型事務，否則復原可能超慢無比。

本文轉載自風輕揚的部落格：http://wangyuanzju.blog.163.com/blog/static/130292009107101544125/

1. PostgreSQL 8.4發布 新增查詢監控工具
2. PostgreSQL新版提高Oracle相容性 欲與甲骨文試比高？
3. 細化解析PostgreSQL的昨天今天和明天
4. InnoDB還是MyISAM 再談MySQL儲存引擎的選擇
5. 淺談MySQL儲存引擎選擇 InnoDB還是MyISAM