深入解析：Postgres 和 MySQL 的核心差异与选择建议

几十年来，关系数据库为无数应用程序提供了支持，它们仍然是许多现代系统的支柱。说到可用于生产的选项，有两种最为广泛使用的数据库，即PostgreSQL和MySQL。两者都提供了可靠的性能、可靠性和社区支持，但它们在处理数据的方式、功能集和配置难易程度方面存在明显差异。了解这些细微差别可以帮助您根据特定需求选择合适的数据库。

何时应该使用 PostgreSQL 或 MySQL？

下表概括了一些最大的差异：

功能概述

PostgreSQL
PostgreSQL 使用模式在单个数据库中组织数据，使团队能够对权限和逻辑数据分区进行细粒度控制。它还支持多种数据类型，包括 JSON、数组、范围甚至自定义类型，这使其对于处理复杂或半结构化数据的应用程序具有吸引力。该数据库使用多版本并发控制 (MVCC) 来减少锁争用，因此它通常擅长处理大量写入负载和复杂查询，这些查询受益于窗口函数和通用表表达式 (CTE) 等功能。另一个关键优势是可扩展性：您可以添加自定义函数、运算符或扩展，例如用于地理空间数据的 PostGIS - 如果您的应用程序需要专门的功能，这将非常方便。

MySQL
MySQL 则更简单地组织数据，使用不同的数据库而不是模式。这可以让小型项目或团队的工作变得更轻松，他们只需启动一个新数据库即可保持数据隔离。MySQL 最大的卖点之一是它在读取密集型场景中的强大性能，尤其是当 InnoDB 引擎与适当的索引和缓存结合使用时。它还以简单的复制而闻名，许多高流量网站使用它将读取操作分布到多个服务器，并为全球用户提供更快的响应。MySQL 通常易于设置，并且拥有庞大的知识库，如果您需要快速启动项目，或者您的团队已经熟悉 MySQL 生态系统，那么这将很有吸引力。

数据库详细信息

读/写吞吐量
MySQL 通常在处理读取密集型工作负载方面表现出色，前提是索引和缓存层经过适当调整。一些大型用户（例如 Uber）发现，只要仔细配置数据库，即使对于大量写入负载，MySQL 也能成功。对于简单的插入和更新，MySQL 可以在许多基准测试中与 PostgreSQL 匹敌。但是，PostgreSQL 通常在更复杂的写入和复杂查询方面处于领先地位。其并发功能（由 MVCC 增强）减少了锁争用，并使其在同时涉及大量事务的场景中保持高性能。通过适当的调整，PostgreSQL 可以在典型的 OLTP 或分析工作负载中达到或超过 MySQL 的性能。

可扩展性
这两个数据库的扩展性都很好，但它们的扩展方式不同。PostgreSQL 对垂直扩展反应良好——添加更多 CPU、RAM 或更快的存储通常会带来显着的好处。水平扩展稍微复杂一些；用于连接池和逻辑复制的 PgBouncer 等工具可以提供帮助，Instagram 和 Notion 等大型平台已经证明它可以支持庞大的用户群。MySQL 一直因其简单的复制（主副本）而受到称赞，可以轻松卸载读取流量并将这些查询分发到多个服务器。这种内置复制设置通常足以满足许多全局读取可扩展性至关重要的用例。

索引和查询优化
PostgreSQL 提供多种索引类型，例如 B 树、GiST、GIN 和 BRIN，它们适用于特定类型的查询并可显著提高性能。它还具有复杂的 JSON 索引和全文搜索功能，但您可能需要启用某些扩展。MySQL 的 InnoDB 引擎主要依赖于 B 树索引，适用于大多数常见查询模式，并且它具有一些全文索引功能 - 尽管不如 PostgreSQL 的那么广泛。

性能调优
PostgreSQL 和 MySQL 都需要调优参数（例如缓冲区大小、缓存、检查点间隔）来优化性能。PostgreSQL 可能更复杂，尤其是对于新用户而言，但通过精心设计的索引和查询，任何一个数据库都可以在大多数生产环境中有效扩展。

近期趋势和认可
近年来，PostgreSQL 迅速流行起来，获得了“年度最佳数据库管理系统”等殊荣，并在开发者调查中取得了长足进步。其宽松的许可证和现代功能集继续吸引新用户。尽管如此，在 Oracle 的支持和庞大的社区的推动下，MySQL 仍然是全球安装最多的开源关系数据库。它的稳定性、简单性以及托管提供商和工具的生态系统确保了它在许多场景中继续占据主导地位。

许可证注意事项
MySQL 的社区版是 GPL 许可的，如果您希望保持自己的代码专有，这可能会受到限制。在这种情况下，可能需要 Oracle 的商业许可证。PostgreSQL 的许可证类似于 BSD/MIT，限制较少，并且不需要披露您的源代码。

技术比较
PostgreSQL 的对象层次结构为数据库 → 模式 → 表，而 MySQL 使用数据库 → 表。PostgreSQL 完全符合 ACID 标准，可以处理 DML 和 DDL 事务；MySQL 还通过 InnoDB 引擎符合 ACID 标准，并在 8.0+ 版本中支持原子 DDL。在安全方面，PostgreSQL 提供开箱即用的行级安全性 (RLS)，而 MySQL 需要视图或存储过程等变通方法来模拟类似的功能。

在复制方面，PostgreSQL 支持物理（基于 WAL）和逻辑（发布/订阅）方法。MySQL 使用二进制日志来促进逻辑复制，并且通常配置为使用主副本设置进行读取扩展。得益于其强大的索引和函数数组，PostgreSQL 中的 JSON 处理更加全面。虽然 MySQL 在 8.0+ 版本中也包含 JSON 功能，但其对 JSON 数据的索引有些有限。PostgreSQL 的窗口函数和 CTE 更加成熟，尽管 MySQL 最近通过添加这些功能迎头赶上。如果您重视可扩展性，PostgreSQL 提供了广泛的扩展 - 用于地理空间用例的 PostGIS、用于详细查询见解的 pg_stat_statements 以及定义自定义数据类型的能力 - 而 MySQL 的自定义选项则专注于存储过程和插件。

Postgres 与 MySQL 性能

在使用具有类似配置的 Go 客户端的测试中：

1. 插入（写入）测试

   • 设置：多个虚拟客户端连续插入随机记录。
   • 结果：
     • 在配备 SSD 的 4-CPU 服务器上，PostgreSQL 的插入速度徘徊在每秒 19,000 次，而 MySQL 的插入速度则为 10,000 次。
     • PostgreSQL 在第 99 个百分位显示出更低的延迟，并且更有效地使用 CPU、磁盘和内存。
     • MySQL 性能下降约 5,500 个查询/秒，导致 CPU 使用率更高。

2. 选择（阅读）测试

   • 设置：查询涉及与约 7000 万行客户表关联的随机事件 ID。
   • 结果：
     • PostgreSQL 再次显示出较低的延迟，可以顺利扩展到约 32,000 个查询/秒。
     • MySQL 开始显示接近 18,000 个查询/秒的延迟峰值，这与 CPU 使用率的上升有关。
     • 两者最终都达到了 CPU 饱和，但 PostgreSQL 在遇到瓶颈之前还能继续延伸。

关键要点

• 写入效率：PostgreSQL 以较少的资源使用处理大量插入负载。
• 读取性能：MySQL 最初表现良好，但在高并发性下很快就下降了。
• 资源利用率：在同等负载下，PostgreSQL 通常使用较少的系统资源。

实际性能会因硬件、索引策略、查询模式和配置而异。在做出最终选择之前，请务必在反映您的生产设置的环境中进行测试。

为了简化 Postgres 和 MySQL 的测试和使用，Outerbase 提供了一个强大的界面来探索、查询和可视化您的数据库。无论您是在比较基准还是管理生产工作负载，Outerbase都可以帮助您简化流程。

Postgres 与 MySQL 哪个更好

• 如果遇到以下情况，请考虑 PostgreSQL

  • 您需要高级功能，例如窗口函数、CTE、自定义数据类型或用于地理空间查询的 PostGIS。
  • 您预计会有复杂或高度并发的工作负载。
  • 您想要一个更宽松、限制更少的许可证。
  • 您渴望加入快速扩张的生态系统和社区。

• 如果出现以下情况，请考虑使用 MySQL

  • 您主要关注的是读取繁重的工作负载和简单的查询。
  • 您需要某种能够快速、简单地部署的东西，并由大量知识库支持。
  • 您的团队已经了解 MySQL，或者您的托管环境已针对它进行了优化。
  • 您更喜欢轻松复制以实现水平扩展。

最好的方法通常是同时测试两者。启动几个实例，复制您的实际工作负载，并查看每个实例的性能。您可能会发现一个数据库自然更适合您的数据和查询模式，尤其是当您考虑到您的团队对每种技术的熟悉程度时。

结论

如果您需要窗口函数、CTE、自定义数据类型或用于地理空间工作的 PostGIS 等高级功能，您可能会更喜欢 PostgreSQL。它在处理更大的并发性或复杂的工作负载方面也表现出色，而且其宽松的许可证不会对您自己的代码施加太多限制。同时，如果您的应用程序读取量很大并且您希望快速部署，那么 MySQL 仍然是一个引人注目的选择，特别是如果您的团队已经熟悉 MySQL 或您的环境针对它进行了优化。其更简单的复制机制对于那些需要扩展读取的人来说很方便。

最后，最好的方法是在镜像生产设置的环境中测试这两个数据库。检查它们在实际数据、查询和并发级别下的性能。“更好”的选项通常归结为功能要求、工作负载配置文件、操作熟悉度、许可和长期可扩展性目标等因素。虽然 PostgreSQL 的功能集吸引了快速增长的用户群，但 MySQL 的良好记录和庞大的社区确保它将在未来几年继续成为主流。