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

介紹

關聯數據模型將數據組織成行和列的表格，在數據庫管理工具中佔主導地位。今天有其他數據模型，包括NoSQL和NewSQL，但關聯數據庫管理系統（RDBMSs）在全球範圍內仍然佔主導地位。

本文將比較和對比三個最廣泛使用的開源RDBMSs：SQLite、MySQL和PostgreSQL。具體而言，它將探討每個RDBMS使用的數據類型、它們的優缺點以及最佳優化的情況。

A Bit About Database Management Systems

數據庫是邏輯上模擬的信息集群，或者數據。另一方面，數據庫管理系統（DBMS）是與數據庫交互的計算機程序。DBMS允許您控制對數據庫的訪問，寫入數據，運行查詢，以及執行與數據庫管理相關的任何其他任務。

雖然資料庫管理系統通常被稱為“資料庫”，但這兩個術語並不是可互換使用的。一個資料庫可以是任何數據的集合，不僅僅是存儲在計算機上的數據。相反，DBMS 專指允許您與資料庫交互的軟體。

所有資料庫管理系統都有一個底層模型，該模型結構化了數據的存儲和訪問方式。關聯資料庫管理系統是一種使用關聯數據模型的 DBMS。在這個關聯模型中，數據被組織成表格。在 RDBMS 的上下文中，表格更正式地被稱為關係。一個關係是一組元組，這些元組是表格中的行，每個元組共享一組屬性，這些屬性是表格中的列：

大多數關聯式資料庫使用結構化查詢語言（SQL）來管理和查詢數據。然而，許多 RDBMS 使用自己特定的 SQL 方言，可能具有特定的限制或擴展。這些擴展通常包括額外功能，允許用戶執行比標準 SQL 更複雜的操作。

數據類型和約束

每個列被分配一個數據類型，該類型決定了該列中允許的輸入類型。不同的RDBMS實現了不同的數據類型，這些類型並不總是直接可互換的。一些常見的數據類型包括日期、字符串、整數和布爾值。

將整數存儲在數據庫中比在表中放置數字更為微妙。數字數據類型可以是帶符號的，這意味著它們可以表示正數和負數，也可以是無符號的，這意味著它們只能表示正數。例如，MySQL的tinyint數據類型可以保存8位數據，相當於256個可能的值。該數據類型的有符號範圍是從-128到127，而無符號範圍是從0到255。

能夠控制允許進入數據庫的數據很重要。有時，數據庫管理員會對表施加約束，以限制可以輸入其中的值。約束通常適用於一個特定的列，但有些約束也可以適用於整個表。以下是SQL中常用的一些約束：

• 獨特：將此約束應用於一列確保該列中沒有兩個項目是相同的。
• 非空：此約束確保一列中沒有任何NULL項目。
• 主鍵：結合獨特和非空，主鍵約束確保該列中沒有NULL項目，並且每個項目都是獨特的。
• 外鍵：一個表中的列參考另一個表的主鍵的外鍵。此約束用於將兩個表關聯在一起。要成功進行寫入操作，外鍵列中的項目必須已存在於父主鍵列中。
• 檢查：此約束限制可以輸入到一列中的值的範圍。例如，如果您的應用僅面向阿拉斯加居民，您可以在郵政編碼列上添加一個檢查約束，以僅允許在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 排名，自該網站開始追蹤數據庫流行度以來，MySQL 自 2012 年以來一直是最受歡迎的開源 RDBMS。它是一個功能豐富的產品，驅動著世界上許多最大的網站和應用程序，包括 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 支持用戶管理，並允許您按用戶逐個授予訪問權限。
• 速度：MySQL 開發人員選擇不實現 SQL 的某些功能，以便優先考慮速度。盡管最近的基準測試顯示其他關聯式數據庫管理系統（RDBMS）如 PostgreSQL 可以與 MySQL 在速度方面相匹敵，甚至接近，但 MySQL 仍然被認為是一個非常快速的數據庫解決方案。
• 複製：MySQL 支援多種不同類型的 複製，這是在兩個或更多主機之間共享資訊的做法，以幫助提高可靠性、可用性和容錯能力。這對於建立資料庫備份解決方案或 水平擴展 資料庫都很有幫助。

MySQL 的缺點

• 已知限制：因為 MySQL 設計用於速度和易用性，而不是完全符合 SQL 的規範，所以它具有一些功能上的限制。例如，它 不支援 FULL JOIN 子句。
• 授權和專有功能：MySQL 是 雙授權 軟體，有一個在 GPLv2 下許可的免費開源社區版和幾個以專有許可證發布的付費商業版。因此，一些功能和插件僅適用於專有版本。
• 開發放緩：自2008年MySQL項目被Sun Microsystems收購，後來於2009年被Oracle Corporation收購以來，有使用者抱怨DBMS的開發過程明顯放緩，因為社區不再能夠迅速回應問題並實施變更。

何時使用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支持160项完全核心SQL:2011兼容性所需的179项功能，还支持一长串可选功能。据官方PostgreSQL文档，PostgreSQL支持160项完全核心SQL:2011兼容性所需的179项功能，还支持一长串可选功能。
• 开源且由社区驱动：作为一个完全开源的项目，PostgreSQL的源代码是由一个庞大而忠诚的社区开发的。同样，Postgres社区维护并贡献了大量在线资源，介绍如何使用这个数据库管理系统，包括官方文档、PostgreSQL wiki以及各种在线论坛。
• 可扩展性：用户可以通过其目录驱动操作和动态加载，以编程方式在运行时扩展PostgreSQL。用户可以指定一个对象代码文件，例如共享库，PostgreSQL将根据需要加载它。

PostgreSQL的缺點

• 內存性能：對於每個新的客戶端連接，PostgreSQL都會派生出一個新的進程。每個新進程分配約10MB的內存，對於具有大量連接的數據庫來說，這可能會很快增加。因此，對於簡單的讀取密集型操作，PostgreSQL通常比其他關聯型數據庫管理系統（如MySQL）性能較差。
• 流行度：儘管近年來使用越來越廣泛，但PostgreSQL在流行度上歷史上一直落後於MySQL。這帶來的一個後果是，仍然缺乏可以幫助管理PostgreSQL數據庫的第三方工具。同樣，與具有MySQL經驗的數據庫管理員相比，具有管理Postgres數據庫經驗的人數也較少。

何時使用PostgreSQL

• 數據完整性很重要：自2001年以來，PostgreSQL已完全符合ACID標準並實現了多版本並發控制，以確保數據保持一致性，這使其成為在數據完整性至關重要時的強大關聯型數據庫管理系統的選擇。
• 與其他工具集成：PostgreSQL與多種編程語言和平台兼容。這意味著如果您需要將數據庫遷移到另一個操作系統或將其與特定工具集成，使用PostgreSQL數據庫可能比使用其他DBMS更容易。
• 複雜操作：Postgres支持可以利用多個CPU來回答查詢的查詢計劃，從而提高速度。這與其對多個並發寫入的強大支持相結合，使其成為處理數據倉儲和在線交易處理等複雜操作的絕佳選擇。

不適用於使用PostgreSQL的情況

• 速度至關重要：為了擴展性和兼容性，PostgreSQL的設計可能會以速度為代價。如果您的項目需要盡可能快的讀取操作，則PostgreSQL可能不是最佳的DBMS選擇。
• 簡單設置：由於其豐富的功能集和對標準SQL的嚴格遵循，對於簡單的數據庫設置來說，Postgres可能過度複雜。對於讀取密集型操作且需要速度的情況，通常更實用的選擇是MySQL。
• 複雜的複製：儘管PostgreSQL確實提供了強大的複製支持，但這仍然是一個相對較新的功能。一些配置，比如主-主架構，只有使用擴展才能實現。對於那些缺乏必要的數據庫和系統管理經驗的用戶來說，許多用戶認為MySQL的複製更容易實施。

結論

如今，SQLite、MySQL和PostgreSQL是世界上三個最流行的開源關聯式數據庫管理系統。每個系統都有其獨特的功能和限制，在特定情況下表現出色。在決定使用關聯式數據庫管理系統時，有很多變量需要考慮，選擇很少像選擇最快或功能最多的那樣簡單。下次當您需要關聯式數據庫解決方案時，請務必深入研究這些工具和其他工具，找到最適合您需求的工具。

如果您想了解更多關於 SQL 及如何使用它來管理關聯式資料庫，我們鼓勵您參考我們的《如何管理 SQL 資料庫》秘笈表。另一方面，如果您想了解非關聯式（或 NoSQL）資料庫，請查看我們的《NoSQL 資料庫管理系統比較》比較。

參考資料

• DB-Engines 排名
• SQLite 官方文件
• SQLite 無伺服器
• SQLite 適當使用情境
• MySQL 官方文件
• 比較 MySQL 和 PostgreSQL 9.0 複寫
• PostgreSQL 官方文件
• PostgreSQL 終於迎來時機了嗎？