一條 sql 的執(zhí)行過程詳解
寫操作執(zhí)行過程
如果這條sql是寫操作(insert、update、delete)，那么大致的過程如下，其中引擎層是屬于 InnoDB 存儲引擎的，因為InnoDB 是默認的存儲引擎，也是主流的，所以這里只說明 InnoDB 的引擎層過程。由于寫操作較查詢操作更為復(fù)雜，所以先看一下寫操作的執(zhí)行圖。方便后面解析。
0x01: 組件介紹?
Server層
1、連接器
1）負責(zé)與客戶端的通信，是半雙工模式，這就意味著某一固定時刻只能由客戶端向服務(wù)器請求或者服務(wù)器向客戶端發(fā)送數(shù)據(jù)，而不能同時進行。
2）驗證用戶名和密碼是否正確（數(shù)據(jù)庫mysql的user表中進行驗證），如果錯誤返回錯誤通知（deAcess ?nied ?for ?user ?'root'@'localhost'（using password：YES）），如果正確，則會去 mysql 的權(quán)限表（mysql中的 user、db、columns_priv、Host 表，分別存儲的是全局級別、數(shù)據(jù)庫級別、表級別、列級別、配合 db 的數(shù)據(jù)庫級別）查詢當(dāng)前用戶的權(quán)限。
2、緩存（Cache）
也稱為查詢緩存，存儲的數(shù)據(jù)是以鍵值對的形式進行存儲，如果開啟了緩存，那么在一條查詢sql語句進來時會先判斷緩存中是否包含當(dāng)前的sql語句鍵值對，如果存在直接將其對應(yīng)的結(jié)果返回，如果不存在再執(zhí)行后面一系列操作。如果沒有開啟則直接跳過。
相關(guān)操作：
查看緩存配置：show ?variables ?like ?'have_query_cache';
查看是否開啟：show ?variables ?like ?'query_cache_type';
查看緩存占用大?。簊how ?variables ?like ?'query_cache_size';
查看緩存狀態(tài)信息：show ?status ?like ?'Qcache%';
相關(guān)參數(shù)的含義：
?緩存失效場景：
1、查詢語句不一致。前后兩條查詢SQL必須完全一致。
2、查詢語句中含有一些不確定的值時，則不會緩存。比如 now()、current_date()、curdate()、curtime()、rand()、uuid()等。
3、不使用任何表查詢。如 select 'A';
4、查詢 mysql、information_schema 或 performance_schema 數(shù)據(jù)庫中的表時，不會走查詢緩存。
5、在存儲的函數(shù)，觸發(fā)器或事件的主體內(nèi)執(zhí)行的查詢。
6、如果表更改，則使用該表的所有高速緩存查詢都變?yōu)闊o效并從緩存中刪除，這包括使用 MERGE 映射到已更改表的表的查詢。一個表可以被許多類型的語句改變，如 insert、update、delete、truncate rable、alter table、drop table、drop database。
?
通過上面的失效場景可以看出緩存是很容易失效的，所以如果不是查詢次數(shù)遠大于修改次數(shù)的話，使用緩存不僅不能提升查詢效率還會拉低效率（每次讀取后需要向緩存中保存一份，而緩存又容易被清除）。所以在 MYSQL5.6默認是關(guān)閉緩存的，并且在 8.0?直接被移除了。當(dāng)然，如果場景需要用到，還是可以使用的。
開啟
在配置文件(linux下是安裝目錄的cnf文件，windows是安裝目錄下的ini文件)中，增加配置：?query_cache_type = 1
關(guān)于 query_type_type 參數(shù)的說明：
指定 SQL_NO_CACHE：select ?SQL_NO_CACHE ?*? from ?student ?where age >20;? ? SQL_CACHE 同理。
3、分析器
對客戶端傳來的 sql 進行分析，這將包括預(yù)處理與解析過程，并進行關(guān)鍵詞的提取、解析，并組成一個解析樹。具體的解析詞包括但不局限于 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;
在分析器中就通過語義規(guī)則器將select from where這些關(guān)鍵詞提取和匹配出來,mysql會自動判斷關(guān)鍵詞和非關(guān)鍵詞，將用戶的匹配字段和自定義語句識別出來。這個階段也會做一些校驗:比如校驗當(dāng)前數(shù)據(jù)庫是否存在user表，同時假如User表中不存在userId這個字段同樣會報錯：unknown column in field list.
4、優(yōu)化器
進入優(yōu)化器說明sql語句是符合標(biāo)準(zhǔn)語義規(guī)則并且可以執(zhí)行。優(yōu)化器會根據(jù)執(zhí)行計劃選擇最優(yōu)的選擇，匹配合適的索引，選擇最佳的方案。比如一個典型的例子是這樣的：
?
表T,對A、B、C列建立聯(lián)合索引(A,B,C)，在進行查詢的時候，當(dāng)sql查詢條件是:select xx where ?B=x and A=x and C=x.很多人會以為是用不到索引的，但其實會用到,雖然索引必須符合最左原則才能使用,但是本質(zhì)上,優(yōu)化器會自動將這條sql優(yōu)化為:where A=x and B=x and C=X,這種優(yōu)化會為了底層能夠匹配到索引，同時在這個階段是自動按照執(zhí)行計劃進行預(yù)處理,mysql會計算各個執(zhí)行方法的最佳時間,最終確定一條執(zhí)行的sql交給最后的執(zhí)行器
5、執(zhí)行器
執(zhí)行器會調(diào)用對應(yīng)的存儲引擎執(zhí)行 sql。主流的是MyISAM 和 Innodb。
存儲引擎（InnoDB）層
1、undo log?與 MVCC
undo log是 Innodb 引擎專屬的日志，是記錄每行數(shù)據(jù)事務(wù)執(zhí)行前的數(shù)據(jù)。主要作用是用于實現(xiàn)MVCC版本控制，保證事務(wù)隔離級別的讀已提交和讀未提交級別。而 MVCC 相關(guān)的可以參考 MySQL中的事務(wù)原理和鎖機制。
?
2、redo log?與 Buffer Pool
InnoDB 內(nèi)部維護了一個緩沖池，用于減少對磁盤數(shù)據(jù)的直接IO操作，并配合 redo log 來實現(xiàn)異步的落盤，保證程序的高效執(zhí)行。redo log 大小固定，采用循環(huán)寫
?
?
?write pos 表示當(dāng)前正在記錄的位置，會向后記錄， checkpoint 表示數(shù)據(jù)落盤的邊界，也就是 checkpoint 與 write pos中間是已記錄的，當(dāng) write pos寫完 id_logfile_3后，會回到id_logfile_0循環(huán)寫，而追上 checkpomnit 后則需要先等數(shù)據(jù)進行落盤，等待 checkponit向后面移動一段距離再寫。redo log存儲的內(nèi)容個人認為當(dāng)直接更新到數(shù)據(jù)頁緩存時記錄的就是數(shù)據(jù)頁邏輯，如果更新到 Change Buffer 那么就是操作的 sql。
關(guān)于 Buffer Pool詳情可查看博客 InnoDB 中的緩沖池(Buffer Pool)。
?
3、bin log(Server?層)
redo log 因為大小固定，所以不能存儲過多的數(shù)據(jù)，它只能用于未更新的數(shù)據(jù)落盤，而數(shù)據(jù)操作的備份恢復(fù)、以及主從復(fù)制是靠 bin log（如果數(shù)據(jù)庫誤刪需要還原，那么需要某個時間點的數(shù)據(jù)備份以及bin log）。5.7默認記錄的是修改后的行記錄。
在更新到數(shù)據(jù)頁緩存或者 Change Buffer 后，首先進行 redo log 的編寫，此時 redo log 處于 prepare 狀態(tài)，隨后再進行 bin log 的編寫，等到 bin log 也編寫完成后再將 redo log 設(shè)置為 commit 狀態(tài)。這是為了防止數(shù)據(jù)庫宕機導(dǎo)致 bin log 沒有將修改記錄寫入，后面數(shù)據(jù)恢復(fù)、主從復(fù)制時數(shù)據(jù)不一致。當(dāng)數(shù)據(jù)庫啟動后如果發(fā)現(xiàn) redo log 為 prepare 狀態(tài)，那么就會檢查 bin log 與 redo log 最近的記錄是否對的上，如果對的上就提交，對不上就進行事務(wù)回滾。
三種格式：
1、Row（5.7默認）。記錄被修改后的行記錄。缺點是占空間大。優(yōu)點是能保證數(shù)據(jù)安全，不會發(fā)生遺漏。
2、Statement。記錄修改的 sql。缺點是在 mysql 集群時可能會導(dǎo)致操作不一致從而使得數(shù)據(jù)不一致（比如在操作中加入了Now()函數(shù)，主從數(shù)據(jù)庫操作的時間不同結(jié)果也不同）。優(yōu)點是占空間小，執(zhí)行快。
3、Mixed。會針對于操作的 sql 選擇使用Row 還是 Statement。缺點是還是可能發(fā)生主從不一致的情況。
三個日志的比較（undo、redo、bin）
1、undo log是用于事務(wù)的回滾、保證事務(wù)隔離級別讀已提交、可重復(fù)讀實現(xiàn)的。redo log是用于對暫不更新到磁盤上的操作進行記錄，使得其可以延遲落盤，保證程序的效率。bin log是對數(shù)據(jù)操作進行備份恢復(fù)（并不能依靠 bin log 直接完成數(shù)據(jù)恢復(fù)）。
2、undo log 與 redo log 是存儲引擎層的日志，只能在 InnoDB 下使用；而bin log 是 Server 層的日志，可以在任何引擎下使用。
3、redo log 大小有限，超過后會循環(huán)寫；另外兩個大小不會。
4、undo log 記錄的是行記錄變化前的數(shù)據(jù)；redo log 記錄的是 sql 或者是數(shù)據(jù)頁修改邏輯或 sql（個人理解）；bin log記錄的是修改后的行記錄（5.7默認）或者sql語句。
?
0x02: 執(zhí)行過程
寫操作
?通過上面的分析，可以很容易地了解開始的更新執(zhí)行圖。這里就不過多闡述了。
讀操作
查詢的過程和更新比較相似，但是有些不同，主要是來源于他們在查找篩選時的不同，更新因為在查找后會進行更新操作，所以查詢這一行為至始至終都在緩沖池中（使用到索引且緩沖池中包含數(shù)據(jù)對應(yīng)的數(shù)據(jù)頁）。而查詢則更復(fù)雜一些。
Where?條件的提取
在 MySQL 5.6開始，引入了一種索引優(yōu)化策略——索引下推，其本質(zhì)優(yōu)化的就是 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');
?那么執(zhí)行 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 查詢在索引中的連續(xù)范圍（起始點 + 終止點）的查詢條件，被稱之為Index Key；由于一個范圍，至少包含一個起始條件與一個終止條件，因此 Index Key 也被拆分為 Index First Key 和 Index Last Key，分別用于定位索引查找的起始點以終止點
Index First Key
用于確定索引查詢范圍的起始點；提取規(guī)則：從索引的第一個鍵值開始，檢查其在 where 條件中是否存在，若存在并且條件是 =、>=，則將對應(yīng)的條件加入Index First Key之中，繼續(xù)讀取索引的下一個鍵值，使用同樣的提取規(guī)則；若存在并且條件是 >，則將對應(yīng)的條件加入 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'，應(yīng)用這個提取規(guī)則，提取出來的 Index First Key 為?b?>=?2, c?>?0?，由于 c 的條件為 >，提取結(jié)束
Index Last Key
用于確定索引查詢范圍的終止點，與 Index First Key 正好相反；提取規(guī)則：從索引的第一個鍵值開始，檢查其在 where 條件中是否存在，若存在并且條件是 =、<=，則將對應(yīng)條件加入到 Index Last Key 中，繼續(xù)提取索引的下一個鍵值，使用同樣的提取規(guī)則；若存在并且條件是 < ，則將條件加入到 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'，應(yīng)用這個提取規(guī)則，提取出來的 Index Last Key為?b?<?7?，由于是 < 符號，提取結(jié)束
2、Index Filter
在完成 Index Key 的提取之后，我們根據(jù) where 條件固定了索引的查詢范圍，那么是不是在范圍內(nèi)的每一個索引項都滿足 WHERE 條件了 ？很明顯?4,0,5?，?2,1,2?均屬于范圍中，但是又均不滿足SQL 的查詢條件
所以 Index Filter 用于索引范圍確定后，確定 SQL 中還有哪些條件可以使用索引來過濾；提取規(guī)則：從索引列的第一列開始，檢查其在 where 條件中是否存在，若存在并且 where 條件僅為 =，則跳過第一列繼續(xù)檢查索引下一列，下一索引列采取與索引第一列同樣的提取規(guī)則；若 where 條件為 >=、>、<、<= 其中的幾種，則跳過索引第一列，將其余 where 條件中索引相關(guān)列全部加入到 Index Filter 之中；若索引第一列的 where 條件包含 =、>=、>、<、<= 之外的條件，則將此條件以及其余 where 條件中索引相關(guān)列全部加入到 Index Filter 之中；若第一列不包含查詢條件，則將所有索引相關(guān)條件均加入到 Index Filter之中
針對 SQL：select * from tbl_test where b >= 2 and b < 7 and c > 0 and d != 2 and e != 'a'，應(yīng)用這個提取規(guī)則，提取出來的 Index Filter 為?c?>?0?and?d?!=?2?，因為索引第一列只包含 >=、< 兩個條件，因此第一列跳過，將余下的 c、d 兩列加入到 Index Filter 中，提取結(jié)束
3、Table Filter
這個就比較簡單了，where 中不能被索引過濾的條件都歸為此中；提取規(guī)則：所有不屬于索引列的查詢條件，均歸為 Table Filter 之中
針對 SQL：select * from tbl_test where b >= 2 and b < 7 and c > 0 and d != 2 and e != 'a'，應(yīng)用這個提取規(guī)則，那么 Table Filter 就為??e?!=?'a'?
?
在5.6 之前，是不分 Table Filter 與 Index Filter 的，這兩個條件都直接分配到 Server 層進行篩選。篩選過程是先根據(jù) Index Key 的條件先在引擎層進行初步篩選，然后得到對應(yīng)的主鍵值進行回表查詢得到初篩的行記錄，傳入 Server 層進行后續(xù)的篩選，在 Server 層的篩選因為沒有用到索引所以會進行全表掃描。而索引下推的優(yōu)化就是將 Index Filter 的條件下推到引擎層，在使用? Index First Key 與 Index Last Key 進行篩選時，就帶上 Index Filter 的條件再次篩選，以此來過濾掉不符合條件的記錄對應(yīng)的主鍵值，減少回表的次數(shù)，同時發(fā)給 Server 層的記錄也會更少，全表掃描篩選的效率也會變高。下面是未使用索引下推和使用索引下推的示意圖。
?從上面的分析來看，查詢的流程圖大致可以用下面這張圖來概括
?
?這里要注意的是如果在一開始沒有用到索引，會依次將磁盤上的數(shù)據(jù)頁讀取到緩沖池中進行查詢。
source:?https://www.cnblogs.com/mengxinJ/p/14045520.html推薦閱讀：
完全整理 | 365篇高質(zhì)技術(shù)文章目錄整理
專注服務(wù)器后臺技術(shù)棧知識總結(jié)分享
歡迎關(guān)注交流共同進步