这是什么？

我之前只用过MySQL，但现在要开始使用PostgreSQL，所以这是一篇总结了MySQL和PostgreSQL的区别的文章。

使用PostgreSQL还是MySQL？从8个角度来深入比较数据库专家的建议！参考了“PostgreSQL和MySQL，使用哪个？”一文，总结了概述，包括其他文章的参考，官方文档的参考以及一些补充资料。

※ 尽管我们尽可能查看最新的文档和参考对比文章（至2018年10月），但在比较过程中可能会遗漏一些目标版本和末尾发布版本等内容。
※ 关于MySQL的说明，我们主要基于InnoDB引擎。

特征（设计理念）

のあるデータベース管理システム。

详细

MySQL被设计得非常注重速度方面。虽然下面会详细列举一些具体对比项目，比如在默认情况下进行字符串转换和判断时会更宽松，以便追求速度。
然而，它在处理复杂的搜索操作上相对弱点明显，比如一次只能使用一个索引和较为有限的Join算法。

首先，PostgreSQL早在MySQL之前就实现了窗口函数和能够在执行SELECT语句之前创建子查询的WITH子句（尽管MySQL最终也引入了这些功能），此外，它似乎比MySQL更早引入了JSON类型。
此外，PostgreSQL没有隐式的字符串转换，需要明确进行转换，判断更加严格（关于此更多详细信息请参见下文）。

这是一篇相当古老的2008年的文章，但当时的信息对于比较数据库系统PostgreSQL的功能以及其他关系型数据库管理系统是完整的。

数据定义语言操作

・MySQL 现在可以在线执行多种操作。

根据DDL语句的不同，可能会导致包括添加列等结构更改在内的操作会阻塞甚至影响整个表的引用。

解释

关于MySQL的在线DDL，在MySQL 5.6参考手册的“表14.5 DDL操作的在线状态摘要”中有详细说明，其中大部分支持并行DML（常规SQL操作）。

根据以上的说明，似乎PostgreSQL在进行DDL操作时会阻止几乎所有的并行DML（通常的SQL操作）。（它会获取ACCESS EXCLUSIVE锁）
然而，有一个名为pg_repack的外部工具，据说可以将锁定操作降至最小。
通过pg_repack进行ALTER TABLE操作的详细信息请参考以下文档：
https://reorg.github.io/pg_repack/jp/

即在线执行相当于 ALTER TABLE … SET TABLESPACE 的操作。

我认为这个地方大概是这样的。

附带说一句

此外，我认为仅仅因为一个查询可以在线执行（而不会被阻塞），并不意味着其他查询的操作肯定可以无影响地执行。可能是因为使用了“RDS”，在执行数百万个ADD INDEX时，SELECT/UPDATE/INSERT的操作会出现几十秒的延迟。这个问题也在下面的文章中有类似的调查报道。
同时，外道父的匠Aurora进行了ALTER TABLE性能测试并与RDS进行了比较。

RDS的结果出现分散是因为在ALTER TABLE执行后，几秒钟内会持续处于轻量状态然后变得重量级，这是由于其性质所致。

选择语句

MySQL的算法并不是很优秀。

PostgreSQL和MySQL的差异在除排序部分之外并不大。

详细的

如果只是简单的SELECT语句，两者之间差距不大。
但是，如果涉及到稍后出现的Join算法或者多个索引的复杂SQL语句，PostgreSQL会更擅长。

更新文件

・MySQL
迅捷

・与MySQL相比，PostgreSQL基本上较慢。

详细 – xì

PostgreSQL的UPDATE语句比MySQL慢。

MySQL是直接覆写更新目标行的值，而PostgreSQL采用了追加式架构，因此更新操作类似于插入操作。
追加式架构是指将before/after的内部记录分别保存，并切换引用的方式。

此外，物理记录具有称为cttid的内部ID，在配置二级索引时会将关联的ctid引用参考索引进行更改。换句话说，对于具有大量索引的表格等，添加/更新会导致该部分的更新以及效率低下。Uber Engineering发现该架构与此不相容，因此他们转向了MySQL。

删除

「MySQL曾被說成很慢，但現在並非如此。」

PostgreSQL与MySQL并没有太大差异，但由于采用了追加式架构，可能会稍微慢一些。

解释

在UPDATE操作中，追記型架构使得在速度上基本上PostgreSQL劣势较大，不过MySQL在DELETE操作中也做了类似的事情（同步更新辅助索引的引用）。然而，由于MySQL 5.5开始进行了异步的辅助索引更新，速度并不会太慢。所以，从速度方面来看，MySQL比PostgreSQL更有优势。

加入

只有嵌套循环连接（Nested Loop Join）是可行的。

PostgreSQL支持”嵌套循环连接（Nested Loop Join）”，”哈希连接（Hash Join）”和”排序合并连接（Sort Merge Join）”。

说明

MySQL只基本实现了Nested Loop连接算法。Nested Loop连接算法为8.2.1.10 Nested Loop连接算法。

PostgreSQL支持MySQL不具备的”哈希连接（Hash Join）”和”排序合并连接（Sort Merge Join）”。哈希连接在内存中创建哈希表，因此哈希匹配速度较快，但是需要注意的是，如果表很大，则需要相应的内存量。

考虑

每个JOIN的解释都在这里很容易理解。
这是关于执行计划和SQL调优的考虑因素的EddieLabo 4.5.4.
Tech Score博客关于PostgreSQL索引仅扫描的困扰之旅第3部分。

交易

• MySQL 的默认隔离级别是“REPEATABLE-READ”。这意味着在一个事务中，SELECT 语句的结果不会受到其他事务的影响（可以看到相同的结果）。

• PostgreSQL的默认隔离级别是“READ-COMMITTED”。这意味着其他事务的更新结果会被立即反映，因此在运营过程中要注意注意。

解释

无论默认模式是否不同，以及即使将PostgreSQL的事务隔离级别更改为REPEATABLE-READ，它也不会获取Next-Key Lock，从而可以防止幻读。

为什么可以防止呢？是通过使用”Predicate Lock（述語锁）”机制实现的。
以下链接提供了更详细的解释：
https://qiita.com/yuba/items/89496dda291edb2e558c
据我了解，它是通过在另一个事务中检测作为条件的述语（如’username = ‘alice’等）来实现的。

换句话说，即使在相同的事务隔离级别下，由于实现方式的不同（行为上的差异），需要注意。

备考

维基百科的事务隔离级别

存储过程、触发器

– MySQL中，存储过程仅支持SQL，触发器只有FOR EACH ROW，而没有FOR EACH STATEMENT。
(SQL in MySQL，存储过程只支持SQL，触发器只有FOR EACH ROW，没有FOR EACH STATEMENT。)

可以使用SQL、Python等外部过程（太厉害了！）

复制的逻辑类型和物理类型

・MySQL–>MySQL是一种开源关系型数据库管理系统。

可以选择逻辑复制（复制SQL文本本身）或者物理复制（复制修改后的行镜像）两种类型。逻辑复制是默认设置，但从MySQL 5.7开始，物理复制成为默认选项。

・PostgreSQL
从版本10开始，PostgreSQL不仅支持物理型，还支持逻辑型。

解释

據傳，注重安全性的PostgreSQL終於引入了邏輯類型。相比於物理類型，邏輯類型能夠避免數據不一致，看起來更具穩健性。儘管如此，還有一些運營是基於邏輯類型的使用。

在中国的本地语言中，只需要给出一种方式：目前还没有详细调查关于复制同步/异步的差异以及最新情况。

只需要一种选择，将以下内容用中文进行本地化：
闲话

逻辑型只要通过SQL就可以实现复制，即使数据结构不同，因此利用它可以:
1. 在被复制的从属端执行大型ALTER TABLE（在大型ALTER TABLE执行期间，由于复制会发生锁定而导致延迟）
2. 当从属端的ALTER TABLE完成并且复制追上时，再与主服务器交替
3. 表面上，可以在没有对大型ALTER TABLE影响的情况下进行运作
我也曾经做过这样的事情（在很久以前）。

其他功能

・MySQL的版本8.0将会引入窗口函数和WITH子句

・PostgreSQL（数据库软件）
具有独特的特性，如并行查询等。
https://www.postgresql.jp/document/9.6/html/parallel-query.html

有一个非常受欢迎的第三方开源工具叫做PostGIS，它可以处理地图和几何数据。

数据类型

MySQL的5.6版本之前比较宽松，但从5.7版本开始变得严格了。

・PostgreSQL
严格的。

解释

MySQL存在着相当数量的隐式类型转换，版本5.6 MySQL 5.6参考手册12.2节式评估中也有相关内容。

mysql> SELECT 1+'1';
        -> 2
mysql> SELECT CONCAT(2,' test');
        -> '2 test'

由于在MySQL端自动处理好，所以从经验上来说，问题比较多，而不是方便。
然而，在新版本中，不再存在这种隐式处理。
另外，在MySQL中，就像在“PostgreSQL和MySQL，如果使用哪个？通过8个专家视角进行彻底比较！”中所提到的那样，

「は」と「ぱ」と「ば」是相等的，而「びょういん」と「びよういん」也是相等的。

据说，Unicode排序的严格性设置为从级别1到级别4中的1（最宽松的）作为默认值，以提高处理速度。

PostgreSQL最初本身沒有隱含的轉換，而是非常嚴格的。

真空（优化）

・MySQL（并非母语中文）

在通常情况下，被删除或由于更新而变为无需的元组在物理上不会从表中被删除。（元组指内部记录。由于PostgreSQL是一种追加类型数据库，因此在更新之前和之后都会存在元组）

解释

VACUUM（OPTIMIZE）是一个解决数据碎片化和记录删除等问题的命令，用于释放不必要的空间。
无论在MySQL还是PostgreSQL中，此操作都是必需的。

在PostgreSQL中，也有类似于PostgreSQL 10.4文档19.10中自动Vacuum操作的自动执行机制。

由于VACUUM FULL操作需要将整个表的内容无缺地重写到新的磁盘文件中，因此可以向操作系统返回未使用的空间。然而，这种方式会导致执行速度变得相当慢。此外，对正在处理的表需要进行独占锁定。

所以，在这种情况下，最后还是需要根据时机来决定做什么的。

MySQL说已经实现了在线DDL，而且OPTIMIZE TABLE也不再需要锁定。（不过，我认为像其他DDL一样，不能断言没有锁定之外的影响） MySQL 5.6 参考手册 / 13.7.2.4 OPTIMIZE TABLE 语法

总结

如果要选择使用MySQL还是PostgreSQL，数据库专家通过8个角度进行了全面比较！并总结了其他参考资料。

在选择哪一个使用方面，由于基本功能相似，所以差异可能无关紧要。也许可以根据个人偏好、生态系统或工具的便利性来决定。

我对写下的内容，特别是关于PostgreSQL的内容几乎没有经验，可能存在错误之处。如果您发现任何问题，请告诉我！