SQL最佳化器究竟幫你做了哪些工作？-知識星球

來自：大碼侯（微訊號：cool_wier）

這篇聊聊SQL最佳化器的工作。

關係型資料庫的一大優勢之一，使用者無需關心資料的訪問方式，因為這些最佳化器都幫我們處理好了，但sql查詢最佳化的時候，我不得不要對此進行關註，因為這牽扯到查詢效能問題。

有經驗的程式員都會對一些sql最佳化瞭如指掌，比如我們常說的最左匹配原則，非BT謂詞規避等等，那麼最佳化器是如何確定這些的？以及為何一定要最左匹配，最左匹配的原理是什麼，你是否有深入瞭解？

這一篇我們就透過一些實體來剖析最佳化器做了哪些工作，以方便我們更好的最佳化SQL查詢。

本篇你可以知道:

sql的訪問路徑是什麼

最佳化器如何確定最優訪問路徑

最左匹配的原則依據是什麼

如何有效的評估sql命中行數

示例table:

CREATE TABLE test (

  id int(11) NOT NULL AUTO_INCREMENT,

  user_name varchar(100) DEFAULT NULL,

  sex int(11) DEFAULT NULL,

  age int(11) DEFAULT NULL,

  c_date datetime DEFAULT NULL,

  PRIMARY KEY (id),

  # 索引

  KEY id_name_sex (id,user_name,sex),

  KEY name_sex_age (user_name,sex,age)

) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

一、訪問路徑

在SQL陳述句能夠被真正執行之前，最佳化器必須首先確定如何訪問資料。這包括：應該使用哪一個索引，索引的訪問方式如何，是否需要輔助式隨機讀，等等。

從一條SQL，到最佳化器最佳化，再到引擎進行資料查詢，落地到資料的儲存頁面，這是一個訪問路徑確定的過程。

二、謂詞

謂詞就是我們常說的where子句中的一個或多個搜尋引陣列成。謂詞運算式是索引設計的主要入手點，如果一個索引能夠滿足select查詢陳述句的所有謂詞運算式，那麼最佳化器就可能建立一個高效的訪問路徑。


select * from test where id =1 and user_name like ’test%’

比如，上述查詢中，where後面的搜尋引數，id 和user_name 就是謂詞。

三、索引片

索引片即代表謂詞運算式所確定的值域範圍，而訪問路徑的成本很大程度上取決於索引片的厚度。

索引片越厚，需要掃描的索引頁就越多，需要處理的索引記錄也越多，而且最大的開銷還是來自於需要對標進行同步讀操作。相反，索引片比較窄，就會顯著減少索引訪問的那部分開銷，同時會有更少的表同步讀取上。

同步讀是一個隨機IO操作，單次的讀取就要耗費10ms左右的時間。這個我們在上篇有說明。

比如：


//會匹配到5個資料

sql1:select * from test where sex=1;

// 匹配到2個資料

sql2：select * from test where sex=1 and age <10;

因此我們需要透過謂詞來確定索引片的厚度，過濾的值域範圍越少，索引片厚度就越窄。那麼謂詞一定就能匹配到索引麼，或者說匹配的規則是什麼？

四、匹配列&過濾列

謂詞不一定都能匹配到索引，能夠匹配上的，我們稱之為匹配列。此時它可以參與索引片的定義。

只有匹配列和過濾列可以參與索引片的定義和過濾，其他不可。

我們來看下謂詞匹配的定義：

檢查索引列，從頭到尾依次檢查索引列，檢視以下規則:

在where子句中，該列是否至少擁有一個足夠簡單的謂詞與之對應？如果有，則這個列就是匹配列。如果沒有，那麼這個列及其後面的索引列都是非匹配列。

謂詞是否是一個範圍謂詞，如果是，那麼剩餘的索引列都是非匹配列。

對於最後一個匹配列之後的索引列，如果擁有一個足夠簡單的謂詞與其對應，那麼該列為過濾列。

1、示例


select * from test where user_name=’test1’ and sex>0 and age =10

發現索引id_name_sex

逐行檢查其索引列(id,user_name,sex)

首先檢查 id,發現where後面的謂詞沒有與之對應，則這個索引列以及後面的索引列都是非匹配列

索引id_name_sex匹配結束,無匹配列

發現索引name_sex_age

逐行檢查其索引列(user_name,sex,age)

首先檢查 user_name,發現where後面的謂詞 user_name 有與之對應，認定此列為匹配列

檢查索引欄位sex,發現where後面有謂詞sex與之對應，認定此列為匹配列，由於謂詞sex是範圍謂詞，則剩餘的索引為非匹配列。

索引列age 是在最後一個匹配列sex 之後，而又有謂詞age 與之對應，因此此列為過濾列,

透過這個示例，我們最終確定了：

匹配索引: name_sex_age

匹配列: user_name,sex

過濾列: age

我們檢視下 explain ,和我們分析的對應。

2、確定匹配列有什麼用

確定匹配列之後我們可以知道當前的查詢會用到哪些索引，以及匹配到該索引的哪些列，最終可以提前鎖定資料的訪問範圍，為資料的讀取節省讀取壓力。

相對於沒用匹配到索引的查詢，有匹配列的查詢，條件過濾是前置的，而沒有匹配到索引的查詢，條件過濾是後置的，即全表掃描之後，再過濾結果，如此磁碟IO壓力過大。

另外 “最左匹配”原則也是基於匹配列規則而來，為何是最左匹配，除了B樹的原理之外，還有一個重要的原因，在核對匹配列的時候，是從頭到尾依次檢查索引列。

所以對於是否能夠匹配到索引,where後面的謂詞順序不重要，重要的是索引列的順序。

比如：


select * from test where user_name=’test1’ and sex>0 and age =10

select * from test where sex>0 and user_name=’test1’ and age =10

select * from test where age =10 and user_name='test1' and sex>0

都可以匹配到name_sex_age 索引

3、複雜謂詞

like 謂詞

如果值是%xx ，那麼將會選擇全索引掃描，不參與索引匹配，如果是xx%，這會參與索引匹配，選擇索引片掃描。

OR運運算元

即便是簡單的謂詞，如果它們與其他謂詞之間為OR操作，對最佳化器而言是異常困難的，除非在多索引訪問，才有可能參與到一個索引片的定義，儘量不要用。

假設一個謂詞的判定結果為false,而此時不檢查其他謂詞就不能確定的將一行記錄排除在外，那麼這類謂詞對最佳化器而言就是十分困難的。

BT謂詞

比如只有and 運運算元，那麼所有的簡單謂詞都可以稱謂BT謂詞，也就是好的謂詞，除非訪問路徑是一個多索引掃描，否則只有BT謂詞可以參加定義索引片。

謂詞值不確定

比如謂詞的值採用了函式，或者參與了計算，最佳化器在做靜態SQL系結的時候，每次都需要重新計算選擇，無法快取，耗費大量的CPU，也無法參與索引列的匹配。

五、過濾因子

匹配列確定了使用那些索引列，但索引片的厚度（也就是預計要訪問多少行），還沒有估算出來。此處需要進行透過過濾因子來確定。

過濾因子描述的謂詞的選擇性，即表中滿足謂詞條件的記錄行數所佔用的比例，依賴於列值分佈情況。

1、單個謂詞的過濾因子

比如，我們的的test表有10000條記錄，謂詞user_name 匹配了一個索引列，其過濾因子是0.02%（1/不同user_name數量=user_name中有5000個不同的值），則意味著查詢結果會包含2行的記錄。


select * from test where user_name=’test’

2、組合謂詞的過濾因子

當有多個謂詞符合匹配列的時候，我們可以透過單個謂詞的過濾因子推匯出組合過濾因子。一般的公式是:

組合過濾因子=謂詞1過濾因子*謂詞2過濾因子….

比如如下查詢


select * from test where user_name=’test’ and sex=1 and age =10

包含3個謂詞，user_name、sex、age、其中user_name有5000個不同的值，sex有2個不同的值，age有400個不同的值。

則每個謂詞的過濾因子:

FF(user_name) =1/5000*100 =0.02%

FF(sex) =1/2*100=50%

FF(age) =1/4000*100=0.025%

組合過濾因子=0.02%50%0.025%=0.025%

透過以上組合過濾因子，可以推算出最終的結果集=10000*0.025%=2.5 ~=3

透過以上過濾因子評估之後，我們可以看到，最終需要查詢的結果集只需要獲取3行就夠了，這對資料庫的磁碟訪問有很高的效能提升。

這也是最佳化器在評估可選訪問路徑成時，必須先進行過濾因子評估的重要性。

六、排序

物化結果集意味著透過執行必要的資料庫訪問來構建結果集。最好情況下，只需要傳回一條記錄，而最壞的情況下需要傳回多條記錄，需要發起大量的磁碟讀取。而排序就是其中一種。

在以下情況中，一次fetch呼叫只需要物化一條記錄，否則對結果進行排序的時候就需要物化整個結果集。

沒有排序需求，比如order by,group by 等。

雖然需要排序滿足以下兩個條件：

最後

sql最佳化器做的不僅僅是你這些工作，但索引片的大小的預估，以及訪問路徑的確定卻是它最重要的工作，後續我們再繼續介紹。

●本文編號267，以後想閱讀這篇文章直接輸入267即可

●輸入m獲取文章目錄

推薦↓↓↓

Linux學習

更多推薦《18個技術類微信公眾號》

涵蓋：程式人生、演演算法與資料結構、駭客技術與網路安全、大資料技術、前端開發、Java、Python、Web開發、安卓開發、iOS開發、C/C++、.NET、Linux、資料庫、運維等。

SQL最佳化器究竟幫你做了哪些工作？