400 186 1886
在 MySQL 的 InnoDB 存儲(chǔ)引擎中，非唯一索引觸發(fā)間隙鎖的核心原因是為了 防止幻讀（Phantom Read），尤其是在 REPEATABLE READ（可重復(fù)讀）隔離級(jí)別下。以下從原理、場(chǎng)景和示例詳細(xì)解釋?zhuān)?/p>
1. 間隙鎖的作用
間隙鎖（Gap Lock）是 InnoDB 在索引記錄之間的 間隙（Gap） 上施加的鎖，用于阻止其他事務(wù)在這些間隙中插入新數(shù)據(jù)，從而保證事務(wù)執(zhí)行期間查詢(xún)結(jié)果集的一致性。
示例場(chǎng)景：
假設(shè)事務(wù) A 執(zhí)行 SELECT * FROM users WHERE age = 25 FOR UPDATE，若 age 是非唯一索引，InnoDB 會(huì)鎖定 age = 25 的所有記錄以及相鄰的間隙，阻止其他事務(wù)插入 age = 25 的新記錄。
2. 非唯一索引的結(jié)構(gòu)特點(diǎn)
非唯一索引允許存在重復(fù)的鍵值，因此索引樹(shù)中可能存在多個(gè)相同鍵值的記錄。
當(dāng)通過(guò)非唯一索引查詢(xún)時(shí)，InnoDB 無(wú)法僅通過(guò)鎖定單一行來(lái)避免幻讀，因?yàn)槠渌聞?wù)可能在 相同的鍵值范圍內(nèi)插入新數(shù)據(jù)。
示例數(shù)據(jù)：
假設(shè)表 users 的 age 是非唯一索引，數(shù)據(jù)如下：
+----+-----+
| id | age |
+----+-----+
| 1 | 20 |
| 3 | 25 |
| 5 | 25 |
| 7 | 30 |
+----+-----+
索引 age 的結(jié)構(gòu)（按升序排列）：
(20) → (25) → (25) → (30)
3. 非唯一索引觸發(fā)間隙鎖的場(chǎng)景
場(chǎng)景 1：等值查詢(xún)（age = 25）
-- 事務(wù) A
BEGIN;
SELECT * FROM users WHERE age = 25 FOR UPDATE;
鎖定范圍：
- 行鎖：鎖定所有
age = 25的記錄（id=3 和 id=5）。 - 間隙鎖：鎖定
age = 25的前后間隙：- 左間隙：
(20, 25) - 右間隙：
(25, 30)
- 左間隙：
- 行鎖：鎖定所有
其他事務(wù)插入
age = 25的新數(shù)據(jù)會(huì)被阻塞：-- 事務(wù) B（阻塞） INSERT INTO users (id, age) VALUES (6, 25);
場(chǎng)景 2：范圍查詢(xún)（age > 20）
-- 事務(wù) A
BEGIN;
SELECT * FROM users WHERE age > 20 FOR UPDATE;
- 鎖定范圍：
- 行鎖：鎖定
age = 25和age = 30的記錄。 - 間隙鎖：鎖定所有符合條件的間隙：
(20, 25)、(25, 25)、(25, 30)、(30, +∞)
- 行鎖：鎖定
4. 對(duì)比唯一索引的行為
如果是 唯一索引（如主鍵 id），等值查詢(xún)不會(huì)觸發(fā)間隙鎖：
-- 事務(wù) A
BEGIN;
SELECT * FROM users WHERE id = 3 FOR UPDATE;
- 鎖定范圍：僅鎖定
id = 3的行，不涉及間隙鎖。 - 其他事務(wù)插入
id = 4不會(huì)被阻塞：-- 事務(wù) B（成功） INSERT INTO users (id, age) VALUES (4, 25);
例外：唯一索引的范圍查詢(xún)?nèi)詴?huì)觸發(fā)間隙鎖：
-- 事務(wù) A
BEGIN;
SELECT * FROM users WHERE id > 3 FOR UPDATE;
- 鎖定
id > 3的所有行和間隙（如(3, 5)、(5, 7)、(7, +∞)）。
5. 為什么非唯一索引必須加間隙鎖？
- 防止幻讀：如果僅鎖定現(xiàn)有行，其他事務(wù)可以在間隙中插入相同鍵值的新數(shù)據(jù)，導(dǎo)致事務(wù) A 的后續(xù)查詢(xún)出現(xiàn)“幻行”。
- 保證可重復(fù)讀：事務(wù) A 在多次查詢(xún)同一范圍時(shí)，結(jié)果集必須一致。
6. 如何避免非唯一索引的間隙鎖？
方案 1：改用 READ COMMITTED 隔離級(jí)別
在 READ COMMITTED 級(jí)別下，InnoDB 不使用間隙鎖（僅行鎖），但可能產(chǎn)生幻讀：
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
方案 2：優(yōu)化查詢(xún)條件
盡量使用 唯一索引 或 精確匹配，減少范圍查詢(xún)。
方案 3：縮短事務(wù)時(shí)間
盡快提交事務(wù)，減少鎖持有時(shí)間。
總結(jié)
非唯一索引觸發(fā)間隙鎖的本質(zhì)是 防止在重復(fù)鍵值范圍內(nèi)插入新數(shù)據(jù)導(dǎo)致幻讀。理解這一機(jī)制有助于：
- 合理設(shè)計(jì)索引（優(yōu)先使用唯一索引）。
- 在高并發(fā)場(chǎng)景下優(yōu)化鎖競(jìng)爭(zhēng)（如選擇
READ COMMITTED）。 - 避免大范圍查詢(xún)對(duì)性能的影響。
