先從上次遇到的一個執(zhí)行計劃相關(guān)的疑問入手，類似于select count(1) from table的查詢，可以在即某些較小字段上的索引進(jìn)行掃描來替代全表掃描來實現(xiàn)count優(yōu)化，這是一個MySQL中傳統(tǒng)的優(yōu)化套路，但是在postgresql中類似場景總是會走全表掃描而不是預(yù)期的索引掃描，為什么同樣的套路搬到Postgresql上就不好使了？這是一個postgresql中一個典型的MVCC機(jī)制造成的，應(yīng)該是一個比較有代表性的問題了，看起來是十年前就有人問過類似的問題了https://dba./questions/2070/postgresql-count-uses-a-sequential-scan-not-index  因此經(jīng)驗跟教條之間，其實很近很近，不能輕易“復(fù)印”的以往的經(jīng)驗。

 

這里涉及到一個數(shù)據(jù)可見性的問題

Postgresql在數(shù)據(jù)修改時通過保留數(shù)據(jù)的歷史版本來實現(xiàn)MVCC，也即不同的事務(wù)要看到同一條數(shù)據(jù)的不同版本，這需要依次保留不同版本的問題。
不同的數(shù)據(jù)庫的MVCC機(jī)制實現(xiàn)是不同的，MySQL或者Oracle中是通過將歷史記錄寫入undo表空間實現(xiàn)，Postgresql是直接在當(dāng)前頁面保留這個數(shù)據(jù)的歷史版本。

這里暫時拋開Postgresql的HOT優(yōu)化機(jī)制，粗略來看一條update或者delete發(fā)生時是如何實現(xiàn)多版本的。

數(shù)據(jù)修改操作：將某一行的data字段從a修改為b
可以直觀地想象一下Postgresql中修改一條記錄事生成的“undo”記錄的實現(xiàn)，（當(dāng)然除此之外這個undo記錄與xlog有關(guān)）

其過程就是update的時候保留老的記錄，重新寫入一條新紀(jì)錄的， 通過不同的事務(wù)Id決定不同的事務(wù)可以看到修改前或者修改后的記錄
數(shù)據(jù)刪除操作：這里示例刪除上面修改后的記錄的過程
刪除操作是類似的一個過程，僅標(biāo)記原始記錄被刪除（set t_xmax），但此時記錄還保存在原地。

這里就存在2個問題：

1、誰&什么時候&什么條件下，清理歷史版本
大量的歷史版本會造成表膨脹的問題，不過目前看來應(yīng)該不是問題，絕大多數(shù)情況下后臺清理進(jìn)程完全可以hold的住。
其實這個問題源自于MVCC需要保留不同版本數(shù)據(jù)的機(jī)制造成的，是一個支持MVCC的共性問題，MySQL中也有類似問題，MySQL 5.7之前undo 表空間膨脹且無法裝直接收縮，業(yè)內(nèi)也為此整出來各種奇淫巧技來處理該問題、所以某些問題是必須要經(jīng)歷或者說面對的，沒有絕對好或者絕對壞的方法。
參考前面統(tǒng)計新信息更新時涉及到的vacuum自動化機(jī)制：https://www.cnblogs.com/wy123/p/13347176.html

2、如何解決索引鍵無法“直接”感知數(shù)據(jù)行的變化（索引上沒有行版本信息）
多版本的只能在數(shù)據(jù)行級別體現(xiàn)，而無法在索引樹中體現(xiàn)出來，也就是說索引上是沒有版本信息的，刪除一條記錄會標(biāo)記一條記錄刪除前的版本，以及將新寫入一個條記錄并標(biāo)記為刪除，這個過程可以認(rèn)為該表上的索引是無感知的，或者對應(yīng)的索引鍵是無法直接知道“我對應(yīng)的記錄被刪除了”，這一點是postgresql所特有的。如果索引想知道其某個鍵值對應(yīng)的數(shù)據(jù)行有沒有發(fā)生變化或者被刪除，是需要結(jié)合clog，也就是commit log（新版本中叫xact log）的，通過索引鍵訪問數(shù)據(jù)行的時候，需要經(jīng)過xlog做一次驗證，才能決定該索引鍵是否發(fā)生了變換（增刪改）。

所以現(xiàn)在可以想明白，為什么在count(1) 不會通過僅掃描索引就可以完成的了吧，因為在計算總行數(shù)的過程中，必須要通過“回表”重新驗證該記錄是否當(dāng)當(dāng)前事務(wù)可見。

“回表”這一點如何體現(xiàn)？如下demo select count(c2) from myschema.table_test where c2>100 and c2<103;
可以發(fā)現(xiàn)其執(zhí)行過程中雖然是index only scan，但Heap Fetches標(biāo)明依舊進(jìn)行了回表（驗證索引上符合條件數(shù)據(jù)的可見性），因此這里的執(zhí)行計劃顯式的index only scan并不合適。

此外隨著數(shù)據(jù)范圍的增加，優(yōu)化器開始采用bitmap scan的方式來執(zhí)行，其目的只有一個：回表進(jìn)行數(shù)據(jù)可見性的檢查（Heap Blocks）

上面兩種情況都是一個小范圍的count，換成一個大范圍或者全表的count，如果每次這么回表（Heap Fetches）或者bitmap index scan校驗就太低效了，那么就直接全表掃描還是相對比較直接的做法。

 

為了避免索引上沒有版本信息導(dǎo)致的回表recheck，PostgreSQL對目標(biāo)表的做了一個可見性映射。也就是說，如果一個頁面中存儲的所有元組都是可見的，PostgreSQL使用索引元組的鍵，就無須回表再次確認(rèn)數(shù)據(jù)的可見性，否則，PostgreSQL將從索引元組中讀取指向的表元組，并檢查元組的可見性，這是一個常規(guī)的過程。需要注意的是，這個可見性映射是一個非精確值。參考http://www./docs/9.4/storage-vm.html

其原理如下圖所示，當(dāng)前事務(wù)通過一個visibility map元數(shù)據(jù)來判斷哪些數(shù)據(jù)頁面是可見的，哪些數(shù)據(jù)頁面（因為發(fā)生過修改）是不可見的。

那么這個可見性映射visibility map如何直觀地體現(xiàn)出來？
這里涉及到pg_class表的一個relallvisible字段，其含義是在表的可見映射中標(biāo)記所有可見的頁的數(shù)目。只是優(yōu)化參考的一個估計值， 由VACUUM,ANALYZE 和幾個 DDL 命令，比如CREATE INDEX更新。
這個字段的解釋見這里：http://www./docs/9.4/catalog-pg-class.html

在更新relallvisible字段的信息之后，再次執(zhí)行select count(c2) from myschema.table_test where c2>100 and c2<103;因為可見性映射告訴優(yōu)化器復(fù)合條件的數(shù)據(jù)頁面的數(shù)據(jù)都是可見的，因此這里就無須再次回表recheck了

 

Postgresql MVCC機(jī)制的優(yōu)缺點

 

參考鏈接