MySQL SQL 語法中的 constraint、EXPLAIN 進行 SQL tuning 的實作、index 的規劃以及 DB lock 等相關知識

 

Constraint (約束)

Constraint 是用來確保資料庫中資料的完整性和一致性的規則。它們定義了表中允許儲存的資料類型、格式和數值範圍，防止了無效或不一致的資料進入資料庫。MySQL 中常見的 constraint 類型包括：

• NOT NULL: 確保欄位的值不能為 NULL。
• UNIQUE: 確保欄位中的所有值都是唯一的。一個表可以有多個 UNIQUE 約束。
• PRIMARY KEY: 唯一標識表中的每一行，且值不能為 NULL。一個表只能有一個 PRIMARY KEY 約束。實際上，PRIMARY KEY 是 UNIQUE 和 NOT NULL 的組合。
• FOREIGN KEY: 用於建立和強化兩個表之間的連接。它引用另一個表中的 PRIMARY KEY 或 UNIQUE 欄位，確保關聯表中的資料一致性。
• CHECK: 定義欄位中允許的值的範圍或條件。MySQL 8.0.16 版本之後開始支援 CHECK 約束的強制執行。
• DEFAULT: 為欄位指定預設值，當插入新記錄時沒有為該欄位指定值，則使用預設值。

語法範例：

假設我們有一個名為 users 的表，包含 id、username 和 email 欄位。

SQL

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    age INT CHECK (age >= 18),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

在這個範例中：

• users 表的 id 是主鍵且自動遞增。
• username 不允許為空且必須唯一。
• email 不允許為空。
• age 必須大於等於 18。
• created_at 的預設值是目前的 timestamp。
• orders 表的 user_id 是外鍵，引用了 users 表的 id 欄位，確保每個訂單都屬於一個已存在的用戶。

你可以使用 ALTER TABLE 語句來新增、修改或刪除 constraint。

SQL

-- 新增 UNIQUE 約束到已存在的 email 欄位
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

-- 移除 UNIQUE 約束
ALTER TABLE users DROP INDEX unique_email;

-- 新增 FOREIGN KEY 約束
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

-- 移除 FOREIGN KEY 約束
ALTER TABLE orders DROP FOREIGN KEY fk_user;

使用 EXPLAIN 進行 SQL Tuning

EXPLAIN 語句是 MySQL 中用於分析 SQL 查詢執行計畫的重要工具。通過分析 EXPLAIN 的輸出，你可以了解 MySQL 如何執行你的查詢，從而找出潛在的性能瓶頸並進行優化。

基本語法：

SQL

EXPLAIN SELECT column1, column2 FROM table_name WHERE condition;

EXPLAIN 的輸出會包含多個欄位，以下是一些重要的欄位及其含義：

• id: 查詢的標識符。對於複雜的查詢，可能會有多個 id。
• select_type: 查詢的類型，例如 SIMPLE (簡單查詢，不包含子查詢或 UNION)、PRIMARY (最外層的 SELECT)、SUBQUERY (子查詢)、DERIVED (從子查詢產生的臨時表) 等。
• table: 查詢涉及的表名。
• partitions: 查詢涉及的分區。
• type: 訪問類型，表示 MySQL 如何查找表中的行。常見的類型按性能從最佳到最差依次為：
  • system: 表中只有一行記錄。
  • const: 使用常數值進行查詢。
  • eq_ref: 對於前一個表中的每一行，最多只會匹配當前表中的一行（通常用於主鍵或唯一索引的連接）。
  • ref: 對於前一個表中的每一行，可能會匹配當前表中的多行（使用了非唯一索引）。
  • fulltext: 使用了全文索引。
  • ref_or_null: 類似 ref，但會額外搜索包含 NULL 值的行。
  • index_merge: 使用了多個索引的合併結果。
  • unique_subquery: 在 IN 子查詢中使用唯一索引。
  • index_subquery: 在 IN 子查詢中使用非唯一索引。
  • range: 在給定範圍內檢索行（使用了索引的範圍查詢，如 BETWEEN、>、< 等）。
  • index: 全索引掃描，只掃描索引樹。
  • ALL: 全表掃描，性能最差。
• possible_keys: 可能用於查詢的索引。
• key: MySQL 實際選擇使用的索引。如果為 NULL，則表示沒有使用索引。
• key_len: MySQL 使用的索引的長度（以位元組為單位）。通過這個值可以判斷使用了複合索引的哪些部分。
• ref: 與索引比較的欄位或常數。
• rows: MySQL 估計為了找到所需的行而需要掃描的行數。這個值越小越好。
• filtered: 按表條件過濾後，結果集的百分比估計值。
• Extra: 包含一些額外的資訊，例如：
  • Using index: 表示查詢可以直接使用索引中的資訊而不需要回表查詢（覆蓋索引）。
  • Using where: 表示 MySQL 需要在儲存引擎檢索行後再進行 WHERE 條件的過濾。
  • Using temporary: 表示 MySQL 需要建立一個臨時表來處理查詢結果，通常發生在 GROUP BY、ORDER BY 等操作中。
  • Using filesort: 表示 MySQL 需要對結果進行外部排序，通常性能較差。
  • Select tables optimized away: 表示 MySQL 能夠直接從優化器中獲取結果，而不需要訪問表。

實作 SQL Tuning 的步驟：

1. 使用 EXPLAIN 分析查詢： 對你想要優化的 SQL 查詢執行 EXPLAIN 命令，觀察其輸出結果。
2. 關注關鍵欄位： 特別關注 type、possible_keys、key、rows 和 Extra 欄位。
3. 分析性能瓶頸：
   • 如果 type 是 ALL 或 index，並且 rows 的值很大，通常表示需要添加或優化索引。
   • 如果 key 是 NULL，表示沒有使用到索引，需要檢查是否可以添加合適的索引。
   • 如果 Extra 中出現 Using temporary 或 Using filesort，通常表示查詢需要額外的資源進行處理，應該盡量避免。
4. 優化 SQL 查詢或索引： 根據 EXPLAIN 的分析結果，考慮以下優化策略：
   • 添加缺失的索引： 為經常在 WHERE 子句、JOIN 條件、ORDER BY 或 GROUP BY 子句中使用的欄位添加索引。
   • 優化現有的索引： 檢查索引是否被有效利用，例如，對於複合索引，查詢條件是否使用了索引的最左前綴。
   • 重寫 SQL 查詢： 有時候，改變查詢的寫法可以提高性能，例如避免在 WHERE 子句中對索引欄位進行函數操作，或者優化子查詢。
   • 分析和優化表結構： 根據查詢模式調整表結構，例如適當的資料類型選擇。
5. 再次使用 EXPLAIN 驗證： 在進行優化後，再次對相同的查詢執行 EXPLAIN，檢查執行計畫是否有所改善。目標是看到 type 變得更優化，key 使用了相關的索引，rows 的值顯著減少，並且 Extra 中不再出現性能較差的提示。

如何規劃 Index (索引)

Index 是提高資料庫查詢性能的關鍵。它可以幫助 MySQL 快速定位到表中的特定行，而不需要掃描整個表。然而，索引也會增加寫入操作的成本，因為每次插入、更新或刪除資料時，MySQL 也需要更新索引。因此，合理規劃索引非常重要。

規劃索引的原則：

1. 識別經常被查詢的欄位： 為經常在 WHERE 子句、JOIN 條件、ORDER BY 和 GROUP BY 子句中使用的欄位建立索引。
2. 考慮查詢的類型：
   • 等值查詢 (=): 通常為這些欄位建立 B-tree 索引。
   • 範圍查詢 (>, <, BETWEEN, LIKE 'prefix%'): B-tree 索引也適用於範圍查詢。注意，以 %value' 開頭的 LIKE 查詢無法有效利用 B-tree 索引。
   • 排序 (ORDER BY): 為排序欄位建立索引可以避免 filesort 操作。
   • 分組 (GROUP BY): 為分組欄位建立索引可以提高分組效率。
3. 使用複合索引： 如果一個查詢經常涉及到多個欄位，可以考慮建立複合索引。複合索引的欄位順序很重要，應該將最常使用的欄位放在最前面（最左前綴原則）。
4. 限制索引的數量： 過多的索引會增加寫入操作的成本，並可能導致優化器選擇錯誤的索引。只為真正需要的欄位建立索引。
5. 考慮欄位的選擇性 (Cardinality)： 選擇性高的欄位（即欄位中唯一值的比例較高）更適合建立索引。例如，像性別這樣的欄位，其選擇性很低，建立索引的效益可能不大。可以使用 SHOW INDEX FROM table_name; 查看索引的 Cardinality。
6. 定期審查和清理不使用的索引： 隨著應用程式的變化，某些索引可能不再被使用。定期檢查並刪除這些無用的索引可以提高性能。
7. 全文索引 (Fulltext Index)： 對於需要進行全文搜索的文本欄位，可以使用全文索引。
8. 空間資料索引 (Spatial Index)： 對於儲存空間資料的欄位，可以使用空間資料索引。

常見的索引類型：

• B-tree 索引 (最常見)： 適用於等值查詢、範圍查詢和排序。
• Hash 索引： 適用於等值查詢，但不支持範圍查詢和排序。Memory 儲存引擎預設使用 Hash 索引。
• Fulltext 索引： 用於全文搜索。
• Spatial 索引： 用於空間資料類型。

創建索引的語法：

SQL

-- 創建單列索引
CREATE INDEX index_name ON table_name (column_name);

-- 創建複合索引
CREATE INDEX index_name ON table_name (column1, column2);

-- 創建 UNIQUE 索引
CREATE UNIQUE INDEX index_name ON table_name (column_name);

-- 創建全文索引
CREATE FULLTEXT INDEX index_name ON table_name (text_column);

-- 使用 ALTER TABLE 語句創建索引
ALTER TABLE table_name ADD INDEX index_name (column_name);
ALTER TABLE table_name ADD UNIQUE INDEX index_name (column_name);
ALTER TABLE table_name ADD FULLTEXT INDEX index_name (text_column);

查看索引：

SQL

SHOW INDEX FROM table_name;

刪除索引：

SQL

DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;

理解 DB Lock (資料庫鎖)

資料庫鎖是資料庫管理系統用來控制並發存取資源的機制，以確保資料的一致性和完整性。當多個事務同時存取相同的資料時，鎖可以防止它們互相干擾，導致資料錯誤。

鎖的類型：

MySQL 中常見的鎖類型包括：

1. 鎖的粒度 (Lock Granularity)：

   • 表級鎖 (Table-Level Locks)： 對整個表進行鎖定。開銷小，加鎖快，但並發性能較差。MyISAM 和 Memory 等儲存引擎主要使用表級鎖。
   • 行級鎖 (Row-Level Locks)： 只鎖定需要存取的行。開銷大，加鎖慢，但並發性能好。InnoDB 儲存引擎主要使用行級鎖。
   • 頁級鎖 (Page-Level Locks)： 介於表級鎖和行級鎖之間，鎖定一個頁面（資料庫中儲存的基本單元）。開銷和並發性能也介於兩者之間。BDB 儲存引擎使用頁級鎖。

2. 鎖的模式 (Lock Modes)：

   • 共享鎖 (Shared Lock, S 鎖，讀鎖)： 允許多個事務同時讀取同一資源，但不允許任何事務修改該資源。
   • 排他鎖 (Exclusive Lock, X 鎖，寫鎖)： 當一個事務持有資源的排他鎖時，其他任何事務都不能對該資源進行讀取或寫入操作。

3. 意向鎖 (Intention Locks)： InnoDB 為了支持多粒度鎖（表級鎖和行級鎖並存）而引入的。意向鎖表示事務希望在表中的某些行上獲得共享鎖或排他鎖。

   • 意向共享鎖 (Intention Shared Lock, IS 鎖)： 表示事務打算在表中的某些行上加共享鎖。
   • 意向排他鎖 (Intention Exclusive Lock, IX 鎖)： 表示事務打算在表中的某些行上加排他鎖。

鎖的相容性矩陣 (InnoDB)：

	X (排他)	S (共享)	IX (意向排他)	IS (意向共享)
X	不相容	不相容	不相容	不相容
S	不相容	相容	不相容	相容
IX	不相容	不相容	相容	相容
IS	不相容	相容	相容	相容

常見的鎖問題：

• 死鎖 (Deadlock)： 當兩個或多個事務互相持有對方需要的鎖，導致所有事務都無法繼續執行時，就會發生死鎖。MySQL 會自動檢測死鎖並回滾其中一個事務。
• 鎖競爭 (Lock Contention)： 當大量事務嘗試存取相同的資源時，會導致鎖的競爭，降低並發性能。

如何處理鎖問題：

• 優化 SQL 查詢： 減少事務持有鎖的時間，例如盡量在事務中只鎖定需要的資源，並盡快釋放鎖。
• 合理設計事務： 保持事務的簡短，避免長時間的事務持有大量的鎖。
• 使用適當的鎖粒度： 對於高並發的應用，應盡量使用行級鎖。
• 避免多個事務以不同的順序存取相同的資源： 這可能會導致死鎖。確保事務以相同的順序獲取鎖。
• 設定合理的鎖等待超時時間： 當事務等待鎖的時間超過設定值時，MySQL 會返回錯誤，避免事務永久阻塞。
• 分析鎖的等待情況： 可以使用 SHOW ENGINE INNODB STATUS 命令查看 InnoDB 的狀態信息，包括鎖的等待情況。
• 使用樂觀鎖 (Optimistic Locking)： 在某些應用場景下，可以使用樂觀鎖來減少實際的鎖競爭。樂觀鎖不是真正的鎖，而是一種並發控制機制，通過版本號等方式來檢查資料是否被修改。

理解 constraint、EXPLAIN、index 和 DB lock 是開發高性能和高可靠性 MySQL 應用程式的基礎。希望這些說明能夠幫助你更好地掌握這些重要的概念。如果你有更具體的問題，歡迎隨時提出。