Mysql存儲引擎

InnoDB?（MySQL 5.5 及以后版本中的默認存儲引擎）

??事務支持??：支持 ??ACID 事務??，適合需要高可靠性的場景（如支付、訂單）。
??鎖機制??：默認使用 ??行級鎖??，支持高并發操作。
??外鍵約束??：支持外鍵，保證數據完整性。
??崩潰恢復??：通過 ??redo log?? 和 ??undo log?? 實現崩潰后的數據恢復。
??存儲結構??：數據按主鍵聚簇索引存儲，二級索引保存主鍵值。
??適用場景??：OLTP（在線事務處理）、高并發讀寫、需要事務的場景。

MyISAM?

事務支持??：??不支持事務??，無法保證數據一致性。
??鎖機制??：??表級鎖??，并發寫入性能差。
??存儲結構??：數據文件（.MYD）、索引文件（.MYI）、表結構文件（.frm）分離。
??特性??：支持全文索引（FULLTEXT）、壓縮表、空間索引（GIS）。
??適用場景??：讀多寫少（如日志分析）、靜態數據、不需要事務的場景。

Memory（HEAP）?

數據存儲??：數據完全存儲在內存中，重啟后數據丟失。
??鎖機制??：表級鎖，并發性能一般。
??特性??：支持哈希索引和B樹索引，查詢速度極快。
??適用場景??：臨時表、緩存表、快速查詢中間結果。

Archive?

存儲方式??：數據高度壓縮存儲，適合歸檔。
??寫入性能??：僅支持插入和查詢，不支持刪除和更新。
??適用場景??：日志歸檔、歷史數據存儲。

CSV?

存儲方式??：數據以純文本CSV格式存儲。
??特性??：不支持索引，適合導入/導出數據。
??適用場景??：與外部系統交換數據。

Blackhole?

特性??：接收數據但不存儲，直接丟棄。常用于數據復制的中繼。

核心區別對比?

如何選擇存儲引擎

??需要事務??：必須選 ??InnoDB??。
??讀多寫少??：若不需要事務，可選 ??MyISAM??（注意表鎖問題）。
??臨時數據/緩存??：使用 ??Memory??，但需注意數據易失性。
??歸檔數據??：選擇 ??Archive?? 或 InnoDB 壓縮表。
??高并發寫入??：優先 InnoDB（行級鎖）。

操作示例?

??查看表的存儲引擎??：

SHOW TABLE STATUS LIKE 'table_name';

??修改表的存儲引擎??：

ALTER TABLE table_name ENGINE = InnoDB;

??設置默認存儲引擎??（需在 my.cnf 中配置）：

[mysqld]
default-storage-engine = InnoDB

總結

InnoDB?? 是 MySQL 默認引擎，適用于大多數場景，尤其是需要事務和高并發的場景。
??MyISAM?? 在只讀或低并發寫入場景下仍有價值，但逐漸被 InnoDB 替代。
其他引擎（如 Memory、Archive）在特定場景下能發揮獨特優勢。

MySQL 的鎖機制

鎖的粒度分類?

表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的概率最高，并發度最低。
行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高。
頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般

表級鎖（Table-Level Lock）?

定義??：直接鎖定整張表，粒度最粗，并發度最低。
??適用引擎??：MyISAM、Memory、CSV 等。
??特點??：
??共享鎖（S）??：允許其他事務讀表，但禁止寫。
??排他鎖（X）??：禁止其他事務讀寫表。
??操作示例??：

-- MyISAM 隱式加表鎖（自動管理）
SELECT * FROM table_name;          -- 加共享鎖
INSERT INTO table_name VALUES (...); -- 加排他鎖

頁級鎖（Page-Level Lock）?

定義??：鎖定數據頁（一組連續的行），粒度介于表鎖和行鎖之間。
??適用引擎??：BDB（已被 MySQL 廢棄）。

行級鎖（Row-Level Lock）?

定義??：鎖定單行或多行數據，粒度最細，并發度最高。
??適用引擎??：InnoDB。
??特點??：
??共享鎖（S Lock）??：允許其他事務讀，但禁止修改該行。
??排他鎖（X Lock）??：禁止其他事務讀寫該行。
??操作示例??：

-- 顯式加行鎖（InnoDB）
SELECT * FROM table_name WHERE id = 1 FOR SHARE;   -- 共享鎖
SELECT * FROM table_name WHERE id = 1 FOR UPDATE;  -- 排他鎖

??子類型??：
記錄鎖（Record Lock）??：鎖定索引中的一條記錄。
??間隙鎖（Gap Lock）??：鎖定索引記錄之間的間隙（防止幻讀）。
??臨鍵鎖（Next-Key Lock）??：記錄鎖 + 間隙鎖（鎖定左開右閉區間）。
??插入意向鎖（Insert Intention Lock）??：在插入前標記意向，避免間隙鎖沖突。

InnoDB 的行級鎖實現?

記錄鎖（Record Lock）?

作用??：鎖定索引中的一條記錄。
??示例??：SELECT * FROM user WHERE id = 1 FOR UPDATE;

間隙鎖（Gap Lock）?

鎖定范圍??：索引記錄之間的 ??間隙??（即兩個索引值之間的區間）。
作用??：鎖定索引記錄之間的間隙，防止其他事務插入數據。
特點?：
不鎖定現有記錄??：僅鎖定間隙，不影響已存在的記錄修改或刪除。
??僅作用于非唯一索引??：
—唯一索引的等值查詢（如 WHERE id=10）??不會加間隙鎖??（因唯一性保證不會有重復值）。
—唯一索引的范圍查詢（如 WHERE id > 10）仍會加間隙鎖。
??示例??：

-- 表結構：id（主鍵）, age（普通索引）
-- 數據：id=1(age=10), id=2(age=20)-- 事務 A（RR 隔離級別）
SELECT * FROM users WHERE age = 15 FOR UPDATE;
-- 鎖定間隙 (10,20)，阻止插入 age=15 的記錄-- 事務 B 嘗試插入
INSERT INTO users (age) VALUES (15); -- 被阻塞

臨鍵鎖（Next-Key Lock）?

間隙鎖是臨鍵鎖的組成部分，臨鍵鎖 = 行鎖（Record Lock） + 間隙鎖（Gap Lock）。當臨鍵鎖僅鎖定間隙（不鎖定具體行）時，退化為純間隙鎖。
定義??：行鎖 + 間隙鎖，鎖定左開右閉區間（例如 (1,5]）。
??目的??：解決幻讀問題（在 ??RR 隔離級別?? 下，InnoDB 對范圍查詢默認使用 ??臨鍵鎖??（Next-Key Lock），其鎖定范圍為 ??左開右閉區間??。）。
適用于所有索引??：包括主鍵索引、唯一索引和普通索引。
??示例??：

-- 表結構：id（主鍵）, age（普通索引）
-- 數據：id=1(age=10), id=2(age=20)-- 事務 A（RR 隔離級別）
SELECT * FROM users WHERE age > 10 AND age <= 20 FOR UPDATE;
-- 加臨鍵鎖 (10,20]，阻止插入 age ∈ (10,20] 的數據-- 事務 B 嘗試插入或修改
INSERT INTO users (age) VALUES (15); -- 被阻塞
UPDATE users SET age = 15 WHERE id = 1; -- 被阻塞（修改為 age=15）

插入意向鎖（Insert Intention Lock）?

作用??：在插入數據前設置，表示事務想在某個間隙插入數據，與間隙鎖沖突。

按鎖的行為分類?

共享鎖（Shared Lock, S Lock； 又稱讀鎖）?

??作用??：允許其他事務讀，但禁止修改鎖定的數據。
??使用場景??：讀操作（如 SELECT … LOCK IN SHARE MODE或SELECT … FOR SHARE）。

??語法?	說明?
SELECT … LOCK IN SHARE MODE	舊版語法（MySQL 5.7 及之前版本常用），用于為讀取的數據行加共享鎖。
SELECT … FOR SHARE	新版語法（MySQL 8.0 引入），替代 LOCK IN SHARE MODE，功能相同，但支持更多擴展選項（如 NOWAIT、SKIP LOCKED）。
??兼容性??：多個共享鎖可共存。

排他鎖（Exclusive Lock, X Lock；又稱寫鎖、獨占鎖）?

??作用??：禁止其他事務讀寫鎖定的數據。
??使用場景??：寫操作（如 SELECT … FOR UPDATE、INSERT/UPDATE/DELETE）。
??兼容性??：與其他鎖互斥。

意向鎖（Intention Lock）?

??目的??：快速判斷表級鎖和行級鎖的沖突，避免逐行檢查。
??類型??：
??意向共享鎖（IS）??：事務打算對某些行加共享鎖。
??意向排他鎖（IX）??：事務打算對某些行加排他鎖。
??兼容性??：

按鎖的實現方式分類?

樂觀鎖?

樂觀鎖則基于一種樂觀的態度來處理并發問題，它假設數據沖突將不會頻繁發生，因此在數據讀取時不會加鎖，而是在更新數據時檢查數據是否被其他事務修改過。
??實現??：通過版本號或時間戳（應用層控制）。
適用場景??：讀多寫少，沖突概率低。
??示例??：

UPDATE product SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND version = 5;

工作機制：

數據讀取：

事務首先讀取需要修改的數據及其版本號（或時間戳）。這個版本號是在數據庫中額外維護的一個字段，用于跟蹤數據的更新情況。

數據操作：

事務在本地對讀取的數據進行修改，但不立即更新到數據庫中。

提交前的檢查：

在提交事務之前，事務會再次查詢數據庫中對應數據的版本號，并將其與第一步讀取的版本號進行比較。

數據更新：

如果版本號相同，說明數據在讀取和提交之間沒有被其他事務修改過，事務可以安全地更新數據，并將版本號增加（或更新時間戳）。
如果版本號不同，說明數據在讀取和提交之間已經被其他事務修改過，此時當前事務可以選擇重試（重新讀取數據并嘗試更新）、回滾或向應用層報告錯誤。

提交事務：

如果數據更新成功，事務將提交，所做的修改將被保存到數據庫中。

悲觀鎖?

悲觀鎖是假定沖突將頻繁發生，因此在操作數據之前先鎖定數據。在MySQL中，悲觀鎖可以通過行級鎖（InnoDB存儲引擎提供）或表級鎖（MyISAM或InnoDB在某些情況下）來實現。
??適用場景??：寫多讀少，沖突概率高。
實現??：
1.使用SELECT … FOR UPDATE）：
這是最常用的悲觀鎖實現方式。當事務使用SELECT … FOR UPDATE語句讀取記錄時，MySQL會對這些記錄加鎖，其他事務必須等待鎖釋放才能修改這些記錄。

START TRANSACTION;
SELECT * FROM accounts WHERE id = 100 FOR UPDATE;
-- 在這里進行更新操作
UPDATE accounts SET balance = balance - 100 WHERE id = 100;
COMMIT;

注意：FOR UPDATE必須在一個事務中使用，否則會立即釋放鎖。
2.使用表鎖
雖然表鎖不是悲觀鎖的最佳實踐（因為它會鎖定整個表），但在某些情況下仍然可以使用。通過LOCK TABLES和UNLOCK TABLES命令可以顯式地鎖定和解鎖表。

LOCK TABLES accounts WRITE;
-- 在這里進行更新操作
UPDATE accounts SET balance = balance - 100 WHERE id = 100;
UNLOCK TABLES;

索引類型與加鎖規則?

在 ??可重復讀（RR）隔離級別?? 下，SELECT * FROM users WHERE age = 25 FOR UPDATE; 的加鎖行為取決于 age 字段的索引類型和數據分布。以下是不同場景下的詳細分析：

age 是普通索引（非唯一索引）?

??數據分布假設??：
表中存在 age 為 20、25、30 的記錄（假設 age 索引結構如下）：
age索引樹：20 → 25 → 30
??加鎖范圍??：
??行鎖（Record Lock）??：鎖定所有 age=25 的記錄。
??間隙鎖（Gap Lock）??：鎖定 age=25 前后的間隙：
左間隙：(20, 25)
右間隙：(25, 30)
??Next-Key Lock??：實際加鎖為 ??左開右閉區間??（InnoDB 默認使用 Next-Key Lock），即：
(20, 25]（鎖定 age=25 的行及其左間隙）
(25, 30)（鎖定右間隙，防止插入新的 age=25）
??效果??：
其他事務無法插入或修改 age=25 的記錄，也無法在 (20, 30) 區間插入 age=25 的新數據。
徹底避免幻讀。

age 是唯一索引（Unique Index）?

??數據分布假設??：
age 是唯一索引，表中存在 age=25 的記錄。
??加鎖范圍??：
??僅行鎖??：鎖定 age=25 的記錄。
??無間隙鎖??：唯一索引的唯一性保證了其他事務無法插入 age=25 的新數據，因此無需加間隙鎖。
??例外情況??：
如果 age=25 ??不存在??，則會鎖定該值所在的間隙。例如：
現有 age=20 和 age=30，則鎖定 (20, 30) 間隙，阻止插入 age=25。

age 無索引?

??加鎖范圍??：
??全表掃描??：所有記錄的行鎖 + 所有間隙的間隙鎖（即鎖全表）。
??效果??：其他事務無法插入或修改表中任何數據，性能極差。
??建議??：
必須為 WHERE 條件字段添加索引，避免全表鎖。

InnoDB 特有的鎖?

自增鎖（AUTO-INC Lock）?

作用??：
確保自增列（AUTO_INCREMENT）的值連續且唯一。
??模式??：
??傳統模式??：表級鎖，事務完成后釋放（影響并發插入性能）。
??連續模式??（MySQL 8.0 默認）：輕量級鎖，僅保證自增值的連續性。

隱式鎖（Implicit Lock）?

觸發條件??：插入操作時，對新插入的數據自動加隱式排他鎖。
??作用??：避免其他事務在插入完成前訪問未提交的數據。

死鎖

死鎖（Deadlock）是指兩個或多個事務相互等待對方釋放鎖資源，導致無限阻塞的現象。MySQL 中死鎖通常由 ??行級鎖（Record Lock）??、??間隙鎖（Gap Lock）?? 和 ??Next-Key Lock?? 的沖突引起，尤其在 ??可重復讀（RR）?? 隔離級別下更易發生

導致死鎖的鎖類型?

鎖類型?	描述?	死鎖場景?
行鎖（Record Lock）?	鎖定某一行記錄，其他事務無法修改或刪除。	多個事務以不同順序請求同一批行鎖時，形成循環等待。
間隙鎖（Gap Lock）?	鎖定索引記錄之間的間隙，防止其他事務插入數據。	多個事務在相同間隙范圍內插入數據，互相等待對方釋放間隙鎖。
Next-Key Lock?	行鎖 + 間隙鎖，鎖定一個左開右閉的區間（如 (5,10]）。	事務以相反順序鎖定不同范圍的 Next-Key Lock，形成環路。
插入意向鎖（Insert Intention Lock）?	一種特殊的間隙鎖，表示事務準備在某個間隙插入數據。	多個事務在同一間隙的不同位置插入數據，互相等待對方的間隙鎖釋放。

典型死鎖場景與原理?

行鎖沖突（交叉更新順序）?

場景??：
事務 A 和事務 B 以相反順序更新兩條記錄。
??示例??：

-- 事務 A
UPDATE users SET name = 'A' WHERE id = 1;  -- 鎖定 id=1 的行
UPDATE users SET name = 'B' WHERE id = 2;  -- 嘗試鎖定 id=2 的行（被事務 B 阻塞）-- 事務 B
UPDATE users SET name = 'B' WHERE id = 2;  -- 鎖定 id=2 的行
UPDATE users SET name = 'A' WHERE id = 1;  -- 嘗試鎖定 id=1 的行（被事務 A 阻塞）

死鎖原因??：
事務 A 持有 id=1 的行鎖，等待 id=2 的行鎖；事務 B 持有 id=2 的行鎖，等待 id=1 的行鎖，形成循環依賴。

間隙鎖沖突（并發插入相同間隙）?

??場景??：
事務 A 和事務 B 在同一個間隙內插入數據，但插入位置不同。
??示例??：

-- 表結構：id 是主鍵，當前存在 id=10 和 id=20 的記錄。
-- 事務 A
INSERT INTO users (id) VALUES (15);  -- 嘗試獲取間隙 (10,20) 的插入意向鎖-- 事務 B
INSERT INTO users (id) VALUES (18);  -- 嘗試獲取間隙 (10,20) 的插入意向鎖

??死鎖原因??：
事務 A 和事務 B 都試圖在間隙 (10,20) 插入數據。
插入意向鎖與已存在的間隙鎖（如 Next-Key Lock）沖突，導致互相等待。

Next-Key Lock 沖突（范圍查詢與插入）?

場景??：
事務 A 范圍查詢加 Next-Key Lock，事務 B 在范圍內插入數據。
??示例??：

-- 事務 A（RR 隔離級別）
SELECT * FROM users WHERE id > 10 AND id < 20 FOR UPDATE;  -- 加 Next-Key Lock (10,20]-- 事務 B
INSERT INTO users (id) VALUES (15);  -- 嘗試獲取插入意向鎖，被事務 A 阻塞-- 事務 A 再次操作
INSERT INTO users (id) VALUES (15);  -- 嘗試獲取插入意向鎖，被事務 B 阻塞

死鎖原因??：
事務 A 持有 (10,20] 的 Next-Key Lock，事務 B 等待插入；事務 A 隨后嘗試插入同一條數據，被事務 B 阻塞，形成死鎖。

死鎖產生條件?

互斥??：資源被獨占。
??請求與保持??：事務持有鎖并請求新鎖。
??不可剝奪??：鎖只能由持有者釋放。
??循環等待??：事務間形成環形等待。

死鎖處理?

檢測??：InnoDB 使用等待圖（wait-for graph）檢測死鎖。
??解決??：強制回滾代價較小的事務。

查看死鎖日志??：

SHOW ENGINE INNODB STATUS;  -- 查看 LATEST DETECTED DEADLOCK 部分

查找輸出中的 LATEST DETECTED DEADLOCK 部分，這里會顯示導致死鎖的具體事務信息，包括涉及的表、行、鎖和事務 ID。
??關鍵信息解析??：
??TRANSACTION??：參與死鎖的事務 ID 和狀態。
??HOLDS THE LOCK(S)??：事務當前持有的鎖類型和范圍。
??WAITING FOR THIS LOCK??：事務正在等待的鎖類型和范圍。
??WE ROLL BACK TRANSACTION??：被回滾的事務 ID。

查詢MySQL整體的鎖狀態：

show status like 'innodb_row_lock_%';

Innodb_row_lock_current_waits：當前正在阻塞等待鎖的事務數量。
Innodb_row_lock_time：MySQL啟動到現在，所有事務總共阻塞等待的總時長。
Innodb_row_lock_time_avg：平均每次事務阻塞等待鎖時，其平均阻塞時長。
Innodb_row_lock_time_max：MySQL啟動至今，最長的一次阻塞時間。
Innodb_row_lock_waits：MySQL啟動到現在，所有事務總共阻塞等待的總次數。

使用 INFORMATION_SCHEMA 表獲取詳細信息：

可以查詢 INFORMATION_SCHEMA 表來獲取當前進行的事務和連接信息。例如，使用以下 SQL 語句獲取活動中的事務信息：

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

查詢進程列表

使用 SHOW PROCESSLIST 命令可以看到當前所有連接和執行中的 SQL 語句：

SHOW PROCESSLIST;

輸出將包括每個連接的線程 ID、USER、HOST、DB、COMMAND、TIME、STATE 和 INFO 字段，其中 INFO 字段顯示正在執行的 SQL 語句。

終止導致死鎖的事務

一旦確認了具體的事務和 SQL 語句，下一步是終止這個事務。
根據 SHOW ENGINE INNODB STATUS 和 SHOW PROCESSLIST 得到的 ID，可以使用 KILL 命令終止相應的連接。

-- 從SHOW PROCESSLIST結果中獲取具體線程ID
KILL 12345;

避免死鎖?

保持事務簡短，減少鎖持有時間。
按固定順序訪問表和行。
合理設計索引，減少鎖范圍。
使用 SELECT … FOR UPDATE 時明確指定索引。