每日學習Java之一萬個為什么？[MySQL面試篇]

分析SQL語句執行流程中遇到的問題

前言
1 MySQL是怎么在一臺服務器上啟動的
2 MySQL主庫和從庫是同時啟動保持Alive的嗎？
3 如果不是主從怎么在啟動的時候保證數據一致性
4 ACID原則在MySQL上的體現
5 數據在MySQL是通過什么DTO實現的
6 客戶端怎么與MySQL Server建立連接，有哪些建立連接的方式，默認是什么，可以選什么，有哪些應用場景
7.SQL組件接受的SQL是什么形式？為什么單一一條SQL不做任何處理就被MySQL認為是一個事務？
8.查詢后的結果是由哪個組件以什么形式返回？
9.解析器是怎么解析的，解析器會調用API嗎？
10.優化器是怎么優化的？會調用API嗎？
11.SQL報錯機制會在哪些區域出現？以什么形式發給客戶端？以什么形式寫到哪個日志中？
12.執行器本質上是什么？
13 MySQL內部有哪些線程？怎么分類？
14 MySQL進程包含哪些內容
15 執行器會將sql提交給引擎還是調用引擎，又是怎么調用的
16 buffer pool區域中的數據頁有多少，以什么結構存儲，臟頁呢？
17 buffer pool 中的數據頁怎么更新的，淘汰后的數據頁由誰清理
18 后臺線程對臟頁刷盤是怎么實現的
19 sql語句中加鎖在哪個部分實現的
20 事務在engine中怎么實現
21 MVCC怎么實現
22 redolog分為哪幾塊內容？有什么作用
23 binlog呢
24 undolog 呢
25 數據寫磁盤怎么實現的，怎么傳輸的，怎么保證一致性的
26 MySQL斷開連接可以從線程和客戶端區分嗎
27 MySQL進程結束會執行哪些持久化操作

前言

面試想要過關斬將，你就必須捫心自問，問得越多問的越細，才能披荊斬棘。盔甲若是漏洞百出，不被戳死才怪。

下列問題為作者提出，回答搜集而來。
由于作者并沒有系統化從零開始學習MySQL，所以可能某些非常基礎的問題并不了解。

歡迎讀者留言比較重要的基礎，wink👍

1 MySQL是怎么在一臺服務器上啟動的

MySQL通過配置文件：my.cnf my.ini 來指定各種參數，如端口號、數據目錄等。
初始化數據庫：首次啟動的時候，MySQL需要初始化系統表（這里指定的是系統表空間嗎）
啟動系統，生成日志

2 MySQL主庫和從庫是同時啟動保持Alive的嗎？

為了實現數據同步，通過binlog+并行復制維持數據一致性，主庫應該首先啟動并正常運行，然后從庫再啟動并連接到主庫。

3 如果不是主從怎么在啟動的時候保證數據一致性

如果不是主從庫，沒法保證，可以自己設置一些通信維護。
如果沒有同時啟動，其實無所謂。
保證數據一致性，主從之間首先需要通信，分為三類異步、同步、半同步。由于是主庫寫日志，等待從庫響應。所以主庫到底等不等從庫是一個影響性能的問題。異步-半同步-同步，一致性會越來越高，期望響應時間越來越長。（那么這里的sql寫日志都是事務寫進去的嗎）
一句話概括：主庫提交會寫binlog，會由一個dump線程監聽binlog文件的變更，如果有更新會推送更新時間到從庫，從庫接收到事件后會拉取數據，有一個IO線程將binlog傳過來的數據寫到自己的relaylog中，慢慢消化。所以，我們先更新的是事務語句，至于執行則是慢慢來。
5.6庫級別并行復制：
5.7 Group Commit 級別并行復制
logical_clock 邏輯時鐘復制
Write Set復制

4 ACID原則在MySQL上的體現

A:MySQL使用BEGIN /COMMIT /ROLLBACK 來管理事務
C：通過約束和觸發器確保數據庫的狀態始終符合預期
I：支持四種隔離級別，讀未提交，讀已提交，可重復讀，串行化，通過鎖定機制和MVCC實現
D：一旦事務提交，數據就會永久保存，即使發生系統崩潰也不會丟失。MySQL使用重做日志和雙寫緩沖區來實現持久性

5 數據在MySQL是通過什么DTO實現的

6 客戶端怎么與MySQL Server建立連接，有哪些建立連接的方式，默認是什么，可以選什么，有哪些應用場景

TCP IP 通過網絡地址和端口連接到MySQL服務器，適用于遠程連接
Unix Socket連接：在本地Linux/Unix系統上，可以通過Unix域套接字連接到MySQL服務器。這種方式比TCP IP更快，因為它避免了網絡層的開銷

jdbc:mysql://database_name?socket=/temp/mysql.sck

命名管道：僅適用于win 本地連接
共享內存：另一種win特有的連接方法
默認使用TCP IP

7.SQL組件接受的SQL是什么形式？為什么單一一條SQL不做任何處理就被MySQL認為是一個事務？

MySQL接收到的SQL是標準的SQL語句，通常以文本的形式發給服務層

在MySQL中，默認情況下，每條單獨提交的SQL語句都被視為一個獨立的事務。這是因為autocommit模式下，每條語句執行完畢后會自動提交事務。如果autocommit被關閉，需要顯式的使用 BEGIN 和 COMMIT 提交事務

8.查詢后的結果是由哪個組件以什么形式返回？

執行器也就是sql線程生成結果集，通過網絡協議返回給客戶端

執行器：執行查詢并生成結果集 MYSQL_RES結構
網絡層：將結果集打包成適當的格式（如二進制或文本），并通過連接通道返回給客戶端。

typedef struct st_mysql_res
{
my_ulonglong row_count;
unsigned int field_count,current_field;
MYSQL_FIELD *fields;
MYSQL_DATA *data;
MYSQL_ROWS *data_cursor;
MEM_ROOT field_alloc;
MYSQL_ROW row;
MYSQL_ROW current_row;
unsigned long *lengths;
MYSQL *handle;
my_bool eof;
}MYSQL_REStypedef struct st_mysql_rows 
{struct st_mysql_rows *next;	//列表的行MYSQL_ROW data;
} MYSQL_ROWS;   //mysql的數據的鏈表節點。可見mysql的結果集是鏈表結構typedef struct st_mysql_data 
{my_ulonglong rows;unsigned int fields;MYSQL_ROWS *data;MEM_ROOT alloc;
} MYSQL_DATA;   // 數據集的結構typedef struct st_mysql_field 
{char *name;					// 列名稱 char *table;	                //如果列是字段，列表char *def;	                //默認值(由mysql_list_fields設置)enum enum_field_types type;	//類型的字段。Se mysql_com。h的類型unsigned int length;	        //列的寬度unsigned int max_length;	    //選擇集的最大寬度unsigned int flags;	        //Div標記集unsigned int decimals;	    //字段中的小數位數
} MYSQL_FIELD;  //列信息的結構typedef struct st_used_mem      //結構為once_alloc
{	struct st_used_mem *next;   	//下一個塊使用unsigned int left;	        //記憶留在塊unsigned int size;	        //塊的大小
} USED_MEM;     //內存結構typedef struct st_mem_root 
{USED_MEM *free;USED_MEM *used;USED_MEM *pre_alloc;unsigned int	min_malloc;unsigned int	block_size;void (*error_handler)(void);
} MEM_ROOT;  //內存結構

可以看到MySQL是用C++/C 開發的

9.解析器是怎么解析的，解析器會調用API嗎？

詞法分析：將SQL語句分解為一個個token
語法分析：tokens轉換語法樹
語義分析
解析器本身并不調用外部API，但他可能會調用內部函數或者模塊來完成某些任務，例如：
元數據訪問：訪問系統表或緩存中的元數據，以驗證表和列的存在性。
權限檢查：調用權限管理模塊，確保用戶有足夠的權限執行該操作

10.優化器是怎么優化的？會調用API嗎？

查詢重寫：對查詢初步重寫，以簡化查詢結構
統計信息收集：收集表的統計信息（例如行數、索引選擇性等），用于生成最優執行計劃。
生成候選計劃：基于不同的訪問路徑（如全表掃描、索引掃描），生成多個候選執行計劃（生成策略？）
選擇最佳計劃：評估每個候選計劃的成本（評估算法？），選擇成本最低的計劃作為最終執行計劃
優化器內部統計信息模塊：獲取表的統計信息（包含什么？）
代價模型：計算成本（理論支持？）
訪問路徑模塊：確定可用的訪問路徑（索引、表掃描等）

11.SQL報錯機制會在哪些區域出現？以什么形式發給客戶端？以什么形式寫到哪個日志中？

報錯機制出現的區域：

解析階段：SQL語法錯誤或未知表/列名
優化階段：無法生成有效的執行計劃
執行階段：運行時錯誤，如違反約束、超時等
網絡傳輸階段：連接斷開或其他通信錯誤

發給客戶端的形式：

錯誤代碼：一個唯一的數字標識。
錯誤消息：詳細的描述信息，幫助理解錯誤原因。

{"code":1054,"message":"Unknown column 'nonexistent_column'in 'field list'"
}

寫入日志的形式：

錯誤日志：記錄所有嚴重的錯誤和警告，默認位于/var/log/mysql/error.log 或安裝目錄下的data文件夾中。
慢查詢日志：記錄執行時間超過閾值的查詢，默認位于/var/log/mysql/slow-query.log

2025-02-27T11:41:23.123456Z 0 [ERROR] [MY-01054] Unknown column 'nonexistent_column' in 'field list'

12.執行器本質上是什么？

執行器是MySQL查詢處理過程中實際執行查詢計劃的部分。它根據優化器生成的執行計劃，調用存儲引擎接口來訪問和修改數據。

執行器的主要職責：

1.物理操作執行：根據執行計劃中的物理操作（如表掃描、索引查找等）執行相應的數據庫操作。
2.數據獲取與處理：從存儲引擎中獲取數據，并進行必要的過濾、排序、聚合等操作。
3.結果集生成：將處理后的數據組織成結果集，準備返回給客戶端。

執行器的工作流程：
1.初始化執行計劃：加載執行計劃并準備執行環境。
2.執行物理操作：按順序執行各個物理操作步驟。
3.返回結果

13 MySQL內部有哪些線程？怎么分類？

1.主線程（Main Thread）

職責：負責啟動和管理其他的線程
實例：mysql進程的主線程

2.連接處理線程（Connection Threads）

職責：處理客戶端連接請求，每個客戶端連接對應一個獨立的線程
實例：thd線程對象，用于處理每個客戶端連接

3.后臺線程（Background Threads）

職責：執行各種后臺任務，如日志刷新、數據頁刷盤等。
示例：InnoDB緩沖池線程：負責緩沖池管理和臟頁刷盤。Binlog Dump線程：監聽binlog 發送給從庫。Purge線程：負責清理不再需要的undo日志

4.IO線程（I/O Threads）

職責：處理與存儲設備的IO操作
示例：Redo Log線程：負責寫入重做日志。 Binlog線程：負責寫入二進制日志。

5.事務處理線程（Transaction Handing Threads）

職責：管理事務的開始、提交和回滾操作。
示例：事務管理線程，負責協調事務的狀態。

14 MySQL進程包含哪些內容

MySQL進程（mysqld）包含多個組件和模塊，主要分為以下幾個部分：

1.服務器核心 Server Core
1. 存儲引擎 Strorage Engines
3.緩存和緩沖區（Caches and Buffers）
- Buffer Pool：用于緩存數據和索引數據。
- Query Cache：緩存查詢結果以提高查詢效率（讀緩存已被移除）
日志系統（Logging System）
- Binlog：用于主從復制和數據備份，記錄所有更改操作。
- Undo Log：用于事務回滾，記錄事務的反向操作
- Redo Log：用于崩潰恢復，記錄所有未提交的事務
網絡層（Network Layer）
- 處理客戶端連接、數據傳輸和協議解析。
安全管理（Security Management）
- 執行各種后臺任務，如日志刷新、數據頁刷盤等。

15 執行器會將sql提交給引擎還是調用引擎，又是怎么調用的

執行器會通過調用存儲引擎的接口來執行具體的數據庫操作

調用方式：

接口調用：執行器通過調用存儲引擎提供的接口（如InnoDB的API）來執行具體的操作。
物理操作執行：根據優化器生成的執行計劃，執行器調用相應的存儲引擎接口來訪問和修改數據。

16 buffer pool區域中的數據頁有多少，以什么結構存儲，臟頁呢？

數據頁數量：

大小可配置：Buffer Pool的大小可以通過配置文件中的innodb_buffer_pool_size參數設置，默認值通常為系統內存的70%-80%。
每頁大小：默認情況下，每頁大小為16KB。

存儲結構：

LRU鏈表：Buffer Pool 使用LRU（Least Recently Used）鏈表來管理數據也。最近使用的頁面位于鏈表前端，較少使用的頁面位于鏈表末端。
Flush鏈表：臟頁（已修改但尚未寫入磁盤的頁面）會被放入Flush鏈表中，等待刷盤

臟頁：

定義：臟頁是指已經被修改但還寫回到磁盤的數據頁。
存儲位置:臟頁同樣存儲在Buffer Pool中，并被標記為臟頁。
管理：臟頁通過Flush鏈表管理，定期或在特定條件下（如內存不足或打到一定閾值）被刷回磁盤。

17 buffer pool 中的數據頁怎么更新的，淘汰后的數據頁由誰清理

數據頁更新：

讀取當前數據頁：當需要訪問某個數據頁時，如果該頁不在Buffer Pool中，則從磁盤加載到Buffer Pool。
修改數據頁：對數據頁進行修改后，將其標記為臟頁。
寫回磁盤：臟頁會在適當的時候（如內存不足或達到一定的閾值）通過后臺線程寫回到磁盤。

淘汰后的數據頁清理：

LRU鏈表：當Buffer Pool 滿時，使用LRU算法淘汰最近最少使用的頁面
清理過程：
- 如果被淘汰的頁面是干凈的（未修改），可以直接丟棄。
- 如果被淘汰的頁面是臟頁，則必須先將其寫回磁盤（通過Flush鏈表管理）。

18 后臺線程對臟頁刷盤是怎么實現的

后臺線程通過以下幾種機制實現臟頁的刷盤：
1.定時刷盤：

InnoDB自動刷新：InnoDB會定期檢查臟頁的比例，如果超過設定的閾值（如innodb_max_dirty_pages_pct），則觸發自動刷新操作。

2.后臺線程：

Page Cleaner線程：負責將臟頁寫回到磁盤。它會定期檢查Flush鏈表中的臟頁，并將這些臟頁回到磁盤。

3.事務提交：

同步刷盤：在某些情況下（如事務提交），為了保證數據一致性，需要立即刷盤。此時會調用fsync（）函數確保數據已經寫入磁盤。

4.內存壓力：

當Buffer Pool內存不足時，會優先淘汰臟頁，并將其寫回磁盤。

19 sql語句中加鎖在哪個部分實現的

SQL語句的加鎖操作主要在執行器和存儲引擎中實現：

1.解析階段：

解析器解析SQL語句，識別出需要加鎖的對象（如表、行等）

2.執行器：

執行器根據解析結果，在執行過程中調用存儲引擎的加鎖接口，對相關對象加鎖。

3.存儲引擎：

存儲引擎實現具體的加鎖機制。例如，InnoDB支持行級鎖（共享鎖，排他鎖）和表級鎖。

SELECT * FROM users WHERE id = 1 FOR UPDATE;

執行器在執行這條查詢時，會調用InnoDB的加鎖接口，對users表中id=1的行加排它鎖

20 事務在engine中怎么實現

事務在存儲引擎中的實現主要包括以下幾個方面：

1.事務管理器：

負責事務的生命周期，包括開始、提交、回滾。

2.日志系統：

Redo Log：記錄所有未提交的事務操作，用于崩潰恢復。
Undo Log：記錄事務的反向操作

3.并發控制：

MVCC（多版本并發控制）：支持高并發下的讀寫操作，避免鎖沖突
鎖機制：提供行級鎖和表級鎖，確保事務的隔離性。

4.持久化：

雙寫緩沖區（Doublewrite Buffer）：確保數據頁在寫入磁盤時的一致性。
刷盤操作：通過后臺線程定期將臟頁協會到磁盤里。

21 MVCC怎么實現

MVCC，通過維護數據不同的版本來支持高并發下的讀寫操作。其主要實現機制如下：

隱藏列：每個數據行包含兩個隱藏列：DB_TRX_ID 事務ID DB_ROLL_PTR 指向undo的指針。
快照讀：讀操作（如SELECT）不會阻塞寫操作，而是讀取數據的歷史版本。讀操作會根據當前事務的Read View來決定讀取哪個版本的數據
寫操作：寫操作會在新版本的數據上進行操作，并記錄undo日志以便回滾。
Undo日志：記錄事務的反向操作，用于事務回滾和舊版本構建

假如有一個事務在時間點T1插入一條記錄，另一個事務B在時間點T2更新了該記錄。事務C在T3時間點進行讀取操作時，會讀取到T2時間點之前的數據版本。

22 redolog分為哪幾塊內容？有什么作用

Redo Log 是 InnoDB 存儲引擎用于崩潰恢復的日志系統，其主要分為以下幾個部分：

1.Log Blocks：
- Redo Log被劃分為多個固定大小的塊（通常是512字節），每個塊稱為一個 Log Block。
2.Log Sequence Number（LSN）：
- 每條日志記錄都有一個唯一的LSN，表示日志記錄的順序號。
3.Checkpoint
- Checkpoint 是 Redo Log中的一個特殊標記，表示已成功寫入磁盤的數據頁。它用于加速崩潰恢復的過程。
崩潰恢復：在MySQL崩潰重啟時，通過Redo Log恢復未完成的事務，確保數據的一致性。
提高性能：通過批量寫入Redo Log，減少頻繁的磁盤IO操作。

23 binlog呢

主要用于主從復制和數據備份。

事件類型：
- Query Event:記錄SQL查詢語句。
- Row Event：記錄行級別的變更。
- XID Event：記錄事務提交信息。
格式：
- Statement-Based Logging：記錄SQL語句。
- Row-Based Logging ：記錄每一行的變化。
- Mixed-Based Logging ：混合使用Statement和Row格式。

24 undolog 呢

Undo Log 是 InnoDB 存儲引擎用于事務回滾和MVCC 的日志系統。其主要分為以下幾個部分：

1.Undo Segment：每個事務分配一個Undo Segment，用于記錄該事務的所有Undo日志。
2.Undo Record：每條Undo日志記錄一次事務的反向操作，用于回滾和舊版本數據的構建。
事務回滾：在事務回滾時，通過Undo Log 將數據恢復到事務開始前的狀態。
MVCC:提供歷史版本的數據，支持高并發下的操作。

25 數據寫磁盤怎么實現的，怎么傳輸的，怎么保證一致性的

數據寫磁盤的實現：

1.Buffer Pool：數據首先寫入Buffer Pool,而不是直接寫入磁盤。
2.Dirty Page：修改后的數據頁標記為臟頁，等待后續刷盤操作。
3.后臺線程：通過后臺線程定期將臟頁寫回磁盤。

數據傳輸：

異步寫入：大多數情況下，數據寫入磁盤是異步的，以提高性能。
同步寫入：在某些情況下（如事務提交），需要確保數據已經寫入磁盤，此時會調用fsync（）函數。

保證一致性：

Redolog：記錄所有未提交的事務操作，用于數據恢復。
雙寫緩沖區：確保數據頁在寫入磁盤時的一致性。
兩階段提交：在分布式環境中，使用兩階段提交協議，確保事務的原子性和一致性。

26 MySQL斷開連接可以從線程和客戶端區分嗎

線程角度：
線程終止：MySQL 服務器端會終止對應的連接線程，并釋放相關的資源。
日志記錄：斷開連接的信息會被記錄到錯誤日志中。
客戶端角度：
異常處理：客戶端應用程序需要捕獲連接斷開的異常，并進行相應的處理（如重新連接）。
超時機制：客戶端可以通過設置超時參數（如 wait_timeout 和 interactive_timeout）來控制連接的有效期。

27 MySQL進程結束會執行哪些持久化操作

MySQL 進程結束時會執行一系列持久化操作，以確保數據的一致性和完整性：

Redo Log 刷盤：
將所有未提交的事務操作寫入 Redo Log，確保在崩潰恢復時能夠恢復這些事務。
Dirty Page 刷盤：
將 Buffer Pool 中的所有臟頁寫回到磁盤，確保數據的一致性。
Binlog 刷盤：
將 Binlog 中的所有更改操作寫入磁盤，確保主從復制和數據備份的一致性。
Checkpoints：
創建 Checkpoint，標記已成功寫入磁盤的數據頁，加快崩潰恢復的速度。
關閉存儲引擎：
關閉所有存儲引擎實例，釋放相關資源。
日志記錄：
記錄進程結束的相關信息到錯誤日志中，便于后續排查問題。