你想知道如何通過優(yōu)化 SQL 語句來減少 MySQL 中的鎖等待問題，核心是從 SQL 層面減少鎖的范圍、縮短鎖持有時(shí)間、避免鎖沖突，我會(huì)結(jié)合具體的 SQL 優(yōu)化案例和底層邏輯，給出可直接落地的優(yōu)化方法。

一、先理解：鎖等待的核心原因

鎖等待本質(zhì)是多個(gè)事務(wù)競(jìng)爭(zhēng)同一資源（行/間隙/表鎖），而 SQL 語句的寫法直接決定了鎖的范圍、加鎖時(shí)機(jī)和持有時(shí)間：

• 鎖范圍越大（如表鎖 > 間隙鎖 > 行鎖），沖突概率越高；
• 鎖持有時(shí)間越長(zhǎng)（如長(zhǎng)事務(wù)），沖突時(shí)間窗口越大；
• 無索引的 SQL 會(huì)導(dǎo)致全表掃描，觸發(fā)表級(jí)鎖，直接引發(fā)大量鎖等待。

二、核心優(yōu)化方法（附 SQL 案例）

1. 確保 SQL 走索引，縮小鎖范圍（最核心）

InnoDB 只有通過主鍵/唯一索引定位數(shù)據(jù)時(shí)，才會(huì)加精準(zhǔn)的行鎖；無索引時(shí)會(huì)掃描全表并加表級(jí)鎖（或大范圍間隙鎖），是鎖等待的最大誘因。

反例（無索引導(dǎo)致全表鎖）：

-- name 無索引，UPDATE 會(huì)掃描全表，加大量間隙鎖/表鎖
UPDATE account SET balance = 2000 WHERE name = '張三';

優(yōu)化后（走索引，僅鎖單行）：

-- 1. 先給 name 加唯一索引（業(yè)務(wù)允許的話）
ALTER TABLE account ADD UNIQUE INDEX idx_name (name);

-- 2. 或用主鍵查詢（最優(yōu)）
UPDATE account SET balance = 2000 WHERE id = 1; -- id 是主鍵，僅鎖 id=1 的行

關(guān)鍵原則：

• UPDATE/DELETE 必須通過主鍵/唯一索引過濾數(shù)據(jù)；
• 避免用非索引字段作為 WHERE 條件，尤其是高并發(fā)修改的場(chǎng)景。

2. 拆分大事務(wù)，縮短鎖持有時(shí)間

長(zhǎng)事務(wù)會(huì)持續(xù)持有鎖，擴(kuò)大鎖沖突的時(shí)間窗口，需將非核心操作移出事務(wù)，只保留必須的修改操作。

反例（長(zhǎng)事務(wù)持有鎖過久）：

START TRANSACTION;
-- 1. 非核心查詢（無必要在事務(wù)內(nèi)）
SELECT * FROM account WHERE id = 1;
SELECT COUNT(*) FROM order WHERE user_id = 1;
-- 2. 核心修改（持有鎖）
UPDATE account SET balance = 2000 WHERE id = 1;
-- 3. 非核心統(tǒng)計(jì)（繼續(xù)持有鎖）
SELECT SUM(amount) FROM trade WHERE user_id = 1;
COMMIT; -- 事務(wù)全程持有鎖，耗時(shí)數(shù)秒

優(yōu)化后（短事務(wù)，僅保留核心操作）：

-- 非核心操作移出事務(wù)（無鎖）
SELECT * FROM account WHERE id = 1;
SELECT COUNT(*) FROM order WHERE user_id = 1;
SELECT SUM(amount) FROM trade WHERE user_id = 1;

-- 短事務(wù)：僅核心修改，快速提交
START TRANSACTION;
UPDATE account SET balance = 2000 WHERE id = 1;
COMMIT; -- 鎖持有時(shí)間僅毫秒級(jí)

3. 避免批量操作，拆分細(xì)粒度 SQL

批量 UPDATE/DELETE 會(huì)鎖定大量行/間隙，引發(fā)大規(guī)模鎖沖突，需拆分為單條操作（或小批量），并加短暫延遲。

反例（批量更新，鎖大量行）：

-- 批量更新 1000 行，鎖定大量行，引發(fā)鎖等待
UPDATE order SET status = 2 WHERE create_time < '2026-01-01';

優(yōu)化后（拆分小批量，減少鎖沖突）：

-- 方式1：按主鍵分批更新（每次 100 行）
UPDATE order SET status = 2 WHERE id BETWEEN 1 AND 100;
COMMIT;
-- 短暫延遲（10ms），避免集中鎖沖突
SELECT SLEEP(0.01);

UPDATE order SET status = 2 WHERE id BETWEEN 101 AND 200;
COMMIT;
-- 以此類推...

-- 方式2：用 LIMIT 限制單次更新行數(shù)（需結(jié)合索引）
UPDATE order SET status = 2 WHERE create_time < '2026-01-01' LIMIT 100;

4. 避免使用 SELECT ... FOR UPDATE 濫用行鎖

SELECT ... FOR UPDATE 會(huì)主動(dòng)加排他行鎖，僅在“必須鎖定數(shù)據(jù)防止并發(fā)修改”的場(chǎng)景使用（如庫(kù)存扣減），普通查詢用普通 SELECT。

反例（濫用 FOR UPDATE）：

START TRANSACTION;
-- 普通查詢也加鎖，無意義且增加沖突
SELECT balance FROM account WHERE id = 1 FOR UPDATE;
-- 后續(xù)無修改操作，卻持有鎖
COMMIT;

優(yōu)化后（僅核心場(chǎng)景用 FOR UPDATE）：

START TRANSACTION;
-- 僅在需要修改時(shí)用 FOR UPDATE
SELECT balance FROM account WHERE id = 1 FOR UPDATE;
-- 立即執(zhí)行修改，快速釋放鎖
UPDATE account SET balance = balance - 100 WHERE id = 1;
COMMIT;

5. 避免幻讀引發(fā)的間隙鎖沖突（REPEATABLE READ 級(jí)別）

InnoDB 在 REPEATABLE READ 下會(huì)加間隙鎖（Next-Key Lock）防止幻讀，不合理的 SQL 會(huì)擴(kuò)大間隙鎖范圍，需優(yōu)化查詢條件。

反例（范圍查詢觸發(fā)大范圍間隙鎖）：

-- BETWEEN 觸發(fā)間隙鎖，鎖定 (100, 200] 區(qū)間，插入 id=150 的數(shù)據(jù)會(huì)被阻塞
SELECT * FROM account WHERE id BETWEEN 100 AND 200 FOR UPDATE;

優(yōu)化后（精準(zhǔn)查詢，縮小間隙鎖范圍）：

-- 1. 用等值查詢（僅鎖單行，無間隙鎖）
SELECT * FROM account WHERE id = 100 FOR UPDATE;
SELECT * FROM account WHERE id = 101 FOR UPDATE;

-- 2. 若必須范圍查詢，改為 READ COMMITTED 級(jí)別（關(guān)閉間隙鎖）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM account WHERE id BETWEEN 100 AND 200 FOR UPDATE;

6. 避免 NULL 值導(dǎo)致的鎖范圍擴(kuò)大

WHERE 條件中包含 NULL 值判斷（如 name IS NULL）會(huì)導(dǎo)致索引失效，觸發(fā)全表掃描，進(jìn)而加大量鎖。

反例（NULL 值導(dǎo)致鎖范圍擴(kuò)大）：

-- name 有索引，但 IS NULL 導(dǎo)致索引失效，加大量鎖
UPDATE account SET balance = 2000 WHERE name IS NULL;

優(yōu)化后（避免 NULL 值，或用默認(rèn)值替代）：

-- 1. 表設(shè)計(jì)時(shí)避免 NULL，用空字符串 '' 替代
ALTER TABLE account MODIFY COLUMN name VARCHAR(20) NOT NULL DEFAULT '';

-- 2. 優(yōu)化 SQL 條件
UPDATE account SET balance = 2000 WHERE name = '';

7. 避免事務(wù)內(nèi)的無用查詢

事務(wù)內(nèi)的無用 SELECT 會(huì)延長(zhǎng)事務(wù)生命周期，間接增加鎖持有時(shí)間，需全部移出。

反例（事務(wù)內(nèi)無用查詢）：

START TRANSACTION;
UPDATE order SET status = 2 WHERE id = 100;
-- 無用查詢：延長(zhǎng)事務(wù)提交時(shí)間
SELECT * FROM user WHERE id = 1;
COMMIT;

優(yōu)化后（移除無用查詢）：

START TRANSACTION;
UPDATE order SET status = 2 WHERE id = 100;
COMMIT;
-- 無用查詢移到事務(wù)外
SELECT * FROM user WHERE id = 1;

三、輔助優(yōu)化技巧

1. 按相同順序訪問資源：多個(gè)事務(wù)對(duì)同一批數(shù)據(jù)操作時(shí)，按主鍵升序加鎖，避免死鎖（鎖等待的極端情況）：

   -- 事務(wù)1：先鎖 id=1，再鎖 id=2
   UPDATE account SET balance = 2000 WHERE id = 1;
   UPDATE account SET balance = 2000 WHERE id = 2;
   
   -- 事務(wù)2：同樣先鎖 id=1，再鎖 id=2（避免反向加鎖導(dǎo)致死鎖）
   UPDATE account SET balance = 2000 WHERE id = 1;
   UPDATE account SET balance = 2000 WHERE id = 2;
   

2. 使用樂觀鎖替代悲觀鎖：高并發(fā)讀、低并發(fā)寫場(chǎng)景，用版本號(hào)（version）實(shí)現(xiàn)樂觀鎖，避免加行鎖：

   -- 樂觀鎖更新（無鎖，通過版本號(hào)控制并發(fā)）
   UPDATE account 
   SET balance = 2000, version = version + 1 
   WHERE id = 1 AND version = 1;
   

3. 監(jiān)控鎖等待 SQL：通過慢查詢?nèi)罩净?sys.schema_unused_indexes 找出未走索引的 SQL，針對(duì)性優(yōu)化：

   -- 查看未使用的索引（優(yōu)化索引設(shè)計(jì)）
   SELECT * FROM sys.schema_unused_indexes;
   
   -- 查看鎖等待相關(guān)的慢查詢
   mysqldumpslow -s t -g 'UPDATE|DELETE' /var/lib/mysql/slow.log;
   

四、優(yōu)化效果驗(yàn)證

優(yōu)化后通過以下 SQL 驗(yàn)證鎖等待是否減少：

-- 1. 查看行鎖等待次數(shù)（對(duì)比優(yōu)化前后）
SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_row_lock_waits';

-- 2. 查看當(dāng)前鎖等待的事務(wù)
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;