SQLite vs MySQL vs PostgreSQL：关系数据库管理系统比较

介绍

关系型数据模型将数据组织成行和列的表，在数据库管理工具中占主导地位。如今还有其他数据模型，包括NoSQL和NewSQL，但关系型数据库管理系统（RDBMS）仍然在全球范围内保持主导地位，用于存储和管理数据。

本文比较和对比了三种最广泛实现的开源RDBMS： SQLite、MySQL和PostgreSQL。具体来说，它将探讨每种RDBMS使用的数据类型、它们的优缺点以及最适合优化的情况。

A Bit About Database Management Systems

数据库是逻辑建模的信息集群，或者数据。另一方面，数据库管理系统（DBMS）是与数据库交互的计算机程序。DBMS允许您控制对数据库的访问、编写数据、运行查询以及执行与数据库管理相关的任何其他任务。

尽管数据库管理系统通常被称为“数据库”，但这两个术语并不可互换。数据库可以是任何数据集合，不仅仅是存储在计算机上的数据。相比之下，DBMS specifically 指的是允许您与数据库进行交互的软件。

所有数据库管理系统都有一个底层模型，该模型结构化数据的存储和访问方式。关系数据库管理系统是使用关系数据模型的DBMS。在这种关系模型中，数据被组织成表。在RDBMS的上下文中，表更正式地被称为关系。关系是一组元组，它们是表中的行，每个元组共享一组属性，这些属性是表中的列：

大多数关系数据库使用结构化查询语言（SQL）来管理和查询数据。然而，许多RDBMS使用它们自己特定的SQL方言，可能具有某些限制或扩展。这些扩展通常包括额外的功能，允许用户执行比标准SQL更复杂的操作。

数据类型和约束

每个列都被分配了一个数据类型，该类型规定了该列允许的输入类型。不同的关系数据库管理系统实现了不同的数据类型，它们并不总是直接可互换的。一些常见的数据类型包括日期、字符串、整数和布尔值。

在数据库中存储整数比在表中放置数字更为微妙。数值数据类型可以是有符号的，这意味着它们可以表示正数和负数，也可以是无符号的，这意味着它们只能表示正数。例如，MySQL的`tinyint`数据类型可以容纳8位数据，这相当于256个可能的值。该数据类型的有符号范围为-128到127，而无符号范围为0到255。

控制允许进入数据库的数据是很重要的。有时，数据库管理员会对表施加约束，以限制可以输入其中的值。约束通常适用于一个特定的列，但有些约束也可以适用于整个表。以下是SQL中常用的一些约束：

• 唯一：将此约束应用于列可确保该列中的两个条目不相同。
• 非空：此约束确保列中没有任何NULL条目。
• 主键：是唯一和非空的组合，主键约束确保列中没有NULL条目，并且每个条目都是唯一的。
• 外键：是一张表中的一列，它引用另一张表的主键。此约束用于将两个表连接在一起。对于写入过程成功，外键列的条目必须已经存在于父主键列中。
• 检查：此约束限制可以输入到列中的值的范围。例如，如果您的应用程序仅用于阿拉斯加居民，您可以在ZIP码列上添加一个检查约束，以仅允许输入99501到99950之间的条目。

如果您想了解更多关于数据库管理系统的信息，请查看我们的文章：比较NoSQL数据库管理系统和模型。

现在我们已经概述了关系型数据库管理系统的一般情况，让我们进入本文将涵盖的三个开源关系数据库中的第一个：SQLite。

SQLite

SQLite是一种自包含、基于文件的、完全开源的关系型数据库管理系统，以其可移植性、可靠性和即使在低内存环境下也具有强大性能而闻名。它的事务是ACID兼容的，即使在系统崩溃或断电的情况下也是如此。

SQLite项目的网站将其描述为“无服务器”数据库。大多数关系型数据库引擎都是以服务器进程实现的，程序通过进程间通信与主机服务器通信以传递请求。相比之下，SQLite允许访问数据库的任何进程直接读写数据库磁盘文件。这简化了SQLite的设置过程，因为它消除了配置服务器进程的需要。同样，对于将使用SQLite数据库的程序，无需任何配置：它们只需访问磁盘即可。

SQLite是免费且开源的软件，使用它无需特殊许可证。但是，该项目提供了几个扩展功能 —— 每个扩展功能只需一次性费用 —— 以帮助压缩和加密。此外，该项目还提供各种商业支持套餐，每年收费。

SQLite支持的数据类型

SQLite允许多种数据类型，分为以下存储类别：

在SQLite的上下文中，“存储类别”和“数据类型”这两个术语被认为是可互换的。如果您想了解更多关于SQLite的数据类型和SQLite类型亲和性的信息，请查阅SQLite在该主题上的官方文档。

SQLite的优点

• 小占地面积：顾名思义，SQLite库非常轻量级。虽然它在安装的系统中所占空间会有所不同，但它的空间占用量可以少于600KiB。此外，它是完全自包含的，这意味着您不需要在系统上安装任何外部依赖项即可使SQLite正常工作。
• 用户友好：SQLite有时被描述为“零配置”数据库，可立即使用。SQLite不作为服务器进程运行，这意味着它永远不需要停止、启动或重新启动，并且没有任何需要管理的配置文件。这些特性有助于简化从安装SQLite到将其集成到应用程序中的路径。
• 便携：与其他数据库管理系统不同，其他系统通常将数据存储为大批独立文件，整个SQLite数据库存储在单个文件中。此文件可以位于目录层次结构的任何位置，并且可以通过可移动媒体或文件传输协议共享。

SQLite的缺点

• 并发性有限：虽然多个进程可以同时访问和查询SQLite数据库，但任何给定时间只能有一个进程对数据库进行更改。这意味着虽然SQLite支持比大多数其他嵌入式数据库管理系统更多的并发性，但它不能像MySQL或PostgreSQL等客户/服务器关系数据库管理系统那样提供很多支持。
• 没有用户管理: 数据库系统通常带有对用户的支持，或者管理与数据库和表格具有预定义访问权限的连接。由于SQLite直接读写普通磁盘文件，所以唯一适用的访问权限是底层操作系统的典型访问权限。这使得SQLite对于需要多个具有特殊访问权限的用户的应用程序来说并不是一个好选择。
• 安全性: 使用服务器的数据库引擎在某些情况下可以比SQLite等无服务器数据库提供更好的保护，防止客户端应用程序中的错误。例如，客户端中的杂散指针无法损坏服务器上的内存。此外，由于服务器是一个单一持久性进程，客户端-服务器数据库可以比无服务器数据库更精确地控制数据访问。这允许进行更精细的锁定和更好的并发性。

何时使用SQLite

• 嵌入式应用: SQLite是需要可移植性且不需要未来扩展的应用程序的理想数据库选择。例如，单用户本地应用程序、移动应用程序或游戏。
• 替代磁盘访问: 在应用程序需要直接读写磁盘文件的情况下，使用SQLite可以获得使用SQL带来的额外功能和简单性的好处。
• 测试：对于许多应用程序来说，使用需要额外服务器进程的数据库管理系统(DBMS)来测试其功能可能有些大材小用。SQLite具有内存模式，可用于快速运行测试，而无需实际数据库操作的开销，使其成为测试的理想选择。

不适用SQLite的情况

• 处理大量数据：SQLite在技术上可以支持最大达到140TB的数据库，只要磁盘驱动器和文件系统也支持数据库的大小要求。然而，SQLite网站建议将接近1TB的任何数据库放置在集中式客户端-服务器数据库上，因为那么大或更大的SQLite数据库将难以管理。
• 高写入量：SQLite每次只允许进行一次写入操作，这严重限制了其吞吐量。如果您的应用程序需要大量写入操作或多个并发写入者，那么SQLite可能不适合您的需求。
• 需要网络访问：由于SQLite是一种无服务器数据库，它不直接提供对其数据的网络访问。这种访问是建立在应用程序中的。如果SQLite中的数据位于与应用程序分开的计算机上，则需要通过网络进行高带宽的引擎到磁盘的链接。这是一种昂贵且低效的解决方案，在这种情况下，客户端-服务器DBMS可能是更好的选择。

MySQL

根据DB-Engines排名，自2012年开始跟踪数据库流行度以来，MySQL一直是最受欢迎的开源关系型数据库管理系统。它是一个功能丰富的产品，支撑着许多世界上最大的网站和应用程序，包括Twitter、Facebook、Netflix和Spotify。由于其详尽的文档和庞大的开发者社区，以及在线上丰富的MySQL相关资源，入门MySQL相对来说比较简单。

MySQL旨在提高速度和可靠性，但这牺牲了对标准SQL的完全遵循。MySQL开发人员不断努力使其更加符合标准SQL，但它仍然落后于其他SQL实现。但它确实带有各种SQL模式和扩展，使其更接近符合标准。

与使用SQLite的应用程序不同，使用MySQL数据库的应用程序通过一个独立的守护进程访问它。因为服务器进程位于数据库和其他应用程序之间，所以可以更好地控制谁可以访问数据库。

MySQL已经激发了大量的第三方应用程序、工具和集成库，扩展了其功能，并帮助简化了使用。其中一些更广泛使用的第三方工具包括phpMyAdmin、DBeaver和HeidiSQL。

MySQL支持的数据类型

MySQL的数据类型可以分为三大类：数值类型、日期和时间类型、以及字符串类型。

数值类型:

日期和时间类型:

字符串类型:

MySQL的优势

• 流行度和易用性：作为世界上最流行的数据库系统之一，有大量的数据库管理员有使用MySQL的经验。同样，在印刷品和在线文档中有大量关于如何安装和管理MySQL数据库的资料。这包括一些第三方工具，比如phpMyAdmin，旨在简化开始使用数据库的过程。
• 安全性：MySQL自带一个脚本，可以帮助您提高数据库的安全性，设置安装密码安全级别，为root用户定义密码，移除匿名帐户，并移除默认对所有用户可访问的测试数据库。此外，与SQLite不同，MySQL支持用户管理，并允许您逐个用户地授予访问权限。
• 速度：通过选择不实现SQL的某些功能，MySQL开发人员能够将速度优先。尽管更近期的基准测试显示其他RDBMS（如PostgreSQL）在速度方面可以与MySQL相匹敌或至少接近，但MySQL仍然以极快的数据库解决方案而闻名。
• 复制：MySQL支持多种不同类型的复制，这是在两个或多个主机之间共享信息的实践，有助于提高可靠性、可用性和容错性。这对设置数据库备份解决方案或水平扩展数据库非常有帮助。

MySQL的缺点

• 已知限制：因为MySQL设计用于速度和易用性而不是完全的SQL兼容性，它具有某些功能限制。例如，它不支持FULL JOIN子句。
• 许可和专有功能：MySQL是双许可证软件，有一个在GPLv2下许可的免费开源社区版和几个以专有许可证发布的付费商业版。因此，一些功能和插件仅适用于专有版本。
• 开发放缓：自2008年MySQL项目被Sun Microsystems收购，后来又在2009年被Oracle Corporation收购以来，用户投诉称这个数据库管理系统的开发过程明显放缓，因为社区不再能够迅速应对问题并实施变更。

何时使用MySQL

• 分布式操作：MySQL的复制支持使其成为分布式数据库设置的理想选择，如主从或主主架构。
• 网站和Web应用：MySQL驱动着互联网上许多网站和应用程序。这在很大程度上归功于安装和设置MySQL数据库的简便性，以及它在长期内的速度和可扩展性。
• 预期未来增长：MySQL的复制支持有助于促进水平扩展。此外，升级到商业MySQL产品（如MySQL Cluster）相对直接，后者支持自动分片，另一种水平扩展过程。

何时不应使用MySQL

• SQL合规性是必要的：由于MySQL不尝试实现完整的SQL标准，因此该工具并不完全符合SQL标准。如果对于您的使用情况来说，完全或接近完全的SQL合规性是必需的，您可能希望使用一个更完全合规的DBMS。
• 并发和大数据量：虽然MySQL通常在读操作繁重的情况下表现良好，但并发读写可能会有问题。如果您的应用程序将有许多用户同时向其写入数据，那么另一个RDBMS如PostgreSQL可能是更好的数据库选择。

PostgreSQL

PostgreSQL，也称为Postgres，自称为“世界上最先进的开源关系型数据库”。它的创建目标是高度可扩展和符合标准。PostgreSQL是一种面向对象的关系型数据库，这意味着虽然它主要是关系型数据库，但它还包括一些更常与对象数据库相关联的特性，如表继承和函数重载。

Postgres 能够有效地同时处理多个任务，这是一种称为并发性的特性。它通过实现多版本并发控制（MVCC）来实现这一点，确保了事务的原子性、一致性、隔离性和持久性，也称为 ACID 符合性。

PostgreSQL 并不像 MySQL 那样被广泛使用，但仍然有许多第三方工具和库专为简化与 PostgreSQL 的工作而设计，包括pgAdmin和Postbird。

PostgreSQL 支持的数据类型

PostgreSQL 支持数字、字符串、日期和时间等数据类型，就像 MySQL 一样。此外，它还支持用于几何形状、网络地址、位串、文本搜索和 JSON 条目的数据类型，以及一些特殊的数据类型。

数字类型:

字符类型:

日期和时间类型:

几何类型:

网络地址类型:

位串类型:

文本搜索类型:

JSON 类型:

其他数据类型:

PostgreSQL的优势

• SQL兼容性:与SQLite或MySQL相比，PostgreSQL更加注重遵循SQL标准。根据官方PostgreSQL文档，PostgreSQL支持179个核心SQL:2011兼容性所需的功能中的160个，另外还支持一长串可选功能。
• 开源且社区驱动:作为一个完全开源的项目，PostgreSQL的源代码由一个庞大而忠诚的社区开发。同样，Postgres社区维护并贡献了许多在线资源，描述了如何使用该数据库管理系统，包括官方文档、PostgreSQL维基和各种在线论坛。
• 可扩展性:用户可以通过其目录驱动操作和动态加载，以编程方式在运行时扩展PostgreSQL。用户可以指定一个对象代码文件，如共享库，PostgreSQL将根据需要加载它。

PostgreSQL的缺点

• 内存性能：对于每个新的客户端连接，PostgreSQL都会fork一个新的进程。每个新进程分配大约10MB的内存，对于连接数量较多的数据库来说，这会很快累积起来。因此，对于简单的读密集型操作，PostgreSQL通常比其他关系型数据库（如MySQL）的性能要低。
• 普及度：尽管近年来使用更为广泛，但PostgreSQL在普及度上历史上一直落后于MySQL。这导致的一个后果是，能够帮助管理PostgreSQL数据库的第三方工具较少。同样，有经验管理Postgres数据库的数据库管理员相对较少，与有MySQL经验的管理员相比。

何时使用PostgreSQL

• 数据完整性很重要：自2001年以来，PostgreSQL已完全符合ACID，并实现了多版本并发控制，以确保数据保持一致，因此在数据完整性至关重要时，它是一种强大的关系型数据库选择。
• 与其他工具集成: PostgreSQL 兼容各种编程语言和平台。这意味着，如果您需要将数据库迁移到另一个操作系统或将其与特定工具集成，使用 PostgreSQL 数据库通常比使用其他数据库管理系统更容易。
• 复杂操作: Postgres 支持可以利用多个 CPU 的查询计划，以更快的速度回答查询。这与它对多个并发写入者的强大支持相结合，使其成为数据仓库和在线事务处理等复杂操作的不错选择。

不适合使用 PostgreSQL 的情况

• 速度至关重要: 以速度为代价，PostgreSQL 的设计考虑了可扩展性和兼容性。如果您的项目需要尽可能快的读取操作，PostgreSQL 可能不是最佳的数据库管理系统选择。
• 简单设置: 由于其丰富的功能集和对标准 SQL 的严格遵循，对于简单的数据库设置来说，Postgres 可能过于复杂。对于需要快速读取的操作，MySQL 通常是更实用的选择。
• 复杂复制：虽然PostgreSQL确实提供了强大的复制支持，但这仍然是一个相对较新的功能。一些配置，比如主-主架构，只能通过扩展实现。相比之下，MySQL的复制更成熟，并且许多用户认为MySQL的复制更容易实现，特别是对于那些缺乏必要的数据库和系统管理经验的用户。

结论

如今，SQLite、MySQL和PostgreSQL是全球最流行的三种开源关系数据库管理系统。每种系统都有其独特的功能和局限性，并且在特定情况下表现出色。在决定使用关系数据库管理系统时，有很多因素需要考虑，选择往往不像选择最快的或功能最多的那么简单。下次您需要关系数据库解决方案时，请务必深入研究这些以及其他工具，找到最适合您需求的工具。

如果您想了解更多关于SQL以及如何使用它来管理关系型数据库的信息，我们鼓励您参考我们的如何管理SQL数据库速查表。另一方面，如果您想了解非关系型（或NoSQL）数据库，请查看我们的NoSQL数据库管理系统比较。

参考资料

• DB-Engines排名
• SQLite官方文档
• SQLite是无服务器的
• SQLite的适用场景
• MySQL官方文档
• 比较MySQL和Postgres 9.0复制
• PostgreSQL官方文档
• PostgreSQL的时代终于到来了吗？