在高併發的資料函式庫環境中，鎖定衝突是影響效能的重要因素。本文將探討如何減少鎖定衝突，並深入分析索引和外部索引鍵對鎖定的影響。最佳化交易大小和執行時間、選擇合適的索引、調整交易隔離級別等策略可以有效減少鎖定的數量和持有時間。此外，停用自適應雜湊索引和調整 max_write_lock_count 等組態選項也能降低鎖定的影響。預防性鎖定雖然可以避免死鎖，但也可能增加鎖定持有時間，需要謹慎使用。
降低鎖定衝突的策略
在資料函式倉管理系統中，鎖定是確保資料一致性和完整性的重要機制。然而，鎖定也可能導致效能問題，特別是在高並發的環境中。本章將探討如何降低鎖定衝突的策略，包括減少鎖定的數量和持有時間、調整組態以減少鎖定的影響等。
減少鎖定的數量和持有時間
減少鎖定的數量和持有時間是降低鎖定衝突最直接的方法。以下是一些實用的策略：
- 最佳化交易大小和執行時間：盡量減少交易的規模和執行時間，以降低鎖定的持有時間。這可以透過最佳化查詢、減少不必要的操作等方式來實作。
- 選擇合適的索引：合適的索引可以減少查詢所需的鎖定數量，從而降低鎖定衝突的可能性。
- 調整交易隔離級別：交易隔離級別會影響鎖定的數量和持有時間。例如，使用
READ COMMITTED隔離級別可以減少鎖定的衝突。
調整組態以減少鎖定的影響
除了減少鎖定的數量和持有時間外，調整組態也可以幫助減少鎖定的影響。以下是一些可調整的組態選項：
- 停用自適應雜湊索引：自適應雜湊索引可以提高查詢效能，但也可能導致鎖定衝突。如果發現自適應雜湊索引成為瓶頸，可以考慮停用它。
- 調整
max_write_lock_count：這個引數控制在給予讀鎖定優先權之前，可以授予多少個寫鎖定。適當地調整這個引數可以幫助避免讀鎖定請求被餓死。
預防性鎖定
預防性鎖定是一種主動的鎖定策略，透過在交易開始時就鎖定所需的資源，可以減少死鎖的發生。但是，這種策略也可能增加鎖定的持有時間，因此需要謹慎使用。
使用預防性鎖定的時機
- 當交易需要執行多個查詢，並且這些查詢之間存在依賴關係時，可以使用預防性鎖定來避免死鎖。
- 當需要確保資料的一致性和完整性時，可以使用預防性鎖定來鎖定相關的資源。
索引與外部索引鍵對鎖定的影響
在前一章中，我們探討了索引和外部索引鍵如何影響鎖定。本章將深入研究主鍵、次鍵、升序、降序和唯一索引對鎖定的影響，並進一步討論外部索引鍵如何影響DML和DDL陳述式的鎖定。
索引的作用
索引提供了一種快速存取特定記錄的方法，減少了需要檢查的記錄數量。這對鎖定數量有正面影響，因為只有被存取的列才會被鎖定。在前一章中，我們看到在world.city表的Name列上新增索引後，對Name列進行篩選的查詢鎖定數量減少了。當連線表格時，索引變得尤為重要，因為沒有索引的話，存取的列數量將是連線表格列數量的乘積。
主鍵與次鍵的比較
最有效的存取列的方法是透過其主鍵，因為這確保了只有受陳述式影響的列才被存取。考慮以下範例：
更新使用非唯一次鍵的列
-- 連線 1
Connection 1> ALTER TABLE world.city ADD INDEX (Name);
Query OK, 0 rows affected (1.3916 sec)
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 1> UPDATE world.city SET Population = 5000000 WHERE Name = 'Sydney';
Query OK, 1 row affected (0.0007 sec)
查詢鎖定的情況如下：
-- 連線 2
Connection 2> SELECT index_name, lock_type, lock_mode, lock_data
FROM performance_schema.data_locks
WHERE object_schema = 'world' AND object_name = 'city' AND thread_id = 1171\G
*************************** 1. row ***************************
index_name: NULL
lock_type: TABLE
lock_mode: IX
lock_data: NULL
*************************** 2. row ***************************
index_name: Name
lock_type: RECORD
lock_mode: X
lock_data: 'Sydney ', 130
*************************** 3. row ***************************
index_name: PRIMARY
lock_type: RECORD
lock_mode: X,REC_NOT_GAP
lock_data: 130
*************************** 4. row ***************************
index_name: Name
lock_type: RECORD
lock_mode: X,GAP
lock_data: 'Syktyvkar ', 3660
更新使用主鍵的列
-- 連線 1
Connection 1> SELECT ID FROM world.city WHERE Name = 'Sydney';
+
---
--+
| ID |
+
---
--+
| 130 |
+
---
--+
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 1> UPDATE world.city SET Population = 5000000 WHERE ID = 130;
Query OK, 1 row affected (0.0027 sec)
查詢鎖定的情況如下：
-- 連線 2
Connection 2> SELECT index_name, lock_type, lock_mode, lock_data
FROM performance_schema.data_locks
WHERE object_schema = 'world' AND object_name = 'city' AND thread_id = 1180\G
*************************** 1. row ***************************
index_name: NULL
lock_type: TABLE
lock_mode: IX
lock_data: NULL
*************************** 2. row ***************************
index_name: PRIMARY
lock_type: RECORD
lock_mode: X,REC_NOT_GAP
lock_data: 130
程式碼解密：
- 主鍵更新：當使用主鍵更新列時，只需要在主鍵上進行記錄鎖定，大幅減少鎖定的數量。
- 次鍵更新：當使用次鍵更新列時，不僅需要在次鍵上進行記錄鎖定和間隙鎖定，還需要在主鍵上進行記錄鎖定。
- 交易外的主鍵查詢：在交易外查詢主鍵值，然後在交易內使用主鍵值進行更新，可以減少鎖定的數量，但需要注意資料在查詢和更新之間的變化風險。
索引與鎖定：提升資料函式庫效能的關鍵
在資料函式倉管理中，索引的使用對於提升查詢和更新操作的效能至關重要。除了選擇適當的索引型別外，瞭解索引與鎖定之間的關係也同樣重要。本篇文章將探討 MySQL 中的索引型別及其對鎖定的影響，特別是在更新和刪除操作中的表現。
升序與降序索引
MySQL 8.0 引入了對降序索引的支援，這使得在降序存取資料時能夠獲得更好的效能。當使用升序索引進行降序存取時，雖然仍然能夠完成操作，但可能會導致頁面間的跳躍存取，從而影響效能。降序索引的主要優勢在於減少了這種跳躍，使得資料存取更加連續。
鎖定機制分析
在更新操作中使用索引時，不僅要考慮存取的效率，還需要了解鎖定的機制。以下是一個範例，展示了在使用升序索引和降序索引更新資料時鎖定的差異。
使用升序索引更新資料
-- 連線 1
ALTER TABLE world.city ADD INDEX (Population);
START TRANSACTION;
UPDATE world.city
SET Population = Population * 1.10
WHERE Population BETWEEN 1000000 AND 2000000
ORDER BY Population DESC
LIMIT 3;
內容解密：
- 新增升序索引：在
world.city表的Population欄位上新增一個升序索引，以加速根據該欄位的查詢。 - 啟動事務：使用
START TRANSACTION;開始一個新的事務，這樣可以確保後續的操作在一個事務內進行。 - 更新資料：執行
UPDATE陳述式，將Population在 100 萬至 200 萬之間的城市人口增加 10%，並按照Population的降序排列，僅更新前 3 筆資料。
-- 連線 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 = 1363
ORDER BY index_name, lock_data DESC;
內容解密：
- 查詢鎖定資訊：在另一個連線中查詢
performance_schema.data_locks表，以取得當前事務持有的鎖定資訊。 - 過濾條件：篩選出
world.city表中與當前事務相關的記錄鎖定資訊，並按照索引名稱和鎖定資料降序排列。
查詢結果顯示，使用升序索引更新資料時，除了對更新的行持有排他鎖（X）外，還會對下一個鍵值範圍持有間隙鎖（GAP），以防止其他事務插入滿足條件的新記錄。
使用降序索引更新資料
-- 連線 1
ALTER TABLE world.city ADD INDEX (Population DESC);
START TRANSACTION;
UPDATE world.city
SET Population = Population * 1.10
WHERE Population BETWEEN 1000000 AND 2000000
ORDER BY Population DESC
LIMIT 3;
內容解密：
- 新增降序索引：在
world.city表的Population欄位上新增一個降序索引，以最佳化降序查詢的效能。 - 更新資料：與前面的例子類別似，更新
Population在指定範圍內的城市人口，但這次使用的是降序索引。
-- 連線 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 = 1374
ORDER BY index_name, lock_data DESC;
內容解密：
查詢結果顯示，使用降序索引時，鎖定情況與使用升序索引類別似，但少了間隙鎖（GAP），這是因為降序索引使得資料存取更加連續，從而減少了鎖定的需求。
唯一索引與鎖定機制最佳化
在探討資料函式庫鎖定機制時，唯一索引（Unique Indexes）扮演著重要的角色。相較於非唯一索引，唯一索引除了提供快速查詢功能外，還強制保證了資料的唯一性，從而在某些情況下最佳化鎖定機制。
唯一索引對鎖定的影響
當 InnoDB 知道某一索引值最多隻會對應一筆記錄（除非該值為 NULL），它就能減少所需的鎖定數量。以下是一個具體的例子：
實驗設計
考慮兩個表 _tmp_city1 和 _tmp_city2，它們包含相同的資料，但 _tmp_city1 在 Name 欄位上有非唯一索引，而 _tmp_city2 在 Name 欄位上有唯一索引。當對這兩個表執行更新操作時，觀察其鎖定情況。
-- 對_tmp_city1 表的操作（非唯一索引）
CREATE TABLE world._tmp_city1
SELECT * FROM world.city WHERE CountryCode = 'AUS';
ALTER TABLE world._tmp_city1 ADD PRIMARY KEY (ID), ADD INDEX (Name);
START TRANSACTION;
UPDATE world._tmp_city1 SET Population = 5000000 WHERE Name = 'Sydney';
-- 對_tmp_city2 表的操作（唯一索引）
CREATE TABLE world._tmp_city2
SELECT * FROM world.city WHERE CountryCode = 'AUS';
ALTER TABLE world._tmp_city2 ADD PRIMARY KEY (ID), ADD UNIQUE INDEX (Name);
START TRANSACTION;
UPDATE world._tmp_city2 SET Population = 5000000 WHERE Name = 'Sydney';
鎖定分析
透過查詢 performance_schema.data_locks 表，可以觀察到兩個交易的鎖定情況。
-- 查詢_tmp_city1 交易的鎖定情況
SELECT index_name, lock_mode, lock_data
FROM performance_schema.data_locks
WHERE object_schema = 'world' AND lock_type = 'RECORD' AND thread_id = 1209\G
-- 查詢_tmp_city2 交易的鎖定情況
SELECT index_name, lock_mode, lock_data
FROM performance_schema.data_locks
WHERE object_schema = 'world' AND lock_type = 'RECORD' AND thread_id = 1210\G
結果顯示，對於具有非唯一索引的 _tmp_city1 表，交易持有三把鎖：
- 對
Name索引的 X 鎖 - 對主鍵的 X,REC_NOT_GAP 鎖
- 對
Name索引的 X,GAP 鎖
而對於具有唯一索引的 _tmp_city2 表，交易僅持有兩把鎖：
- 對
Name索引的 X,REC_NOT_GAP 鎖 - 對主鍵的 X,REC_NOT_GAP 鎖
#### 內容解密：
- 非唯一索引鎖定分析：由於
_tmp_city1表的Name欄位沒有唯一性保證，InnoDB 需要額外加上 GAP 鎖，以防止其他交易插入相同Name的記錄，從而避免幻讀現象。 - 唯一索引鎖定分析：由於
_tmp_city2表的Name欄位具有唯一性約束，InnoDB 知道最多隻會有一筆記錄符合條件，因此不需要 GAP 鎖，減少了鎖定的數量。 - 效能影響：在高並發環境下，減少鎖定數量可以顯著提升系統的吞吐量和效能。
外部索引鍵與鎖定
外部索引鍵（Foreign Keys）是確保資料一致性的重要工具，但它們也會引入額外的鎖定開銷。
外部索引鍵鎖定機制
當修改涉及外部索引鍵的欄位時，InnoDB 會在相關聯的表上設定分享記錄級鎖（Shared Record-Level Lock）和意向分享鎖（Intention Shared Lock）。這確保了資料的一致性，但也可能增加死鎖的風險。
#### 內容解密：
- 外部索引鍵鎖定行為：無論外部索引鍵約束是否被違反，InnoDB 都會在相關表上加上必要的鎖定，以確保資料的一致性。
- 無外部索引鍵變更的情況：如果沒有修改涉及外部索引鍵的欄位，InnoDB 不會額外加上鎖定，即使該欄位被用於篩選條件。
- 設計考量：在設計資料函式庫時，需要權衡外部索引鍵帶來的資料一致性好處與其可能造成的效能影響。
資料函式庫鎖定機制與外部索引鍵關係的探討
在資料函式倉管理系統中，鎖定機制是確保資料一致性和完整性的重要手段。當涉及到外部索引鍵關係時，鎖定機制的複雜度會顯著增加。本文將探討在執行DML（Data Manipulation Language）陳述式和DDL（Data Definition Language）陳述式時，外部索引鍵關係如何影響鎖定機制。
DML陳述式與鎖定
考慮一個簡單的UPDATE陳述式，用於將一部電影從一個商店轉移到另一個商店。這個操作看似簡單，但由於外部索引鍵關係的存在，它實際上會對多個表造成鎖定影響。
清單10-8：更新具有外部索引鍵關係的表中的列
-- 連線1
START TRANSACTION;
UPDATE sakila.inventory
SET store_id = 1
WHERE inventory_id = 4090;
InnoDB鎖定分析
執行上述UPDATE陳述式後，InnoDB會對多個表施加鎖定。清單10-9展示了這些鎖定的詳細資訊。
-- 連線2
SELECT object_schema, object_name, lock_type,
index_name, lock_mode, lock_data
FROM performance_schema.data_locks
WHERE thread_id = 1329\G
輸出結果顯示，InnoDB對inventory表和store表都施加了鎖定。其中，對inventory表的鎖定是獨佔的（X），而對store表的鎖定是分享的（S）。
#### 內容解密：
- lock_type: TABLE 和 lock_mode: IX 表示對整個
inventory表施加了意向獨佔鎖。 - lock_type: RECORD 和 lock_mode: X,REC_NOT_GAP 表示對
inventory表中的特定記錄（inventory_id = 4090）施加了獨佔鎖。 - 對
store表的鎖定是因為外部索引鍵關係，確保了資料的一致性。
中繼資料鎖定
除了InnoDB鎖定之外，中繼資料鎖定也是理解鎖定機制的重要部分。清單10-10展示了由UPDATE陳述式引起的中繼資料鎖定。
清單10-10：中繼資料鎖定分析
-- 連線2
SELECT object_type, object_schema, object_name,
column_name, lock_type, lock_duration
FROM performance_schema.metadata_locks
WHERE owner_thread_id = 1329
ORDER BY object_type, object_schema, object_name,
column_name, lock_type\G
結果顯示，多個表被施加了中繼資料鎖定，包括customer、film、inventory、payment、rental、staff和store等表。
#### 內容解密：
- object_type: SCHEMA 和 lock_type: INTENTION_EXCLUSIVE 表示對
sakila資料函式庫施加了意向獨佔鎖。 - 對多個表的SHARED_READ和SHARED_WRITE鎖定是由於外部索引鍵關係和級聯操作引起的。
- 特別是對
rental表的鎖定，是因為它具有對inventory表的外部索引鍵關係，並且設定了ON UPDATE CASCADE。
DDL陳述式與鎖定
當執行DDL陳述式（如OPTIMIZE TABLE）時，也會對相關表施加中繼資料鎖定。清單10-11展示了一個例子。
清單10-11：對具有外部索引鍵關係的表執行DDL操作
-- 連線1
OPTIMIZE TABLE sakila.inventory;
-- 連線2
SELECT object_name, lock_type, lock_duration
FROM performance_schema.metadata_locks
WHERE owner_thread_id = 1340
AND object_type = 'TABLE';
#### 內容解密：
- 對具有外部索引鍵關係的表執行DDL操作時，會對相關表施加SHARED_UPGRADABLE中繼資料鎖定。
- 這種鎖定確保了在執行DDL操作期間，相關表的資料不會被其他事務修改。