一條 sql 的執行過程詳解

萌新J發表於2020-12-03

寫操作執行過程

　　如果這條sql是寫操作(insert、update、delete)，那麼大致的過程如下，其中引擎層是屬於 InnoDB 儲存引擎的，因為InnoDB 是預設的儲存引擎，也是主流的，所以這裡只說明 InnoDB 的引擎層過程。由於寫操作較查詢操作更為複雜，所以先看一下寫操作的執行圖。方便後面解析。

元件介紹

Server層

1、聯結器

　　1）負責與客戶端的通訊，是半雙工模式，這就意味著某一固定時刻只能由客戶端向伺服器請求或者伺服器向客戶端傳送資料，而不能同時進行。

　　2）驗證使用者名稱和密碼是否正確（資料庫mysql的user表中進行驗證），如果錯誤返回錯誤通知（deAcess nied for user 'root'@'localhost'（using password：YES）），如果正確，則會去 mysql 的許可權表（mysql中的 user、db、columns_priv、Host 表，分別儲存的是全域性級別、資料庫級別、表級別、列級別、配合 db 的資料庫級別）查詢當前使用者的許可權。

2、快取（Cache）

　　也稱為查詢快取，儲存的資料是以鍵值對的形式進行儲存，如果開啟了快取，那麼在一條查詢sql語句進來時會先判斷快取中是否包含當前的sql語句鍵值對，如果存在直接將其對應的結果返回，如果不存在再執行後面一系列操作。如果沒有開啟則直接跳過。

相關操作：

檢視快取配置：show variables like 'have_query_cache';

檢視是否開啟：show variables like 'query_cache_type';

檢視快取佔用大小：show variables like 'query_cache_size';

檢視快取狀態資訊：show status like 'Qcache%';

相關引數的含義：

快取失效場景：

1、查詢語句不一致。前後兩條查詢SQL必須完全一致。

2、查詢語句中含有一些不確定的值時，則不會快取。比如 now()、current_date()、curdate()、curtime()、rand()、uuid()等。

3、不使用任何表查詢。如 select 'A';

4、查詢 mysql、information_schema 或 performance_schema 資料庫中的表時，不會走查詢快取。

5、在儲存的函式，觸發器或事件的主體內執行的查詢。

6、如果表更改，則使用該表的所有快取記憶體查詢都變為無效並從快取中刪除，這包括使用 MERGE 對映到已更改表的表的查詢。一個表可以被許多型別的語句改變，如 insert、update、delete、truncate rable、alter table、drop table、drop database。

通過上面的失效場景可以看出快取是很容易失效的，所以如果不是查詢次數遠大於修改次數的話，使用快取不僅不能提升查詢效率還會拉低效率（每次讀取後需要向快取中儲存一份，而快取又容易被清除）。所以在 MYSQL5.6預設是關閉快取的，並且在 8.0 直接被移除了。當然，如果場景需要用到，還是可以使用的。

開啟

在配置檔案(linux下是安裝目錄的cnf檔案，windows是安裝目錄下的ini檔案)中，增加配置： query_cache_type = 1

關於 query_type_type 引數的說明：

指定 SQL_NO_CACHE：select SQL_NO_CACHE * from student where age >20; SQL_CACHE 同理。

3、分析器

對客戶端傳來的 sql 進行分析，這將包括預處理與解析過程，並進行關鍵詞的提取、解析，並組成一個解析樹。具體的解析詞包括但不侷限於 select/update/delete/or/in/where/group by/having/count/limit 等，如果分析到語法錯誤，會直接拋給客戶端異常：ERROR:You have an error in your SQL syntax.

比如：select * from user where userId =1234;

在分析器中就通過語義規則器將select from where這些關鍵詞提取和匹配出來,mysql會自動判斷關鍵詞和非關鍵詞，將使用者的匹配欄位和自定義語句識別出來。這個階段也會做一些校驗:比如校驗當前資料庫是否存在user表，同時假如User表中不存在userId這個欄位同樣會報錯：unknown column in field list.

4、優化器

進入優化器說明sql語句是符合標準語義規則並且可以執行。優化器會根據執行計劃選擇最優的選擇，匹配合適的索引，選擇最佳的方案。比如一個典型的例子是這樣的：

表T,對A、B、C列建立聯合索引(A,B,C)，在進行查詢的時候，當sql查詢條件是:select xx where B=x and A=x and C=x.很多人會以為是用不到索引的，但其實會用到,雖然索引必須符合最左原則才能使用,但是本質上,優化器會自動將這條sql優化為:where A=x and B=x and C=X,這種優化會為了底層能夠匹配到索引，同時在這個階段是自動按照執行計劃進行預處理,mysql會計算各個執行方法的最佳時間,最終確定一條執行的sql交給最後的執行器

5、執行器

執行器會呼叫對應的儲存引擎執行 sql。主流的是MyISAM 和 Innodb。

儲存引擎（InnoDB）層

1、undo log 與 MVCC

undo log是 Innodb 引擎專屬的日誌，是記錄每行資料事務執行前的資料。主要作用是用於實現MVCC版本控制，保證事務隔離級別的讀已提交和讀未提交級別。而 MVCC 相關的可以參考 MySQL中的事務原理和鎖機制。

2、redo log 與 Buffer Pool

InnoDB 內部維護了一個緩衝池，用於減少對磁碟資料的直接IO操作，並配合 redo log 來實現非同步的落盤，保證程式的高效執行。redo log 大小固定，採用迴圈寫

write pos 表示當前正在記錄的位置，會向後記錄， checkpoint 表示資料落盤的邊界，也就是 checkpoint 與 write pos中間是已記錄的，當 write pos寫完 id_logfile_3後，會回到id_logfile_0迴圈寫，而追上 checkpomnit 後則需要先等資料進行落盤，等待 checkponit向後面移動一段距離再寫。redo log儲存的內容個人認為當直接更新到資料頁快取時記錄的就是資料頁邏輯，如果更新到 Change Buffer 那麼就是操作的 sql。

關於 Buffer Pool詳情可檢視部落格 InnoDB 中的緩衝池(Buffer Pool)。

3、bin log(Server 層)

redo log 因為大小固定，所以不能儲存過多的資料，它只能用於未更新的資料落盤，而資料操作的備份恢復、以及主從複製是靠 bin log（如果資料庫誤刪需要還原，那麼需要某個時間點的資料備份以及bin log）。5.7預設記錄的是修改後的行記錄。

在更新到資料頁快取或者 Change Buffer 後，首先進行 redo log 的編寫，此時 redo log 處於 prepare 狀態，隨後再進行 bin log 的編寫，等到 bin log 也編寫完成後再將 redo log 設定為 commit 狀態。這是為了防止資料庫當機導致 bin log 沒有將修改記錄寫入，後面資料恢復、主從複製時資料不一致。當資料庫啟動後如果發現 redo log 為 prepare 狀態，那麼就會檢查 bin log 與 redo log 最近的記錄是否對的上，如果對的上就提交，對不上就進行事務回滾。

三種格式：

1、Row（5.7預設）。記錄被修改後的行記錄。缺點是佔空間大。優點是能保證資料安全，不會發生遺漏。

2、Statement。記錄修改的 sql。缺點是在 mysql 叢集時可能會導致操作不一致從而使得資料不一致（比如在操作中加入了Now()函式，主從資料庫操作的時間不同結果也不同）。優點是佔空間小，執行快。

3、Mixed。會針對於操作的 sql 選擇使用Row 還是 Statement。缺點是還是可能發生主從不一致的情況。

三個日誌的比較（undo、redo、bin）

1、undo log是用於事務的回滾、保證事務隔離級別讀已提交、可重複讀實現的。redo log是用於對暫不更新到磁碟上的操作進行記錄，使得其可以延遲落盤，保證程式的效率。bin log是對資料操作進行備份恢復（並不能依靠 bin log 直接完成資料恢復）。

2、undo log 與 redo log 是儲存引擎層的日誌，只能在 InnoDB 下使用；而bin log 是 Server 層的日誌，可以在任何引擎下使用。

3、redo log 大小有限，超過後會迴圈寫；另外兩個大小不會。

4、undo log 記錄的是行記錄變化前的資料；redo log 記錄的是 sql 或者是資料頁修改邏輯或 sql（個人理解）；bin log記錄的是修改後的行記錄（5.7預設）或者sql語句。

執行過程

寫操作

通過上面的分析，可以很容易地瞭解開始的更新執行圖。這裡就不過多闡述了。

讀操作

查詢的過程和更新比較相似，但是有些不同，主要是來源於他們在查詢篩選時的不同，更新因為在查詢後會進行更新操作，所以查詢這一行為至始至終都在緩衝池中（使用到索引且緩衝池中包含資料對應的資料頁）。而查詢則更復雜一些。

Where 條件的提取

在 MySQL 5.6開始，引入了一種索引優化策略——索引下推，其本質優化的就是 Where 條件的提取。Where 提取過程是怎樣的？用一個例子來說明，首先進行建表，插入記錄。

create table tbl_test (a int primary key, b int, c int, d int, e varchar(50));
create index idx_bcd on tbl_test(b, c, d);
insert into tbl_test values (4,3,1,1,'a');
insert into tbl_test values (1,1,1,2,'d');
insert into tbl_test values (8,8,7,8,'h');
insert into tbl_test values (2,2,1,2,'g');
insert into tbl_test values (5,2,2,5,'e');
insert into tbl_test values (3,3,2,1,'c');
insert into tbl_test values (7,4,0,5,'b');
insert into tbl_test values (6,5,2,4,'f');

那麼執行 select * from tbl_test where b >= 2 and b < 7 and c > 0 and d != 2 and e != 'a'; 在提取時，會將 Where 條件拆分為 Index Key（First Key & Last Key）、Index Filter 與 Table Filter。

1、Index Key

用於確定 SQL 查詢在索引中的連續範圍（起始點 + 終止點）的查詢條件，被稱之為Index Key；由於一個範圍，至少包含一個起始條件與一個終止條件，因此 Index Key 也被拆分為 Index First Key 和 Index Last Key，分別用於定位索引查詢的起始點以終止點

　　　　Index First Key

　　　　用於確定索引查詢範圍的起始點；提取規則：從索引的第一個鍵值開始，檢查其在 where 條件中是否存在，若存在並且條件是 =、>=，則將對應的條件加入Index First Key之中，繼續讀取索引的下一個鍵值，使用同樣的提取規則；若存在並且條件是 >，則將對應的條件加入 Index First Key 中，同時終止 Index First Key 的提取；若不存在，同樣終止 Index First Key 的提取

　　　　針對 SQL：select * from tbl_test where b >= 2 and b < 7 and c > 0 and d != 2 and e != 'a'，應用這個提取規則，提取出來的 Index First Key 為 b >= 2, c > 0 ，由於 c 的條件為 >，提取結束

　　　　Index Last Key

　　　　用於確定索引查詢範圍的終止點，與 Index First Key 正好相反；提取規則：從索引的第一個鍵值開始，檢查其在 where 條件中是否存在，若存在並且條件是 =、<=，則將對應條件加入到 Index Last Key 中，繼續提取索引的下一個鍵值，使用同樣的提取規則；若存在並且條件是 < ，則將條件加入到 Index Last Key 中，同時終止提取；若不存在，同樣終止Index Last Key的提取

　　　　針對 SQL：select * from tbl_test where b >= 2 and b < 7 and c > 0 and d != 2 and e != 'a'，應用這個提取規則，提取出來的 Index Last Key為 b < 7 ，由於是 < 符號，提取結束

2、Index Filter

在完成 Index Key 的提取之後，我們根據 where 條件固定了索引的查詢範圍，那麼是不是在範圍內的每一個索引項都滿足 WHERE 條件了？很明顯 4,0,5 ， 2,1,2 均屬於範圍中，但是又均不滿足SQL 的查詢條件

　　　　所以 Index Filter 用於索引範圍確定後，確定 SQL 中還有哪些條件可以使用索引來過濾；提取規則：從索引列的第一列開始，檢查其在 where 條件中是否存在，若存在並且 where 條件僅為 =，則跳過第一列繼續檢查索引下一列，下一索引列採取與索引第一列同樣的提取規則；若 where 條件為 >=、>、<、<= 其中的幾種，則跳過索引第一列，將其餘 where 條件中索引相關列全部加入到 Index Filter 之中；若索引第一列的 where 條件包含 =、>=、>、<、<= 之外的條件，則將此條件以及其餘 where 條件中索引相關列全部加入到 Index Filter 之中；若第一列不包含查詢條件，則將所有索引相關條件均加入到 Index Filter之中

　　　　針對 SQL：select * from tbl_test where b >= 2 and b < 7 and c > 0 and d != 2 and e != 'a'，應用這個提取規則，提取出來的 Index Filter 為 c > 0 and d != 2 ，因為索引第一列只包含 >=、< 兩個條件，因此第一列跳過，將餘下的 c、d 兩列加入到 Index Filter 中，提取結束

3、Table Filter

這個就比較簡單了，where 中不能被索引過濾的條件都歸為此中；提取規則：所有不屬於索引列的查詢條件，均歸為 Table Filter 之中

針對 SQL：select * from tbl_test where b >= 2 and b < 7 and c > 0 and d != 2 and e != 'a'，應用這個提取規則，那麼 Table Filter 就為 e != 'a'

在5.6 之前，是不分 Table Filter 與 Index Filter 的，這兩個條件都直接分配到 Server 層進行篩選。篩選過程是先根據 Index Key 的條件先在引擎層進行初步篩選，然後得到對應的主鍵值進行回表查詢得到初篩的行記錄，傳入 Server 層進行後續的篩選，在 Server 層的篩選因為沒有用到索引所以會進行全表掃描。而索引下推的優化就是將 Index Filter 的條件下推到引擎層，在使用 Index First Key 與 Index Last Key 進行篩選時，就帶上 Index Filter 的條件再次篩選，以此來過濾掉不符合條件的記錄對應的主鍵值，減少回表的次數，同時發給 Server 層的記錄也會更少，全表掃描篩選的效率也會變高。下面是未使用索引下推和使用索引下推的示意圖。