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

运维了MySQL和PGSQL已经有一段时间了，最近接到一个数据库选型需求，于是便开始收集资料整理了一下，然后就有了下面的对比表

关键词：PostgreSQL 11、MySQL5.7

 

比较版本：PostgreSQL 11    VS      MySQL5.7（innodb引擎） Oracle官方社区版

版权情况：PostgreSQL 11（免费开源）、MySQL5.7 Oracle官方社区版（免费开源）

---

1. CPU限制

PGSQL

没有CPU核心数限制，有多少CPU核就用多少

 

 

MySQL

能用128核CPU，超过128核用不上

---

 2. 配置文件参数

PGSQL

一共有255个参数，用到的大概是80个，参数比较稳定，用上个大版本配置文件也可以启动当前大版本数据库

 

 

MySQL

一共有707个参数，用到的大概是180个，参数不断增加，就算小版本也会增加参数，大版本之间会有部分参数不兼容情况

---

3. 第三方工具依赖情况

PGSQL

只有高可用集群需要依靠第三方中间件，例如：patroni+etcd、repmgr

 

 

MySQL

大部分操作都要依靠percona公司的第三方工具（percona-toolkit，XtraBackup），工具命令太多，学习成本高，高可用集群也需要第三方中间件，官方MGR集群还没成熟

---

4. 底层主从复制原理

PGSQL

物理复制，跟SQL Server镜像/AlwaysOn一样，严格一致，没有任何可能导致不一致，性能和可靠性上，物理复制完胜逻辑复制，维护简单   

 

 

MySQL

逻辑复制，（sql_log_bin、binlog_format等参数设置不正确都会导致主从不一致）
大事务并行复制效率低，对于重要业务，需要依赖 percona-toolkit的pt-table-checksum和pt-table-sync工具定期比较和修复主从一致
主从复制出错严重时候需要重搭主从
MySQL的逻辑复制并不阻止两个不一致的数据库建立复制关系

---

5. 从库只读状态

PGSQL

系统自动设置从库默认只读，不需要人工介入，维护简单   

 

 

MySQL

从库需要手动设置参数super_read_only=on，让从库设置为只读，super_read_only参数有bug，链接： https://baijiahao.baidu.com/s?id=1636644783594388753&wfr=spider&for=pc

---

6. 版本分支

PGSQL

只有社区版，没有其他任何分支版本，PGSQL官方统一开发，统一维护，社区版有所有功能，不像SQL Server和MySQL有标准版、企业版、经典版、社区版、开发版、web版之分
国内外还有一些基于PGSQL做二次开发的数据库厂商，例如：Enterprise DB、瀚高数据库等等，当然这些只是二次开发并不算独立分支

 

 

MySQL

由于历史原因，分裂为三个分支版本，MariaDB分支、Percona分支 、Oracle官方分支，发展到目前为止各个分支基本互相不兼容
Oracle官方分支还有版本之分，分为标准版、企业版、经典版、社区版

---

7. SQL特性支持

PGSQL

SQL特性支持情况支持94种，SQL语法支持最完善，例如：支持公用表表达式（WITH查询）

 

 

MySQL

SQL特性支持情况支持36种，SQL语法支持比较弱，例如：不支持公用表表达式（WITH查询）

 

关于SQL特性支持情况的对比，可以参考： http://www.sql-workbench.net/dbms_comparison.html

---

8. 主从复制安全性

PGSQL
同步流复制、强同步（remote apply)、高安全，不会丢数据
PGSQL同步流复制：所有从库宕机，主库会罢工，主库无法自动切换为异步流复制（异步模式），需要通过增加从库数量来解决，一般生产环境至少有两个从库
手动解决：在PG主库修改参数synchronous_standby_names =''，并执行命令：  pgctl reload ，把主库切换为异步模式

主从数据完全一致是高可用切换的第一前提，所以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

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