一、MySQL基礎(chǔ)篇

面試官：請(qǐng)你介紹一下MySQL的架構(gòu)，它主要由哪些部分組成？

回答：

好的，MySQL的架構(gòu)可以分為三層，我從上到下給您介紹一下：

第一層是連接層，也叫客戶端連接層。當(dāng)我們的應(yīng)用程序連接MySQL時(shí)，首先會(huì)經(jīng)過這一層。它主要負(fù)責(zé)：

• 處理客戶端的連接請(qǐng)求
• 進(jìn)行身份認(rèn)證，驗(yàn)證用戶名密碼
• 管理連接池，維護(hù)線程

第二層是服務(wù)層，這是MySQL的核心層，包含了很多重要組件：

• 查詢緩存：不過在MySQL 8.0已經(jīng)移除了，因?yàn)槊新侍?/li>
• 解析器：對(duì)SQL語句進(jìn)行詞法分析和語法分析，生成解析樹
• 優(yōu)化器：這個(gè)很重要，它會(huì)對(duì)SQL進(jìn)行優(yōu)化，選擇最優(yōu)的執(zhí)行計(jì)劃，比如選擇用哪個(gè)索引
• 執(zhí)行器：調(diào)用存儲(chǔ)引擎接口，執(zhí)行SQL語句

第三層是存儲(chǔ)引擎層，MySQL支持插件式的存儲(chǔ)引擎，常見的有InnoDB、MyISAM等。存儲(chǔ)引擎負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和讀取。

一條SQL語句的執(zhí)行流程大概是這樣的：客戶端發(fā)送SQL → 連接器驗(yàn)證 → 解析器解析 → 優(yōu)化器優(yōu)化 → 執(zhí)行器執(zhí)行 → 存儲(chǔ)引擎讀寫數(shù)據(jù)。

面試官：你剛才提到了InnoDB和MyISAM，能詳細(xì)說說它們的區(qū)別嗎？

回答：

好的，這兩個(gè)是MySQL最常用的存儲(chǔ)引擎，它們的區(qū)別我從幾個(gè)維度來說：

首先是事務(wù)支持方面：

• InnoDB支持事務(wù)，支持ACID特性，可以進(jìn)行COMMIT和ROLLBACK操作
• MyISAM不支持事務(wù)，如果操作出錯(cuò)，沒辦法回滾

其次是鎖的粒度：

• InnoDB支持行級(jí)鎖，鎖的粒度更細(xì)，并發(fā)性能更好
• MyISAM只支持表級(jí)鎖，一個(gè)寫操作會(huì)鎖住整張表，并發(fā)性能較差

第三是外鍵約束：

• InnoDB支持外鍵，可以保證數(shù)據(jù)的引用完整性
• MyISAM不支持外鍵

第四是索引結(jié)構(gòu)：

• InnoDB使用聚簇索引，數(shù)據(jù)文件和索引文件是綁定在一起的，主鍵索引的葉子節(jié)點(diǎn)直接存儲(chǔ)數(shù)據(jù)行
• MyISAM使用非聚簇索引，數(shù)據(jù)文件和索引文件是分開的，索引的葉子節(jié)點(diǎn)存儲(chǔ)的是數(shù)據(jù)的地址

第五是COUNT(*)性能：

• MyISAM會(huì)保存表的總行數(shù)，執(zhí)行COUNT(*)時(shí)直接返回，非常快
• InnoDB不保存行數(shù)，需要全表掃描來統(tǒng)計(jì)

第六是崩潰恢復(fù)：

• InnoDB有redo log和undo log，支持崩潰后的數(shù)據(jù)恢復(fù)
• MyISAM沒有這個(gè)機(jī)制，崩潰后可能丟失數(shù)據(jù)

總結(jié)一下，現(xiàn)在基本都推薦使用InnoDB，它從MySQL 5.5版本開始就是默認(rèn)引擎了。除非是一些只讀的、不需要事務(wù)的場(chǎng)景，否則都應(yīng)該選InnoDB。

面試官：你提到InnoDB使用聚簇索引，能詳細(xì)解釋一下什么是聚簇索引和非聚簇索引嗎？

回答：

好的，這個(gè)問題很重要，我來詳細(xì)說一下。

聚簇索引，英文叫Clustered Index，它的特點(diǎn)是：索引和數(shù)據(jù)是存儲(chǔ)在一起的。在InnoDB中，主鍵索引就是聚簇索引，它的B+Tree葉子節(jié)點(diǎn)直接存儲(chǔ)的是完整的數(shù)據(jù)行。

舉個(gè)形象的例子，聚簇索引就像一本按拼音順序排列的字典，字的解釋就直接跟在拼音后面，找到拼音就找到了內(nèi)容。

非聚簇索引，也叫二級(jí)索引或輔助索引，它的葉子節(jié)點(diǎn)存儲(chǔ)的不是數(shù)據(jù)行，而是主鍵的值。

比如我們?cè)趗sername字段上建了一個(gè)普通索引，當(dāng)我們通過username查詢時(shí)：

• 首先在username索引的B+Tree中找到對(duì)應(yīng)的主鍵id
• 然后再用這個(gè)id去聚簇索引中查找完整的數(shù)據(jù)行

這個(gè)過程就叫做回表。

關(guān)于回表，我再補(bǔ)充一下： 回表是有性能損耗的，因?yàn)樾枰獌纱蜝+Tree查找。所以在實(shí)際開發(fā)中，我們會(huì)盡量避免回表，方法就是使用覆蓋索引。

什么是覆蓋索引呢？就是我們查詢的字段剛好都在索引中，不需要回表就能拿到數(shù)據(jù)。比如：

-- 假設(shè)有聯(lián)合索引 idx_name_age(name, age)
SELECT name, age FROM user WHERE name = '張三';
-- 這個(gè)查詢就用到了覆蓋索引，不需要回表

還有一點(diǎn)要補(bǔ)充：InnoDB要求表必須有主鍵。如果我們沒有顯式定義主鍵，InnoDB會(huì)這樣處理：

• 首先找一個(gè)非空的唯一索引作為聚簇索引
• 如果也沒有，就會(huì)自動(dòng)生成一個(gè)6字節(jié)的隱藏主鍵ROW_ID

所以建表時(shí)一定要顯式定義主鍵，而且推薦使用自增主鍵，因?yàn)轫樞虿迦胄矢?，不?huì)造成頁分裂。

二、索引篇

面試官：為什么MySQL選擇B+Tree作為索引的數(shù)據(jù)結(jié)構(gòu)，而不是B-Tree或者Hash？

回答：

這是個(gè)好問題，我從幾個(gè)角度來分析：

首先說說為什么不用Hash：

Hash索引的等值查詢確實(shí)很快，時(shí)間復(fù)雜度是O(1)。但它有幾個(gè)致命缺點(diǎn)：

• 不支持范圍查詢：Hash只能做等值比較，像WHERE age > 20這種范圍查詢就沒法用
• 不支持排序：Hash是散列存儲(chǔ)的，沒有順序
• 不支持最左前綴匹配：對(duì)于聯(lián)合索引沒辦法部分使用
• 存在Hash沖突：大量數(shù)據(jù)時(shí)沖突會(huì)影響性能

然后說說B+Tree相比B-Tree的優(yōu)勢(shì)：

B-Tree的每個(gè)節(jié)點(diǎn)都存儲(chǔ)數(shù)據(jù)，而B+Tree只在葉子節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)，非葉子節(jié)點(diǎn)只存儲(chǔ)索引鍵。這帶來幾個(gè)好處：

• 樹的高度更低

  • 因?yàn)榉侨~子節(jié)點(diǎn)不存數(shù)據(jù)，每個(gè)節(jié)點(diǎn)能存儲(chǔ)更多的索引鍵
  • 一個(gè)節(jié)點(diǎn)通常是一個(gè)磁盤頁（16KB），能存更多key意味著樹更矮
  • 樹矮了，磁盤IO次數(shù)就少了，查詢更快

• 范圍查詢效率高

  • B+Tree的葉子節(jié)點(diǎn)之間用雙向鏈表連接
  • 范圍查詢時(shí)，找到起點(diǎn)后沿著鏈表遍歷就行
  • B-Tree做范圍查詢需要中序遍歷，效率低很多

• 查詢效率穩(wěn)定

  • B+Tree所有查詢都要走到葉子節(jié)點(diǎn)，路徑長(zhǎng)度一致
  • B-Tree可能在中間節(jié)點(diǎn)就找到數(shù)據(jù)，查詢效率不穩(wěn)定

實(shí)際舉個(gè)例子：

假設(shè)一個(gè)節(jié)點(diǎn)能存1000個(gè)key，3層的B+Tree能存儲(chǔ)1000×1000×1000 = 10億條數(shù)據(jù)，而3次磁盤IO就能定位到任意數(shù)據(jù)。這就是B+Tree的威力。

面試官：什么是最左前綴原則？能舉例說明嗎？

回答：

好的，最左前綴原則是使用聯(lián)合索引時(shí)必須遵循的規(guī)則。

簡(jiǎn)單來說，對(duì)于聯(lián)合索引，查詢條件必須從索引的最左邊開始匹配，并且不能跳過中間的列。

我舉個(gè)具體例子：

假設(shè)我們有一個(gè)聯(lián)合索引idx_a_b_c(a, b, c)，包含a、b、c三個(gè)字段。

-- 能用到索引的情況：
WHERE a = 1                    -- 用到索引(a)
WHERE a = 1 AND b = 2          -- 用到索引(a, b)
WHERE a = 1 AND b = 2 AND c = 3  -- 用到索引(a, b, c)，完全匹配

-- 能部分用到索引的情況：
WHERE a = 1 AND c = 3          -- 只用到(a)，c用不上，因?yàn)樘^了b

-- 用不到索引的情況：
WHERE b = 2                    -- 用不到，沒有最左邊的a
WHERE b = 2 AND c = 3          -- 用不到，沒有最左邊的a
WHERE c = 3                    -- 用不到

有一個(gè)特殊情況要注意：查詢條件的順序不影響索引使用，因?yàn)镸ySQL優(yōu)化器會(huì)自動(dòng)調(diào)整順序。

WHERE b = 2 AND a = 1          -- 優(yōu)化器會(huì)調(diào)整為 a = 1 AND b = 2，能用到索引

還有一個(gè)重要的點(diǎn)：范圍查詢會(huì)導(dǎo)致后面的列無法使用索引。

WHERE a = 1 AND b > 5 AND c = 3  
-- a走索引，b走索引（范圍），但c就用不到索引了

實(shí)際工作中的建議：

• 把區(qū)分度高的字段放在聯(lián)合索引的前面
• 把等值查詢的字段放在范圍查詢字段的前面
• 利用覆蓋索引避免回表

面試官：哪些情況會(huì)導(dǎo)致索引失效？

回答：

索引失效是面試高頻題，我總結(jié)了幾種常見的情況：

1. 違反最左前綴原則

-- 聯(lián)合索引 idx_a_b_c(a, b, c)
SELECT * FROM t WHERE b = 1;  -- 沒有a，索引失效

2. 在索引列上使用函數(shù)或運(yùn)算

-- 索引失效
SELECT * FROM user WHERE LEFT(name, 3) = '張';
SELECT * FROM user WHERE age + 1 = 25;
SELECT * FROM user WHERE YEAR(create_time) = 2024;
-- 正確寫法
SELECT * FROM user WHERE name LIKE '張%';
SELECT * FROM user WHERE age = 24;
SELECT * FROM user WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

3. 隱式類型轉(zhuǎn)換

-- 假設(shè)phone是varchar類型
SELECT * FROM user WHERE phone = 13800138000;  -- 數(shù)字，索引失效
SELECT * FROM user WHERE phone = '13800138000';  -- 字符串，索引有效

這里的原理是：當(dāng)類型不匹配時(shí)，MySQL會(huì)對(duì)索引列進(jìn)行類型轉(zhuǎn)換，相當(dāng)于加了函數(shù)，就失效了。

4. LIKE以%開頭

SELECT * FROM user WHERE name LIKE '%張';   -- 索引失效
SELECT * FROM user WHERE name LIKE '%張%';  -- 索引失效
SELECT * FROM user WHERE name LIKE '張%';   -- 索引有效

5. OR條件中有非索引列

-- 假設(shè)name有索引，status沒有索引
SELECT * FROM user WHERE name = '張三' OR status = 1;  -- 整體不走索引

要解決這個(gè)問題，要么給status也加索引，要么改寫成UNION：

SELECT * FROM user WHERE name = '張三'
UNION
SELECT * FROM user WHERE status = 1;

6. 使用NOT IN、NOT EXISTS、!=、<>

SELECT * FROM user WHERE id NOT IN (1, 2, 3);
SELECT * FROM user WHERE name != '張三';

這些操作可能導(dǎo)致優(yōu)化器認(rèn)為全表掃描更快，從而放棄索引。

7. IS NULL / IS NOT NULL（看情況）

如果表中NULL值很多，IS NOT NULL可能走索引；如果NULL值很少，IS NULL可能走索引。這個(gè)取決于數(shù)據(jù)分布和優(yōu)化器的判斷。

總結(jié)一句話：索引列要保持"干凈"，不要對(duì)它做任何加工，讓它直接參與比較。

三、事務(wù)篇

面試官：說說事務(wù)的四大特性ACID，以及MySQL是如何實(shí)現(xiàn)的？

回答：

ACID是事務(wù)的四大特性，我來逐一說明：

A - 原子性（Atomicity）

原子性是指事務(wù)是一個(gè)不可分割的整體，要么全部成功，要么全部失敗。

比如轉(zhuǎn)賬操作，A給B轉(zhuǎn)100塊：

• A的賬戶減100
• B的賬戶加100

這兩步必須同時(shí)成功或同時(shí)失敗，不能出現(xiàn)A扣了錢但B沒收到的情況。

MySQL的實(shí)現(xiàn)方式：通過undo log（回滾日志） 來實(shí)現(xiàn)。在執(zhí)行事務(wù)時(shí)，MySQL會(huì)把修改前的數(shù)據(jù)保存到undo log中。如果事務(wù)需要回滾，就用undo log中的數(shù)據(jù)恢復(fù)。

C - 一致性（Consistency）

一致性是指事務(wù)執(zhí)行前后，數(shù)據(jù)庫從一個(gè)一致狀態(tài)變到另一個(gè)一致狀態(tài)。比如轉(zhuǎn)賬前后，A和B的總金額應(yīng)該不變。

MySQL的實(shí)現(xiàn)方式：一致性是事務(wù)的最終目標(biāo)，是由其他三個(gè)特性（原子性、隔離性、持久性）共同保證的。

I - 隔離性（Isolation）

隔離性是指多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)，相互之間不能干擾。一個(gè)事務(wù)內(nèi)部的操作對(duì)其他并發(fā)事務(wù)是隔離的。

MySQL的實(shí)現(xiàn)方式：通過鎖和MVCC（多版本并發(fā)控制） 來實(shí)現(xiàn)。

• 寫寫沖突通過鎖來解決
• 讀寫沖突通過MVCC來解決

D - 持久性（Durability）

持久性是指事務(wù)一旦提交，對(duì)數(shù)據(jù)的修改就是永久的，即使系統(tǒng)崩潰也不會(huì)丟失。

MySQL的實(shí)現(xiàn)方式：通過redo log（重做日志） 來實(shí)現(xiàn)。事務(wù)提交時(shí)，先把修改寫入redo log并刷盤，這樣即使系統(tǒng)崩潰，重啟后也能通過redo log恢復(fù)數(shù)據(jù)。

補(bǔ)充一下redo log和undo log的區(qū)別：

• redo log：記錄的是"物理日志"，即數(shù)據(jù)頁的修改，用于崩潰恢復(fù)
• undo log：記錄的是"邏輯日志"，即相反的操作，用于事務(wù)回滾和MVCC

面試官：MySQL的事務(wù)隔離級(jí)別有哪些？分別能解決什么問題？

回答：

MySQL有四種事務(wù)隔離級(jí)別，從低到高分別是：

1. 讀未提交（READ UNCOMMITTED）

這是最低的隔離級(jí)別，一個(gè)事務(wù)可以讀取到另一個(gè)事務(wù)未提交的數(shù)據(jù)。

問題：會(huì)產(chǎn)生臟讀。比如事務(wù)A讀到了事務(wù)B修改但未提交的數(shù)據(jù)，如果B回滾了，A讀到的就是無效的臟數(shù)據(jù)。

實(shí)際開發(fā)中基本不用這個(gè)級(jí)別。

2. 讀已提交（READ COMMITTED）

一個(gè)事務(wù)只能讀取到其他事務(wù)已提交的數(shù)據(jù)。

解決了：臟讀問題
仍存在：不可重復(fù)讀。就是在同一個(gè)事務(wù)中，兩次讀取同一條數(shù)據(jù)可能得到不同的結(jié)果，因?yàn)槠陂g可能有其他事務(wù)修改并提交了。

Oracle數(shù)據(jù)庫的默認(rèn)隔離級(jí)別就是這個(gè)。

3. 可重復(fù)讀（REPEATABLE READ）

在同一個(gè)事務(wù)中多次讀取同一數(shù)據(jù)，結(jié)果是一致的。

解決了：臟讀、不可重復(fù)讀
仍存在：幻讀。就是在同一事務(wù)中，兩次查詢的結(jié)果集行數(shù)不同，因?yàn)橛衅渌聞?wù)插入或刪除了數(shù)據(jù)。

但是，MySQL的InnoDB在這個(gè)級(jí)別通過Next-Key Lock（臨鍵鎖） 在很大程度上解決了幻讀問題。

MySQL的默認(rèn)隔離級(jí)別就是REPEATABLE READ。

4. 串行化（SERIALIZABLE）

最高的隔離級(jí)別，事務(wù)串行執(zhí)行，完全隔離。

解決了：臟讀、不可重復(fù)讀、幻讀
代價(jià)：性能最差，并發(fā)度最低

我用表格總結(jié)一下：

實(shí)際工作中的選擇：

• 大多數(shù)互聯(lián)網(wǎng)項(xiàng)目用默認(rèn)的REPEATABLE READ就夠了
• 對(duì)于一些金融類的高一致性要求場(chǎng)景，可能會(huì)用到SERIALIZABLE
• 如果是讀多寫少、對(duì)一致性要求不那么高的場(chǎng)景，可以用READ COMMITTED提高并發(fā)性能

面試官：什么是MVCC？它是怎么實(shí)現(xiàn)的？

回答：

MVCC，全稱Multi-Version Concurrency Control，多版本并發(fā)控制。它的核心思想是：通過保存數(shù)據(jù)的多個(gè)版本，讓讀操作和寫操作不沖突，從而提高并發(fā)性能。

簡(jiǎn)單來說，當(dāng)你讀數(shù)據(jù)的時(shí)候，讀的是某一個(gè)歷史版本；當(dāng)其他事務(wù)在寫數(shù)據(jù)的時(shí)候，并不影響你的讀取。這就是所謂的"快照讀"。

MVCC的實(shí)現(xiàn)依賴三個(gè)核心組件：

1. 隱藏字段

InnoDB會(huì)為每行數(shù)據(jù)添加幾個(gè)隱藏字段：

• DB_TRX_ID（6字節(jié)）：記錄最后一次修改該行的事務(wù)ID
• DB_ROLL_PTR（7字節(jié)）：回滾指針，指向這行數(shù)據(jù)的上一個(gè)版本（在undo log中）
• DB_ROW_ID（6字節(jié)）：隱藏的自增主鍵，如果表沒有主鍵才會(huì)有這個(gè)

2. Undo Log（版本鏈）

每次修改數(shù)據(jù)時(shí)，舊版本會(huì)被保存到undo log中。多次修改就形成了一個(gè)版本鏈：

當(dāng)前數(shù)據(jù) → undo log版本1 → undo log版本2 → undo log版本3 → ...

通過DB_ROLL_PTR指針可以找到所有歷史版本。

3. Read View（讀視圖）

當(dāng)事務(wù)執(zhí)行快照讀（普通SELECT）時(shí)，會(huì)生成一個(gè)Read View，它包含：

• m_ids：當(dāng)前活躍（未提交）的事務(wù)ID列表
• min_trx_id：m_ids中的最小事務(wù)ID
• max_trx_id：系統(tǒng)應(yīng)該分配給下一個(gè)事務(wù)的ID
• creator_trx_id：創(chuàng)建這個(gè)Read View的事務(wù)ID

Read View的可見性判斷規(guī)則：

對(duì)于版本鏈中的某個(gè)版本，它的trx_id與Read View比較：

• 如果trx_id < min_trx_id，說明這個(gè)版本在Read View創(chuàng)建之前就已經(jīng)提交了，可見
• 如果trx_id >= max_trx_id，說明這個(gè)版本是在Read View創(chuàng)建之后才生成的，不可見
• 如果min_trx_id <= trx_id < max_trx_id：
  • 如果trx_id在m_ids中，說明這個(gè)版本的事務(wù)還未提交，不可見
  • 如果trx_id不在m_ids中，說明這個(gè)版本的事務(wù)已經(jīng)提交，可見

不同隔離級(jí)別下Read View的生成時(shí)機(jī)不同：

• READ COMMITTED：每次SELECT都會(huì)生成新的Read View
• REPEATABLE READ：只在第一次SELECT時(shí)生成Read View，后續(xù)復(fù)用

這就是為什么RC級(jí)別會(huì)有不可重復(fù)讀，而RR級(jí)別可以保證可重復(fù)讀的原因。

四、鎖篇

面試官：MySQL中有哪些類型的鎖？

回答：

MySQL的鎖可以從多個(gè)維度來分類，我來詳細(xì)說一下：

一、按鎖的粒度分

1. 全局鎖鎖住整個(gè)數(shù)據(jù)庫實(shí)例，使其處于只讀狀態(tài)。主要用于全庫邏輯備份。

FLUSH TABLES WITH READ LOCK;  -- 加鎖
UNLOCK TABLES;                -- 解鎖

2. 表級(jí)鎖鎖住整張表，開銷小、加鎖快，但并發(fā)度低。包括：

• 表鎖：LOCK TABLES t READ/WRITE
• 元數(shù)據(jù)鎖（MDL）：訪問表時(shí)自動(dòng)加，防止DDL和DML沖突
• 意向鎖：表級(jí)別的鎖，用于快速判斷表中是否有行鎖

3. 行級(jí)鎖只鎖住需要的行，開銷大、加鎖慢，但并發(fā)度高。InnoDB支持行級(jí)鎖。

二、按鎖的模式分

1. 共享鎖（S鎖/讀鎖）

SELECT ... LOCK IN SHARE MODE;  -- MySQL 5.x
SELECT ... FOR SHARE;           -- MySQL 8.0+

多個(gè)事務(wù)可以同時(shí)持有S鎖，用于讀取數(shù)據(jù)。

2. 排他鎖（X鎖/寫鎖）

SELECT ... FOR UPDATE;

只有一個(gè)事務(wù)能持有X鎖，用于修改數(shù)據(jù)。INSERT/UPDATE/DELETE會(huì)自動(dòng)加X鎖。

三、InnoDB的行鎖類型（重點(diǎn)）

1. Record Lock（記錄鎖）鎖住索引中的一條記錄。

2. Gap Lock（間隙鎖）鎖住索引記錄之間的間隙，防止其他事務(wù)在間隙中插入數(shù)據(jù)。這是為了解決幻讀問題。

比如表中有id為1、5、10的記錄，間隙鎖可以鎖住(1,5)、(5,10)這些區(qū)間。

3. Next-Key Lock（臨鍵鎖）Record Lock + Gap Lock的組合，鎖住一條記錄以及它前面的間隙。

這是InnoDB在REPEATABLE READ級(jí)別下默認(rèn)的行鎖算法。

舉個(gè)例子：假設(shè)表中有id為1、5、10的記錄，執(zhí)行：

SELECT * FROM t WHERE id = 5 FOR UPDATE;

在RR級(jí)別下，會(huì)加Next-Key Lock，鎖住(1,5]這個(gè)范圍。

面試官：什么是死鎖？怎么避免和解決？

回答：

什么是死鎖？

死鎖是指兩個(gè)或多個(gè)事務(wù)在執(zhí)行過程中，因互相持有對(duì)方需要的鎖而造成的一種阻塞現(xiàn)象。如果沒有外力介入，這些事務(wù)都無法繼續(xù)執(zhí)行。

舉個(gè)經(jīng)典的例子：

-- 事務(wù)A
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;  -- 獲得id=1的X鎖
-- 此時(shí)事務(wù)A持有id=1的鎖，等待id=2的鎖

-- 事務(wù)B
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 2;  -- 獲得id=2的X鎖
UPDATE account SET balance = balance + 100 WHERE id = 1;  -- 等待id=1的鎖

-- 事務(wù)A繼續(xù)
UPDATE account SET balance = balance + 100 WHERE id = 2;  -- 等待id=2的鎖
-- 死鎖產(chǎn)生！A等B釋放id=2，B等A釋放id=1

如何檢測(cè)和處理死鎖？

InnoDB有兩種策略：

1. 等待超時(shí)參數(shù)innodb_lock_wait_timeout，默認(rèn)50秒。超時(shí)后事務(wù)會(huì)回滾。
缺點(diǎn)：等待時(shí)間長(zhǎng)，業(yè)務(wù)響應(yīng)慢。

2. 死鎖檢測(cè)（推薦）參數(shù)innodb_deadlock_detect=ON（默認(rèn)開啟）。
InnoDB會(huì)主動(dòng)檢測(cè)死鎖，發(fā)現(xiàn)后立即回滾其中一個(gè)代價(jià)較小的事務(wù)。

如何避免死鎖？

1. 按固定順序訪問資源所有業(yè)務(wù)代碼都按照相同的順序獲取鎖。比如都先鎖id小的，再鎖id大的。

2. 減小鎖的粒度和持有時(shí)間

• 盡量使用行級(jí)鎖而不是表級(jí)鎖
• 事務(wù)中的SQL盡量少，盡快提交
• 避免在事務(wù)中進(jìn)行耗時(shí)操作（如調(diào)用外部接口）

3. 使用合理的索引如果查詢沒有走索引，InnoDB會(huì)進(jìn)行全表掃描并鎖住所有行，更容易死鎖。

4. 降低隔離級(jí)別如果業(yè)務(wù)允許，使用READ COMMITTED級(jí)別，沒有Gap Lock，死鎖概率降低。

5. 使用樂觀鎖代替悲觀鎖

-- 樂觀鎖方式
UPDATE product SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND version = 10;

如何排查死鎖？

-- 查看最近一次死鎖信息
SHOW ENGINE INNODB STATUS;

-- 查看當(dāng)前鎖等待
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

面試官：說說樂觀鎖和悲觀鎖的區(qū)別，以及各自的使用場(chǎng)景

回答：

悲觀鎖

悲觀鎖的思想是：每次操作數(shù)據(jù)時(shí)都認(rèn)為會(huì)有并發(fā)沖突，所以先加鎖再操作。

實(shí)現(xiàn)方式：數(shù)據(jù)庫的鎖機(jī)制，如SELECT FOR UPDATE

START TRANSACTION;
-- 先加排他鎖
SELECT stock FROM product WHERE id = 1 FOR UPDATE;
-- 業(yè)務(wù)邏輯處理
UPDATE product SET stock = stock - 1 WHERE id = 1;
COMMIT;

特點(diǎn)：

• 數(shù)據(jù)安全性高
• 性能開銷大（鎖等待、可能死鎖）
• 適合寫多讀少的場(chǎng)景

樂觀鎖

樂觀鎖的思想是：假設(shè)沖突很少發(fā)生，不加鎖，而是在更新時(shí)檢查數(shù)據(jù)是否被修改過。

實(shí)現(xiàn)方式：通常用版本號(hào)或時(shí)間戳

-- 1. 先查詢數(shù)據(jù)和版本號(hào)
SELECT id, stock, version FROM product WHERE id = 1;
-- 假設(shè)返回 stock=100, version=1

-- 2. 更新時(shí)檢查版本號(hào)
UPDATE product 
SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND version = 1;

-- 3. 檢查受影響行數(shù)
-- 如果是0，說明數(shù)據(jù)被其他事務(wù)修改過，需要重試

特點(diǎn)：

• 性能好（無鎖開銷）
• 可能需要重試機(jī)制
• 適合讀多寫少的場(chǎng)景

使用場(chǎng)景對(duì)比：

CAS（Compare And Swap）：

樂觀鎖的本質(zhì)其實(shí)就是CAS思想：比較并交換。在Java中，Atomic類就是用CAS實(shí)現(xiàn)的。在數(shù)據(jù)庫中，我們用version字段來模擬CAS操作。

五、SQL優(yōu)化篇

面試官：如何定位和優(yōu)化慢SQL？

回答：

這是一個(gè)很實(shí)際的問題，我按照實(shí)際工作中的流程來說：

第一步：開啟慢查詢?nèi)罩荆ㄎ宦齋QL

-- 查看慢查詢?nèi)罩臼欠耖_啟
SHOW VARIABLES LIKE 'slow_query_log';

-- 開啟慢查詢?nèi)罩?
SET GLOBAL slow_query_log = ON;

-- 設(shè)置慢查詢閾值，比如2秒
SET GLOBAL long_query_time = 2;

-- 查看慢查詢?nèi)罩疚募恢?
SHOW VARIABLES LIKE 'slow_query_log_file';

在生產(chǎn)環(huán)境中，也可以通過監(jiān)控系統(tǒng)（如Prometheus+Grafana）來發(fā)現(xiàn)慢SQL。

第二步：使用EXPLAIN分析執(zhí)行計(jì)劃

EXPLAIN SELECT * FROM user WHERE username = 'zhangsan';

重點(diǎn)關(guān)注這幾個(gè)字段：

• type：訪問類型，從好到差：system > const > eq_ref > ref > range > index > ALL

  • const：通過主鍵或唯一索引查詢，最多一行
  • ref：使用非唯一索引
  • range：索引范圍掃描
  • index：全索引掃描
  • ALL：全表掃描（最差，要優(yōu)化）

• key：實(shí)際使用的索引，NULL表示沒用索引

• rows：預(yù)估掃描的行數(shù)，越少越好

• Extra：額外信息

  • Using index：覆蓋索引，好
  • Using filesort：需要額外排序，需要優(yōu)化
  • Using temporary：使用臨時(shí)表，需要優(yōu)化

第三步：針對(duì)性優(yōu)化

1. 添加合適的索引

-- 為WHERE條件字段添加索引
CREATE INDEX idx_username ON user(username);

-- 為排序字段添加索引
CREATE INDEX idx_create_time ON user(create_time);

-- 聯(lián)合索引
CREATE INDEX idx_status_create_time ON order(status, create_time);

2. 優(yōu)化SQL寫法

-- 避免SELECT *
SELECT id, username, email FROM user WHERE id = 1;

-- 避免函數(shù)操作索引列
-- 不好
SELECT * FROM user WHERE YEAR(create_time) = 2024;
-- 好
SELECT * FROM user WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

-- 避免隱式類型轉(zhuǎn)換
-- phone是varchar類型
-- 不好
SELECT * FROM user WHERE phone = 13800138000;
-- 好
SELECT * FROM user WHERE phone = '13800138000';

3. 分頁優(yōu)化

-- 深分頁問題
SELECT * FROM order LIMIT 1000000, 10;  -- 需要掃描100萬+10行

-- 優(yōu)化方案：延遲關(guān)聯(lián)
SELECT * FROM order 
WHERE id >= (SELECT id FROM order ORDER BY id LIMIT 1000000, 1) 
LIMIT 10;

-- 或者：記錄上次查詢的最后一個(gè)ID
SELECT * FROM order WHERE id > 1000000 ORDER BY id LIMIT 10;

4. 批量操作

-- 不好：循環(huán)單條插入
INSERT INTO user VALUES (1, 'a');
INSERT INTO user VALUES (2, 'b');

-- 好：批量插入
INSERT INTO user VALUES (1, 'a'), (2, 'b'), (3, 'c');

-- 大批量數(shù)據(jù)建議分批，每批500-1000條

5. 合理使用JOIN

-- 小表驅(qū)動(dòng)大表
-- 如果department表小，用IN
SELECT * FROM employee WHERE dept_id IN (SELECT id FROM department);

-- 如果employee表小，用EXISTS
SELECT * FROM employee e 
WHERE EXISTS (SELECT 1 FROM department d WHERE d.id = e.dept_id);

-- JOIN時(shí)確保關(guān)聯(lián)字段有索引
-- 控制JOIN的表數(shù)量，一般不超過3張

面試官：你在實(shí)際項(xiàng)目中做過哪些SQL優(yōu)化？能舉個(gè)具體的例子嗎？

回答：

好的，我舉一個(gè)之前處理過的真實(shí)案例。

背景：有一個(gè)訂單列表查詢接口，在數(shù)據(jù)量達(dá)到500萬后，查詢變得很慢，平均響應(yīng)時(shí)間超過5秒。

原始SQL：

SELECT * FROM order 
WHERE user_id = 12345 
  AND status IN (1, 2, 3) 
  AND create_time >= '2024-01-01' 
ORDER BY create_time DESC 
LIMIT 0, 20;

排查過程：

• 首先用EXPLAIN分析，發(fā)現(xiàn)type是ALL，全表掃描，沒走索引
• 檢查發(fā)現(xiàn)表上只有主鍵索引，沒有針對(duì)查詢條件的索引

優(yōu)化方案：

第一步：添加聯(lián)合索引

CREATE INDEX idx_user_status_time ON order(user_id, status, create_time);

添加后，type變成了range，rows從500萬降到了幾千。

**第二步：優(yōu)化SELECT ***

-- 改成只查需要的字段
SELECT id, order_no, status, amount, create_time 
FROM order WHERE ...

第三步：考慮覆蓋索引

因?yàn)椴樵兊淖侄伪容^多，創(chuàng)建覆蓋索引不太現(xiàn)實(shí)。但對(duì)于一些只需要少量字段的查詢，可以考慮。

第四步：分頁優(yōu)化

原來的分頁到后面頁數(shù)時(shí)會(huì)很慢：

-- 原來的，第1000頁時(shí)要掃描20000行
SELECT ... LIMIT 19980, 20;

-- 優(yōu)化后，使用游標(biāo)分頁
SELECT ... WHERE id < 上一頁最后一條的id ORDER BY id DESC LIMIT 20;

優(yōu)化效果：

• 查詢時(shí)間從5秒+降到50毫秒以內(nèi)
• 支撐了日均百萬級(jí)的查詢量

總結(jié)幾個(gè)要點(diǎn)：

• 一定要有合適的索引
• 聯(lián)合索引要考慮查詢條件的順序和區(qū)分度
• 避免SELECT *
• 深分頁要特別處理
• 定期EXPLAIN分析關(guān)鍵SQL

六、其他高頻問題

面試官：MySQL主從復(fù)制的原理是什么？

回答：

MySQL主從復(fù)制是通過binlog來實(shí)現(xiàn)的，整個(gè)過程可以分為三個(gè)步驟：

第一步：Master記錄binlog主庫執(zhí)行寫操作時(shí)（INSERT/UPDATE/DELETE），會(huì)把變更記錄到binlog（二進(jìn)制日志）中。

第二步：從庫IO線程讀取binlog從庫有一個(gè)IO線程，它會(huì)連接到主庫，讀取主庫的binlog，然后寫入到從庫本地的relay log（中繼日志）中。

第三步：從庫SQL線程執(zhí)行relay log從庫還有一個(gè)SQL線程，它會(huì)讀取relay log中的事件，在從庫上重新執(zhí)行一遍，從而實(shí)現(xiàn)數(shù)據(jù)同步。

整個(gè)流程：

Master寫數(shù)據(jù) → 寫入binlog → 從庫IO線程讀取 → 寫入relay log → 從庫SQL線程執(zhí)行 → 數(shù)據(jù)同步

binlog的三種格式：

• STATEMENT：記錄SQL語句本身

  • 優(yōu)點(diǎn)：日志量小
  • 缺點(diǎn)：某些函數(shù)（如NOW()、UUID()）可能導(dǎo)致主從數(shù)據(jù)不一致

• ROW：記錄每行數(shù)據(jù)的變化

  • 優(yōu)點(diǎn)：不會(huì)出現(xiàn)數(shù)據(jù)不一致
  • 缺點(diǎn)：日志量大

• MIXED：混合模式，MySQL自動(dòng)選擇

  • 一般語句用STATEMENT，特殊情況用ROW

推薦使用ROW格式，雖然日志量大，但數(shù)據(jù)一致性有保證。

主從延遲的原因和解決方案：

原因：

• 主庫并發(fā)寫入，從庫單線程重放
• 網(wǎng)絡(luò)延遲
• 從庫機(jī)器性能差
• 大事務(wù)

解決方案：

• 使用并行復(fù)制（MySQL 5.6+）
• 從庫配置更好的硬件
• 避免大事務(wù)
• 讀寫分離時(shí)，對(duì)實(shí)時(shí)性要求高的讀走主庫

面試官：如何保證MySQL和Redis緩存的數(shù)據(jù)一致性？

回答：

這是一個(gè)經(jīng)典問題，我來說說常見的幾種策略：

策略一：Cache Aside Pattern（旁路緩存模式）

這是最常用的策略：

• 讀：先讀緩存，緩存沒有再讀數(shù)據(jù)庫，然后寫入緩存
• 寫：先更新數(shù)據(jù)庫，再刪除緩存

// 讀取
public User getUser(Long id) {
    User user = redis.get("user:" + id);
    if (user == null) {
        user = db.getUser(id);
        redis.set("user:" + id, user);
    }
    return user;
}

// 寫入
public void updateUser(User user) {
    db.update(user);
    redis.delete("user:" + user.getId());
}

為什么是刪除緩存而不是更新緩存？

• 更新緩存可能有并發(fā)問題：A先更新DB，B后更新DB，但B先更新緩存，A后更新緩存，導(dǎo)致緩存是舊數(shù)據(jù)
• 刪除更簡(jiǎn)單，讓下次讀取時(shí)重新加載

為什么是先更新DB再刪緩存，而不是先刪緩存再更新DB？

• 先刪緩存的問題：刪除緩存后、更新DB前，另一個(gè)請(qǐng)求讀到舊數(shù)據(jù)并寫入緩存，導(dǎo)致臟數(shù)據(jù)

策略一的問題：即使先更新DB再刪緩存，極端情況下仍可能不一致：

• 緩存剛好失效
• 請(qǐng)求A讀DB（舊值）
• 請(qǐng)求B更新DB（新值）
• 請(qǐng)求B刪除緩存
• 請(qǐng)求A把舊值寫入緩存

不過這種情況概率很低，因?yàn)閷懖僮魍ǔ１茸x操作慢。

策略二：延遲雙刪

為了解決上面的極端情況：

public void updateUser(User user) {
    redis.delete("user:" + user.getId());  // 第一次刪除
    db.update(user);
    Thread.sleep(500);  // 等待一段時(shí)間
    redis.delete("user:" + user.getId());  // 第二次刪除
}

延遲時(shí)間要大于一次讀操作的時(shí)間。

策略三：異步更新緩存（基于消息隊(duì)列或binlog）

寫操作 → 更新DB → 發(fā)送消息/監(jiān)聽binlog → 異步更新/刪除緩存

使用Canal監(jiān)聽binlog的方式更可靠，缺點(diǎn)是有一定延遲。

總結(jié)：

• 一般場(chǎng)景：Cache Aside Pattern + 設(shè)置緩存過期時(shí)間
• 要求較高：延遲雙刪
• 強(qiáng)一致性要求：分布式事務(wù)或不用緩存

面試官：最后一個(gè)問題，你覺得一個(gè)合格的索引應(yīng)該怎么設(shè)計(jì)？

回答：

這個(gè)問題很好，我總結(jié)一下索引設(shè)計(jì)的核心原則：

1. 選擇合適的字段建索引

• 高頻查詢條件字段：WHERE后面經(jīng)常出現(xiàn)的字段
• 排序字段：ORDER BY的字段
• 連接字段：JOIN ON的字段
• 高區(qū)分度字段：值的種類多，重復(fù)少（如用戶ID），而不是像性別這種

2. 聯(lián)合索引的設(shè)計(jì)原則

• 最左前綴：把最常用的查詢條件放在最左邊
• 區(qū)分度高的在前：區(qū)分度高的字段放前面，可以更快過濾數(shù)據(jù)
• 范圍查詢放最后：范圍查詢會(huì)使后面的字段無法使用索引
• 考慮覆蓋索引：盡量讓查詢的字段都在索引中

-- 假設(shè)查詢條件是：WHERE status = 1 AND type = 2 AND create_time > '2024-01-01'
-- 好的設(shè)計(jì)：idx_status_type_time(status, type, create_time)
-- 等值查詢?cè)谇?，范圍查詢?cè)诤?/pre>

3. 避免過多索引

• 索引不是越多越好，會(huì)影響寫入性能
• 一般一張表的索引控制在5個(gè)以內(nèi)
• 定期清理無用索引

4. 主鍵索引的設(shè)計(jì)

• 推薦使用自增ID，順序插入效率高
• 分布式場(chǎng)景用雪花算法，也是趨勢(shì)遞增的
• 避免使用UUID或隨機(jī)字符串，會(huì)導(dǎo)致頁分裂

5. 避免冗余索引

-- 冗余
INDEX idx_a (a)
INDEX idx_a_b (a, b)  -- idx_a是冗余的，idx_a_b可以覆蓋它

-- 不冗余
INDEX idx_a_b (a, b)
INDEX idx_b_a (b, a)  -- 順序不同，不冗余

6. 使用前綴索引壓縮長(zhǎng)字段

-- 對(duì)于很長(zhǎng)的字符串字段
CREATE INDEX idx_email ON user(email(10));  -- 只索引前10個(gè)字符

7. 實(shí)踐建議

• 先有業(yè)務(wù)查詢，再設(shè)計(jì)索引（不要憑空設(shè)計(jì)）
• 定期用EXPLAIN檢查關(guān)鍵SQL
• 用慢查詢?nèi)罩景l(fā)現(xiàn)問題
• 數(shù)據(jù)量小的表不需要太多索引

總結(jié) 

到此這篇關(guān)于MySQL存粹問題面試準(zhǔn)備總結(jié)的文章就介紹到這了,更多相關(guān)MySQL面試準(zhǔn)備內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論