对比分析：SQLite、MySQL和PostgreSQL的关系数据库管理系统

发表时间: 2020-03-23 14:00

介绍

该关系数据模型，这在数据库管理工具的行和列，占主导地位的表组织数据。今天，还有其他数据模型，包括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实现不同的数据类型，这些数据类型并不总是可以直接互换的。一些常见的数据类型包括日期，字符串，整数和布尔值。

数值数据类型既可以签署，这意味着它们可以同时代表正数和负数，或无符号的，这意味着它们只能表示正数。例如，MySQL的tinyint数据类型可以容纳8位数据，这等于256个可能的值。此数据类型的有符号范围是-128至127，而无符号范围是0至255。

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

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

如果您想了解有关数据库管理系统的更多信息，请查看关于理解SQL和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任何数据块，每个数据块的存储都与输入的数据完全相同。

在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一blob列，最大长度为255（2 ^ 8-1）个字节的数据。mediumblob一blob列，最大长度为16777215（2 ^ 24-1）个字节的数据。longblob一blob列，最大长度为4294967295（2 ^ 32-1）字节数据。text一个字符串，最大长度为65535（2 ^ 16-1）个字符。tinytext一text列，最大长度为255（2 ^ 8-1）个字符。mediumtext一text列，最大长度为16777215（2 ^ 24-1）个字符。longtext一text列，最大长度为4294967295（2 ^ 32-1）个字符。enum枚举，它是一个字符串对象，它从创建表时声明的值列表中获取单个值。set与枚举类似，字符串对象可以具有零个或多个值，每个值都必须从创建表时指定的允许值列表中选择。

MySQL的优点

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

MySQL的缺点

已知局限性：由于MySQL是为了提高速度和易用性而不是完全符合SQL的要求而设计的，因此它具有某些功能局限性。例如，它不支持FULL JOIN子句。
许可和专有功能：MySQL是双重许可的软件，具有根据GPLv2许可的免费开源社区版本，以及根据专有许可发行的若干付费商业版本。因此，某些功能和插件仅适用于专有版本。
发展缓慢：自从MySQL项目于2008年被Sun Microsystems收购，然后在2009年被Oracle Corporation收购后，用户抱怨DBMS的开发过程已经大大减慢了，因为社区不再拥有代理机构快速应对问题并实施更改。

何时使用MySQL

分布式操作：MySQL的复制支持使其成为分布式数据库设置（例如主从或主从体系结构）的理想选择。
网站和Web应用程序：MySQL在Internet上为许多网站和应用程序提供支持。这在很大程度上要归功于安装和设置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平面上的闭合几何路径。

网络地址类型：

数据类型说明cidrIPv4或IPv6网络地址。inetIPv4或IPv6主机地址。macaddr媒体访问控制（MAC）地址。

位字符串类型：

数据类型说明bit固定长度的位字符串。bit varying可变长度的位字符串。

文字搜索类型：

数据类型说明tsquery文本搜索查询。tsvector文本搜索文档。

JSON类型：

数据类型说明json文本JSON数据。jsonb分解后的二进制JSON数据。

其他数据类型：

数据类型说明boolean逻辑布尔值，代表true或false。bytea“字节数组”的缩写，此类型用于二进制数据。money一定数量的货币。pg_lsnPostgreSQL日志序列号。txid_snapshot用户级交易ID快照。uuid通用唯一标识符。xmlXML数据。

PostgreSQL的优点

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

PostgreSQL的缺点

内存性能：对于每个新的客户端连接，PostgreSQL都会派生一个新进程。每个新进程都分配了大约10MB的内存，对于具有大量连接的数据库而言，这可以快速增加内存。因此，对于简单的繁重的操作，PostgreSQL通常比其他RDBMS（如MySQL）的性能要差。
流行度：尽管近年来使用更为广泛，但从历史上看，PostgreSQL在流行度方面一直落后于MySQL。其结果之一是，可以帮助管理PostgreSQL数据库的第三方工具仍然较少。同样，与拥有MySQL经验的数据库管理员相比，拥有经验丰富的Postgres数据库的数据库管理员也要少得多。

何时使用PostgreSQL

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

何时不使用PostgreSQL

速度势在必行：牺牲速度，PostgreSQL在设计时就考虑了可扩展性和兼容性。如果您的项目需要尽可能快的读取操作，则PostgreSQL可能不是DBMS的最佳选择。
简单的设置：由于Postgres具有强大的功能集和对标准SQL的强烈支持，因此对于简单的数据库设置来说可能会显得过大。对于需要速度的重读操作，MySQL通常是更实际的选择。
复杂的复制：尽管PostgreSQL确实提供了对复制的强大支持，但是它仍然是一个相对较新的功能，并且某些配置（例如主-主体系结构）只能通过扩展来实现。复制是MySQL上更为成熟的功能，许多用户认为MySQL的复制更容易实现，特别是对于那些缺乏必要的数据库和系统管理经验的用户。

结论

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