MySQL 的執行流程#
可以看到， MySQL 的架構共分為兩層：Server 層和存儲引擎層，
- Server 層負責建立連接、分析和執行 SQL。MySQL 大多數的核心功能模塊都在這實現，主要包括連接器，查詢緩存、解析器、預處理器、優化器、執行器等。另外，所有的內置函數（如日期、時間、數學和加密函數等）和所有跨存儲引擎的功能（如存儲過程、觸發器、視圖等。）都在 Server 層實現。
- 存儲引擎層負責數據的存儲和提取。支持 InnoDB、MyISAM、Memory 等多個存儲引擎，不同的存儲引擎共用一個 Server 層。現在最常用的存儲引擎是 InnoDB，從 MySQL 5.5 版本開始， InnoDB 成為了 MySQL 的默認存儲引擎。我們常說的索引數據結構，就是由存儲引擎層實現的，不同的存儲引擎支持的索引類型也不相同，比如 InnoDB 支持的索引類型是 B + 樹，且是默認使用，也就是說在數據表中創建的主鍵索引和二級索引默認使用的是 B+ 樹索引。
第一步：連接器#
連接器的工作:
- 與客戶端進行 TCP 三次握手建立連接；
- 校驗客戶端的用戶名和密碼，如果用戶名或密碼不對，則會報錯；
- 如果用戶名和密碼都對了，會讀取該用戶的權限，然後後面的權限邏輯判斷都基於此時讀取到的權限；
如何查看 MySQL 服務被多少個客戶端連接了？
執行 show processlist
空閒連接會一直佔用著嗎？
由 wait_timeout 參數控制的，默認值是 8 小時（28880 秒），如果空閒連接超過了這個時間，連接器就會自動將它斷開。
MySQL 的連接數有限制嗎？
由 max_connections 參數控制
怎麼解決長連接佔用內存的問題？
- 定期斷開長連接
- 客戶端主動重置連接
MySQL 5.7 版本實現了 mysql_reset_connection () 函數的接口，當客戶端執行了一個很大的操作後，在代碼裡調用 mysql_reset_connection 函數來重置連接，達到釋放內存的效果。這個過程不需要重連和重新做權限驗證，但是會將連接恢復到剛剛創建完時的狀態。
第二步：查詢緩存#
客戶端向 MySQL 服務發送 SQL 語句了，MySQL 服務收到 SQL 語句後，就會解析出 SQL 語句的第一個字段，看看是什麼類型的語句。
如果 SQL 是查詢語句（select 語句），MySQL 就會先去查詢緩存（ Query Cache ）裡查找緩存數據，看看之前有沒有執行過這一條命令，這個查詢緩存是以 key-value 形式保存在內存中的，key 為 SQL 查詢語句，value 為 SQL 語句查詢的結果。
如果查詢的語句命中查詢緩存，那麼就會直接返回 value 給客戶端。如果查詢的語句沒有命中查詢緩存中，那麼就要往下繼續執行，等執行完後，查詢的結果就會被存入查詢緩存中。
然而對於更新比較頻繁的表，查詢緩存的命中率很低，因為只要一個表有更新操作，那麼這個表的查詢緩存就會被清空。所以，MySQL 8.0 版本直接將查詢緩存刪掉了。
第三步：解析 SQL#
在正式執行 SQL 查詢語句之前， MySQL 會先對 SQL 語句做解析，這個工作交由「解析器」來完成。
解析器#
解析器會做如下兩件事情。
第一件事情，詞法分析。MySQL 會根據你輸入的字符串識別出關鍵字出來，構建出 SQL 語法樹，這樣方便後面模塊獲取 SQL 類型、表名、字段名、 where 條件等等。
第二件事情，語法分析。根據詞法分析的結果，語法解析器會根據語法規則，判斷你輸入的這個 SQL 語句是否滿足 MySQL 語法。
如果我們輸入的 SQL 語句語法不對，就會在解析器這個階段報錯。
但是注意，表不存在或者字段不存在，並不是在解析器裡做的。
第四步：執行 SQL#
預處理器#
我們先來說說預處理階段做了什麼事情。
- 檢查 SQL 查詢語句中的表或者字段是否存在；
- 將
select *中的*符號，擴展為表上的所有列；
優化器#
經過預處理階段後，還需要為 SQL 查詢語句先制定一個執行計劃，這個工作交由「優化器」來完成的。
優化器主要負責將 SQL 查詢語句的執行方案確定下來，比如在表裡面有多個索引的時候，優化器會基於查詢成本的考慮，來決定選擇使用哪個索引。
執行器#
經歷完優化器後，就確定了執行方案，接下來 MySQL 就真正開始執行語句了，這個工作是由「執行器」完成的。在執行的過程中，執行器就會和存儲引擎交互了，交互是以記錄為單位的。
主鍵索引查詢#
select * from product where id = 1;
這條查詢語句的查詢條件用到了主鍵索引，而且是等值查詢，同時主鍵 id 是唯一，不會有 id 相同的記錄，所以優化器決定選用訪問類型為 const 進行查詢，也就是使用主鍵索引查詢一條記錄，那麼執行器與存儲引擎的執行流程是這樣的：
- 執行器第一次查詢，會調用 read_first_record 函數指針指向的函數，因為優化器選擇的訪問類型為 const，這個函數指針被指向為 InnoDB 引擎索引查詢的接口，把條件 id = 1 交給存儲引擎，讓存儲引擎定位符合條件的第一條記錄。
- 存儲引擎通過主鍵索引的 B+ 樹結構定位到 id = 1 的第一條記錄，如果記錄是不存在的，就會向執行器上報記錄找不到的錯誤，然後查詢結束。如果記錄是存在的，就會將記錄返回給執行器；
- 執行器從存儲引擎讀到記錄後，接著判斷記錄是否符合查詢條件，如果符合則發送給客戶端，如果不符合則跳過該記錄。
- 執行器查詢的過程是一個 while 循環，所以還會再查一次，但是這次因為不是第一次查詢了，所以會調用 read_record 函數指針指向的函數，因為優化器選擇的訪問類型為 const，這個函數指針被指向為一個永遠返回 - 1 的函數，所以當調用該函數的時候，執行器就退出循環，也就是結束查詢了。
全表掃描#
select * from product where name = 'iphone';
這條查詢語句的查詢條件沒有用到索引，所以優化器決定選用訪問類型為 ALL 進行查詢，也就是全表掃描的方式查詢，那麼這時執行器與存儲引擎的執行流程是這樣的：
- 執行器第一次查詢，會調用 read_first_record 函數指針指向的函數，因為優化器選擇的訪問類型為 all，這個函數指針被指向為 InnoDB 引擎全掃描的接口，讓存儲引擎讀取表中的第一條記錄；
- 執行器會判斷讀到的這條記錄的 name 是不是 iphone，如果不是則跳過；如果是則將記錄發給客戶的（是的沒錯，Server 層每從存儲引擎讀到一條記錄就會發送給客戶端，之所以客戶端顯示的時候是直接顯示所有記錄的，是因為客戶端是等查詢語句查詢完成後，才會顯示出所有的記錄）。
- 執行器查詢的過程是一個 while 循環，所以還會再查一次，會調用 read_record 函數指針指向的函數，因為優化器選擇的訪問類型為 all，read_record 函數指針指向的還是 InnoDB 引擎全掃描的接口，所以接著向存儲引擎層要求繼續讀剛才那條記錄的下一條記錄，存儲引擎把下一條記錄取出後就將其返回給執行器（Server 層），執行器繼續判斷條件，不符合查詢條件即跳過該記錄，否則發送到客戶端；
- 一直重複上述過程，直到存儲引擎把表中的所有記錄讀完，然後向執行器（Server 層） 返回了讀取完畢的信息；
- 執行器收到存儲引擎報告的查詢完畢的信息，退出循環，停止查詢。
索引下推#
索引下推能夠減少二級索引在查詢時的回表操作，提高查詢的效率，因為它將 Server 層部分負責的事情，交給存儲引擎層去處理了。
select * from t_user where age > 20 and reward = 100000;
聯合索引當遇到範圍查詢 (>、<) 就會停止匹配，也就是 age 字段能用到聯合索引，但是 reward 字段則無法利用到索引。
那麼，不使用索引下推（MySQL 5.6 之前的版本）時，執行器與存儲引擎的執行流程是這樣的：
- Server 層首先調用存儲引擎的接口定位到滿足查詢條件的第一條二級索引記錄，也就是定位到 age > 20 的第一條記錄；
- 存儲引擎根據二級索引的 B+ 樹快速定位到這條記錄後，獲取主鍵值，然後進行回表操作，將完整的記錄返回給 Server 層；
- Server 層在判斷該記錄的 reward 是否等於 100000，如果成立則將其發送給客戶端；否則跳過該記錄；
- 接著，繼續向存儲引擎索要下一條記錄，存儲引擎在二級索引定位到記錄後，獲取主鍵值，然後回表操作，將完整的記錄返回給 Server 層；
- 如此往復，直到存儲引擎把表中的所有記錄讀完。
可以看到，沒有索引下推的時候，每查詢到一條二級索引記錄，都要進行回表操作，然後將記錄返回給 Server，接著 Server 再判斷該記錄的 reward 是否等於 100000。
而使用索引下推後，判斷記錄的 reward 是否等於 100000 的工作交給了存儲引擎層，過程如下 ：
- Server 層首先調用存儲引擎的接口定位到滿足查詢條件的第一條二級索引記錄，也就是定位到 age > 20 的第一條記錄；
- 存儲引擎定位到二級索引後，先不執行回表操作，而是先判斷一下該索引中包含的列（reward 列）的條件（reward 是否等於 100000）是否成立。如果條件不成立，則直接跳過該二級索引。如果成立，則執行回表操作，將完成記錄返回給 Server 層。
- Server 層在判斷其他的查詢條件（本次查詢沒有其他條件）是否成立，如果成立則將其發送給客戶端；否則跳過該記錄，然後向存儲引擎索要下一條記錄。
- 如此往復，直到存儲引擎把表中的所有記錄讀完。
可以看到，使用了索引下推後，雖然 reward 列無法使用到聯合索引，但是因為它包含在聯合索引（age，reward）裡，所以直接在存儲引擎過濾出滿足 reward = 100000 的記錄後，才去執行回表操作獲取整個記錄。相比於沒有使用索引下推，節省了很多回表操作。
當你發現執行計劃裡的 Extr 部分顯示了 “Using index condition”，說明使用了索引下推。
MySQL 的記錄是怎麼存儲的？#
MySQL 的數據存放在哪個文件？#
MySQL 的數據都是保存在磁碟的，那具體是保存在哪個文件，以 InnoDB 存儲引擎展開討論。
共有三個文件，這三個文件分別代表著：
- db.opt，用來存儲當前數據庫的默認字符集和字符校驗規則。
- t_order.frm ，t_order 的表結構會保存在這個文件。在 MySQL 中建立一張表都會生成一個.frm 文件，該文件是用來保存每個表的元數據信息的，主要包含表結構定義。
- t_order.ibd，t_order 的表數據會保存在這個文件。表數據既可以存在共享表空間文件（文件名：ibdata1）裡，也可以存放在獨占表空間文件（文件名：表名字.ibd）。這個行為是由參數 innodb_file_per_table 控制的，若設置了參數 innodb_file_per_table 為 1，則會將存儲的數據、索引等信息單獨存儲在一個獨占表空間，從 MySQL 5.6.6 版本開始，它的默認值就是 1 了，因此從這個版本之後， MySQL 中每一張表的數據都存放在一個獨立的 .ibd 文件。
好了，現在我們知道了一張數據庫表的數據是保存在「 表名字.ibd 」的文件裡的，這個文件也稱為獨占表空間文件。
表空間文件的結構是怎麼樣的？#
表空間由段（segment）、區（extent）、頁（page）、行（row）組成，InnoDB 存儲引擎的邏輯存儲結構大致如下圖：
行（row）#
數據庫表中的記錄都是按行（row）進行存放的，每行記錄根據不同的行格式，有不同的存儲結構。後面會詳細介紹
頁（page）#
記錄是按照行來存儲的，但是數據庫的讀取並不以「行」為單位，否則一次讀取（也就是一次 I/O 操作）只能處理一行數據，效率會非常低。
因此，InnoDB 的數據是按「頁」為單位來讀寫的，也就是說，當需要讀一條記錄的時候，並不是將這個行記錄從磁碟讀出來，而是以頁為單位，將其整體讀入內存。
默認每個頁的大小為 16KB，也就是最多能保證 16KB 的連續存儲空間。
頁是 InnoDB 存儲引擎磁碟管理的最小單元，意味著數據庫每次讀寫都是以 16KB 為單位的，一次最少從磁碟中讀取 16K 的內容到內存中，一次最少把內存中的 16K 內容刷新到磁碟中。
頁的類型有很多，常見的有數據頁、undo 日誌頁、溢出頁等等。數據表中的行記錄是用「數據頁」來管理的。
區（extent）#
我們知道 InnoDB 存儲引擎是用 B+ 樹來組織數據的。
B+ 樹中葉子節點層是通過雙向鏈表連接起來的，如果是以頁為單位來分配存儲空間，那麼鏈表中相鄰的兩個頁之間的物理位置並不是連續的，可能離得非常遠，那麼磁碟查詢時就會有大量的隨機 I/O，隨機 I/O 是非常慢的。
解決這個問題也很簡單，就是讓鏈表中相鄰的頁的物理位置也相鄰，這樣就可以使用順序 I/O 了，那麼在範圍查詢（掃描葉子節點）時候性能就會很高。
那具體怎麼解決呢？
在表中數據量大的時候，為某個索引分配空間的時候就不再按照頁為單位分配了，而是按照區（extent）為單位分配。每個區的大小為 1MB，對於 16KB 的頁來說，連續的 64 個頁會被劃為一個區，這樣就使得鏈表中相鄰的頁的物理位置也相鄰，就能使用順序 I/O 了。
段（segment）#
表空間是由各個段（segment）組成的，段是由多個區（extent）組成的。段一般分為數據段、索引段和回滾段等。
- 索引段：存放 B + 樹的非葉子節點的區的集合；
- 數據段：存放 B + 樹的葉子節點的區的集合；
- 回滾段：存放的是回滾數據的區的集合，之前講事務隔離 (opens new window) 的時候就介紹到了 MVCC 利用了回滾段實現了多版本查詢數據。
InnoDB 行格式有哪些？#
InnoDB 提供了 4 種行格式，分別是 Redundant、Compact、Dynamic 和 Compressed 行格式。
Redundant 行格式現在基本沒人用了，這次重點介紹 Compact 行格式，因為 Dynamic 和 Compressed 這兩個行格式跟 Compact 非常像。
COMPACT 行格式長什麼樣？#
一條完整的記錄分為「記錄的額外信息」和「記錄的真實數據」兩個部分。
記錄的額外信息#
記錄的額外信息包含 3 個部分：變長字段長度列表、NULL 值列表、記錄頭信息。
變長字段長度列表
varchar (n) 和 char (n) 的區別是什麼，相信大家都非常清楚，char 是定長的，varchar 是變長的，變長字段實際存儲的數據的長度（大小）不固定的。
所以，在存儲數據的時候，也要把數據佔用的大小存起來，存到「變長字段長度列表」裡面，讀取數據的時候才能根據這個「變長字段長度列表」去讀取對應長度的數據。其他 TEXT、BLOB 等變長字段也是這麼實現的。
為什麼「變長字段長度列表」的信息要按照逆序存放？
因為「記錄頭信息」中指向下一個記錄的指針，指向的是下一條記錄的「記錄頭信息」和「真實數據」之間的位置，這樣的好處是向左讀就是記錄頭信息，向右讀就是真實數據，比較方便。
同樣的道理， NULL 值列表的信息也需要逆序存放。
每個數據庫表的行格式都有「變長字段字節數列表」嗎？
當數據表沒有變長字段的時候，比如全部都是 int 類型的字段，這時候表裡的行格式就不會有「變長字段長度列表」了
varchar (n) 中 n 最大取值為多少？
MySQL 規定除了 TEXT、BLOBs 這種大對象類型之外，其他所有的列（不包括隱藏列和記錄頭信息）佔用的字節長度加起來不能超過 65535 個字節。
varchar (n) 字段類型的 n 代表的是最多存儲的字符數量，並不是字節大小。要算 varchar (n) 最大能允許存儲的字節數，還要看數據庫表的字符集，因為字符集代表著，1 個字符要佔用多少字節，比如 ascii 字符集， 1 個字符佔用 1 字節，那麼 varchar (100) 意味著最大能允許存儲 100 字節的數據。
如果有多個字段的話，要保證所有字段的長度 + 變長字段字節數列表所佔用的字節數 + NULL 值列表所佔用的字節數 <= 65535。
NULL 值列表
如果存在允許 NULL 值的列，則每個列對應一個二進制位（bit），二進制位按照列的順序逆序排列。
- 二進制位的值為 1 時，代表該列的值為 NULL。
- 二進制位的值為 0 時，代表該列的值不為 NULL。
另外，NULL 值列表必須用整數個字節的位表示（1 字節 8 位），如果使用的二進制位個數不足整數個字節，則在字節的高位補 0。
每個數據庫表的行格式都有「NULL 值列表」嗎？
當數據表的字段都定義成 NOT NULL 的時候，這時候表裡的行格式就不會有 NULL 值列表了。
所以在設計數據庫表的時候，通常都是建議將字段設置為 NOT NULL，這樣可以至少節省 1 字節的空間（NULL 值列表至少佔用 1 字節空間）。
「NULL 值列表」是固定 1 字節空間嗎？如果這樣的話，一條記錄有 9 個字段值都是 NULL，這時候怎麼表示？
「NULL 值列表」的空間不是固定 1 字節的。
當一條記錄有 9 個字段值都是 NULL，那麼就會創建 2 字節空間的「NULL 值列表」，以此類推。
記錄頭信息
記錄頭信息中包含的內容很多，我就不一一列舉了，這裡說幾個比較重要的：
- delete_mask ：標識此條數據是否被刪除。從這裡可以知道，我們執行 detele 刪除記錄的時候，並不會真正的刪除記錄，只是將這個記錄的 delete_mask 標記為 1。
- next_record：下一條記錄的位置。從這裡可以知道，記錄與記錄之間是通過鏈表組織的。在前面我也提到了，指向的是下一條記錄的「記錄頭信息」和「真實數據」之間的位置，這樣的好處是向左讀就是記錄頭信息，向右讀就是真實數據，比較方便。
- record_type：表示當前記錄的類型，0 表示普通記錄，1 表示 B + 樹非葉子節點記錄，2 表示最小記錄，3 表示最大記錄
記錄的真實數據#
記錄真實數據部分除了我們定義的字段，還有三個隱藏字段，分別為：row_id、trx_id、roll_pointer，我們來看下這三個字段是什麼。
- row_id: 如果我們建表的時候指定了主鍵或者唯一約束列，那麼就沒有 row_id 隱藏字段了。如果既沒有指定主鍵，又沒有唯一約束，那麼 InnoDB 就會為記錄添加 row_id 隱藏字段。row_id 不是必需的，佔用 6 個字節。
- trx_id: 事務 id，表示這個數據是由哪個事務生成的。 trx_id 是必需的，佔用 6 個字節。
- roll_pointer: 這條記錄上個版本的指針。roll_pointer 是必需的，佔用 7 個字節。
trx_id 和 roll_pointer 與 MVCC 機制有關。
行溢出後，MySQL 是怎麼處理的？#
MySQL 中磁碟和內存交互的基本單位是頁，一個頁的大小一般是 16KB，也就是 16384 字節，而一個 varchar (n) 類型的列最多可以存儲 65532 字節，一些大對象如 TEXT、BLOB 可能存儲更多的數據，這時一個頁可能就存不了一條記錄。這個時候就會發生行溢出，多的數據就會存到另外的「溢出頁」中。
如果一個數據頁存不了一條記錄，InnoDB 存儲引擎會自動將溢出的數據存放到「溢出頁」中。在一般情況下，InnoDB 的數據都是存放在 「數據頁」中。但是當發生行溢出時，溢出的數據會存放到「溢出頁」中。
當發生行溢出時，在記錄的真實數據處只會保存該列的一部分數據，而把剩餘的數據放在「溢出頁」中，然後真實數據處用 20 字節存儲指向溢出頁的地址，從而可以找到剩餘數據所在的頁。大致如下圖所示。
上面這個是 Compact 行格式在發生行溢出後的處理。
Compressed 和 Dynamic 這兩個行格式和 Compact 非常類似，主要的區別在於處理行溢出數據時有些區別。
這兩種格式採用完全的行溢出方式，記錄的真實數據處不會存儲該列的一部分數據，只存儲 20 個字節的指針來指向溢出頁。而實際的數據都存儲在溢出頁中，看起來就像下面這樣：