深入淺出MySQL - 架構與執行

Photo by Merilin Kirsika Tedder from Pexels

一、MySQL的邏輯架構

MySQL作為一個流行的開源關系型資料庫管理系統，它可以運行在多種平臺上，支持多種存盤引擎，提供了靈活的資料操作和管理功能，MySQL的邏輯架構可以分為三層：連接層、服務層和引擎層，下方是網上流傳度很廣的一張架構圖，

需要注意的是, 上圖描述的是MySQL5.7及以前的邏輯架構，MySQL8.0中正式移除了查詢快取組件, 因為從收集的資料來看查詢快取的命中率很低，即使是在MySQL5.7中查詢快取這個選項也是默認關閉的，所以本篇文章就不對快取這款內容做決議了，具體可以查看官方的一篇博客：

MySQL ：： MySQL 8.0：停用對查詢快取的支持

事實上，如果不去關注其內部的細節，《高性能MySQL》一書中的這張簡圖也足夠讓我們對其邏輯架構有一個直觀的認知：

1、連接層詳解

當客戶端發送連接請求時，MySQL服務器會在連接層接收請求，分配一個執行緒來處理該連接，隨后進行身份驗證，具體的功能如下：

• 客戶端連接的建立與處理：當客戶端發起連接請求時，MySQL會創建一個專用的執行緒（以作業系統級別的執行緒實作）來為該客戶端服務，這些服務執行緒使用執行緒池里的長連接服務多個用戶請求，減少了執行緒切換的開銷，

• 安全認證：安全認證是連接層的另一項重要任務，當客戶端連接到MySQL服務器時，服務器首先需要驗證客戶端的身份，MySQL使用基于用戶名、主機和密碼的認證方式，在連接時，客戶端需要提供有效的用戶名、主機名和密碼，服務器會根據在"mysql.user"表中的資料進行驗證，若通過，則建立連接，

• 連接資源管理：MySQL支持可配置的最大連接數，當到達最大連接數時，新的連接請求會被拒絕，符合條件的客戶端可以設定連接超時時間、客戶端閑置關閉時間等引數，同時，可以通過"mysql.user"表配置特定用戶對于資料庫的操作權限，

• 執行緒管理：MySQL會自動創建和管理連接執行緒，其中包括以執行緒數作為上限的執行緒池，執行緒池的目的是復用連接執行緒，避免了執行緒切換和創建的開銷，此外，MySQL使用異步I/O機制和協程，盡可能提高了并發和吞吐量，

2、服務層詳解

服務層是MySQL中的核心組件，負責提供各種資料庫操作所需的基本功能，如SQL語法處理、事務管理、鎖管理等，

SQL語法處理

服務層負責從客戶端接收來自連接層的SQL查詢請求，并進行初始分析、決議和預處理，

1. 查詢快取（MySQL8.0 中不存在）：MySQL會將查詢陳述句和其結果快取在記憶體中，當收到一個相同的查詢請求時，先檢查快取中是否有匹配的結果，如果有匹配結果，則直接回傳，并跳過剩余的處理步驟，如果沒有匹配，將繼續執行下一個步驟，
2. SQL決議器：服務層的SQL決議器主要進行語法決議，決議器會根據MySQL詞法分析器和語法分析器的決議規則，將查詢陳述句決議成一個字串表示的樹狀結構，用于存盤語法單位（詞素）及它們之間的關系，
3. SQL預處理：在構建完成決議樹后，預處理模塊對決議樹進行優化和處理，這包括檢查權限、完整性約束、函式呼叫和資料型別等，在預處理階段，還可能對查詢進行改寫，例如將"UNION"操作轉換為"JOIN"操作，或者將子查詢轉換為連接操作，

事務管理

MySQL的服務層負責事務管理，確保在執行一系列操作時，滿足原子性、一致性、隔離性和持久性這四個特性，事務管理涉及的主要功能包括：

1. 事務隔離級別：MySQL支持四個事務隔離級別：讀未提交、讀已提交、可重復讀和串行化，這些隔離級別分別定義了事務間資料訪問的隔離程度，用于防止臟讀、不可重復讀和幻讀，
2. 鎖管理：在事務程序中，可能需要對資料加鎖，以確保資料的一致性，MySQL支持的鎖型別包括共享鎖、排它鎖、意向鎖、行鎖、表鎖等，
3. Undo日志：服務層通過Undo日志實作了事務回滾操作，當事務執行中途出現例外或用戶發出回滾請求時，可以通過Undo日志回滾資料到事務開始前的狀態，
4. Redo日志：為了保證事務的持久性，在事務執行程序中，修改的資料首先寫入到Redo日志中，再更新到磁盤檔案上，在系統恢復程序中，可以通過Redo日志進行資料恢復，

快取管理

MySQL優化器使用快取來提高查詢速度，包括：

1. 查詢快取：當相同的SQL查詢被多次執行時，可以從查詢快取中直接獲取結果，提高性能，由于MySQL 8.0中已移除了查詢快取功能，使用者需要自行實作相關功能，如使用Redis、Memcached等中間快取系統，
2. 表快取：用于存盤表的元資料，如表的結構定義，當查詢需要表資訊時，優先從表快取中獲取，避免磁盤操作，
3. 執行緒快取：用于復用服務器的連接執行緒，當一個連接關閉后，它的執行緒會被放回執行緒快取池中，供新的連接使用，執行緒池意味著減少了創建和銷毀執行緒的開銷，
4. 緩沖池：主要用于InnoDB存盤引擎，緩沖池管理快取的資料頁，包括資料和索引，當需要訪問這些頁時，可以直接從緩沖池讀取，提高訪問速度，

3、引擎層詳解

引擎層負責存盤資料和執行SQL陳述句，MySQL支持多種存盤引擎，每種引擎各有特點，根據實際需求進行選用，當然，只要沒有非常明確的特殊需求就不需要更改存盤引擎，因為InnoDB在大部分場景下都比其他引擎更加適用，引擎層通過標準API與服務層互動，實作資料的存盤和查詢，

1. InnoDB：InnoDB是MySQL的默認存盤引擎，提供了事務支持、行級鎖定、外鍵約束等功能，主要用于高并發、高可靠性的OLTP場景，
2. MyISAM：MyISAM通常用于只讀資料表，適用于簡單查詢和全文索引，其不支持事務、行級鎖等功能，適用于OLAP場景，
3. Memory：Memory存盤引擎支持哈希和B樹索引，它將資料存盤在記憶體中，易受到系統斷電或宕機等影響，具有較高的寫性能但不適用于大規模資料分布，
4. 其他存盤引擎：MySQL還支持如Archive、NDB Cluster等其他存盤引擎，它們分別適用于存檔表、分布式資料庫等不同場景，

我們可以在SQL命令列中執行 show engines; 來查看當前支持的存盤引擎：

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

二、MySQL陳述句執行流程

SQL陳述句的執行流程可以簡單分為以下幾個步驟：

1. 連接器：客戶端連接到MySQL服務器，連接器負責驗證客戶端的身份和權限，如果通過驗證，就建立一個連接，并從權限表中讀取該用戶的所有權限資訊，
2. 語法分析：在執行SQL陳述句之前，MySQL需要對SQL陳述句進行語法分析，語法分析器會檢查SQL陳述句是否符合語法規則，并將其轉換為一棵語法樹，如果SQL陳述句不符合語法規則，MySQL將會回傳一個錯誤訊息，語意分析是在語法分析之后進行的，它會檢查SQL陳述句是否符合語意規則，并將其轉換為一棵語意樹，語意分析器會檢查表名、列名、資料型別等資訊是否正確，如果SQL陳述句不符合語意規則，MySQL將會回傳一個錯誤訊息，
3. 查詢優化：在語意分析之后，MySQL會對SQL陳述句進行查詢優化，查詢優化器會分析查詢陳述句，并生成一個最優的執行計劃，執行計劃是指MySQL執行SQL陳述句的具體步驟，包括使用哪些索引、如何連接表等，
4. 執行SQL陳述句：在查詢優化之后，MySQL會執行SQL陳述句，執行器會按照執行計劃的步驟，逐步執行SQL陳述句，執行器會根據查詢陳述句，從磁盤讀取資料，并將其存盤在記憶體中，然后，執行器會對資料進行排序、分組、聚合等操作，最終生成查詢結果，

另外請注意，本篇文章依舊是在為后續寫MySQL優化流程做知識上的鋪墊，所以一些細節會簡單介紹，但實際的SQL優化思想會等到后面的文章再詳細介紹，

下面我們來詳細解釋一下SQL陳述句的執行流程和細節，

1. 語法分析

語法分析是MySQL執行SQL陳述句的第一步，語法分析器會對SQL陳述句進行分析，檢查其是否符合語法規則，如果SQL陳述句不符合語法規則，MySQL將會回傳一個錯誤訊息，詳細的來說又可分為以下幾步：

• 詞法分析：主要負責從SQL陳述句中提取關鍵字，比如：查詢的表，欄位名，查詢條件等等，詞法分析器會將SQL陳述句分割成一個個的詞法單元（token），并為每個token賦予一個型別（type）和值（value），
• 語法規則：主要判斷SQL陳述句是否合乎MySQL的語法，語法規則模塊會使用yacc工具生成的語法分析器，根據MySQL的語法規則（grammar rule）來檢查詞法單元是否符合語法要求，
• 語意分析：主要負責檢查SQL陳述句的語意是否正確，比如：表名和欄位名是否存在，資料型別是否匹配，函式是否合法等，語意分析器會根據資料字典（data dictionary）和目錄（catalog）來驗證SQL陳述句的有效性，

MySQL使用的語法分析器是Bison，它是一種自動生成決議器的工具，可以根據語法規則自動生成語法分析器，下面是一個示例SQL陳述句：

SELECT name, age FROM student WHERE id = 1;

在語法分析階段，MySQL會進行以下操作：

• 詞法分析：將SQL陳述句分割成以下詞法單元：

• 語法規則：根據MySQL的語法規則，檢查詞法單元是否符合以下格式：

select_statement: SELECT select_expression_list FROM table_reference_list [WHERE where_condition]

• 語意分析：根據資料字典和目錄，檢查以下內容：
  • 表student是否存在
  • 欄位name, age, id是否屬于表student
  • 欄位id的資料型別是否與數字1匹配
  • 等等

如果以上步驟都沒有出現錯誤，那么MySQL就會認為這條SQL陳述句在語法分析階段是正確的，并繼續進行后續的處理，否則，MySQL就會報錯，并停止執行這條SQL陳述句，

2. 查詢優化

查詢優化是MySQL執行SQL陳述句的第三步，SQL陳述句在查詢優化階段會經歷以下步驟：

• 查詢重寫：MySQL會對SQL陳述句進行一些語法和邏輯上的變換，以便于后續的優化和執行，例如，將子查詢轉換為連接，將or條件轉換為union，將in條件轉換為exists等，
• 查詢分解：MySQL會將一條復雜的SQL陳述句分解為多個簡單的子查詢，每個子查詢可以單獨優化和執行，例如，將union查詢分解為多個select查詢，將關聯子查詢分解為獨立的select查詢等，
• 預處理：MySQL會對SQL陳述句進行一些基本的檢查和處理，例如檢查語法錯誤，決議引數，分配內部資源等，
• 優化器：MySQL會根據統計資訊和成本模型，為SQL陳述句選擇一個最佳的執行計劃，執行計劃包括了連接順序，訪問方法，索引選擇，排序策略等，

舉例說明，下面是一個示例SQL陳述句：

SELECT name, age FROM student WHERE id IN (SELECT student_id FROM score WHERE score > 80);

則首先在查詢重寫時，MySQL會將這條SQL陳述句重寫為：

SELECT name, age FROM student s JOIN score c ON s.id = c.student_id WHERE c.score > 80;

這樣做的好處是：

• 連接查詢通常比子查詢更快，因為MySQL優化器可以生成更佳的執行計劃，可以預先裝載資料，更高效地處理查詢，
• 子查詢往往需要運行重復的查詢，子查詢生成的臨時表上也沒有索引，因此效率會更低，
• 連接查詢可以利用索引加速，比如在student表的id列和score表的student_id列上建立索引，

接下來在查詢分解階段，MySQL會將這條SQL陳述句分解為兩個子查詢：

SELECT name, age, id FROM student; 
SELECT student_id FROM score WHERE score > 80;

預處理時MySQL會對SQL陳述句進行一些基本的檢查和處理，例如檢查表名和欄位名是否存在，決議引數等，

最后優化器會根據統計資訊和成本模型，為SQL陳述句選擇一個最佳的執行計劃，

MySQL優化器是負責為SQL陳述句選擇一個最佳的執行計劃的模塊，執行計劃包括了連接順序，訪問方法，索引選擇，排序策略等，MySQL優化器是基于成本的優化器（cost-based optimizer），也就是說它會根據統計資訊和成本模型來估算不同執行計劃的代價，并選擇代價最小的那個，

MySQL優化器在選擇執行計劃時會考慮以下幾個方面：

• 表依賴關系：MySQL優化器會分析SQL陳述句中涉及到的表之間是否有依賴關系，比如外鍵約束，主鍵約束等，這些依賴關系會影響連接順序和訪問方法的選擇，
• 可用索引：MySQL優化器會分析SQL陳述句中參與條件過濾或排序的列是否有可用索引，并根據索引型別和覆寫度來選擇合適的索引，
• 預估行數：MySQL優化器會根據資料字典和目錄中存盤的統計資訊來預估每個表或每個索引范圍內的行數，這些行數會影響成本模型中的I/O代價和CPU代價，
• 預估成本：MySQL優化器會根據預估行數和成本常數（cost constant）來預估每個執行計劃的成本，成本常數是一些固定引數，比如隨機讀一頁資料的代價，排序一行資料的代價等，MySQL優化器會選擇成本最低的執行計劃，

例如，上面能夠重寫后的SQL陳述句應該是：

SELECT name, age FROM student s JOIN score c ON s.id = c.student_id WHERE c.score > 80;

MySQL優化器在選擇執行計劃時會進行以下操作：

• 分析表依賴關系：發現student表和score表之間沒有依賴關系，因此可以任意調整連接順序，
• 分析可用索引：發現student表有一個主鍵索引idx_student_id（id），

這么一通分析之后到底有點紙上談兵，接下來我們在MySQL 8.0 里執行命令，畢竟實踐出真知，進入MySQL命令列，利用explain來查看執行計劃：

mysql> SELECT name, age FROM student WHERE id IN (SELECT student_id FROM score WHERE score > 80);
+----------+------+
| name     | age  |
+----------+------+
| zhangsan |   18 |
| wangwu   |   20 |
+----------+------+
2 rows in set (0.00 sec)

mysql> explain SELECT name, age FROM student WHERE id IN (SELECT student_id FROM score WHERE score > 80);
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------+
|  1 | SIMPLE      | student | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    4 |   100.00 | NULL                                                            |
|  1 | SIMPLE      | score   | NULL       | ALL  | student_id    | NULL | NULL    | NULL |    8 |    12.50 | Using where; FirstMatch(student); Using join buffer (hash join) |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

我們可以在explain的結果中看到，兩行的ID值都為1，而我們自己寫的SQL陳述句里有兩個Select，說明實際并沒有按照我們的原SQL來執行

再使用show warnings來查看實際執行的sql內容：

mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                              |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `datasets`.`student`.`name` AS `name`,`datasets`.`student`.`age` AS `age` from `datasets`.`student` semi join (`datasets`.`score`) where ((`datasets`.`score`.`student_id` = `datasets`.`student`.`id`) and (`datasets`.`score`.`score` > 80)) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以發現，實際執行的時候用的是semi join，這是因為Semi Join回傳的結果只包含主表（左表）中滿足連接條件的行，而不包含從表（右表）的任何資料，它的主要目的是通過減少要比較的資料量來提高查詢性能，通過使用Semi Join，可以避免將兩個表的所有資料進行連接，并僅僅關注滿足連接條件的部分資料，

但是對于開發人員來說，我們并不需要關注優化器內部的所有決策，因為涉及的因素太多了，所以我們從整體上來看知道大致的優化方向即可，

這里也給出上面示例的建表陳述句，方便有心的讀者自行嘗試：

CREATE TABLE student
(
    id   INT PRIMARY KEY auto_increment,
    name VARCHAR(20),
    age  INT
) charset = utf8mb4;

CREATE TABLE score
(
    student_id INT,
    course     VARCHAR(20),
    score      INT,
    FOREIGN KEY (student_id) REFERENCES student (id)
) charset = utf8mb4;
INSERT INTO student (id, name, age) VALUES (1, 'zhangsan', 18),(2, 'lisi', 19),(3, 'wangwu', 20),(4, 'zhaoliu', 21);
INSERT INTO score (student_id, course, score) VALUES (1, '數學', 85),(1, '語文', 90),(2, '數學', 75),(2, '語文', 80),(3, '數學', 95),(3, '語文', 100),(4, '數學', 65),(4, '語文', 70);
SELECT name, age FROM student WHERE id IN (SELECT student_id FROM score WHERE score > 80);

3. 執行SQL陳述句

執行SQL陳述句是MySQL執行SQL陳述句的最后一步，簡單來說，執行器會按照執行計劃的步驟，逐步執行SQL陳述句，執行器會根據查詢陳述句，從磁盤讀取資料，并將其存盤在記憶體中，然后，執行器會對資料進行排序、分組、聚合等操作，最終生成查詢結果，

說的詳細一點，一些重要的步驟如下：

• 打開表：MySQL會根據執行計劃中涉及到的表，打開相應的表檔案，并為每個表分配一個表句柄（table handle），
• 鎖定表：MySQL會根據SQL陳述句的型別（讀或寫）和事務的隔離級別，為涉及到的表加上相應的鎖（共享鎖或排他鎖），鎖的作用是保證資料的一致性和并發性，
• 讀取資料：MySQL會根據執行計劃中選擇的訪問方法（全表掃描或索引掃描），從存盤引擎中讀取資料，存盤引擎是負責管理資料檔案的模塊，不同的存盤引擎有不同的特性和優化，
• 過濾資料：MySQL會根據執行計劃中的過濾條件（where，group by，having，order by，limit等），對讀取到的資料進行過濾和處理，過濾條件可以減少回傳給客戶端的資料量，提高查詢效率，
• 回傳結果：MySQL會將過濾后的資料回傳給客戶端，并釋放相關的資源（表句柄，鎖等），客戶端可以接收到結果集，并進行后續的操作，

實際上，依舊可以通過MySQL命令列來了解其執行程序：

mysql> set profiling = 'ON';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT name, age FROM student WHERE id IN (SELECT student_id FROM score WHERE score > 80);
+----------+------+
| name     | age  |
+----------+------+
| zhangsan |   18 |
| wangwu   |   20 |
+----------+------+
2 rows in set (0.00 sec)


mysql> show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000094 |
| Executing hook on transaction  | 0.000005 |
| starting                       | 0.000008 |
| checking permissions           | 0.000005 |
| checking permissions           | 0.000004 |
| Opening tables                 | 0.000088 |
| init                           | 0.000009 |
| System lock                    | 0.000009 |
| optimizing                     | 0.000012 |
| statistics                     | 0.000035 |
| preparing                      | 0.000076 |
| executing                      | 0.000065 |
| end                            | 0.000004 |
| query end                      | 0.000005 |
| waiting for handler commit     | 0.000009 |
| closing tables                 | 0.000008 |
| freeing items                  | 0.000021 |
| cleaning up                    | 0.000010 |
+--------------------------------+----------+
18 rows in set, 1 warning (0.00 sec)

可以看到，命令執行結果非常詳細的列出了所有步驟，本文只是挑選了一部分來展開說，

具體結合到例子來說明，假設有一條SQL陳述句如下：

SELECT name, age FROM student s JOIN score c ON s.id = c.student_id WHERE c.score > 80 ORDER BY s.age LIMIT 10;

在執行階段，MySQL會進行以下操作：

• 打開表：MySQL會打開student表和score表，并為它們分配兩個表句柄s和c，
• 鎖定表：MySQL會根據SQL陳述句是讀操作，并且假設事務的隔離級別是可重復讀（repeatable read），為student表和score表加上共享鎖（shared lock），
• 讀取資料：MySQL會根據執行計劃中選擇的訪問方法，從存盤引擎中讀取資料，假設執行計劃是先掃描score表的索引idx_score（score），然后回表獲取student_id列，再通過student_id列關聯到student表，并獲取name和age列，
• 過濾資料：MySQL會根據執行計劃中的過濾條件，對讀取到的資料進行過濾和處理，具體步驟如下：
  • 根據where條件c.score > 80，篩選出符合條件的記錄，
  • 根據order by條件s.age，對記錄按照學生年齡進行排序，
  • 根據limit條件10，只取前10條記錄作為結果集，
• 回傳結果：MySQL會將結果集回傳給客戶端，并釋放相關的資源，

三、InnoDB存盤結構

InnoDB是MySQL的默認存盤引擎，它支持事務、行級鎖、外鍵、MVCC等特性，提供了高性能和高可靠性的資料存盤方案，InnoDB的底層結構主要由兩部分組成：記憶體結構和磁盤結構，

圖片來源：https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html

記憶體結構

InnoDB的記憶體結構主要包括以下幾個部分：

• 緩沖池（Buffer Pool）
• 更改緩沖區（Change Buffer）
• 自適應哈希索引（Adaptive Hash Index）
• 日志緩沖區（ Log Buffer）

如果對這部分內容感興趣可以看官方檔案，這里只做一個簡單的介紹，

緩沖池（Buffer Pool）

主要用于快取表資料和索引資料，加快訪問速度，緩沖池是InnoDB記憶體結構中最重要的部分，通常占用宿主機80%的記憶體，緩沖池被分成多個頁，每頁默認大小為16KB，每頁可以存放多條記錄，緩沖池中的頁按照LRU（最近最少使用）演算法進行淘汰，同時也被分成兩個子鏈表：New Sublist和Old Sublist，分別存放訪問頻繁和不頻繁的頁，

• New Sublist：占用緩沖池5/8的空間，存放最近被訪問過的頁，當一個新的頁被讀入緩沖池時，會被放在New Sublist的最前端，當一個已經存在于緩沖池的頁被訪問時，如果它在New Sublist中，則不會改變位置；如果它在Old Sublist中，則會被移動到New Sublist的最前端，
• Old Sublist：占用緩沖池3/8的空間，存放較久未被訪問過的頁，當一個已經存在于緩沖池的頁被訪問時，如果它在Old Sublist中，則會被移動到New Sublist的最前端；如果它在New Sublist中，則不會改變位置，
• 淘汰策略：當緩沖池已滿時，需要淘汰一些頁來騰出空間，淘汰策略是從Old Sublist的尾部開始掃描，找到第一個沒有被修改過（clean）且沒有被鎖定（unlocked）的頁，并將其淘汰出緩沖池，

寫緩沖（Change Buffer）

主要用于快取對非聚集索引的修改操作，減少磁盤I/O，寫緩沖是緩沖池的一部分，當對非聚集索引進行插入、洗掉或更新時，不會立即修改磁盤上的索引頁，而是先記錄在寫緩沖中，當緩沖池中的資料頁被重繪到磁盤時，會將寫緩沖中的修改操作合并到相應的索引頁中，

• 寫緩沖型別：寫緩沖有三種型別，分別用于記錄不同型別的非聚集索引修改操作：
  • Insert Buffer：用于記錄對非聚集索引進行插入操作時產生的新條目（entry），
  • Delete Buffer：用于記錄對非聚集索引進行洗掉操作時需要洗掉的條目（entry），
  • Purge Buffer：用于記錄對非聚集索引進行更新操作時需要洗掉和插入的條目（entry），
• 寫緩沖結構：寫緩沖是由多個段（segment）組成的，每個段有一個位圖（bitmap），用于標記哪些頁有寫緩沖條目（entry），每個段都有一個根頁（root page），用于存盤寫緩沖條目（entry），每個根頁都有一個B+樹結構，用于按照空間ID（space ID）和頁號（page number）排序寫緩沖條目（entry），
• 寫緩沖合并：當緩沖池中的資料頁被重繪到磁盤時，會觸發寫緩沖的合并操作，合并操作的步驟如下：
  • 根據資料頁的空間ID（space ID）和頁號（page number），在寫緩沖中查找是否有對應的條目（entry），
  • 如果有，就將寫緩沖中的條目（entry）應用到磁盤上的索引頁中，完成修改操作，
  • 如果沒有，就直接重繪資料頁到磁盤，不做任何修改操作，
  • 將寫緩沖中已經合并的條目（entry）洗掉，釋放空間，

日志緩沖（Log Buffer）

主要用于快取重做日志（Redo Log），保證事務的持久性，日志緩沖是一個回圈使用的記憶體區域，默認大小為16MB，可以通過引數innodb_log_buffer_size來調整，當事務提交時，會將日志緩沖中的重做日志重繪到磁盤上的重做日志檔案中，日志緩沖中的重做日志也會在以下情況下被重繪：日志緩沖已滿、每秒鐘一次、每個事務檢查點一次，

• 重做日志：重做日志是一種物理日志，記錄了對資料頁進行的物理修改操作，重做日志可以用于恢復事務在崩潰或例外情況下未完成的修改操作，保證資料的完整性和一致性，
• 重做日志格式：重做日志由多個固定大小的日志塊（log block）組成，每個日志塊默認大小為512位元組，每個日志塊包含以下資訊：
  • 日志塊頭（log block header）：占用12位元組，包含了日志塊編號（log block number），校驗和（checksum），資料長度（data length）等資訊，
  • 日志記錄（log record）：占用不定長度，包含了對資料頁進行的物理修改操作的詳細資訊，例如，修改了哪個資料頁，修改了哪個偏移量，修改前后的值是什么等，
  • 日志塊尾（log block tail）：占用4位元組，包含了日志塊編號（log block number）的副本，
• 重做日志檔案：重做日志檔案是磁盤上存盤重做日志的檔案，通常有兩個或多個，以實作回圈寫入和備份，重做日志檔案可以通過引數innodb_log_files_in_group來指定數量，通過引數innodb_log_file_size來指定大小，重做日志檔案中有一個特殊的位置叫做檢查點（checkpoint），表示到這個位置之前的所有重做日志都已經被應用到資料檔案中，可以被覆寫或洗掉，

自適應哈希索引（Adaptive Hash Index）

主要用于加速等值查詢，提高查詢效率，自適應哈希索引是InnoDB根據查詢頻率和模式自動建立的一種哈希索引，可以將某些B+樹索引轉換為哈希索引，從而減少樹的搜索次數，自適應哈希索引是可選的，可以通過引數innodb_adaptive來開啟或關閉，

• 自適應哈希索引原理：當InnoDB發現某個B+樹索引被頻繁用于等值查詢時，就會為該索引建立一個對應的哈希索引，哈希索引是一種基于鍵值對的索引，可以通過哈希函式快速定位到資料頁的位置，哈希索引比B+樹索引更簡單，更高效，但只能用于等值查詢，不能用于范圍查詢或排序查詢，
• 自適應哈希索引結構：自適應哈希索引是由多個磁區（partition）組成的，每個磁區有一個哈希表（hash table），用于存盤鍵值對，每個鍵值對包含以下資訊：
  • 鍵（key）：由B+樹索引的鍵值和空間ID（space ID）組成，
  • 值（value）：由資料頁的頁號（page number）和記錄的偏移量（offset）組成，
• 自適應哈希索引使用：當InnoDB執行一個等值查詢時，會先在自適應哈希索引中查找是否有匹配的鍵值對，如果有，就直接定位到資料頁的位置，并獲取記錄，如果沒有，就回退到B+樹索引中進行搜索，

磁盤結構

表空間（Tablespace）

表空間是InnoDB存盤引擎邏輯結構的最高層，所有的資料都存放在表空間中，表空間可以分為以下五種型別123??：

• 系統表空間（system tablespace）：系統表空間是InnoDB的默認表空間，通常存盤在ibdata1檔案中，也可以分成多個檔案，系統表空間包含了InnoDB的資料字典，雙寫緩沖區，Change Buffer和undo log等重要資訊，系統表空間是共享的，可以存盤多個表的資料和索引，系統表空間的大小是不可縮小的，只能通過重建整個資料庫來減小，
• 臨時表空間（temporary tablespace）：臨時表空間用于存盤臨時表的資料和索引，例如在執行復雜查詢時產生的中間結果，臨時表空間通常存盤在ibtmp1檔案中，可以配置為自動擴展或固定大小，臨時表空間不會持久化到磁盤，每次資料庫重啟后都會被清空，
• 常規表空間（general tablespace）：常規表空間是一種共享表空間，可以存盤多個表的資料和索引，常規表空間可以在任意位置創建多個檔案，檔案的擴展方式可以是自動擴展或預分配，常規表空間可以用于跨資料庫存盤資料，或者將不同型別或大小的表分開存盤，
• undo表空間（undo tablespace）：undo表空間包含undo log撤銷記錄的集合，用于事務回滾和一致性讀，undo log記錄了資料頁修改前的值，用于恢復未提交或已回滾的事務所做的修改，或者提供給其他事務一個資料修改前的快照，undo表空間可以有多個檔案組成，檔案存盤在innodb_undo_directory指定的目錄下，以undofile開頭命名，
• 檔案獨占表空間（file-per-table tablespace）：檔案每表表空間是一種獨占表空間，含單個InnoDB表的資料和索引，并存盤在檔案系統中自己的資料檔案中，檔案每表表空間的檔案名與表名相同，以.ibd為擴展名，位于資料庫目錄下，檔案每表表空間可以實作單個表的壓縮，加密，傳輸和優化等操作，

資料字典（Data Dictionary）

資料字典包含用于跟蹤物件，如表，索引，和列等元資料的內部系統表2?，元資料實際上位于InnoDB系統表空間中，InnoDB使用資料字典來管理和訪問資料庫物件，并檢查用戶對物件的權限，資料字典在資料庫啟動時加載到記憶體中，并在資料庫關閉時重繪到磁盤上，

雙寫緩沖區（Doublewrite Buffer）

雙寫緩沖區位于系統表空間中的存盤區域，用于保證資料頁在寫入磁盤時不會損壞2??，InnoDB在Buffer Pool中重繪頁面時，會將資料頁寫入doublewrite緩沖區后才會寫入磁盤，如果在寫入OS Cache或者磁盤mysql行程奔潰后， InnoDB啟動崩潰恢復能從doublewrite找到完整的副本用來恢復，

重做日志（Redo Log）

重做日志是基于磁盤的資料結構，在崩潰恢復期間用于糾正不完整事務寫入的資料 ，MySQL以回圈方式寫入重做日志檔案，默認會產生ib_logfile0 和 ib_logfile1兩個檔案，InnoDB在提交事務之前重繪事務的redo log，InnoDB使用組提交（group commit）技術來提高性能，重做日志記錄了資料頁的物理修改，而不是邏輯修改，這樣可以減少日志的大小和恢復的時間，重做日志可以通過innodb_log_file_size和innodb_log_files_in_group引數來調整大小和數量，

更改緩沖區（Change Buffer）

更改緩沖區是Buffer Pool中的一部分，用于快取對輔助索引頁的修改 ，當InnoDB需要修改一個輔助索引頁時，如果該頁在Buffer Pool中，則直接修改；如果該頁不在Buffer Pool中，則將修改記錄在Change Buffer中，而不是從磁盤讀取該頁，這樣可以減少磁盤I/O操作，提高性能，Change Buffer中的修改會在后臺或者檢查點時合并到輔助索引頁中，Change Buffer的大小可以通過innodb_change_buffer_max_size引數來調整，

四、InnoDB磁盤空間管理結構

這部分簡單介紹即可，參考官方檔案：MySQL ：： MySQL 8.0 參考手冊 ：： 15.11.2 檔案空間管理

InnoDB的磁盤結構主要包括以下幾個部分：

• 表空間（Tablespace）：表空間是InnoDB存盤資料和索引的邏輯單位，它由一個或多個檔案組成，表空間可以分為系統表空間（System Tablespace），通用表空間（General Tablespace），檔案表空間（File-Per-Table Tablespace）和臨時表空間（Temporary Tablespace），
• 段（Segment）：段是表空間中分配和管理空間的單位，它由一個或多個連續或不連續的區（Extent）組成，段可以分為資料段（Data Segment），索引段（Index Segment），回滾段（Rollback Segment），撤銷日志段（Undo Log Segment）和系統段（System Segment），
• 區（Extent）：區是段中分配空間的單位，它由一組連續的頁（Page）組成，每個區的大小固定為1MB，包含64個頁，
• 頁（Page）：頁是InnoDB在磁盤和記憶體之間傳輸資料的基本單位，它由一個固定大小的塊（Block）組成，每個頁的大小默認為16KB，可以通過引數innodb_page_size來調整，頁可以分為不同的型別，根據存盤的內容而定，比如資料頁（Data Page），索引頁（Index Page），系統頁（System Page），事務系統頁（Transaction System Page），撤銷日志頁（Undo Log Page）等，
• 行（Row）：行是InnoDB存盤資料記錄的最小單位，它由一個或多個列（Column）組成，每個行的大小不能超過半個頁，行可以分為兩種格式，根據存盤方式而定，比如緊湊格式（Compact Format）和動態格式（Dynamic Format），

參考資料:

• MySQL :: MySQL 8.0 Reference Manual
• 高性能MySQL 第四版

標籤：MySQL

上一篇：【后端面經】MySQL主鍵、唯一索引、聯合索引的區別和作用

下一篇：返回列表