PostgreSQL 教程: PostgreSQL 对比 MySQL

摘要：MySQL 和 PostgreSQL 提供了许多相同的特性和功能，但这两个开源数据库之间存在许多差异。

目录

介绍

PostgreSQL 和 MySQL 都是广泛使用的开源数据库，可支持各种实时应用程序。虽然 MySQL 被公认为世界上最受欢迎的数据库，但 PostgreSQL 通常被描述为世界上最先进的关系数据库管理系统 （RDBMS）。与 PostgreSQL 不同，MySQL 并不完全符合 SQL 标准，并且缺少 PostgreSQL 中可用的许多功能，这就是 PostgreSQL 越来越受欢迎并成为开发人员首选的原因。

在 Oracle 收购 MySQL 之后，该数据库现在有两个版本：企业版和开源版，后者由于 Oracle 对 MySQL 开发的控制而面临用户的批评。相反，PostgreSQL 因其全面的企业级特性和功能列表而受到全球青睐。它由一个全球性社区开发，旨在通过各家公司的重大贡献，来增强 PostgreSQL 的产品，确保它保持功能丰富，并与其他开源和商业数据库相比具有高度竞争力。

为什么选择 PostgreSQL？

PostgreSQL 是一个开源、功能丰富的对象关系数据库管理系统（ORDBMS），可与 Oracle 等实时顶级的数据库竞争。开发人员还选择 PostgreSQL 作为他们的 NoSQL 数据库，因为它简化了本地和云上数据库的设置和使用。在拥有大量数据库的私有云或公有云环境中，自动构建 PostgreSQL 实例可以节省大量时间。它还在所有平台上得到广泛采用，包括 Docker 容器。

适合什么应用程序？

PostgreSQL 完全符合 ACID 标准，是企业级的，对开发者和 DBA 都非常友好。它是跨任何领域的高事务量和复杂应用程序的最佳选择，可以满足各种基于 Web 和移动的应用程序服务。此外，PostgreSQL 还是一个出色的数据仓库，用于对大量数据运行复杂的报告查询和处理流程。

为什么选择 MySQL？

MySQL 提供了开源和商业版本，商业版本由 Oracle 管理。作为 RDBMS 数据库，它易于设置和使用，但对于需要完全 SQL 标准兼容性的应用程序来说可能并不理想。MySQL 在 SQL 标准方面存在很大限制，使其更适合在数据库上处理较小数据量的简单 Web 应用程序。此外，MySQL 的数据集成能力存在限制，这使得它在异构数据库环境中的使用有些复杂。

适合什么应用程序？

MySQL 是一种部分兼容 SQL 标准的数据库，适用于简单的 Web 应用程序，或只需要简单结构设计和简单 SQL 查询操作的应用程序。对于处理大量数据的复杂应用程序，它不是理想的选择。

数据类型

PostgreSQL 与 MySQL 数据类型对比表

应用场景

PostgreSQL

PostgreSQL 几乎无处不在 — 它跻身当今最常用的前四名数据库之列，仅次于 MySQL。Bloomberg、Goldman Sachs 和 Nokia 等大公司都在其后端运行 PostgreSQL。

PostgreSQL 可供各种行业使用，不仅限于一个行业。以下是目前广泛使用 PostgreSQL 的几个领域。

• 政府地理信息数据：PostgreSQL 包含一个称为 “PostGIS” 的强大扩展。此扩展提供了许多功能，可帮助处理不同的几何形状（如点、线串），并经过优化以减少磁盘和内存占用，从而提高查询性能。电力、应急服务和水利基础设施服务，主要依靠 GIS 来定位船员并将他们引导到准确的目的地，这通常是在很有挑战性的情况下，因此对政府来说会派上用场。
• 制造业：许多制造业需要大量高效的数据存储设施。PostgreSQL 是优化供应链性能和存储的合适选择。它是首选，因为它符合 ACID 标准，并且可以配置为自动故障转移、完全冗余和几乎零停机时间的升级。由于 Oracle 的新许可政策使小型企业难以维持使用 Oracle 的成本，因此 PostgreSQL 是首选。
• Web 技术：PostgreSQL 不仅仅是一个关系数据库；它还可以用作 NoSQL 形式的数据存储。您可以在单个产品中同时拥有关系型和面向文档的数据存储。它可以在许多现代框架中运行，如 Django（Python）、Hibernate（Java）、Ruby on Rails、PHP 等。基于其复制能力，网站可以轻松扩展，以纳入您需要的任意数量的数据库服务器。
• 科学数据：研究和科学项目可能会生成数 TB 的数据，必须以最实用的方式处理这些数据。PostgreSQL 具有出色的分析能力并提供强大的 SQL 引擎，因此处理大量数据不会引起问题。PostgreSQL 也可以很容易地扩展。您可以集成 Matlab 和 R 来执行多个数学和聚合函数。

MySQL

事实证明，MySQL 对 Web 应用程序很有用，因为大多数服务器都依赖于 MySQL。除了用作 WordPress 数据库外，Joomla、TYPO3 和 Drupal 等许多非 WordPress 企业，也使用 MySQL 作为其主要数据库。

以下是 MySQL 的一些应用案例，证明它是一个可靠且高效的数据库系统：

• OLTP 事务：事务需要速度和准确性。MySQL 可以高效、轻松地扩展到每秒 1000 个查询。事务需要确保原子性、一致性、隔离性和持久性（ACID）。MySQL 还遵守 ACID 原则，使其对关键事务是安全的。如果系统在事务期间发生故障，它将回滚失败的事务。
• LAMP 开源技术栈：MySQL 对于在 LAMP 开源软件栈（LAMP 代表 Linux、Apache、MySQL 和 PHP/Python/Perl）上运行的众多应用程序至关重要。LAMP 是 Web 服务的通用解决方案技术栈，被广泛认为是动态网站和高性能 Web 应用程序的首选方案。
• 电子商务应用程序：MySQL 是电子商务平台最普遍的事务处理系统之一。它有利于管理客户数据、交易和产品目录。在电子商务解决方案中，MySQL 通常与其他非关系数据库同时使用，包括用于同步订单数据和存储非产品数据的文档型和键值型存储。

JSON 支持

PostgreSQL

PostgreSQL 从 9.2 版本开始支持 JSON 数据类型，提供了比 MySQL 更高级的 JSON 数据功能。它包括各种特定于 JSON 的运算符和函数，有助于在 JSON 文档中进行高效的数据搜索。PostgreSQL 9.4 版本中的 JSONB 功能，可以二进制格式存储 JSON，还支持全文索引，也称为 GIN 索引。此增强功能显著加快了对 JSON 文档的全文搜索。

MySQL

相比之下，MySQL 从版本 5.7 才开始引入对 JSON 数据类型的支持，这要晚多了。虽然可以使用 SQL 查询 JSON 数据列，并且可以为 JSON 属性创建索引，但与 PostgreSQL 相比，特定于 JSON 的函数的范围是有限的。MySQL 的一个重大限制是，它不支持对 JSON 列进行全文索引。由于 MySQL 不完全符合 SQL 标准，因此它可能不是存储和处理 JSON 数据的最佳选择。

索引

为了提高数据库性能，您可以在处理大型数据表时，通过使用索引来加快 SQL 查询速度。如果没有索引，查询速度会很慢，并且会给 DBMS 带来很大的负担。

PostgreSQL 和 MySQL 都提供了不同的索引选项。PostgreSQL 的索引类型包括：

• 仅排列表中某部分信息的部分索引
• B 树索引和哈希索引
• 生成由表达式函数而不是列值得到的索引的表达式索引

另一方面，MySQL 提供了以下索引选项：

• 存储在 R 树上的索引，例如在空间数据类型上找到的索引
• 存储在 B 树上的索引，例如 PRIMARY KEY、INDEX、FULLTEXT 和 UNIQUE
• 使用 FULLTEXT 索引时的倒排列表和哈希索引

架构

MySQL 是纯粹的关系型数据库，而 PostgreSQL 是对象关系型数据库。PostgreSQL 提供了更复杂的数据类型，并允许对象继承属性。另一方面，这也让使用 PostgreSQL 变得更加复杂。PostgreSQL 包含一个符合 ACID 标准的存储引擎。MySQL 除了默认存储引擎 InnoDB 之外，还支持 15 种不同的存储引擎。大量的存储引擎允许您将它们快速用于各种应用场景。

PostgreSQL 通过分配内存为每个建立的客户端连接生成一个新的系统进程。这在具有许多客户端连接的系统上需要大量内存，但它提供了更好的隔离，例如，无效的内存访问错误只会使单个进程崩溃，而不是整个数据库服务器崩溃。另一方面，MySQL 使用单个进程并为每个连接维护一个线程。这使得 MySQL 成为企业级领域以外的应用程序的更合适选择。

复制和集群

MySQL 和 PostgreSQL 都提供了复制和集群功能，允许数据操作水平扩展。

PostgreSQL

PostgreSQL 复制因其可靠性而备受推崇。与 MySQL 不同，PostgreSQL 的复制基于 WAL 文件，使其更快、更可靠且更易于管理。PostgreSQL 支持主从和一主多从的配置，包括级联复制。这称为流式或物理复制，它可以是同步的，也可以是异步的。

默认情况下，复制是异步的，副本可满足读取请求。对于需要副本上的数据快照来镜像主数据库的应用程序，同步复制是有益的。但是，如果事务未提交到副本，这可能会导致主数据库挂起。

可以使用 Slony、Bucardo、Londiste 和 RubyRep 等外部开源工具实现表级复制，所有这些工具都使用基于触发器的复制。此外，PostgreSQL 还支持逻辑复制，它使用 WAL 记录执行表级复制，并降低了基于触发器的复制的复杂度。逻辑复制最初是由名为 pglogical 的扩展来提供，自版本 10 以后它已经是 PostgreSQL 内核的一部分了。

MySQL

MySQL 支持主从和一主多从的机制，确保数据更改通过 SQL 从主数据库复制到副本数据库。复制是异步的，这可能会在性能和可扩展性方面带来挑战。

MySQL 复制的一个主要优点是，副本不是只读的；如果应用程序在主数据库崩溃时故障转移到副本，则副本可以同时满足读取和写入，从而确保应用程序的操作无间断。但是，DBA 必须确保副本退出副本模式，并且所有更改都反向复制回原主数据库，这在处理长时间运行的 SQL 时可能会很慢。

视图

MySQL 支持视图，但视图中的 SQL 使用的表数上限为 61。视图作为虚拟表，不会实际存储数据，MySQL 不支持物化视图。使用简单 SQL 创建的视图可以更新，而使用复杂 SQL 创建的视图则不能。

PostgreSQL 支持的视图操作方式，与 MySQL 中的视图类似。简单 SQL 构造的视图可以更新，而复杂 SQL 构造的视图则不能。但是，有一种解决方法，可以使用规则来更新复杂视图。此外，PostgreSQL 还支持物化视图，如果需要物理存储数据，可以刷新和创建索引。

触发器

MySQL 支持在 ‘INSERT’、‘UPDATE’ 和 ‘DELETE’ 语句上触发 ‘AFTER’ 和 ‘BEFORE’ 事件。但是，MySQL 中的触发器无法执行动态 SQL 语句或存储过程。此限制可能会影响到处理更复杂的数据库操作所需的灵活性。

PostgreSQL 提供了更高级的触发器功能，支持针对 ‘INSERT’、‘UPDATE’ 和 ‘DELETE’ 事件的 ‘AFTER’、‘BEFORE’ 和 ‘INSTEAD OF’ 触发器。这种动态执行能力使 PostgreSQL 触发器更加通用，从而能够通过使用函数，高效处理复杂的 SQL 操作。

CREATE TRIGGER audit
AFTER INSERT OR UPDATE OR DELETE ON employee
FOR EACH ROW EXECUTE FUNCTION employee_audit_func();

存储过程

存储过程是数据库的关键组件，可满足复杂的数据提取要求，应用开发者经常将存储过程融入到其数据库开发过程中。MySQL 和 PostgreSQL 都支持存储过程，但 MySQL 仅支持标准 SQL 语法，而 PostgreSQL 提供了更复杂的过程。

PostgreSQL 将存储过程实现为带有“RETURN VOID”子句的函数，这是应用开发者青睐的功能，因为它支持 MySQL 中不可用的多种编程语言，例如 Ruby、Perl（PL/Perl）、Python（PL/Python）、PL/PgSQL、SQL 和 JavaScript。

存储

数据存储是任何数据库系统的关键方面。PostgreSQL 和 MySQL 提供了多种存储数据的选项，其中包括将表和索引等物理数据库对象保存到磁盘。本节探讨了两种类型的存储选项：通用存储和可插拔存储。

PostgreSQL 采用了一种称为表空间的常见存储机制，表空间可容纳表、索引和物化视图等物理对象。表空间通过跨多个物理位置对对象进行分组和存储，来实现 I/O 的高效分配。但是，PostgreSQL 目前不支持可插拔存储引擎，尽管此功能预计将在未来版本中推出。

MySQL 在其 InnoDB 引擎中提供了一个类似于 PostgreSQL 的表空间选项，允许 DBA 对物理对象进行分组和存储，从而增强 I/O 分布。此外，MySQL 还支持可插拔存储引擎，以满足 OLTP 和数据仓库等各种应用程序的特定存储需求。此功能是 MySQL 最显著的优势之一，因为可插拔存储功能是通过安装插件实现的。尽管配置可插拔存储可能很复杂，但应用程序不会受到这些复杂性的影响。

分析函数

分析函数对行集执行聚合。分析函数有两种主要类型：窗口函数和聚合函数。聚合函数为每组行返回单个值（例如 SUM、AVG、MIN、MAX），而窗口函数为每行返回一个值。MySQL 和 PostgreSQL 都支持各种分析函数。MySQL 在 8.0 版本后引入了一些窗口函数，而 PostgreSQL 长期以来一直支持丰富的窗口函数，例如：

MySQL 支持几乎所有与 PostgreSQL 相同的窗口函数，但存在以下限制：

• 窗口函数不能用作 ‘UPDATE’ 或 ‘DELETE’ 语句的一部分。
• 窗口函数不支持 ‘DISTINCT’。
• 不支持 ‘NESTED’ 窗口函数。

管理和 GUI 工具

可以使用各种 GUI 工具（如 Oracle 的 SQL Developer、MySQL Workbench、DBeaver 和 OmniDB），远程访问 MySQL 数据库。为了监控 MySQL 数据库的性能和运行状况，流行的工具包括 Nagios、Cacti 和 Zabbix。

同样，PostgreSQL 可以使用 Oracle 的 SQL Developer、pgAdmin、OmniDB 和 DBeaver 进行图形化管理。为了监控 PostgreSQL 的性能和健康状况，Nagios、Zabbix 和 Cacti 等工具也被广泛使用。

性能

MySQL

优化 MySQL 数据库性能可能具有挑战性，因为它的参数选项有限，并且缺乏对许多索引类型的支持。由于没有完全的 SQL 标准兼容性，优化出高效且高性能的 SQL 查询就会变得困难。MySQL 也不是处理大型数据量的理想选择。虽然存在表空间以在多个磁盘之间分配数据，但它们仅限于 InnoDB 引擎，也无法适应表分区。要加快访问表的简单查询，创建 B 树索引可能是有益的。

PostgreSQL

PostgreSQL 对各种业务负载具有很强的适应性，包括 OLTP、OLAP 和数据仓库。它高度兼容 SQL 标准，允许编写高效的查询和 PL/PgSQL 程序。它支持各种索引（如 B 树、位图、部分索引和全文索引），以提高整体性能。在线重建索引和重新组织表，可以帮助有效地消除数据膨胀。PostgreSQL 还提供了多个内存分配的配置选项，分区表可以分布在多个表空间中，以有效地平衡磁盘 I/O。

安全

对于企业来说，数据库安全在防止数据非法访问方面起着至关重要的作用。安全访问是在数据库内的不同级别实现的，包括对象级别和连接级别。

MySQL

MySQL 通过角色和权限管理数据库、对象和连接访问。必须要使用 SQL 命令，来为每个用户授予他们连接的每个 IP 地址的连接权限，或者可以一次授予子网内多个 IP 地址的所有权限。

将数据库 “testdb” 上的所有权限，授予来自 IP 地址 “192.168.1.1” 的用户 “testuser” 的示例命令，如下所示：

GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'192.168.1.1' IDENTIFIED BY 'newpassword';

如果用户要从 192.168.1 子网内的所有 IP 进行连接，则命令将为：

GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'192.168.1.*' IDENTIFIED BY 'newpassword';

授予权限时，必须指定密码，否则用户将无法连接。

此外，MySQL 支持基于 SSL 的网络连接，可以通过 SE-Linux 模块提供安全性。MySQL 企业版中提供了与外部身份验证系统的集成，例如轻量级目录访问协议（LDAP）和访问权限管理（PAM）。

PostgreSQL

PostgreSQL 支持使用角色和通过 ‘GRANT’ 命令定义的权限，控制数据库对象和数据的访问。连接身份验证通过 “pg_hba.conf” 身份验证文件进行管理，该文件列出了 IP 地址、用户名和访问类型，提供了一种更直接、更可靠的方法。“pg_hba.conf” 文件中的示例条目，可能像下面这样：

host   database  user  address  auth-method  [md5 or trust or reject]

PostgreSQL 的开源版本支持基于 SSL 的连接，并且可以与外部身份验证系统集成，包括 LDAP、Kerberos 和 PAM，使其既高效又可靠。

扩展性

PostgreSQL 被认为是一种高度可扩展的系统，因为它提供了很多 MySQL 不支持的各种高级数据类型。这包括了网络地址类型、内置 UUID、几何/GIS 类型、可构建索引的 JSON，以及时区相关的时间戳。如果这些都还不足以让 PostgreSQL 打动你，您还可以添加运算符、数据类型和索引类型。

因此，如果您的应用程序正在处理非结构化数据，或其支持的任何独特数据类型，那么 PostgreSQL 可能是更好的选择。但是，如果您只处理基本的数值和字符数据类型，则两个数据库都应该可以正常工作。

支持和社区

PostgreSQL 和 MySQL 都有帮助社区来为用户提供支持。

PostgreSQL 拥有庞大的志愿者社区，他们通过邮件列表和 IRC 为用户提供免费建议。最重要的是，您还可以通过第三方提供商购买付费支持。您甚至可以通过浏览市场上各种有用的 PostgreSQL 书籍和手册，来处理故障。

MySQL 也有一个庞大的志愿者社区，他们花时间为您提供免费的建议和支持。您可以在第三方和 MySQL 网站上使用这种支持。除了免费的社区支持之外，Oracle 还为其所有产品的商业版本提供了 24/7 付费支持。与 PostgreSQL 一样，您也可以通过深入研究众多免费且有用的 MySQL 指南、书籍和教程，来进行故障处理。

您应该选择哪个？

总而言之，在这两个数据库之间进行选择，并不总是那么简单。由于无法给出完全正确的答案，最终关键还是取决于各种背景因素。

如果您正在寻找一个功能丰富的数据库，它可以顺利处理大型数据量和复杂查询，同时允许您将任何应用程序扩展到企业范围，那么您应该选择 PostgreSQL。

另一方面，如果您是初学者，正在寻找更易于管理和设置的数据库，同时需要可靠性、运行快速且易于理解，您可以尝试 MySQL。

如果您根本无法决定，一种选择是将它们都拿来试用一段时间，再做出最终的决定。