10、MVCC實現原理？

事務ID（Transaction ID）：每個事務在執行時都會被分配一個唯一的事務ID，用于標識該事務的開始時間順序。事務ID是一個遞增的整數，隨著每個新事務的開始而遞增。

Undo日志（Undo Log）：在MySQL中，每次修改數據的操作都會被記錄在Undo日志中，包括修改前的數據內容和修改后的數據內容。這樣即使事務回滾，也可以通過Undo日志來還原數據。

Read View：讀視圖用于實現MVCC中讀取數據的一致性。在InnoDB中，每次啟動一個新事務時，都會創建一個Read View，其中包含了該事務啟動時正在運行的所有活躍事務的事務ID和已提交事務的ID范圍。

版本鏈和版本庫：為了支持MVCC，InnoDB使用了一種稱為undochain的數據結構來管理數據的不同版本。版本鏈中的每個版本都有一個指向前一版本的指針，形成了一個鏈表結構。當一行數據被更新時，會在版本鏈中創建一個新版本，并將舊版本標記為已刪除。

通過上述組件的協作，MySQL的MVCC機制實現了高效的并發控制。具體地，通過事務ID、Undo日志、讀視圖和版本鏈，MySQL能夠實現數據的多版本管理，實現事務的隔離性和一致性，同時支持高并發讀寫操作，提高數據庫性能和并發能力。

11、一條select語句發生了什么？

首先通過連接器簡建立連接，這個連接通過show processlist查看，TCP協議建立連接，有空閑時間過期等方式斷開連接。

其次通過語法樹解析SQL，語法樹不會去查表和檢查字段是否存在，只會檢查語法是否正確。

然后SQL執行器工作，這時候分三個階段

• 預處理階段：這時候就會看看字段表存在不存在

• 優化階段：explain可以查看策略，例如using index就是使用了覆蓋索引，all就是全表掃描，Using index condition用了索引下推

• 執行階段

總結：
執行一條 SQL 查詢語句，期間發生了什么？

• 連接器：建立連接，管理連接、校驗用戶身份；
• 查詢緩存：查詢語句如果命中查詢緩存則直接返回，否則繼續往下執行。MySQL 8.0 已刪除該模塊；
• 解析 SQL，通過解析器對 SQL 查詢語句進行詞法分析、語法分析，然后構建語法樹，方便后續模塊讀取表名、字段、語句類型；
• 執行 SQL：執行 SQL 共有三個階段：
  • 預處理階段：檢查表或字段是否存在；將 select * 中的 * 符號擴展為表上的所有列。
  • 優化階段：基于查詢成本的考慮， 選擇查詢成本最小的執行計劃；
  • 執行階段：根據執行計劃執行 SQL 查詢語句，從存儲引擎讀取記錄，返回給客戶端；

12、MySQL鎖有哪些？

• 全局鎖
• 表級鎖
  1. 表鎖
  2. 元數據鎖
  3. 意向鎖
• 行級鎖
  1. Record lock
  2. 間隙鎖
  3. 臨鍵鎖
  4. 插入意向鎖

13、講一下什么時候加什么鎖？全表掃描會發生什么呢？

14、什么是意向鎖，意向鎖什么時候生效？意向鎖會和行級鎖沖突嗎?

15、出現過數據庫死鎖嘛？舉個例子？為什么導致的死鎖？

兩個事務都對同一個范圍加間隙鎖，然后嘗試插入導致死鎖。

16、知道mysql的undo log日志嘛？有什么作用？

首先描述結構然后說一下保證數據回滾等作用，最后結合MVCC機制再講一下作用。

17、redo log干什么用的？熟悉Buffer Pool嘛？

如何回答？首先回答Buffer pool然后講一下WAL技術，最后引出redo log存在的必要性。因為buffer pool內存的內容丟失問題。

redo log 是物理日志，記錄了某個數據頁做了什么修改，比如對 XXX 表空間中的 YYY 數據頁 ZZZ 偏移量的地方做了AAA 更新，每當執行一個事務就會產生這樣的一條或者多條物理日志。

在事務提交時，只要先將 redo log 持久化到磁盤即可，可以不需要等到將緩存在 Buffer Pool 里的臟頁數據持久化到磁盤。

18、兩個日志的區別？

redo log 和 undo log 區別在哪？

這兩種日志是屬于 InnoDB 存儲引擎的日志，它們的區別在于：

• redo log 記錄了此次事務「完成后」的數據狀態，記錄的是更新之后的值；
• undo log 記錄了此次事務「開始前」的數據狀態，記錄的是更新之前的值；

19、redo log刷盤時機？

InnoDB 還提供了另外兩種策略，由參數 innodb_flush_log_at_trx_commit 參數控制，可取的值有：0、1、2，默認值為 1

當設置該參數為 1 時，表示每次事務提交時，都將緩存在 redo log buffer 里的 redo log 直接持久化到磁盤，這樣可以保證 MySQL 異常重啟之后數據不會丟失。

20、redo log寫滿了怎么辦？需要一直存著嘛？

重做日志文件組是以循環寫的方式工作的，從頭開始寫，寫到末尾就又回到開頭，相當于一個環形。所以 InnoDB 存儲引擎會先寫 ib_logfile0 文件，當 ib_logfile0 文件被寫滿的時候，會切換至 ib_logfile1 文件，當 ib_logfile1 文件也被寫滿時，會切換回 ib_logfile0 文件。

21、介紹binlog 日志？

binlog 文件是記錄了所有數據庫表結構變更和表數據修改的日志，不會記錄查詢類的操作，比如 SELECT 和 SHOW 操作。

• binlog 是追加寫，寫滿一個文件，就創建一個新的文件繼續寫，不會覆蓋以前的日志，保存的是全量的日志。

• redo log 是循環寫，日志空間大小是固定，全部寫滿就從頭開始，保存未被刷入磁盤的臟頁日志。

• binlog 用于備份恢復、主從復制；

• redo log 用于掉電等故障恢復。

22、如果不小心整個數據庫的數據被刪除了，能使用 redo log 文件恢復數據嗎？

不可以使用 redo log 文件恢復，只能使用 binlog 文件恢復。

因為 redo log 文件是循環寫，是會邊寫邊擦除日志的，只記錄未被刷入磁盤的數據的物理日志，已經刷入磁盤的數據都會從 redo log 文件里擦除。

binlog 文件保存的是全量的日志，也就是保存了所有數據變更的情況，理論上只要記錄在 binlog 上的數據，都可以恢復，所以如果不小心整個數據庫的數據被刪除了，得用 binlog 文件恢復數據。

23、redo log文件滿了會發生什么？

如果 write pos 追上了 checkpoint，就意味著 redo log 文件滿了，這時 MySQL 不能再執行新的更新操作，也就是說 MySQL 會被阻塞（因此所以針對并發量大的系統，適當設置 redo log 的文件大小非常重要），此時會停下來將 Buffer Pool 中的臟頁刷新到磁盤中，然后標記 redo log 哪些記錄可以被擦除，接著對舊的 redo log 記錄進行擦除，等擦除完舊記錄騰出了空間，checkpoint 就會往后移動（圖中順時針），然后 MySQL 恢復正常運行，繼續執行新的更新操作。

所以，一次 checkpoint 的過程就是臟頁刷新到磁盤中變成干凈頁，然后標記 redo log 哪些記錄可以被覆蓋的過程。

redo log 是循環寫的方式，相當于一個環形，InnoDB 用 write pos 表示 redo log 當前記錄寫到的位置，用 checkpoint 表示當前要擦除的位置。

24、Mysql主從復制怎么實現的？

• MySQL 主庫在收到客戶端提交事務的請求之后，會先寫入 binlog，再提交事務，更新存儲引擎中的數據，事務提交完成后，返回給客戶端“操作成功”的響應。
• 從庫會創建一個專門的 I/O 線程，連接主庫的 log dump 線程，來接收主庫的 binlog 日志，再把 binlog 信息寫入 relay log 的中繼日志里，再返回給主庫“復制成功”的響應。
• 從庫會創建一個用于回放 binlog 的線程，去讀 relay log 中繼日志，然后回放 binlog 更新存儲引擎中的數據，最終實現主從的數據一致性。（說白了就是執行更新過來的新信息，讓主從同步。因為只是同步了日志，這個日志還沒有執行）

25、Compact記錄行為什么要這么設計？

主要是因為「記錄頭信息」中指向下一個記錄的指針，指向的是下一條記錄的「記錄頭信息」和「真實數據」之間的位置，這樣的好處是向左讀就是記錄頭信息，向右讀就是真實數據，比較方便。

「變長字段長度列表」中的信息之所以要逆序存放，是因為這樣可以使得位置靠前的記錄的真實數據和數據對應的字段長度信息可以同時在一個 CPU Cache Line 中，這樣就可以提高 CPU Cache 的命中率。

26、buffer pool的刷盤時機？

后臺會有專門的線程每隔一段時間就把 flush 鏈表中的臟頁刷入磁盤中，刷新的速率取決于當前系統是否繁忙。在這樣的機制下，萬一系統崩潰，是會產生數據不一致的問題的，沒有刷入磁盤的數據就會丟失，而MySQL通過日志系統解決了這個問題。

InnoDB Buffer Pool的刷盤時機取決于以下幾個因素：

1. 臟頁比例：InnoDB引擎會將修改了而未被刷新到磁盤的數據頁稱為“臟頁”。當臟頁比例超過一定閾值時，InnoDB會啟動刷盤操作，將一部分臟頁寫回到磁盤。
2. Checkpoint：Checkpoint是InnoDB引擎用于控制臟頁比例的一種技術。通過定期向磁盤寫入Checkpoint，InnoDB可以使臟頁的數量保持在一個合理的范圍內。
3. LRU算法：LRU（Least Recently Used）算法是InnoDB Buffer Pool用于管理內存中數據頁的一種置換算法。當內存空間不足時，InnoDB會根據LRU算法選擇一些不常用的數據頁從內存中移除，以便為新的數據頁騰出空間。同時，如果這些數據頁是臟頁，InnoDB也會將它們刷入磁盤。

在我們開啟了慢 SQL 監控后，如果你發現**「偶爾」會出現一些用時稍長的 SQL**，這可能是因為臟頁在刷新到磁盤時可能會給數據庫帶來性能開銷，導致數據庫操作抖動。

如果間斷出現這種現象，就需要調大 Buffer Pool 空間或 redo log 日志的大小

27、為什么bin log要等一個事務提交了刷盤？

因為中間就提交會導致事務原子性分離。

28、為什么需要兩階段提交？

將 redo log 的寫入拆成了兩個步驟：prepare 和 commit，中間再穿插寫入binlog，具體如下：

• prepare 階段：將 XID（內部 XA 事務的 ID） 寫入到 redo log，同時將 redo log 對應的事務狀態設置為 prepare，然后將 redo log 持久化到磁盤（innodb_flush_log_at_trx_commit = 1 的作用）；
• commit 階段：把 XID 寫入到 binlog，然后將 binlog 持久化到磁盤（sync_binlog = 1 的作用），接著調用引擎的提交事務接口，將 redo log 狀態設置為 commit，此時該狀態并不需要持久化到磁盤，只需要 write 到文件系統的 page cache 中就夠了，因為只要 binlog 寫磁盤成功，就算 redo log 的狀態還是 prepare 也沒有關系，一樣會被認為事務已經執行成功；

磁盤 I/O 次數高：對于“雙1”配置，每個事務提交都會進行兩次 fsync（刷盤），一次是 redo log 刷盤，另一次是 binlog 刷盤

鎖競爭激烈：兩階段提交雖然能夠保證「單事務」兩個日志的內容一致，但在「多事務」的情況下，卻不能保證兩者的提交順序一致，因此，在兩階段提交的流程基礎上，還需要加一個鎖來保證提交的原子性，從而保證多事務的情況下，兩個日志的提交順序一致

MySQL 引入了 binlog 組提交（group commit）機制，當有多個事務提交的時候，會將多個 binlog 刷盤操作合并成一個，從而減少磁盤 I/O 的次數

29、MySQL 磁盤 I/O 很高，有什么優化的方法？

設置組提交。將 sync_binlog 設置為大于 1 的值（比較常見是 100~1000），表示每次提交事務都 write，但累積 N 個事務后才 fsync，相當于延遲了 binlog 刷盤的時機。但是這樣做的風險是，主機掉電時會丟 N 個事務的 binlog 日志。

將 innodb_flush_log_at_trx_commit 設置為 2。表示每次事務提交時，都只是緩存在 redo log buffer 里的 redo log 寫到 redo log 文件，注意寫入到「 redo log 文件」并不意味著寫入到了磁盤，因為操作系統的文件系統中有個 Page Cache，專門用來緩存文件數據的。

30、buffer pool 緩存了什么？

數據頁：包括表數據、索引數據等。當查詢需要訪問某個表或索引的數據時，如果數據頁已經在Buffer Pool中，則可以直接從內存中讀取，避免了頻繁的磁盤IO操作，加快數據訪問速度。

修改頁（Dirty Page）：當對表數據進行修改時，會將數據頁加載到Buffer Pool中，并在內存中進行修改，此時該數據頁會被標記為"臟頁"。之后會通過MySQL的后臺線程將臟頁刷新回磁盤，確保數據的一致性。

適應性哈希索引（Adaptive Hash Index）：Buffer Pool中還會緩存一些適應性哈希索引，在一定程度上提高一些常訪問數據的查詢速度。

通過緩存表數據和索引數據，Buffer Pool減少了對磁盤的IO操作，提高了數據庫的性能和響應速度。合理調整Buffer Pool的大小能夠有效地優化數據庫的性能，但需要根據實際情況評估系統的內存和磁盤資源，避免出現內存不足或過度消耗內存等問題。

31、如何管理buffer pool的臟頁和空閑頁？

為了能夠快速找到空閑的緩存頁，可以使用鏈表結構，將空閑緩存頁的「控制塊」作為鏈表的節點，這個鏈表稱為 Free 鏈表（空閑鏈表）。為了能快速知道哪些緩存頁是臟的，于是就設計出 Flush 鏈表，它跟 Free 鏈表類似的，鏈表的節點也是控制塊，區別在于 Flush 鏈表的元素都是臟頁。

32、如何提高mysql的緩存命中率？

InnoDB 對 LRU 做了一些優化，我們熟悉的 LRU 算法通常是將最近查詢的數據放到 LRU 鏈表的頭部，而 InnoDB 做 2 點優化：

• 將 LRU 鏈表 分為young 和 old 兩個區域，加入緩沖池的頁，優先插入 old 區域；頁被訪問時，才進入 young 區域，目的是為了解決預讀失效的問題。
• 當**「頁被訪問」且「 old 區域停留時間超過 innodb_old_blocks_time 閾值（默認為1秒）」**時，才會將頁插入到 young 區域，否則還是插入到 old 區域，目的是為了解決批量數據訪問，大量熱數據淘汰的問題。

33、為什么mysql沒有使用LRU算法？做了什么改進呢？

簡單的 LRU 算法并沒有被 MySQL 使用，因為簡單的 LRU 算法無法避免下面這兩個問題：

• 預讀失效；

但是可能這些被提前加載進來的數據頁，并沒有被訪問，相當于這個預讀是白做了，這個就是預讀失效。

如果使用簡單的 LRU 算法，就會把預讀頁放到 LRU 鏈表頭部，而當 Buffer Pool空間不夠的時候，還需要把末尾的頁淘汰掉。

怎么解決預讀失效而導致緩存命中率降低的問題？

我們不能因為害怕預讀失效，而將預讀機制去掉，大部分情況下，局部性原理還是成立的。

要避免預讀失效帶來影響，最好就是讓預讀的頁停留在 Buffer Pool 里的時間要盡可能的短，讓真正被訪問的頁才移動到 LRU 鏈表的頭部，從而保證真正被讀取的熱數據留在 Buffer Pool 里的時間盡可能長。、

那到底怎么才能避免呢？

MySQL 是這樣做的，它改進了 LRU 算法，將 LRU 劃分了 2 個區域：old 區域 和 young 區域。

劃分這兩個區域后，預讀的頁就只需要加入到 old 區域的頭部，當頁被真正訪問的時候，才將頁插入 young 區域的頭部。

• Buffer Pool 污染；

當某一個 SQL 語句掃描了大量的數據時，在 Buffer Pool 空間比較有限的情況下，可能會將 Buffer Pool 里的所有頁都替換出去，導致大量熱數據被淘汰了，等這些熱數據又被再次訪問的時候，由于緩存未命中，就會產生大量的磁盤 IO，MySQL 性能就會急劇下降，這個過程被稱為 Buffer Pool 污染。

那到底怎么才能避免呢？

LRU 鏈表中 young 區域就是熱點數據，只要我們提高進入到 young 區域的門檻，就能有效地保證 young 區域里的熱點數據不會被替換掉。MySQL 是這樣做的，進入到 young 區域條件增加了一個停留在 old 區域的時間判斷。只有同時滿足「被訪問」與「在 old 區域停留時間超過 1 秒」兩個條件，才會被插入到 young 區域頭部，這樣就解決了 Buffer Pool 污染的問題 。（理解：短期內的訪問是臨時的）