面經精選：資料庫高頻面試十問

1.InnoDB和MyISAM存儲引擎的區別？

MySQL 預設的存儲引擎是 InnoDB，它採用 B+Tree 作為索引的數據結構。

在創建表時，InnoDB 存儲引擎預設會創建一個主鍵索引，也就是聚簇索引，其它索引都屬於二級索引。

MySQL 的 MyISAM 存儲引擎支持多種索引數據結構，比如 B+ 樹索引、R 樹索引、Full-Text 索引。MyISAM 存儲引擎在創建表時，創建的主鍵索引預設使用的是 B+ 樹索引。

InnoDB存儲引擎有2個文件：Frm文件和Ibd文件。Frm文件是表的定義文件，而Ibd文件是數據和索引存儲文件（數據以主鍵進行聚集索引，把真正的數據保存在葉子節點中）。

MyISAM存儲引擎有3個文件：Frm文件、MYD文件和MYI文件。Frm文件是表的定義文件，MYD文件是數據文件（所有的數據保存在這個文件中），MYI文件是索引文件。

綜上所述，InnoDB 和 MyISAM 都支持 B+ 樹索引，但是它們數據的存儲結構實現方式不同。不同之處在於：

• InnoDB 存儲引擎：B+ 樹索引的葉子節點保存數據本身，即數據和索引都放在一個文件中；
• MyISAM 存儲引擎：B+ 樹索引的葉子節點保存數據的物理地址，即兩個文件分開存儲；

2.聚合索引和非聚合索引的區別，以及各自的優缺點？

InnoDB 存儲引擎根據索引類型不同，分為聚簇索引（上圖就是聚簇索引）和二級索引。它們區別在於，聚簇索引的葉子節點存放的是實際數據，所有完整的用戶數據都存放在聚簇索引的葉子節點，而二級索引的葉子節點存放的是主鍵值，而不是實際數據。

聚簇索引的優點：

• 當你需要取出一定範圍內的數據時，用聚簇索引比用非聚簇索引好
• 數據訪問更快，聚集索引將索引和數據保存在同一個B-Tree中，因此從聚集索引中獲取數據通常比在非聚集索引中查找要快。
• 當通過聚簇索引查找目標數據時理論上比非聚簇索引要快，因為非聚簇索引定位到對應主鍵時可能還要多一次回表操作
• 使用覆蓋索引掃描的查詢可以直接使用葉節點中的主鍵值

聚簇索引的缺點：

• 插入速度嚴重依賴於插入順序
• 更新主鍵的代價很高，因為將會導致被更新的行移動

非聚簇索引的優點：

• 插入和更新數據時不需要移動其他數據行，因此性能較好。
• 非聚簇索引能夠加速數據查詢，提高查詢速度。

非聚簇索引的缺點：

• 查詢非索引列的時候，需要進行二次查找，因此相對於聚簇索引，查詢速度較慢。
• 非聚簇索引的葉子節點不存儲數據行，因此對於需要查詢全部列的查詢語句，需要進行額外的I/O操作，降低查詢效率。

3.索引失效情景？

• 當我們使用左或者左右模糊匹配的時候，也就是 like %xx 或者 like %xx% 這兩種方式都會造成索引失效。
• 如果查詢條件中對索引欄位使用函數，就會導致索引失效。
• 在查詢條件中對索引進行表達式計算，也是無法走索引的。
• 如果索引欄位是字元串類型，但是在條件查詢中，輸入的參數是整型的話會發生隱式類型轉換，此時也是沒法走索引的。
• 如果不符合最左匹配原則，也就無法匹配上聯合索引，聯合索引就會失效。
• 在 WHERE 子句中，如果在 OR 前的條件列是索引列，而在 OR 後的條件列不是索引列，那麼索引會失效。

4.說一說B+T是一個怎麼樣的數據結構？相對於平衡二叉樹、紅黑樹、跳錶，以及BT來說，為什麼使用B+T？

• B+T一個節點有多個葉子節點，並且非葉子節點只存儲索引，葉子節點存放實際數據；葉子節點間用雙向鏈表維護，實現了高效的範圍查詢。

為什麼使用B+T

MySQL是基於磁碟的資料庫，磁碟的性能瓶頸在於磁碟IO，我們知道磁碟是按照頁進行存取的，每一頁是固定大小，比如16KB，對於平衡二叉樹和紅黑樹等，當數據量大的時候，它們的樹通常是很高的，每次查詢都只能取一個節點放入記憶體中查找，這樣就會增加IO次數，查詢效率低下。因此B樹一族的優化思路就是不再限制一個節點就只能有 2 個子節點，而是允許 M 個子節點 (M>2)，從而降低樹的高度。

B 樹的每一個節點最多可以包括 M 個子節點，M 稱為 B 樹的階，所以 B 樹就是一個多叉樹。每一次都取一批節點放入記憶體中查找，極大降低了磁碟IO。

相對於B樹，B+樹做了兩方面優化，一方面是非葉子節點只存放索引，另一方面是葉子節點間使用雙向鏈表維護。對於非葉子節點的優化，其實還是針對減少磁碟IO的進一步優化。磁碟IO每一頁的大小是固定的，我們希望一頁存取的元素個數越多，那麼每個元素的大小就得越小，因此B+樹的優化就是非葉子節點不再存放完整記錄，這樣節點的大小就極大減小了，磁碟IO次數也就減少了，進一步提升了查詢效率。針對於葉子節點用雙向鏈表維護，這種設計對範圍查找非常有幫助。而 B 樹沒有將所有葉子節點用鏈表串聯起來的結構，因此只能通過樹的遍歷來完成範圍查詢，這會涉及多個節點的磁碟 I/O 操作，範圍查詢效率不如 B+ 樹。

5.事務的隔離級別分別怎麼實現的？

• 對於「讀未提交」隔離級別的事務來說，因為可以讀到未提交事務修改的數據，所以直接讀取最新的數據就好了；

• 對於「串列化」隔離級別的事務來說，通過加讀寫鎖的方式來避免並行訪問；

• 對於「讀提交」和「可重覆讀」隔離級別的事務來說，它們是通過 *Read View 來實現的，它們的區別在於創建 Read View 的時機不同，大家可以把 Read View 理解成一個數據快照，就像相機拍照那樣，定格某一時刻的風景。 *** 「讀提交」隔離級別是在「每個語句執行前」都會重新生成一個 Read View，而「可重覆讀」隔離級別是「啟動事務時」生成一個 Read View，然後整個事務期間都在用這個 Read View。

  註意，執行「開始事務」命令，並不意味著啟動了事務。在 MySQL 有兩種開啟事務的命令，分別是：

  這兩種開啟事務的命令，事務的啟動時機是不同的：

• • 執行了 begin/start transaction 命令後，並不代表事務啟動了。只有在執行這個命令後，執行了第一條 select 語句，才是事務真正啟動的時機；
  • 執行了 start transaction with consistent snapshot 命令，就會馬上啟動事務。
  • 第一種：begin/start transaction 命令；
  • 第二種：start transaction with consistent snapshot 命令；

6.可重覆讀解決幻讀問題了嗎？

首先說一下什麼是幻讀。當同一個查詢在不同的時間產生不同的結果集時，事務中就會出現所謂的幻象問題。例如，如果 SELECT 執行了兩次，但第二次返回了第一次沒有返回的行，則該行是“幻像”行。

可重覆讀隔離級是由 MVCC（多版本併發控制）實現的，實現的方式是開始事務後（執行 begin 語句後），在執行第一個查詢語句後，會創建一個 Read View，後續的查詢語句利用這個 Read View，通過這個 Read View 就可以在 undo log 版本鏈找到事務開始時的數據，所以事務過程中每次查詢的數據都是一樣的，即使中途有其他事務插入了新紀錄，是查詢不出來這條數據的，所以就很好了避免幻讀問題。

MySQL 里除了普通查詢是快照讀，其他都是當前讀，比如 update、insert、delete，這些語句執行前都會查詢最新版本的數據，然後再做進一步的操作。

針對當前讀是仍存在幻讀問題的，因為行鎖並不影響其他事務的插入操作。因此Innodb 引擎為瞭解決「可重覆讀」隔離級別使用「當前讀」而造成的幻讀問題，就引出了間隙鎖，通過 記錄鎖+間隙鎖形成next-key lock（臨鍵鎖）的方式解決了幻讀。

但是可重覆讀隔離級別下仍然沒徹底解決幻讀問題，舉兩個例子：

• 對於快照讀， MVCC 並不能完全避免幻讀現象。因為當事務 A 更新了一條事務 B 插入的記錄，那麼事務 A 前後兩次查詢的記錄條目就不一樣了，所以就發生幻讀。
• 對於當前讀，如果事務開啟後，並沒有執行當前讀，而是先快照讀，然後這期間如果其他事務插入了一條記錄，那麼事務後續使用當前讀進行查詢的時候，就會發現兩次查詢的記錄條目就不一樣了，所以就發生幻讀。

所以，MySQL 可重覆讀隔離級別並沒有徹底解決幻讀，只是很大程度上避免了幻讀現象的發生。

要避免這類特殊場景下發生幻讀的現象的話，就是儘量在開啟事務之後，馬上執行 select ... for update 這類當前讀的語句，因為它會對記錄加 next-key lock，從而避免其他事務插入一條新記錄。

7.資料庫都有哪些鎖？

一、按鎖的區間劃分

1. 間隙鎖（gap locks） ：

   • 是開區間的，是一個在索引記錄之間的間隙上的鎖。
   • 作用是保證某個間隙內的數據在鎖定情況下不會發生任何變化。例如在預設隔離級別（可重覆讀）下，當使用非唯一索引搜索或沒有索引時等情況會產生間隙鎖。

2. 臨鍵鎖（next - key locks）

   • 是行鎖 + 間隙鎖，即臨鍵鎖是是一個左開右閉的區間。
   • InnoDB 的預設事務隔離級別是可重覆讀，在這種級別下，如果使用特定語句（如select... for update 等）會觸發臨鍵鎖，可以防止幻讀。

二、按鎖的粒度劃分

1. 表級鎖（table - level lock）

   • 直接給整個表添加鎖。如 select * from student where name = 'tom' for update（InnoDB 在不通過索引檢索數據時也是表鎖 ）。
   • 開銷小，加鎖快；不會出現死鎖；但鎖定粒度大，發生鎖衝突的概率最高，併發度最低。
   • MyISAM在執行查詢語句（select）前，會自動給涉及的所有表加讀鎖，在執行更新操作（update、delete 、insert 等）前，會自動給涉及的表加寫鎖。

2. 行級鎖（record locks）

   • InnoDB中給指定的行添加鎖：如 select * from student where id > 10 for update 。
   • 是通過給索引上的索引項加鎖來實現的，如果沒有索引則會類似表鎖（比如通過隱藏的聚簇索引） 。
   • 行鎖的劣勢是開銷大、加鎖慢、會出現死鎖；優勢是鎖的粒度小，發生鎖衝突的概率低；處理併發的能力強。

3. 頁級鎖

   • 頁級鎖的顆粒度介於行級鎖與表級鎖之間。
   • 主要應用於BDB存儲引擎（現在使用相對較少）。

三、按鎖級別劃分

1. 共用鎖（share lock，即 S 鎖）

   • 又稱讀鎖，允許一個事務去讀取一行，阻止其他事務獲得相同數據集的排它鎖。若事務 t 對數據對象 a 加上 S 鎖，則事務 t 可以讀 a，但不能修改 a，其他事務只能對再對 a 加 S 鎖，而不能加 X 鎖 ，直到 t 釋放 a 上的鎖。這保證了其他事務可以讀 a，但在釋放 a 上的 S 鎖之前不能對 a 做任何修改。

2. 排它鎖 / 獨占鎖（exclusive lock，即 X 鎖）

   • 又稱寫鎖，允許獲取排它鎖的事物更新數據，阻止其他事務取得相同的數據集共用讀鎖和排它寫鎖。若事務 t 對數據對象 a 加上 X 鎖，事物 t 可以讀 a 也可以修改 a，其他事務不能再對 a 加任何鎖，直到 t 釋放 a 上的鎖。

3. 意向鎖

   • 意向共用鎖（IS）：表示事務準備給數據行加入共用鎖，也就是說一個數據行加共用鎖前必須先取得該表的 IS 鎖；
   • 意向排他鎖（IX）：類似上面，表示事務準備給數據行加入排他鎖，說明事務在一個數據行加排他鎖前必須先取得該表的 IX 鎖。意向鎖是 InnoDB 自動加的，不需要用戶干預。

四、按加鎖方式分類

1. 自動鎖（automatic locks） ：資料庫自動根據操作和場景加的鎖。
2. 顯示鎖（lock tables） ：通過特定的命令（如lock tables... ）手動顯示加的鎖。

五、按鎖的使用方式分類

1. 樂觀鎖（optimistic lock） ： 並不是真正的鎖機制，通常是通過在表中增加版本號等欄位來實現，在更新時檢查版本等標識是否符合預期來判斷是否發生併發衝突等。
2. 悲觀鎖（pessimistic lock） ：通過實實在在的鎖來控制併發訪問，如前面提到的共用鎖、排它鎖等都屬於悲觀鎖策略。

六、其他特殊鎖

1. 死鎖：兩個或多個事務相互等待對方持有的資源，從而導致都無法繼續執行的情況。

2. 全局鎖：

   • 對整個資料庫實例加鎖，讓整個資料庫處於只讀狀態。如MySQL提供了flush tables with read lock(ftwrl) 命令加全局讀鎖，加鎖之後整個資料庫實例處於只讀狀態，相關數據操作命令都會被阻塞。一般僅用於全庫備份等特殊場景（且在InnoDB等支持一致性讀的引擎中全庫備份不一定需要 ）。

8.說一說意向鎖？

首先為什麼需要引入意向鎖呢？

我們在引入意向鎖前有這樣一個場景：其他事務對當前數據表進行了鎖行操作（獨占鎖），而我們當前事務需要對該表加表級鎖（獨占表鎖）。此時我們能加表鎖的前提是當前表不存在獨占鎖，就需要遍歷表裡所有記錄，查看是否有記錄存在獨占鎖，這樣效率會很慢。那麼有了「意向鎖」，由於在對記錄加獨占鎖前，先會加上表級別的意向獨占鎖，那麼在加「獨占表鎖」時，直接查該表是否有意向獨占鎖，如果有就意味著表裡已經有記錄被加了獨占鎖，這樣就不用去遍歷表裡的記錄。

那麼什麼是意向鎖呢

• 在使用 InnoDB 引擎的表裡對某些記錄加上「共用鎖」之前，需要先在表級別加上一個「意向共用鎖」；
• 在使用 InnoDB 引擎的表裡對某些紀錄加上「獨占鎖」之前，需要先在表級別加上一個「意向獨占鎖」；

也就是，當執行插入、更新、刪除操作，需要先對錶加上「意向獨占鎖」，然後對該記錄加獨占鎖。

而普通的 select 是不會加行級鎖的，普通的 select 語句是利用 MVCC 實現一致性讀，是無鎖的。

意向鎖帶來的影響有哪些呢？（優點）

意向共用鎖和意向獨占鎖是表級鎖，不會和行級的共用鎖和獨占鎖發生衝突，而且意向鎖之間也不會發生衝突，只會和共用表鎖（lock tables ... read）和獨占表鎖（lock tables ... write）發生衝突。因此引入了意向鎖不僅解決了上述問題，而且併發性能也是很高的。

9.Redo Log 和 Binlog 的區別？

Redo Log 和 BinLog 是 MySQL 中兩種重要的日誌，它們的區別如下：

• 功能：

  • Redo Log 主要用於實現事務的持久性，確保在資料庫發生故障（如停電、系統崩潰等）時，能夠恢復未完成事務對數據的修改，從而保證數據不會丟失。
  • BinLog 則用於記錄資料庫的變更操作，包括數據的插入、更新和刪除等，主要用於數據備份、主從複製和數據恢復等場景。

• 寫入方式：

  • Redo Log 是迴圈寫入的，空間固定大小，寫滿時會覆蓋舊的日誌。
  • BinLog 可以通過配置來控制文件大小，當達到一定大小或其他條件時，會生成新的文件進行續寫。

• 存儲位置：

  • Redo Log 是存儲在 InnoDB 存儲引擎特有的文件中。
  • BinLog 是存儲在伺服器級別的日誌文件中。

• 寫入時機：

  redo log寫入時機（InnoDB存儲引擎層面）

  在事務執行過程中：

  1. 事務中的操作會先在記憶體中的redo log buffer（重做日誌緩衝）中記錄相應的redo log 條目。 以下是後續幾種觸發刷到磁碟（redo log文件）的情況：

  • 由參數innodb_flush_log_at_trx_commit控制：

    • 當設置為 1（預設）：每次事務提交時都將redo log buffer中的redo log強制持久化到磁碟。
    • 當設置為 0：每次事務提交的時候都只是把redo log留在redo log buffer中；後臺線程每隔1s進行一次刷盤操作，但如果MySQL進程崩潰可能丟失1s內的事務日誌 。
    • 當設置為 2 ：每次事務提交時都只是把redo log寫到page cache（文件系統緩存），由操作系統決定何時刷到磁碟；如果MySQL進程崩潰，操作系統不崩潰則數據不會丟失，但如果系統崩潰則可能丟失1s內的數據（因為後臺線程1s刷新一次）。

  • 當redo log buffer占用的空間即將達到innodb_log_buffer_size一半的時候，後臺線程會主動寫盤（write，沒有fsync）。

  • 並行的事務提交的時候，順帶將這個事務的redo log buffer持久化到磁碟（事務a執行一半，部分redo log到buffer中；事務b提交，且innodb_flush_log_at_trx_commit設置為1或2，會把redo log buffer里的log全部持久化到磁碟中）。

  binlog寫入時機

  1. 在事務執行期間：

     • 先把日誌寫到binlog cache（每個線程都有自己的binlog cache，binlog cache大小由參數binlog_cache_size控制，如果超過了這個大小就要暫存磁碟）。

  2. 在事務提交時：

     • 執行器把binlog cache里完整的事務寫入binlog文件中，並清空binlog cache。

  • 由參數sync_binlog控制真正刷盤（write後進行fsync）的時機：

    • 當sync_binlog = 0 時，每次提交事務都只write，不fsync；如果操作系統崩潰，可能丟失部分事務的binlog 。
    • 當sync_binlog = 1 時，每次提交事務都會執行fsync，保證binlog完整持久化；這是最安全但性能相對低的方式。
    • 當sync_binlog = n（n > 1）時，表示每次提交事務都write，但累積n個事務後才fsync；這種方式可以減少磁碟IO操作次數提升性能，但如果主機發生異常重啟，會丟失最近n個事務的binlog日誌。

10.Binlog 有哪幾種日誌格式？

1. statement（基於語句的複製，SBR ）

優點：

• 每一條會修改數據的 SQL 語句都會被記錄在 binlog 中，日誌量相對較小（如果不是批量、整表等操作情況下），相比於 row 格式在很多常規單一語句執行時節省空間和 I/O 。
• 直觀顯示執行的語句，方便理解。

缺點：

• 一些語句在主從環境下可能出現不一致結果，例如使用了不確定函數（如UUID() 每次生成不同值 ）等情況。
• 特定存儲過程、函數、觸發器調用和觸發在從庫可能無法正確複製。
• 對於有依賴上下文的語句（如依賴於當前數據分佈等）可能導致主從結果不同。

2. row（基於行的複製，RBR）

優點：

• 不記錄 SQL 語句上下文信息，只記錄數據行的變化情況（哪條記錄被修改以及被修改成什麼樣等）。
• 能準確地進行數據複製，不會出現由於語句邏輯在主從不同環境下的不一致性問題。

缺點：

• 產生的日誌內容會非常多，特別是在執行批量更新、整表刪除、alter 表等操作時，日誌量極大，會造成大量的磁碟 I/O 開銷。
• 查看日誌相對不夠直觀，不能直接看出執行的語句邏輯。

3. mixed（混合模式，MBR）

從 MySQL 5.1.8 版本開始推出。

特點和運行機制：

• 是 statement 和 row 的混合體。
• 系統自動判斷語句該用 statement 還是 row 格式來記錄日誌。一般情況下，常規的語句修改使用 statement 格式保存 binlog ；對於一些 statement 無法準確完成主從複製的複雜操作（如函數使用等導致潛在不一致的語句 ）則採用 row 格式保存 binlog 。
• 可以一定程度上兼顧日誌量和數據複製的準確性。但有時在切換格式時可能會存在一些邊界情況需要關註和處理（比如切換瞬間數據的一致性保障等 ）。

歡迎關註 ❤

我們搞了一個免費的面試真題共用群，互通有無，一起刷題進步。

沒準能讓你能刷到自己意向公司的最新面試題呢。

感興趣的朋友們可以加我微信：wangzhongyang2024，備註：博客園面試群。