問題描述

一條SQL，在數(shù)據(jù)庫中是如何執(zhí)行的呢？相信很多人都會(huì)對(duì)這個(gè)問題比較感興趣。當(dāng)然，要完整描述一條SQL在數(shù)據(jù)庫中的生命周期，這是一個(gè)非常巨大的問題，涵蓋了SQL的詞法解析、語法解析、權(quán)限檢查、查詢優(yōu)化、SQL執(zhí)行等一系列的步驟，簡短的篇幅是絕對(duì)無能為力的。因此，本文挑選了其中的部分內(nèi)容，也是我一直都想寫的一個(gè)內(nèi)容，做重點(diǎn)介紹：

給定一條SQL，如何提取其中的where條件？where條件中的每個(gè)子條件，在SQL執(zhí)行的過程中有分別起著什么樣的作用？

通過本文的介紹，希望讀者能夠更好地理解查詢條件對(duì)于SQL語句的影響；撰寫出更為優(yōu)質(zhì)的SQL語句；更好地理解一些術(shù)語，例如：MySQL 5.6中一個(gè)重要的優(yōu)化——Index Condition Pushdown，究竟push down了什么？

本文接下來的內(nèi)容，安排如下：

• 簡單介紹關(guān)系型數(shù)據(jù)庫中數(shù)據(jù)的組織形式
• 給定一條SQL，如何提取其中的where條件
• 最后做一個(gè)小的總結(jié)

關(guān)系型數(shù)據(jù)庫中的數(shù)據(jù)組織

關(guān)系型數(shù)據(jù)庫中，數(shù)據(jù)組織涉及到兩個(gè)最基本的結(jié)構(gòu)：表與索引。表中存儲(chǔ)的是完整記錄，一般有兩種組織形式：堆表(所有的記錄無序存儲(chǔ))，或者是聚簇索引表(所有的記錄，按照記錄主鍵進(jìn)行排序存儲(chǔ))。索引中存儲(chǔ)的是完整記錄的一個(gè)子集，用于加速記錄的查詢速度，索引的組織形式，一般均為B+樹結(jié)構(gòu)。

有了這些基本知識(shí)之后，接下來讓我們創(chuàng)建一張測(cè)試表，為表新增幾個(gè)索引，然后插入幾條記錄，最后看看表的完整數(shù)據(jù)組織、存儲(chǔ)結(jié)構(gòu)是怎么樣的。(注意：下面的實(shí)例，使用的表的結(jié)構(gòu)為堆表形式，這也是Oracle/DB2/PostgreSQL等數(shù)據(jù)庫采用的表組織形式，而不是InnoDB引擎所采用的聚簇索引表。其實(shí)，表結(jié)構(gòu)采用何種形式并不重要，最重要的是理解下面章節(jié)的核心，在任何表結(jié)構(gòu)中均適用)

create table t1 (a int primary key, b int, c int, d int, e varchar(20));
create index idx_t1_bcd on t1(b, c, d);

insert into t1 values (4,3,1,1,'d');
insert into t1 values (1,1,1,1,'a');
insert into t1 values (8,8,8,8,'h'):
insert into t1 values (2,2,2,2,'b');
insert into t1 values (5,2,3,5,'e');
insert into t1 values (3,3,2,2,'c');
insert into t1 values (7,4,5,5,'g');
insert into t1 values (6,6,4,4,'f');

idx_t1_bcd索引上有[b,c,d]三個(gè)字段(注意：若是InnoDB類的聚簇索引表，idx_t1_bcd上還會(huì)包括主鍵a字段)，不包括[a,e]字段。idx_t1_bcd索引，首先按照b字段排序，b字段相同，則按照c字段排序，以此類推。記錄在索引中按照[b,c,d]排序，但是在堆表上是亂序的，不按照任何字段排序。

SQL的where條件提取

在有了以上的t1表之后，接下來就可以在此表上進(jìn)行SQL查詢了，獲取自己想要的數(shù)據(jù)。例如，考慮以下的一條SQL：

select * from t1 where b >= 2 and b < 8 and c > 1 and d != 4 and e != 'a';

一條比較簡單的SQL，一目了然就可以發(fā)現(xiàn)where條件使用到了[b,c,d,e]四個(gè)字段，而t1表的idx_t1_bcd索引，恰好使用了[b,c,d]這三個(gè)字段，那么走idx_t1_bcd索引進(jìn)行條件過濾，應(yīng)該是一個(gè)不錯(cuò)的選擇。接下來，讓我們拋棄數(shù)據(jù)庫的思想，直接思考這條SQL的幾個(gè)關(guān)鍵性問題：

此SQL，覆蓋索引idx_t1_bcd上的哪個(gè)范圍？

起始范圍：記錄[2,2,2]是第一個(gè)需要檢查的索引項(xiàng)。索引起始查找范圍由b >= 2，c > 1決定。

終止范圍：記錄[8,8,8]是第一個(gè)不需要檢查的記錄，而之前的記錄均需要判斷。索引的終止查找范圍由b < 8決定；

在確定了查詢的起始、終止范圍之后，SQL中還有哪些條件可以使用索引idx_t1_bcd過濾？

根據(jù)SQL，固定了索引的查詢范圍[(2,2,2),(8,8,8))之后，此索引范圍中并不是每條記錄都是滿足where查詢條件的。例如：(3,1,1)不滿足c > 1的約束；(6,4,4)不滿足d != 4的約束。而c，d列，均可在索引idx_t1_bcd中過濾掉不滿足條件的索引記錄的。

因此，SQL中還可以使用c > 1 and d != 4條件進(jìn)行索引記錄的過濾。

在確定了索引中最終能夠過濾掉的條件之后，還有哪些條件是索引無法過濾的？

此問題的答案顯而易見，e != ‘a’這個(gè)查詢條件，無法在索引idx_t1_bcd上進(jìn)行過濾，因?yàn)樗饕⑽窗琫列。e列只在堆表上存在，為了過濾此查詢條件，必須將已經(jīng)滿足索引查詢條件的記錄回表，取出表中的e列，然后使用e列的查詢條件e != ‘a’進(jìn)行最終的過濾。

在理解以上的問題解答的基礎(chǔ)上，做一個(gè)抽象，可總結(jié)出一套放置于所有SQL語句而皆準(zhǔn)的where查詢條件的提取規(guī)則：

所有where條件均可歸納為3大類

• Index Key (First Key & Last Key)
• Index Filter
• Table Filter

接下來，讓我們來詳細(xì)分析這3大類分別是如何定義，以及如何提取的。

1.Index Key

用于確定SQL查詢?cè)谒饕械倪B續(xù)范圍(起始范圍+結(jié)束范圍)的查詢條件，被稱之為Index Key。由于一個(gè)范圍，至少包含一個(gè)起始與一個(gè)終止，因此Index Key也被拆分為Index First Key和Index Last Key，分別用于定位索引查找的起始，以及索引查詢的終止條件。

Index First Key

用于確定索引查詢的起始范圍。提取規(guī)則：從索引的第一個(gè)鍵值開始，檢查其在where條件中是否存在，若存在并且條件是=、>=，則將對(duì)應(yīng)的條件加入Index First Key之中，繼續(xù)讀取索引的下一個(gè)鍵值，使用同樣的提取規(guī)則；若存在并且條件是>，則將對(duì)應(yīng)的條件加入Index First Key中，同時(shí)終止Index First Key的提?。蝗舨淮嬖?，同樣終止Index First Key的提取。
針對(duì)上面的SQL，應(yīng)用這個(gè)提取規(guī)則，提取出來的Index First Key為(b >= 2, c > 1)。由于c的條件為 >，提取結(jié)束，不包括d。

Index Last Key

Index Last Key的功能與Index First Key正好相反，用于確定索引查詢的終止范圍。提取規(guī)則：從索引的第一個(gè)鍵值開始，檢查其在where條件中是否存在，若存在并且條件是=、<=，則將對(duì)應(yīng)條件加入到Index Last Key中，繼續(xù)提取索引的下一個(gè)鍵值，使用同樣的提取規(guī)則；若存在并且條件是 < ，則將條件加入到Index Last Key中，同時(shí)終止提?。蝗舨淮嬖?，同樣終止Index Last Key的提取。

針對(duì)上面的SQL，應(yīng)用這個(gè)提取規(guī)則，提取出來的Index Last Key為(b < 8)，由于是 < 符號(hào)，因此提取b之后結(jié)束。

2.Index Filter

在完成Index Key的提取之后，我們根據(jù)where條件固定了索引的查詢范圍，但是此范圍中的項(xiàng)，并不都是滿足查詢條件的項(xiàng)。在上面的SQL用例中，(3,1,1)，(6,4,4)均屬于范圍中，但是又均不滿足SQL的查詢條件。

Index Filter的提取規(guī)則：同樣從索引列的第一列開始，檢查其在where條件中是否存在：若存在并且where條件僅為 =，則跳過第一列繼續(xù)檢查索引下一列，下一索引列采取與索引第一列同樣的提取規(guī)則；若where條件為 >=、>、<、<= 其中的幾種，則跳過索引第一列，將其余where條件中索引相關(guān)列全部加入到Index Filter之中；若索引第一列的where條件包含 =、>=、>、<、<= 之外的條件，則將此條件以及其余where條件中索引相關(guān)列全部加入到Index Filter之中；若第一列不包含查詢條件，則將所有索引相關(guān)條件均加入到Index Filter之中。

針對(duì)上面的用例SQL，索引第一列只包含 >=、< 兩個(gè)條件，因此第一列可跳過，將余下的c、d兩列加入到Index Filter中。因此獲得的Index Filter為 c > 1 and d != 4 。

3.Table Filter

Table Filter是最簡單，最易懂，也是提取最為方便的。提取規(guī)則：所有不屬于索引列的查詢條件，均歸為Table Filter之中。
同樣，針對(duì)上面的用例SQL，Table Filter就為 e != ‘a’。

Index Key/Index Filter/Table Filter小結(jié)

SQL語句中的where條件，使用以上的提取規(guī)則，最終都會(huì)被提取到Index Key (First Key & Last Key)，Index Filter與Table Filter之中。

• Index First Key，只是用來定位索引的起始范圍，因此只在索引第一次Search Path(沿著索引B+樹的根節(jié)點(diǎn)一直遍歷，到索引正確的葉節(jié)點(diǎn)位置)時(shí)使用，一次判斷即可；
• Index Last Key，用來定位索引的終止范圍，因此對(duì)于起始范圍之后讀到的每一條索引記錄，均需要判斷是否已經(jīng)超過了Index Last Key的范圍，若超過，則當(dāng)前查詢結(jié)束；
• Index Filter，用于過濾索引查詢范圍中不滿足查詢條件的記錄，因此對(duì)于索引范圍中的每一條記錄，均需要與Index Filter進(jìn)行對(duì)比，若不滿足Index Filter則直接丟棄，繼續(xù)讀取索引下一條記錄；
• Table Filter，則是最后一道where條件的防線，用于過濾通過前面索引的層層考驗(yàn)的記錄，此時(shí)的記錄已經(jīng)滿足了Index First Key與Index Last Key構(gòu)成的范圍，并且滿足Index Filter的條件，回表讀取了完整的記錄，判斷完整記錄是否滿足Table Filter中的查詢條件，同樣的，若不滿足，跳過當(dāng)前記錄，繼續(xù)讀取索引的下一條記錄，若滿足，則返回記錄，此記錄滿足了where的所有條件，可以返回給前端用戶。

結(jié)語

在讀完、理解了以上內(nèi)容之后，詳細(xì)大家對(duì)于數(shù)據(jù)庫如何提取where中的查詢條件，如何將where中的查詢條件提取為Index Key，Index Filter，Table Filter有了深刻的認(rèn)識(shí)。以后在撰寫SQL語句時(shí)，可以對(duì)照表的定義，嘗試自己提取對(duì)應(yīng)的where條件，與最終的SQL執(zhí)行計(jì)劃對(duì)比，逐步強(qiáng)化自己的理解。

同時(shí)，我們也可以回答文章開始提出的一個(gè)問題：MySQL 5.6中引入的Index Condition Pushdown，究竟是將什么Push Down到索引層面進(jìn)行過濾呢？對(duì)了，答案是Index Filter。在MySQL 5.6之前，并不區(qū)分Index Filter與Table Filter，統(tǒng)統(tǒng)將Index First Key與Index Last Key范圍內(nèi)的索引記錄，回表讀取完整記錄，然后返回給MySQL Server層進(jìn)行過濾。而在MySQL 5.6之后，Index Filter與Table Filter分離，Index Filter下降到InnoDB的索引層面進(jìn)行過濾，減少了回表與返回MySQL Server層的記錄交互開銷，提高了SQL的執(zhí)行效率。

到此這篇關(guān)于MySQL WHERE語句用法小結(jié)的文章就介紹到這了,更多相關(guān)MySQL WHERE 內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論