一條 sql 的執(zhí)行過程詳解

點(diǎn)擊上方「藍(lán)字」關(guān)注我們

寫操作執(zhí)行過程

　　如果這條sql是寫操作(insert、update、delete)，那么大致的過程如下，其中引擎層是屬于 InnoDB 存儲引擎的，因為InnoDB 是默認(rèn)的存儲引擎，也是主流的，所以這里只說明 InnoDB 的引擎層過程。由于寫操作較查詢操作更為復(fù)雜，所以先看一下寫操作的執(zhí)行圖。方便后面解析。

0x01: 組件介紹?

Server層

1、連接器

　　1）負(fù)責(zé)與客戶端的通信，是半雙工模式，這就意味著某一固定時刻只能由客戶端向服務(wù)器請求或者服務(wù)器向客戶端發(fā)送數(shù)據(jù)，而不能同時進(jìn)行。

　　2）驗證用戶名和密碼是否正確（數(shù)據(jù)庫mysql的user表中進(jìn)行驗證），如果錯誤返回錯誤通知（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ù)是以鍵值對的形式進(jìn)行存儲，如果開啟了緩存，那么在一條查詢sql語句進(jìn)來時會先判斷緩存中是否包含當(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ù)遠(yuǎn)大于修改次數(shù)的話，使用緩存不僅不能提升查詢效率還會拉低效率（每次讀取后需要向緩存中保存一份，而緩存又容易被清除）。所以在 MYSQL5.6默認(rèn)是關(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 進(jìn)行分析，這將包括預(yù)處理與解析過程，并進(jìn)行關(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)化器

進(jìn)入優(yōu)化器說明sql語句是符合標(biāo)準(zhǔn)語義規(guī)則并且可以執(zhí)行。優(yōu)化器會根據(jù)執(zhí)行計劃選擇最優(yōu)的選擇，匹配合適的索引，選擇最佳的方案。比如一個典型的例子是這樣的：

?

表T,對A、B、C列建立聯(lián)合索引(A,B,C)，在進(jìn)行查詢的時候，當(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í)行計劃進(jìn)行預(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ù)原理和鎖機(jī)制。

?

2、redo log?與 Buffer Pool

InnoDB 內(nèi)部維護(hù)了一個緩沖池，用于減少對磁盤數(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ù)進(jìn)行落盤，等待 checkponit向后面移動一段距離再寫。redo log存儲的內(nèi)容個人認(rèn)為當(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ù)庫誤刪需要還原，那么需要某個時間點(diǎn)的數(shù)據(jù)備份以及bin log）。5.7默認(rèn)記錄的是修改后的行記錄。

在更新到數(shù)據(jù)頁緩存或者 Change Buffer 后，首先進(jìn)行 redo log 的編寫，此時 redo log 處于 prepare 狀態(tài)，隨后再進(jìn)行 bin log 的編寫，等到 bin log 也編寫完成后再將 redo log 設(shè)置為 commit 狀態(tài)。這是為了防止數(shù)據(jù)庫宕機(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 最近的記錄是否對的上，如果對的上就提交，對不上就進(jìn)行事務(wù)回滾。

三種格式：

1、Row（5.7默認(rèn)）。記錄被修改后的行記錄。缺點(diǎn)是占空間大。優(yōu)點(diǎn)是能保證數(shù)據(jù)安全，不會發(fā)生遺漏。

2、Statement。記錄修改的 sql。缺點(diǎn)是在 mysql 集群時可能會導(dǎo)致操作不一致從而使得數(shù)據(jù)不一致（比如在操作中加入了Now()函數(shù)，主從數(shù)據(jù)庫操作的時間不同結(jié)果也不同）。優(yōu)點(diǎn)是占空間小，執(zhí)行快。

3、Mixed。會針對于操作的 sql 選擇使用Row 還是 Statement。缺點(diǎn)是還是可能發(fā)生主從不一致的情況。

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

1、undo log是用于事務(wù)的回滾、保證事務(wù)隔離級別讀已提交、可重復(fù)讀實現(xiàn)的。redo log是用于對暫不更新到磁盤上的操作進(jìn)行記錄，使得其可以延遲落盤，保證程序的效率。bin log是對數(shù)據(jù)操作進(jìn)行備份恢復(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默認(rèn)）或者sql語句。

?

0x02: 執(zhí)行過程

寫操作

?通過上面的分析，可以很容易地了解開始的更新執(zhí)行圖。這里就不過多闡述了。

讀操作

查詢的過程和更新比較相似，但是有些不同，主要是來源于他們在查找篩選時的不同，更新因為在查找后會進(jìn)行更新操作，所以查詢這一行為至始至終都在緩沖池中（使用到索引且緩沖池中包含數(shù)據(jù)對應(yīng)的數(shù)據(jù)頁）。而查詢則更復(fù)雜一些。

Where?條件的提取

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

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ù)范圍（起始點(diǎn) + 終止點(diǎn)）的查詢條件，被稱之為Index Key；由于一個范圍，至少包含一個起始條件與一個終止條件，因此 Index Key 也被拆分為 Index First Key 和 Index Last Key，分別用于定位索引查找的起始點(diǎn)以終止點(diǎn)

　　　　Index First Key

用于確定索引查詢范圍的起始點(diǎn)；提取規(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

用于確定索引查詢范圍的終止點(diǎn)，與 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 中不能被索引過濾的條件都?xì)w為此中；提取規(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ìn)行篩選。篩選過程是先根據(jù) Index Key 的條件先在引擎層進(jìn)行初步篩選，然后得到對應(yīng)的主鍵值進(jìn)行回表查詢得到初篩的行記錄，傳入 Server 層進(jìn)行后續(xù)的篩選，在 Server 層的篩選因為沒有用到索引所以會進(jìn)行全表掃描。而索引下推的優(yōu)化就是將 Index Filter 的條件下推到引擎層，在使用? Index First Key 與 Index Last Key 進(jìn)行篩選時，就帶上 Index Filter 的條件再次篩選，以此來過濾掉不符合條件的記錄對應(yīng)的主鍵值，減少回表的次數(shù)，同時發(fā)給 Server 層的記錄也會更少，全表掃描篩選的效率也會變高。下面是未使用索引下推和使用索引下推的示意圖。

?從上面的分析來看，查詢的流程圖大致可以用下面這張圖來概括

?

?這里要注意的是如果在一開始沒有用到索引，會依次將磁盤上的數(shù)據(jù)頁讀取到緩沖池中進(jìn)行查詢。

source:?https://www.cnblogs.com/mengxinJ/p/14045520.html

掃碼二維碼

獲取更多精彩

Java樂園