03 instagram鏡像2022(小白準備轉行學習前端，有大神可以提一些建議嗎)

超大數據量，MySQL自身架構的問題

31. 分布式演進

PGSQL

HTAP數據庫：買粉絲ckroachDB、騰訊Tbase

分片集群： Postgres-XC、Postgres-XL

MySQL

HTAP數據庫：TiDB

分片集群： 各種各樣的中間件，不一一列舉

32. 數據庫的文件名和命名規律

PGSQL

PGSQL在這方面做的比較不好，DBA不能在操作系統層面（停庫狀態下）看清楚數據庫的文件名和命名規律，文件的數量，文件的大小

一旦操作系統發生文件丟失或硬盤損壞，非常不利于恢復，因為連名字都不知道

PGSQL表數據物理文件的命名/存放規律是： 在一個表空間下面，如果沒有建表空間默認在默認表空間也就是base文件夾下，例如：/data/base/16454/3599

base：默認表空間pg_default所在的物理文件夾

16454：表所在數據庫的oid

3599：就是表對象的oid，當然，一個表的大小超出1GB之后會再生成多個物理文件，還有表的fsm文件和vm文件，所以一個大表實際會有多個物理文件

由于PGSQL的數據文件布局內容太多，大家可以查閱相關資料

當然這也不能全怪PGSQL，作為一個DBA，時刻做好數據庫備份和容災才是正道，做介質恢復一般是萬不得已的情況下才會做

MySQL

數據庫名就是文件夾名，數據庫文件夾下就是表數據文件，但是要注意表名和數據庫名不能有特殊字符或使用中文名，每個表都有對應的frm文件和ibd文件，存儲元數據和表/索引數據，清晰明了，做介質恢復或者表空間傳輸都很方便

33. 權限設計

PGSQL

PGSQL在權限設計這塊是比較坑爹，拋開實例權限和表空間權限，PGSQL的權限層次有點像SQL Server，db=》schema=》object

要說權限，這里要說一下Oracle，用Oracle來類比

在ORACLE 12C之前，實例與數據庫是一對一，也就是說一個實例只能有一個數據庫，不像MySQL和SQL Server一個實例可以有多個數據庫，并且可以隨意跨庫查詢

而PGSQL不能跨庫查詢的原因也是這樣，PGSQL允許建多個數據庫，跟ORACLE類比就是有多個實例（之前說的實例與數據庫是一對一）

一個數據庫相當于一個實例，因為PGSQL允許有多個實例，所以PGSQL單實例不叫一個實例，叫集簇（cluster），集簇這個概念可以查閱PGSQL的相關資料

PGSQL里面一個實例/數據庫下面的schema相當于數據庫，所以這個schema的概念對應MySQL的database

注意點：正因為是一個數據庫相當于一個實例，PGSQL允許有多個實例/數據庫，所以數據庫之間是互相邏輯隔離的，導致的問題是，不能一次對一個PGSQL集簇下面的所有數據庫做操作

必須要逐個逐個數據庫去操作，例如上面說到的安裝pg_stat_statements插件，如果您需要在PGSQL集簇下面的所有數據庫都做性能收集的話，需要逐個數據庫去執行加載命令

又例如跨庫查詢需要dblink插件或fdw插件，兩個數據庫之間做查詢相當于兩個實例之間做查詢，已經跨越了實例了，所以需要dblink插件或fdw插件，所以道理非常簡單

權限操作也是一樣逐個數據庫去操作，還有一個就是PGSQL雖然像SQL Server的權限層次結構db=》schema=》object，但是實際會比SQL Server要復雜一些，還有就是新建的表還要另外授權

在PGSQL里面，角色和用戶是一樣的，對新手用戶來說有時候會傻傻分不清，也不知道怎么去用角色，所以PGSQL在權限設計這一塊確實比較坑爹

MySQL

使用mysql庫下面的5個權限表去做權限映射，簡單清晰，唯一問題是缺少權限角色

user表

db表

host表

tables_priv表

買粉絲lumns_priv表

1. 架構對比

Mysql：多線程

PostgreSql：多進程

多線程架構和多進程架構之間沒有絕對的好壞，例如oracle在unix上是多進程架構，在windows上是多線程架構。

2. 對存儲過程及事務的支持能力

MySql對于無事務的MyISAM表，采用表鎖定，一個長時間運行的查詢很可能會長時間的阻礙，而PostgreSQL不會尊在這種問題。

PostgreSQL支持存儲過程，要比MySql好，具備本地緩存執行計劃的能力。

3. 穩定性及性能

高并發讀寫，負載逼近極限下，PG的性能指標仍可以維持雙曲線甚至對數曲線，到頂峰之后不再下降，而 MySql 明顯出現一個波峰后下滑（5.5版本后Mysql企業版有優化，需要付費）

MySql的InnoDB引擎，可以充分優化利用系統的所有內存，超大內存下PG對內存使用的不那么充分（需要根據內存情況合理分配）。

4. 高可用

InnoDB的基于回滾實現的 MVCC 機制，對于 PG 新老數據一起放的基于 XID 的 MVCC機制，是占優的。新老數據一起存放，需要定時觸發 VACUUM，會帶來多余的 IO 和數據庫對象加鎖開銷，引起數據庫整理的并發能力下降。而且 VACUUM 清理不及時，還可能會引發數據膨脹

5. 數據同步方式：

Mysql到現在也是異步復制，pgsql可以做到同步、異步、半同步復制。

Mysql同步是基于binlog復制，屬于邏輯復制，類似于oracle golden gate，是基于stream的復制，做到同步很困難，這種方式更加適合異步復制；

Pgsql的同是基于wal，屬于物理復制，可以做到同步復制。同時，pgsql還提供stream復制。

Mysql的復制可以用多級從庫，但是在9.2之前，PgSql不能用從庫帶從庫。

Pgsql的主從復制屬于物理復制，相對于Mysql基于binlog的邏輯復制，數據的一致性更加可靠，復制性能更高，對主機性能的影響也更小。

6. 權限控制對比

MySql允許自定義一套不同的數據級、表級和列的權限，運行指定基于主機的權限

Mysql的merge表提供了 一個獨特管理多個表的方法。myisampack可以對只讀表進行壓縮，以后仍然可以直接訪問該表中的行。

7. SQL語句支持能力

PG有極其強悍的 SQL 編程能力（9.x 圖靈完備，支持遞歸！），有非常豐富的統計函數和統計語法支持，例如分析函數（Oracle的叫法，PG里叫window函數）

支持用多種語言來寫存儲過程，對于R的支持也很好。這一點上Mysql就差的很遠，很多分析功能都不支持。

PgSql對表名大小寫的處理，只有在Sql語句中，表明加雙引號，才區分大小寫。

在Sql的標準實現上要比Mysql完善，而且功能實現比較嚴謹。

對表連接支持比較完整，優化器的功能比較完整，支持的索引類型很多，復雜查詢能力較強。

Mysql采用索引組織表，這種存儲方式非常適合基于主鍵匹配的查詢、刪改操作，但是對表結果設計存在約束；

Mysql的Join操作的性能非常的差，只支持Nest Join，所以一旦數據量大，性能就非常的差。PostgresSQL除了支持 Nest Join 和 Sort Merge Join，PostgreSQL還支持正則表達式查詢，MySql不支持。

8. 數據類型支持能力

PostgreSQL可以更方便的使用UDF（用戶定義函數）進行擴展。

有豐富的幾何類型，實際上不止集合類型，PG有大量的字典、數組、bitmap等數據類型，因此PG多年來在 GIS 領域處于優勢地位。相比之下Mysql就差很多，instagram就是因為PG的空間數據擴展 PostGIS遠遠強于 MySql的 my spatial 而采用 PgSql的。Mysql中的空間數據類型有4種，分別是 CEOMETRY、POINT、LINESTRING、POLYGON，其空間索引只能在存儲引擎為 MyiSam的表中創建，用SPATIAL關鍵字進行擴展，使得能夠用于創建正規索引類型的語法創建空間索引。創建空間索引的列，必須將其聲明為NOT NULL。不同的存儲親情有差別。MyISAM和InnoDB 都支持 spatial extensions，但差別在于：如果使用MyISAM，可以建立 spatial index，而 InnoDB是不支持的。

pgsql對json支持比較好，還有很逆天的fdw功能，就是把別的數據庫中的表當自己的用。

pgsql的字段類型支持的多，有很多mysql沒有的類型，但是實際中有時候用到。

一半關系型數據庫的字符串長度8k左右，無限長的 TEXT 類型的功能受限，只能作為外部帶數據訪問。而 PG 的 TEXT 類型可以直接訪問，SQL 語法內置正則表達式，可以索引，還可以全文檢索，或使用 xml xpath。用 PG 的話，文檔數據庫都可以省了。

postgresql 有函數，用于報表、統計很方便

PG支持 R-Trees這樣可擴展的索引類型，可以方便的處理一些特殊數據。

PG可以使用函數和條件所以，使得數據庫的調優非常靈活，mysql就沒有這個功能，條件索引在web應用中很重要。

9. 如可過程容錯能力

大批量數據入庫，PostgreSql要求所有的數據必須完全滿足要求，有一條錯誤，整個數據入庫過程失敗。MySql無此問題。

10. 表組織方式

pgsql用繼承的方式實現分區表，讓分區表的使用不方便且性能差，這點比不上mysql。

pg主表采用堆表存放，MySQL采用索引組織表，能夠支持比MySql更大的數據量。

MySql分區表的實現要優于PG的基于繼承表的分區實現，主要體現在分區個數達到成千上萬后的處理性能差異很大。

11. 開發結構

對于web應用來所，mysql 5.6 的內置 MC API 功能很好用，PgSQL差一些。

PG的“無鎖定”特性非常突出，甚至包括 vacuum 這樣的整理數據空間的操作，這個和 PGSQL的 MVCC 實現有關系。

好文要頂 關注我 收藏該文  

茄子777

粉絲 - 0 關注 - 0

+加關注

00

« 上一篇： 多線程中的wait與join

» 下一篇： 負載均衡相關

posted @ 2022-11-02 16:20 茄子777 閱讀(55) 評論(0) 編輯 收藏 舉報

刷新評論刷新頁面返回頂部

登錄后才能查看或發表評論，立即 登錄 或者 逛逛 博客園首頁

【推薦】阿里云新人特

很赞哦!（83213）