MySQL 交易 ACID 特性與效能

– 12 分鐘閱讀

在資料函式庫系統中，交易管理是確保資料一致性和完整性的關鍵機制。MySQL 的 InnoDB 儲存引擎提供 ACID 特性，確保交易的可靠性。然而，交易的鎖定機制和隔離級別設定也可能影響資料函式庫效能。本文將探討如何平衡交易的 ACID 特性與效能需求，並介紹一些常見的效能最佳化技巧。長時間執行的交易容易造成鎖定衝突和 undo log 膨脹，進而影響系統吞吐量。透過調整 InnoDB purge 引數，可以有效控制 undo log 的大小，並提高 purge 效率。此外，群組提交機制可以合併多個交易的磁碟 I/O 操作，降低延遲並提升整體效能。選擇適當的交易隔離級別也是效能調校的關鍵。SERIALIZABLE 隔離級別提供最高的一致性，但會帶來較高的鎖定開銷；而 REPEATABLE READ 隔離級別則在一致性和效能之間取得了平衡。

交易（Transactions）的重要性與ACID特性

在資料函式倉管理系統中，交易（Transactions）扮演著至關重要的角色。它們將多個變更操作組合在一起，確保這些變更要麼全部成功，要麼全部失敗，從而維護資料的一致性和完整性。本章將探討交易的本質、ACID特性，以及交易對鎖定和效能的影響。

交易與ACID特性

交易是一種容器，可以包含一或多個SQL陳述式。交易的ACID特性是其核心，分別代表原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）和永續性（Durability）。

原子性（Atomicity）

原子性確保交易中的所有變更要麼全部提交，要麼全部回復。這種特性避免了部分提交導致的資料不一致問題。例如，在銀行轉帳交易中，從付款人帳戶扣款和向收款人帳戶存款必須作為一個原子操作，否則可能導致資金丟失。

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'payer';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'receiver';
COMMIT;

內容解密：

  1. BEGIN; 開始一個新的交易。
  2. 第一個 UPDATE 陳述式從付款人帳戶扣款。
  3. 第二個 UPDATE 陳述式向收款人帳戶存款。
  4. COMMIT; 提交交易，確保兩步操作都成功。

一致性（Consistency）

一致性確保交易提交後，資料函式庫保持一致狀態。這依賴於業務邏輯和資料函式庫約束（如外部索引鍵）來維護。例如，不能為不存在的實體建立銀行帳戶。

CREATE TABLE entities (
    entity_id INT PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    entity_id INT,
    FOREIGN KEY (entity_id) REFERENCES entities(entity_id)
);

內容解密：

  1. entities 表儲存實體資訊。
  2. accounts 表儲存帳戶資訊，並透過 entity_id 與 entities 表建立外部索引鍵關係，確保每個帳戶都對應一個存在的實體。

交易對鎖定和效能的影響

交易在維護資料一致性的同時，也可能引入額外的鎖定和效能開銷。特別是在高並發環境下，交易的隔離級別和鎖定策略對系統效能有著重要影響。

群組提交（Group Commit）

群組提交是一種最佳化技術，透過將多個交易的提交操作合併處理，提高系統的吞吐量和降低延遲。

交易的特性與影響

在資料函式倉管理系統中，交易（Transaction）是一個非常重要的概念。它確保了資料函式庫操作的原子性、一致性、隔離性和永續性（ACID）。本篇文章將探討交易的特性以及它們對資料函式庫系統的影響。

交易的四個特性

交易的四個特性是原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）和永續性（Durability）。

原子性

原子性確保了一個交易中的所有操作要麼全部成功，要麼全部失敗。這意味著如果交易中的任何一個操作失敗了，整個交易就會被回復，所有已經執行的操作都會被復原。

一致性

一致性確保了資料函式庫在交易前後保持一致的狀態。也就是說，交易不能違反資料函式庫的完整性約束。

隔離性

隔離性確保了多個交易可以並發執行而不會相互幹擾。也就是說，每個交易都看到的是一個一致的資料函式庫狀態，即使其他交易正在並發執行。

永續性

永續性確保了一旦交易被提交，其結果就是永久的，即使系統當機也不會丟失。

交易對資料函式庫系統的影響

雖然交易提供了一種非常有用的機制來確保資料函式庫的一致性和完整性，但它們也對資料函式庫系統產生了一定的影響。

鎖定資源

當一個交易正在執行時，它會鎖定某些資源，例如表格或行。這意味著其他交易可能需要等待這些資源被釋放，這可能會導致效能問題。

START TRANSACTION;
SELECT * FROM customers WHERE customer_id = 1 FOR UPDATE;
-- 其他操作
COMMIT;

記憶體使用

大型的交易可能會使用大量的記憶體來儲存鎖定資訊和 undo 日誌。這可能會導致記憶體不足的問題。

鎖定記憶體使用過多的後果

如果交易的鎖定記憶體使用過多，可能會導致以下問題：

  • 交易被中止並出現 ER_LOCK_TABLE_FULL 錯誤。
  • 系統效能下降，因為 InnoDB 的 buffer pool 被大量使用於鎖定資訊。
  • 其他交易被阻塞，無法執行。

metadata 鎖定

metadata 鎖定是在查詢表格時取得的分享鎖定，並在交易結束時釋放。這可能會導致其他連線無法執行 DDL 陳述式，例如 OPTIMIZE TABLE。

最佳化交易的建議

為了減少交易的負面影響，可以採取以下措施：

  • 使用讀取専用交易（START TRANSACTION READ ONLY），可以減少鎖定的需求。
  • 盡量縮短交易的執行時間，以減少鎖定資源的時間。
  • 定期檢查和最佳化資料函式庫的效能，以減少交易的負面影響。

使用讀取専用交易的優點

使用讀取専用交易可以帶來以下優點：

  • 減少鎖定的需求，從而提高系統的並發效能。
  • 減少記憶體的使用，從而降低系統當機的風險。

縮短交易執行時間的方法

可以透過以下方法來縮短交易的執行時間：

  • 最佳化查詢陳述式，以減少查詢的時間。
  • 減少交易的範圍，以減少鎖定的資源。
  • 使用索引，以提高查詢的速度。

交易管理與效能最佳化

在資料函式倉管理中，交易（Transaction）扮演著至關重要的角色，尤其是在需要確保資料一致性和完整性的應用場景中。MySQL的InnoDB儲存引擎支援遵循ACID原則的交易處理，這使得它能夠在面對系統故障或並發操作時，仍然能夠保持資料的可靠性。

長時間執行的交易及其影響

長時間執行的交易可能會對資料函式庫效能產生負面影響。當一個交易長時間佔用鎖定資源時，其他需要存取相同資源的交易或查詢就不得不等待，從而導致系統整體效能下降。此外，長時間執行的交易還會導致undo log的增長，進而影響查詢效能。

Undo Log的管理

Undo Log是用於儲存交易變更前資料的記錄，它們對於交易的回復和一致性讀取至關重要。然而，長時間執行的交易，尤其是那些使用REPEATABLE READ隔離級別的交易，可能會導致undo log迅速增長，從而佔用大量磁碟空間。在MySQL 5.7及更早版本中，這可能會導致ibdata1檔案變得非常大。

如何管理Undo Log
  • 調整innodb_purge_batch_size: 控制每次purge操作的undo log頁面數量。
  • 調整innodb_purge_threads: 設定平行purge執行緒的數量，以提高purge操作的效率。
  • 設定innodb_max_purge_lag: 當history list長度超過此值時，系統會對DML操作新增延遲，以減緩history list的增長速度。
  • 設定innodb_max_purge_lag_delay: 當history list長度超過innodb_max_purge_lag時，DML操作的最大延遲時間。

群組提交（Group Commit）最佳化

為了提高交易的永續性（Durability），InnoDB預設會在交易提交時將變更同步到磁碟。然而，這種做法可能會導致磁碟I/O成為效能瓶頸。群組提交機制透過將多個交易合併提交，以減少磁碟I/O操作的次數，從而提高系統的整體效能。

群組提交的組態選項

  • binlog_group_commit_sync_delay: 設定等待更多交易提交的延遲時間（毫秒），以便將它們合併提交。預設值為0。
  • binlog_group_commit_sync_no_delay_count: 設定在完成群組提交之前，允許在佇列中累積的最大交易數量。

內容解密：

  1. 長時間執行的交易的影響：長時間執行的交易會佔用鎖定資源，導致其他查詢或交易等待，並可能導致undo log迅速增長。
  2. Undo Log的管理：透過調整相關引數，如innodb_purge_batch_size和innodb_purge_threads，可以提高undo log的purge效率。
  3. 群組提交最佳化：透過設定binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count，可以最佳化交易的提交效能。

程式碼範例：

-- 檢視目前的InnoDB狀態
SHOW ENGINE INNODB STATUS;

-- 調整purge執行緒數量
SET GLOBAL innodb_purge_threads = 8;

-- 設定群組提交的延遲時間
SET GLOBAL binlog_group_commit_sync_delay = 100;

內容解密：

此範例展示瞭如何檢視InnoDB的目前狀態，以及如何動態調整innodb_purge_threads和binlog_group_commit_sync_delay引數，以最佳化系統效能。

交易隔離級別

在前一章中，您瞭解了隔離是事務的重要屬性。事實證明，回答兩個事務是否隔離並不那麼簡單，因為答案取決於所需的隔離程度。隔離程度是透過事務隔離級別定義的。

InnoDB 支援 SQL:1992 標準定義的四種事務隔離級別，它們按隔離程度降序排列：SERIALIZABLE、REPEATABLE READ、READ COMMITTED 和 READ UNCOMMITTED。預設的事務隔離級別是可重複讀（REPEATABLE READ）。本章將介紹每種隔離級別，並討論它們的工作原理和對鎖定的影響。

比較不同事務隔離級別下的鎖定

為了比較在不同事務隔別級下更新列時所取得的鎖定，將使用更新斯洛伐克布拉迪斯拉發區城市的範例。world.city 表中斯洛伐克有三個城市：

mysql> SELECT ID, Name, District
FROM world.city
WHERE CountryCode = 'SVK';
+
---
---
+
---
-
---
-
---
-+
---
-
---
-
---
-
---
-
---
-+
| ID | Name | District |
+
---
---
+
---
-
---
-
---
-+
---
-
---
-
---
-
---
-
---
-+
| 3209 | Bratislava | Bratislava |
| 3210 | Košice | Východné Slovensko |
| 3211 | Prešov | Východné Slovensko |
+
---
---
+
---
-
---
-
---
-+
---
-
---
-
---
-
---
-
---
-+
3 rows in set (0.0032 sec)

內容解密：

此查詢用於檢索斯洛伐克境內所有城市的ID、名稱和區網域名稱。透過 CountryCode 索引縮小搜尋範圍至三個城市，然後對 District 進行非索引過濾，以找出符合區名的城市。這有助於展示 SERIALIZABLE、REPEATABLE READ 和 READ COMMITTED 事務隔離級別下不同的鎖定數量。

可序列化（SERIALIZABLE）

SERIALIZABLE 隔離級別是最嚴格的。除非啟用了自動提交（autocommit）且沒有明確啟動事務，否則所有陳述式都會取得鎖定。對於 SELECT 陳述式，這等同於新增 FOR SHARE。這確保了事務的所有方面都是可重複的，但也意味著它是最容易產生鎖定的事務隔離級別。列表 12-1 展示了一個 SELECT 陳述式取得鎖定的範例。

-- Connection 1
Connection 1> SET transaction_isolation = 'SERIALIZABLE';
Query OK, 0 rows affected (0.0007 sec)
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)

內容解密：

此範例展示瞭如何在 SERIALIZABLE 隔離級別下啟動一個事務。在此級別下，除了啟用了自動提交的 SELECT 陳述式外，所有陳述式都會取得鎖定，以確保事務的可重複性。

圖示：事務隔離級別比較

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title MySQL 交易 ACID 特性與效能

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

此圖示展示了四種事務隔離級別之間的比較，從最嚴格的 SERIALIZABLE 到最不嚴格的 READ UNCOMMITTED。

內容解密：

此圖示用於展示四種事務隔離級別之間的關係。從 SERIALIZABLE 到 READ UNCOMMITTED，隔離程度逐漸降低。瞭解這些級別有助於選擇合適的隔離策略，以平衡資料一致性和系統效能。

交易隔離級別詳解

在資料函式倉管理系統中，交易隔離級別（Transaction Isolation Level）是確保資料一致性和平行控制的重要機制。MySQL 的 InnoDB 儲存引擎支援多種交易隔離級別，包括 SERIALIZABLE、REPEATABLE READ、READ COMMITTED 和 READ UNCOMMITTED。本文將探討 SERIALIZABLE 和 REPEATABLE READ 這兩種隔離級別的鎖定機制和應用場景。

SERIALIZABLE 隔離級別

SERIALIZABLE 是最高的隔離級別，它確保交易之間是完全串列化的。在此級別下，InnoDB 對讀取操作也使用鎖定機制，以防止幻讀（Phantom Read）的發生。

鎖定機制

在 SERIALIZABLE 級別下，InnoDB 會對查詢涉及的資料行和索引記錄加鎖。這些鎖包括分享鎖（S）和排他鎖（X），以及間隙鎖（Gap Lock），以防止其他交易插入或修改相關資料。

-- 連線 1
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT ID, Name, Population
FROM world.city
WHERE CountryCode = 'SVK'
AND District = 'Bratislava';

執行上述查詢後，InnoDB 會對相關的資料行和索引加鎖。透過查詢 performance_schema.data_locks 表，可以觀察到鎖定的詳細資訊：

-- 連線 2
SELECT index_name, lock_type, lock_mode, lock_data
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND lock_type = 'RECORD'
AND thread_id = 1560
ORDER BY index_name, lock_data DESC;

輸出結果顯示了鎖定的索引名稱、鎖型別、鎖模式和鎖定的資料：

+
---
-
---
-
---
--+
---
-
---
-
---
+
---
-
---
-
---
-
---
+
---
-
---
-
---
--+
| index_name | lock_type | lock_mode | lock_data |
+
---
-
---
-
---
--+
---
-
---
-
---
+
---
-
---
-
---
-
---
+
---
-
---
-
---
--+
| CountryCode | RECORD | S,GAP | 'SVN', 3212 |
| CountryCode | RECORD | S | 'SVK', 3211 |
| CountryCode | RECORD | S | 'SVK', 3210 |
| CountryCode | RECORD | S | 'SVK', 3209 |
| PRIMARY | RECORD | S,REC_NOT_GAP | 3211 |
| PRIMARY | RECORD | S,REC_NOT_GAP | 3210 |
| PRIMARY | RECORD | S,REC_NOT_GAP | 3209 |
+
---
-
---
-
---
--+
---
-
---
-
---
+
---
-
---
-
---
-
---
+
---
-
---
-
---
--+

#### 內容解密：

  1. index_name: 表示被鎖定的索引名稱，例如 CountryCode 和 PRIMARY。
  2. lock_type: 表示鎖定型別，此處為 RECORD，表示行級鎖。
  3. lock_mode: 表示鎖定模式，例如 S（分享鎖）、X（排他鎖）、S,GAP（分享間隙鎖）等。
  4. lock_data: 表示被鎖定的資料，例如 'SVK', 3209 表示 CountryCode 為 'SVK' 且主鍵為 3209 的記錄。

當執行更新操作時，鎖定機制會更為嚴格：

-- 連線 1
UPDATE world.city
SET Population = Population * 1.10
WHERE CountryCode = 'SVK'
AND District = 'Bratislava';

此時，查詢 performance_schema.data_locks 表會顯示更嚴格的鎖定：

+
---
-
---
-
---
--+
---
-
---
-
---
+
---
-
---
-
---
-
---
+
---
-
---
-
---
--+
| index_name | lock_type | lock_mode | lock_data |
+
---
-
---
-
---
--+
---
-
---
-
---
+
---
-
---
-
---
-
---
+
---
-
---
-
---
--+
| CountryCode | RECORD | X,GAP | 'SVN', 3212 |
| CountryCode | RECORD | X | 'SVK', 3211 |
| CountryCode | RECORD | X | 'SVK', 3210 |
| CountryCode | RECORD | X | 'SVK', 3209 |
| PRIMARY | RECORD | X,REC_NOT_GAP | 3211 |
| PRIMARY | RECORD | X,REC_NOT_GAP | 3210 |
| PRIMARY | RECORD | X,REC_NOT_GAP | 3209 |
+
---
-
---
-
---
--+
---
-
---
-
---
+
---
-
---
-
---
-
---
+
---
-
---
-
---
--+

#### 內容解密：

  1. 更新操作將分享鎖升級為排他鎖（X），以確保資料的一致性。
  2. 鎖定範圍包括所有相關的 CountryCode 索引記錄和主鍵記錄，以及間隙鎖，以防止幻讀。

REPEATABLE READ 隔離級別

REPEATABLE READ 是 InnoDB 的預設隔離級別。它確保在同一交易中，多次讀取相同的資料會得到一致的結果，即使其他交易已經修改了這些資料。

快照讀取

在 REPEATABLE READ 級別下，InnoDB 使用快照讀取（Snapshot Read）來實作一致性讀取。快照是在交易開始時或第一條查詢陳述式執行時建立的。

-- 連線 1
SET transaction_isolation = 'REPEATABLE-READ';
START TRANSACTION;
SELECT ID, Name, Population
FROM world.city
WHERE CountryCode = 'SVK'
AND District = 'Bratislava';

鎖定機制

雖然 REPEATABLE READ 使用快照讀取來避免鎖定，但在某些情況下仍然需要鎖定。例如，當執行更新操作時，InnoDB 會對相關資料行加排他鎖。

玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。