【学习资料】第10期数据库选型思考(PostgreSQL,MySQL,Oracle)

背景

https://www.enterprisedb.com/blog/postgresql-vs-mysql-360-degree-comparison-syntax-performance-scalability-and-features?mkt_tok=eyJpIjoiTVRZMVlqRXlOVFkzWldabSIsInQiOiJBZnQwRmExdzl5VlhWTktBenpDa211TzV6MnJXUGwrTHdNY20yTEw4VnpYUzhzVlZSd2ZLeGxRa1NjRkxlaXJob3ZQNkp1MGZub0gyMU10WDRySE5yRnpVVUFhVVlyNnNzSitGK1oxNlJmZnVYdEtzeXduRzBcL0FHa0dhRVNjdDAifQ%3D%3D

数据库选型是一件很大的事情，也是一件很头疼的事情。

很多企业并没有数据库的选型标准，或者并不了解业务需要什么样的数据库。

很多企业的数据库是开发说了算，熟悉什么就用什么，很多选型失误，导致后期非常尴尬的局面。

那么数据库选型要注意什么呢？

列举一些例子，取自如下文档

《数据库选型的关键指标》

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E9%80%89%E5%9E%8B%E7%BB%B4%E5%BA%A6 选型维度

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E5%8A%9F%E8%83%BD%E5%B7%AE%E5%BC%82 功能差异

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

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)

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

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

5. 哈希聚合

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

6. 事务间共享事务快照

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多列展开式B树》

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E7%BA%A6%E6%9D%9F 约束

1. foreign key

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

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

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 约束

4. exclusion 约束

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E6%98%93%E7%94%A8%E6%80%A7 易用性

1. 表空间

2. alter 列值转表达式

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

http://www.postgresql.org/docs/9.5/static/sql-altertable.html https://yq.aliyun.com/articles/30470

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

PostgreSQL少量操作需要重组

vacuum full, cluster, 修改字段数据类型, (修改长度不需要重组表)

4. 分区表

《PostgreSQL 9.5+ 高效分区表实现 - pg_pathman》《PostgreSQL 10.0 内置分区表》

5. 物化视图

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

6. 物化视图增量刷新

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

7. 表继承关系

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

8. 使用 like 建结构类似的表

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

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E5%BC%80%E5%8F%91%E5%8A%9F%E8%83%BD 开发功能

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. 服务端绑定变量

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

5. savepoint

6. 异步消息

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 暂不支持数组)

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E7%B1%BB%E5%9E%8B 类型

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

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E7%B4%A2%E5%BC%95%E6%94%AF%E6%8C%81 索引支持

1. 索引方法

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

MySQL 支持 btree, gis类型索引

2. 规则表达式、前后模糊查询，支持索引检索

PostgreSQL 支持

3. 数组支持索引

4. 全文检索支持索引

5. 索引扩展功能

5.1 PostgreSQL 支持表达式索引，部分索引，联合索引

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

5.3 自定义索引访问方法(bloom, rum, ......)

PostgreSQL支持

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E5%A4%9A%E5%BC%95%E6%93%8E%E5%92%8C%E5%A4%96%E9%83%A8%E6%BA%90%E6%94%AF%E6%8C%81 多引擎和外部源支持

1. 多引擎支持

内置heap, 通过插件实现内存表, 列存储, 压缩存储, 流式存储, 非关系存储等。

MyISAM, innodb, ...

2. 外部表

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

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

3. dblink

4. 内存表

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

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

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E5%AE%89%E5%85%A8 安全

1. 数据加密

PostgreSQL 支持加密数据类型，可选GPG加密算法

2. 认证方法

PostgreSQL 支持密码、LDAP、AD、GSSAPI、SSPI、Ident、Peer、RADIUS、PAM、签名认证

3. 数据传输加密

4. 行安全策略

5. 数据库内部支持libselinux接口, (美国国家安全局制定的安全加强标准)

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E4%BC%98%E5%8C%96%E5%99%A8 优化器

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

1. GPU 并行计算支持

2. 遗传优化器算法

PostgreSQL 支持CBO、CRO、遗传算法

3. HINT PLAN

4. CPU 并行计算

PostgreSQL 支持 (线性性能提升)

5. 自定义成本因子

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E7%BC%96%E7%A8%8B%E6%89%A9%E5%B1%95 编程扩展

1. 是否支持采样查询

2. 是否支持扩展采样算法

3. 自定义数据类型

4. 自定义索引方法

5. 字符集自动转换, C扩展接口

6. 自定义聚合

7. 自定义窗口

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E6%89%A9%E5%B1%95%E8%83%BD%E5%8A%9B 扩展能力

1. 类型扩展，操作符扩展，函数扩展，索引方法扩展，索引扩展，

2. C触发器函数， C事件触发器函数

3. 函数语言扩展

PostgreSQL 支持扩展函数支持接口, erlang, ....

4. 机器学习库

5. 流式计算

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E6%80%A7%E8%83%BD 性能

《数据库界的华山论剑 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的百宝箱》

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E6%9C%8D%E5%8A%A1%E7%AB%AF%E7%BC%96%E7%A8%8B%E8%AF%AD%E8%A8%80 服务端编程语言

1. 函数(过程)语言

PostgreSQL支持几乎所有的地球编程语言，你可以用他们在数据库中编写UDF

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

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#scale-out scale out

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

支持聚合算子下推

支持WHERE条件下推

支持JOIN下推

支持SORT下推

支持SELECT 子句下推

支持跨数据源JOIN

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#scale-up%E8%83%BD%E5%8A%9B scale up能力

PostgreSQL

资源管控能力强。

单实例可以充分发挥HPC的性能，有多少资源就能使用多少资源。

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E5%8F%AF%E7%94%A8%E6%80%A7%E5%8F%AF%E9%9D%A0%E6%80%A7 可用性、可靠性

PIRT，任意时间点恢复；FPW；

物理、逻辑级流式复制；金融级多副本；

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E6%8A%80%E6%9C%AF%E5%82%A8%E5%A4%87 技术储备

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E7%A4%BE%E5%8C%BA%E7%94%9F%E6%80%81 社区、生态

社区状态

单一开源分支，社区力量较集中。全球有1000名以上的内核研发人员。

社区研发由核心组员和committer组成，持续5年以上对社区版本有贡献内核研发人员超过50位。

社区核心人员分别来自数据库厂商，数据库支持和服务公司，数据库最终用户的公司，形成了一个非常好的力量均衡。

社区活跃度

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

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E5%BA%94%E7%94%A8%E5%9C%BA%E6%99%AF%E6%A1%88%E4%BE%8B 应用场景、案例

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E5%BC%80%E6%BA%90%E8%AE%B8%E5%8F%AF 开源许可

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E5%85%A8%E7%90%83%E5%8F%91%E5%B1%95%E5%89%8D%E6%99%AF 全球发展前景

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#%E8%AF%A6%E7%BB%86%E6%96%87%E6%A1%A3%E5%8F%82%E8%80%83 详细文档参考

《PostgreSQL 生态；原理、案例、开发实践、管理实践、学习资料、视频 - 珍藏级》 https://www.2ndquadrant.com/en/blog/postgresql-is-the-worlds-best-database/

https://github.com/digoal/blog/blob/master/201702/20170208_03.md#postgresql-%E8%AE%B8%E6%84%BF%E9%93%BE%E6%8E%A5 https://github.com/digoal/blog/issues/76