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

运维资讯 2024-04-18 共饮一杯 手机阅读

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

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';