資料庫選型思考(PostgreSQL,MySQL,Oracle)

背景

https://www.enterprisedb.com/blog/postgresql-vs-mysql-360-degree-comparison-syntax-performance-scalability-and-features

資料庫選型是一件很大的事情，也是一件很頭疼的事情，

很多企業并沒有資料庫的選型標準，或者并不了解業務需要什么樣的資料庫，

很多企業的資料庫是開發說了算，熟悉什么就用什么，很多選型失誤，導致后期非常尷尬的局面，

那么資料庫選型要注意什么呢？

列舉一些例子，取自如下檔案

《資料庫選型的關鍵指標》

選型維度

功能差異

1. 遞回查詢, connect by, 樹形查詢

PostgreSQL 通過(with 或 tablefunc支持)支持例子

https://yq.aliyun.com/articles/240

http://www.postgresql.org/docs/9.5/static/tablefunc.html

2. 視窗查詢, window over

PostgreSQL 支持例子

http://blog.163.com/digoal@126/blog/static/16387704020137154137930

http://blog.163.com/digoal@126/blog/static/16387704020121024102312302/

http://blog.163.com/digoal@126/blog/static/16387704020124239390354/

http://www.postgresql.org/docs/9.5/static/functions-window.html

3. rollup, grouping sets, cube

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/queries-table-expressions.html#QUERIES-GROUPING-SETS

http://blog.163.com/digoal@126/blog/static/16387704020154269591874/

4. 高級聚合(json,陣列,相關性,標準差(采樣,全域),截距,斜率,方差(采樣,全域),mode,percentile_cont,distc,rank,dense_rank,percent_rank,cume_dist,grouping)

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/functions-aggregate.html

http://blog.163.com/digoal@126/blog/static/1638770402015224124337/

http://blog.163.com/digoal@126/blog/static/1638770402015379286873/

http://blog.163.com/digoal@126/blog/static/16387704020153713222764

4. hash join, merge join, nestloop join

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/xoper-optimization.html

http://www.postgresql.org/docs/9.5/static/planner-optimizer.html

5. 哈希聚合

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/runtime-config-query.html#RUNTIME-CONFIG-QUERY-ENABLE

6. 事務間共享事務快照

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION

http://www.postgresql.org/docs/9.5/static/sql-set-transaction.html

http://www.postgresql.org/docs/9.5/static/app-pgdump.html

http://blog.163.com/digoal@126/blog/static/163877040201326829943/

http://blog.163.com/digoal@126/blog/static/163877040201241134721101/

7. 展開式索引(支持多列任意組合查詢)

PostgreSQL 支持例子

《寶劍贈英雄 - 任意組合欄位等效查詢, 探探PostgreSQL多列展開式B樹》

約束

1. foreign key

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/ddl-constraints.html

2. for no key update, for key share 粒度鎖

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/explicit-locking.html#LOCKING-ROWS

http://blog.163.com/digoal@126/blog/static/16387704020130249109133/

http://blog.163.com/digoal@126/blog/static/16387704020130305109687/

3. check 約束

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/ddl-constraints.html

4. exclusion 約束

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/ddl-constraints.html

易用性

1. 表空間

2. alter 列值轉運算式

(alter table alter column c1 type newtype using (expression(...)) )

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/sql-altertable.html

https://yq.aliyun.com/articles/30470

3. alter table 需要重組表的操作

PostgreSQL少量操作需要重組

vacuum full, cluster, 修改欄位資料型別, (修改長度不需要重組表)

http://www.postgresql.org/docs/9.5/static/sql-altertable.html

4. 磁區表

《PostgreSQL 9.5+ 高效磁區表實作 - pg_pathman》

《PostgreSQL 10.0 內置磁區表》

5. 物化視圖

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/sql-creatematerializedview.html

6. 物化視圖增量重繪

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/sql-refreshmaterializedview.html

7. 表繼承關系

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/tutorial-inheritance.html

8. 使用 like 建結構類似的表

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/sql-createtable.html

開發功能

1. 客戶端開發語言支持

C, java, python, ...

2. 函式

回傳 void, 單行，SRF，事件觸發器(MySQL 不支持)，觸發器

例子

http://blog.163.com/digoal@126/blog/static/16387704020132131361949/

http://www.postgresql.org/docs/9.5/static/event-triggers.html

3. 2PC

4. 服務端系結變數

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/sql-prepare.html

5. savepoint

6. 異步訊息

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/sql-notify.html

http://www.postgresql.org/docs/9.5/static/sql-listen.html

7. 游標

陣列FOR回圈，query FOR回圈，游標FOR回圈

PostgreSQL 全面支持例子

http://www.postgresql.org/docs/9.5/static/plpgsql-control-structures.html

http://www.postgresql.org/docs/9.5/static/plpgsql-cursors.html

(MySQL 暫不支持陣列)

型別

1. 資料型別

1.1 PostgreSQL

高精度numeric, 浮點, 自增序列，貨幣，位元組流，時間，日期，時間戳，布爾，列舉，平面幾何，立體幾何，多維幾何，地球，PostGIS，網路，位元流，全文檢索，UUID，XML，JSON，陣列，復合型別，域型別，范圍，樹型別，化學型別，基因序列，FDW, 大物件, 影像

1.2 MySQL

數字，時間，字串，簡單的GIS，JSON

2. 支持索引的資料型別

2.1 PostgreSQL

高精度numeric, 浮點, 自增序列，貨幣，位元組流，時間，日期，時間戳，布爾，列舉，平面幾何，立體幾何，多維幾何，地球，PostGIS，網路，位元流，全文檢索，UUID，XML，JSON，陣列，復合型別，域型別，范圍，樹型別，化學，基因序列

2.2 MySQL

數字，字串，位元流，時間，全文檢索，GIS

索引支持

1. 索引方法

PostgreSQL 支持 btree, hash, gist, sp-gist, gin, brin , bloom , rum 索引

MySQL 支持 btree, gis型別索引

2. 規則運算式、前后模糊查詢，支持索引檢索

PostgreSQL 支持

3. 陣列支持索引

PostgreSQL 支持

4. 全文檢索支持索引

5. 索引擴展功能

5.1 PostgreSQL 支持 運算式索引，部分索引，聯合索引

5.2 PostgreSQL 支持影像相似度、文本相似度搜索

5.3 自定義索引訪問方法(bloom, rum, ......)

PostgreSQL支持

多引擎和外部源支持

1. 多引擎支持

1.1 PostgreSQL

內置heap, 通過插件實作記憶體表, 列存盤, 壓縮存盤, 流式存盤, 非關系存盤等，

1.2 MySQL

MyISAM, innodb, ...

2. 外部表

PostgreSQL支持任意外部資料源, (例如jdbc, file, odbc, oracle, mysql, db2, redis, mongo, ES, hadoop......)

https://wiki.postgresql.org/wiki/Fdw

3. dblink

PostgreSQL 支持

4. 記憶體表

PostgreSQL 通過外部表支持，或者通過插件支持

5. ES(彈性搜索引擎) 引擎

PostgreSQL 支持

安全

1. 資料加密

PostgreSQL 支持加密資料型別，可選GPG加密演算法

2. 認證方法

PostgreSQL 支持 密碼、LDAP、AD、GSSAPI、SSPI、Ident、Peer、RADIUS、PAM、簽名認證

3. 資料傳輸加密

PostgreSQL 支持

4. 行安全策略

PostgreSQL 支持

5. 資料庫內部支持libselinux介面, (美國國家安全域制定的安全加強標準)

PostgreSQL 支持

優化器

http://www.postgresql.org/docs/9.5/static/runtime-config-query.html

1. GPU 并行計算支持

PostgreSQL 支持

2. 遺傳優化器演算法

PostgreSQL 支持CBO、CRO、遺傳演算法

3. HINT PLAN

4. CPU 并行計算

PostgreSQL 支持 (線性性能提升)

5. 自定義成本因子

PostgreSQL 支持

編程擴展

1. 是否支持采樣查詢

PostgreSQL 支持

2. 是否支持擴展采樣演算法

PostgreSQL 支持

3. 自定義資料型別

PostgreSQL 支持

4. 自定義索引方法

PostgreSQL 支持

5. 字符集自動轉換, C擴展介面

PostgreSQL 支持

6. 自定義聚合

PostgreSQL 支持

7. 自定義視窗

PostgreSQL 支持

擴展能力

1. 型別擴展，運算子擴展，函式擴展，索引方法擴展，索引擴展，

PostgreSQL 支持

2. C觸發器函式， C事件觸發器函式

PostgreSQL 支持

3. 函式語言擴展

PostgreSQL 支持擴展函式支持介面, erlang, ....

4. 機器學習庫

PostgreSQL 支持

5. 流式計算

PostgreSQL 支持

性能

《資料庫界的華山論劍 tpc.org》

《facebook linkbench 測驗PostgreSQL社交關系圖譜場景性能》

《PostgreSQL 主機性能測驗方法 - 單機多實體》

《PostgreSQL 主機性能測驗方法 - 單機單實體》

《PostgreSQL 原始碼性能診斷(perf profiling)指南》

《"物聯網"流式處理應用 - 用PostgreSQL實時處理(萬億每天)》

《PostgreSQL 百億地理位置資料 近鄰查詢性能》

《PostgreSQL 百億資料 秒級響應 正則及模糊查詢》

《PostgreSQL 1000億資料量 正則匹配 速度與激情》

《恭迎萬億級營銷(圈人)瀟灑的邁入毫秒時代 - 萬億user_tags級實時推薦系統資料庫設計》

《從難纏的模糊查詢聊開 - PostgreSQL獨門絕招之一 GIN , GiST , SP-GiST , RUM 索引原理與技術背景》

《分析加速引擎黑科技 - LLVM、列存、多核并行、算子復用 大聯姻 - 一起來開啟PostgreSQL的百寶箱》

服務端編程語言

1. 函式(程序)語言

PostgreSQL支持幾乎所有的地球編程語言，你可以用他們在資料庫中撰寫UDF

plpgsql, sql, c, c++, java, javascript, R, python, perl, php, tcl, ruby, lua, ...

scale out

1. PostgreSQL完全支持以下下推功能

支持聚合算子下推

支持WHERE條件下推

支持JOIN下推

支持SORT下推

支持SELECT 子句下推

支持跨資料源JOIN

scale up能力

PostgreSQL

資源管控能力強，

單實體可以充分發揮HPC的性能，有多少資源就能使用多少資源，

可用性、可靠性

PostgreSQL

PIRT，任意時間點恢復；FPW；

物理、邏輯級流式復制；金融級多副本；

技術儲備

社區、生態

社區狀態

PostgreSQL

單一開源分支，社區力量較集中，全球有1000名以上的內核研發人員，

社區研發由核心組員和committer組成，持續5年以上對社區版本有貢獻內核研發人員超過50位，

社區核心人員分別來自資料庫廠商，資料庫支持和服務公司，資料庫最終用戶的公司，形成了一個非常好的力量均衡，

社區活躍度

PostgreSQL

非常活躍(see git.postgresql.org, github.com/postgres)

學習成本

維護成本

平臺兼容性

代碼成熟度

應用場景、案例

開源許可

全球發展前景

詳細檔案參考

《資料庫選型的關鍵指標》

《PostgreSQL 生態；原理、案例、開發實踐、管理實踐、學習資料、視頻 - 珍藏級》

https://www.2ndquadrant.com/en/blog/postgresql-is-the-worlds-best-database/

 作者丨digoal

本文來自博客園，作者：古道輕風，轉載請注明原文鏈接：https://www.cnblogs.com/88223100/p/Thoughts-on-database-selection-PostgreSQL_MySQL_Oracle.html

標籤：PostgreSQL

上一篇：PostgreSQL DBA最常用SQL

下一篇：使用BCP + Polybase 實作本地資料遷移到Azure DB