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中引入了HOT（Heap-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/
【編輯推薦】
