【干货总结】:可能是史上最全的MySQL和PGSQL对比材料

 ，把主库切换为异步模式 主从数据完全一致是高可用切换的第一前提，所以PGSQL选择主库罢工也是可以理解     MySQL
增强半同步复制 ，mysql5.7版本增强半同步才能保证主从复制时候不丢数据
mysql5.7半同步复制相关参数：
参数rpl_semi_sync_master_wait_for_slave_count 等待至少多少个从库接收到binlog，主库才提交事务，一般设置为1，性能最高
参数rpl_semi_sync_master_timeout 等待多少毫秒，从库无回应自动切换为异步模式，一般设置为无限大，不让主库自动切换为异步模式
所有从库宕机，主库会罢工，因为无法收到任何从库的应答包 手动解决：在MySQL主库修改参数rpl_semi_sync_master_wait_for_slave_count=0

---

9. 多字段统计信息

PGSQL 支持多字段统计信息     MySQL 不支持多字段统计信息

---

10. 索引类型

PGSQL 多种索引类型（btree , hash , gin , gist , sp-gist , brin , bloom , rum , zombodb , bitmap，部分索引，表达式索引）     MySQL btree 索引，全文索引（低效），表达式索引(需要建虚拟列)，hash 索引只在内存表

---

11. 物理表连接算法

PGSQL 支持  nested-loop join 、hash join 、merge join        MySQL 只支持  nested-loop join

---

12. 子查询和视图性能

PGSQL 子查询，视图优化，性能比较高     MySQL 视图谓词条件下推限制多，子查询上拉限制多

---

13. 执行计划即时编译

PGSQL 支持  JIT    执行计划即时编译，使用LLVM编译器     MySQL 不支持执行计划即时编译

---

14. 并行查询

PGSQL 并行查询（多种并行查询优化方法），并行查询一般多见于商业数据库，是重量级功能     MySQL

有限，只支持主键并行查询

---

15. 物化视图

PGSQL 支持物化视图     MySQL

不支持物化视图

---

16. 插件功能

PGSQL 支持插件功能，可以丰富PGSQL的功能，GIS地理插件，时序数据库插件， 向量化执行插件等等     MySQL

不支持插件功能

---

17. check约束

PGSQL 支持check约束     MySQL

不支持check约束，可以写check约束，但存储引擎会忽略它的作用，因此check约束并不起作用（mariadb 支持）

---

18. gpu 加速SQL

PGSQL 可以使用gpu 加速SQL的执行速度        MySQL

不支持gpu 加速SQL 的执行速度   

---

19. 数据类型

PGSQL 数据类型丰富，如 ltree，hstore，数组类型，ip类型，text类型，有了text类型不再需要varchar，text类型字段最大存储1GB     MySQL

数据类型不够丰富

---

20. 跨库查询

PGSQL 不支持跨库查询，这个跟Oracle 12C以前一样     MySQL

可以跨库查询

---

21. 备份还原

PGSQL 备份还原非常简单，时点还原操作比SQL Server还要简单，完整备份+wal归档备份（增量）
假如有一个三节点的PGSQL主从集群，可以随便在其中一个节点做完整备份和wal归档备份

    MySQL

备份还原相对不太简单，完整备份+binlog备份（增量）
完整备份需要percona的XtraBackup工具做物理备份，MySQL本身不支持物理备份
时点还原操作步骤繁琐复杂

---

22. 性能视图

PGSQL 需要安装pg_stat_statements插件，pg_stat_statements插件提供了丰富的性能视图：如：等待事件，系统统计信息等
不好的地方是，安装插件需要重启数据库，并且需要收集性能信息的数据库需要执行一个命令：create extension pg_stat_statements命令
否则不会收集任何性能信息，比较麻烦

  MySQL

自带PS库，默认很多功能没有打开，而且打开PS库的性能视图功能对性能有影响（如：内存占用导致OOM bug）

---

23. 安装方式

PGSQL 有各个平台的包rpm包，deb包等等，相比MySQL缺少了二进制包，一般用源码编译安装，安装时间会长一些，执行命令多一些

  MySQL

有各个平台的包rpm包，deb包等等，源码编译安装、二进制包安装，一般用二进制包安装，方便快捷

---

24. DDL操作

PGSQL 加字段、可变长字段类型长度改大不会锁表，所有的DDL操作都不需要借助第三方工具

  MySQL

由于大部分DDL操作都会锁表，例如加字段、可变长字段类型长度改大，所以需要借助percona-toolkit里面的pt-online-schema-change工具去完成操作
将影响减少到最低，特别是对大表进行DDL操作

---

25. 大版本发布速度

PGSQL PGSQL每年一个大版本发布，大版本发布的第二年就可以上生产环境，版本迭代速度很快 PGSQL 10正式版推出时间：2017年
PGSQL 11正式版推出时间：2018年
PGSQL 12正式版推出时间：2019年   MySQL

MySQL的大版本发布一般是2年~3年，一般大版本发布后的第二年才可以上生产环境，避免有坑，版本发布速度比较慢

MySQL5.6正式版推出时间：2013年
MySQL5.7正式版推出时间：2015年
MySQL8.0正式版推出时间：2018年

---

26. returning语法

PGSQL 支持returning语法，returning clause 支持 DML 返回 Resultset，减少一次 Client <-> DB Server 交互

  MySQL 不支持returning语法

---

27. 内部架构

PGSQL 多进程架构，并发连接数不能太多，跟Oracle一样，既然跟Oracle一样，那么很多优化方法也是相通的，例如：开启大页内存

  MySQL

多线程架构，虽然多线程架构，但是官方有限制连接数，原因是系统的并发度是有限的，线程数太多，反而系统的处理能力下降，随着连接数上升，反而性能下降
一般同时只能处理200 ~300个数据库连接

---

28. 聚集索引

PGSQL 不支持聚集索引，PGSQL本身的MVCC的实现机制所导致

  MySQL

支持聚集索引

---

29. 空闲事务终结功能

PGSQL 通过设置 idle_in_transaction_session_timeout 参数来终止空闲事务，比如：应用代码中忘记关闭已开启的事务，PGSQL会自动查杀这种类型的会话事务

  MySQL

不支持终止空闲事务功能

---

30. 应付超大数据量

PGSQL 不能应付超大数据量，由于PGSQL本身的MVCC设计问题，需要垃圾回收，只能期待后面的大版本做优化   

  MySQL

不能应付超大数据量，MySQL自身架构的问题

---

31. 分布式演进

PGSQL HTAP数据库：cockroachDB、腾讯Tbase 分片集群：  Postgres-XC、Postgres-XL

  MySQL
HTAP数据库：TiDB
分片集群： 各种各样的中间件，不一一列举

---

 

 

小结

上面的对比表还不是很完善，只有一些本人认为比较关键的特性拿出来对比

 

总的来说，MySQL因为需要支持更换存储引擎，所以某些功能都要受制于存储引擎层，例如：物理复制

而PGSQL不支持更换存储引擎（在PGSQL V12开始也支持可插拨的表存取接口），而且一直由官方统一开发和维护，所以相对比较稳定，功能也比较完善，对得上它的称号：《世界上功能最为强大的开源数据库》

PGSQL V12 支持可插拨的表存取接口之后，有可能由第三方存储引擎来改进PGSQL本身的MVCC实现机制，而不需要等待官方去解决，聚集索引、undo表空间这些都不再是问题

 

 

如有不对的地方，欢迎大家拍砖o(∩_∩)o 

本文版权归作者所有，未经作者同意不得转载。

【干货总结】:可能是史上最全的MySQL和PGSQL对比材料

标签：always   res   完整备份   对比   mysql   关于   tran   text   避免