返回文章列表
玄貓（BlackCat）

資料函式庫設計核心概念與最佳實踐

本文探討資料函式庫設計的核心概念，包含日期時間型別、約束、索引最佳化、資料品品檢查以及交易隔離級別等關鍵技術。文章以 MySQL 和 PostgreSQL 為例，闡述不同資料型別、約束的應用場景，並解析索引的建立與維護策略，同時也探討瞭如何透過交易和隔離級別確保資料函式庫一致性，以及 MVCC 等平行控制機制

資料函式庫 系統設計
資料函式庫設計 資料型別 SQL 索引 交易 ACID

資料函式庫設計的關鍵在於平衡資料完整性、效能和可擴充套件性。選擇正確的資料型別，例如 TIMESTAMP 和 DATETIME，對於處理時間資料至關重要，同時理解它們的區別和限制才能避免時區和範圍問題。約束，包含主鍵、外部索引鍵、唯一約束和檢查約束，是確保資料完整性的根本。索引的設計和最佳化，例如複合索引，對於提升查詢效能至關重要，但必須謹慎管理索引數量以避免影響寫入效能。此外，資料品品檢查和驗證策略，結合資料函式庫約束和應用程式層級的驗證，是確保資料準確性的重要手段。最後，理解交易隔離級別，例如 REPEATABLE READ，以及鎖定機制和 MVCC 等平行控制技術，對於在高平行環境下維護資料一致性至關重要。

資料函式庫物件的深入理解

在資料函式庫設計中，瞭解不同資料型別及其特性對於建立高效且可靠的資料函式庫至關重要。特別是在處理日期和時間資料時，選擇正確的資料型別可以避免許多潛在的問題。

日期和時間資料型別

MySQL 提供了多種日期和時間資料型別，包括 TIMESTAMP 和 DATETIME。這兩種資料型別都支援毫秒精確度的時間戳。

mysql> show create table example_ts_ds\G
************************* 1. row **************************
Table: example_ts_ds
Create Table: CREATE TABLE `example_ts_ds` (
  `id` int DEFAULT NULL,
  `event_time` timestamp(3) NULL DEFAULT NULL,
  `event_date` datetime(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> select * from example_ts_ds;
+
---
---
+
---
-
---
-
---
-
---
-
---
-
---
--+
---
-
---
-
---
-
---
-
---
-
---
--+
| id   | event_time              | event_date              |
+
---
---
+
---
-
---
-
---
-
---
-
---
-
---
--+
---
-
---
-
---
-
---
-
---
-
---
--+
| 1    | 2023-12-09 12:34:56.789 | 2023-12-10 10:10:15.000 |
+
---
---
+
---
-
---
-
---
-
---
-
---
-
---
--+
---
-
---
-
---
-
---
-
---
-
---
--+

TIMESTAMP 與 DATETIME 的差異

  • TIMESTAMP 資料在儲存時會被轉換為 UTC 時間，並在檢索時轉換回伺服器或客戶端的目前時區。
  • TIMESTAMP 的範圍較小（1970-01-01 00:00:01 UTC 至 2038-01-19 03:14:07 UTC），通常使用 4 個位元組儲存。

處理時區偏移

在 SQL 陳述式中使用時區偏移是處理與特定時區相關的日期和時間值的絕佳方法。這種方法允許您為每個時間戳指定與協調世界時（UTC）的確切偏移。

INSERT INTO example_ts_ds
VALUES (2, "2024-02-19 07:17:07.000+05:00", NOW());

其他資料型別

除了日期和時間資料型別外，還有其他一些資料型別需要謹慎使用，特別是在關聯式資料函式庫的世界中。這些資料型別包括：

  • 二進位型別：用於儲存二進位資料，如影像或檔案。
  • 布林型別：用於儲存真或假值。
  • 大型物件型別：BLOB 和 CLOB，用於儲存大型資料集，如多媒體檔案。
  • JSON 型別：非常適合儲存不適合傳統關聯式資料函式庫架構的結構化資料。

約束

約束是對資料欄位強制執行的規則，以確保資料函式庫中資料的準確性和可靠性。

主鍵約束

主鍵約束是資料函式庫約束中最重要的。它唯一標識表中的每個記錄，不能為 NULL。

ALTER TABLE order_items
CHANGE order_id order_id BIGINT UNSIGNED NOT NULL,
ADD CONSTRAINT fk_order_items_orders
FOREIGN KEY (order_id) REFERENCES orders(order_id);

外部索引鍵約束

外部索引鍵約束建立兩個表之間的關係，並確保參考完整性。

ALTER TABLE order_items
DROP FOREIGN KEY fk_order_items_orders;

ALTER TABLE orders
DROP PRIMARY KEY,
CHANGE order_id order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

ALTER TABLE order_items
CHANGE order_id order_id BIGINT UNSIGNED NOT NULL,
ADD CONSTRAINT fk_order_items_orders
FOREIGN KEY (order_id) REFERENCES orders(order_id);

唯一約束

唯一約束確保欄位中的所有值都不同。

資料函式庫設計與建模的基礎構建區塊精粹

掌握資料完整性與效能最佳化

資料函式庫設計的核心在於確保資料的完整性、可靠性和安全性。正確實施資料型別和約束對於建立高效、可靠且安全的資料函式庫至關重要。這些元素有助於有效地結構化資料函式庫，並在資料驗證和完整性方面發揮重要作用。

鍵與索引的最佳實踐

鍵是關係型資料函式庫設計的基礎，在確保資料完整性、最佳化查詢和定義表之間的關係方面發揮著至關重要的作用。正確理解和實施不同型別的鍵可以顯著增強資料函式庫的結構和功能。

複合索引的應用

複合索引，也稱為覆寫索引，包含兩個或多個列作為索引的一部分。當單一列不足以唯一標識記錄時，應使用複合鍵。例如，假設常見查詢涉及檢索訂單狀態、購買時間戳和客戶資訊，覆寫索引可能如下所示：

CREATE INDEX idx_orders_covering
ON ShopSphere.orders (
    order_id,
    order_status,
    order_purchase_timestamp,
    customer_id
);

更好的做法是建立一個以 customer_id 為前導列的複合索引：

CREATE INDEX idx_orders_customer_id_covering
ON ShopSphere.orders (
    customer_id,
    order_status,
    order_purchase_timestamp
);

索引的最佳化與維護

建立索引可以提高查詢效能，但過多的索引會減慢寫入操作（INSERT、UPDATE 和 DELETE），因為索引需要更新。確保這種權衡是可以接受的。索引大小會隨著列數量的增加而增加，定期的維護可能是最佳效能的必要條件。

資料函式庫檢查與約束

除了主鍵和外部索引鍵之外，還有其他約束支援關係型資料函式庫物件，用於控制和限制操作。

檢查約束

檢查約束確保列中的所有值滿足特定條件。例如，age 列上的檢查約束可以確保只輸入大於 0 的值。

預設約束

預設約束在未指定值時為列分配預設值。例如，預設約束可以自動將狀態列設定為啟用。

非空約束

非空約束確保列不具有 NULL 值。這對於維護資料完整性至關重要，尤其是對於必須具有有效資料的列，例如主鍵列。

資料品品檢查

透過有效地使用上述約束，可以顯著提高資料函式庫中的資料品質。約束作為防止資料輸入錯誤的第一道防線，確保儲存的資料的一致性、有效性和完整性。

資料驗證策略

理想情況下，應同時使用資料函式庫約束和應用層驗證方法。依賴資料函式庫約束來執行基本的資料完整性規則（例如參考完整性、唯一性和基本資料型別檢查），並使用應用層驗證來執行更複雜的、與上下文相關的規則。

檢查約束例項

要防止日期列接受未來的日期，可以使用 CHECK 約束（在 MySQL 8.0.16 及更高版本中可用）。假設我們有一個名為 events 的表，其中包含一個 event_date 日期列：

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(255) NOT NULL,
    event_date DATE,
    CHECK (event_date <= CURDATE())
);

這個 CHECK 約束確保 event_date 永遠不會被設定為未來的日期。

資料函式庫設計與完整性維護

檢查資料品質的限制條件

特定範圍內的數值驗證

若要確保某個數字型欄位的值落在特定範圍內，可以使用CHECK限制條件。例如，假設有一個名為ratings的表格，其中包含一個score欄位，該欄位只能接受1到5之間的值：

CREATE TABLE ratings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    review TEXT,
    score INT,
    CHECK (score BETWEEN 1 AND 5)
);

這個限制條件使用BETWEEN來指定score的允許範圍。任何試圖插入或更新一筆具有超出此範圍的score記錄的操作都會導致錯誤。

內容解密：

  1. CREATE TABLE ratings： 建立一個名為ratings的新表格。
  2. id INT AUTO_INCREMENT PRIMARY KEY： 定義一個自動遞增的主鍵欄位id，用於唯一標識每一筆記錄。
  3. review TEXT： 定義一個名為review的文字欄位，用於儲存評論內容。
  4. score INT： 定義一個名為score的整數欄位，用於儲存評分。
  5. CHECK (score BETWEEN 1 AND 5)： 設定檢查條件，確保score的值在1到5之間。

如何避免冗餘

在之前的章節中，我們已經介紹了多個有助於避免資料函式庫設計中冗餘的主題。讓我們再次回顧這些要點：

  • 正規化（Normalization）： 正規化是一種資料函式庫設計技術，旨在減少資料冗餘。它涉及將大型表格分解成較小、更易於管理的表格，並定義它們之間的關係。正規化通常分為幾個步驟或“正規形式”，每個步驟針對不同型別的冗餘和資料異常。
  • 使用主鍵和外部索引鍵： 確保每個表格都有一個主鍵來唯一標識每一行。使用外部索引鍵來參照其他表格中的主鍵，建立關係並避免資料重複。
  • 實施適當的資料模型： 有效地使用關係模型來建立定義良好的表格和關係。實體關係圖（Entity-Relationship Diagrams）可以幫助視覺化和設計資料函式庫架構，以避免冗餘資料。
  • 避免重複記錄： 使用唯一限制條件來防止在關鍵欄位中出現重複條目。在應用程式層面和資料函式庫限制條件中實施資料驗證。
  • 資料整合和ETL（Extract, Transform, Load）流程： 如果資料來自多個來源，請使用ETL流程來整合和清理資料。確保ETL流程在整合來自不同來源的資料時不會產生重複資料。

避免冗餘的關鍵在於智慧地設計資料函式庫，使用正規化、實施完整性限制條件以及定期維護資料函式庫。這些做法不僅節省儲存空間，還能提高效能、維護資料完整性，並使資料管理更加容易。記得定期進行稽核和應用層面的檢查，並持續監控資料函式庫中的冗餘錯誤。

資料函式庫一致性與其延伸議題

資料函式庫一致性是資料函式倉管理的關鍵組成部分，它確保每個交易都能將資料函式庫從一個有效狀態轉換到另一個有效狀態，同時遵守所有預先定義的規則和限制條件，如完整性限制條件、觸發器和級聯更新。它是原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）和永續性（Durability，ACID）屬性的其中一部分，這些屬性對於資料函式庫中的交易處理至關重要。

一致效能夠確保交易被完整執行或完全不執行，從而始終保持資料函式庫的正確性。除了資料函式庫自身的限制條件外，應用層面的規則也有助於維護一致性，確保業務邏輯準確地反映在資料中。

然而，管理一個資料函式庫不僅僅是維護一致性。它涉及在一致性和效能、可擴充套件性之間取得平衡，尤其是在資料函式庫規模和複雜度不斷增長的情況下。這種平衡在分散式資料函式庫中至關重要，因為CAP定理（代表一致性、可用性和分割容忍度）強調了一致性和可用性之間的權衡。諸如存取控制和加密等安全措施對於保護資料完整性是不可或缺的。

簡單來說，一致性意味著當資料函式庫執行在單一節點上並且完全不受分割影響時，因為它是在獨立運作。然而，根據CAP定理的定義，當節點發生故障且沒有其他冗餘機制時，它不具備可用性特性。透過複製或容錯移轉技術，可以使一個資料函式庫節點對另一個節點具有高用性，但這同時會引入分割容忍度的風險。

此外，備份與還原、長期資料完整性、法規遵從以及定期的監控和稽核對於全面管理資料函式庫系統都是至關重要的。這些做法不僅能確保資料的完整性和一致性，還能保障其安全性、合規性和隨著時間的推移保持其相關性。

交易 – 確保資料完整性

除了ACID屬性外，您還應該使用隔離級別來確保一致性。隔離級別在資料函式倉管理中扮演著至關重要的角色，尤其是在確保資料一致性方面。這些級別定義了平行交易彼此互動的程度，在資料完整性和資料存取效率之間取得平衡。

在資料函式庫系統的背景下，隔離級別是控制交易如何隔離或與其他平行交易分享資料的機制。隔離級別的選擇會影響資料的一致性、平行性和系統效能。

隔離級別的重要性

  • 定義平行交易的互動程度： 隔離級別決定了多個交易如何同時進行而不會相互幹擾。
  • 平衡資料完整性和存取效率： 較高的隔離級別提供更好的資料一致性，但可能會降低系統效能；較低的隔離級別則相反。
  • 影響系統效能： 選擇合適的隔離級別對於最佳化系統效能至關重要。

綜上所述，維護資料函式庫的一致性和完整性需要綜合運用多種技術，包括檢查條件、正規化、主鍵和外部索引鍵的使用、適當的資料模型設計，以及交易管理等。這些措施共同確保了資料的正確性和可靠性，為高效的資料管理和應用提供了堅實的基礎。

資料函式庫一致性與超越：解析交易隔離級別與平行控制

在資料函式倉管理系統中，保持資料的一致性和完整性是至關重要的。MySQL 預設的交易隔離級別為 REPEATABLE READ，在此模式下，交易讀取資料函式庫在交易開始時的快照，在交易提交之前，其他交易對資料函式庫的變更對該交易不可見。這種級別提供了高度的隔離性，但可能導致在高平行環境中增加爭用和降低效能。

交易隔離級別的重要性

交易隔離級別提供了一層額外的控制，以維持資料的一致性。它們確保平行交易不會以破壞資料完整性的方式相互幹擾。根據應用程式的具體需求，可以選擇不同的隔離級別，以在一致性和效能之間取得平衡。

常見的交易隔離級別包括 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE，每個級別提供不同程度的隔離和平行性。開發人員和資料函式倉管理員應仔細考慮其應用程式的需求，選擇最合適的隔離級別，以滿足資料一致性和效能目標。

鎖定機制與平行控制

為了實作資料函式庫使用者之間的完整性，存取許可權受到鎖定機制的限制。有多種型別的鎖定，如分享鎖、獨佔鎖和列級鎖，每種鎖都在資料平行性和完整性方面發揮著特定的作用。

列級鎖定

現代 RDBMS 的一大優點是支援列級鎖定，這使得資料函式庫能夠鎖定個別列，而非整個表格。這種粒度意味著，當一個交易正在修改特定列時，其他交易仍可讀取或修改同一表格中的其他列。

ShopSphere 資料函式庫範例
-- 查詢 order_status 為 Shipped 的 order_id
mysql> SELECT order_id FROM ShopSphere.orders WHERE order_status = 'Shipped' LIMIT 1;
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
| order_id                         |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
| 002f19a65a2ddd70a090297872e6d64e |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
1 row in set (0.00 sec)

-- 使用者 A 啟動交易並更新此列
mysql> begin work;
mysql> SELECT * FROM ShopSphere.orders WHERE order_id = '002f19a65a2ddd70a090297872e6d64e' FOR UPDATE \G
************************* 1. row *************************
order_id: 002f19a65a2ddd70a090297872e6d64e
customer_id: 7fa80efb1ef15ca4104627910c29791c
order_status: shipped
order_purchase_timestamp: 2018-03-21 13:05:30
order_approved_at: 2018-03-21 13:15:27
order_delivered_carrier_date: 2018-03-22 00:13:35
order_delivered_customer_date: NULL
order_estimated_delivery_date: 2018-04-16 00:00:00
1 row in set (0.00 sec)

-- 使用者 B 的交易將被阻塞，直到使用者 A 的交易被提交或回復
mysql> UPDATE ShopSphere.orders SET order_status = 'Delivered' WHERE order_id = '002f19a65a2ddd70a090297872e6d64e';

-- 使用者 A 回復交易
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

-- 使用者 B 的交易完成更新
mysql> UPDATE ShopSphere.orders SET order_status = 'Delivered' WHERE order_id = '002f19a65a2ddd70a090297872e6d64e';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

多版本平行控制（MVCC）

MVCC 是另一種用於管理平行的技術，它透過維護多個版本的資料或快照來增強效能。當交易讀取資料時，它會看到特定時間點的資料快照，而不是當前的形式。這種方法允許平行讀取和寫入，而無需鎖定資料資源。

PostgreSQL 中的 MVCC

MVCC 是 PostgreSQL 架構的基本部分，用於所有交易，以提供一致的資料檢視並允許非阻塞讀取。當資料列被修改時，PostgreSQL 會建立新版本的列，並保留原始版本供先前的交易使用。

MySQL 中的 MVCC

在 MySQL 中，MVCC 的支援取決於使用的儲存引擎。最常見的支援 MVCC 的引擎是 InnoDB。InnoDB 使用 MVCC 提供列級鎖定和一致的讀取，從而最小化鎖定並允許更高的平行性。