這篇文章主要介紹了Postgresql和MySQL如何選擇，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

成都創(chuàng)新互聯(lián)是專業(yè)的達(dá)州網(wǎng)站建設(shè)公司，達(dá)州接單;提供成都做網(wǎng)站、網(wǎng)站建設(shè),網(wǎng)頁設(shè)計(jì),網(wǎng)站設(shè)計(jì),建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行達(dá)州網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴(kuò)展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團(tuán)隊(duì),希望更多企業(yè)前來合作!

經(jīng)常看到有人寫關(guān)于鎖的事情，但常常感覺給人一個(gè)感覺，數(shù)據(jù)庫的ACID 是通過鎖來控制的，實(shí)際上數(shù)據(jù)庫的ACID 控制是復(fù)雜的，MVCC 就是一個(gè)對資源并發(fā)訪問時(shí)的提高并發(fā)訪問的有效的方法

在首次定義ACID事務(wù)屬性時(shí)，假定具有可串行性。為了提供嚴(yán)格的可序列化事務(wù)結(jié)果，使用了2PL(兩階段鎖定)機(jī)制。在使用2PL時(shí)，每次讀操作都需要一個(gè)共享鎖獲取，而寫操作則需要一個(gè)獨(dú)占鎖。

保持?jǐn)?shù)據(jù)的完整性，作為一個(gè)重要的經(jīng)驗(yàn)法則，對事務(wù)性處理DBs的所有修改都應(yīng)該在原子事務(wù)下進(jìn)行。而且，每個(gè)事務(wù)都應(yīng)該使數(shù)據(jù)庫處于一致的狀態(tài)，隔離是最難處理的實(shí)踐????。從理論上講，非常簡單，他隔離保證了所有事務(wù)的執(zhí)行，即使它們同時(shí)運(yùn)行，也“好像”它們是串行執(zhí)行的。可實(shí)踐中，它要復(fù)雜得多在保持合理性能的同時(shí)保持隔離.

多版本并發(fā)控制(MVCC)，會創(chuàng)建行的“先前版本”(“快照”)，并將該行的“先前版本”提供給任何可能嘗試并發(fā)運(yùn)行的其他事務(wù)，而不是在有人開始讀取該行時(shí)鎖定該行。這是有道理的——畢竟，在提交第一個(gè)事務(wù)之前，不會考慮更改DB的狀態(tài)。

寫到這，會比較枯燥，下面就開始講點(diǎn)和實(shí)際數(shù)據(jù)庫貼邊的 MVCC 實(shí)現(xiàn)。

就目前掌握的數(shù)據(jù)庫類型，大致解決MVCC的方式有兩種

1 新的數(shù)據(jù)與舊數(shù)據(jù)分離轉(zhuǎn)移到一個(gè)地方，例如undo log，其他人讀數(shù)據(jù)時(shí)，從回滾段中把舊的數(shù)據(jù)讀出來，Oracle和MySQL中的innodb引擎是這樣做的。

2寫新數(shù)據(jù)時(shí)，舊數(shù)據(jù)不刪除，而是把新數(shù)據(jù)插入，新舊數(shù)據(jù)在一起。PostgreSQL就是使用的這種實(shí)現(xiàn)方法。

那么我們可以對比一下這兩種方式的不同

1  Postgresql 中通過行設(shè)計(jì)和xact 的方式來解決MVCC的問題， 我們可以通過一個(gè)表的查詢 xmin,xmax,cmin,cmax 來查看相關(guān)的原理。

下面的這段代碼解釋了PG上關(guān)于 tuple 設(shè)計(jì)上的一些原理

typedef struct HeapTupleFields { TransactionId t_xmin;   /* inserting xact ID *

/TransactionId t_xmax;   /* deleting or locking xact ID *

/union{   CommandId t_cid;   /* inserting or deleting command ID, or both *

/   TransactionId t_xvac; /* VACUUM FULL xact ID */}    t_field3; } HeapTupleFields;

• t_xmin 表現(xiàn)的是產(chǎn)生這個(gè)行或更高這行的事務(wù)ID

• t_xmax 表現(xiàn)的是刪除或鎖定這個(gè)元組的事務(wù)ID

• t_cid 包含cmin和cmax兩個(gè)字段，標(biāo)識在一個(gè)事務(wù)里面的這些行的操作順序，例如插入5行，那這5行的插入順序是什么，那些tuple 對那些tuple是可見的，這個(gè)是一個(gè)事務(wù)級的可見性的展示。

• t_xvac 存儲的是VACUUM FULL 命令的事務(wù)ID

當(dāng)插入一行時(shí)，postgres將在該行中存儲XID并將其稱為xmin。已經(jīng)提交的并且xmin小于當(dāng)前事務(wù)的XID的每一行對事務(wù)都是可見的。這意味著您可以啟動(dòng)一個(gè)事務(wù)并插入一行，而在該事務(wù)提交之前，其他事務(wù)不會看到該行。一旦提交并創(chuàng)建了其他事務(wù)，它們就能夠查看新行，因?yàn)樗鼈儩M足xmin < XID條件——并且創(chuàng)建該行的事務(wù)已經(jīng)完成。

下面我們看看postgresql 表結(jié)構(gòu)，以city表為例

一個(gè)表中都有的字段 tableoid,cmax,xmax,cmin,xmin

select attname, attnum, atttypid::regtype, attisdropped::text from pg_attribute where attrelid = 'city'::regclass;

我們舉一個(gè)例子就能很好的解釋MVCC 的具體操作

我們選擇一個(gè)city 表，然后我們開兩個(gè)事物，一個(gè)更新city_id 1 - 20 另一個(gè)事物更新city_id 21 40 

事務(wù)1

事務(wù)2

不在事務(wù)1 和事務(wù)2 中看到的

從上面可以總結(jié)出

1 每個(gè)事務(wù)更改操作都會觸發(fā) xmin xmax ,改變 

2 每個(gè)事務(wù)的更改xmin 只會在自己的事務(wù)內(nèi)部看的到，而xmax 就是別的事務(wù)正在更改的信息標(biāo)記

這樣MVCC 的初步功能就可以進(jìn)行下去了，所以postgresql 沒有頁鎖，只有表鎖和行鎖。

這樣做的優(yōu)點(diǎn)就是事務(wù)的回滾非常迅速,但需要經(jīng)常性的 vacuum

反觀MYSQL 的MVCC 采用的是undo log的方式，這和ORACLE 的方式雷同，MVCC 的功能實(shí)現(xiàn)并不是在每行中實(shí)現(xiàn)的，innodb存儲引擎對undo的管理采用段的方式,rollback segment稱為回滾段，每個(gè)回滾段中有1024個(gè)undo log segment。(MYSQL 8 已經(jīng)有改變)

使所有回滾段(rsegs)駐留在所選的UNDO表空間中不活動(dòng)。Inactive意味著這些回滾段不會分配給新的事務(wù)。清除系統(tǒng)將繼續(xù)釋放不再需要的回滾段。這將分配給回滾段的頁面標(biāo)記為空閑，并減少回滾的邏輯大小。

通過上面的一個(gè)UNDO 表空間的大概的流程，可以提出幾個(gè)問題

1 回滾段是有數(shù)量限制的，回滾段的數(shù)量限制就是這個(gè)數(shù)據(jù)庫系統(tǒng)的同一個(gè)時(shí)間可以執(zhí)行事務(wù)的數(shù)量的限制，每個(gè)回滾段維護(hù)一個(gè)頁頭，每個(gè)頁面會劃分1024slot 每個(gè)slot 會對應(yīng)一個(gè)事務(wù)，所以MYSQL 5.7（8.0重新設(shè)計(jì)了UNDOLOG）另外即使是只讀的事務(wù)，只要有對臨時(shí)表的寫入，也是分配回滾段的。

例如MYSQL的事務(wù)在prepare 階段，insert undo 和 update undo的狀態(tài)為prepare，調(diào)用trx_undo_set_state_at_prepare，對對應(yīng)的undo log slot頭頁面(trx_undo_t::hdr_page_no)，將頁面段頭的TRX_UNDO_STATE設(shè)置為TRX_UNDO_PREPARED，同時(shí)修改其他對應(yīng)字段。

在commit 階段，Undo狀態(tài)為TRX_UNDO_CACHED，則加入到回滾段的insert_undo_cached鏈表上，或者將該undo所占的segment及其所占用的回滾段的slot全部釋放掉，修改當(dāng)前回滾段的大小，并釋放undo對象所占的內(nèi)存，如果是Update_undo操作，則insert_undo不放到History list上。最后事務(wù)提交后將回滾段的計(jì)數(shù)器減一。

其實(shí)就是將事務(wù)ID 和 回滾段的指針連接起來，同時(shí)MYSQL的行中也有兩個(gè)字段來記錄，針對MYSQL 表每一行 都有 6個(gè)字節(jié)的 db_trx_id , 7個(gè)字節(jié)的 db_roll_ptr ,undo log對于update或者delete操作，每一行都保存了一個(gè)事務(wù)Id，修改事務(wù)Id為當(dāng)前Session的事務(wù)id,生成數(shù)據(jù)行事務(wù)之前的版本，將當(dāng)前行的回滾指針指向事務(wù)之前的版本。對于insert操作，將當(dāng)前行的回滾指針指為空，因?yàn)閕nsert沒有事務(wù)操作之前的版本。

數(shù)據(jù)庫如果在執(zhí)行事務(wù)的過程中想要回滾，必然要考慮并發(fā)和回滾，這就造成隨著并發(fā)和回滾的需求，導(dǎo)致占用更多的磁盤空間，而在事務(wù)提交后就需要清理掉這些無用的東西，POSTGRESQL 叫 VACUUM ，MYSQL 叫 Purge ，在InnoDB中，更新后的行的最新版本只保留在表中。舊版本的行在回滾段，而刪除后的行版本則保留在原處，并標(biāo)記為以后的清理。因此，須從表本身清理標(biāo)記任何已刪除的行，并從回滾段中清除任何更新后的舊版本的行。查找被刪除的記錄所需的所有信息。

所以從設(shè)計(jì)結(jié)構(gòu)上來說postgresql 的結(jié)構(gòu)設(shè)計(jì)要簡單，MYSQL ORACLE 的結(jié)構(gòu)設(shè)計(jì)要復(fù)雜，并且POSTGRESQL 也沒有redo等結(jié)構(gòu)，所以針對POSTGRESQL 最大的問題就是VACUUM ， 而MYSQL  INNODB ，則會面對redo ,undo ，purge 等方面的I/O 壓力。

純個(gè)人認(rèn)為，postgresql 在不考慮vacuum 的情況下，性能上的瓶頸要小于MYSQL 方面的復(fù)雜結(jié)構(gòu)上產(chǎn)生的影響（可以在非頻繁工作期間進(jìn)行一些其他的回收方式）。postgresql 在使用中要給出的磁盤空間要有余量，mysql  在這方面上要好一些。

所以單純說那個(gè) better ，沒有什么意義，有意義的是你掌握了多少他們的特性  knowledge

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“Postgresql和MYSQL如何選擇”這篇文章對大家有幫助，同時(shí)也希望大家多多支持創(chuàng)新互聯(lián)，關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，更多相關(guān)知識等著你來學(xué)習(xí)!