PostgreSQL vs MySQL：全栈开发者如何做选择？

选择正确的数据库对于全栈开发人员来说是一个非常的关键决策，它会影响从应用程序性能到可扩展性的各个方面。

PostgreSQL 和 MySQL 是两个最流行的开源关系数据库管理系统。每个都具有自己的一套优势，适合不同的发展需求。

让我们探讨这些差异，深入了解 Linux 上的安装，并讨论安全和备份策略，以帮助各位为下一个项目做出明智的决定。

MySQL 发展沿革

• Unireg 是 MySQL 的最初源码库，于1981 年启动。

• MySQL 公司于1995 年 在瑞典成立。

• 2000年，MySQL开源，可供所有人访问和使用。

• 2001年，Marten Mickos当选为MySQL首席执行官。

• 2002年，MySQL除了瑞典总部外，还在美国设立了总部。

• 2003 年，MySQL 与 SAP 建立了合作伙伴关系，在 MySQL 中开发了许多功能，同时考虑到 SAP。

• 2005年，MySQL推出了MySQL Network。Oracle 收购了 Innobase，它是 MySQL InnoDB 存储的后端。

• 2008年，MySQL被Sun Microsystems收购。

• 2009 年，Sun Microsystems 和 Oracle 签订了最终协议，Oracle 收购了 Sun Microsystems。

现在，让我们再了解一下MySQL的历史：

• mSQL 数据库系统旨在使用定制的快速低级 (ISAM) 例程连接存储在表中的数据。

• ISAM指的是索引顺序存取方式，是一种文件管理系统。它是一种帮助顺序访问表中的记录的技术，即按照记录输入表的顺序，或者借助索引随机访问。每个索引都可用于定义表中记录的不同顺序。

• ISAM最初是由IBM在VSAM（虚拟存储访问方法）和关系数据库开发之前开发的。

• 当测试 mSQL 以查看其效率时，发现 mSQL 对于当前的需求来说不够快或不够灵活。

• MySQL 最初是基于低级语言 ISAM 的 mSQL 创建供个人使用的。

• MySQL 以联合创始人 Monty Widenius 的女儿 My 命名。标志中，一只海豚被称为“Sakila”。

PostgreSQL 发展沿革

PostgreSQL 是由 UCBerkerly 计算机科学教授 Michael Stonebraker 创建的。它最初被称为 Postgres。它是由 Stonebreaker 教授于 1986 年启动的，作为后续项目和后 Ingres 项目，旨在克服当代数据库系统的问题。PostgreSQL 现在是最先进的开源数据库。

历史沿革，简介：

• 1977-1985：开发了一个名为 INGRES 的项目，完成了关系数据库的概念验证。

• 1994 年被 Computer Associates 收购。

• 1986-1994: 后备研究人员

• INGRES 中概念的发展，重点是面向对象和查询语言 Quel。

• INGRES 的代码库未用作 POSTGRES 的基础。

• 商业化为 Illustra（被 Informix 收购，被 IBM 收购）。

• 1994-1995：Postgres95

• 1994 年添加了对 SQL 的支持。

• 1995 年作为 Postgres95 发布。

• 1996 年重新发布为 PostgreSQL 6.0。

• PostgreSQL 全球开发团队成立。

事务支持和 ACID 合规性

PostgreSQL 和 MySQL 都支持 ACID（原子性、一致性、隔离性、持久性）原则，这对于可靠的事务管理至关重要。

PostgreSQL 因其对复杂事务的强大支持和严格的 ACID 合规性而闻名。它特别适合需要可靠交易的应用程序，例如财务或医疗记录管理。

MySQL 及其 InnoDB 存储引擎也提供了强大的 ACID 合规性，但其默认事务隔离级别是“可重复读取”，以平衡性能和一致性。

考虑这些事务示例以了解 PostgreSQL 和 MySQL 之间的 SQL 语法细微差别：

• 在 PostgreSQL 中，要插入新员工并将其分配给项目，您可以使用带有序列 ID 的事务块：

BEGIN;INSERT INTO employees (name, role, hire_date) VALUES ('Jane Doe', 'Developer', '2023-01-10');UPDATE project_assignments SET project_id = 2 WHERE employee_id = CURRVAL('employees_id_seq');COMMIT;

• 在 MySQL 中，类似的操作可能如下所示，利用LAST_INSERT_ID()：

START TRANSACTION;INSERT INTO employees (name, role, hire_date) VALUES ('John Smith', 'Project Manager', '2023-02-15');UPDATE projects SET status = 'Active' WHERE id = LAST_INSERT_ID();COMMIT;

性能和可扩展性

在评估 PostgreSQL 和 MySQL 的性能和可扩展性时，必须考虑应用程序的具体用例。MySQL 传统上因其高速读取操作而受到青睐，这使其成为内容管理系统或博客平台等读取密集型应用程序的绝佳选择。

另一方面，PostgreSQL 在需要大量写入和复杂查询的场景中表现出色，例如分析应用程序或具有复杂数据关系的系统。

例如：

• MySQL 适用于读取量大的场景：考虑一个博客平台，其中大部分数据库操作都是读取的（获取帖子、评论等）。MySQL 的默认存储

• 引擎 InnoDB 针对读取操作进行了高度优化，可提供快速的数据检索。

SELECT post_title, post_content FROM blog_posts WHERE post_date > '2023-01-01';

该查询在 MySQL 数据库上运行，受益于 MySQL 的读取优化，可以有效地从年初获取博客文章。

• 适用于大量写入场景的 PostgreSQL：在处理金融交易的应用程序中，数据完整性和复杂写入至关重要，PostgreSQL 的高级事务管理大放异彩。

BEGIN;INSERT INTO transactions (user_id, amount, transaction_date) VALUES (1, -100.00, '2023-04-05');UPDATE accounts SET balance = balance - 100.00 WHERE user_id = 1;COMMIT;

这个事务保证了原子性和一致性，展示了 PostgreSQL 在处理复杂的、写入量大的操作方面的实力。

可扩展性和高级功能

PostgreSQL

PostgreSQL 具有高度可扩展性，支持大量开箱即用的高级功能，包括：

• 高级数据类型：PostgreSQL支持几何数据类型和自定义类型，甚至允许像JSONB这样的复杂类型，使开发人员能够高效地存储和查询JSON格式的数据。

SELECT * FROM orders WHERE customer_details->>'city' = 'San Francisco';

此查询利用 JSONB 数据类型来高效查询表中存储的 JSON 数据orders，查找来自旧金山客户的订单。

• 全文搜索：PostgreSQL提供了强大的文本搜索功能，可以快速搜索大量文本数据。

SELECT * FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('english', 'PostgreSQL & databases');

此示例演示搜索同时包含“PostgreSQL”和“数据库”的文章，展示 PostgreSQL 的全文搜索功能。

MySQL

MySQL 的可扩展性包括以下功能：

• JSON 支持：虽然不如 PostgreSQL 的 JSONB 那么先进，但 MySQL 的 JSON 数据类型允许高效存储和查询 JSON 文档。

SELECT * FROM products WHERE JSON_EXTRACT(info, '$.manufacturer') = 'Acme';

products此查询在表中搜索info列（存储为 JSON）包含“Acme”作为制造商的产品。

开发者工具和生态系统

PostgreSQL 工具：

• pgAdmin：最流行且功能丰富的 PostgreSQL 开源管理和开发工具。pgAdmin 下载

• PostGIS：一个扩展，为 PostgreSQL 添加了对地理对象的支持，将其转变为空间数据库。PostGIS 文档

MySQL 工具：

• MySQL Workbench：用于数据库设计、SQL 开发、管理任务等的集成工具环境。MySQL 工作台下载

• phpMyAdmin：一个用 PHP 编写的免费软件工具，旨在通过 Web 处理 MySQL 的管理。phpMyAdmin 下载

安全和备份

安全和备份策略对于任何数据库管理系统都至关重要，可确保数据完整性和可用性。

PostgreSQL 和 MySQL 都支持对传输中的数据进行 SSL 加密、用于细粒度权限管理的基于角色的访问控制，以及通过“chroot”监狱增强安全性的能力。

• 具有压缩和加密功能的 PostgreSQL 备份：

pg_dump mydatabase | gzip | openssl enc -aes-256-cbc -e > mydatabase_backup.sql.gz.enc

此命令创建 PostgreSQL 数据库的压缩和加密备份mydatabase，用于gzip压缩和openssl加密。

• 带压缩和加密的 MySQL 备份：

mysqldump -u user -p mydatabase | gzip | openssl enc -aes-256-cbc -e > mydatabase_backup.sql.gz.enc

与 PostgreSQL 示例类似，此命令执行 MySQL 数据库的备份mydatabase，并应用压缩和加密以确保安全性和效率。

有关深入的安全和备份策略，请参阅官方文档：

• PostgreSQL：安全性|备份

• MySQL：安全性|备份

在 Linux 上安装两个系统

PostgreSQL

在基于 Ubuntu 或 Debian 的系统上，安装 PostgreSQL 非常简单：

sudo apt updatesudo apt-get install postgresql postgresql-contrib

更多详细信息请参阅PostgreSQL 官方安装指南。

MySQL

同样，对于 MySQL：

sudo apt updatesudo apt-get install mysql-server

MySQL 安装文档提供了全面的说明。

结语

选择 PostgreSQL 还是 MySQL 进行全栈开发取决于项目的具体要求、数据的性质以及您打算执行的操作的复杂性。

PostgreSQL 提供无与伦比的可扩展性和高级功能，使其成为需要强大数据完整性、复杂查询和广泛数据类型的项目的理想选择。它处理写入量大的应用程序的能力以及对高级数据结构和全文搜索的支持使其成为处理复杂数据关系的分析和应用程序的强大动力。

另一方面，MySQL 在需要高速读取操作和直接可扩展性的场景中表现出色，使其成为性能和简单性至关重要的 Web 应用程序、内容管理系统和博客平台的首选。它的广泛采用，加上强大的社区支持和大量的开发工具，确保了可靠和高效的开发体验。

这两个数据库都配备了全面的安全功能和灵活的备份选项，确保数据完整性和灾难恢复功能从头开始内置到您的应用程序中。围绕 PostgreSQL 和 MySQL 的丰富生态系统为开发人员提供了一系列工具和资源，进一步增强了开发体验，并提供了解决几乎所有数据库挑战的途径。

最终，在 PostgreSQL 和 MySQL 之间做出决定时，应仔细考虑项目当前的需求和未来的增长。这两个数据库都在初创公司和科技巨头的手中证明了其可靠性和性能，展示了它们支持最苛刻的应用程序和最具创新性项目的能力。

通过了解每个人的优势和能力，开发人员可以做出最适合其全栈项目要求的明智决策，为成功奠定坚实的基础。