昨日內容復習

MySQL 使用 B+ 樹作為索引的優勢是什么？

B+ 樹是一種多叉樹，其非葉子結點僅存放索引，而葉子結點存放真正的數據，葉子結點之間通過雙向鏈表連接，可以優化查詢效率。

通過 B+ 樹存儲千萬級別的數據，在訪問時只需要進行 3 ～ 4 次的磁盤 I/O 操作。在對 B+ 樹進行修改時，由于 B+ 樹僅在葉子結點存放真正的數據，因此修改數據時不會修改樹的結果。與 B 樹相比，由于 B 樹在葉子結點和非葉子結點都會存放數據，因此 B 樹在修改時可能會涉及樹的變形。

索引有哪幾種？

• 單值索引：一個索引只包含一個列，一個表可以包含多個單值索引；
• 唯一索引：索引列的值必須唯一，但允許存在重復的 null；
• 主鍵索引：設定數據庫表中某個鍵為主鍵時，該主鍵會自動成為主鍵索引；
• 復合索引：一個索引包含多個列；
• 前綴索引：對字符類型的前幾個字符建立索引；

什么是最左匹配原則？

MySQL 的最左匹配原則是 B+ 樹聯合索引的核心特性，指查詢時必須從索引的最左列開始，并按照聯合索引的定義順序依次匹配，否則聯合索引失效。

需要注意的是，根據最左匹配原則，當復合索引當中出現范圍查詢時，該范圍查詢將會生效，但是范圍查詢之后的條件均不生效。

索引區分度？

當查詢優化器發現所使用索引當中某個值出現的比例超過某個閾值（比如 30%）時，查詢優化器將放棄走索引，轉而進行全表掃描。

聯合索引如何排序？

可以利用所有的有序性，在排序列和索引列建立聯合索引。

使用索引有哪些缺陷？

索引本身也是一張表，建立索引需要占用存儲空間。

此外，索引不宜建立在頻繁增刪改的字段上，增刪改的字段如果建立了索引，那么原表和索引表都需要修改，增加額外的時間開銷。

什么時候需要建立索引，什么時候不需要？

需要建立索引

• 表的主鍵：自動成為主鍵索引；
• 表當中某個字段需要唯一性約束：對該字段建立唯一索引；
• 直接進行條件查詢的字段：經常使用 WHERE 查詢的字段，建立索引可以提高效率；
• 宜對外鍵建立索引；
• 對排序字段建立索引可以大大提高排序速度；
• 常用于 GROUP BY 和 ORDER BY 的字段可以建立聯合索引。

不宜建立索引的場景

• 表中某個需要頻繁增刪改的字段不宜建立索引；
• 表的記錄很少時，可以直接走全表掃描；
• 數據重復且分布均勻的字段不宜建立索引；
• 經常和其他字段一起查詢但其他字段的索引值較多的字段不宜建立索引。

使用索引的注意事項

• like 前導模糊查詢會使索引失效；
• 負向查詢不能使用索引；
• 聯合查詢注意最左匹配原則；
• 避免過度使用索引，因為建立索引會引入額外的存儲與維護開銷；
• 更新頻繁的字段不宜建立索引；
• 避免在索引列引入算式或函數，如是會使索引列失效并退化為全表掃描；

復習 MySQL Day3：鎖

MySQL 有哪些類型的鎖？每種類型的鎖當中又具體有哪些鎖？

按鎖的粒度分類

全局鎖

• FLUSH TABLES WITH READ LOCK（FTWRL）：鎖定整個數據庫實例（注意是整個數據庫，而不是某個表），所有數據表進入只讀狀態。全局鎖主要用于數據備份的場景。

表級鎖

表鎖

• 基本表鎖：LOCK TABLES ... READ/WRITE；
• 意向鎖（Intention Locks）：進一步細分為意向共享鎖（IS）和意向排他鎖（IX）；
• 與行鎖的功能類似，表鎖的核心作用也是并發控制：協調多個會話對同一個表的并發訪問，防止并發操作導致的數據不一致。表鎖以整張表為單位進行鎖定，是最簡單之間的并發控制方式。
• 表鎖的使用場景包括：數據庫備份操作、大批量數據導入/導出、需要確保數據一致性的復雜多表操作。

元數據鎖（MDL）

元數據鎖：

• 自動加鎖，用于保護表結構的變更；
• 讀鎖：查詢時自動讀取；
• 寫鎖：表結構更改時獲取。

行級鎖（InnoDB 特有）

記錄鎖（Record Locks）

記錄鎖用于鎖定索引中的單條記錄，是最基本的行鎖類型。

間隙鎖（Gap Locks）

• 記錄鎖用于鎖定索引記錄間的間隙。
• 可防止幻讀現象的發生。
• 僅在「可重復讀」隔離級別下生效。

臨鍵鎖（Next-Key Locks）

• 臨鍵鎖是記錄鎖和間隙鎖的組合。
• 臨鍵鎖可以鎖定當前記錄及當前記錄之前的間隙，具體來說，臨鍵鎖鎖定的是一個「左開右閉」區間，上一條索引值到當前索引值之間的間隙將會被鎖定，避免在讀寫期間有新的記錄插入這個區間。
• 臨鍵鎖是 InnoDB 的默認行鎖實現方式。

插入意向鎖（Insert Intention Locks）

• 插入意向鎖是特殊的間隙鎖，需要注意的是，插入意向鎖是行鎖，要與表級的意向鎖區分開來。
• 一個事務在向數據表中插入一條數據時，需要先判斷插入位置是否有間隙鎖（注意，臨鍵鎖也包含間隙鎖）。如果有，那么當前插入操作會被阻塞，直到擁有間隙鎖的事務提交。在當前事務阻塞期間，會生成一個插入意向鎖，表明事務想在某個區間插入新記錄，但是目前處于等待狀態。

按鎖的模式分類

共享鎖（S鎖）

• 共享鎖又稱讀鎖，通過 SELECT ... LOCK IN SHARE MODE 的方式加鎖。
• 共享鎖允許多個事務同時獲取。

排他鎖（X鎖）

• 排他鎖又稱寫鎖，通過 SELECT ... FOR UPDATE 的方式獲取。
• 排他鎖一次只能由一個事務持有。

特殊鎖類型

自增鎖（Auto Inc Locks）

自增鎖用于自增列的插入操作，它是特殊的表級鎖。

謂詞鎖（Predicate Locks）

在串行化隔離級別下生效，鎖定滿足特定搜索條件的行。

意向鎖的作用是什么？什么時候需要加意向鎖？

意向鎖是 InnoDB 中特殊的表級鎖，它在行鎖和表鎖之間起到了關鍵的協調作用。

意向鎖的核心作用

• 多粒度鎖定協調：解決表鎖和行鎖的共存問題，使不同粒度的鎖能夠高效協同工作；
• 快速沖突檢測：提供一種“預先聲明”機制，避免在加表鎖時還需要檢查行鎖狀態。

意向鎖的工作機制
首先，意向鎖是表鎖。之后，意向鎖分為意向共享鎖（IS）和意向排他鎖（IX）。具體來說：

• IS：表示事務準備在表的某些行加共享鎖；
• IX：表示事務準備在表的某些行加排他鎖。

何時會加意向鎖

• IS：當事務需要加行級 S 鎖時，會首先自動加表級的 IS 鎖；
• IX：當事務需要加行級 X 鎖時，會首先自動加表級的 IX 鎖。

意向鎖的價值

• 避免在加表鎖時檢查索引行的狀態來查看是否有行已經加表鎖，直接查看意向鎖這個表級鎖就可以得知當前表中是否有行被加鎖。
• 意向鎖可以預防死鎖：在加行鎖之前，必須先獲取意向鎖，然后才能夠加行鎖。

MySQL 的全局鎖有什么作用？

MySQL 全局鎖的作用是做全庫邏輯備份，加全局鎖之后整個數據庫處于只讀狀態，增刪改會被阻塞。

全局鎖的缺陷在于當數據庫數據過多時，全局備份時間較慢，由于不能增刪改，因此會使業務停滯。一個優化的方法是通過可重復讀隔離級別下的 MVCC。

MySQL 如何加鎖？

下面以 InnoDB 引擎為例，簡述 MySQL 的加鎖過程。

加鎖的基本流程

鎖的觸發時機

• 自動加鎖：DML 語句（INSERT / DELETE / UPDATE）時自動獲取；
• 手動加鎖：SELECT ... FOR UPDATE 加排他鎖，SELECT ... LOCK IN SHARE MODE 加排他鎖；
• DDL 加鎖：表的結構變更時自動加鎖。

加鎖的基本步驟

1. 解析 SQL 以確定需要訪問的表以及行；
2. 獲取意向鎖（表級）；
3. 根據隔離級別和查詢條件確定鎖的類型以及查詢范圍；
4. 在存儲引擎層加實際的行鎖 / 表鎖；
5. 記錄鎖信息到內存結構。

不同操作的加鎖規則

SELECT 語句

• 普通 SELECT（快照讀）：不加鎖；
• SELECT ... FOR UPDATE：加排他鎖；
• SELECT ... LOCK IN SHARE MODE：加共享鎖。

DML 語句

• INSERT：排他鎖 + 插入意向鎖；
• UPDATE：先加共享鎖查找，再加排他鎖修改；
• DELETE：加排他鎖。

InnoDB 行鎖的加鎖過程

基于索引的加鎖

-- 假設有索引 idx_age
UPDATE users SET name='yggp' WHERE age=25;

加鎖步驟：

1. 通過 idx_age 找到 age=25 的記錄；
2. 獲取對應行的排他鎖；
3. 如果當前隔離級別是可重復讀，那么還會加間隙鎖防止幻讀。

無索引時加鎖

-- 無合適索引的列
UPDATE users SET name='yggp' WHERE phone='123456';

加鎖步驟：

• 全表掃描：對所有掃描到的行加鎖；
• 風險：容易導致大量鎖沖突和性能問題。

鎖的升級與轉換

鎖升級條件
當單個事務鎖定的行超過 innodb_change_buffer_max_size 時，系統自動將行鎖升級為表鎖。

鎖轉換場景
指的是先加共享鎖的行如果后續需要修改，那么共享鎖將升級為排他鎖。UPDATE 語句的加鎖過程就是先通過共享鎖找到對應的記錄，再加排他鎖對數據進行修改。

-- 事務內的鎖轉換示例
BEGIN;
SELECT * FROM accounts WHERE id=1 LOCK IN SHARE MODE;	-- 獲取 S 鎖
-- 下面執行 UPDATE, S -> X
UPDATE accounts SET balance=100 WHERE id=1;				-- S -> X