關係型資料庫的一大優勢之一，用戶無需關心數據的訪問方式，因為這些優化器都幫我們處理好了，但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.2%（1/不同user_name數量=user_name中有500個不同值的比率），則意味著查詢結果會包含20行的記錄。

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有500個不同的值，sex有2個不同的值，age有40個不同的值。

則每個謂詞的過濾因子:

FF(user_name) =1/500*100 =0.2%

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

FF(age) =1/40*100=2.5%

組合過濾因子=0.2%*50%*2.5%=0.0025%

通過以上組合過濾因子，可以推算出最終的結果集=10000*0.0025%=0.25 ~=1

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

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

六、排序

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

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

• 沒有排序需求，比如order by,group by 等。
• 雖然需要排序滿足以下兩個條件：
• <!--存在一個索引滿足結果集的排序需求，比如上述的(id_name_sex) 或者（name_sex_age）-->
• <!--優化器決定以傳統的方式使用這個索引，即訪問第一條滿足條件的索引行並讀取相應的錶行，然後訪問第二條滿足條件的索引行並讀取相應的錶行，依次類推。-->
• <!--比如使用索引(name_sex_age)時候，select * from test where user_name=』test』 order by sex ，此時在索引中，結果集基於sex本身就是有序的-->

七、最後

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

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！