postgre与mysql区别

SQL兼容性

PostgreSQL 9.5
  兼容 SQL:2011 子集
  http://www.postgresql.org/docs/9.5/static/features-sql-standard.html

MySQL 5.7
  兼容 SQL:1999 子集

功能差异 - 高级SQL

递归查询, connect by, 树形查询  
  PostgreSQL 通过(with 或 tablefunc支持)支持，MySQL 不支持
    例子
    https://yq.aliyun.com/articles/240
    http://www.postgresql.org/docs/9.5/static/tablefunc.html 
窗口查询, window over 
  PostgreSQL 支持，MySQL 不支持
    例子
    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 
rollup, grouping sets, cube
  PostgreSQL 支持，MySQL 不支持
    例子
    http://www.postgresql.org/docs/9.5/static/queries-table-expressions.html#QUERIES-GROUPING-SETS 
    http://blog.163.com/digoal@126/blog/static/16387704020154269591874/ 
高级聚合(json,数组,相关性,标准差(采样,全局),截距,斜率,方差(采样,全局),mode,percentile_cont,distc,rank,dense_rank,percent_rank,cume_dist,grouping)
  PostgreSQL 支持，MySQL 不支持
    例子 
    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 

hash join
  PostgreSQL 支持，MySQL 不支持
merge join
  PostgreSQL 支持，MySQL 不支持
nestloop join
  都支持
    例子
    http://www.postgresql.org/docs/9.5/static/xoper-optimization.html
    http://www.postgresql.org/docs/9.5/static/planner-optimizer.html 
哈希聚合
  PostgreSQL 支持, MySQL 不支持
    例子
    http://www.postgresql.org/docs/9.5/static/runtime-config-query.html#RUNTIME-CONFIG-QUERY-ENABLE 
事务间共享事务快照
  PostgreSQL 支持，MySQL 不支持
    例子
    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/

功能差异 - 约束

foreign key
  PostgreSQL 支持, MySQL 仅 innodb 引擎支持FK
    例子
    http://www.postgresql.org/docs/9.5/static/ddl-constraints.html 
for no key update, for key share 粒度锁
  PostgreSQL 支持, MySQL 不支持
  例子
    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/ 
check 约束
  PostgreSQL 支持, MySQL不支持(仅支持语法，实际不生效，不严谨)
    例子
    http://www.postgresql.org/docs/9.5/static/ddl-constraints.html 
exclusion 约束
  PostgreSQL 支持, MySQL不支持
    例子
    http://www.postgresql.org/docs/9.5/static/ddl-constraints.html

功能差异 - 易用性

表空间
  都支持
alter 列值转表达式(alter table alter column c1 type newtype using (expression(...)) )
  PostgreSQL 支持, MySQL 不支持
    例子
    http://www.postgresql.org/docs/9.5/static/sql-altertable.html 
    https://yq.aliyun.com/articles/30470 
alter table 需要重组表的操作
  MySQL ( innodb ) 
    optimize table, 添加列，删除列, 重排, 修改row_format, key_block_size, mark column null, not null, 修改字段长度, 修改字段数据类型, 添加主键, 删除主键, 转换字符集, 指定字符集, rebuild table
    http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html 
  PostgreSQL
    vacuum full, cluster, 修改字段数据类型, (修改长度不需要重组表)
    http://www.postgresql.org/docs/9.5/static/sql-altertable.html
分区表
  都支持
  PostgreSQL 通过继承支持分区表, 阿里RDS PostgreSQL支持分区表语法  
    例子
    http://www.postgresql.org/docs/9.5/static/ddl-partitioning.html
    https://yq.aliyun.com/articles/113 

物化视图
  PostgreSQL 支持, MySQL 暂不支持
    例子
    http://www.postgresql.org/docs/9.5/static/sql-creatematerializedview.html 
物化视图增量刷新
  PostgreSQL 支持, MySQL 暂不支持
    例子
    http://www.postgresql.org/docs/9.5/static/sql-refreshmaterializedview.html 
表继承关系
  PostgreSQL 支持, MySQL 暂不支持
    例子
    http://www.postgresql.org/docs/9.5/static/tutorial-inheritance.html 
使用 like 建结构类似的表
  PostgreSQL 支持, MySQL 暂不支持
    例子
    http://www.postgresql.org/docs/9.5/static/sql-createtable.html

功能差异 - 开发功能

客户端开发语言支持
  C, java, python, ...
函数
  void, 单行，SRF，事件触发器(MySQL 不支持)，触发器
    例子
    http://blog.163.com/digoal@126/blog/static/16387704020132131361949/ 
    http://www.postgresql.org/docs/9.5/static/event-triggers.html 
2PC
  PostgreSQL 支持, MySQL 仅innodb支持
服务端绑定变量
  PostgreSQL 支持, MySQL 不支持
    例子
    http://www.postgresql.org/docs/9.5/static/sql-prepare.html
savepoint
  PostgreSQL 支持, MySQL 仅innodb支持

异步消息
  PostgreSQL 支持, MySQL 不支持
    例子
    http://www.postgresql.org/docs/9.5/static/sql-notify.html 
    http://www.postgresql.org/docs/9.5/static/sql-listen.html
游标
  都支持
数组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 不支持数组

功能差异 - 类型支持

数据类型
  PostgreSQL 
    高精度numeric, 浮点, 自增序列，货币，字节流，时间，日期，时间戳，布尔，枚举，平面几何，立体几何，多维几何，地球，PostGIS，网络，比特流，全文检索，UUID，XML，JSON，数组，复合类型，域类型，范围，树类型，化学类型，基因序列，FDW, 大对象
  MySQL
    数字，时间，字符串，简单的GIS，JSON
支持索引的数据类型
  PostgreSQL 
    高精度numeric, 浮点, 自增序列，货币，字节流，时间，日期，时间戳，布尔，枚举，平面几何，立体几何，多维几何，地球，PostGIS，网络，比特流，全文检索，UUID，XML，JSON，数组，复合类型，域类型，范围，树类型，化学，基因序列
  MySQL 
    数字，字符串，比特流，时间，全文检索，GIS

功能差异 - 索引支持

索引方法
  PostgreSQL 支持 btree, hash, gist, sp-gist, gin, brin 索引
  MySQL 支持 btree, gis类型索引
规则表达式匹配，支持索引检索
  PostgreSQL 支持
  MySQL 不支持
数组支持索引
  PostgreSQL 支持，MySQL不支持数组
全文检索支持索引
  都支持
索引扩展功能
  PostgreSQL 支持 表达式索引，部分索引，联合索引
  MySQL 支持 联合索引
自定义索引方法
  PostgreSQL支持
  MySQL 不支持

功能差异 - 并发能力

隔离级别
  PostgreSQL 支持 RC, RR, 以及高于SQL 92标准的串行隔离级别
  MySQL 支持 RU，RC，RR，以及SQL 92标准的串行隔离级别
slave库支持RR隔离级别查询
  都支持
锁粒度
  PostgreSQL 支持 行锁，对象锁，页锁，预锁，应用锁，自旋锁，共享锁，排它锁
  MySQL 不支持 应用锁，预锁
死锁检测
  都支持
skip locked, nowait
  PostgreSQL 都支持
  MySQL 不支持， 阿里云RDS MySQL 支持nowait

功能差异 - 多引擎和外部源支持

多引擎支持
  PostgreSQL
    内置heap, 通过插件实现内存表, 列存储, 压缩存储, 流式存储, 非关系存储等。
  MySQL
    MyISAM, innodb, ...
外部表
  PostgreSQL支持任意外部数据源, (例如jdbc, file, odbc, oracle, mysql, db2, redis, mongo, ES, hadoop......)
  MySQL 仅支持csv文件外部表
dblink
  PostgreSQL 支持, MySQL 暂不支持
内存表
  都支持, (PostgreSQL 通过外部表支持)
ES 引擎
  PostgreSQL 支持
  MySQL 不支持

功能差异 - 安全

数据加密
  PostgreSQL 支持加密数据类型，可选GPG加密算法 
  MySQL 需要通过其他手段达到目的
认证方法
  PostgreSQL 支持 密码、LDAP、AD、GSSAPI、SSPI、Ident、Peer、RADIUS、PAM、签名认证
  MySQL 支持 密码 认证
数据传输加密
  都支持
行安全策略
  PostgreSQL 支持, MySQL 暂不支持
数据库内部支持libselinux接口, (美国国家安全局制定的安全加强标准) 
  PostgreSQL 支持, MySQL 暂不支持

功能差异 - 优化器

http://www.postgresql.org/docs/9.5/static/runtime-config-query.html
GPU 并行计算支持
  PostgreSQL 支持, MySQL 不支持
遗传优化器算法
  PostgreSQL 支持CBO、CRO、遗传算法
  MySQL 支持CBO、CRO
HINT PLAN
  都支持
CPU 并行计算
  PostgreSQL 9.6支持(线性性能提升)
  MySQL 不支持
自定义成本因子
  PostgreSQL 支持
  MySQL 不支持

功能差异 - 可用性和可靠性

多master
  都支持
逻辑复制
  都支持
物理复制
  PostgreSQL 支持, MySQL 不支持
级联复制
  都支持
同步复制
  都支持
pasox，设置保护级别
  PostgreSQL 9.6 支持
  MySQL 不支持
主备延迟
  PostgreSQL 不受事务大小限制, 几乎没有延迟(ms以内)
  MySQL延迟和事务大小相关, 长事务会导致巨大主备延迟
在线备份, 增量备份
  都支持
基于REDO的数据库回滚，修复主备时间线错乱
  PostgreSQL 支持, MySQL不支持
任意时间点恢复(事务粒度)
  都支持, MySQL 需要用户自己写程序来支持

功能差异 - 编程扩展能力

是否支持采样查询
  PostgreSQL 支持, MySQL不支持
是否支持扩展采样算法
  PostgreSQL 支持, MySQL不支持
自定义数据类型
  PostgreSQL 支持, MySQL 不支持
自定义索引方法
  PostgreSQL 支持, MySQL 不支持
字符集自动转换, C扩展接口
  PostgreSQL 支持, MySQL 不支持
自定义聚合
  PostgreSQL 支持, MySQL 不支持
自定义窗口
  PostgreSQL 支持, MySQL 不支持

功能差异 - 管理特性

JOB 支持
  都支持
支持数据缓存快照和预热
  PostgreSQL 支持, MySQL不支持
支持数据文件块级别fadvise flag设置
  PostgreSQL 支持, MySQL不支持
性能诊断方法
  PostgreSQL
    explain (analyze, verbose, costs, timing, buffers)
    IO Time
    profile
    probe
    auto_explain
  MySQL
    explain
角色权限继承
  PostgreSQL 支持, MySQL 暂不支持

性能差异

TPC-H(OLAP), 复杂查询 PostgreSQL 优势非常明显
  PostgreSQL 9.6 支持CPU并行
  PostgreSQL 支持GPU插件加速
  PostgreSQL rewrite能力更强
  PostgreSQL 支持hash join, hash agg, merge join
  PostgreSQL btree,hash,gin,gist,sp-gist,brin索引方法
TPC-C(OLTP)  
  请自行测试
TPC-B
  请自行测试

秒杀，模糊查询，地理位置信息查询，范围查询，实时流式计算，批量入库等场景 PostgreSQL 优势非常明显

以下无法给出客观意见，请自行测试，或者下载源码欣赏。

  稳定性
  可靠性
  代码成熟度

平台兼容性

  都很棒

服务端编程语言

函数(过程)语言
  PostgreSQL
    plpgsql, sql, c, c++, java, javascript, R, python, perl, php, tcl, ruby, lua, julia, ...
  MySQL
    sql, 不支持其他扩展编程语言

扩展能力

类型扩展，操作符扩展，函数扩展，索引方法扩展，索引扩展， 
  PostgreSQL 支持, MySQL不支持
C触发器函数， C事件触发器函数
  PostgreSQL 支持, MySQL不支持
函数语言扩展
  PostgreSQL 支持扩展函数支持接口, erlang, .... 
  MySQL 不支持
机器学习库
  PostgreSQL 支持, MySQL 不支持
流式计算
  PostgreSQL 支持, MySQL 不支持

scale up 能力

PostgreSQL 
  资源管控能力强。
  单实例可以充分发挥HPC的性能，有多少资源就能使用多少资源。
  支持GPU并行计算
  支持CPU并行计算
MySQL
  请自行测试

scale out 能力

都支持sharding
PostgreSQL
  pg_shard, oneproxy, plproxy, pg-xc, pg-xl, PostgreSQL 9.6 FDW based shard

# 应用场景
PostgreSQL
  几乎适合任何场景
MySQL
  适合相对较简单的应用场景

社区状态

PostgreSQL
  单一开源分支，社区力量较集中。全球有1000名以上的内核研发人员。
  社区研发由核心组员和committer组成，持续5年以上对社区版本有贡献内核研发人员超过50位。
  社区核心人员分别来自数据库厂商，数据库支持和服务公司，数据库最终用户的公司，形成了一个非常好的力量均衡。
MySQL
  分支非常多， 引擎非常多。

社区活跃度

PostgreSQL
    非常活跃
MySQL
    非常活跃

生态

DBA
  PostgreSQL，国内供不应求
内核研发
  PostgreSQL，国内超过100名内核研发，依旧供不应求
应用开发
  PostgreSQL，SQL兼容性强，开发人员通用
  MySQL，SQL兼容性较弱，企业应用开发人员难接受
客户端驱动
  PostgreSQL，兼容性强
  MySQL，兼容性强
开发框架
  PostgreSQL，兼容性非常好，有一些非常特殊的开发框架（如IOT，化学，医疗，基因库，GIS...）
  MySQL，兼容性好
服务端编程语言
  PostgreSQL，扩展能力极强，支持各种服务端编程语言
  MySQL，不支持扩展

软件开发商数量，不相上下
软件厂商覆盖面 ，不相上下
培训公司
  PostgreSQL，国内较少，国外较多
软件外包公司
技术支持公司
  PostgreSQL ，国内较少，国外较多
数据库厂商
  PostgreSQL，国内外都非常多(BSD许可的原因)
  MySQL，非常少
用户量
  PostgreSQL，国内企业用户偏多，互联网行业偏少，国外都较多
用户覆盖面，
  PostgreSQL，覆盖几乎各个行业的核心系统
  MySQL，互联网较多，企业周边系统较多
高校
  PostgreSQL，教育资源丰富

应用案例

PostgreSQL
  生物制药 {Affymetrix(基因芯片), 美国化学协会, gene(结构生物学应用案例), …}
  电子商务 { CD BABY,  etsy(与淘宝类似), whitepages, flightstats, Endpoint Corporation, 阿里巴巴 …}
  学校 {加州大学伯克利分校, 哈佛大学互联网与社会中心, .LRN, 莫斯科国立大学, 悉尼大学, 武汉大学, 人民大学, 上海交大, 华东师范 …}
  金融 {Journyx, LLC, trusecommerce(类似支付宝), 日本证券交易交所, 邮储银行, 同花顺, 平安银行…}
  游戏 {MobyGames, 斯凯网络 …}
  政府 {美国国家气象局, 印度国家物理实验室, 联合国儿童基金, 美国疾病控制和预防中心, 美国国务院, 俄罗斯杜马, 国家电网, 12306…}
  医疗 {calorieking, 开源电子病历项目, shannon医学中心, …}
  制造业 {Exoteric Networks, 丰田, 捷豹路虎}
  媒体 {IMDB.com, 美国华盛顿邮报国会投票数据库, MacWorld, 绿色和平组织, …}
  开源项目 {Bricolage, Debian, FreshPorts, FLPR, PostGIS, SourceForge, OpenACS, Gforge, …}
  零售 {ADP, CTC, Safeway, Tsutaya, Rockport, …}
  科技 {Sony, MySpace, Yahoo, Afilias, APPLE, 富士通, Omniti, Red Hat, Sirius IT, SUN, 国际空间站, Instagram, Disqus, 去哪儿, 腾讯, 华为, 中兴, 云游, 智联招聘, 高德地图 …}
  通信 {Cisco, Juniper, NTT(日本电信), 德国电信, Optus, Skype, Tlestra(澳洲电讯), 中国移动…}
  物流 {SF}

目前国内较大规模应用PG的企业
  平安科技、邮储银行、高德、去哪儿、腾讯、阿里、华为、斯凯、通策医疗、云游、探探科技
  国家电网、XX铁路、xx保险、XX运营商、XXOA办公、同花顺、中航

发展潜力

PostgreSQL
  SQL兼容性强，功能强大，稳定性好，性能优越，扩展能力强，社区活跃，几乎覆盖全世界所有行业。
  国外生态已形成，国内正逐渐成为数据库焦点，伴随IOT和工业4.0的发展，redis+mongo+postgresql+仓储分析系统，能覆盖几乎所有应用场景的需求，发展潜力巨大。

学习成本

PostgreSQL
  文档完备脉络清晰，代码注释完备，认真学习非常容易达到较高的水平。
  PostgreSQL功能非常强大，与Oracle旗鼓相当，初期学习成本会较高，但是学成之后，会有一种与之融为一体的感觉，使用起来得心应手。可以大大降低使用成本，管理成本和风险。

开发成本

PostgreSQL
  SQL兼容性好，功能强大，扩展能力强，服务端编程能力强。
  数据库端可以解决非常多的程序需求，不需要move data，开发成本低。

开源许可

PostgreSQL
  BSD许可，允许任意形式分发和使用
MySQL
  GPL许可，商用软件分发必须开源

本文转自：https://yq.aliyun.com/ask/19599?spm=a2c4e.11155435.0.0.49f26596BoyotH

posted @ 2018-07-11 20:50 ppjj 阅读(2888) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

ppjj

postgre与mysql区别

公告