2025年01月21日玄貓（BlackCat）

MySQL 資料函式庫設計與管理最佳實踐

本文探討 MySQL 資料函式庫設計的最佳實踐，涵蓋資料型別選擇、ENUM 和 SET 的正確使用、避免 NULL 濫用、UUID 值儲存、自動生成結構的陷阱、線上 Schema 變更工具的比較與選擇（pt-online-schema-change 和 gh-ost），以及如何結合 CI/CD

資料函式庫 Web 開發

MySQL 資料函式庫設計效能最佳化 Schema 管理線上 DDL CI/CD

MySQL 資料函式庫設計的優劣直接影回應用程式的效能和可擴充套件性。正確選擇資料型別，例如使用整數型別搭配 AUTO_INCREMENT 作為識別符，並避免使用字串型別作為主鍵，可以顯著提升查詢效率。針對 UUID 值，建議轉換為 BINARY(16) 儲存以節省空間。ENUM 和 SET 型別適用於靜態定義表，但不建議用於識別符。此外，應避免濫用 NULL 值，並謹慎處理自動生成的資料函式庫結構，以防止效能問題。線上 Schema 變更工具如 pt-online-schema-change 和 gh-ost 可用於最小化服務中斷，但需注意其限制和適用場景。最後，整合 CI/CD 流程，搭配版本控制和安全基準組態，能有效提升資料函式庫結構變更的效率和安全性，實作自動化佈署和管理。

資料函式庫設計與管理的最佳實踐

在設計和管理資料函式庫時，選擇適當的資料型別對於效能和可擴充套件性至關重要。本篇文章將探討不同資料型別在MySQL中的使用，以及一些常見的陷阱和最佳實踐。

整數型別（Integer Types）

整數型別通常是識別符的最佳選擇，因為它們的速度快並且與AUTO_INCREMENT相容。AUTO_INCREMENT是一種欄位屬性，可以為每個新行生成一個新的整數值。例如，在一個計費系統中，可能需要為每個客戶生成一個新的發票。使用AUTO_INCREMENT可以確保第一個發票是1，第二個是2，依此類別推。

程式碼範例：使用`AUTO_INCREMENT`

CREATE TABLE invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    invoice_date DATE
);

內容解密：

id欄位被定義為INT型別並具有AUTO_INCREMENT屬性，這意味著MySQL將自動為每個新插入的行生成一個唯一的整數ID。
PRIMARY KEY約束確保id欄位的值是唯一且不為空的。
選擇正確的整數大小對於預期的資料增長非常重要，以避免耗盡整數值的情況發生。

ENUM和SET型別

ENUM和SET型別通常不是識別符的最佳選擇，但可以用於靜態的“定義表”，例如儲存狀態或“型別”值。

程式碼範例：使用ENUM欄位

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    type ENUM('electronics', 'clothing', 'home goods')
);

內容解密：

type欄位被定義為ENUM型別，限制了該欄位只能接受特定的幾個值之一。
ENUM型別適合用於儲存具有固定集合值的欄位，例如訂單狀態或產品型別。
在某些情況下，可以使用ENUM作為識別符，但通常應避免這樣做。

字串型別（String Types）

應盡量避免使用字串型別作為識別符，因為它們佔用空間大且通常比整數型別慢。

程式碼範例：避免使用字串作為識別符

-- 不推薦
CREATE TABLE users (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(100)
);

-- 推薦
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    uuid VARCHAR(36) UNIQUE
);

內容解密：

第一個範例中，id欄位使用了VARCHAR(36)，這不是識別符的最佳選擇。
第二個範例中，id欄位使用了INT並具有AUTO_INCREMENT屬性，而UUID則儲存在單獨的欄位中。
使用整數作為識別符可以提高查詢效能。

UUID值的儲存

如果需要儲存UUID值，應將其轉換為16位元組的數字並儲存在BINARY(16)欄位中，以節省空間。

程式碼範例：儲存UUID值

CREATE TABLE users (
    id BINARY(16) PRIMARY KEY,
    name VARCHAR(100)
);

-- 插入UUID值
INSERT INTO users (id, name) VALUES (UNHEX(REPLACE('123e4567-e89b-12d3-a456-426655440000', '-', '')), 'John Doe');

內容解密：

使用BINARY(16)欄位儲存UUID值可以節省空間並提高效能。
UNHEX和REPLACE函式用於將UUID字串轉換為16位元組的二進位制值。
這種方法比直接儲存UUID字串更有效率。

自動生成結構的陷阱

自動生成的資料函式庫結構可能導致嚴重的效能問題。應避免使用大型VARCHAR欄位儲存一切資料，並確保比較的欄位具有相同的資料型別。

MySQL特定設計陷阱

MySQL的實作方式可能導致一些特定的設計陷阱，例如過多的欄位或連線操作。

過多的欄位

MySQL的儲存引擎API透過在行緩衝區格式中複製行來工作，這可能導致效能問題，特別是在有數百個欄位的情況下。

過多的連線操作

實體屬性值（EAV）設計模式可能導致過多的自連線操作，從而超出MySQL的61個表連線限制。

特別型別的資料

某些型別的資料不直接對應於可用的內建型別，例如IPv4地址。應將其儲存為無符號整數，而不是字串。

程式碼範例：儲存IPv4地址

CREATE TABLE ip_addresses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip_address INT UNSIGNED
);

-- 插入IP位址
INSERT INTO ip_addresses (ip_address) VALUES (INET_ATON('192.168.1.1'));

內容解密：

使用INT UNSIGNED欄位儲存IPv4地址可以節省空間並提高效能。
INET_ATON函式用於將IP位址字串轉換為無符號整數。
這種方法比直接儲存IP位址字串更有效率。

資料函式庫設計與管理中的陷阱與最佳實踐

在資料函式庫設計與管理的過程中，許多看似簡單的決定往往會在後續帶來巨大的問題。本章節將探討一些常見的陷阱，以及如何透過最佳實踐來避免這些問題。

ENUM 與 SET 的正確使用

在設計資料表時，ENUM 和 SET 是兩種常見的資料型別，它們允許欄位從一組預定義的值中選擇。然而，它們的使用場景是不同的。ENUM 允許欄位持有單一值，而 SET 允許欄位持有多個值。

錯誤範例

CREATE TABLE ...(
is_default SET('Y','N') NOT NULL DEFAULT 'N'

這個範例中，is_default 欄位被定義為 SET，但根據其名稱和預設值，它應該只能持有單一值 ‘Y’ 或 ‘N’。在這種情況下，使用 ENUM 會更合適。

正確做法

CREATE TABLE ...(
is_default ENUM('Y','N') NOT NULL DEFAULT 'N'

內容解密：

ENUM('Y','N') 定義了一個列舉型別，只允許 ‘Y’ 或 ‘N’ 兩個值。
使用 ENUM 而非 SET 是因為 is_default 的語義表明它應該是一個布林值，不應該同時為真和假。
這樣的設計可以避免無效值的插入，提高資料的一致性。

避免濫用 NULL

NULL 用於表示未知或缺失的值，但濫用 NULL 可能會導致查詢複雜化，並引入潛在的錯誤。

錯誤範例

CREATE TABLE ... (
dt DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'

使用全零日期作為預設值是一種常見的錯誤，這可能會導致許多問題。

正確做法

CREATE TABLE ... (
dt DATETIME NULL DEFAULT NULL

內容解密：

將 dt 欄位設定為允許 NULL，可以明確表示日期未知或未設定。
使用 NULL 而非魔術值（如全零日期）可以簡化查詢邏輯，避免因特殊值而引起的錯誤。
MySQL 可以索引 NULL 值，這與 Oracle 不同，後者不會索引 NULL 值。

資料函式庫結構管理

隨著資料函式庫例項的增加和業務需求的變化，如何高效、安全地進行結構變更成為一大挑戰。

自動化與工具

使用工具如 Skeema 可以實作資料函式庫結構變更的版本控制和自動化佈署。

CI/CD 流程整合

將資料函式庫結構變更納入 CI/CD 流程，可以確保變更的安全性和一致性。

開源工具與商業方案

開源工具如 Skeema 提供了靈活的 CLI 介面，可以與現有的 CI 工具整合。商業方案如 Flyway 和 Liquibase 也提供了豐富的功能，但需要注意其成本模型和可用性影響。

資料函式庫結構管理與自動化佈署

在現代化的軟體開發流程中，資料函式庫結構（Schema）的管理與變更對於確保系統穩定性和開發效率至關重要。隨著 DevOps 和持續整合/持續佈署（CI/CD）實踐的普及，如何實作資料函式庫結構變更的自動化與規範化，已成為許多技術團隊關注的焦點。

結合 Skeema 與 Buildkite 實作資料函式庫結構變更自主管理

對於希望自主管理資料函式庫結構變更的團隊來說，結合 Skeema 與 Buildkite 不失為一種有效的解決方案。Skeema 是一款開源的資料函式庫結構管理工具，能夠與現有的 CI/CD 工具鏈（如 Buildkite）無縫整合，實作資料函式庫結構變更的自動化佈署。

然而，這種方案需要能夠存取所有環境（包括生產環境）以執行結構變更。這意味著需要與安全團隊密切合作，建立正確的存取控制措施，以充分發揮自動化佈署的優勢。

Vitess 的特殊考量

如果您的團隊正在使用 Vitess 來擴充套件資料函式庫基礎設施，那麼需要注意 Vitess 也提供了管理結構變更的功能。在選擇工具時，務必查閱相關檔案，以避免功能重疊或使用衝突。

選擇合適工具的關鍵因素

在選擇資料函式庫結構變更管理工具時，以下幾個因素值得考慮：

與現有工具鏈的整合度：盡量選擇與現有軟體佈署工具和工作流程相近的方案，以降低團隊的學習成本。
基本的程式碼檢查功能：工具應該具備基本的程式碼檢查（linting）功能，以確保結構變更符合一定的規範要求。例如，檢查新表是否使用了正確的字元集（charset），或者是否包含了不允許的外部索引鍵。
避免人為瓶頸：對於多語言且快速成長的組織，應避免引入人為瓶頸，如將所有資料函式庫和結構變更集中在一個儲存函式庫中。

在生產環境中執行結構變更

在生產環境中執行資料函式庫結構變更時，如何避免對資料函式庫正常執行時間（uptime）或相關服務造成影響是一個重要的挑戰。

原生 DDL 陳述式的使用

MySQL 自版本 5.6 開始引入了非阻塞的結構變更功能，但在早期版本中，這一功能存在諸多限制。直到 MySQL 8.0 正式發布後，原生 DDL 的支援才得到了極大的擴充套件。然而，即使在 MySQL 8.0 及以上版本中，仍有一些結構變更無法使用原生 DDL 完成，例如修改主鍵、更改字元集、啟用單表加密或新增/移除外部索引鍵等。

使用外部工具執行結構變更

如果您的 MySQL 版本不支援所需的原生 DDL，或者需要更精細的控制（如限速機制），那麼可以使用外部工具來執行結構變更。兩個值得注意的開源工具是 Percona 的 pt-online-schema-change 和 GitHub 的 gh-ost。

pt-online-schema-change 的特點與取捨

pt-online-schema-change 因其穩定性和在 MySQL 社群中的長期使用而受到歡迎。它主要利用觸發器（triggers）來實作對大表的結構變更，且對資料函式庫可用性的影響極小。然而，其核心設計也帶來了一些取捨：

觸發器的限制：在 MySQL 8.0 之前，每個表不能有多於一個相同動作的觸發器。這意味著，如果您已經在表上使用了觸發器，那麼在使用 pt-online-schema-change 時就需要額外小心。

-- 使用 pt-online-schema-change 的範例命令
pt-online-schema-change --alter "ADD COLUMN new_column INT" D=your_database,t=your_table --execute

#### 內容解密：

此命令用於在 your_database 資料函式庫的 your_table 表上新增一個名為 new_column 的整數欄位。其中：

--alter 指定了要執行的結構變更。
D=your_database,t=your_table 指定了目標資料函式庫和表。
--execute 表示實際執行變更，而非僅測試。

資料函式庫結構管理與線上變更工具的選擇

在進行資料函式庫結構變更時，選擇適當的工具至關重要。兩款流行的開源工具分別是 pt-online-schema-change 和 gh-ost。這兩款工具都旨在最小化服務中斷的情況下進行結構變更，但它們在實作方式和適用場景上有所不同。

pt-online-schema-change 的限制

pt-online-schema-change 透過在原始表上建立觸發器來追蹤變更並同步到新表。然而，這種方法存在一些限制：

觸發器的限制：在 MySQL 8.0 之前的版本中，不允許在同一張表上建立多個插入、更新或刪除觸發器。如果表上已經存在觸發器，使用 pt-online-schema-change 將會失敗。
效能影響：觸發器的存在會對資料函式庫效能產生影響，特別是在高交易量（transactions per second）的環境中。
平行遷移的限制：由於觸發器的限制，無法使用 pt-online-schema-change 對同一張表進行多個結構變更。
外部索引鍵的約束：雖然 pt-online-schema-change 嘗試支援具有外部索引鍵的表的結構變更，但需要仔細閱讀檔案以確定最合適的選項。

gh-ost 的優勢

gh-ost 由 GitHub 的資料工程團隊開發，它不依賴觸發器，而是透過連線複本並讀取根據行的複製日誌來追蹤變更。這種方法帶來了幾個優勢：

無觸發器：避免了觸發器帶來的效能問題。
更好的版本相容性：gh-ost 對不同版本的 MySQL 有更好的相容性，甚至可以與根據陳述式的複製一起工作（有一些限制）。
已在大型佈署中驗證：gh-ost 已經在大型生產環境中得到驗證。

然而，gh-ost 對外部索引鍵有較嚴格的限制，如果檢測到外部索引鍵，通常會直接離開操作。

選擇合適的工具

如果您的資料函式庫中已經存在外部索引鍵，並且移除它們並不可行，那麼 pt-online-schema-change 可能是更好的選擇，因為它嘗試支援具有外部索引鍵的表的結構變更。
如果您的團隊正在建立持續整合（CI）流程，並且願意避免使用外部索引鍵，那麼 gh-ost 是更安全的選擇。

建構 CI/CD 管道進行結構變更

透過結合使用版本控制工具和線上結構變更工具，可以建立一個完整的 CI/CD 管道，從而簡化資料函式庫結構變更流程並提高工程效率。

組織結構原始碼控制

首先，將每個資料函式庫叢集的結構定義存放在單獨的倉函式庫中。這樣，不同團隊可以根據自己的需求定義不同的檢查規則，並且可以獨立地進行變更。

此圖示展示瞭如何組織和管理不同資料函式庫叢集的結構定義。

內容解密：

獨立倉函式庫：將每個資料函式庫叢集的結構定義放在單獨的倉函式庫中，有助於不同團隊根據自己的需求進行管理。
定義檢查規則：根據團隊的需求，可以在各自的倉函式庫中定義不同的檢查規則，以確保結構變更符合特定的標準。
獨立變更：不同團隊可以根據自己的節奏進行結構變更，提高了靈活性和效率。

綜上所述，選擇合適的線上結構變更工具，並結合 CI/CD 管道，可以顯著提高資料函式庫結構變更的效率和安全性。

資料函式庫結構管理與變更佈署的最佳實踐

在現代化的軟體開發流程中，資料函式庫結構（Schema）的管理與變更佈署是一項關鍵任務。尤其是在快速迭代的開發環境中，如何安全、自動化地將資料函式庫結構變更佈署到不同環境，並確保變更的安全性，是一個重要的挑戰。

從本地變更到生產環境的流程

工程師在本地進行資料函式庫結構變更後，需要經過一系列的測試和驗證，才能將變更佈署到生產環境。一個典型的流程包括：

變更請求：工程師在本地進行資料函式庫結構變更，並將變更提交到版本控制系統。
自動化測試：變更提交後，觸發自動化測試流程，包括語法檢查、邏輯檢查和效能測試等。
審核和批准：變更請求需要經過團隊成員的審核和批准，以確保變更的安全性和正確性。
自動化佈署：審核透過後，變更將自動佈署到不同的環境，包括開發環境、測試環境和預生產環境。
生產環境佈署：最後，變更將佈署到生產環境，並進行最終的驗證和測試。

使用 Pull Request 模式進行變更管理

使用 Pull Request（PR）模式可以有效地管理資料函式庫結構變更。PR 模式允許團隊成員對變更進行審核和討論，以確保變更的安全性和正確性。同時，PR 模式也可以與自動化測試流程整合，以確保變更符合預定的標準。

@startuml
skinparam backgroundColor #FEFEFE

title MySQL 資料函式庫設計與管理最佳實踐

|開發者|
start
:提交程式碼;
:推送到 Git;

|CI 系統|
:觸發建置;
:執行單元測試;
:程式碼品質檢查;

if (測試通過?) then (是)
    :建置容器映像;
    :推送到 Registry;
else (否)
    :通知開發者;
    stop
endif

|CD 系統|
:部署到測試環境;
:執行整合測試;

if (驗證通過?) then (是)
    :部署到生產環境;
    :健康檢查;
    :完成部署;
else (否)
    :回滾變更;
endif

stop

@enduml

此圖示說明瞭使用 PR 模式進行資料函式庫結構變更管理的流程。

圖解說明：

工程師在本地進行資料函式庫結構變更，並提交 PR。
團隊成員對 PR 進行審核和批准。
自動化測試流程對變更進行測試。
測試透過後，變更自動佈署到不同的環境。
最終驗證透過後，變更佈署到生產環境。

定義安全基準組態

為了確保資料函式庫結構變更的安全性，需要定義一個安全基準組態。這包括：

選擇合適的線上 Schema 變更工具：例如 gh-ost 等工具，可以提供安全、自動化的變更流程。
定義變更的安全引數：例如最大 MySQL 執行緒數、最大系統負載等，以確保變更不會對系統造成過大的負擔。

-- gh-ost 組態示例
{
  "max-load": "Threads_running=25",
  "critical-load": "Threads_running=50"
}

程式碼解密：

max-load 設定：當 MySQL 的執行緒數達到 25 時，gh-ost 將暫停變更，以避免對系統造成過大的負擔。
critical-load 設定：當 MySQL 的執行緒數達到 50 時，gh-ost 將立即停止變更，以防止系統當機。

為不同團隊提供靈活的 Pipeline

不同的團隊可能有不同的需求，因此需要提供靈活的 Pipeline 以滿足不同團隊的需求。例如：

自動化佈署：一些團隊可能希望自動化佈署流程，以加快迭代速度。
人工審核：另一些團隊可能希望人工審核流程，以確保變更的安全性。

# skeema 組態示例
{
  "pipeline": {
    "auto-promote": true,
    "require-approval": false
  }
}

程式碼解密：

auto-promote 設定：當設定為 true 時，Skeema 將自動佈署變更到下一個環境。
require-approval 設定：當設定為 false 時，Skeema 不需要人工審核即可佈署變更。

資料函式庫設計與管理的最佳實踐

整數型別（Integer Types）

程式碼範例：使用AUTO_INCREMENT

內容解密：

ENUM和SET型別

程式碼範例：使用ENUM欄位

內容解密：

字串型別（String Types）

程式碼範例：避免使用字串作為識別符

內容解密：

UUID值的儲存

程式碼範例：儲存UUID值

內容解密：

自動生成結構的陷阱

MySQL特定設計陷阱

過多的欄位

過多的連線操作

特別型別的資料

程式碼範例：儲存IPv4地址

內容解密：

資料函式庫設計與管理中的陷阱與最佳實踐

ENUM 與 SET 的正確使用

錯誤範例

正確做法

內容解密：

避免濫用 NULL

錯誤範例

正確做法

內容解密：

資料函式庫結構管理

自動化與工具

CI/CD 流程整合

開源工具與商業方案

資料函式庫結構管理與自動化佈署

結合 Skeema 與 Buildkite 實作資料函式庫結構變更自主管理

Vitess 的特殊考量

選擇合適工具的關鍵因素

在生產環境中執行結構變更

原生 DDL 陳述式的使用

使用外部工具執行結構變更

pt-online-schema-change 的特點與取捨

#### 內容解密：

資料函式庫結構管理與線上變更工具的選擇

pt-online-schema-change 的限制

gh-ost 的優勢

選擇合適的工具

建構 CI/CD 管道進行結構變更

組織結構原始碼控制

內容解密：

資料函式庫結構管理與變更佈署的最佳實踐

從本地變更到生產環境的流程

使用 Pull Request 模式進行變更管理

圖解說明：

定義安全基準組態

程式碼解密：

為不同團隊提供靈活的 Pipeline

程式碼解密：

程式碼範例：使用`AUTO_INCREMENT`