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

王中阳Go1發表於2024-08-12
資料庫面試

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，備註：部落格園面試群。

相關文章