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

介绍

在数据库管理工具中占主导地位的 relational data model,它组织数据在行和列的表中,今天还有其他数据模型,包括 NoSQL和 NewSQL,但关系数据库管理系统(RDBMSs) 仍然占主导地位用于全球数据存储和管理。

本文比较和对比了三种最广泛实施的开源RDBMS: SQLite, MySQL和 PostgreSQL。

关于数据库管理系统的一点点

数据库是逻辑上建模的信息集群,或 data. 另一方面,数据库管理系统(DBMS)是一个与数据库互动的计算机程序,DBMS允许您控制访问数据库,写数据,运行查询,并执行与数据库管理相关的任何其他任务。

虽然数据库管理系统通常被称为数据库,但这两个术语不能互换,数据库可以是任何数据集,而不仅仅是存储在计算机上的数据集。

所有数据库管理系统都有一个基础模型,该模型结构数据的存储和访问方式. 关系数据库管理系统是使用关系数据模型的DBMS。 在这个关系模型中,数据被组织成表。 表,在RDBMS的背景下,更正式地被称为 relations。 关系是 tuples 的集合,这是表中的行,每个 tuple 共享了 attributes 的集合,这是表中的列:

Diagram example showing how relations, tuples, and attributes relate to one another

大多数关系数据库使用 structured query language (SQL) 来管理和查询数据. 然而,许多 RDBMS 使用自己的特定 SQL 方言,可能有某些限制或扩展。

<$>[注] 注:标准 SQL一词在本指南中出现了几次。SQL标准由美国国家标准研究所(ANSI)(https://www.ansi.org/),国际标准化组织(ISO)(https://www.iso.org/home.html)和国际电气技术委员会(IEC)(https://www.iec.ch/)共同维护。当本文提到标准 SQL或SQL标准时,它指的是这些机构发布的当前版本的SQL标准。

应该指出的是,完整的 SQL 标准是大而复杂的:完整的 SQL:2011 核心合规性需要 179 个功能,因此,大多数 RDBMS 并不支持整个标准,尽管有些人比其他人更接近完全合规性。

数据类型与限制

每个列都被分配一个 data type 来决定该列中允许的输入类型。 不同的 RDBMS 实现了不同的数据类型,这些数据类型并不总是可以直接交换。

在数据库中存储整数比在表中放置数字更为细微。 数字数据类型可以是 signed,这意味着它们可以代表正数和负数,或者是 unsigned,这意味着它们只能代表正数。

能够控制数据被允许进入数据库是很重要的。有时,数据库管理员会对表施加 constraint 以限制可以输入的值。

  • UNIQUE:将此限制应用到一列,确保该列中的两个条目不相同
  • NOT NULL:此限制确保一列没有任何 NULL 条目
  • PRIMARY KEY: 一列中的 UNIQUE和 NOT NULL 一列是指另一个表中的 PRIMARY KEY,这个限制确保列中的任何条目都不是 NULL,而且每个条目都是单独的
  • FOREIGN KEY: A FOREIGN KEY 是指另一个表的 PRIMARY KEY 一列。

如果您想了解有关数据库管理系统的更多信息,请参阅我们关于 NoSQL 数据库管理系统和模型的比较(https://andsky.com/tech/tutorials/a-comparison-of-nosql-database-management-systems-and-models)的文章。

现在我们已经涵盖了关系数据库管理系统一般,让我们转向本文将涵盖的三个开源关系数据库中的第一个:SQLite。

SQLite 是一个独立的、基于文件的、完全开源的 RDBMS 系统,即使在低内存环境中也因其可移植性、可靠性和强大的性能而闻名。

SQLite项目的网站将其描述为一个无服务器的数据库。大多数关系数据库引擎被实施为一个服务器流程,其中程序通过传递请求的跨流程通信与主机服务器进行通信。相比之下,SQLite允许访问数据库的任何流程直接阅读和写入数据库磁盘文件。这简化了SQLite的设置流程,因为它消除了任何需要配置服务器流程的需要。

SQLite 是免费的和开源的软件,不需要特殊的许可证来使用它,但该项目确实提供了几个扩展 - 每一个为一次性费用 - 帮助压缩和加密。

SQLite 支持的数据类型

SQLite 允许各种数据类型,分为以下 _storage 类:

数据类型 解释

null 包含任何 NULL 值 integer 签名整数,存储在 1, 2, 3, 4, 6 或 8 字节,取决于值的大小 real 实数,或浮点值,存储为 8 字节浮点数 text 文本字符串存储使用数据库编码,可以是 UTF-8, UTF-16BE 或 UTF-16LE `blob 任何数据,每一个字符串都被存储完全像输入一样。

在SQLite的背景下,术语存储类和数据类型被认为是可互换的。如果您想了解更多关于SQLite的数据类型和SQLite类型亲属性,请参阅SQLite的官方文档(http://www.sqlite.org/datatype3.html)。

SQLite 的优点

  • 小足迹:正如它的名字所暗示的那样,SQLite库非常轻,虽然其使用的空间因安装系统而异,但它可以占用不到600KiB的空间。此外,它完全自有,这意味着您不需要在您的系统上安装任何外部依赖性,以便SQLite工作
  • 用户友好:SQLite有时被描述为一个零配置数据库,它已经准备好使用。SQLite不作为一个服务器流程运行,这意味着它永远不需要停止,启动或重新启动,并且没有任何需要管理的配置文件。这些功能有助于简化从安装SQLite到与应用程序集成(MBRK1)**的路径。

SQLite 的缺点

  • ** 有限货币**: 虽然多个进程可以同时访问并查询一个SQLite数据库,但只有一个进程可以在任何时候对数据库进行修改. 这意味着,虽然SQLite比大多数其他嵌入式数据库管理系统支持更大的货币,但是它不能像MySQL或PostgreSQL这样的客户端/服务器RDBMS那样支持更多. () ( )* ** 没有用户管理**:数据库系统经常支持_用户,或管理连接,预设数据库和表格的访问权限。 由于SQLite直接读取并写入普通磁盘文件,所以唯一适用的访问权限是基础操作系统的典型访问权限. 这使得SQLite对于需要多个用户有特殊访问权限的应用程序选择不佳. (_) ( )* Security:一个使用服务器的数据库引擎在某些情况下可以比SQLite这样的无服务器数据库更好地保护客户端应用程序中的bug. 例如,客户端中的出错指针不能损坏服务器上的内存. 另外,由于一个服务器是一个单一的持续过程,一个客户端-服务器数据库可以比一个没有服务器的数据库更精确地控制数据访问. 这样可以进行更精细的锁锁和更好的通货(-) (英语)

何时使用 SQLite

  • 嵌入式应用程序:SQLite是对于需要便携性且不需要未来的扩展的应用程序的绝佳数据库选择。示例包括单用户本地应用程序、移动应用程序或游戏
  • 磁盘访问替换:在应用程序需要直接读取和写入磁盘的文件的情况下,使用SQLite对于使用SQL 带来的额外功能和简单性可能是有益的。

何时不应该使用 SQLite

  • ** 与大量数据相配合**:只要磁盘驱动器和文件系统也支持数据库的大小要求,SQLite可以在技术上支持一个大小可达140TB的数据库. 然而,SQLite网站建议认为,任何接近1TB的数据库都应设置在一个集中的客户端-服务器数据库上,因为大小或更大的SQLite数据库将难以管理. () ( )* ** 高写卷**: SQLite只允许在任何特定时间进行一次写作操作,这大大限制了它的吞吐量. 如果您的应用程序需要大量写操作或多个并行写手, SQLite 可能不足以满足您的需要 。 () ( )* ** 需要上网**: 由于SQLite是一个没有服务器的数据库,因此它不提供直接的网络访问其数据. 此访问权限包含在应用程序中 。 如果SQLite中的数据位于一个与应用程序相隔的机器上,则需要高带宽引擎到磁盘的全网络连接. 这是一个昂贵而低效的解决方案,在这种情况下,客户端-服务器DBMS可能是更好的选择. (_) (英语)

思考

根据DB引擎排名(LINK0),MySQL自网站开始追踪数据库普及以来一直是最受欢迎的开源RDBMS,它是一个功能丰富的产品,支持许多世界上最大的网站和应用程序,包括Twitter,Facebook,Netflix和Spotify。

MySQL 是为了速度和可靠性而设计的,以完全遵守标准 SQL 的代价。MySQL 开发人员不断致力于更接近标准 SQL 的遵守,但它仍然落后于其他 SQL 实现。

與使用 SQLite 的應用程式不同,使用 MySQL 資料庫的應用程式通過獨立的 DAEMON 流程存取它,因為伺服器流程位於資料庫與其他應用程式之間,因此它允許對誰可以存取資料庫進行更大的控制。

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

MySQL 支持的数据类型

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

  • 數字類型:

数据类型QQ解释 ------------------(- )-----------tinyint'--------------------------------------------(--------------------------------------------------------------------------------------------------------------------------------------------------------- 此数字数据类型的签名范围为-128至127,而未签名范围为0至255. ' smallint' QQ小整数. 此数字类型的签名范围为-32768至32767,而未签名范围为0至65535. mediint' → A中型整数. 此数字数据类型的签名范围为-8388608至8388607,而未签名范围为0至16777215. int' 或intger' → 普通大小整数. 此数字数据类型的签名范围为-2147483648至-2147483647,而未签名范围为0至4294967295. bigint' | A大整数. 这一数字数据类型的签名范围为-9223303685475808至-922327303685475807,而未签名范围为0至1844644073709551615. 浮动'-A小(单精度)浮点数. 双精度',双精度',或真'-A平面大小(双精度)浮点数. 已定',十进取',已定',或`已装定点数'。 此数据类型条目的显示长度在创建列时被定义,每个条目都坚持该长度. "bool"或"boolean""A Boolean"是一个数据类型,只有两个可能值,通常有" true" 或"false". "bit" QQ 一个位值类型,您可以指定每个值的位数,从1到64.

** 日期和时间类型**:

数据类型 解释 ------------- Mediateca ------------- dateannoo 日期,表示为 YYYY-MM-DD datetime,表示日期和时间的时刻印记,显示为 YYYYY-MM-DD HH:MM:SS timestamp,表示自 Unix 时代(1970年1月1日00:00)以来的时间印记。

类型: String类型:

数据类型QQ解释 . ) char QQ 固定长字符串; 此类型的条目在右侧被添加空格以在存储时满足指定的长度. (_) )'varchar' QQ可变长度的字符串. 'binary' QQ与'char'类型相类似,但是一个指定长度的二进制字节字符串而非非二进制字符串. 'varbinary' 与"varchar"类型相类似,但可变长度的二进制字节字符串而不是非二进制字符串. (- )'blob'' → 二进制字符串,最大长度为65535(2^16-1)字节. (-- )'tinyblob'-" A'blob"一行,最大长度为255(2^8-1)字节. (-) )中段blob' * 最大长度为16777215 (2^24-1)字节的 A " blob " 一栏数据. (- )长段blob' * 最大长度为4294967295 (2^32-1)字节. (- )文本' * A " 字符串 " 最大长度为65535 (2^16-1)字符. (- )tinytext' * A " text " 一栏数据最长长度为255 (2^8-1)字符. (-) ) " 中文本 " -- -- A " 文本 " 一栏,最长长度为16777215 (2^24-1) 字符。 " 长文本 " -- -- -- A " 文本 " 一栏,最大长度为4294967295 (2^32-1) 字符。 enum QQ 盘点,是一个字符串对象,它从表格创建时声明的值列表中取出一个单一的值. ` set' QQ 类似于一个盘点,一个可以有零或更多值的字符串对象,每个盘点必须从表格创建时指定的允许值列表中选择.

MySQL 的优点

  • ** 优点和易用性**: 作为世界上最受欢迎的数据库系统之一,并不缺少拥有MySQL工作经验的数据库管理员. 同样,关于如何安装和管理MySQL数据库的印刷和在线文档也很多. 这包括一些第三方工具,如phpMyAdmin,目的是简化数据库启动的过程。 () ( )* Security: MySQL 安装了脚本,通过设置安装的密码安全级别,定义root用户的密码,去除匿名账户,并去除默认所有用户都可以访问的测试数据库,帮助您改善数据库的安全. 另外,与SQLite不同的是,MySQL确实支持用户管理,并允许您在用户的基础上给予访问权限. () ( )* Speed:通过选择不执行SQL的某些功能,MySQL开发者得以优先速度. 虽然更近期的基准测试显示其他RDBMS如PostgreSQL在速度上可以匹配或至少接近于MySQL,但MySQL仍保持着超快的数据库解决方案的声誉. () ( )* ** replace**:MySQL支持一些不同类型的[replate(https://en.wikipedia.org/wiki/Replication(computing)#Database_replate],这是在两个或两个以上主机之间共享信息来帮助改进可靠性,可用性和过错容忍性的做法. 这有助于建立数据库备份解决方案或范围缩放个人数据库。 (_) (英语)

MySQL的缺点

什么时候使用MySQL

  • 分布式操作:MySQL的复制支持使其成为分布式数据库设置的绝佳选择,例如 初级二级或 初级初级架构
  • 网站和Web应用程序:MySQL在互联网上支持许多网站和应用程序,这在很大程度上是由于安装和设置MySQL数据库的简单性,以及其长期的总体速度和可扩展性
  • 预期未来增长:MySQL的复制支持可以帮助促进水平扩展。

何时不使用MySQL

  • SQL 合规性是必不可少的:由于 MySQL 没有试图实现完整的 SQL 标准,所以这个工具并不完全符合 SQL。如果完整的或几乎完整的 SQL 合规性是您的应用程序必不可少的,您可能想要使用更完全合规的 DBMS
  • 竞争力和大数据量:虽然 MySQL 通常在读重操作中表现良好,同时读写可能具有问题。

邮局

PostgreSQL,也被称为Postgres,称自己为世界上最先进的开源关系数据库。它是以高度可扩展和符合标准的目标创建的 PostgreSQL是一个对象关系数据库,这意味着虽然它主要是关系数据库,但它还包括功能 - 如表继承和函数过载 - 这些功能更常与 object databases相关。

Postgres 能够有效地同时处理多个任务,这是一种被称为 concurrency 的特性,它通过实施 Multiversion Concurrency Control (MVCC) 来实现这一目标,从而确保其交易的原子性、一致性、隔离性和耐用性,也被称为 ACID 合规性。

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

PostgreSQL 支持的数据类型

PostgreSQL 支持数值、字符串、日期和时间数据类型,例如 MySQL. 此外,它还支持地理形状、网络地址、位符串、文本搜索和 JSON 条目等数据类型,以及多种独特的数据类型。

  • 數字類型:

数据类型 解释

** 性格类型**:

数据类型 解释 ------------- Mediateca --------- character Mediateca 指定固定长度的字符串 字符变量 或 varchar 一字符串具有变量但有限长度 text 一字符串的变量,无限长度。

** 日期和时间类型**:

数据类型 解释

date Mediateca 日历日期由日、月和年组成 间隔 A time span time 或 time without time zone A time of day, not including the time zone. time with time zone A time of day, including the time zone. timestamp 或 timestamp without time zone A date and time, not including the time zone. timestamp with time zone A date and time, including the time zone.

(二)地质类型:

数据类型 解释

网络地址类型:

数据类型 解释

cidrcoil 一个 IPv4 或 IPv6 网络地址 inetcoil 一个 IPv4 或 IPv6 主机地址 macaddrcoil 一个媒体访问控制(MAC)地址。

** 字符串类型**:

数据类型 解释

bitAlike 一个固定长度的位符串 bit varyingAlike 一个可变长度的位符串。

** 文本搜索类型**:

数据类型 数据类型 解释 -------------annoo --------- tsqueryAlike 文本搜索查询 tsvectorAlike 文本搜索文档。

JSON 类型:

数据类型 数据类型 解释 -------------███ --------- jsonannoo 文本 JSON 数据. jsonb███ 解构了二进制 JSON 数据。

其他数据类型**:

数据类型 解释 -------------♰ ------------- boolean♰ 一个逻辑布尔语,代表无论是 true 还是 false. bytea♰ 简称为 "byte array",这种类型用于二进制数据 money♰ 一个货币数量 pg_lsn 一个 PostgreSQL 日志序列号 txid_snapshot 一个用户级的交易 ID snapshot.( _iduu 一个通用独特的标识符 xml XML

PostgreSQL 的优点

  • SQL 合规性:比 SQLite 或 MySQL 还要多,PostgreSQL 旨在密切遵守 SQL 标准。 根据官方 PostgreSQL 文档,PostgreSQL 支持 160 个为完整的核心 SQL:2011 合规性所需的 179 个功能,除了描述如何使用 DBMS 的长列表之外
  • 开源和社区驱动的:一个完全开源的项目,PostgreSQL 的源代码由一个庞大而专注的社区开发。同样,PostgreSQL 社区维护并贡献了许多在线资源,这些资源包括 官方文档, PostgreSQL 维基,以及各种在线论坛 ***:用户可以扩展 Postgre

PostgreSQL 的缺点

  • ** 记忆性能**:对于每一个新的客户端连接,PostgreSQL 都会分配一个新的流程。每一个新的流程都分配了大约 10 MB 的内存,这可以快速为具有大量连接的数据库增加。因此,对于简单易读的操作,PostgreSQL 通常比 MySQL 这样的其他 RDBMS 更低的性能。 ** 普及性**:尽管近年来更广泛使用,但PostgreSQL 历史上在普及方面落后于 MySQL。

如何使用PostgreSQL

  • 数据完整性很重要:自2001年以来,PostgreSQL已经完全兼容了ACID,并实施了多版本货币控制,以确保数据保持一致,这使得它在数据完整性至关重要时成为RDBMS的强大选择
  • 与其他工具的集成:PostgreSQL与各种编程语言和平台兼容。这意味着,如果您需要将数据库迁移到另一个操作系统或与特定工具集成,则可能比其他DBMS更容易使用PostgreSQL 复杂操作:PostgreSQL支持查询计划,可以利用多个CPU以更快地响应查询。

什么时候不使用PostgreSQL

如果您的项目需要最快的读取操作,PostgreSQL可能不是DBMS的最佳选择

  • 简单的设置:由于其大功能集和强烈的遵守标准SQL,PostgreSQL可以是简单的数据库设置的过度杀伤。对于需要速度的重量级操作,MySQL通常不是DBMS的最佳选择
  • 复杂的复制:尽管PostgreSQL提供了强大的复制支持,但它仍然是一个相对较新的功能。

结论

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

如果您想了解更多关于 SQL 和如何使用它来管理关系数据库,我们鼓励您参阅我们的 如何管理 SQL 数据库骗局表。

参考

DB-Engines Rankings

Published At
Categories with 技术
Tagged with
comments powered by Disqus