PostgreSQL与MySQL 分析对比

逃离我推掉我的手 2022-11-18 02:29 225阅读 0赞

http://www.pgsql.tech/article\_101\_10000079

# 概述 #

在几个流行的数据库中，我首先接触到的是MySQL，随着工作发展，接触到越来越多的是PostgreSQL数据库。这两个十分流行的开源数据库。在这之后，我就会经常和一些朋友进行讨论：MySQL和PostgreSQL两者之间到底有什么异同点呢？  
今天我就来说一说

# ACID的遵从性(ACID Compliance )对比 #

PostgreSQL：  
完全遵从ACID，确保满足所有ACID的需求  
MySQL：  
只有InnoDB等少量存储引擎遵从ACID

# 原子性（Atomic） #

Atomic是指事务包含的所有操作要么全部成功，要么全部失败回滚  
PostgreSQL是全面支持事务的关系型数据库，不仅DML语句可以在一个事务中，DDL语句也支持事务，即可以把创建多张表时的DDL语句放到一个事务中，这样可以保证这些表要不都创建成功，要不都不成功。

而MySQL无法把多条DDL语句放到一个事务中。PostgreSQL能保证多条DDL的原子性，这是PG的亮点。

# 一致性（Consistency） #

Consistency是指事务必须使数据库从一个一致性的状态变到另外一个一致的状态，也就是执行事务之前和之后的状态都必须处于一致的状态。一致性：例如数据库完整性约束正确；日志状态一致； 索引和数据的一致， 即事务完成后，数据库的一切都是对的。  
而两者的约束对比如下

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70]

# 隔离性（Isolcation） #

Isolcation是指并发执行多个事务时，使它们各自好像是系统在给定时间内执行的唯一操作，即每一事务在系统中认为只有该事务在使用系统，不受其它事务干扰。  
达到的效果： 对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。  
几种隔离级别的性能比较  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70 1]

而PostgreSQL和MySQL的隔离性差异如下  
MySQL和PostgreSQL都实现了基于快照的事务隔离级别，即snapshot isolation，PostgreSQL还实现了可串行化的快照隔离级别，即SSI。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70 2]

# 持久性（Durability） #

Durability是指一个事务一旦被提交了，那么对于数据库中的数据改变就是永久性的，即便是在数据库系统遭遇到故障的情况下也不会丢失提交事务的操作。  
早期的MySQL版本中的默认配置不是提交后立即持久化的，即提交后，数据可能丢失（追求性能），PostgreSQL一开始默认就是持久化的，当然也可以设置成非立即持久化。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70 3]

# 对SQL标准的支持度对比 #

PostgreSQL：  
遵从SQL标准支持最好的数据库。SQL2011标准中，179个核心特性（mandatory features ），做到至少160个，也支持了很多附加特性

MySQL：  
只有部分支持，基本是只持了SQL 92标准的部分

# 支持的业务场景对比 #

PostgreSQL：  
既支持OLTP场景也支持OLAP场景，混合性业务场景，类似Oracle数据库。能很好的支持JSON文档型业务场景。金融级的可靠性，设计目标是以稳定性和可靠性为第一目标，支持大数据库，可以可靠支持几十T的大数据库

MySQL：  
只支持OLTP场景，不可用于OLAP场景  
OLTP和OLAP的中间场景也不适合，如ERP、CRM等系统  
对JSON文档型支持不是太好  
简单和性能是设计第一目标，支持可靠性很高的业务存在一定的困难  
大数据库支持存在一定的困难，需要做单机多实例

# 复制功能对比 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70 4]

复制类型的支持  
**PostgreSQL支持以下类型**：

单主到单备  
单主到多备  
物理备库可以读  
双向复杂  
逻辑的流复制（发布和订阅模式）  
级联的复制  
全同步、半同步、异步  
同步复制中支持最少同步到几个备节点，即保证至少写几份数据

**MySQL支持以下类型**：

单主到单备  
单主到多备  
单主到单个备再到单个或多个备  
循环复制：A->B->C->A  
双主复制  
异步复制和半同步复制

# 主要功能对比 #

查询相关功能对比

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70 5]

表的功能支持  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70 6]

架构功能对比  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70 7]

视图表功能对比  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70 8]

加密使用功能对比  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70 9]

# 后记 #

总之，PostgreSQL和MySQL是从底层设计原理开始就不一样的两种数据库，在数据量小的时候，数据库更趋于轻量化，MySQL会更适合。但是一旦数据量稍涨，计算量上升，PostgreSQL会是更好的选择。

部分内容摘自唐成培训介绍PPT

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70]: /images/20221022/270a15c3ee72454fb8b56c3e325d3a87.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70 1]: /images/20221022/0d0dc571ad5c4d9199a2896956dad112.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70 2]: /images/20221022/0db5fbba8de0406992f136963f08e81b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70 3]: /images/20221022/71b489ddd5c647bcbfa37d6bec113eeb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70 4]: /images/20221022/41bd242e1bd04d97a6ad2db08c32972d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70 5]: /images/20221022/d7e458a9d3c34deeb5879bf5bdd2b03d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70 6]: /images/20221022/52e75eec110b4d56bf938b418b8a3db4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70 7]: /images/20221022/fe59bfdd91474f31b3b8f6bb71e4437b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70 8]: /images/20221022/03abbae4858c407488d7677d2d0e078f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70 9]: /images/20221022/2f2449994bed49ec8416832aa17d4f24.png