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

介绍

以行和列的表格组织数据的关系数据模型在数据库管理工具中占主导地位。今天还有其他数据模型，包括NoSQL和NewSQL，但关系数据库管理系统 (RDBMS)在全球范围内存储和管理数据方面仍然占主导地位。

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

一点关于数据库管理系统

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

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

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

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

注意： “标准 SQL”一词在本指南中多次出现。SQL 标准由美国国家标准协会 (ANSI)、国际标准化组织 (ISO)和国际电工委员会 (IEC)共同维护。每当本文提到“标准 SQL”或“SQL 标准”时，它指的是这些机构发布的 SQL 标准的当前版本。

需要注意的是，完整的 SQL 标准庞大而复杂：完整的核心 SQL:2011 合规性需要 179 个特性。正因为如此，大多数 RDBMS 并不支持整个标准，尽管有些 RDBMS 确实比其他的更接近完全合规。

数据类型和约束

每列都分配有一个数据类型，该数据类型指示该列中允许的条目类型。不同的 RDBMS 实现不同的数据类型，这些数据类型并不总是可以直接互换。一些常见的数据类型包括日期、字符串、整数和布尔值。

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

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

UNIQUE：将此约束应用于列可确保该列中没有两个条目是相同的。
NOT NULL：此约束确保列没有任何NULL条目。
PRIMARY KEY: 和的组合UNIQUE，NOT NULL约束PRIMARY KEY确保列中没有条目，NULL并且每个条目都是不同的。
FOREIGN KEY: AFOREIGN KEY是一个表中的一列，它引用PRIMARY KEY另一个表的列。此约束用于将两个表链接在一起。该列的条目FOREIGN KEY必须已经存在于父PRIMARY KEY列中，写入过程才能成功。
CHECK：此约束限制可以输入到列中的值的范围。例如，如果您的应用程序仅适用于阿拉斯加居民，您可以CHECK在邮政编码列上添加一个约束，以仅允许 99501 到 99950 之间的条目。

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

现在我们已经大致介绍了关系数据库管理系统，让我们转向本文将介绍的三个开源关系数据库中的第一个：SQLite。

SQLite

SQLite 是一个独立的、基于文件的、完全开源的 RDBMS，即使在低内存环境中也以其可移植性、可靠性和强大的性能而闻名。即使在系统崩溃或断电的情况下，它的事务也是符合 ACID 的。

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

SQLite 是免费的开源软件，使用它不需要特殊的许可。然而，该项目确实提供了几个扩展——每一个都需要一次性付费——这有助于压缩和加密。此外，该项目还提供各种商业支持包，每个包都需要支付年费。

SQLite 支持的数据类型

SQLite 允许多种数据类型，组织成以下存储类：

数据类型	解释
`null`	包括任何`NULL`值。
`integer`	有符号整数，根据值的大小存储在 1、2、3、4、6 或 8 个字节中。
`real`	实数或浮点值，存储为 8 字节浮点数。
`text`	使用数据库编码存储的文本字符串，可以是 UTF-8、UTF-16BE 或 UTF-16LE。
`blob`	任何 blob 数据，每个 blob 都完全按照输入的方式存储。

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

SQLite 的优点

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

SQLite 的缺点

有限并发：虽然多个进程可以同时访问和查询 SQLite 数据库，但在任何给定时间只有一个进程可以对数据库进行更改。这意味着虽然 SQLite 支持比大多数其他嵌入式数据库管理系统更高的并发性，但它不能支持像 MySQL 或 PostgreSQL 这样的客户端/服务器 RDBMS。
无用户管理：数据库系统通常支持用户，或具有对数据库和表的预定义访问权限的托管连接。因为 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 一直是最受欢迎的开源 RDBMS。它是一个功能丰富的产品，为许多世界上最大的网站和应用程序提供支持，包括 Twitter、Facebook 、Netflix 和 Spotify。MySQL 入门相对简单，这在很大程度上要归功于其详尽的文档和庞大的开发人员社区，以及在线 MySQL 相关资源的丰富。

MySQL 是为速度和可靠性而设计的，但以完全遵守标准 SQL 为代价。MySQL 开发人员不断致力于更紧密地遵循标准 SQL，但它仍然落后于其他 SQL 实现。但是，它确实带有各种 SQL 模式和扩展，使其更接近合规性。

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

MySQL 启发了大量第三方应用程序、工具和集成库，这些应用程序扩展了其功能并有助于使其更易于使用。这些第三方工具中使用更广泛的一些是phpMyAdmin、DBeaver和HeidiSQL。

MySQL 支持的数据类型

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

数字类型：

数据类型	解释
`tinyint`	一个非常小的整数。此数值数据类型的有符号范围是 -128 到 127，而无符号范围是 0 到 255。
`smallint`	一个小整数。此数字类型的有符号范围是 -32768 到 32767，而无符号范围是 0 到 65535。
`mediumint`	一个中等大小的整数。此数值数据类型的有符号范围是 -8388608 到 8388607，而无符号范围是 0 到 16777215。
`int`或者`integer`	一个正常大小的整数。此数值数据类型的有符号范围是 -2147483648 到 2147483647，而无符号范围是 0 到 4294967295。
`bigint`	一个大整数。此数值数据类型的有符号范围是 -9223372036854775808 到 9223372036854775807，而无符号范围是 0 到 18446744073709551615。
`float`	一个小的（单精度）浮点数。
`double`, `double precision`, 或`real`	一个正常大小（双精度）的浮点数。
`dec`, `decimal`, `fixed`, 或`numeric`	压缩定点数。此数据类型的条目的显示长度是在创建列时定义的，并且每个条目都遵循该长度。
`bool`或者`boolean`	布尔值是一种只有两个可能值的数据类型，通常是`true`或`false`。
`bit`	一种位值类型，您可以为其指定每个值的位数，从 1 到 64。

日期和时间类型：

数据类型	解释
`date`	一个日期，表示为`YYYY-MM-DD`。
`datetime`	显示日期和时间的时间戳，显示为`YYYY-MM-DD HH:MM:SS`。
`timestamp`	表示自Unix 纪元（1970 年 1 月 1 日 00:00:00）以来的时间量的时间戳。
`time`	一天中的某个时间，显示为`HH:MM:SS`。
`year`	以 2 位或 4 位格式表示的年份，默认为 4 位。

字符串类型：

数据类型	解释
`char`	一个固定长度的字符串；这种类型的条目在存储时在右侧用空格填充以满足指定的长度。
`varchar`	可变长度的字符串。
`binary`	与`char`类型类似，但是是指定长度的二进制字节字符串，而不是非二进制字符串。
`varbinary`	与`varchar`类型类似，但是是可变长度的二进制字节字符串，而不是非二进制字符串。
`blob`	最大长度为 65535 (2^16 – 1) 字节数据的二进制字符串。
`tinyblob`	最大长度为 255 (2^8 – 1) 字节数据的`blob`列。
`mediumblob`	最大长度为 16777215 (2^24 – 1) 字节数据的`blob`列。
`longblob`	最大长度为 4294967295 (2^32 – 1) 字节数据的`blob`列。
`text`	最大长度为 65535 (2^16 – 1) 个字符的字符串。
`tinytext`	最大长度为 255 (2^8 – 1) 个字符的`text`列。
`mediumtext`	最大长度为 16777215 (2^24 – 1) 个字符的`text`列。
`longtext`	最大长度为 4294967295 (2^32 – 1) 个字符的`text`列。
`enum`	枚举，它是一个字符串对象，它从创建表时声明的值列表中获取单个值。
`set`	与枚举类似，一个字符串对象可以有零个或多个值，每个值都必须从创建表时指定的允许值列表中选择。

MySQL的优点

流行性和易用性：作为世界上最流行的数据库系统之一，不乏有使用 MySQL 经验的数据库管理员。同样，有大量关于如何安装和管理 MySQL 数据库的印刷和在线文档。这包括许多第三方工具——例如 phpMyAdmin——旨在简化开始使用数据库的过程。
安全性：MySQL 安装了一个脚本，通过设置安装的密码安全级别、为root用户定义密码、删除匿名帐户以及删除默认情况下可访问的测试数据库来帮助您提高数据库的安全性所有用户。此外，与 SQLite 不同的是，MySQL 确实支持用户管理，并允许您逐个用户授予访问权限。
速度：通过选择不实现 SQL 的某些特性，MySQL 开发人员能够优先考虑速度。虽然最近的基准测试表明 PostgreSQL 等其他 RDBMS 在速度方面可以匹敌或至少接近 MySQL，但 MySQL 仍然享有超快速数据库解决方案的声誉。
复制：MySQL 支持多种不同类型的复制，这是在两台或多台主机之间共享信息以帮助提高可靠性、可用性和容错性的做法。这有助于设置数据库备份解决方案或水平扩展数据库。

MySQL的缺点

已知限制：因为 MySQL 是为速度和易用性而设计的，而不是完全符合 SQL 标准，所以它具有某些功能限制。例如，它缺乏对FULL JOIN子句的支持。
许可和专有功能：MySQL 是双重许可软件，有一个根据GPLv2许可的免费和开源社区版本，以及根据专有许可发布的几个付费商业版本。因此，某些功能和插件仅适用于专有版本。
开发缓慢：自从 MySQL 项目在 2008 年被 Sun Microsystems 收购，后来又在 2009 年被 Oracle 公司收购后，用户抱怨 DBMS 的开发进程明显放缓，因为社区不再有代理快速响应问题并实施更改。

何时使用 MySQL

分布式操作：MySQL 的复制支持使其成为分布式数据库设置（如主次或主次架构）的绝佳选择。
网站和网络应用程序：MySQL 为互联网上的许多网站和应用程序提供支持。这在很大程度上要归功于安装和设置 MySQL 数据库的容易程度，以及从长远来看它的整体速度和可扩展性。
预期的未来增长：MySQL 的复制支持有助于促进水平扩展。此外，升级到商业 MySQL 产品是一个相对简单的过程，例如支持自动分片的 MySQL Cluster，这是另一个水平扩展过程。

何时不使用 MySQL

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

PostgreSQL

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

Postgres 能够同时有效地处理多个任务，这一特性被称为并发。由于它实现了多版本并发控制（MVCC），它在没有读锁的情况下实现了这一点，它确保了事务的原子性、一致性、隔离性和持久性，也称为 ACID 合规性。

PostgreSQL 不像 MySQL 那样广泛使用，但仍有许多第三方工具和库旨在简化 PostgreSQL 的使用，包括pgAdmin和Postbird。

PostgreSQL 支持的数据类型

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

数字类型：

数据类型	解释
`bigint`	一个有符号的 8 字节整数。
`bigserial`	一个自动递增的 8 字节整数。
`double precision`	一个 8 字节双精度浮点数。
`integer`	一个有符号的 4 字节整数。
`numeric`或者`decimal`	一些可选择的精度，建议在精确度至关重要的情况下使用，例如货币金额。
`real`	一个 4 字节单精度浮点数。
`smallint`	带符号的 2 字节整数。
`smallserial`	一个自动递增的 2 字节整数。
`serial`	一个自动递增的 4 字节整数。

字符类型：

数据类型	解释
`character`	具有指定固定长度的字符串。
`character varying`或者`varchar`	具有可变但有限长度的字符串。
`text`	一个可变的字符串，长度不限。

日期和时间类型：

数据类型	解释
`date`	由日、月和年组成的日历日期。
`interval`	一个时间跨度。
`time`或者`time without time zone`	一天中的某个时间，不包括时区。
`time with time zone`	一天中的某个时间，包括时区。
`timestamp`或者`timestamp without time zone`	日期和时间，不包括时区。
`timestamp with time zone`	日期和时间，包括时区。

几何类型：

数据类型	解释
`box`	平面上的一个长方形盒子。
`circle`	飞机上的一个圆圈。
`line`	平面上的无限线。
`lseg`	平面上的线段。
`path`	平面上的几何路径。
`point`	平面上的几何点。
`polygon`	平面上的封闭几何路径。

网络地址类型：

数据类型	解释
`cidr`	IPv4 或 IPv6 网络地址。
`inet`	IPv4 或 IPv6 主机地址。
`macaddr`	媒体访问控制 (MAC) 地址。

位串类型：

数据类型	解释
`bit`	一个固定长度的位串。
`bit varying`	一个可变长度的位串。

文本搜索类型：

数据类型	解释
`tsquery`	文本搜索查询。
`tsvector`	文本搜索文档。

JSON 类型：

数据类型	解释
`json`	文本 JSON 数据。
`jsonb`	分解的二进制 JSON 数据。

其他数据类型：

数据类型	解释
`boolean`	一个逻辑布尔值，表示`true`或`false`。
`bytea`	“字节数组”的缩写，这种类型用于二进制数据。
`money`	一定数量的货币。
`pg_lsn`	PostgreSQL 日志序列号。
`txid_snapshot`	用户级事务 ID 快照。
`uuid`	一个普遍唯一的标识符。
`xml`	XML 数据。

PostgreSQL 的优点

SQL 合规性：与 SQLite 或 MySQL 相比，PostgreSQL 旨在严格遵守 SQL 标准。根据官方 PostgreSQL 文档，除了一长串可选功能之外，PostgreSQL 还支持完全核心 SQL:2011 合规所需的 179 种功能中的 160 种。
开源和社区驱动：一个完全开源的项目，PostgreSQL 的源代码是由一个庞大而专注的社区开发的。同样，Postgres 社区维护并贡献了大量描述如何使用 DBMS 的在线资源，包括官方文档、PostgreSQL wiki和各种在线论坛。
可扩展：用户可以通过其目录驱动的操作和动态加载的使用，以编程方式和动态扩展 PostgreSQL 。可以指定一个目标代码文件，例如共享库，PostgreSQL 将根据需要加载它。

PostgreSQL的缺点

内存性能：对于每个新的客户端连接，PostgreSQL 都会派生一个新进程。每个新进程分配了大约 10MB 的内存，对于有大量连接的数据库来说，这可以快速增加。因此，对于简单的重读操作，PostgreSQL 通常不如 MySQL 等其他 RDBMS 的性能。
流行度：尽管近年来使用更广泛，但 PostgreSQL 在流行度方面历来落后于 MySQL。这样做的一个后果是，可以帮助管理 PostgreSQL 数据库的第三方工具仍然较少。同样，与具有 MySQL 经验的管理员相比，具有管理 Postgres 数据库经验的数据库管理员数量并不多。

何时使用 PostgreSQL

数据完整性很重要：自 2001 年以来，PostgreSQL 已完全符合 ACID，并实施多版本货币控制以确保数据保持一致，使其成为数据完整性至关重要时 RDBMS 的有力选择。
与其他工具集成：PostgreSQL 与多种编程语言和平台兼容。这意味着，如果您需要将数据库迁移到另一个操作系统或将其与特定工具集成，那么使用 PostgreSQL 数据库可能比使用另一个 DBMS 更容易。
复杂操作：Postgres 支持可以利用多个 CPU 的查询计划，以便以更快的速度回答查询。再加上它对多个并发写入者的强大支持，使其成为数据仓库和在线事务处理等复杂操作的绝佳选择。

何时不使用 PostgreSQL

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

结论

如今，SQLite、MySQL 和 PostgreSQL 是世界上最流行的三种开源关系数据库管理系统。每个都有自己独特的功能和局限性，并且在特定场景中表现出色。在决定一个 RDBMS 时，有很多变量在起作用，而且选择很少像选择最快的一个或具有最多功能的一个那么简单。下次您需要关系数据库解决方案时，请务必深入研究这些工具和其他工具，以找到最适合您需求的工具。

介绍