2025年5月19日 星期一

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 應用程式的基礎。希望這些說明能夠幫助你更好地掌握這些重要的概念。如果你有更具體的問題，歡迎隨時提出。

於 沒有留言:
訂閱： 文章 (Atom)
  • 《面試官別再問》PHP運用多執行緒(Multi-thread)實現非阻塞方法
    詳細的請參考： 多程序還是多執行緒的選擇和區別 ,感覺這位牛人寫的特別清楚。下面我們再來看看php環境下使用多程序和多執行緒要注意的。 PHP是單進程同步模型，一個請求對應一個進程，I/O是同步阻塞的。通過nginx/apache/php-fpm等服務的擴展，才使得P...
  • 使用 Laravel、Redis 和 LINE Messaging API 建立客服系統
    這個系統的目標是讓 LINE 的訊息可以透過網頁介面讓客服人員回覆。 好的，這是一個從 LINE 用戶發送訊息開始，到客服人員在網頁上回覆的完整步驟流程： 打造高併發 LINE 客服系統：設計與實踐 前言：以技術解決即時通訊挑戰 在高流量即時通訊場景下，系統的穩定性與響應速度是企...
  • Laravel9 + Redis 如何解決高併發交易及處理限量商品的庫存超賣問題?
    在高併發交易場景下，使用 Laravel 9 搭配 Redis 在 AWS EC2 和 RDS 環境中，需要一套全面的策略來應對挑戰。這涉及到架構設計、程式碼優化、服務配置和監控。 高併發交易問題的核心挑戰 資料庫鎖 (Database Locks) ：高併發寫入導致行鎖、表鎖...