pgsql的主鍵存儲方式

pgsql的主鍵存儲方式

PostgreSQL的穩(wěn)定性極強(qiáng)，Innodb等索引在崩潰，斷電之類的災(zāi)難場景下 抗擊打能力有了長足進(jìn)步，然而很多 MqSQL用戶 都遇到過 Server級的數(shù)據(jù)庫丟失的場景 — MySQL系統(tǒng)庫是 MyISAM，相比之下，PG數(shù)據(jù)庫這方面要更好一些。 任何系統(tǒng)都有它的性能極限，在高并發(fā)讀寫，負(fù)載逼近極限下，PG的性能指標(biāo)仍可以位置雙曲線甚至對數(shù)曲線，到 頂峰之后不在下降，而MySQL明顯出現(xiàn)一個波峰后下滑（5.5版本 之后，在企業(yè)級版本中有個插件可以改善很多，不過需要付費(fèi)）。

PG多年來在 GIS（地理信息）領(lǐng)域處于優(yōu)勢地位，因為它有豐富的幾何類型，PG有大量字典，數(shù)組，bitmap等數(shù)據(jù)類型，相比之下 MySQL就差很多， Instagram就是因為 PG的空間數(shù)據(jù)庫 擴(kuò)展 POSTGIS遠(yuǎn)遠(yuǎn)強(qiáng)于 MySQL的 my spatial 而采用 PgSQL的。

PG的“無鎖定”特性非常突出，甚至包括 vacuum這樣的整理數(shù)據(jù)空間的操作，這個和PGSQL的MVCC實現(xiàn)有關(guān)系。 PG可以使用函數(shù) 和 條件索引，這使得 PG數(shù)據(jù)庫的調(diào)優(yōu)非常靈活， MySQL就沒有這個功能，條件索引在 web應(yīng)用中 很重要。 PG有極其強(qiáng)悍的 SQL編程能力（9.x 圖靈完備，支持遞歸！），有非常豐富的統(tǒng)計函數(shù)和統(tǒng)計語法支持，比如分析函數(shù)（Oracle的叫法，PG里叫Window函數(shù)），還可以用多種語言來寫存儲過程，對于 R的支持也很好。這一點(diǎn)MySQL就差很多，很多分析功能都不支持，騰訊內(nèi)部的存儲主要是 MySQL，但是數(shù)據(jù)分析主要是 Hadoop+ PgSQL。

PG的有多種集群架構(gòu)可以選擇，plproxy可以之hi語句級的鏡像或分片，slony可以進(jìn)行字段級的同步配置，standby 可以構(gòu)建 WAL文件級或流式的讀寫分離集群，同步頻率和集群策略調(diào)整方便。 一般關(guān)系型數(shù)據(jù)庫字符串有長度限制 8k 左右，無限長 TEXT類型的功能受限，只能作為外部大數(shù)據(jù)訪問。而 PG 的 TEXT 類型 可以直接訪問且無長度限制， SQL語法內(nèi)置 正則表達(dá)式，可以索引，還可以全文檢索，或使用 xml xpath。

用 PG的話，文檔數(shù)據(jù)庫都可以省了。 PgSQL對于 numa 架構(gòu)的支持比 MySQL強(qiáng)一些，比 MySQL對于讀的性能更好一些， PgSQL提交可以完全異步提交，而 MySQL的內(nèi)存表不夠?qū)嵱茫ㄒ驗楸礞i的原因）。pgsql除了存儲正常的數(shù)據(jù)類型外，還支持存儲array，不管是一維數(shù)組還是多維數(shù)組均支持。

json和jsonb，相比使用 text存儲要高效很多。json和 jsonb在更高的層面上看起來幾乎是一樣的，但是存儲實現(xiàn)上是不同的。json存儲完的文本，json列會每次都解析存儲的值，它不支持索引，但 可以為創(chuàng)建表達(dá)式索引。

jsonb存儲的二進(jìn)制格式，避免了重新解析數(shù)據(jù)結(jié)構(gòu)。它支持索引，這意味著 可以不使用指定索引就能查詢?nèi)魏温窂?。?dāng)我們比較寫入數(shù)據(jù)速度時，由于數(shù)據(jù)存儲 的方式的原因，jsonb會比 json 稍微的慢一點(diǎn)。json列會每次都 解析存儲的值，這意味著鍵的順序要和輸入的 時候一樣。

但是 jsonb不同，以二進(jìn)制格式存儲且不保證鍵的順序。因此如果有軟件需要依賴鍵的順序，jsonb可能不是**選擇。使用 jsonb的優(yōu)勢還在于可以輕易的整合關(guān)系型數(shù)據(jù)和非關(guān)系型 數(shù)據(jù) ，PostgreSQL對于 mongodb這類數(shù)據(jù)庫是一個不小的威脅，畢竟如果一個表中只有一列數(shù)據(jù)的類型是半結(jié)構(gòu)化的，沒有必要為了遷就它而整個表的設(shè)計都采用 schemaless的結(jié)構(gòu)。1. CPU限制PGSQL沒有CPU核心數(shù)限制，有多少CPU核就用多少M(fèi)ySQL能用128核CPU，超過128核用不上 2. 配置文件參數(shù)PGSQL一共有255個參數(shù)，用到的大概是80個，參數(shù)比較穩(wěn)定，用上個大版本配置文件也可以啟動當(dāng)前大版本數(shù)據(jù)庫 MySQL一共有707個參數(shù)，用到的大概是180個，參數(shù)不斷增加，就算小版本也會增加參數(shù)，大版本之間會有部分參數(shù)不兼容情況3. 第三方工具依賴情況PGSQL只有高可用集群需要依靠第三方中間件，例如：patroni+etcd、repmgr MySQL大部分操作都要依靠percona公司的第三方工具（percona-toolkit，XtraBackup），工具命令太多，學(xué)習(xí)成本高，高可用集群也需要第三方中間件，**MGR集群還沒成熟4. 高可用主從**底層原理PGSQL物理流**，屬于物理**，跟SQL Server鏡像/AlwaysOn一樣，嚴(yán)格一致，沒有任何可能導(dǎo)致不一致，性能和可靠性上，物理**完勝邏輯**，維護(hù)簡單 MySQL主從**，屬于邏輯**，（sql_log_bin、binlog_format等參數(shù)設(shè)置不正確都會導(dǎo)致主從不一致）大事務(wù)并行**效率低，對于重要業(yè)務(wù)，需要依賴 percona-toolkit的pt-table-checksum和pt-table-sync工具定期比較和修復(fù)主從一致主從**出錯嚴(yán)重時候需要重搭主從MySQL的邏輯**并不阻止兩個不一致的數(shù)據(jù)庫建立**關(guān)系5. 從庫只讀狀態(tài)PGSQL系統(tǒng)自動設(shè)置從庫默認(rèn)只讀，不需要人工介入，維護(hù)簡單 MySQL從庫需要手動設(shè)置參數(shù)super_read_only=on，讓從庫設(shè)置為只讀，super_read_only參數(shù)有bug，鏈接：https://baijiahao.baidu.com/s?id=94388753&wfr=spider&for=pc6. 版本分支PGSQL只有社區(qū)版，沒有其他任何分支版本，PGSQL**統(tǒng)一開發(fā)，統(tǒng)一維護(hù)，社區(qū)版有所有功能，不像SQL Server和MySQL有標(biāo)準(zhǔn)版、企業(yè)版、經(jīng)典版、社區(qū)版、開發(fā)版、web版之分國內(nèi)外還有一些基于PGSQL做二次開發(fā)的數(shù)據(jù)庫廠商，例如：Enterprise DB、瀚高數(shù)據(jù)庫等等，當(dāng)然這些只是二次開發(fā)并不算獨(dú)立分支 MySQL由于歷史原因，分裂為三個分支版本，MariaDB分支、Percona分支 、Oracle**分支，發(fā)展到目前為止各個分支基本互相不兼容Oracle**分支還有版本之分，分為標(biāo)準(zhǔn)版、企業(yè)版、經(jīng)典版、社區(qū)版7. SQL特性支持PGSQLSQL特性支持情況支持94種，SQL語法支持最完善，例如：支持公用表表達(dá)式（WITH查詢） MySQLSQL特性支持情況支持36種，SQL語法支持比較弱，例如：不支持公用表表達(dá)式（WITH查詢） 關(guān)于SQL特性支持情況的對比，可以參考：http://www.sql-workbench.net/dbms_comparison.html8. 主從**安全性PGSQL同步流**、強(qiáng)同步（remote apply)、高安全，不會丟數(shù)據(jù)PGSQL同步流**：所有從庫宕機(jī)，主庫會**，主庫無法自動切換為異步流**（異步模式），需要通過增加從庫數(shù)量來解決，一般生產(chǎn)環(huán)境至少有兩個從庫手動解決：在PG主庫修改參數(shù)synchronous_standby_names =\’\’，并執(zhí)行命令： pgctl reload ，把主庫切換為異步模式主從數(shù)據(jù)完全一致是高可用切換的**前提，所以PGSQL選擇主庫**也是可以理解 MySQL增強(qiáng)半同步** ，mysql5.7版本增強(qiáng)半同步才能保證主從**時候不丟數(shù)據(jù)mysql5.7半同步**相關(guān)參數(shù)：參數(shù)rpl_semi_sync_master_wait_for_slave_count 等待至少多少個從庫接收到binlog，主庫才提交事務(wù)，一般設(shè)置為1，性能**參數(shù)rpl_semi_sync_master_timeout 等待多少毫秒，從庫無回應(yīng)自動切換為異步模式，一般設(shè)置為無限大，不讓主庫自動切換為異步模式所有從庫宕機(jī)，主庫會**，因為無法收到任何從庫的應(yīng)答包手動解決：在MySQL主庫修改參數(shù)rpl_semi_sync_master_wait_for_slave_count=09. 多字段統(tǒng)計信息PGSQL支持多字段統(tǒng)計信息 MySQL不支持多字段統(tǒng)計信息10. 索引類型PGSQL多種索引類型（btree , hash , gin , gist , sp-gist , brin , bloom , rum , zombodb , bitmap，部分索引，表達(dá)式索引） MySQLbtree 索引，全文索引（低效），表達(dá)式索引(需要建虛擬列)，hash 索引只在內(nèi)存表11. 物理表連接算法PGSQL支持 nested-loop join 、hash join 、merge join MySQL只支持 nested-loop join12. 子查詢和視圖性能PGSQL子查詢，視圖優(yōu)化，性能比較高 MySQL視圖謂詞條件下推限制多，子查詢上拉限制多13. 執(zhí)行**即時編譯PGSQL支持 JIT 執(zhí)行**即時編譯，使用LLVM編譯器 MySQL不支持執(zhí)行**即時編譯14. 并行查詢PGSQL并行查詢（多種并行查詢優(yōu)化方法），并行查詢一般多見于商業(yè)數(shù)據(jù)庫，是重量級功能 MySQL有限，只支持主鍵并行查詢15. 物化視圖PGSQL支持物化視圖 MySQL不支持物化視圖16. 插件功能PGSQL支持插件功能，可以豐富PGSQL的功能，GIS地理插件，時序數(shù)據(jù)庫插件， 向量化執(zhí)行插件等等 MySQL不支持插件功能17. check約束PGSQL支持check約束 MySQL不支持check約束，可以寫check約束，但存儲引擎會忽略它的作用，因此check約束并不起作用（mariadb 支持）18. gpu 加速SQLPGSQL可以使用gpu 加速SQL的執(zhí)行速度 MySQL不支持gpu 加速SQL 的執(zhí)行速度 19. 數(shù)據(jù)類型PGSQL數(shù)據(jù)類型豐富，如 ltree，hstore，數(shù)組類型，ip類型，text類型，有了text類型不再需要varchar，text類型字段**存儲1GB MySQL數(shù)據(jù)類型不夠豐富20. 跨庫查詢PGSQL不支持跨庫查詢，這個跟Oracle 12C以前一樣 MySQL可以跨庫查詢21. 備份還原PGSQL備份還原非常簡單，時點(diǎn)還原操作比SQL Server還要簡單，完整備份+wal歸檔備份（增量）假如有一個三節(jié)點(diǎn)的PGSQL主從集群，可以隨便在其中一個節(jié)點(diǎn)做完整備份和wal歸檔備份 MySQL備份還原相對不太簡單，完整備份+binlog備份（增量）完整備份需要percona的XtraBackup工具做物理備份，MySQL本身不支持物理備份時點(diǎn)還原操作步驟繁瑣復(fù)雜22. 性能視圖PGSQL需要安裝pg_stat_statements插件，pg_stat_statements插件提供了豐富的性能視圖：如：等待事件，系統(tǒng)統(tǒng)計信息等不好的地方是，安裝插件需要重啟數(shù)據(jù)庫，并且需要收集性能信息的數(shù)據(jù)庫需要執(zhí)行一個命令：create extension pg_stat_statements命令否則不會收集任何性能信息，比較麻煩 MySQL自帶PS庫，默認(rèn)很多功能沒有打開，而且打開PS庫的性能視圖功能對性能有影響（如：內(nèi)存占用導(dǎo)致OOM bug）23. 安裝方式PGSQL有各個平臺的包rpm包，deb包等等，相比MySQL缺少了二進(jìn)制包，一般用源碼編譯安裝，安裝時間會長一些，執(zhí)行命令多一些 MySQL有各個平臺的包rpm包，deb包等等，源碼編譯安裝、二進(jìn)制包安裝，一般用二進(jìn)制包安裝，方便快捷24. DDL操作PGSQL加字段、可變長字段類型長度改大不會鎖表，所有的DDL操作都不需要借助第三方工具，并且跟商業(yè)數(shù)據(jù)庫一樣，DDL操作可以回滾，保證事務(wù)一致性 MySQL由于大部分DDL操作都會鎖表，例如加字段、可變長字段類型長度改大，所以需要借助percona-toolkit里面的pt-online-schema-change工具去完成操作將影響減少到**，特別是對大表進(jìn)行DDL操作DDL操作不能回滾25. 大版本發(fā)布速度PGSQLPGSQL每年一個大版本發(fā)布，大版本發(fā)布的第二年就可以上生產(chǎn)環(huán)境，版本迭代速度很快PGSQL 9.6正式版推出時間：2016年P(guān)GSQL 10 正式版推出時間：2017年P(guān)GSQL 11 正式版推出時間：2018年P(guān)GSQL 12 正式版推出時間：2019年 MySQLMySQL的大版本發(fā)布一般是2年~3年，一般大版本發(fā)布后的第二年才可以上生產(chǎn)環(huán)境，避免有坑，版本發(fā)布速度比較慢MySQL5.5正式版推出時間：2010年MySQL5.6正式版推出時間：2013年MySQL5.7正式版推出時間：2015年MySQL8.0正式版推出時間：2018年26. returning語法PGSQL支持returning語法，returning clause 支持 DML 返回 Resultset，減少一次 Client <-> DB Server 交互 MySQL不支持returning語法27. 內(nèi)部架構(gòu)PGSQL多進(jìn)程架構(gòu)，并發(fā)連接數(shù)不能太多，跟Oracle一樣，既然跟Oracle一樣，那么很多優(yōu)化方法也是相通的，例如：開啟大頁內(nèi)存 MySQL多線程架構(gòu)，雖然多線程架構(gòu)，但是**有限制連接數(shù)，原因是系統(tǒng)的并發(fā)度是有限的，線程數(shù)太多，反而系統(tǒng)的處理能力下降，隨著連接數(shù)上升，反而性能下降一般同時只能處理200 ~300個數(shù)據(jù)庫連接28. 聚集索引PGSQL不支持聚集索引，PGSQL本身的MVCC的實現(xiàn)機(jī)制所導(dǎo)致 MySQL支持聚集索引29. 空閑事務(wù)終結(jié)功能PGSQL通過設(shè)置 idle_in_transaction_session_timeout 參數(shù)來終止空閑事務(wù)，比如：應(yīng)用代碼中忘記關(guān)閉已開啟的事務(wù)，PGSQL會自動查殺這種類型的會話事務(wù) MySQL不支持終止空閑事務(wù)功能30. 應(yīng)付超大數(shù)據(jù)量PGSQL不能應(yīng)付超大數(shù)據(jù)量，由于PGSQL本身的MVCC設(shè)計問題，需要**回收，只能期待后面的大版本做優(yōu)化 MySQL不能應(yīng)付超大數(shù)據(jù)量，MySQL自身架構(gòu)的問題31. 分布式演進(jìn)PGSQLHTAP數(shù)據(jù)庫：cockroachDB、騰訊Tbase分片集群： Postgres-XC、Postgres-XL MySQLHTAP數(shù)據(jù)庫：TiDB分片集群： 各種各樣的中間件，不一一列舉32. 數(shù)據(jù)庫的文件名和命名規(guī)律PGSQLPGSQL在這方面做的比較不好，DBA不能在操作系統(tǒng)層面（停庫狀態(tài)下）看清楚數(shù)據(jù)庫的文件名和命名規(guī)律，文件的數(shù)量，文件的大小一旦操作系統(tǒng)發(fā)生文件丟失或硬盤損壞，非常不利于恢復(fù)，因為連名字都不知道PGSQL表數(shù)據(jù)物理文件的命名/存放規(guī)律是： 在一個表空間下面，如果沒有建表空間默認(rèn)在默認(rèn)表空間也就是base文件夾下，例如：/data/base/16454/3599base：默認(rèn)表空間pg_default所在的物理文件夾16454：表所在數(shù)據(jù)庫的oid3599：就是表對象的oid，當(dāng)然，一個表的大小超出1GB之后會再生成多個物理文件，還有表的f**文件和vm文件，所以一個大表實際會有多個物理文件由于PGSQL的數(shù)據(jù)文件布局內(nèi)容太多，大家可以查閱相關(guān)資料當(dāng)然這也不能全怪PGSQL，作為一個DBA，時刻做好數(shù)據(jù)庫備份和容災(zāi)才是正道，做介質(zhì)恢復(fù)一般是萬不得已的情況下才會做 MySQL數(shù)據(jù)庫名就是文件夾名，數(shù)據(jù)庫文件夾下就是表數(shù)據(jù)文件，但是要注意表名和數(shù)據(jù)庫名不能有特殊字符或使用中文名，每個表都有對應(yīng)的frm文件和ibd文件，存儲元數(shù)據(jù)和表/索引數(shù)據(jù)，清晰明了，做介質(zhì)恢復(fù)或者表空間傳輸都很方便33. 權(quán)限設(shè)計PGSQLPGSQL在權(quán)限設(shè)計這塊是比較坑爹，拋開實例權(quán)限和表空間權(quán)限，PGSQL的權(quán)限層次有點(diǎn)像SQL Server，db=》schema=》object要說權(quán)限，這里要說一下Oracle，用Oracle來類比在ORACLE 12C之前，實例與數(shù)據(jù)庫是一對一，也就是說一個實例只能有一個數(shù)據(jù)庫，不像MySQL和SQL Server一個實例可以有多個數(shù)據(jù)庫，并且可以隨意跨庫查詢而PGSQL不能跨庫查詢的原因也是這樣，PGSQL允許建多個數(shù)據(jù)庫，跟ORACLE類比就是有多個實例（之前說的實例與數(shù)據(jù)庫是一對一）一個數(shù)據(jù)庫相當(dāng)于一個實例，因為PGSQL允許有多個實例，所以PGSQL單實例不叫一個實例，叫集簇（cluster），集簇這個概念可以查閱PGSQL的相關(guān)資料PGSQL里面一個實例/數(shù)據(jù)庫下面的schema相當(dāng)于數(shù)據(jù)庫，所以這個schema的概念對應(yīng)MySQL的database注意點(diǎn)：正因為是一個數(shù)據(jù)庫相當(dāng)于一個實例，PGSQL允許有多個實例/數(shù)據(jù)庫，所以數(shù)據(jù)庫之間是互相邏輯隔離的，導(dǎo)致的問題是，不能一次對一個PGSQL集簇下面的所有數(shù)據(jù)庫做操作必須要逐個逐個數(shù)據(jù)庫去操作，例如上面說到的安裝pg_stat_statements插件，如果您需要在PGSQL集簇下面的所有數(shù)據(jù)庫都做性能收集的話，需要逐個數(shù)據(jù)庫去執(zhí)行加載命令又例如跨庫查詢需要dblink插件或fdw插件，兩個數(shù)據(jù)庫之間做查詢相當(dāng)于兩個實例之間做查詢，已經(jīng)跨越了實例了，所以需要dblink插件或fdw插件，所以道理非常簡單權(quán)限操作也是一樣逐個數(shù)據(jù)庫去操作，還有一個就是PGSQL雖然像SQL Server的權(quán)限層次結(jié)構(gòu)db=》schema=》object，但是實際會比SQL Server要復(fù)雜一些，還有就是新建的表還要另外授權(quán)在PGSQL里面，角色和用戶是一樣的，對新手用戶來說有時候會傻傻分不清，也不知道怎么去用角色，所以PGSQL在權(quán)限設(shè)計這一塊確實比較坑爹 MySQL使用mysql庫下面的5個權(quán)限表去做權(quán)限映射，簡單清晰，**問題是缺少權(quán)限角色user表db表host表tables_priv表columns_priv表1. 架構(gòu)對比Mysql：多線程PostgreSql：多進(jìn)程多線程架構(gòu)和多進(jìn)程架構(gòu)之間沒有**的好壞，例如oracle在unix上是多進(jìn)程架構(gòu)，在windows上是多線程架構(gòu)。

2. 對存儲過程及事務(wù)的支持能力MySql對于無事務(wù)的MyISAM表，采用表鎖定，一個長時間運(yùn)行的查詢很可能會長時間的阻礙，而PostgreSQL不會尊在這種問題。PostgreSQL支持存儲過程，要比MySql好，具備本地緩存執(zhí)行**的能力。 3. 穩(wěn)定性及性能高并發(fā)讀寫，負(fù)載逼近極限下，PG的性能指標(biāo)仍可以維持雙曲線甚至對數(shù)曲線，到頂峰之后不再下降，而 MySql 明顯出現(xiàn)一個波峰后下滑（5.5版本后Mysql企業(yè)版有優(yōu)化，需要付費(fèi)）MySql的InnoDB引擎，可以充分優(yōu)化利用系統(tǒng)的所有內(nèi)存，超大內(nèi)存下PG對內(nèi)存使用的不那么充分（需要根據(jù)內(nèi)存情況合理分配）。 4. 高可用InnoDB的基于回滾實現(xiàn)的 MVCC 機(jī)制，對于 PG 新老數(shù)據(jù)一起放的基于 XID 的 MVCC機(jī)制，是占優(yōu)的。

新老數(shù)據(jù)一起存放，需要定時觸發(fā) VACUUM，會帶來多余的 IO 和數(shù)據(jù)庫對象加鎖開銷，引起數(shù)據(jù)庫整理的并發(fā)能力下降。而且 VACUUM 清理不及時，還可能會引發(fā)數(shù)據(jù)膨脹 5. 數(shù)據(jù)同步方式：Mysql到現(xiàn)在也是異步**，pgsql可以做到同步、異步、半同步**。Mysql同步是基于binlog**，屬于邏輯**，類似于oracle golden gate，是基于stream的**，做到同步很困難，這種方式更加適合異步**;Pgsql的同是基于wal，屬于物理**，可以做到同步**。

同時，pgsql還提供stream**。Mysql的**可以用多級從庫，但是在9.2之前，PgSql不能用從庫帶從庫。Pgsql的主從**屬于物理**，相對于Mysql基于binlog的邏輯**，數(shù)據(jù)的一致性更加可靠，**性能更高，對主機(jī)性能的影響也更小。

6. 權(quán)限控制對比MySql允許自定義一套不同的數(shù)據(jù)級、表級和列的權(quán)限，運(yùn)行指定基于主機(jī)的權(quán)限Mysql的merge表提供了 一個獨(dú)特管理多個表的方法。myisampack可以對只讀表進(jìn)行壓縮，以后仍然可以直接訪問該表中的行。 7. SQL語句支持能力PG有極其強(qiáng)悍的 SQL 編程能力（9.x 圖靈完備，支持遞歸?。?，有非常豐富的統(tǒng)計函數(shù)和統(tǒng)計語法支持，例如分析函數(shù)（Oracle的叫法，PG里叫window函數(shù)）支持用多種語言來寫存儲過程，對于R的支持也很好。這一點(diǎn)上Mysql就差的很遠(yuǎn)，很多分析功能都不支持。

PgSql對表名大小寫的處理，只有在Sql語句中，表明加雙引號，才區(qū)分大小寫。在Sql的標(biāo)準(zhǔn)實現(xiàn)上要比Mysql完善，而且功能實現(xiàn)比較嚴(yán)謹(jǐn)。對表連接支持比較完整，優(yōu)化器的功能比較完整，支持的索引類型很多，復(fù)雜查詢能力較強(qiáng)。

Mysql采用索引組織表，這種存儲方式非常適合基于主鍵匹配的查詢、刪改操作，但是對表結(jié)果設(shè)計存在約束;Mysql的Join操作的性能非常的差，只支持Nest Join，所以一旦數(shù)據(jù)量大，性能就非常的差。PostgresSQL除了支持 Nest Join 和 Sort Merge Join，PostgreSQL還支持正則表達(dá)式查詢，MySql不支持。 8. 數(shù)據(jù)類型支持能力PostgreSQL可以更方便的使用UDF（用戶定義函數(shù)）進(jìn)行擴(kuò)展。有豐富的幾何類型，實際上不止**類型，PG有大量的字典、數(shù)組、bitmap等數(shù)據(jù)類型，因此PG多年來在 GIS 領(lǐng)域處于優(yōu)勢地位。

相比之下Mysql就差很多，instagram就是因為PG的空間數(shù)據(jù)擴(kuò)展 PostGIS遠(yuǎn)遠(yuǎn)強(qiáng)于 MySql的 my spatial 而采用 PgSql的。Mysql中的空間數(shù)據(jù)類型有4種，分別是 CEOMETRY、POINT、LINESTRING、POLYGON，其空間索引只能在存儲引擎為 MyiSam的表中創(chuàng)建，用SPATIAL關(guān)鍵字進(jìn)行擴(kuò)展，使得能夠用于創(chuàng)建正規(guī)索引類型的語法創(chuàng)建空間索引。創(chuàng)建空間索引的列，必須將其聲明為NOT NULL。

不同的存儲親情有差別。MyISAM和InnoDB 都支持 spat。

pgsql添加limit變慢

您好，您的問題很有意思，PostgreSQL添加limit變慢的原因可能有很多，但是最常見的原因是因為您使用了不正確的索引。如果您的查詢中使用了索引，但是查詢的結(jié)果集仍然很大，那么PostgreSQL就會在查詢完成后再執(zhí)行l(wèi)imit操作，這樣就會導(dǎo)致查詢變慢。

另外，如果您的查詢中沒有使用索引，那么PostgreSQL就會掃描整個表，然后再執(zhí)行l(wèi)imit操作，這也會導(dǎo)致查詢變慢。

因此，為了解決PostgreSQL添加limit變慢的問題，您需要檢查您的查詢是否使用了正確的索引，如果沒有，您可以嘗試創(chuàng)建索引來提高查詢性能。

有2個數(shù)據(jù)庫，怎么實現(xiàn)數(shù)據(jù)同步

在pgsql的數(shù)據(jù)庫中將要同步的表建立一個一模一樣的同步表。然后建觸發(fā)器，將進(jìn)入源表中的數(shù)據(jù)觸發(fā)到同步表中。

然后做一個windows服務(wù)掃描這個同步表并且將同步表中的數(shù)據(jù)同步到mysql或access中。

如果是整個數(shù)據(jù)庫中有許多表要進(jìn)行同步。則建立一個共用的同步表，結(jié)構(gòu)非常簡單。字段如下：主鍵(f_id),源表名(f_tablename),主鍵字段名(f_keyname),主鍵值(f_keyvalue),操作標(biāo)識(f_operflag i:插入u:更新 d:刪除),時間字段(f_time).將要同步的表都建立觸發(fā)器，只要有增、改、刪都觸發(fā)到這個表里。這樣你只掃描這個共用的同步表，根據(jù)這些字段值完全可以實現(xiàn)數(shù)據(jù)的同步。

postgresql和mysql的區(qū)別

特性 MySQL PostgreSQL 實例 通過執(zhí)行 MySQL 命令（mysqld）啟動實例。一個實例可以管理一個或多個數(shù)據(jù)庫。

一臺服務(wù)器可以運(yùn)行多個 mysqld 實例。

一個實例管理器可以監(jiān)視 mysqld 的各個實例。 通過執(zhí)行 Postmaster 進(jìn)程（pg_ctl）啟動實例。一個實例可以管理一個或多個數(shù)據(jù)庫，這些數(shù)據(jù)庫組成一個集群。集群是磁盤上的一個區(qū)域，這個區(qū)域在安裝時初始化并由一個目錄組成，所有數(shù)據(jù)都存儲在這個目錄中。

使用 initdb 創(chuàng)建**個數(shù)據(jù)庫。一臺機(jī)器上可以啟動多個實例。 數(shù)據(jù)庫 數(shù)據(jù)庫是命名的對象**，是與實例中的其他數(shù)據(jù)庫分離的實體。

一個 MySQL 實例中的所有數(shù)據(jù)庫共享同一個系統(tǒng)編目。 數(shù)據(jù)庫是命名的對象**，每個數(shù)據(jù)庫是與其他數(shù)據(jù)庫分離的實體。每個數(shù)據(jù)庫有自己的系統(tǒng)編目，但是所有數(shù)據(jù)庫共享 pg_databases。

數(shù)據(jù)緩沖區(qū) 通過 innodb_buffer_pool_size 配置參數(shù)設(shè)置數(shù)據(jù)緩沖區(qū)。這個參數(shù)是內(nèi)存緩沖區(qū)的字節(jié)數(shù)，InnoDB 使用這個緩沖區(qū)來緩存表的數(shù)據(jù)和索引。在專用的數(shù)據(jù)庫服務(wù)器上，這個參數(shù)**可以設(shè)置為機(jī)器物理內(nèi)存量的 80%。

Shared_buffers 緩存。在默認(rèn)情況下分配 64 個緩沖區(qū)。默認(rèn)的塊大小是 8K。可以通過設(shè)置 postgresql.conf 文件中的 shared_buffers 參數(shù)來更新緩沖區(qū)緩存。

數(shù)據(jù)庫連接 客戶機(jī)使用 CONNECT 或 USE 語句連接數(shù)據(jù)庫，這時要指定數(shù)據(jù)庫名，還可以指定用戶 id 和密碼。使用角色管理數(shù)據(jù)庫中的用戶和用戶組。 客戶機(jī)使用 connect 語句連接數(shù)據(jù)庫，這時要指定數(shù)據(jù)庫名，還可以指定用戶 id 和密碼。使用角色管理數(shù)據(jù)庫中的用戶和用戶組。

身份驗證 MySQL 在數(shù)據(jù)庫級管理身份驗證。 基本只支持密碼認(rèn)證。 PostgreSQL 支持豐富的百科認(rèn)證方法：信任認(rèn)證、口令認(rèn)證、Kerberos 認(rèn)證、基于 Ident 的認(rèn)證、LDAP 認(rèn)證、PAM 認(rèn)證 加密 可以在表級指定密碼來對數(shù)據(jù)進(jìn)行加密。還可以使用 AES_ENCRYPT 和 AES_DECRYPT 函數(shù)對列數(shù)據(jù)進(jìn)行加密和解密。

可以通過 SSL 連接實現(xiàn)**加密。 可以使用 pgcrypto 庫中的函數(shù)對列進(jìn)行加密/解密?？梢酝ㄟ^ SSL 連接實現(xiàn)**加密。

審計 可以對 querylog 執(zhí)行 grep。 可以在表上使用 PL/pgSQL 觸發(fā)器來進(jìn)行審計。 查詢解釋 使用 EXPLAIN 命令查看查詢的解釋**。

使用 EXPLAIN 命令查看查詢的解釋**。 備份、恢復(fù)和日志 InnoDB 使用寫前（write-ahead）日志記錄。支持**和離線完全備份以及崩潰和事務(wù)恢復(fù)。需要第三方軟件才能支持熱備份。

在數(shù)據(jù)目錄的一個子目錄中維護(hù)寫前日志。支持**和離線完全備份以及崩潰、時間點(diǎn)和事務(wù)恢復(fù)。 可以支持熱備份。

JDBC 驅(qū)動程序 可以從 參考資料 下載 JDBC 驅(qū)動程序。 可以從 參考資料 下載 JDBC 驅(qū)動程序。 表類型 取決于存儲引擎。例如，NDB 存儲引擎支持分區(qū)表，內(nèi)存引擎支持內(nèi)存表。

支持臨時表、常規(guī)表以及范圍和列表類型的分區(qū)表。不支持哈希分區(qū)表。 由于PostgreSQL的表分區(qū)是通過表繼承和規(guī)則系統(tǒng)完成了，所以可以實現(xiàn)更復(fù)雜的分區(qū)方式。

索引類型 取決于存儲引擎。MyISAM：BTREE，InnoDB：BTREE。 支。