postgresql和mysql的區(qū)別？

1 架構(gòu)對(duì)比

mysql：多線程

postgresql：多進(jìn)程

多線程架構(gòu)和多進(jìn)程架構(gòu)之間沒(méi)有絕對(duì)的好壞，例如ORACLE在unix上是多進(jìn)程架構(gòu)，在windows上是多線程架構(gòu)。

PG 的有多種集群架構(gòu)可以選擇，plproxy 可以支持語(yǔ)句級(jí)的鏡像或分片，slony 可以進(jìn)行字段級(jí)的同步設(shè)置，standby 可以構(gòu)建WAL文件級(jí)或流式的讀寫(xiě)分離集群，同步頻率和集群策略調(diào)整方便，操作非常簡(jiǎn)單。

PGSQL對(duì)于numa架構(gòu)的支持比mysql強(qiáng)一些，比MYSQL對(duì)于讀的性能更好一些，pgsql提交可以完全異步，而mysql的內(nèi)存表不夠?qū)嵱茫ㄒ驗(yàn)楸礞i的原因）

2 對(duì)存儲(chǔ)過(guò)程及事務(wù)的支持能力

1） MySQL對(duì)于無(wú)事務(wù)的MyISAM表，采用表鎖定，一個(gè)長(zhǎng)時(shí)間運(yùn)行的查詢很可能會(huì)長(zhǎng)時(shí)間地阻礙對(duì)表的更新，而PostgreSQL不存在這樣的問(wèn)題。

2） PostgreSQL支持存儲(chǔ)過(guò)程，要比MySQL好，具備本地緩存執(zhí)行計(jì)劃的能力；

3） MySQL 4.0.2-alpha開(kāi)始支持事務(wù)的概念，保留無(wú)事務(wù)的表類(lèi)型， 為用戶提供了更多的選擇。

3 穩(wěn)定性及性能

1）高并發(fā)讀寫(xiě)，負(fù)載逼近極限下，PG的性能指標(biāo)仍可以維持雙曲線甚至對(duì)數(shù)曲線，到頂峰之后不再下降，而 MySQL 明顯出現(xiàn)一個(gè)波峰后下滑（5.5版本之后，在企業(yè)級(jí)版本中有個(gè)插件可以改善很多，不過(guò)需要付費(fèi)）

2） PostgreSQL 的穩(wěn)定性極強(qiáng)， Innodb 等引擎在崩潰、斷電之類(lèi)的災(zāi)難場(chǎng)景下抗打擊能力有了長(zhǎng)足進(jìn)步，然而很多 MySQL 用戶都遇到過(guò)Server級(jí)的數(shù)據(jù)庫(kù)丟失的場(chǎng)景——mysql系統(tǒng)庫(kù)是MyISAM的，相比之下，PG數(shù)據(jù)庫(kù)這方面要好一些。

3） mysql的innodb引擎，可以充分優(yōu)化利用系統(tǒng)所有內(nèi)存，超大內(nèi)存下PG對(duì)內(nèi)存使用的不那么充分（需要根據(jù)內(nèi)存情況合理配置）。從測(cè)試結(jié)果上看，mysql 5.5的性能提升很大，單機(jī)性能強(qiáng)于pgsql，5.6應(yīng)該會(huì)強(qiáng)更多。

4 高可用性

MySQL可以適應(yīng)24/7運(yùn)行。在絕大多數(shù)情況下，你不需要為MySQL運(yùn)行任何清除程序。PostgreSQL目前仍不完全適應(yīng)24/7運(yùn)行，這是因?yàn)槟惚仨毭扛粢欢螘r(shí)間運(yùn)行一次VACUUM。

innodb的基于回滾段實(shí)現(xiàn)的MVCC機(jī)制，相對(duì)PG新老數(shù)據(jù)一起存放的基于XID的MVCC機(jī)制，是占優(yōu)的。新老數(shù)據(jù)一起存放，需要定時(shí)觸 發(fā)VACUUM，會(huì)帶來(lái)多余的IO和數(shù)據(jù)庫(kù)對(duì)象加鎖開(kāi)銷(xiāo)，引起數(shù)據(jù)庫(kù)整體的并發(fā)能力下降。而且VACUUM清理不及時(shí)，還可能會(huì)引發(fā)數(shù)據(jù)膨脹；

5 數(shù)據(jù)同步方式

1）mysql到現(xiàn)在也是異步復(fù)制，pgsql可以做到同步，異步，半同步復(fù)制。

2） mysql的同步是基于binlog復(fù)制，類(lèi)似oracle golden gate,是基于stream的復(fù)制，做到同步很困難，這種方式更加適合異地復(fù)制；pgsql的復(fù)制基于wal，可以做到同步復(fù)制。同時(shí)，pgsql還提供stream復(fù)制。

3） MySQL的復(fù)制可以用多級(jí)從庫(kù)，但是在9.2之前，PGSQL不能用從庫(kù)帶從庫(kù)。

4） PG的主備復(fù)制屬于物理復(fù)制，相對(duì)于MySQL基于binlog的邏輯復(fù)制，數(shù)據(jù)的一致性更加可靠，復(fù)制性能更高，對(duì)主機(jī)性能的影響也更小。

7 權(quán)限控制對(duì)比

MySQL允許你定義一整套的不同的數(shù)據(jù)級(jí)、表級(jí)和列級(jí)的權(quán)限，允許你指定基于主機(jī)的權(quán)限；

MySQL的merge表提供了一個(gè)獨(dú)特管理多個(gè)表的方法。myisampack可以對(duì)只讀表進(jìn)行壓縮，此后仍然可以直接訪問(wèn)該表中的行。

7 SQL語(yǔ)句支持能力

1） PG有極其強(qiáng)悍的 SQL 編程能力（9.x 圖靈完備，支持遞歸！），有非常豐富的統(tǒng)計(jì)函數(shù)和統(tǒng)計(jì)語(yǔ)法支持，比如分析函數(shù)（ORACLE的叫法，PG里叫window函數(shù)）；

2） 支持用多種語(yǔ)言來(lái)寫(xiě)存儲(chǔ)過(guò)程，對(duì)于R的支持也很好。這一點(diǎn)上MYSQL就差的很遠(yuǎn)，很多分析功能都不支持。騰訊內(nèi)部數(shù)據(jù)存儲(chǔ)主要是MYSQL，但是數(shù)據(jù)分析主要是HADOOP+PGSQL（聽(tīng)李元佳說(shuō)過(guò)，但是沒(méi)有驗(yàn)證過(guò)）。

3） pgsql對(duì)表名大小的處理，只有在SQL語(yǔ)句中，表名加雙引號(hào)，才區(qū)分大小寫(xiě)。

4）在SQL的標(biāo)準(zhǔn)實(shí)現(xiàn)上要比MySQL完善，而且功能實(shí)現(xiàn)比較嚴(yán)謹(jǐn)；

5）對(duì)表連接支持較完整，優(yōu)化器的功能較完整，支持的索引類(lèi)型很多，復(fù)雜查詢能力較強(qiáng)；

6） MySQL采用索引組織表，這種存儲(chǔ)方式非常適合基于主鍵匹配的查詢、刪改操作，但是對(duì)表結(jié)構(gòu)設(shè)計(jì)存在約束；

7） MySQL的Join操作的性能非常的差，只支持Nest Join，所以一旦數(shù)據(jù)量大，性能就非常的差。PostgreSQL除了支持nest join外，還支持hash join和 sort merge join；PostgreSQL支持正則表達(dá)式查找，MySQL不支持；

8 數(shù)據(jù)類(lèi)型支持能力

PostgreSQL可以更方便地使用UDF(用戶定義函數(shù))進(jìn)行擴(kuò)展。

1）有豐富的幾何類(lèi)型，實(shí)際上不止幾何類(lèi)型，PG有大量字典、數(shù)組、bitmap 等數(shù)據(jù)類(lèi)型, 因此PG 多年來(lái)在 GIS 領(lǐng)域處于優(yōu)勢(shì)地位。相比之下mysql就差很多，instagram就是因?yàn)镻G的空間數(shù)據(jù)庫(kù)擴(kuò)展POSTGIS遠(yuǎn)遠(yuǎn)強(qiáng)于MYSQL的my spatial而采用PGSQL的。

MYSQL中的空間數(shù)據(jù)類(lèi)型有4種，分別是GEOMETRY、POINT、LINESTRING、POLYGON，其空間索引只能在存儲(chǔ)引擎為MYISAM的表中創(chuàng)建，用SPATIAL關(guān)鍵字進(jìn)行擴(kuò)展，使得能夠用于創(chuàng)建正規(guī)索引類(lèi)型的語(yǔ)法創(chuàng)建空間索引。創(chuàng)建空間索引的列，必須將其聲明為NOT NULL。不同的存儲(chǔ)引擎有差別。

MyISAM和InnoDB都支持spatial extensions，但差別在于：如果使用MyISAM，可以建立spatial index，而InnoDB是不支持的。

2） pgsql對(duì)json支持比較好，還有很逆天的fdw]功能，就是把別的數(shù)據(jù)庫(kù)的表當(dāng)自己的用；

3） pgsql的字段類(lèi)型支持的多，有很多mysql沒(méi)有的類(lèi)型，但是實(shí)際中有時(shí)候用到。

4） 一般關(guān)系型數(shù)據(jù)庫(kù)的字符串有限定長(zhǎng)度8k左右，無(wú)限長(zhǎng) TEXT 類(lèi)型的功能受限，只能作為外部大數(shù)據(jù)訪問(wèn)。而 PG 的 TEXT 類(lèi)型可以直接訪問(wèn)，SQL語(yǔ)法內(nèi)置正則表達(dá)式，可以索引，還可以全文檢索，或使用xml xpath。用PG的話，文檔數(shù)據(jù)庫(kù)都可以省了。

5） postgresql有g(shù)rouping sets函數(shù)，也是迫使我拋棄mysql第一原因。做報(bào)表后臺(tái)計(jì)算，olap/oltp之類(lèi)的這個(gè)函數(shù)簡(jiǎn)直是剛性需求。沒(méi)有g(shù)rouping sets函數(shù)，我感覺(jué)做報(bào)表后臺(tái)計(jì)算，簡(jiǎn)直慘不忍睹。

當(dāng)然pgsql還有挺多很好用的窗口函數(shù)之類(lèi)，用起來(lái)真心爽。mysql做數(shù)據(jù)報(bào)表計(jì)算后臺(tái)最大缺點(diǎn)就是沒(méi)有g(shù)rouping sets和一些窗口函數(shù)，替代方案很麻煩而且效率低，做很多統(tǒng)計(jì)數(shù)據(jù)各種表連接、外連接等等一大堆，不同數(shù)據(jù)庫(kù)之間數(shù)據(jù)的利用計(jì)算。

8） PG支持R-trees這樣可擴(kuò)展的索引類(lèi)型，可以更方便地處理一些特殊數(shù)據(jù)。

9）PG可以使用函數(shù)和條件索引，使得數(shù)據(jù)庫(kù)的調(diào)優(yōu)非常靈活，mysql就沒(méi)有這個(gè)功能，條件索引在web應(yīng)用中很重要。

9 入庫(kù)過(guò)程容錯(cuò)能力

大批量數(shù)據(jù)入庫(kù)，PostgresSQL要求所有數(shù)據(jù)必須完全滿足要求，有一條錯(cuò)誤，整個(gè)數(shù)據(jù)入庫(kù)過(guò)程失敗；MySQL無(wú)此問(wèn)題。比如，每天1000萬(wàn)行數(shù)據(jù)，就因?yàn)橐粭l打印的不完整，PostgreSQL會(huì)直接報(bào)錯(cuò)，導(dǎo)致一條也導(dǎo)入不進(jìn)去。

1000萬(wàn)里面有一行將數(shù)字類(lèi)型的等級(jí)打印成了字符串的東西，那么pgsql會(huì)非讓你找出這一條刪掉，然后才能將剩下的數(shù)據(jù)導(dǎo)入進(jìn)去。mysql就完全沒(méi)有這個(gè)問(wèn)題，比如mysql level字段定義的int類(lèi)型，幾千萬(wàn)中有一條數(shù)據(jù)沒(méi)注意打印成字符串，mysql會(huì)自己給你轉(zhuǎn)成0存儲(chǔ)的，不會(huì)有任何報(bào)錯(cuò)。

10 表組織方式

1） pgsql用繼承的方法實(shí)現(xiàn)分區(qū)表，讓分區(qū)表的使用不方便且性能差，這點(diǎn)比不上mysql。

2） PG主表采用堆表存放，MySQL采用索引組織表，能夠支持比MySQL更大的數(shù)據(jù)量；

3） MySQL分區(qū)表的實(shí)現(xiàn)要優(yōu)于PG的基于繼承表的分區(qū)實(shí)現(xiàn)，主要體現(xiàn)在分區(qū)個(gè)數(shù)達(dá)到上千上萬(wàn)后的處理性能差異較大。

11 開(kāi)發(fā)接口

對(duì)于web應(yīng)用來(lái)說(shuō),mysql 5.6 的內(nèi)置MC API功能很好用，PGSQL差一些。

PG 的“無(wú)鎖定”特性非常突出，甚至包括 vacuum 這樣的整理數(shù)據(jù)空間的操作，這個(gè)和PGSQL的MVCC實(shí)現(xiàn)有關(guān)系。

12 維護(hù)團(tuán)隊(duì)

MySQL的背后是一個(gè)成熟的商業(yè)公司，使得MySQL的開(kāi)發(fā)過(guò)程更為慎重；

PostgreSQL的背后是一個(gè)龐大的志愿開(kāi)發(fā)組， PostgreSQL的反應(yīng)更為迅速。這樣的兩種背景直接導(dǎo)致了各自固有的優(yōu)點(diǎn)和缺點(diǎn)。

對(duì)于一個(gè)嚴(yán)肅的商業(yè)應(yīng)用來(lái)說(shuō)，事務(wù)的支持是不可或缺的。對(duì)于一個(gè)嚴(yán)肅的商業(yè)應(yīng)用來(lái)說(shuō)，作為數(shù)據(jù)庫(kù)本身，有眾多的商業(yè)邏輯的存在，此時(shí)使用存儲(chǔ)過(guò)程可以在較少地增加數(shù)據(jù)庫(kù)服務(wù)器的負(fù)擔(dān)的前提下，對(duì)這樣的商業(yè)邏輯進(jìn)行封裝，并可以利用數(shù)據(jù)庫(kù)服務(wù)器本身的內(nèi)在機(jī)制對(duì)存儲(chǔ)過(guò)程的執(zhí)行進(jìn)行優(yōu)化。此外存儲(chǔ)過(guò)程的存在也避免了在網(wǎng)絡(luò)上大量的原始的SQL語(yǔ)句的傳輸，這樣的優(yōu)勢(shì)是顯而易見(jiàn)的。

用統(tǒng)一的SQL，去訪問(wèn)其他關(guān)系數(shù)據(jù)庫(kù)，其他NoSQL數(shù)據(jù)庫(kù)，HBase，甚至是各種格式的文件，操作系統(tǒng)信息，在線數(shù)據(jù)集。