从mysql到postgresql

可以说一半以上的开发人员第一个接触的数据库都是MySQL，无论是大学，自学或者是培训，基本是先学MySQL，面试也是MySQL相关知识问的比较多，网上随便一搜就有大把的MySQL的存储引擎、MVCC、B+树、各种锁等知识。当然，MySQL也是很多互联网公司的一大选择。

但对于传统企业，和crm，erp等项目来说，更多的是选择pg了。这里可以试着简单分析一下这样的原因。首先，pg是免费且开源的数据库管理系统，oracle和sql server都不是免费的，所有着重点在于和MySQL的比较。

表现

可以选择一下几点来比较：

• 数据完整性和安全性：PG 在数据完整性和安全性方面提供更强大的功能，支持更严格的约束和验证规则。MySQL 也提供基本的数据完整性功能，但 PG 提供了更多的选项和灵活性。

• 复杂查询和高级特性：PG 对复杂查询的支持更强大，包括子查询、联合查询、窗口函数等功能。它还提供了更多的数据类型和高级特性，如数组、JSON、全文搜索等。MySQL 也支持一些复杂查询，但 PG 在这方面更为出色。

• 扩展性和可定制性：PG 在扩展性方面表现更好，提供了更多的选项，如分区表、并行查询等。它还支持自定义数据类型、函数和存储过程，提供更高的可定制性。MySQL 也具有良好的扩展性，但相对来说功能较少。

• 性能和吞吐量：在某些情况下，MySQL 可能在性能和吞吐量方面表现更好。对于简单的查询和事务处理，MySQL 的性能可能更高。然而，对于处理大型数据集和复杂查询，PG 的性能可能更优秀。

• 成熟度和稳定性：PG 是一个经过长期发展和广泛应用的成熟数据库系统，具有良好的稳定性和并发控制机制。MySQL 也是一种常用的数据库系统，在稳定性方面表现良好，但可能相对于 PG 来说稍逊一筹。

• 社区支持和生态系统：PG 和 MySQL 都有活跃和强大的开源社区，提供丰富的文档、教程和支持资源。两个数据库系统都有各自的第三方工具和插件生态系统。在这方面，它们相对接近。

对这两个免费开源数据来看，可以很容易得出一个结论，MySQL在数据量大的简单查询上性能占优，而pg在数据量一般的复杂查询上性能更优。

实现

可以再稍微稍微分析一下这个的原因，从以下几点（这里MySQL只考虑InnoDB引擎）：

• 存储方式 ： pg用的堆表的结构来存储，数据存储在堆中，插入数据时直接插入，有较好的性能，但因为数据存放在堆中的任意位置，可能导致内存碎片，需要底层做额外的处理，对于查找数据需要借助辅助索引，不然就只能全表扫描。 MySQL是索引组织表，数据存储在聚簇索引的叶子节点中，插入数据可能会导致树的重平衡，拆页等，但对于范围查找和主键查找很简单。
• 存储引擎：pg默认存储引擎是heap表，也支持InnoDB、Btree-Gis等。MySQL默认存储引擎是InnoDB，也支持MyISAM。
• 索引结构：pg支持B+Tree，Hash索引，全文索引，GIST等。MySQL支持B+Tree，Hash索引，全文索引。
• 复制数据：pg使用WAL写前日志的方式进行复制，这是一种物理日志，记录了对数据库中的数据页进行的修改操作，可支持异步和同步复制。mysql使用binlog进行日志的复制，是以逻辑格式记录的，记录的是对数据的操作，支持异步和半同步复制。
• 执行计划：pg使用基于规则的优化器，根据各种规则和启发式方法来生成执行计划。 MySQL使用基于成本的优化器来生成执行计划，它根据估计的成本选择最优的连接算法和执行顺序。
• 连接算法：连接算法也是影响查询速度的一大因素。pg和MySQL都支持的连接算法有循环嵌套连接、哈希连接、排序合并连接、索引连接等。pg还支持并行连接，在执行连接操作时可以利用多个并行工作者进行并行连接。这可以提高查询的执行速度，特别是在大型数据集上进行连接操作时。MySQL支持唯一连接，当连接条件保证连接操作的结果中没有重复行时，MySQL可以使用唯一连接算法。它通过在连接操作中进行去重来减少结果集的大小和处理复杂度。

这些是他们实现方式的一些差异，对于使用者，特别是刚从MySQL转为pg的开发者来说，可能更关注日常开发使用的一些差异。

日常

对于使用mysql的开发者来说，都会给MySQL设定一个主键，因为它是索引结构表，就算不设定主键，MySQL也会自己设置一个隐藏的id。但对于pg堆表来说，主键并不是必须的。pg的自增也要通过额外的序列（Sequence）来实现。这些对于刚转pg的同学来说，可能不太习惯。

当然还有其他的一些事项，比如语法差异，MySQL 和 PostgreSQL 在 SQL 语法上存在一些差异。需要注意的是，一些常用的语句、函数和操作符在两个数据库之间可能有不同的实现方式。数据类型的支持，pg对于json的支持比较完善。还有索引和约束，事务和并发控制等。

对于并发控制，pg的mvcc的实现和mysql还是有区别的。这里看以下它们的差异：

• 数据存储方式：PG使用行级版本控制（Row Level Versioning），每行数据保留了旧版本和新版本的信息。而MySQL则采用基于页的版本控制（Page Level Versioning）或者事务级的版本控制。这意味着PG在记录每个数据行的版本时更为细粒度，而MySQL以页或事务为单位。

• 快照隔离级别的实现：PG使用满足快照隔离级别（Snapshot Isolation）的快照读，也称为可重复读（Repeatable Read）。它使用了基于快照的方式生成一致性的数据库快照，并确保每个事务在整个事务期间看到的是一致的数据。MySQL也支持可重复读隔离级别，但它使用了锁定和读修复的机制来防止幻读。

• 冲突检测与处理：PG使用乐观并发控制（Optimistic Concurrency Control），它假设事务之间的冲突很少发生，事务提交时才检测可能的冲突，如果发现冲突则回滚。相比之下，MySQL使用悲观并发控制（Pessimistic Concurrency Control），在读取时就会对数据进行锁定，以避免其他事务的修改。

• 数据访问控制：PG使用多版本的数据访问控制策略，对于读操作和写操作都生成新版本，并维护旧版本和新版本之间的关系。这使得PG可以提供高并发的访问和一致性读取。MySQL采用了next-key locking机制，在可重复读隔离级别下使用锁定范围来防止幻读，通过锁定索引范围来保证事务的一致性