六.索引使用

6.1 索引效率驗(yàn)證

在大表場(chǎng)景下，索引對(duì)查詢性能的提升效果極為顯著，可通過以下步驟直觀驗(yàn)證：

-- 1. 無索引時(shí)執(zhí)行查詢（全表掃描）
SELECT * FROM tb_sku WHERE sn = '100000003145001';
-- 2. 為sn字段創(chuàng)建普通索引
CREATE INDEX idx_sku_sn ON tb_sku(sn);
-- 3. 再次執(zhí)行相同查詢（索引掃描）
SELECT * FROM tb_sku WHERE sn = '100000003145001';

現(xiàn)象說明：

• 無索引時(shí)，查詢需遍歷全表數(shù)據(jù)，耗時(shí)通常在數(shù)秒至數(shù)十秒級(jí)別；
• 創(chuàng)建索引后，查詢通過 B+Tree 直接定位數(shù)據(jù)，耗時(shí)可降至毫秒級(jí)，性能提升可達(dá)數(shù)百倍。

6.2 索引失效場(chǎng)景

6.2.1 最左前綴法則（聯(lián)合索引核心規(guī)則）

規(guī)則定義：聯(lián)合索引遵循 “最左前綴匹配” 原則，查詢必須從索引的最左列開始，且不能跳過中間列；若跳過某一列，該列右側(cè)的所有索引列將失效。

示例：假設(shè)聯(lián)合索引為 idx_user_pro_age_sta(profession, age, status)

-- 場(chǎng)景1：完全匹配（三列都用到索引）
EXPLAIN SELECT * FROM tb_user WHERE profession = '軟件工程' AND age = 31 AND status = '0';
-- 場(chǎng)景2：用到前兩列（status列索引失效）
EXPLAIN SELECT * FROM tb_user WHERE profession = '軟件工程' AND age = 31;
-- 場(chǎng)景3：僅用到第一列（age、status列索引失效）
EXPLAIN SELECT * FROM tb_user WHERE profession = '軟件工程';
-- 場(chǎng)景4：跳過最左列（profession列未使用，索引完全失效）
EXPLAIN SELECT * FROM tb_user WHERE age = 31 AND status = '0';
-- 場(chǎng)景5：完全不使用最左列（索引完全失效，全表掃描）
EXPLAIN SELECT * FROM tb_user WHERE status = '0';

優(yōu)化建議：聯(lián)合索引的字段順序需遵循 “等值條件優(yōu)先、范圍條件靠后” 的原則，將高頻等值查詢字段放在左側(cè)。

6.2.2 范圍查詢導(dǎo)致索引失效

規(guī)則定義：聯(lián)合索引中，若某一列使用了>/</BETWEEN等范圍查詢，該列右側(cè)的所有索引列將失效。

示例：

-- 場(chǎng)景1：age使用范圍查詢，status列索引失效
EXPLAIN SELECT * FROM tb_user WHERE profession = '軟件工程' AND age > 30 AND status = '0';
-- 場(chǎng)景2：age使用>=/<=（邊界范圍），status列索引同樣失效
EXPLAIN SELECT * FROM tb_user WHERE profession = '軟件工程' AND age >= 30 AND status = '0';

優(yōu)化建議：將范圍查詢字段放在聯(lián)合索引的最后一列，避免影響后續(xù)字段的索引使用；若業(yè)務(wù)允許，優(yōu)先使用>=/<=替代>/<，減少范圍掃描的行數(shù)。

6.2.3 索引列運(yùn)算 / 函數(shù)操作導(dǎo)致失效

規(guī)則定義：對(duì)索引列進(jìn)行運(yùn)算（如加減乘除）或函數(shù)操作（如SUBSTRING/DATE_FORMAT），會(huì)導(dǎo)致索引失效，MySQL 將轉(zhuǎn)為全表掃描。

示例：

-- 場(chǎng)景：對(duì)phone字段使用SUBSTRING函數(shù)，索引失效
EXPLAIN SELECT * FROM tb_user WHERE SUBSTRING(phone, 10, 2) = '15';

優(yōu)化建議：

• 將運(yùn)算 / 函數(shù)操作移到查詢條件的右側(cè)，避免作用于索引列；
• 對(duì)于字符串截取場(chǎng)景，優(yōu)先使用前綴索引替代函數(shù)操作；
• 高版本 MySQL 支持函數(shù)索引，可直接為運(yùn)算后的字段創(chuàng)建索引。

6.2.4 字符串字段不加引號(hào)導(dǎo)致索引失效

規(guī)則定義：字符串類型字段（如 VARCHAR/CHAR）查詢時(shí)，若值未加引號(hào)，MySQL 會(huì)自動(dòng)進(jìn)行類型轉(zhuǎn)換，導(dǎo)致索引失效。

示例：

-- 場(chǎng)景1：status字段為字符串類型，不加引號(hào)，索引失效
EXPLAIN SELECT * FROM tb_user WHERE profession = '軟件工程' AND age = 31 AND status = 0;
-- 場(chǎng)景2：phone字段為字符串類型，不加引號(hào)，索引失效
EXPLAIN SELECT * FROM tb_user WHERE phone = 17799990015;

原理說明：字符串與數(shù)字比較時(shí)，MySQL 會(huì)將字符串轉(zhuǎn)換為數(shù)字進(jìn)行比較，導(dǎo)致索引列的類型被隱式轉(zhuǎn)換，無法匹配索引中的字符串值。優(yōu)化建議：字符串字段的查詢值必須加單引號(hào)，即使值是純數(shù)字。

6.2.5 模糊查詢（LIKE）導(dǎo)致索引失效

規(guī)則定義：

• 尾部模糊匹配（LIKE '前綴%'）：索引有效；
• 頭部模糊匹配（LIKE '%后綴'/LIKE '%中間%'）：索引失效，轉(zhuǎn)為全表掃描。

示例：

-- 場(chǎng)景1：尾部模糊匹配，索引有效
EXPLAIN SELECT * FROM tb_user WHERE profession LIKE '軟件%';
-- 場(chǎng)景2：頭部模糊匹配，索引失效
EXPLAIN SELECT * FROM tb_user WHERE profession LIKE '%工程';
-- 場(chǎng)景3：前后都模糊匹配，索引失效
EXPLAIN SELECT * FROM tb_user WHERE profession LIKE '%工%';

優(yōu)化建議：

• 優(yōu)先使用前綴索引（如CREATE INDEX idx_profession ON tb_user(profession(4))）；
• 高頻模糊查詢場(chǎng)景，建議使用全文索引（FULLTEXT）替代普通索引；
• 核心業(yè)務(wù)場(chǎng)景可引入 ES 等搜索引擎實(shí)現(xiàn)全文檢索。

6.2.6 數(shù)據(jù)分布影響（MySQL 優(yōu)化器放棄索引）

規(guī)則定義：當(dāng)查詢條件匹配的數(shù)據(jù)量超過表中總行數(shù)的約 20% 時(shí)，MySQL 優(yōu)化器會(huì)評(píng)估使用索引的隨機(jī) IO 成本高于全表掃描的順序 IO 成本，因此會(huì)放棄使用索引，直接進(jìn)行全表掃描。

示例：

-- 場(chǎng)景1：匹配數(shù)據(jù)量少，使用索引
SELECT * FROM tb_user WHERE phone >= '17799990005';
-- 場(chǎng)景2：匹配數(shù)據(jù)量超過20%，MySQL放棄索引，全表掃描
SELECT * FROM tb_user WHERE phone >= '17799990015';

優(yōu)化建議：

• 避免在低基數(shù)字段（如性別、狀態(tài)）上創(chuàng)建索引，此類字段的查詢幾乎必然觸發(fā)全表掃描；
• 對(duì)于大表的范圍查詢，可通過FORCE INDEX強(qiáng)制使用索引，但需評(píng)估性能影響；
• 定期分析表數(shù)據(jù)分布，避免索引因數(shù)據(jù)傾斜失效。

6.3 SQL 索引提示（人工干預(yù)優(yōu)化器）

當(dāng) MySQL 優(yōu)化器選擇的索引不符合預(yù)期時(shí)，可通過索引提示強(qiáng)制指定索引，適用于復(fù)雜查詢或優(yōu)化器誤判場(chǎng)景。

核心語法

-- 1. USE INDEX：建議MySQL使用指定索引（優(yōu)化器仍可能選擇其他索引）
EXPLAIN SELECT * FROM tb_user USE INDEX(idx_user_pro) WHERE profession = '軟件工程';
-- 2. IGNORE INDEX：忽略指定索引（讓優(yōu)化器不使用該索引）
EXPLAIN SELECT * FROM tb_user IGNORE INDEX(idx_user_pro) WHERE profession = '軟件工程';
-- 3. FORCE INDEX：強(qiáng)制MySQL使用指定索引（優(yōu)先級(jí)最高）
EXPLAIN SELECT * FROM tb_user FORCE INDEX(idx_user_pro) WHERE profession = '軟件工程'

使用場(chǎng)景：

• 優(yōu)化器誤判，選擇了低效索引時(shí)；
• 多索引場(chǎng)景下，需人工干預(yù)指定最優(yōu)索引；
• 臨時(shí)驗(yàn)證不同索引的性能差異。

6.4 覆蓋索引（性能天花板優(yōu)化）

定義

覆蓋索引是指查詢所需的所有列，都能在索引中直接獲取，無需回表查詢聚集索引，避免了額外的 IO 操作，是 InnoDB 中性能最優(yōu)的索引使用方式。

原理示例

以聯(lián)合索引 idx_user_pro_age_sta(profession, age, status) 為例：

-- 場(chǎng)景1：使用覆蓋索引，無需回表（Extra顯示Using index）
EXPLAIN SELECT id, profession FROM tb_user WHERE profession = '軟件工程' AND age = 31 AND status = '0';
-- 場(chǎng)景2：索引列+主鍵，同樣是覆蓋索引（InnoDB二級(jí)索引默認(rèn)包含主鍵）
EXPLAIN SELECT id, profession, age, status FROM tb_user WHERE profession = '軟件工程' AND age = 31 AND status = '0';
-- 場(chǎng)景3：查詢包含非索引列，需要回表（Extra無Using index）
EXPLAIN SELECT id, profession, age, status, name FROM tb_user WHERE profession = '軟件工程' AND age = 31 AND status = '0';
-- 場(chǎng)景4：SELECT * 查詢，必然回表，無法使用覆蓋索引
EXPLAIN SELECT * FROM tb_user WHERE profession = '軟件工程' AND age = 31 AND status = '0';

關(guān)鍵判斷（Extra 字段）：

• Using index：使用了覆蓋索引，無需回表，性能最優(yōu)；
• Using index condition：使用了索引，但需要回表查詢完整數(shù)據(jù)；
• 無上述標(biāo)識(shí)：未使用索引或僅使用了部分索引，需全表掃描或大量回表。

優(yōu)化建議

• 避免使用SELECT *，僅查詢業(yè)務(wù)所需的列；
• 高頻查詢場(chǎng)景，創(chuàng)建包含所有查詢列的聯(lián)合索引，實(shí)現(xiàn)覆蓋索引；
• 二級(jí)索引默認(rèn)包含主鍵，因此查詢主鍵列無需額外回表。

6.5 高頻真題：單條 SQL 的最優(yōu)索引設(shè)計(jì)

題目背景

一張用戶表 tb_user，包含字段 id, username, password, status，數(shù)據(jù)量較大，需對(duì)以下 SQL 進(jìn)行優(yōu)化：

SELECT id, username, password FROM tb_user WHERE username = 'silverkite';

最優(yōu)方案設(shè)計(jì)

方案 1：普通單列索引（username）

CREATE INDEX idx_tb_user_username ON tb_user(username);

執(zhí)行邏輯：

• 通過idx_tb_user_username索引定位到username='itcast'對(duì)應(yīng)的主鍵id；
• 再通過id回表查詢聚集索引，獲取username和password字段的值；
• 存在額外的回表 IO 操作，性能中等。

方案 2：覆蓋聯(lián)合索引（最優(yōu)方案）

CREATE INDEX idx_tb_user_uname_pwd ON tb_user(username, password);

執(zhí)行邏輯（無回表，性能天花板）：

• 聯(lián)合索引(username, password)中，已包含查詢所需的所有字段：
  • WHERE條件匹配username；
  • SELECT需要的id（二級(jí)索引默認(rèn)包含主鍵）、username、password全部在索引中；
• MySQL 可直接通過索引完成數(shù)據(jù)讀取，無需回表查詢聚集索引，Extra 字段顯示Using index；
• 無額外 IO 操作，性能最優(yōu)，是該場(chǎng)景下的唯一最優(yōu)解。

6.6 前綴索引優(yōu)化（長字符串字段索引方案）

適用場(chǎng)景

當(dāng)字段為VARCHAR/TEXT等長字符串類型時(shí)，直接創(chuàng)建全字段索引會(huì)導(dǎo)致索引體積過大，查詢時(shí) IO 成本高，可通過前綴索引僅對(duì)字符串的前 N 個(gè)字符創(chuàng)建索引，大幅節(jié)省索引空間并提升查詢效率。

核心語法

-- 為table_name表的column字段，取前n個(gè)字符創(chuàng)建前綴索引
CREATE INDEX idx_xxx ON table_name(column(n));

前綴長度選擇方法（核心：高選擇性）

前綴長度的選擇需保證索引的高選擇性（即不重復(fù)值占比越高，索引效率越高），計(jì)算方式如下：

-- 1. 全字段索引的選擇性（最優(yōu)為1，即唯一索引）
SELECT COUNT(DISTINCT email) / COUNT(*) FROM tb_user;

-- 2. 計(jì)算取前5個(gè)字符的選擇性，對(duì)比全字段選擇性，越接近越好
SELECT COUNT(DISTINCT SUBSTRING(email, 1, 5)) / COUNT(*) FROM tb_user;

-- 3. 當(dāng)選擇性接近全字段時(shí)，即可確定前綴長度（如n=5）
CREATE INDEX idx_tb_user_email ON tb_user(email(5));

前綴索引查詢流程示例

以SELECT * FROM tb_user WHERE email = 'lvbu666@163.com';為例：

• 輔助索引email(5)存儲(chǔ)的是字符串前 5 個(gè)字符，如lvbu6；
• 通過前綴索引定位到匹配的email前綴，獲取對(duì)應(yīng)的主鍵id；
• 回表查詢聚集索引，校驗(yàn)完整的email值是否匹配；
• 若匹配成功，則返回?cái)?shù)據(jù)。

優(yōu)缺點(diǎn)與適用場(chǎng)景

6.7 單列索引 vs 聯(lián)合索引：多條件查詢選型

核心結(jié)論

在多條件查詢場(chǎng)景中，優(yōu)先使用聯(lián)合索引，而非多個(gè)單列索引，可避免 MySQL 優(yōu)化器誤判，同時(shí)實(shí)現(xiàn)覆蓋索引優(yōu)化。

場(chǎng)景對(duì)比

場(chǎng)景 1：多個(gè)單列索引

-- 單列索引1
CREATE INDEX idx_tb_user_phone ON tb_user(phone);
-- 單列索引2
CREATE INDEX idx_tb_user_name ON tb_user(name);
-- 多條件查詢
EXPLAIN SELECT id, phone, name FROM tb_user WHERE phone = '17799990010' AND name = '韓信';

執(zhí)行結(jié)果分析：

• possible_keys中同時(shí)存在兩個(gè)索引，但key僅選擇了idx_tb_user_phone；
• MySQL 優(yōu)化器會(huì)評(píng)估兩個(gè)索引的效率，選擇成本更低的索引（如phone索引的基數(shù)更高）；
• 未被選擇的索引完全失效，僅使用單個(gè)索引過濾，需在服務(wù)層額外過濾name條件，效率低。

場(chǎng)景 2：聯(lián)合索引（最優(yōu)方案）

-- 創(chuàng)建phone和name的聯(lián)合索引
CREATE UNIQUE INDEX idx_tb_user_phone_name ON tb_user(phone, name);
-- 相同查詢
EXPLAIN SELECT id, phone, name FROM tb_user WHERE phone = '17799990010' AND name = '韓信';

執(zhí)行結(jié)果分析：

• 直接使用聯(lián)合索引idx_tb_user_phone_name，同時(shí)匹配phone和name兩個(gè)條件；
• 索引中已包含phone、name和主鍵id，若查詢字段僅包含這三個(gè)，可實(shí)現(xiàn)覆蓋索引，無需回表；
• 避免了優(yōu)化器的索引選擇問題，性能穩(wěn)定且高效。

選型決策樹

• 多條件等值查詢：優(yōu)先創(chuàng)建聯(lián)合索引，字段順序遵循 “高頻等值條件在前，范圍條件在后”；
• 單條件查詢：創(chuàng)建單列索引即可；
• 多個(gè)單條件查詢：避免創(chuàng)建多個(gè)單列索引，優(yōu)先考慮覆蓋索引或聯(lián)合索引，減少索引數(shù)量，降低寫操作的維護(hù)成本。

索引優(yōu)化核心避坑清單

七.索引設(shè)計(jì)原則

索引設(shè)計(jì)是SQL性能優(yōu)化的根源性工作，優(yōu)秀的索引設(shè)計(jì)可從源頭避免慢SQL、全表掃描、回表查詢等性能問題，而非事后補(bǔ)救。以下7條核心設(shè)計(jì)原則，覆蓋業(yè)務(wù)開發(fā)全場(chǎng)景與面試高頻考點(diǎn)，完全承接前文的索引原理、使用規(guī)則與優(yōu)化實(shí)踐。

7.1 原則一：優(yōu)先為數(shù)據(jù)量大、查詢頻繁的表建立索引

核心邏輯

索引的核心價(jià)值是解決大數(shù)據(jù)量下的查詢性能問題，需精準(zhǔn)匹配場(chǎng)景，避免無效索引：

• 收益場(chǎng)景：表數(shù)據(jù)量≥10萬行，且有高頻業(yè)務(wù)查詢，索引帶來的查詢性能提升，遠(yuǎn)大于索引維護(hù)的成本；
• 無效場(chǎng)景：僅幾千行的小表（如系統(tǒng)配置表、數(shù)據(jù)字典表），全表掃描的成本極低，建索引反而會(huì)增加額外的維護(hù)開銷，無實(shí)際性能收益。

實(shí)操避坑

• 不要為極少查詢的冷數(shù)據(jù)歸檔表創(chuàng)建大量索引，僅需為歸檔篩選字段建少量索引即可；
• 不要為全量小表盲目建索引，優(yōu)先通過業(yè)務(wù)邏輯優(yōu)化替代索引。

7.2 原則二：優(yōu)先為WHERE、ORDER BY、GROUP BY操作的字段建立索引

索引的核心價(jià)值是過濾數(shù)據(jù)、利用有序性避免額外排序，這三類操作是索引最核心的落地場(chǎng)景，直接決定SQL的性能上限。

分場(chǎng)景拆解

1.WHERE查詢條件字段

是索引最基礎(chǔ)的使用場(chǎng)景，通過索引快速過濾數(shù)據(jù)，避免全表掃描，是所有索引設(shè)計(jì)的基礎(chǔ)。

2.ORDER BY排序字段

利用B+Tree索引天生的有序性，直接通過索引獲取有序數(shù)據(jù)，避免MySQL生成臨時(shí)文件做額外排序（執(zhí)行計(jì)劃Extra出現(xiàn)Using filesort），大幅降低CPU消耗。

3.GROUP BY分組字段

分組操作的底層需要先對(duì)數(shù)據(jù)排序，再做聚合計(jì)算，索引可避免分組時(shí)創(chuàng)建臨時(shí)表（執(zhí)行計(jì)劃Extra出現(xiàn)Using temporary），是分組查詢優(yōu)化的核心手段。

實(shí)操建議

多字段組合查詢場(chǎng)景，優(yōu)先創(chuàng)建聯(lián)合索引，字段順序遵循：WHERE等值查詢字段 → GROUP BY分組字段 → ORDER BY排序字段，可實(shí)現(xiàn)索引全流程覆蓋，無額外排序、無臨時(shí)表、無回表。

7.3 原則三：優(yōu)先選擇區(qū)分度（基數(shù)）高的列作為索引，優(yōu)先唯一索引

核心定義

區(qū)分度（也叫選擇性）= 字段不重復(fù)值的數(shù)量 / 表總行數(shù)，比值越接近1，區(qū)分度越高，索引的過濾效率越強(qiáng)。

• 唯一索引：區(qū)分度=1，是性能最優(yōu)的索引，一次索引查找即可精準(zhǔn)定位數(shù)據(jù)，無額外過濾開銷；
• 低基數(shù)字段：如性別（男/女）、狀態(tài)（0/1），區(qū)分度極低，索引過濾后仍需掃描大量數(shù)據(jù)，MySQL優(yōu)化器大概率會(huì)放棄索引，直接全表掃描。

實(shí)操規(guī)范

1.建索引前先計(jì)算字段區(qū)分度，優(yōu)先為區(qū)分度≥0.3的字段建索引：

-- 計(jì)算字段區(qū)分度，越接近1越好
SELECT COUNT(DISTINCT 字段名) / COUNT(*) FROM 表名;

2.業(yè)務(wù)中保證唯一性的字段（如手機(jī)號(hào)、身份證號(hào)、訂單號(hào)），必須創(chuàng)建唯一索引，既保證數(shù)據(jù)唯一性，又獲得最優(yōu)查詢性能；

3.低基數(shù)字段禁止單獨(dú)建索引，可通過聯(lián)合索引（和高區(qū)分度字段組合）實(shí)現(xiàn)優(yōu)化。

7.4 原則四：長字符串字段，優(yōu)先建立前綴索引

核心邏輯

當(dāng)字段為VARCHAR(255)、TEXT等長字符串類型時(shí)，直接創(chuàng)建全字段索引會(huì)導(dǎo)致索引體積過大，磁盤IO成本極高；前綴索引僅對(duì)字符串的前N個(gè)字符創(chuàng)建索引，可大幅節(jié)省索引空間，提升索引查詢效率。

實(shí)操規(guī)范

1.前綴長度選擇核心：保證前綴的選擇性接近全字段的選擇性，平衡索引體積與過濾效率；

-- 1. 全字段選擇性
SELECT COUNT(DISTINCT email) / COUNT(*) FROM tb_user;
-- 2. 測(cè)試前10個(gè)字符的選擇性，接近全字段即可確定前綴長度
SELECT COUNT(DISTINCT SUBSTRING(email, 1, 10)) / COUNT(*) FROM tb_user;
-- 3. 創(chuàng)建前綴索引
CREATE INDEX idx_user_email ON tb_user(email(10));

2.適用場(chǎng)景：郵箱、URL、長文本標(biāo)題等長字符串字段的等值查詢；

3.避坑提醒：前綴索引無法實(shí)現(xiàn)覆蓋索引，因?yàn)樗饕袃H存儲(chǔ)了字段前綴，無完整值，查詢時(shí)必須回表校驗(yàn)完整數(shù)據(jù)。

7.5 原則五：優(yōu)先使用聯(lián)合索引，減少單列索引

核心優(yōu)勢(shì)

• 降低維護(hù)成本：多個(gè)單列索引在數(shù)據(jù)增刪改時(shí)，需要同步維護(hù)多個(gè)B+Tree結(jié)構(gòu)；聯(lián)合索引僅需維護(hù)一個(gè)索引結(jié)構(gòu)，大幅降低寫操作的性能損耗；
• 更容易實(shí)現(xiàn)覆蓋索引：聯(lián)合索引可包含查詢所需的所有字段，直接實(shí)現(xiàn)Using index，避免回表查詢，是性能優(yōu)化的核心手段；
• 避免優(yōu)化器誤判：多條件查詢時(shí)，多個(gè)單列索引僅會(huì)被優(yōu)化器選擇一個(gè)最優(yōu)的，其余索引完全失效；聯(lián)合索引可同時(shí)匹配多個(gè)查詢條件，過濾效率更高。

設(shè)計(jì)規(guī)范

• 聯(lián)合索引字段順序嚴(yán)格遵循最左前綴法則，同時(shí)滿足：高頻等值查詢字段在前、范圍查詢字段在后；
• 避免冗余索引：已存在聯(lián)合索引(a,b,c)，則無需再創(chuàng)建(a)、(a,b)這類前綴子集索引，避免冗余開銷；
• 單表優(yōu)先通過3-5個(gè)聯(lián)合索引覆蓋全量業(yè)務(wù)查詢，而非創(chuàng)建十幾個(gè)單列索引。

7.6 原則六：嚴(yán)格控制索引數(shù)量，索引不是越多越好

核心代價(jià)

索引的本質(zhì)是「空間換時(shí)間」，過量索引會(huì)帶來雙重成本：

• 空間成本：每個(gè)索引都需要獨(dú)立的磁盤存儲(chǔ)空間，大表的索引總體積甚至?xí)^業(yè)務(wù)數(shù)據(jù)本身；
• 性能成本：執(zhí)行INSERT/UPDATE/DELETE時(shí)，需要同步維護(hù)所有相關(guān)索引的B+Tree結(jié)構(gòu)，保證有序性，索引越多，寫操作耗時(shí)越長，數(shù)據(jù)庫并發(fā)性能越差；
• 優(yōu)化成本：過多索引會(huì)增加MySQL查詢優(yōu)化器的選擇成本，可能導(dǎo)致優(yōu)化器選錯(cuò)索引，反而降低查詢性能。

實(shí)操規(guī)范

• 單表索引數(shù)量嚴(yán)格控制在5個(gè)以內(nèi)，核心業(yè)務(wù)表不超過8個(gè)；
• 定期清理無用索引：通過MySQL性能_schema監(jiān)控索引使用頻次，刪除長期未被查詢使用的冗余索引；
• 禁止為每個(gè)字段單獨(dú)創(chuàng)建單列索引，優(yōu)先通過聯(lián)合索引覆蓋多場(chǎng)景查詢。

7.7 原則七：索引列優(yōu)先設(shè)置NOT NULL約束

核心邏輯

• 優(yōu)化器判斷更高效：MySQL優(yōu)化器處理NULL值時(shí)，需要增加額外的空值判斷邏輯，無法高效利用索引；明確NOT NULL的字段，優(yōu)化器可更精準(zhǔn)地生成執(zhí)行計(jì)劃；
• 索引統(tǒng)計(jì)更準(zhǔn)確：NULL值會(huì)影響索引的基數(shù)統(tǒng)計(jì)，導(dǎo)致優(yōu)化器對(duì)查詢成本的評(píng)估出現(xiàn)偏差，可能選錯(cuò)執(zhí)行計(jì)劃；
• 存儲(chǔ)成本更低：InnoDB中，NULL值需要額外的存儲(chǔ)空間標(biāo)記空值，NOT NULL字段可減少索引的存儲(chǔ)體積，提升IO效率。

實(shí)操規(guī)范

• 建表時(shí)，所有索引列必須設(shè)置NOT NULL約束，并搭配合理的默認(rèn)值：字符串類型默認(rèn)空字符串''，數(shù)字類型默認(rèn)0；
• 禁止用NULL作為業(yè)務(wù)有效值，比如用0/1表示狀態(tài)，而非用NULL表示「未設(shè)置」；
• 若業(yè)務(wù)字段確實(shí)存在空值場(chǎng)景，可通過特殊值（如-1、空字符串）替代NULL，保證索引列的NOT NULL約束。

索引設(shè)計(jì)

• 篩選目標(biāo)表：鎖定數(shù)據(jù)量大、查詢頻繁的核心業(yè)務(wù)表，小表/冷表不做過度設(shè)計(jì)；
• 提取關(guān)鍵字段：梳理業(yè)務(wù)中高頻使用的WHERE過濾、ORDER BY排序、GROUP BY分組字段；
• 評(píng)估字段質(zhì)量：優(yōu)先選擇區(qū)分度高的字段，過濾低基數(shù)字段，長字符串字段設(shè)計(jì)前綴索引；
• 設(shè)計(jì)索引結(jié)構(gòu)：優(yōu)先創(chuàng)建聯(lián)合索引，字段順序匹配最左前綴法則，盡可能實(shí)現(xiàn)覆蓋索引；
• 控制索引規(guī)模：單表索引不超過5個(gè)，刪除冗余、無用索引；
• 完善字段約束：所有索引列設(shè)置NOT NULL約束與默認(rèn)值，優(yōu)化優(yōu)化器執(zhí)行計(jì)劃。

三、SQL優(yōu)化

一、插入數(shù)據(jù)優(yōu)化（Insert 優(yōu)化）

插入性能的核心瓶頸是磁盤 IO 與事務(wù)提交開銷，通過以下 4 種方式可大幅提升批量插入效率：

1.1 批量插入（單語句多值插入）

優(yōu)化邏輯

將多條數(shù)據(jù)合并為一條INSERT語句執(zhí)行，減少客戶端與數(shù)據(jù)庫的交互次數(shù)，降低網(wǎng)絡(luò) IO 與 SQL 解析開銷。

示例 SQL

-- 低效方式：單條插入，多次交互
INSERT INTO tb_test VALUES(1, 'Tom');
INSERT INTO tb_test VALUES(2, 'Cat');
INSERT INTO tb_test VALUES(3, 'Jerry');
-- 高效方式：批量插入，單次交互
INSERT INTO tb_test VALUES(1, 'Tom'), (2, 'Cat'), (3, 'Jerry');

實(shí)操規(guī)范

• 單條批量插入建議控制在100-1000 條數(shù)據(jù)，避免單條 SQL 過大導(dǎo)致解析超時(shí)或日志膨脹；
• 批量插入的數(shù)據(jù)需提前校驗(yàn)合法性，避免單條失敗導(dǎo)致整批回滾。

1.2 手動(dòng)提交事務(wù)（事務(wù)包裹批量插入）

優(yōu)化邏輯

InnoDB 默認(rèn)每條 SQL 都會(huì)自動(dòng)提交事務(wù)（autocommit=1），頻繁提交事務(wù)會(huì)產(chǎn)生大量 Redo 日志刷盤操作，通過手動(dòng)開啟事務(wù)，將多條插入包裹在一個(gè)事務(wù)中，僅需一次提交刷盤，大幅減少 IO 次數(shù)。

示例 SQL

-- 開啟事務(wù)
START TRANSACTION;
-- 批量插入數(shù)據(jù)
INSERT INTO tb_test VALUES(1, 'Tom'), (2, 'Cat'), (3, 'Jerry');
INSERT INTO tb_test VALUES(4, 'Tom'), (5, 'Cat'), (6, 'Jerry');
INSERT INTO tb_test VALUES(7, 'Tom'), (8, 'Cat'), (9, 'Jerry');
-- 統(tǒng)一提交事務(wù)，僅一次刷盤
COMMIT;

實(shí)操規(guī)范

• 事務(wù)中插入數(shù)據(jù)量建議控制在1 萬條以內(nèi)，避免事務(wù)過大導(dǎo)致鎖表、日志膨脹或崩潰恢復(fù)時(shí)間過長；
• 異常場(chǎng)景需配合ROLLBACK回滾，保證數(shù)據(jù)一致性。

1.3 主鍵順序插入（避免頁分裂）

優(yōu)化邏輯

InnoDB 中數(shù)據(jù)按主鍵順序存儲(chǔ)在 B+Tree 索引中，順序插入時(shí)數(shù)據(jù)會(huì)追加到當(dāng)前頁的末尾，不會(huì)觸發(fā)頁分裂；亂序插入可能導(dǎo)致數(shù)據(jù)插入到已寫滿的頁中，觸發(fā)頁分裂操作，帶來額外的 IO 開銷。

對(duì)比示例

-- 亂序主鍵（低效，易觸發(fā)頁分裂）：8, 1, 9, 21, 88, 2, 4, 15, 89, 5, 7, 3
-- 順序主鍵（高效，無額外頁分裂）：1, 2, 3, 4, 5, 7, 8, 9, 15, 21, 88, 89

底層原理：頁分裂與頁合并

1.頁分裂：當(dāng)插入數(shù)據(jù)的主鍵值需要寫入已寫滿的頁時(shí)，InnoDB 會(huì)將當(dāng)前頁的數(shù)據(jù)分裂為兩個(gè)頁，移動(dòng)數(shù)據(jù)并調(diào)整 B+Tree 結(jié)構(gòu)，是插入性能的主要瓶頸之一；

插入50，會(huì)落在1#page區(qū)域，但1#page空間不足，就會(huì)進(jìn)行頁分裂，先將1中一半數(shù)據(jù)放進(jìn)3#page頁中，再將50放入3頁，然后調(diào)整頁指針。

2.頁合并：當(dāng)刪除數(shù)據(jù)后，頁中剩余數(shù)據(jù)低于MERGE_THRESHOLD（默認(rèn) 50%）時(shí)，InnoDB 會(huì)嘗試將相鄰的頁合并，減少碎片空間，優(yōu)化存儲(chǔ)效率。

1.4 大批量數(shù)據(jù)導(dǎo)入（LOAD DATA INFILE）

適用場(chǎng)景

一次性導(dǎo)入百萬級(jí)以上的大批量數(shù)據(jù)，使用INSERT語句效率極低，推薦使用 MySQL 提供的LOAD DATA指令，直接從本地文件加載數(shù)據(jù)，跳過 SQL 解析階段，性能提升可達(dá)數(shù)十倍。

操作步驟

1.客戶端連接時(shí)開啟本地文件加載權(quán)限：

mysql --local-infile -u root -p

2.全局開啟本地文件導(dǎo)入開關(guān)：

SET GLOBAL local_infile = 1;

3.執(zhí)行LOAD DATA指令導(dǎo)入數(shù)據(jù)：

LOAD DATA LOCAL INFILE '/root/sql1.log' 
INTO TABLE tb_user 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

注意事項(xiàng)

• 導(dǎo)入文件需提前按主鍵排序，避免導(dǎo)入過程中觸發(fā)頁分裂；
• 導(dǎo)入過程建議關(guān)閉索引，導(dǎo)入完成后再重建索引，減少導(dǎo)入過程中的索引維護(hù)開銷。

二、主鍵優(yōu)化（核心設(shè)計(jì)原則）

主鍵是 InnoDB 數(shù)據(jù)存儲(chǔ)與索引的核心，主鍵設(shè)計(jì)直接影響數(shù)據(jù)插入、查詢與更新的性能，需遵循以下 4 大核心原則：

2.1 降低主鍵長度（短主鍵優(yōu)先）

優(yōu)化邏輯

InnoDB 二級(jí)索引（輔助索引）中會(huì)包含主鍵值，主鍵越短，二級(jí)索引的體積越小，占用的磁盤空間越少，查詢時(shí) IO 效率越高。

實(shí)操建議

• 優(yōu)先使用INT/BIGINT類型的自增主鍵，避免使用長字符串（如 UUID、身份證號(hào)）作為主鍵；
• 業(yè)務(wù)中無需暴露的主鍵，可使用無業(yè)務(wù)含義的自增 ID，避免主鍵長度過長。

2.2 優(yōu)先使用自增主鍵（AUTO_INCREMENT）

優(yōu)化邏輯

自增主鍵保證數(shù)據(jù)按順序插入，數(shù)據(jù)會(huì)追加到當(dāng)前頁的末尾，不會(huì)觸發(fā)頁分裂操作，插入性能最優(yōu)；亂序主鍵（如 UUID、隨機(jī) ID）會(huì)頻繁觸發(fā)頁分裂，導(dǎo)致插入性能下降，同時(shí)產(chǎn)生大量存儲(chǔ)碎片。

底層存儲(chǔ)原理：索引組織表（IOT）

InnoDB 中表數(shù)據(jù)是根據(jù)主鍵順序組織存放的，這種存儲(chǔ)方式稱為索引組織表（Index Organized Table, IOT），數(shù)據(jù)按主鍵順序存儲(chǔ)在 B+Tree 的葉子節(jié)點(diǎn)中，順序插入可保持葉子節(jié)點(diǎn)的有序性與連續(xù)性，避免碎片產(chǎn)生。

實(shí)操規(guī)范

• 業(yè)務(wù)表主鍵統(tǒng)一使用AUTO_INCREMENT自增主鍵，數(shù)據(jù)類型優(yōu)先選擇BIGINT UNSIGNED（范圍更大，避免溢出）；
• 分布式場(chǎng)景需生成全局有序 ID 時(shí)，可使用雪花算法（保證時(shí)間戳部分有序），避免純隨機(jī) UUID。

2.3 避免使用 UUID 或自然主鍵

優(yōu)化邏輯

• UUID：隨機(jī)生成的 UUID 是亂序的，會(huì)導(dǎo)致頻繁的頁分裂，同時(shí)字符串類型的 UUID 長度較長，會(huì)增大二級(jí)索引的體積；
• 自然主鍵（如身份證號(hào)、手機(jī)號(hào)）：存在業(yè)務(wù)變更風(fēng)險(xiǎn)，且長度較長，無法保證插入順序，同時(shí)可能因業(yè)務(wù)需求修改主鍵，導(dǎo)致數(shù)據(jù)與索引的整體調(diào)整，成本極高。

對(duì)比示例

2.4 避免修改主鍵值

優(yōu)化邏輯

主鍵是 InnoDB 數(shù)據(jù)存儲(chǔ)的核心標(biāo)識(shí)，修改主鍵值會(huì)導(dǎo)致：

• 數(shù)據(jù)行需要在 B+Tree 中移動(dòng)位置，觸發(fā)頁分裂或頁合并操作；
• 所有二級(jí)索引中存儲(chǔ)的主鍵值都需要同步更新，維護(hù)成本極高，甚至可能導(dǎo)致索引失效。

實(shí)操規(guī)范

• 業(yè)務(wù)設(shè)計(jì)中，主鍵必須是無業(yè)務(wù)含義的字段，不參與任何業(yè)務(wù)邏輯，避免因業(yè)務(wù)變更修改主鍵；
• 若需修改業(yè)務(wù)標(biāo)識(shí)字段（如手機(jī)號(hào)），直接更新對(duì)應(yīng)字段即可，無需修改主鍵。

插入與主鍵優(yōu)化總結(jié)

三、ORDER by 優(yōu)化

排序操作是SQL性能的高頻瓶頸，核心問題是避免Using filesort（文件排序），優(yōu)先通過索引實(shí)現(xiàn)有序數(shù)據(jù)讀取。

3.1 核心概念：兩種排序方式

1.Using filesort：

非索引排序，通過全表掃描讀取數(shù)據(jù)，在sort_buffer中完成排序操作，所有不通過索引直接返回有序結(jié)果的排序都屬于文件排序，性能較差。

2.Using index：

利用有序索引直接掃描返回有序數(shù)據(jù)，無需額外排序，操作效率高，是排序優(yōu)化的目標(biāo)。

3.2 單字段/多字段排序優(yōu)化

1. 無索引時(shí)的排序（低效）

-- 無索引，觸發(fā)Using filesort
explain select id,age,phone from tb_user order by age , phone;

2. 創(chuàng)建聯(lián)合索引優(yōu)化（高效）

-- 創(chuàng)建age、phone的聯(lián)合索引，實(shí)現(xiàn)Using index
create index idx_user_age_phone_aa on tb_user(age,phone);
-- 升序排序：索引有序，無需額外排序
explain select id,age,phone from tb_user order by age , phone;
-- 同方向降序排序：索引支持，無需額外排序
explain select id,age,phone from tb_user order by age desc , phone desc;

3. 升降序混合排序優(yōu)化

當(dāng)排序字段為一個(gè)升序、一個(gè)降序時(shí)，需創(chuàng)建匹配順序的索引：

-- 創(chuàng)建age升序、phone降序的索引
create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);
-- 匹配索引順序，實(shí)現(xiàn)Using index
explain select id,age,phone from tb_user order by age asc , phone desc;

3.3ORDER BY優(yōu)化核心原則

• 按排序字段創(chuàng)建合適的索引，多字段排序遵循最左前綴法則；
• 盡量使用覆蓋索引，避免回表操作；
• 多字段排序方向需與索引定義的方向匹配，避免混合升降序；
• 若無法避免Using filesort，可適當(dāng)增大排序緩沖區(qū)sort_buffer_size（默認(rèn)256KB），提升排序性能。

四、GROUP BY優(yōu)化

分組操作的核心瓶頸是臨時(shí)表創(chuàng)建與排序，可通過索引優(yōu)化避免Using temporary。

4.1 索引優(yōu)化原理

分組操作底層依賴數(shù)據(jù)有序性，索引的有序性可直接用于分組聚合，無需額外創(chuàng)建臨時(shí)表。

4.2 實(shí)操示例

-- 刪除舊索引
drop index idx_user_pro_age_sta on tb_user;
-- 無索引分組，觸發(fā)Using temporary
explain select profession , count(*) from tb_user group by profession ;
-- 創(chuàng)建(profession, age, status)聯(lián)合索引
create index idx_user_pro_age_sta on tb_user(profession , age , status);
-- 匹配最左前綴，實(shí)現(xiàn)Using index，無臨時(shí)表
explain select profession , count(*) from tb_user group by profession ;
explain select profession , count(*) from tb_user group by profession, age;
-- 帶過濾條件的分組：where profession='軟件工程' group by age
explain select age,count(*) from tb_user where profession = '軟件工程' group by age;

4.3GROUP BY優(yōu)化核心原則

• 分組字段遵循最左前綴法則，優(yōu)先創(chuàng)建包含分組字段的聯(lián)合索引；
• 索引中包含查詢所需的所有字段，實(shí)現(xiàn)覆蓋索引；
• 過濾條件（WHERE）字段需在分組字段之前，保證索引有序性；
• 避免在低基數(shù)字段上進(jìn)行分組，減少臨時(shí)表創(chuàng)建的概率。

五、LIMIT分頁優(yōu)化

大數(shù)據(jù)量下的分頁查詢（如limit 2000000,10）性能極差，需通過覆蓋索引+子查詢優(yōu)化。

5.1 問題根源

limit m,n的執(zhí)行邏輯是先讀取前m+n條記錄，再丟棄前m條，返回后n條，當(dāng)m很大時(shí)，會(huì)產(chǎn)生大量無效IO。

5.2 優(yōu)化方案：覆蓋索引+子查詢

-- 低效方式：直接分頁查詢，全表掃描排序
select * from tb_sku limit 2000000,10;
-- 高效方式：先通過覆蓋索引獲取id，再關(guān)聯(lián)查詢完整數(shù)據(jù)
explain select * from tb_sku t , 
(select id from tb_sku order by id limit 2000000,10) a 
where t.id = a.id;

5.3 優(yōu)化核心原則

• 利用主鍵或唯一索引的有序性，通過子查詢快速定位分頁數(shù)據(jù)的ID；
• 避免select *，僅在子查詢中獲取主鍵ID，減少數(shù)據(jù)讀取量；
• 對(duì)于超大分頁，可通過WHERE id > ? LIMIT n的方式優(yōu)化，前提是主鍵連續(xù)有序。

六、COUNT優(yōu)化

COUNT操作的性能差異主要由存儲(chǔ)引擎與使用方式?jīng)Q定，需根據(jù)業(yè)務(wù)場(chǎng)景選擇最優(yōu)方案。

6.1 存儲(chǔ)引擎差異

• MyISAM：直接將表總行數(shù)存儲(chǔ)在磁盤中，count(*)可直接返回結(jié)果，效率極高；
• InnoDB：不存儲(chǔ)總行數(shù)，執(zhí)行count(*)時(shí)需遍歷數(shù)據(jù)行進(jìn)行計(jì)數(shù)，性能受數(shù)據(jù)量影響較大。

6.2COUNT用法效率對(duì)比

結(jié)論：優(yōu)先使用COUNT(*)，效率最優(yōu)。

6.3 優(yōu)化核心原則

• 優(yōu)先使用COUNT(*)，避免使用COUNT(字段)；
• 高頻統(tǒng)計(jì)場(chǎng)景可通過緩存（如Redis）或統(tǒng)計(jì)表預(yù)存計(jì)數(shù)結(jié)果；
• 帶條件的計(jì)數(shù)查詢，需為過濾條件創(chuàng)建索引，減少掃描行數(shù)。

七、UPDATE優(yōu)化

UPDATE操作的核心風(fēng)險(xiǎn)是行鎖升級(jí)為表鎖，導(dǎo)致并發(fā)性能下降，需通過索引保證行鎖的有效性。

7.1 核心原理

InnoDB的行鎖是針對(duì)索引加鎖，而非針對(duì)記錄加鎖。若更新條件字段沒有索引或索引失效，行鎖會(huì)升級(jí)為表鎖，嚴(yán)重影響并發(fā)性能。

7.2 實(shí)操示例

-- 高效更新：主鍵索引條件，行鎖，僅鎖定id=1的記錄
update student set no = '2000100100' where id = 1;
-- 低效更新：無索引的name字段，索引失效，行鎖升級(jí)為表鎖
update student set no = '2000100105' where name = '韋一笑';

7.3UPDATE優(yōu)化核心原則

• 更新條件字段必須有有效索引，避免索引失效導(dǎo)致表鎖；
• 優(yōu)先使用主鍵或唯一索引作為更新條件，保證行鎖粒度最?。?/li>
• 避免批量更新無索引的條件字段，減少表鎖的概率；
• 大表更新時(shí)，分批執(zhí)行，避免長時(shí)間持有鎖。

補(bǔ)充：SQL優(yōu)化通用避坑清單

四、視圖、存儲(chǔ)過程、觸發(fā)器

一、視圖

一、視圖基礎(chǔ)概念

1. 什么是視圖？

視圖是虛擬存在的表，它本身不存儲(chǔ)真實(shí)數(shù)據(jù)，只保存了查詢的SQL邏輯，數(shù)據(jù)在使用視圖時(shí)動(dòng)態(tài)從基表中生成。

• 本質(zhì)：視圖是SELECT查詢的封裝，相當(dāng)于給復(fù)雜查詢起了個(gè)“別名”。
• 特點(diǎn)：
  • 不占用實(shí)際存儲(chǔ)空間，僅保存SQL定義；
  • 視圖的數(shù)據(jù)完全依賴基表，基表數(shù)據(jù)變化時(shí)，視圖數(shù)據(jù)也會(huì)同步變化；
  • 支持像普通表一樣進(jìn)行查詢、修改（部分場(chǎng)景）操作。

二、視圖的基礎(chǔ)操作

1. 創(chuàng)建視圖

-- 語法格式
CREATE [OR REPLACE] VIEW 視圖名稱[(列名列表)] 
AS SELECT語句 
[WITH [CASCADED | LOCAL] CHECK OPTION];
-- 示例：創(chuàng)建視圖，查詢學(xué)生表中id<=20的數(shù)據(jù)
CREATE VIEW v_student_20 
AS SELECT id, name FROM student WHERE id <= 20;

• OR REPLACE：如果視圖已存在，則替換原有定義；
• WITH CHECK OPTION：視圖更新/插入數(shù)據(jù)時(shí)，必須滿足視圖的查詢條件，否則會(huì)報(bào)錯(cuò)。

2. 查詢視圖

-- 查看視圖的創(chuàng)建語句
SHOW CREATE VIEW v_student_20;
-- 查詢視圖數(shù)據(jù)（和普通表用法一致）
SELECT * FROM v_student_20 WHERE name LIKE '張%';

3. 修改視圖

-- 方式一：CREATE OR REPLACE VIEW（推薦）
CREATE OR REPLACE VIEW v_student_20 
AS SELECT id, name, age FROM student WHERE id <= 20;
-- 方式二：ALTER VIEW
ALTER VIEW v_student_20 
AS SELECT id, name, age FROM student WHERE id <= 20;

4. 刪除視圖

-- 語法格式
DROP VIEW [IF EXISTS] 視圖名稱 [,視圖名稱] ...;

-- 示例
DROP VIEW IF EXISTS v_student_20;

三、視圖的檢查選項(xiàng)（WITH CHECK OPTION）

當(dāng)使用WITH CHECK OPTION創(chuàng)建視圖時(shí)，MySQL會(huì)在視圖的INSERT/UPDATE/DELETE操作中，檢查數(shù)據(jù)是否符合視圖的定義條件，不符合則拒絕執(zhí)行。

對(duì)于多層嵌套視圖，MySQL提供兩種檢查規(guī)則：

1.CASCADED（默認(rèn)）：級(jí)聯(lián)檢查

規(guī)則：檢查當(dāng)前視圖和所有上層依賴視圖的條件，只要有一層視圖條件不滿足，就會(huì)報(bào)錯(cuò)。

示例：

-- 基表student
-- 視圖v1：id <= 20，帶CASCADED檢查
CREATE VIEW v1 AS SELECT id,name FROM student WHERE id <= 20 WITH CASCADED CHECK OPTION;
-- 視圖v2：基于v1，id >= 10，帶CASCADED檢查
CREATE VIEW v2 AS SELECT id,name FROM v1 WHERE id >= 10 WITH CASCADED CHECK OPTION;
-- 視圖v3：基于v2，id <= 15，無檢查
CREATE VIEW v3 AS SELECT id,name FROM v2 WHERE id <= 15;
-- 嘗試向v3插入id=21的數(shù)據(jù)：會(huì)觸發(fā)檢查，不符合v1的id<=20條件，插入失敗
INSERT INTO v3(id,name) VALUES(21,'test');

2.LOCAL：僅檢查當(dāng)前視圖

規(guī)則：只檢查當(dāng)前視圖的條件，不檢查上層依賴視圖的條件（但上層視圖帶CASCADED檢查時(shí)，仍會(huì)觸發(fā)級(jí)聯(lián)檢查）。

示例：

-- 基表student
-- 視圖v1：id <= 15，無檢查
CREATE VIEW v1 AS SELECT id,name FROM student WHERE id <= 15;
-- 視圖v2：基于v1，id >= 10，帶LOCAL檢查
CREATE VIEW v2 AS SELECT id,name FROM v1 WHERE id >= 10 WITH LOCAL CHECK OPTION;

-- 嘗試向v2插入id=16的數(shù)據(jù)：滿足v2的id>=10條件，但不滿足v1的id<=15條件，插入失?。ㄒ?yàn)閿?shù)據(jù)無法被v1查詢到，視圖數(shù)據(jù)不生效）
INSERT INTO v2(id,name) VALUES(16,'test');

四、視圖的更新規(guī)則

視圖的更新（INSERT/UPDATE/DELETE）并非都能執(zhí)行，核心前提是：視圖中的行與基表中的行存在一對(duì)一的映射關(guān)系。

1. 不可更新的場(chǎng)景

如果視圖定義中包含以下任意一項(xiàng)，則該視圖無法更新：

• 包含聚合函數(shù)或窗口函數(shù)：SUM()、MIN()、MAX()、COUNT()、ROW_NUMBER()等；
• 包含DISTINCT去重；
• 包含GROUP BY分組；
• 包含HAVING過濾；
• 包含UNION或UNION ALL；
• 基于多個(gè)基表的連接查詢（非單表視圖）；
• 視圖的列是基表列的計(jì)算結(jié)果（如age+1）。

2. 可更新的場(chǎng)景

• 單表視圖，無聚合、分組、去重等操作；
• 視圖列與基表列一一對(duì)應(yīng)，無計(jì)算列；
• 若使用WITH CHECK OPTION，更新/插入的數(shù)據(jù)必須滿足視圖的查詢條件。

五、視圖的核心作用

1. 簡化操作（Simple）

• 封裝復(fù)雜查詢：將常用的多表連接、條件過濾查詢定義為視圖，后續(xù)直接查詢視圖即可，無需重復(fù)編寫SQL；
• 示例：把JOIN + WHERE + GROUP BY的復(fù)雜報(bào)表查詢封裝為視圖，業(yè)務(wù)人員直接查詢視圖即可獲取數(shù)據(jù)。

2. 安全控制（Security）

• 實(shí)現(xiàn)行級(jí)/列級(jí)權(quán)限控制：數(shù)據(jù)庫無法直接對(duì)特定行/列授權(quán)，但可以通過視圖實(shí)現(xiàn)；
• 示例：創(chuàng)建視圖僅包含用戶的非敏感字段（如隱藏手機(jī)號(hào)、身份證號(hào)），并僅展示當(dāng)前用戶的數(shù)據(jù)，給業(yè)務(wù)人員授予視圖的查詢權(quán)限，避免敏感數(shù)據(jù)泄露。

3. 數(shù)據(jù)獨(dú)立（Data Independence）

• 屏蔽基表結(jié)構(gòu)變化：基表的字段名、字段順序調(diào)整時(shí)，只需修改視圖定義，上層業(yè)務(wù)代碼無需修改；
• 示例：基表student的name字段重命名為stu_name，只需修改視圖的SELECT stu_name AS name，上層查詢視圖的業(yè)務(wù)代碼無需改動(dòng)。

六、視圖的優(yōu)缺點(diǎn)與使用建議

優(yōu)點(diǎn)

• 簡化復(fù)雜查詢，提升開發(fā)效率；
• 實(shí)現(xiàn)數(shù)據(jù)權(quán)限控制，保障數(shù)據(jù)安全；
• 解耦基表與上層業(yè)務(wù)，降低維護(hù)成本。

缺點(diǎn)

• 視圖本身不優(yōu)化查詢，執(zhí)行視圖時(shí)仍會(huì)執(zhí)行基表的查詢邏輯，復(fù)雜視圖可能存在性能問題；
• 多層嵌套視圖會(huì)增加查詢的復(fù)雜度，調(diào)試?yán)щy；
• 視圖更新限制多，不適合頻繁修改基表數(shù)據(jù)的場(chǎng)景。

使用建議

• 優(yōu)先用視圖封裝只讀查詢（如報(bào)表、統(tǒng)計(jì)），避免用于寫操作；
• 避免多層嵌套視圖，建議不超過2層；
• 復(fù)雜查詢優(yōu)先直接編寫SQL，視圖僅用于簡化高頻、固定的查詢場(chǎng)景。

二、存儲(chǔ)過程

一、存儲(chǔ)過程基礎(chǔ)概念

1. 什么是存儲(chǔ)過程？

存儲(chǔ)過程是一組預(yù)編譯并存儲(chǔ)在數(shù)據(jù)庫中的SQL語句集合，相當(dāng)于數(shù)據(jù)庫層面的“函數(shù)”。調(diào)用時(shí)只需傳入?yún)?shù)即可執(zhí)行封裝好的邏輯，無需重復(fù)編寫SQL。

• 核心思想：SQL語句的封裝與復(fù)用；
• 優(yōu)勢(shì)：減少應(yīng)用與數(shù)據(jù)庫的網(wǎng)絡(luò)交互、提升數(shù)據(jù)處理效率、簡化開發(fā)操作。

二、存儲(chǔ)過程基礎(chǔ)操作

1. 創(chuàng)建存儲(chǔ)過程

語法格式

-- 命令行中需先修改結(jié)束符（避免和默認(rèn);沖突）
DELIMITER //
CREATE PROCEDURE 存儲(chǔ)過程名稱([參數(shù)列表])
BEGIN
    -- 存儲(chǔ)過程內(nèi)的SQL邏輯
END //
DELIMITER ; -- 恢復(fù)默認(rèn)結(jié)束符

示例：無參數(shù)存儲(chǔ)過程

-- 示例：查詢用戶表的總數(shù)
DELIMITER //
CREATE PROCEDURE sp_get_user_count()
BEGIN
    SELECT COUNT(*) AS user_total FROM tb_user;
END //
DELIMITER ;

2. 調(diào)用存儲(chǔ)過程

語法格式

CALL 存儲(chǔ)過程名稱([參數(shù)]);

示例：調(diào)用上面創(chuàng)建的存儲(chǔ)過程

CALL sp_get_user_count();

3. 查看存儲(chǔ)過程

-- 1. 查看指定數(shù)據(jù)庫的所有存儲(chǔ)過程信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'test_db';
-- 2. 查看單個(gè)存儲(chǔ)過程的創(chuàng)建語句
SHOW CREATE PROCEDURE sp_get_user_count;

4. 刪除存儲(chǔ)過程

語法格式

DROP PROCEDURE [IF EXISTS] 存儲(chǔ)過程名稱;

示例

DROP PROCEDURE IF EXISTS sp_get_user_count;

三、存儲(chǔ)過程的參數(shù)類型

存儲(chǔ)過程支持3種參數(shù)類型，滿足輸入、輸出、雙向交互需求：

示例1：帶IN輸入?yún)?shù)的存儲(chǔ)過程

-- 示例：根據(jù)用戶id查詢用戶信息
DELIMITER //
CREATE PROCEDURE sp_get_user_by_id(IN p_user_id INT)
BEGIN
    SELECT * FROM tb_user WHERE id = p_user_id;
END //
DELIMITER ;
-- 調(diào)用
CALL sp_get_user_by_id(1);

示例2：帶OUT輸出參數(shù)的存儲(chǔ)過程

-- 示例：查詢用戶總數(shù)，并通過輸出參數(shù)返回
DELIMITER //
CREATE PROCEDURE sp_get_user_count_out(OUT p_total INT)
BEGIN
    SELECT COUNT(*) INTO p_total FROM tb_user;
END //
DELIMITER ;
-- 調(diào)用：用用戶變量接收返回值
CALL sp_get_user_count_out(@user_count);
SELECT @user_count AS user_total;

示例3：帶INOUT雙向參數(shù)的存儲(chǔ)過程

-- 示例：傳入一個(gè)數(shù)字，將其乘以2后返回
DELIMITER //
CREATE PROCEDURE sp_double_num(INOUT p_num INT)
BEGIN
    SET p_num = p_num * 2;
END //
DELIMITER ;
-- 調(diào)用
SET @num = 10;
CALL sp_double_num(@num);
SELECT @num AS doubled_num; -- 結(jié)果為20

四、存儲(chǔ)過程中的變量

MySQL存儲(chǔ)過程中包含三類變量：系統(tǒng)變量、用戶定義變量、局部變量。

1. 系統(tǒng)變量

MySQL服務(wù)器提供的內(nèi)置變量，分為全局變量（GLOBAL）和會(huì)話變量（SESSION）。

查看系統(tǒng)變量

-- 查看所有會(huì)話變量
SHOW SESSION VARIABLES;
-- 模糊查找變量（如查看排序緩沖區(qū)大?。?
SHOW VARIABLES LIKE 'sort_buffer_size';
-- 查看指定變量的值
SELECT @@global.sort_buffer_size; -- 全局變量
SELECT @@session.sort_buffer_size; -- 會(huì)話變量

設(shè)置系統(tǒng)變量

-- 設(shè)置會(huì)話級(jí)變量（僅當(dāng)前會(huì)話生效）
SET SESSION sort_buffer_size = 1024*1024; -- 1MB
-- 設(shè)置全局變量（重啟后失效，需修改配置文件永久生效）
SET GLOBAL sort_buffer_size = 2*1024*1024; -- 2MB

2. 用戶定義變量

用戶自定義的會(huì)話級(jí)變量，無需提前聲明，直接用@變量名使用，作用域?yàn)楫?dāng)前會(huì)話。

賦值與使用

-- 方式1：SET賦值
SET @user_name = 'zhangsan';
SET @age := 18; -- := 也可賦值
-- 方式2：SELECT INTO賦值
SELECT name, age INTO @user_name, @user_age FROM tb_user WHERE id = 1;
-- 使用變量
SELECT * FROM tb_user WHERE name = @user_name;

3. 局部變量

存儲(chǔ)過程內(nèi)的局部變量，需用DECLARE聲明，作用域?yàn)?code>BEGIN...END塊內(nèi)。

聲明與賦值

DELIMITER //
CREATE PROCEDURE sp_local_var_demo()
BEGIN
    -- 聲明局部變量，指定類型和默認(rèn)值
    DECLARE v_total INT DEFAULT 0;
    DECLARE v_name VARCHAR(20);
    -- 賦值
    SELECT COUNT(*) INTO v_total FROM tb_user;
    SET v_name = 'local_test';
    -- 使用變量
    SELECT v_total, v_name;
END //
DELIMITER ;
CALL sp_local_var_demo();

五、存儲(chǔ)過程中的流程控制

1.IF條件語句

語法格式

IF 條件1 THEN
    -- 條件1成立時(shí)執(zhí)行
ELSEIF 條件2 THEN
    -- 條件2成立時(shí)執(zhí)行（可選）
ELSE
    -- 所有條件不成立時(shí)執(zhí)行（可選）
END IF;

示例：根據(jù)用戶數(shù)量判斷用戶規(guī)模

DELIMITER //
CREATE PROCEDURE sp_user_scale()
BEGIN
    DECLARE v_count INT DEFAULT 0;
    DECLARE v_scale VARCHAR(20);
    SELECT COUNT(*) INTO v_count FROM tb_user;
    IF v_count > 1000 THEN
        SET v_scale = '大規(guī)模用戶';
    ELSEIF v_count > 100 THEN
        SET v_scale = '中規(guī)模用戶';
    ELSE
        SET v_scale = '小規(guī)模用戶';
    END IF;
    SELECT v_count, v_scale;
END //
DELIMITER ;
CALL sp_user_scale();

2.CASE條件語句

支持兩種語法格式，適合多分支條件判斷。

語法格式1：匹配固定值

CASE case_value
    WHEN when_value1 THEN statement_list1
    WHEN when_value2 THEN statement_list2
    ELSE statement_list
END CASE;

語法格式2：匹配條件表達(dá)式

CASE
    WHEN search_condition1 THEN statement_list1
    WHEN search_condition2 THEN statement_list2
    ELSE statement_list
END CASE;

示例：根據(jù)用戶等級(jí)返回描述

DELIMITER //
CREATE PROCEDURE sp_user_level_desc(IN p_level INT, OUT p_desc VARCHAR(20))
BEGIN
    CASE p_level
        WHEN 1 THEN SET p_desc = '普通用戶';
        WHEN 2 THEN SET p_desc = 'VIP用戶';
        WHEN 3 THEN SET p_desc = 'SVIP用戶';
        ELSE SET p_desc = '未知等級(jí)';
    END CASE;
END //
DELIMITER ;
-- 調(diào)用
CALL sp_user_level_desc(2, @level_desc);
SELECT @level_desc; -- 結(jié)果為VIP用戶

六、存儲(chǔ)過程中的循環(huán)語句

MySQL 存儲(chǔ)過程支持 3 種循環(huán)：WHILE、REPEAT、LOOP，適用于不同場(chǎng)景的批量數(shù)據(jù)處理。

1.WHILE循環(huán)（先判斷，后執(zhí)行）

語法格式

WHILE 條件 DO
    -- 循環(huán)體SQL邏輯
END WHILE;

示例：批量插入 10 條測(cè)試用戶數(shù)據(jù)

DELIMITER //
CREATE PROCEDURE sp_batch_insert_user()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 10 DO
        INSERT INTO tb_user (name, age) VALUES (CONCAT('test_', i), 18 + i);
        SET i = i + 1; -- 必須更新計(jì)數(shù)器，否則會(huì)死循環(huán)
    END WHILE;
END //
DELIMITER ;
-- 調(diào)用執(zhí)行
CALL sp_batch_insert_user();

2.REPEAT循環(huán)（先執(zhí)行，后判斷）

語法格式

REPEAT
    -- 循環(huán)體SQL邏輯
UNTIL 條件 -- 條件滿足時(shí)退出循環(huán)
END REPEAT;

示例：批量插入 10 條測(cè)試用戶數(shù)據(jù)（REPEAT 實(shí)現(xiàn)）

DELIMITER //
CREATE PROCEDURE sp_repeat_insert_user()
BEGIN
    DECLARE i INT DEFAULT 1;
    REPEAT
        INSERT INTO tb_user (name, age) VALUES (CONCAT('repeat_', i), 20 + i);
        SET i = i + 1;
    UNTIL i > 10 -- i>10時(shí)退出循環(huán)
    END REPEAT;
END //
DELIMITER ;
-- 調(diào)用執(zhí)行
CALL sp_repeat_insert_user();

3.LOOP循環(huán)（無條件循環(huán)，需手動(dòng)退出）

語法格式

[begin_label:] LOOP
    -- 循環(huán)體SQL邏輯
    -- 需用LEAVE手動(dòng)退出循環(huán)，否則會(huì)死循環(huán)
END LOOP [end_label];

• LEAVE label：退出指定標(biāo)簽的循環(huán)；
• ITERATE label：跳過當(dāng)前循環(huán)，直接進(jìn)入下一次循環(huán)。

示例：LOOP 循環(huán)實(shí)現(xiàn)批量插入，含跳過邏輯

DELIMITER //
CREATE PROCEDURE sp_loop_insert_user()
BEGIN
    DECLARE i INT DEFAULT 1;
    loop_label: LOOP -- 定義循環(huán)標(biāo)簽
        IF i > 10 THEN
            LEAVE loop_label; -- 條件滿足時(shí)退出循環(huán)
        END IF;
        -- 跳過偶數(shù)次插入，只插入奇數(shù)
        IF i % 2 = 0 THEN
            SET i = i + 1;
            ITERATE loop_label; -- 跳過當(dāng)前循環(huán)，直接下一次
        END IF;
        INSERT INTO tb_user (name, age) VALUES (CONCAT('loop_', i), 25 + i);
        SET i = i + 1;
    END LOOP loop_label;
END //
DELIMITER ;
-- 調(diào)用執(zhí)行
CALL sp_loop_insert_user();

七、游標(biāo)（CURSOR）：遍歷結(jié)果集

游標(biāo)用于存儲(chǔ)查詢結(jié)果集，可在存儲(chǔ)過程中逐行處理數(shù)據(jù)，適合批量數(shù)據(jù)處理場(chǎng)景。

游標(biāo)使用步驟

• 聲明游標(biāo)：綁定查詢語句；
• 打開游標(biāo)：執(zhí)行查詢，獲取結(jié)果集；
• 獲取數(shù)據(jù)：逐行讀取結(jié)果集數(shù)據(jù)到變量；
• 關(guān)閉游標(biāo)：釋放資源。

語法格式

-- 1. 聲明游標(biāo)
DECLARE 游標(biāo)名稱 CURSOR FOR 查詢語句;
-- 2. 打開游標(biāo)
OPEN 游標(biāo)名稱;
-- 3. 獲取數(shù)據(jù)
FETCH 游標(biāo)名稱 INTO 變量1, 變量2...;
-- 4. 關(guān)閉游標(biāo)
CLOSE 游標(biāo)名稱;

八、條件處理程序（異常處理）

條件處理程序（Handler）用于捕獲存儲(chǔ)過程執(zhí)行中的異常，并定義處理邏輯，避免程序因異常中斷。

語法格式

DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement;

• handler_action：
  • CONTINUE：捕獲異常后繼續(xù)執(zhí)行后續(xù)代碼；
  • EXIT：捕獲異常后終止當(dāng)前存儲(chǔ)過程；
• condition_value：異常條件，支持：
  • SQLSTATE 'xxxx'：指定 SQL 狀態(tài)碼（如02000表示 NOT FOUND）；
  • SQLWARNING：捕獲所有以01開頭的警告；
  • NOT FOUND：捕獲所有以02開頭的未找到數(shù)據(jù)異常；
  • SQLEXCEPTION：捕獲所有其他 SQL 異常。

示例：捕獲異常并記錄日志

-- 先創(chuàng)建日志表
CREATE TABLE IF NOT EXISTS proc_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    error_msg VARCHAR(200),
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
DELIMITER //
CREATE PROCEDURE sp_exception_demo()
BEGIN
    -- 聲明異常處理：捕獲異常后繼續(xù)執(zhí)行，并記錄日志
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        INSERT INTO proc_log (error_msg) VALUES ('執(zhí)行存儲(chǔ)過程時(shí)發(fā)生異常');
    -- 可能出錯(cuò)的SQL：插入重復(fù)主鍵數(shù)據(jù)
    INSERT INTO tb_user (id, name) VALUES (1, 'test_user');
    SELECT '執(zhí)行完成' AS result;
END //
DELIMITER ;
-- 調(diào)用執(zhí)行（若id=1已存在，會(huì)觸發(fā)異常，但程序會(huì)繼續(xù)執(zhí)行并記錄日志）
CALL sp_exception_demo();
-- 查看日志
SELECT * FROM proc_log;

九、存儲(chǔ)過程完整實(shí)戰(zhàn)示例：批量處理用戶數(shù)據(jù)

綜合使用變量、循環(huán)、游標(biāo)、異常處理，實(shí)現(xiàn)一個(gè)完整的批量數(shù)據(jù)處理存儲(chǔ)過程：

DELIMITER //
CREATE PROCEDURE sp_user_batch_process(IN p_start_id INT, IN p_end_id INT)
BEGIN
    -- 1. 聲明變量
    DECLARE v_id INT;
    DECLARE v_age INT;
    DECLARE done INT DEFAULT FALSE;
    DECLARE update_count INT DEFAULT 0;
    -- 2. 聲明游標(biāo)和異常處理
    DECLARE user_cursor CURSOR FOR 
        SELECT id, age FROM tb_user WHERE id BETWEEN p_start_id AND p_end_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
        INSERT INTO proc_log (error_msg) VALUES (CONCAT('批量處理用戶數(shù)據(jù)異常，范圍：', p_start_id, '-', p_end_id));
    -- 3. 打開游標(biāo)，遍歷處理
    OPEN user_cursor;
    user_loop: LOOP
        FETCH user_cursor INTO v_id, v_age;
        IF done THEN
            LEAVE user_loop;
        END IF;
        -- 業(yè)務(wù)邏輯：年齡>30的用戶，標(biāo)記為VIP
        IF v_age > 30 THEN
            UPDATE tb_user SET is_vip = 1 WHERE id = v_id;
            SET update_count = update_count + 1;
        END IF;
    END LOOP;
    CLOSE user_cursor;
    -- 4. 返回處理結(jié)果
    SELECT CONCAT('批量處理完成，共更新', update_count, '條數(shù)據(jù)') AS result;
END //
DELIMITER ;
-- 調(diào)用：處理id 1-100的用戶數(shù)據(jù)
CALL sp_user_batch_process(1, 100);

十、存儲(chǔ)過程使用注意事項(xiàng)

• 循環(huán)控制：WHILE/REPEAT/LOOP循環(huán)中必須有計(jì)數(shù)器更新或退出條件，否則會(huì)導(dǎo)致死循環(huán)；
• 游標(biāo)資源：使用完游標(biāo)后必須CLOSE，否則會(huì)占用數(shù)據(jù)庫資源；
• 異常處理：批量數(shù)據(jù)處理時(shí)建議添加異常處理，避免單條數(shù)據(jù)異常導(dǎo)致整個(gè)批量任務(wù)中斷；
• 性能問題：存儲(chǔ)過程內(nèi)避免大事務(wù)、長循環(huán)，防止鎖表或影響數(shù)據(jù)庫性能；
• 調(diào)試建議：MySQL 存儲(chǔ)過程調(diào)試?yán)щy，復(fù)雜邏輯建議分步測(cè)試，先驗(yàn)證單條 SQL 再封裝。

三、存儲(chǔ)函數(shù)

1. 核心概念

存儲(chǔ)函數(shù)是有返回值的存儲(chǔ)過程，它的參數(shù)只能是 IN 類型，且必須通過 RETURN 語句返回一個(gè)結(jié)果。

它可以像普通內(nèi)置函數(shù)一樣，直接在 SELECT 語句中調(diào)用。

2. 語法格式

CREATE FUNCTION 存儲(chǔ)函數(shù)名稱([參數(shù)列表])
RETURNS type [characteristic ...]
BEGIN
    -- SQL語句
    RETURN ...; -- 必須有RETURN語句
END;

關(guān)鍵字說明

• RETURNS type：指定函數(shù)的返回值類型（如 INT, VARCHAR, DECIMAL 等）。
• characteristic：特性說明，用于優(yōu)化和約束函數(shù)行為：
  • DETERMINISTIC：相同輸入?yún)?shù)總是產(chǎn)生相同結(jié)果（純函數(shù)）。
  • NO SQL：函數(shù)體內(nèi)不包含任何SQL語句。
  • READS SQL DATA：函數(shù)體內(nèi)只包含讀數(shù)據(jù)的語句，不包含寫數(shù)據(jù)的語句。

3. 基礎(chǔ)示例

示例1：無參數(shù)的存儲(chǔ)函數(shù)

-- 示例：獲取當(dāng)前系統(tǒng)日期
DELIMITER //
CREATE FUNCTION fn_get_current_date()
RETURNS DATE
NO SQL
BEGIN
    RETURN CURDATE();
END //
DELIMITER ;
-- 調(diào)用
SELECT fn_get_current_date();

示例2：帶參數(shù)的存儲(chǔ)函數(shù)

-- 示例：根據(jù)用戶ID查詢用戶年齡
DELIMITER //
CREATE FUNCTION fn_get_user_age(p_user_id INT)
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE v_age INT;
    SELECT age INTO v_age FROM tb_user WHERE id = p_user_id;
    RETURN v_age;
END //
DELIMITER ;
-- 調(diào)用
SELECT fn_get_user_age(1);

4. 與存儲(chǔ)過程的核心區(qū)別

5. 使用注意事項(xiàng)

• 必須有返回值：存儲(chǔ)函數(shù)必須包含 RETURN 語句，否則會(huì)報(bào)錯(cuò)。
• 參數(shù)限制：函數(shù)參數(shù)默認(rèn)是 IN 類型，不能顯式指定 OUT 或 INOUT。
• 數(shù)據(jù)修改限制：為了保證可在 SELECT 中安全調(diào)用，存儲(chǔ)函數(shù)內(nèi)不建議執(zhí)行 INSERT/UPDATE/DELETE 等寫操作，否則可能導(dǎo)致數(shù)據(jù)不一致。
• 權(quán)限要求：創(chuàng)建存儲(chǔ)函數(shù)需要 CREATE ROUTINE 權(quán)限，調(diào)用時(shí)需要 EXECUTE 權(quán)限。

四、觸發(fā)器

1. 核心概念

觸發(fā)器是與表關(guān)聯(lián)的數(shù)據(jù)庫對(duì)象，它會(huì)在 INSERT/UPDATE/DELETE 操作執(zhí)行之前或之后，自動(dòng)觸發(fā)并執(zhí)行預(yù)定義的SQL語句集合。

• 作用：在數(shù)據(jù)庫端確保數(shù)據(jù)完整性、記錄操作日志、數(shù)據(jù)校驗(yàn)、級(jí)聯(lián)更新等。
• 特點(diǎn)：
  • 僅支持行級(jí)觸發(fā)（FOR EACH ROW），每操作一行觸發(fā)一次；
  • 使用 OLD 和 NEW 關(guān)鍵字引用數(shù)據(jù)：

2. 語法格式

創(chuàng)建觸發(fā)器

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行級(jí)觸發(fā)器
BEGIN
    trigger_stmt; -- 觸發(fā)時(shí)執(zhí)行的SQL
END;

查看觸發(fā)器

SHOW TRIGGERS;

刪除觸發(fā)器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

3. 實(shí)戰(zhàn)示例：數(shù)據(jù)變更日志觸發(fā)器

我們通過觸發(fā)器實(shí)現(xiàn) tb_user 表的增/改/刪操作日志記錄，將日志寫入 user_logs 表。

步驟1：創(chuàng)建日志表

CREATE TABLE user_logs(
    id INT(11) NOT NULL AUTO_INCREMENT,
    operation VARCHAR(20) NOT NULL COMMENT '操作類型, insert/update/delete',
    operate_time DATETIME NOT NULL COMMENT '操作時(shí)間',
    operate_id INT(11) NOT NULL COMMENT '操作的ID',
    operate_params VARCHAR(500) COMMENT '操作參數(shù)',
    PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

步驟2：創(chuàng)建INSERT觸發(fā)器（記錄新增日志）

DELIMITER //
CREATE TRIGGER tb_user_insert_trigger
AFTER INSERT ON tb_user FOR EACH ROW
BEGIN
    INSERT INTO user_logs(operation, operate_time, operate_id, operate_params) 
    VALUES(
        'insert', 
        NOW(), 
        NEW.id, 
        CONCAT('新增數(shù)據(jù)：id=', NEW.id, ', name=', NEW.name, ', phone=', NEW.phone)
    );
END //
DELIMITER ;

步驟3：創(chuàng)建UPDATE觸發(fā)器（記錄修改日志）

DELIMITER //
CREATE TRIGGER tb_user_update_trigger
AFTER UPDATE ON tb_user FOR EACH ROW
BEGIN
    INSERT INTO user_logs(operation, operate_time, operate_id, operate_params) 
    VALUES(
        'update', 
        NOW(), 
        NEW.id, 
        CONCAT('修改前：id=', OLD.id, ', name=', OLD.name, ' | 修改后：id=', NEW.id, ', name=', NEW.name)
    );
END //
DELIMITER ;

步驟4：創(chuàng)建DELETE觸發(fā)器（記錄刪除日志）

DELIMITER //
CREATE TRIGGER tb_user_delete_trigger
AFTER DELETE ON tb_user FOR EACH ROW
BEGIN
    INSERT INTO user_logs(operation, operate_time, operate_id, operate_params) 
    VALUES(
        'delete', 
        NOW(), 
        OLD.id, 
        CONCAT('刪除數(shù)據(jù)：id=', OLD.id, ', name=', OLD.name, ', phone=', OLD.phone)
    );
END //
DELIMITER ;

測(cè)試觸發(fā)器

-- 1. 新增用戶（觸發(fā)insert觸發(fā)器）
INSERT INTO tb_user(id, name, phone) VALUES(26, '張三', '18809091212');
-- 2. 修改用戶（觸發(fā)update觸發(fā)器）
UPDATE tb_user SET name = '張三三' WHERE id = 26;
-- 3. 刪除用戶（觸發(fā)delete觸發(fā)器）
DELETE FROM tb_user WHERE id = 26;
-- 查看日志
SELECT * FROM user_logs;

4. 使用注意事項(xiàng)

• 觸發(fā)時(shí)機(jī)：BEFORE 觸發(fā)器可以修改 NEW 數(shù)據(jù)，也可以用于數(shù)據(jù)校驗(yàn)；AFTER 觸發(fā)器不能修改數(shù)據(jù)，適合做日志記錄、級(jí)聯(lián)操作。
• OLD/NEW 關(guān)鍵字：
  • INSERT 觸發(fā)器中，只有 NEW 可用；
  • DELETE 觸發(fā)器中，只有 OLD 可用；
  • UPDATE 觸發(fā)器中，OLD 和 NEW 都可用。
• 性能影響：觸發(fā)器是行級(jí)觸發(fā)，批量操作時(shí)會(huì)逐行觸發(fā)，可能影響性能；復(fù)雜邏輯建議放在應(yīng)用層。
• 循環(huán)觸發(fā)風(fēng)險(xiǎn)：避免在觸發(fā)器中對(duì)同一張表執(zhí)行增刪改操作，否則可能導(dǎo)致死循環(huán)。

5. 常見應(yīng)用場(chǎng)景

• 數(shù)據(jù)審計(jì)：記錄表中數(shù)據(jù)的所有變更操作（如用戶日志、訂單日志）；
• 數(shù)據(jù)校驗(yàn)：BEFORE INSERT/UPDATE 觸發(fā)器中校驗(yàn)數(shù)據(jù)合法性（如年齡不能為負(fù)、手機(jī)號(hào)格式）；
• 級(jí)聯(lián)更新：主表數(shù)據(jù)修改/刪除時(shí)，自動(dòng)同步更新關(guān)聯(lián)表數(shù)據(jù)；
• 數(shù)據(jù)同步：實(shí)時(shí)將一張表的數(shù)據(jù)同步到另一張表（如業(yè)務(wù)表到統(tǒng)計(jì)表）。

存儲(chǔ)過程、存儲(chǔ)函數(shù)、觸發(fā)器盡量不要用，阿里范式不允許

開發(fā)中為什么盡量不用存儲(chǔ)過程、存儲(chǔ)函數(shù)、觸發(fā)器（面試必背 + 通俗易懂整理）

一、統(tǒng)一核心原因總覽

• 業(yè)務(wù)邏輯侵入數(shù)據(jù)庫，把代碼寫在數(shù)據(jù)庫里，違背前后端/應(yīng)用層控制業(yè)務(wù)的設(shè)計(jì)思想。
• 調(diào)試難、維護(hù)難、排錯(cuò)極麻煩。
• 可移植性極差，換數(shù)據(jù)庫基本全要重寫。
• 占用數(shù)據(jù)庫性能，把計(jì)算、邏輯壓力丟給DB，容易拖垮庫。
• 版本管理困難，無法像代碼一樣Git版本控制、回滾。
• 分布式、微服務(wù)架構(gòu)下完全不適用。

二、存儲(chǔ)過程 為什么不用

1. 業(yè)務(wù)邏輯下沉到數(shù)據(jù)庫

業(yè)務(wù)邏輯本該寫在 Java/PHP/Go 后端，存儲(chǔ)過程把大量邏輯寫死在DB里，業(yè)務(wù)分散、邏輯混亂，新人接手看不懂。

2. 調(diào)試極其困難

沒有斷點(diǎn)調(diào)試、沒有日志跟蹤，出問題只能靠猜、靠打印SQL，復(fù)雜流程排錯(cuò)成本極高。

3. 版本控制難

存儲(chǔ)過程存在數(shù)據(jù)庫里，不能Git管理，無法做版本迭代、快速回滾，上線、灰度都很麻煩。

4. 可移植性差

MySQL、Oracle、SQL Server 存儲(chǔ)過程語法完全不一樣，一旦換數(shù)據(jù)庫，全部重寫。

5. 加重?cái)?shù)據(jù)庫壓力

復(fù)雜循環(huán)、計(jì)算、業(yè)務(wù)判斷都在DB執(zhí)行，DB本應(yīng)只做存儲(chǔ)和簡單查詢，不適合承載業(yè)務(wù)計(jì)算邏輯，容易造成CPU、連接數(shù)打滿。

6. 微服務(wù)分布式不兼容

微服務(wù)提倡業(yè)務(wù)在服務(wù)層、數(shù)據(jù)只在DB，存儲(chǔ)過程無法跨服務(wù)調(diào)用，也不方便做分布式事務(wù)、限流、熔斷。

7. 并發(fā)與鎖風(fēng)險(xiǎn)

存儲(chǔ)過程里多SQL默認(rèn)在一個(gè)事務(wù)，容易長事務(wù)、鎖等待、死鎖，線上隱患大。

三、存儲(chǔ)函數(shù) 為什么不用

• 不能寫復(fù)雜業(yè)務(wù)，功能有限，不如后端函數(shù)靈活。
• 無法做復(fù)雜邏輯、循環(huán)、外部調(diào)用。
• 在SQL中調(diào)用容易被濫用，嵌套在查詢里會(huì)隱形增加查詢開銷，優(yōu)化器難以預(yù)估成本。
• 同樣難調(diào)試、難版本管理、難遷移。
• 禁止在函數(shù)里做增刪改，容易引發(fā)主從延遲、數(shù)據(jù)不一致。

一句話：能用后端代碼實(shí)現(xiàn)的計(jì)算，絕不寫存儲(chǔ)函數(shù)。

四、觸發(fā)器 為什么堅(jiān)決少用/不用

1. 隱式執(zhí)行，邏輯“隱身”

觸發(fā)器是自動(dòng)偷偷執(zhí)行，開發(fā)者寫 insert/update/delete 時(shí)完全感知不到還有額外邏輯在跑，出了問題根本想不到是觸發(fā)器導(dǎo)致的。

2. 排錯(cuò)極難

數(shù)據(jù)莫名其妙變了、莫名多了日志、莫名被修改，排查半天最后發(fā)現(xiàn)是觸發(fā)器偷偷觸發(fā)，隱蔽性太強(qiáng)，坑很多。

3. 性能損耗大

觸發(fā)器是行級(jí)觸發(fā)，批量插入1萬條，就觸發(fā)1萬次，嚴(yán)重拖慢批量操作性能。

4. 容易觸發(fā)循環(huán)嵌套死循環(huán)

A表觸發(fā)器改B表，B表觸發(fā)器又改A表，連環(huán)觸發(fā)、死循環(huán)、鎖表，線上事故高危。

5. 主從同步容易出問題

觸發(fā)器在主庫執(zhí)行，從庫復(fù)制可能重復(fù)觸發(fā)，導(dǎo)致數(shù)據(jù)重復(fù)、不一致。

6. 不利于數(shù)據(jù)遷移和分庫分表

分表、分庫、數(shù)據(jù)遷移時(shí)，觸發(fā)器邏輯容易被遺漏，導(dǎo)致數(shù)據(jù)行為不一致。

五、什么時(shí)候勉強(qiáng)可以用（僅老舊項(xiàng)目）

只有以下極少數(shù)場(chǎng)景可容忍：

• 老舊單體項(xiàng)目無法改造；
• 純數(shù)據(jù)統(tǒng)計(jì)、報(bào)表固化邏輯；
• 僅做簡單日志記錄、數(shù)據(jù)校驗(yàn)，邏輯極簡不復(fù)雜。

新項(xiàng)目、微服務(wù)、分布式項(xiàng)目：一律禁止使用。

六、面試極簡背誦版

• 存儲(chǔ)過程：業(yè)務(wù)下沉、難調(diào)試、難版本控制、不可移植、壓垮數(shù)據(jù)庫、微服務(wù)不適用。
• 存儲(chǔ)函數(shù)：功能受限、隱藏開銷、無法復(fù)雜業(yè)務(wù)、難維護(hù)。
• 觸發(fā)器：隱式執(zhí)行邏輯隱蔽、排錯(cuò)難、批量性能差、易循環(huán)觸發(fā)、主從數(shù)據(jù)不一致。
• 統(tǒng)一原則：業(yè)務(wù)邏輯放應(yīng)用層，數(shù)據(jù)庫只負(fù)責(zé)存數(shù)據(jù)、查數(shù)據(jù)。

五、鎖

一、鎖的核心概念

鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程/線程并發(fā)訪問同一資源的機(jī)制。

在數(shù)據(jù)庫中，除了CPU、內(nèi)存、I/O等計(jì)算資源的爭用，數(shù)據(jù)本身也是一種多用戶共享資源。鎖的核心目標(biāo)是：

• 保證并發(fā)訪問下的數(shù)據(jù)一致性、有效性；
• 鎖沖突是影響數(shù)據(jù)庫并發(fā)性能的關(guān)鍵因素。

二、MySQL鎖的粒度分類

MySQL的鎖按粒度從大到小分為三類：

三、全局鎖（Global Lock）

1. 介紹

全局鎖會(huì)對(duì)整個(gè)數(shù)據(jù)庫實(shí)例加鎖，加鎖后實(shí)例進(jìn)入只讀狀態(tài)，以下操作都會(huì)被阻塞：

• DML寫語句（INSERT/UPDATE/DELETE）
• DDL語句（CREATE/ALTER/DROP TABLE）
• 已更新事務(wù)的提交語句

2. 典型場(chǎng)景：全庫邏輯備份

當(dāng)你執(zhí)行 mysqldump 全庫備份時(shí)，如果不加特殊參數(shù)，就會(huì)觸發(fā)全局鎖。

• 目的：獲取一致性視圖，保證備份數(shù)據(jù)的完整性；
• 問題：備份期間如果有業(yè)務(wù)寫入，會(huì)導(dǎo)致備份前后數(shù)據(jù)不一致（如訂單表備份到一半，庫存表又被更新）。

3. 核心命令

-- 加全局讀鎖（只讀鎖）
FLUSH TABLES WITH READ LOCK;
-- 釋放全局鎖
UNLOCK TABLES;

4. 操作演示

# 備份命令（傳統(tǒng)方式，會(huì)加全局鎖）
mysqldump -uroot -p1234 itcast > itcast.sql

• 加鎖后：SELECT 查詢可以正常執(zhí)行，INSERT/UPDATE/DELETE 會(huì)被阻塞；
• 解鎖后：寫入操作恢復(fù)執(zhí)行。

5. 全局鎖的問題與優(yōu)化

問題

• 主庫備份：備份期間無法執(zhí)行更新操作，業(yè)務(wù)基本停擺；
• 從庫備份：備份期間從庫無法同步主庫的binlog，導(dǎo)致主從延遲。

優(yōu)化方案：InnoDB 不加鎖備份

在 InnoDB 引擎中，使用 --single-transaction 參數(shù)實(shí)現(xiàn)不加鎖的一致性備份：

mysqldump --single-transaction -uroot -p123456 itcast > itcast.sql

原理：利用 InnoDB 的事務(wù)隔離級(jí)別，在一個(gè)事務(wù)內(nèi)完成一致性快照備份，全程不影響業(yè)務(wù)寫入。

四、表級(jí)鎖

一、表級(jí)鎖概述

表級(jí)鎖是MySQL中粒度最大的鎖，每次操作直接鎖定整張表：

• 優(yōu)點(diǎn)：實(shí)現(xiàn)簡單，無死鎖；
• 缺點(diǎn)：鎖沖突概率高，并發(fā)度最低；
• 適用引擎：MyISAM、InnoDB、BDB等。

表級(jí)鎖主要分為三類：

• 表鎖（顯式表共享/排他鎖）
• 元數(shù)據(jù)鎖（MDL）
• 意向鎖（InnoDB 自動(dòng)維護(hù)）

二、表鎖（顯式表級(jí)鎖）

1. 分類與兼容性

2. 核心語法

-- 1. 加表鎖
LOCK TABLES 表名 READ;    -- 加共享讀鎖
LOCK TABLES 表名 WRITE;   -- 加獨(dú)占寫鎖
-- 2. 釋放鎖
UNLOCK TABLES;            -- 手動(dòng)釋放
-- 客戶端斷開連接時(shí)，也會(huì)自動(dòng)釋放鎖

3. 工作機(jī)制圖解

• 讀鎖：其他客戶端可以執(zhí)行SELECT，但INSERT/UPDATE/DELETE會(huì)被阻塞；
• 寫鎖：其他客戶端的SELECT和寫操作都會(huì)被阻塞，只有持有鎖的會(huì)話能讀寫。

三、元數(shù)據(jù)鎖（MDL）

1. 核心概念

元數(shù)據(jù)鎖（Meta Data Lock，MDL）是MySQL 5.5+自動(dòng)維護(hù)的鎖，無需顯式使用，在訪問表時(shí)自動(dòng)加鎖：

• 作用：維護(hù)表元數(shù)據(jù)的一致性，防止DML與DDL沖突；
• 核心規(guī)則：表上有活動(dòng)事務(wù)時(shí)，不允許執(zhí)行元數(shù)據(jù)寫操作（如ALTER TABLE）。

2. 鎖類型與對(duì)應(yīng)SQL

3. 查看元數(shù)據(jù)鎖

SELECT object_type,object_schema,object_name,lock_type,lock_duration 
FROM performance_schema.metadata_locks;

四、意向鎖（InnoDB 特有）

1. 核心作用

為了解決行鎖與表鎖的沖突檢查效率問題，InnoDB引入了意向鎖：

• 當(dāng)事務(wù)給某行加鎖時(shí)，會(huì)先在表上加對(duì)應(yīng)的意向鎖；
• 后續(xù)表鎖請(qǐng)求只需檢查表級(jí)意向鎖，無需遍歷所有行鎖，大幅提升檢查效率。

2. 意向鎖分類與兼容性

3. 查看意向鎖與行鎖

SELECT object_schema,object_name,index_name,lock_type,lock_mode,lock_data 
FROM performance_schema.data_locks;

五、表級(jí)鎖總結(jié)

• 表鎖：手動(dòng)加鎖，讀鎖共享、寫鎖獨(dú)占，并發(fā)度低；
• MDL鎖：系統(tǒng)自動(dòng)維護(hù)，防止DML與DDL沖突，是線上DDL阻塞的常見原因；
• 意向鎖：InnoDB自動(dòng)維護(hù)，用于優(yōu)化表鎖與行鎖的沖突檢查，不影響業(yè)務(wù)并發(fā)。

五、行級(jí)鎖

1.行級(jí)鎖概述

行級(jí)鎖是 InnoDB 引擎獨(dú)有的鎖機(jī)制，每次操作僅鎖定對(duì)應(yīng)的行數(shù)據(jù)：

• 優(yōu)點(diǎn)：粒度最小，鎖沖突概率最低，并發(fā)度最高；
• 核心實(shí)現(xiàn)：行鎖是對(duì)索引項(xiàng)加鎖，而非直接對(duì)記錄加鎖；
• 三大類型：行鎖（Record Lock）、間隙鎖（Gap Lock）、臨鍵鎖（Next-Key Lock）。

2.行鎖（Record Lock）

1. 分類與兼容性

行鎖分為共享鎖（S鎖）和排他鎖（X鎖），兼容性如下：

2. 觸發(fā)場(chǎng)景與SQL示例

3. 示例1：共享鎖（S鎖）

-- 事務(wù)A
BEGIN;
SELECT * FROM tb_user WHERE id = 1 LOCK IN SHARE MODE; -- 加S鎖

-- 事務(wù)B
BEGIN;
SELECT * FROM tb_user WHERE id = 1 LOCK IN SHARE MODE; -- 可以加S鎖（兼容）
UPDATE tb_user SET name = 'test' WHERE id = 1; -- 加X鎖，被阻塞（沖突）

4. 示例2：排他鎖（X鎖）

-- 事務(wù)A
BEGIN;
UPDATE tb_user SET name = 'test' WHERE id = 1; -- 自動(dòng)加X鎖

-- 事務(wù)B
BEGIN;
SELECT * FROM tb_user WHERE id = 1 FOR UPDATE; -- 加X鎖，被阻塞（沖突）
SELECT * FROM tb_user WHERE id = 1 LOCK IN SHARE MODE; -- 加S鎖，被阻塞（沖突）

5. 關(guān)鍵注意點(diǎn)

• 無索引會(huì)升級(jí)為表鎖：不通過索引條件檢索數(shù)據(jù)時(shí)，InnoDB會(huì)對(duì)表中所有記錄加鎖，相當(dāng)于表鎖；
• 唯一索引等值匹配會(huì)優(yōu)化為行鎖：對(duì)唯一索引的存在記錄進(jìn)行等值查詢，僅鎖定該記錄。

3.間隙鎖（Gap Lock）

1. 核心概念

間隙鎖鎖定索引記錄的間隙（不含記錄本身），目的是防止其他事務(wù)在該間隙插入數(shù)據(jù)，從而避免幻讀，僅在RR隔離級(jí)別下生效。

2. 觸發(fā)場(chǎng)景與示例

示例1：不存在記錄的等值查詢（唯一索引）

-- 表中id為10、20、30，無id=15的記錄
BEGIN;
SELECT * FROM tb_user WHERE id = 15 FOR UPDATE; 
-- 觸發(fā)間隙鎖，鎖定(10,20)之間的間隙，防止插入id=15的數(shù)據(jù)

示例2：普通索引等值查詢的邊界場(chǎng)景

-- 表中age為18、20、22，查詢age=25（不存在）
BEGIN;
SELECT * FROM tb_user WHERE age = 25 FOR UPDATE; 
-- 觸發(fā)間隙鎖，鎖定(22, +∞)之間的間隙

3. 關(guān)鍵注意點(diǎn)

• 間隙鎖的唯一目的是防止其他事務(wù)插入間隙數(shù)據(jù)；
• 間隙鎖可以共存，不同事務(wù)對(duì)同一間隙加間隙鎖不會(huì)互相阻塞。

4.臨鍵鎖（Next-Key Lock）

1. 核心概念

臨鍵鎖是行鎖 + 間隙鎖的組合，同時(shí)鎖定數(shù)據(jù)記錄及其前面的間隙，是InnoDB在RR隔離級(jí)別下默認(rèn)的鎖機(jī)制，用于徹底防止幻讀。

2. 觸發(fā)場(chǎng)景與示例

示例1：范圍查詢（唯一索引）

-- 表中id為10、20、30、40
BEGIN;
SELECT * FROM tb_user WHERE id > 20 AND id < 30 FOR UPDATE; 
-- 觸發(fā)臨鍵鎖，鎖定：
-- 行鎖：id=30（不滿足條件的第一個(gè)值）
-- 間隙鎖：(20,30)和(30,40)之間的間隙

示例2：普通索引的范圍查詢

-- 表中age為18、20、22、25
BEGIN;
SELECT * FROM tb_user WHERE age >= 20 FOR UPDATE; 
-- 觸發(fā)臨鍵鎖，鎖定：
-- 行鎖：age=20、22、25
-- 間隙鎖：(18,20)、(20,22)、(22,25)、(25, +∞)

3. 鎖的優(yōu)化規(guī)則

• 唯一索引等值匹配存在記錄：優(yōu)化為行鎖；
• 唯一索引等值匹配不存在記錄：優(yōu)化為間隙鎖；
• 普通索引等值查詢邊界不滿足：臨鍵鎖退化為間隙鎖；
• 范圍查詢（唯一/普通索引）：默認(rèn)使用臨鍵鎖。

5.查看鎖信息

通過以下SQL可以查看意向鎖、行鎖、間隙鎖的情況：

SELECT object_schema,object_name,index_name,lock_type,lock_mode,lock_data 
FROM performance_schema.data_locks;

6.行級(jí)鎖總結(jié)

六、死鎖（產(chǎn)生原因+解決辦法）

1.死鎖概念

死鎖：兩個(gè)或多個(gè)事務(wù)，互相持有對(duì)方需要的鎖，又都不釋放自己的鎖，無限等待，誰也執(zhí)行不下去。

InnoDB 會(huì)自動(dòng)檢測(cè)死鎖，主動(dòng)回滾代價(jià)更小的一個(gè)事務(wù)，讓另一個(gè)正常執(zhí)行。

2.死鎖產(chǎn)生的四個(gè)必要條件（面試必背）

• 互斥條件：鎖同一資源不能同時(shí)占用；
• 請(qǐng)求保持：事務(wù)已持有鎖，還去申請(qǐng)新鎖；
• 不可剝奪：鎖不能被強(qiáng)行搶走，只能自己釋放；
• 循環(huán)等待：事務(wù)間形成循環(huán)等待鎖的環(huán)路。

四個(gè)條件同時(shí)滿足，必然死鎖。

3.死鎖產(chǎn)生典型場(chǎng)景 + 完整示例

場(chǎng)景1：兩個(gè)事務(wù)加鎖順序相反（最常見）

表 tb_user 有主鍵 id。

事務(wù)A

BEGIN;
UPDATE tb_user SET name='a' WHERE id=1;  -- 持有id=1行鎖
UPDATE tb_user SET name='b' WHERE id=2;  -- 等待事務(wù)B的id=2鎖

事務(wù)B

BEGIN;
UPDATE tb_user SET name='b' WHERE id=2;  -- 持有id=2行鎖
UPDATE tb_user SET name='a' WHERE id=1;  -- 等待事務(wù)A的id=1鎖

形成循環(huán)等待 → 直接死鎖。

場(chǎng)景2：索引失效，行鎖升級(jí)為表鎖引發(fā)死鎖

字段無索引，更新變成表級(jí)排他鎖，互相阻塞產(chǎn)生死鎖。

事務(wù)A

BEGIN;
UPDATE tb_user SET age=20 WHERE name='張三'; -- name無索引，鎖整張表

事務(wù)B

BEGIN;
UPDATE tb_user SET age=25 WHERE name='李四'; -- 同樣無索引，也鎖整張表

互相等待對(duì)方表鎖，形成死鎖。

場(chǎng)景3：RR隔離級(jí)別下 間隙鎖 + 行鎖 互相等待

普通索引范圍查詢產(chǎn)生臨鍵鎖/間隙鎖，間隙之間互相占用，引發(fā)死鎖。

4.如何查看死鎖日志

-- 查看最近一次死鎖詳情
SHOW ENGINE INNODB STATUS;

在輸出信息中找到 LATEST DETECTED DEADLOCK，可看到：

• 哪兩個(gè)事務(wù)
• 各自持有什么鎖、等待什么鎖
• 最終回滾了哪個(gè)事務(wù)

5.死鎖解決與規(guī)避方案

1. 統(tǒng)一SQL加鎖順序（最有效）

所有業(yè)務(wù)事務(wù)，必須按相同順序訪問表、訪問行。

示例：

永遠(yuǎn)先操作 id=1，再操作 id=2，所有服務(wù)都遵守，從根源打破循環(huán)等待。

2. 避免事務(wù)過大、事務(wù)過長

• 事務(wù)里不要放無關(guān)業(yè)務(wù)邏輯；
• 盡量小事務(wù)、快提交，持有鎖時(shí)間越短，死鎖概率越低。

3. 確保條件字段有索引

更新/刪除條件一定要走索引，避免行鎖升級(jí)為表鎖，大幅減少鎖范圍和沖突。

4. 業(yè)務(wù)層面加重試機(jī)制

捕獲死鎖異常后，間隔短暫時(shí)間自動(dòng)重試，線上常用方案。

5. 盡量不用范圍查詢 FOR UPDATE

范圍查詢?nèi)菀子|發(fā)臨鍵鎖、間隙鎖，鎖范圍放大，極易誘發(fā)死鎖；

能用等值查詢就不用范圍。

6. 調(diào)低隔離級(jí)別（可選）

把隔離級(jí)別從 RR 降到 RC：

• 取消間隙鎖、臨鍵鎖；
• 大幅減少死鎖；
• 代價(jià)：可能出現(xiàn)幻讀，業(yè)務(wù)能接受就可以用。

7. 避免同一事務(wù)重復(fù)加鎖、交叉更新

不要在一個(gè)事務(wù)內(nèi)多次更新同一張表不同行，減少鎖競爭。

6.極簡背誦

• 死鎖四個(gè)條件：互斥、請(qǐng)求保持、不可剝奪、循環(huán)等待。
• 最常見原因：事務(wù)加鎖順序不一致、無索引升級(jí)表鎖、間隙鎖沖突。
• 解決辦法：
  • 統(tǒng)一訪問順序；
  • 小事務(wù)快提交；
  • 保證索引有效；
  • 業(yè)務(wù)增加重試；
• 必要時(shí)降級(jí)為RC隔離級(jí)別。

六、InnoDB引擎

一、邏輯存儲(chǔ)結(jié)構(gòu)

1.整體層級(jí)關(guān)系

InnoDB 的數(shù)據(jù)是按表空間（Tablespace）→ 段（Segment）→ 區(qū)（Extent）→ 頁（Page）→ 行（Row）的層級(jí)組織。

2.各層級(jí)詳解

3.層級(jí)關(guān)系與MVCC關(guān)聯(lián)

• 數(shù)據(jù)組織：B+樹索引的葉子節(jié)點(diǎn)就是數(shù)據(jù)段，由多個(gè)區(qū)組成，每個(gè)區(qū)包含64個(gè)頁，每個(gè)頁存儲(chǔ)多行數(shù)據(jù)；
• MVCC支持：行中的 trx_id 和 roll_pointer，配合回滾段的 undo log，實(shí)現(xiàn)了事務(wù)的一致性視圖和多版本讀取。

4.面試速記

• 層級(jí)順序：表空間 → 段 → 區(qū) → 頁 → 行；
• 核心參數(shù)：區(qū)1MB、頁16KB，1個(gè)區(qū)=64個(gè)頁；
• 關(guān)鍵隱藏列：trx_id（事務(wù)ID）、roll_pointer（舊版本指針），用于MVCC；
• 段的分類：數(shù)據(jù)段（葉子節(jié)點(diǎn)）、索引段（非葉子節(jié)點(diǎn)）、回滾段（undo log）。

二、架構(gòu)篇

1.整體架構(gòu)概覽

InnoDB 架構(gòu)分為三大核心部分：

• 內(nèi)存結(jié)構(gòu)（In-Memory Structures）：緩存數(shù)據(jù)、索引和日志，減少磁盤IO
• 磁盤結(jié)構(gòu)（On-Disk Structures）：持久化存儲(chǔ)數(shù)據(jù)、日志和系統(tǒng)信息
• 后臺(tái)線程（Background Threads）：異步處理臟頁刷新、日志同步、資源回收等任務(wù)

2.內(nèi)存結(jié)構(gòu)詳解

1. Buffer Pool（緩沖池）

• 核心作用：主內(nèi)存中緩存磁盤數(shù)據(jù)頁的區(qū)域，是 InnoDB 性能的核心
• 工作機(jī)制：
  • 增刪改查優(yōu)先操作緩沖池?cái)?shù)據(jù)，減少磁盤IO
  • 緩沖池?zé)o數(shù)據(jù)時(shí)，從磁盤加載并緩存
  • 臟頁按一定頻率異步刷新到磁盤
• 頁類型：
  • free page：空閑未使用的頁
  • clean page：已使用且數(shù)據(jù)未修改的頁
  • dirty page：已使用且數(shù)據(jù)已修改，與磁盤數(shù)據(jù)不一致的頁

2. Change Buffer（更改緩沖區(qū)）

• 作用對(duì)象：僅針對(duì)非唯一二級(jí)索引頁
• 工作機(jī)制：DML操作時(shí)，若索引頁不在緩沖池中，不直接操作磁盤，而是先存到Change Buffer；后續(xù)數(shù)據(jù)被讀取時(shí)，再合并到緩沖池并刷新磁盤
• 核心意義：減少二級(jí)索引的隨機(jī)磁盤IO，大幅提升寫入性能

3. Adaptive Hash Index（自適應(yīng)哈希索引）

• 作用：優(yōu)化緩沖池?cái)?shù)據(jù)的查詢速度
• 機(jī)制：InnoDB自動(dòng)監(jiān)控索引頁查詢，當(dāng)哈希索引能提升性能時(shí)，自動(dòng)建立哈希索引，無需人工干預(yù)
• 控制參數(shù)：adaptive_hash_index（默認(rèn)開啟）

4. Log Buffer（日志緩沖區(qū)）

3.磁盤結(jié)構(gòu)詳解

1. 表空間（Tablespaces）

2. Doublewrite Buffer Files（雙寫緩沖區(qū)）

3. Redo Log（重做日志）

• 作用：實(shí)現(xiàn)事務(wù)持久性，崩潰恢復(fù)時(shí)重放修改
• 機(jī)制：事務(wù)提交后將修改寫入redo log，臟頁異步刷盤，刷盤失敗時(shí)可通過redo log恢復(fù)
• 關(guān)鍵文件：ib_logfile0、ib_logfile1，以循環(huán)方式寫入

4.后臺(tái)線程

5.架構(gòu)關(guān)鍵總結(jié)

• 內(nèi)存優(yōu)先：所有讀寫優(yōu)先操作緩沖池，日志暫存緩沖區(qū)，再異步刷盤
• Change Buffer優(yōu)化：非唯一二級(jí)索引寫入性能的關(guān)鍵
• Redo Log保障：事務(wù)持久性的核心，崩潰恢復(fù)的基礎(chǔ)
• 后臺(tái)線程分工：臟頁刷新、日志同步、資源回收全異步處理，不阻塞用戶請(qǐng)求

三、事務(wù)原理

1.事務(wù)基礎(chǔ)概念

事務(wù)是一組不可分割的操作集合，作為一個(gè)整體向系統(tǒng)提交或撤銷請(qǐng)求：

• 要么全部操作同時(shí)成功，要么全部同時(shí)失敗；
• 是數(shù)據(jù)庫保證數(shù)據(jù)一致性的核心機(jī)制。

2.事務(wù)四大特性（ACID）

3.Redo Log（重做日志）：實(shí)現(xiàn)持久性

1. 核心作用

記錄事務(wù)提交時(shí)數(shù)據(jù)頁的物理修改，用于崩潰恢復(fù)，保障事務(wù)持久性。

2. 工作機(jī)制（WAL 預(yù)寫日志）

• WAL原則：事務(wù)提交前，先寫redo log，再異步刷臟頁到磁盤；
• 結(jié)構(gòu)分為兩部分：
  • 內(nèi)存中：redo log buffer（日志緩沖區(qū)）
  • 磁盤中：ib_logfile0/ib_logfile1（重做日志文件，循環(huán)寫入）
• 流程：事務(wù)提交 → 修改寫入redo log buffer → 刷盤到redo log文件 → 臟頁后續(xù)異步刷入數(shù)據(jù)文件；若刷盤失敗，可通過redo log恢復(fù)數(shù)據(jù)。

4.Undo Log（回滾日志）：實(shí)現(xiàn)原子性

1. 核心作用

記錄數(shù)據(jù)修改前的狀態(tài)，提供事務(wù)回滾和MVCC多版本并發(fā)控制。

2. 關(guān)鍵特點(diǎn)

• 屬于邏輯日志：不是物理頁修改，而是反向操作記錄（如delete對(duì)應(yīng)insert，update對(duì)應(yīng)反向update）；
• 回滾時(shí)，可通過undo log中的反向操作恢復(fù)數(shù)據(jù)；
• 事務(wù)提交后不會(huì)立即刪除undo log，因?yàn)榭赡苓€用于MVCC；
• 存儲(chǔ)在回滾段（rollback segment）中，每個(gè)回滾段包含1024個(gè)undo log段。

四、MVCC

1.MVCC基礎(chǔ)概念

MVCC（Multi-Version Concurrency Control，多版本并發(fā)控制），是 InnoDB 實(shí)現(xiàn)讀寫不阻塞的核心機(jī)制：

• 維護(hù)數(shù)據(jù)的多個(gè)版本，使讀寫操作互不沖突；
• 快照讀（普通SELECT）不加鎖，大幅提升并發(fā)性能；
• 核心依賴：隱藏字段 + undo log版本鏈 + ReadView。

2.兩種讀模式

1. 當(dāng)前讀

讀取記錄的最新版本，并對(duì)記錄加鎖，保證其他事務(wù)無法修改。

• 觸發(fā)場(chǎng)景：
  • SELECT ... LOCK IN SHARE MODE（共享鎖）
  • SELECT ... FOR UPDATE（排他鎖）
  • INSERT / UPDATE / DELETE（自動(dòng)加排他鎖）

2. 快照讀

讀取記錄的可見版本（可能是歷史數(shù)據(jù)），不加鎖，是非阻塞讀。

• 觸發(fā)場(chǎng)景：普通 SELECT（無鎖）；
• 不同隔離級(jí)別生成快照時(shí)機(jī)不同：
  • READ COMMITTED：每次SELECT都生成新快照；
  • REPEATABLE READ：事務(wù)中第一次SELECT生成快照，后續(xù)復(fù)用；
  • SERIALIZABLE：快照讀退化為當(dāng)前讀。

3.MVCC實(shí)現(xiàn)三大支柱

1. 記錄中的隱藏字段

每個(gè)InnoDB記錄都包含三個(gè)隱藏字段：

2. undo log版本鏈

• 每次修改記錄時(shí)，會(huì)生成舊版本數(shù)據(jù)，存入undo log；
• 通過DB_ROLL_PTR將多個(gè)版本串聯(lián)成一條版本鏈，鏈表頭是最新版本，尾部是最早版本；
• 事務(wù)提交后，undo log不會(huì)立即刪除（因?yàn)榭煺兆x可能還需要），只有當(dāng)沒有任何事務(wù)引用該版本時(shí)，才會(huì)被Purge線程回收。

3. ReadView（讀視圖）

ReadView是快照讀判斷版本可見性的依據(jù)，記錄當(dāng)前系統(tǒng)中活躍的事務(wù)（未提交）ID集合，包含四個(gè)核心字段：

4.版本可見性判斷規(guī)則

讀取記錄時(shí)，需遍歷版本鏈，直到找到符合以下條件的版本：

• trx_id == creator_trx_id：數(shù)據(jù)由當(dāng)前事務(wù)修改，可見；
• trx_id < min_trx_id：事務(wù)已提交，可見；
• trx_id > max_trx_id：事務(wù)在ReadView生成后開啟，不可見；
• min_trx_id <= trx_id <= max_trx_id：需判斷trx_id是否在m_ids中：
  • 不在集合中：事務(wù)已提交，可見；
  • 在集合中：事務(wù)未提交，不可見。

5.RC與RR隔離級(jí)別下的ReadView差異

6.MVCC總結(jié)

• 核心目的：實(shí)現(xiàn)讀寫不阻塞，提升并發(fā)性能；
• 三大支柱：隱藏字段記錄事務(wù)ID、undo log維護(hù)版本鏈、ReadView判斷版本可見性；
• 隔離級(jí)別差異：ReadView生成時(shí)機(jī)不同，決定了RC和RR的可見性規(guī)則差異。

七、MySQL管理

一、MySQL自帶系統(tǒng)數(shù)據(jù)庫

安裝MySQL后，會(huì)自動(dòng)創(chuàng)建4個(gè)系統(tǒng)數(shù)據(jù)庫，作用如下：

二、MySQL常用客戶端工具

1.mysql：核心客戶端連接工具

用于連接MySQL服務(wù)器、執(zhí)行SQL語句。

• 基礎(chǔ)語法：mysql [options] [database]
• 常用選項(xiàng)：
  • -u / --user=name：指定登錄用戶名
  • -p / --password[=name]：指定登錄密碼（交互輸入更安全）
  • -h / --host=name：指定服務(wù)器IP或域名
  • -P / --port=port：指定連接端口（默認(rèn)3306）
  • -e / --execute=name：執(zhí)行SQL語句并直接退出（適合批處理腳本）
• 示例：

# 連接數(shù)據(jù)庫并執(zhí)行查詢后退出
mysql -uroot -p123456 db01 -e "SELECT * FROM stu;"

2.mysqladmin：服務(wù)器管理工具

用于執(zhí)行服務(wù)器配置檢查、狀態(tài)監(jiān)控、數(shù)據(jù)庫管理等操作。

• 基礎(chǔ)語法：mysqladmin [options] command
• 常用功能：創(chuàng)建/刪除數(shù)據(jù)庫、修改密碼、刷新權(quán)限、查看服務(wù)器狀態(tài)、關(guān)閉服務(wù)器等
• 示例：

# 刪除test01數(shù)據(jù)庫
mysqladmin -uroot -p123456 drop 'test01';
# 查看MySQL版本信息
mysqladmin -uroot -p123456 version;

3.mysqlbinlog：二進(jìn)制日志管理工具

用于解析二進(jìn)制日志文件，查看數(shù)據(jù)修改記錄，支持按時(shí)間、位置過濾日志。

• 基礎(chǔ)語法：mysqlbinlog [options] log-files1 log-files2 ...
• 常用選項(xiàng)：
  • -d / --database=name：僅顯示指定數(shù)據(jù)庫的操作日志
  • -o / --offset=#：忽略日志開頭的前N條命令
  • -r / --result-file=name：將解析后的日志輸出到指定文件
  • --start-datetime / --stop-datetime：按時(shí)間范圍過濾日志
  • --start-position / --stop-position：按日志位置過濾日志

4.mysqlshow：數(shù)據(jù)庫對(duì)象查看工具

快速查詢數(shù)據(jù)庫、表、字段、索引等元數(shù)據(jù)信息。

• 基礎(chǔ)語法：mysqlshow [options] [db_name [table_name [col_name]]]
• 常用選項(xiàng)：
  • --count：顯示數(shù)據(jù)庫/表的統(tǒng)計(jì)信息（表數(shù)量、記錄數(shù)等）
  • -i：顯示指定數(shù)據(jù)庫或表的狀態(tài)信息
• 示例：

# 查詢test庫中每個(gè)表的字段數(shù)和行數(shù)
mysqlshow -uroot -p2143 test --count;

5.mysqldump：數(shù)據(jù)庫備份與遷移工具

用于備份數(shù)據(jù)庫，生成包含建表語句和數(shù)據(jù)插入語句的SQL文件，支持跨數(shù)據(jù)庫遷移。

基礎(chǔ)語法：

# 備份單個(gè)數(shù)據(jù)庫
mysqldump [options] db_name [tables]
# 備份多個(gè)數(shù)據(jù)庫
mysqldump [options] --database/-B db1 [db2 db3...]
# 備份所有數(shù)據(jù)庫
mysqldump [options] --all-databases/-A

常用選項(xiàng)：

• --add-drop-database：在創(chuàng)建數(shù)據(jù)庫前添加DROP DATABASE語句
• --add-drop-table：在創(chuàng)建表前添加DROP TABLE語句（默認(rèn)開啟）
• -n / --no-create-db：不包含數(shù)據(jù)庫創(chuàng)建語句
• -t / --no-create-info：不包含表創(chuàng)建語句
• -d / --no-data：僅備份表結(jié)構(gòu)，不包含數(shù)據(jù)
• -T / --tab=name：分別生成.sql（表結(jié)構(gòu)）和.txt（數(shù)據(jù)）文件

6.mysqlimport/source：數(shù)據(jù)導(dǎo)入工具

• mysqlimport：用于導(dǎo)入mysqldump -T導(dǎo)出的文本數(shù)據(jù)文件。
  • 語法：mysqlimport [options] db_name textfile1 [textfile2...]
  • 示例：mysqlimport -uroot -p2143 test /tmp/city.txt
• source：MySQL客戶端內(nèi)的命令，用于導(dǎo)入SQL文件。
  • 語法：source /root/xxxx.sql

三、工具使用核心場(chǎng)景速記

到此這篇關(guān)于深度解析MySQL存儲(chǔ)引擎與索引的文章就介紹到這了,更多相關(guān)mysql存儲(chǔ)引擎與索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！