简介

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

根据这三个关系型数据库的优缺点，你可以挑选一个最适合你项目的数据库，比如如果在安卓或 iOS 客户端需要本地存储，也许最轻量的 SQLite 就可以满足需要。而如果更复杂一些的场景，则可能要在服务器端用 MySQL 或 PostgreSQL 搭建更强大的功能。如果你需要实现搜索的话，甚至可能需要使用 Elastic Search, Solr 或卡拉搜索。

关系数据模型以表中行与列的形式组织数据，在数据库管理工具中占主导地位。今天还有其他的数据模型，包括 NoSQL 和 NewSQL，但关系数据库管理系统 （RDBMSs） 在存储和管理数据方面仍然占世界范围的主导地位。

关于数据库管理系统

数据库是逻辑建模的信息或数据群集。另一方面，数据库管理系统 （DBMS） 是一个与数据库交互的计算机程序。DBMS 允许你控制对数据库的访问、写入数据、查询以及执行与数据库管理相关的其他任务。尽管数据库管理系统通常称为"数据库"，但两个术语是不可互换的。数据库可以是任何数据的集合，而不仅仅是存储在计算机上的数据集合，而 DBMS 是允许你与数据库交互的软件。

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

关系数据库-rdbms的比较1

大多数的关系数据库使用结构化查询语言（SQL） 来管理和查询数据。但是，许多 RDBMS 使用自己特定的 SQL 语言，这些语言具有某些限制及扩展。这些扩展通常包含额外的特性，允许用户执行比标准 SQL 更复杂的操作。

注意： 在本指南中，"标准 SQL"一词出现多次。SQL 标准（standard SQL）由美国国家标准协会 （ANSI）、国际标准化组织 （ISO） 和国际电工委员会 （IEC） 共同维护。本文提到"标准 SQL"或"SQL 标准"时，指的是这些机构发布的 SQL 标准的当前版本。
应该注意的是，完整的 SQL 标准是庞大而复杂的：完整的核心 SQL：2011 合规需要 179 个功能。因此，大多数 RDBMS 不支持整个标准，尽管有些系统比其他的更接近完全合规。

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

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

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

• UNIQUE 此约束应用于列可确保该列中没有两个条目是相同的。
• NOT NULL 此约束可确保列没有任何 NULL 条目。
• PRIMARY KEY 此约束是UNIQUE和NOT NULL约束相结合，确保列没有条目为NULL，并且每个条目都不同。
• FOREIGN KEYFOREIGN KEY是一个表中的一列，它引用另一个表的PRIMARY KEY。此约束用于将两个表链接在一起： FOREIGN KEY列的条目必须已经存在于父PRIMARY KEY列中，写入过程才算成功。
• CHECK 此约束限制了可以输入到列中的值的范围。例如，如果应用程序仅面向阿拉斯加居民，你可以在邮政编码列上添加一个 CHECK 约束，只允许输入 99501 到 99950 之间的内容。
• DEFAULT 为给定列提供默认值。除非指定了另一个值，否则 SQLite 会自动输入默认值。
• INDEX 该约束有助于从表中更快地检索数据，类似教科书中的索引: 无需检查表中的每个条目，只需检查索引列中的条目即可找到所需的结果。

如果想进一步了解 DBMS 的信息，请查看关于 SQL 和 NoSQL 数据库及不同数据库模型的文章。

请注意，虽然数据库查询 (SELECT 语句）是个非常常见的操作，但如果你需要实现搜索功能（分词、跨列搜索或毫秒级的响应时间），你需要的其实不是 SQL 查询，而是全文索引（请见下文全文索引章节）。如果需要在数据库上实现搜索功能的话，请尝试卡拉搜索。

现在我们已经大致介绍了 RDBMS，接下来我们看一下本文将介绍的三个开源关系数据库中的第一个: SQLite。

SQLite

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

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

SQLite 是免费的开源软件，使用时无需特殊许可。然而，该项目提供了几种扩展——每种都是一次性收费的——来帮助压缩和加密。此外，该项目还提供各种商业支持包，每个包按年收取费用。

SQLite 支持的数据类型

SQLite 支持多种数据类型，如下表:

Data Type

Explanation

null

Includes any NULL values.

integer

有符号的整数，根据值的大小存储在1、2、3、4、6或8个字节中。

real

实际数字或浮点值，存储为 8 字节浮点数。

text

使用数据库编码存储的文本字符串，可以是 UTF-8、UTF-16BE 或 UTF-16LE。

blob

任何的二进制对象，每个 Blob 的存储与输入完全一样。

在 SQLite 环境下，术语"存储类"和"数据类型"被认为是可互换的。如果你想了解 SQLite 的数据类型和 SQLite 类型相关性的详细信息，请查看SQLite关于这个主题的官方文档。

优势

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

劣势

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

以下情况推荐使用 SQLite

• 嵌入式应用程序：SQLite 是需要可移植性且不需要未来扩展的应用程序的数据库的最佳选择。示例包括单用户本地应用程序和移动应用程序或游戏。
• 磁盘访问替换:在应用程序需要直接读写文件到磁盘的情况下，使用 SQLite 来实现 SQL 附带的其他功能和简单性可能是有益的。
• 测试: 对于许多应用程序来说，用一个使用附加服务器进程的 DBMS 来测试其功能可能是多余的。SQLite 有一个内存模式，可用于快速运行测试，而没有实际数据库操作的开销，这使得它成为测试的理想选择。

以下情况不推荐使用 SQLite

• 需要处理大量数据：只要磁盘驱动器和文件系统支持数据库的大小要求，SQLite 可以在技术上支持高达 140TB 的数据库。但是，SQLite 网站建议将任何接近 1TB 的数据库放在一个集中的 client-server 数据库中，因为如此大小或更大的 SQLite 数据库将很难管理。
• 高写入：SQLite 在任何给定时间内只允许进行一次写入操作，这极大地限制了其吞吐量。如果应用程序需要大量的写入操作或多个并发写入，SQLite 可能无法满足需求。
• 需要网络访问: 因为 SQLite 是一个无服务器的数据库，所以它不提供对其数据的直接网络访问。此访问内置于应用程序中，因此如果 SQLite 中的数据与应用程序位于不同的机器上，则需要跨网络的高带宽引擎到磁盘链接。这是一个昂贵、低效的解决方案。在这种情况下，client-server数据库管理系统可能是一个更好的选择。

MySQL

根据 DB-Engines 的排名，自2012年该网站开始跟踪数据库流行度以来，MySQL 一直是最受欢迎的开源 RDBMS。这是一款功能丰富的产品，为许多世界上最大的网站和应用程序提供支持，包括 Twitter, Facebook, Netflix 和 Spotify。一开始使用 MySQL 相对简单，这在很大程度上要归功于其详尽的文档和庞大的开发人员社区，以及丰富的与 MySQL 相关的在线资源。

MySQL 专为速度和可靠性而设计，但代价是完全遵循标准的 SQL。MySQL 开发人员一直在努力更加严格地遵守标准的 SQL，但是它仍然落后于其他的 SQL 实现。确实如此，然而它带有各种各样的 SQL 模式和扩展，使它更接近于合规性。与使用 SQLite 的应用程序不同，使用 MySQL 数据库的应用程序通过单独的后台常驻进程访问它。由于服务器进程位于数据库和其他应用程序之间，因此它可以更好地控制谁有权访问数据库。

MySQL 启发了大量的第三方应用程序、工具和集成库，它们扩展了 MySQl 的功能，使其更容易使用。这些第三方工具中使用较为广泛的有 phpMyAdmin、DBeaver 和 HeidiSQL。

如果需要安装 MySQL，请参考如何在MySQL中创建新用户并授权权限和 Ubuntu如何安装 MySQL。

支持的数据类型

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

数字类型：

Data Type

Explanation

tinyint

非常小的整数。此数值类型的有符号范围是 -128 到 127，而无符号范围是 0 到 255。

smallint

小的整数。此数值类型的有符号范围是 -32768 到 32767，而无符号范围是 0 到 65535。

mediumint

中等大小的整数。此数值类型的有符号范围是 -8388608 到 8388607，而无符号范围是 0 到 16777215。

int or integer

一个正常大小的整数。此数字类型的有符号范围为 -2147483648 到 2147483647，而无符号范围为 0 到 4294967295。

bigint

较大的整数。此数字类型的有符号范围为 -9223372036854775808 至 9223372036854775807，而无符号范围为 0 至 184467407377709551615。

float

较小的（单精度）浮点数。

double, double precision, or real

正常大小的（双精度）浮点数。

dec, decimal, fixed, or numeric

定点数字。此数据类型的条目显示长度是在创建列时定义的，每个条目都遵循该长度。

bool or boolean

一种只有两个值的数据类型，真或假。

bit

比特，你可以为其指定每个值的位数，范围从1到64。

日期和时间类型：

Data Type

Explanation

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 位。

字符串类型：

Data Type

Explanation

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） 字符的"文本"列。

mediumtext

最大长度为 16777215 （2^24 - 1） 字符的"文本"列。

longtext

最大长度为 4294967295 （2^32 - 1） 字符的"文本"列。

enum

枚举，是一个字符串对象，从创建表时声明的值列表 (list) 中获取一个值。

set

与枚举类似，该字符串对象可以有零个或多个值，每个值必须从创建表时指定的允许值列表 (list) 中选择。

优势

• 流行度和易用性：作为世界上最受欢迎的数据库系统之一，有很多具有使用 MySQL 经验的数据库管理员。同样，关于如何安装和管理 MySQL 数据库，也有大量的印刷和在线文档，以及许多第三方工具（如 phpMyAdmin），旨在简化数据库的入门过程。
• 安全性：MySQL 附带了一个脚本，通过设置配置的密码的安全级别、为根用户定义密码、删除匿名帐户以及删除默认情况下所有用户都可以访问的测试数据库等措施，来帮助你提高数据库的安全性。此外，与 SQLite 不同，MySQL 支持用户管理，允许你按用户授予访问权限。
• 速度：通过选择不实现 SQL 的某些功能，开发人员能够优先考虑速度。虽然最近的基准测试表明，其他 RDBMS（如 PostgreSQL）在速度方面可以匹配或至少接近 MySQL，但 MySQl 仍被誉为速度极快的数据库解决方案。
• 复制：MySQL 支持多种不同类型的复制，即在两台或多台主机之间共享信息，以帮助提高可靠性、可用性和容错能力。这对于设置数据库备份解决方案或横向扩展数据库非常有用。

劣势

• 已知的限制：由于 MySQL 旨在加快速度和易用性，而不是完全符合 SQL 标准，因此具有某些功能限制。例如，缺乏对 FULL JOIN 分句的支持。
• 许可和专有功能：MySQL 是双许可软件，一个在 GPLv2 下许可的免费开源社区版，多个在专有许可下发布的付费商业版。因此，一些功能和插件只能在专有版本中使用。
• 开发速度变慢：自 2008 年由 Sun 微系统公司收购 MySQL 项目，随后于 2009 年被 Oracle 公司收购以来，用户一直抱怨 DBMS 的开发过程已显著放缓，因为社区不再有机构快速响应问题并实施更改。

以下情形推荐使用 MySQL

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

以下情形不推荐使用 MySQL

• SQL 合规性是必要的：由于 MySQL 并不试图实现完整的 SQL 标准，因此并不完全符合 SQL 标准。如果你的用例必须实现完整或近乎完整的 SQL 合规性，则可能需要使用更符合要求的 DBMS。
• 并发性和巨大的数据量：尽管 MySQL 通常在读密集型操作中表现良好，但并发的读写可能会有问题。如果应用程序有许多的用户在同一时间向其写入数据，那么其他的 RDBMS（如 PostgreSQL）可能是一个更好的选择。

PostgreSQL

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

Postgres 能够同时高效地处理多个任务，这一特性称为并发性。它无需读取锁实现了这一点，归功于它的多版本并发控制 (MVCC) 的实现。它确保了事务的原子性、一致性、隔离性和持久性，也称为 ACID 合规性。

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

支持的数据类型

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

数值型

Data Type

Explanation

bigint

有符号的 8 字节整数。

bigserial

一个自动递增的 8 字节整数。

double precision

一个 8 字节双精度浮点数。

integer

有符号的 4 字节整数。

numeric or decimal

一种可选的精度，建议在精确性至关重要的情况下使用，如货币金额。

real

一个 4 字节单精度浮点数。

smallint

A signed 2 byte integer.

smallserial

An autoincrementing 2 byte integer.

serial

An autoincrementing 4 byte integer.

字符串型

Data Type

Explanation

character

具有指定固定长度的字符串。

character varying or varchar

具有可变但长度有限制的字符串。

text

具有可变且长度无限制的字符串。

日期和时间型

Data Type

Explanation

date

由日、月和年组成的日历。

interval

时间跨度。

time or time without time zone

一天中的时间，但不包括时区。

time with time zone

一天中的时间，包括时区。

timestamp or timestamp without time zone

日期和时间，不包括时区。

timestamp with time zone

日期和时间，包括时区。

几何形状

Data Type

Explanation

box

平面上的矩形。

circle

A circle on a plane.

line

平面上的无限制的线。

lseg

平面上的线段。

path

平面上的几何路径。

point

平面上的几何点。

polygon

平面上的闭合几何路径。

Network address

Data Type

Explanation

cidr

An IPv4 or IPv6 network address.

inet

An IPv4 or IPv6 host address.

macaddr

A Media Access Control (MAC) address.

Bit string

Data Type

Explanation

bit

固定长度的位字符串。

bit varying

可变长度的位字符串。

Text search

Data Type

Explanation

tsquery

文本搜索的查询。

tsvector

文档的搜索查询。

JSON

Data Type

Explanation

json

文本化的 JSON 数据。

jsonb

分解的二进制 JSON 数据。

其他的数据类型

Data Type

Explanation

boolean

布尔，表示"真"或"假"。

bytea

“字节数组”的缩写，用于二进制数据。

money

An amount of currency.

pg_lsn

PostgreSQL 日志的序列号。

txid_snapshot

用户级的事务 ID 快照。

uuid

通用唯一标识符。

xml

XML data.

优势

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

劣势

• 内存性能：对于每个新的客户端连接，PostgreSQL 都会分出一个新进程。每个新进程被分配大约 10MB 的内存，对于具有大量连接的数据库，会迅速加剧负担。因此，对于简单的密集性读操作，PostgreSQL 的性能通常不如其他 RDBMSs，如 MySQL。
• 流行度：虽然近年来使用更为广泛，但就受欢迎程度而言，PostgreSQL 在历史上落后于 MySQL。一个后果是，能够帮助管理 PostgreSQL 数据库的第三方工具仍然很少。同样，与拥有 MySQL 经验的数据库管理员相比，拥有 Postgres 相关经验的数据库管理员并不多。

推荐使用PostgreSQL 的情形

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

不推荐使用PostgreSQL 的情形

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

全文索引

对于全文索引，特别是分词和倒排引擎，MySQL 和 PostgreSQL 都有部分支持。然而这两个数据库的主要设计出发点都是关系型数据库，而索引只是其附带功能，这也就意味着如果你需要灵活地在数据库上进行数据搜索的话，需要花费较多的精力和时间来配置，同时用户体验可能并不好（比如查询慢，很难实现跨列、跨字段查询，索引管理困难等）。

因此，如果需要在数据库上进行全文索引实现搜索功能，我们建议使用卡拉搜索，几部配置即可实现分词、索引管理和毫秒级的搜索体验。当然如果你的团队里有精通 Elastic Search 的搜索工程师，ES 也是一个选项。

结论

如今，SQLite、MySQL 和 PostgreSQL 是世界上最流行的三个开源 RDBMS。每个都有其独特的功能和限制，并在特定的方案中表现出色。在决定一个 RDBMS 时，有相当多的变量在起作用，很少像选择最快的或选择功能最多的那样简单。当你需要一个关系数据库解决方案时，请务必深入研究这些以及其他的工具，找到最适合需求的工具。

如果想了解更多有关 SQL 以及如何使用它来管理关系数据库的信息，建议你参阅"如何管理 SQL 数据库备忘录"。另一方面，如果想了解非关系（或 NoSQL）数据库，请查阅 NoSQL 数据库管理系统的比较 及其它文章包括安装 MongoDB 查询等。

参考文章

原文出处

MySQL 中如何创建用户

ElasticSearch yaml 配置教程

Ubuntu 如何安装 MySQL

原文作者：卡拉先生
原文链接：https://kalasearch.cn/blog/sqlite-mysql-postgres-comparison/
文章来源：卡拉搜索