|
先從上次遇到的一個(gè)執(zhí)行計(jì)劃相關(guān)的疑問入手，類似于select count(1) from table的查詢，可以在即某些較小字段上的索引進(jìn)行掃描來替代全表掃描來實(shí)現(xiàn)count優(yōu)化，這是一個(gè)MySQL中傳統(tǒng)的優(yōu)化套路，但是在postgresql中類似場景總是會走全表掃描而不是預(yù)期的索引掃描，為什么同樣的套路搬到Postgresql上就不好使了？這是一個(gè)postgresql中一個(gè)典型的MVCC機(jī)制造成的，應(yīng)該是一個(gè)比較有代表性的問題了，看起來是十年前就有人問過類似的問題了https://dba./questions/2070/postgresql-count-uses-a-sequential-scan-not-index 因此經(jīng)驗(yàn)跟教條之間，其實(shí)很近很近，不能輕易“復(fù)印”的以往的經(jīng)驗(yàn)。
 Postgresql MVCC下的數(shù)據(jù)可見性
這里涉及到一個(gè)數(shù)據(jù)可見性的問題
Postgresql在數(shù)據(jù)修改時(shí)通過保留數(shù)據(jù)的歷史版本來實(shí)現(xiàn)MVCC，也即不同的事務(wù)要看到同一條數(shù)據(jù)的不同版本，這需要依次保留不同版本的問題。
不同的數(shù)據(jù)庫的MVCC機(jī)制實(shí)現(xiàn)是不同的，MySQL或者Oracle中是通過將歷史記錄寫入undo表空間實(shí)現(xiàn)，Postgresql是直接在當(dāng)前頁面保留這個(gè)數(shù)據(jù)的歷史版本。 這里暫時(shí)拋開Postgresql的HOT優(yōu)化機(jī)制，粗略來看一條update或者delete發(fā)生時(shí)是如何實(shí)現(xiàn)多版本的。
數(shù)據(jù)修改操作：將某一行的data字段從a修改為b
可以直觀地想象一下Postgresql中修改一條記錄事生成的“undo”記錄的實(shí)現(xiàn)，（當(dāng)然除此之外這個(gè)undo記錄與xlog有關(guān)）  其過程就是update的時(shí)候保留老的記錄，重新寫入一條新紀(jì)錄的， 通過不同的事務(wù)Id決定不同的事務(wù)可以看到修改前或者修改后的記錄
數(shù)據(jù)刪除操作：這里示例刪除上面修改后的記錄的過程 刪除操作是類似的一個(gè)過程，僅標(biāo)記原始記錄被刪除（set t_xmax），但此時(shí)記錄還保存在原地。  這里就存在2個(gè)問題：
1、誰&什么時(shí)候&什么條件下，清理歷史版本
大量的歷史版本會造成表膨脹的問題，不過目前看來應(yīng)該不是問題，絕大多數(shù)情況下后臺清理進(jìn)程完全可以hold的住。 其實(shí)這個(gè)問題源自于MVCC需要保留不同版本數(shù)據(jù)的機(jī)制造成的，是一個(gè)支持MVCC的共性問題，MySQL中也有類似問題，MySQL 5.7之前undo 表空間膨脹且無法裝直接收縮，業(yè)內(nèi)也為此整出來各種奇淫巧技來處理該問題、所以某些問題是必須要經(jīng)歷或者說面對的，沒有絕對好或者絕對壞的方法。 參考前面統(tǒng)計(jì)新信息更新時(shí)涉及到的vacuum自動化機(jī)制：https://www.cnblogs.com/wy123/p/13347176.html 2、如何解決索引鍵無法“直接”感知數(shù)據(jù)行的變化（索引上沒有行版本信息）
多版本的只能在數(shù)據(jù)行級別體現(xiàn)，而無法在索引樹中體現(xiàn)出來，也就是說索引上是沒有版本信息的，刪除一條記錄會標(biāo)記一條記錄刪除前的版本，以及將新寫入一個(gè)條記錄并標(biāo)記為刪除，這個(gè)過程可以認(rèn)為該表上的索引是無感知的，或者對應(yīng)的索引鍵是無法直接知道“我對應(yīng)的記錄被刪除了”，這一點(diǎn)是postgresql所特有的。如果索引想知道其某個(gè)鍵值對應(yīng)的數(shù)據(jù)行有沒有發(fā)生變化或者被刪除，是需要結(jié)合clog，也就是commit log（新版本中叫xact log）的，通過索引鍵訪問數(shù)據(jù)行的時(shí)候，需要經(jīng)過xlog做一次驗(yàn)證，才能決定該索引鍵是否發(fā)生了變換（增刪改）。 所以現(xiàn)在可以想明白，為什么在count(1) 不會通過僅掃描索引就可以完成的了吧，因?yàn)樵谟?jì)算總行數(shù)的過程中，必須要通過“回表”重新驗(yàn)證該記錄是否當(dāng)當(dāng)前事務(wù)可見。
“回表”這一點(diǎn)如何體現(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)行了回表（驗(yàn)證索引上符合條件數(shù)據(jù)的可見性），因此這里的執(zhí)行計(jì)劃顯式的index only scan并不合適。  此外隨著數(shù)據(jù)范圍的增加，優(yōu)化器開始采用bitmap scan的方式來執(zhí)行，其目的只有一個(gè)：回表進(jìn)行數(shù)據(jù)可見性的檢查（Heap Blocks）
 上面兩種情況都是一個(gè)小范圍的count，換成一個(gè)大范圍或者全表的count，如果每次這么回表（Heap Fetches）或者bitmap index scan校驗(yàn)就太低效了，那么就直接全表掃描還是相對比較直接的做法。
 可見性映射
為了避免索引上沒有版本信息導(dǎo)致的回表recheck，PostgreSQL對目標(biāo)表的做了一個(gè)可見性映射。也就是說，如果一個(gè)頁面中存儲的所有元組都是可見的，PostgreSQL使用索引元組的鍵，就無須回表再次確認(rèn)數(shù)據(jù)的可見性，否則，PostgreSQL將從索引元組中讀取指向的表元組，并檢查元組的可見性，這是一個(gè)常規(guī)的過程。需要注意的是，這個(gè)可見性映射是一個(gè)非精確值。參考http://www./docs/9.4/storage-vm.html
其原理如下圖所示，當(dāng)前事務(wù)通過一個(gè)visibility map元數(shù)據(jù)來判斷哪些數(shù)據(jù)頁面是可見的，哪些數(shù)據(jù)頁面（因?yàn)榘l(fā)生過修改）是不可見的。
那么這個(gè)可見性映射visibility map如何直觀地體現(xiàn)出來？
這里涉及到pg_class表的一個(gè)relallvisible字段，其含義是在表的可見映射中標(biāo)記所有可見的頁的數(shù)目。只是優(yōu)化參考的一個(gè)估計(jì)值， 由VACUUM,ANALYZE 和幾個(gè) DDL 命令，比如CREATE INDEX更新。 這個(gè)字段的解釋見這里：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àn)榭梢娦杂成涓嬖V優(yōu)化器復(fù)合條件的數(shù)據(jù)頁面的數(shù)據(jù)都是可見的，因此這里就無須再次回表recheck了
Postgresql MVCC機(jī)制的優(yōu)缺點(diǎn) 這里稱Postgresql的MVCC實(shí)現(xiàn)為“原地副本”，其特色是可實(shí)現(xiàn)快速回滾，一是因?yàn)槭聞?wù)修改前版本還在“原地”，二是依賴于事務(wù)的clog，事務(wù)的提交與否是通過事更新事務(wù)的clog中的標(biāo)記位來實(shí)現(xiàn)的，因此事務(wù)的大小（修改1行和修改100W行數(shù)據(jù)），回滾時(shí)其代價(jià)是一樣的。其次，基于“原地”的數(shù)據(jù)行副本，相比將數(shù)據(jù)修改前的副本轉(zhuǎn)移到undo表空間需要來回移動數(shù)據(jù)，個(gè)人認(rèn)為這樣原地操作效率會稍高一點(diǎn)，同時(shí)這也是其缺點(diǎn)，大量的數(shù)據(jù)修改和刪除，會造成表空間的膨脹，在vacuum回收之前會對加大讀操作的代價(jià)。同時(shí)，即便有vacuum回收這個(gè)不可見數(shù)據(jù)副本，也更容易造成存儲空間上的碎片。
參考鏈接
http://www./pg/pgsql07.html
https://blog.csdn.net/xiaohai928ww/article/details/103742744 http://www./docs/9.4/routine-vacuuming.html https://www./web/detail/1/701/1.html 某些經(jīng)驗(yàn)可以重用，但是不可復(fù)印 |
|
|
