10 分鐘讓你明白 MySQL 是如何利用索引的

雲栖号： https://yqh.aliyun.com 第一手的上雲資訊，不同行業精選的上雲企業案例庫，基于衆多成功案例萃取而成的最佳實踐，助力您上雲決策！

一、前言

在MySQL中進行SQL優化的時候，經常會在一些情況下，對 MySQL 能否利用索引有一些迷惑。

譬如:

MySQL 在遇到範圍查詢條件的時候就停止比對了，那麼到底是哪些範圍條件？

MySQL 在LIKE進行模糊比對的時候又是如何利用索引的呢？

MySQL 到底在怎麼樣的情況下能夠利用索引進行排序？

今天，我将會用一個模型，把這些問題都一一解答，讓你對MySQL索引的使用不再畏懼

二、知識補充

key_len

EXPLAIN執行計劃中有一列 key_len 用于表示本次查詢中，所選擇的索引長度有多少位元組，通常我們可借此判斷聯合索引有多少列被選擇了。

在這裡 key_len 大小的計算規則是：

一般地，key_len 等于索引列類型位元組長度，例如int類型為4 bytes，bigint為8 bytes；

如果是字元串類型，還需要同時考慮字元集因素，例如：CHAR(30) UTF8則key_len至少是90 bytes；

若該列類型定義時允許NULL，其key_len還需要再加 1 bytes；

若該列類型為變長類型，例如 VARCHAR（TEXTBLOB不允許整列建立索引，如果建立部分索引也被視為動态列類型），其key_len還需要再加 2 bytes;

三、哪些條件能用到索引

首先非常感謝登博，給了我一個很好的啟發，我通過他的文章，然後結合自己的了解，制作出了這幅圖

乍一看，是不是很暈，不急，我們慢慢來看

圖中一共分了三個部分：

Index Key ：MySQL是用來确定掃描的資料範圍，實際就是可以利用到的MySQL索引部分，展現在Key Length。

Index Filter：MySQL用來确定哪些資料是可以用索引去過濾，在啟用ICP後，可以用上索引的部分。

Table Filter：MySQL無法用索引過濾，回表取回行資料後，到server層進行資料過濾。

我們細細展開。

Index Key

Index Key是用來确定MySQL的一個掃描範圍，分為上邊界和下邊界。

MySQL利用=、>=、> 來确定下邊界（first key），利用最左原則，首先判斷第一個索引鍵值在where條件中是否存在，如果存在，則判斷比較符号，如果為(=,>=)中的一種，加入下邊界的界定，然後繼續判斷下一個索引鍵，如果存在且是(>)，則将該鍵值加入到下邊界的界定，停止比對下一個索引鍵；如果不存在，直接停止下邊界比對。

exp: idx_c1_c2_c3(c1,c2,c3) where c1>=1 and c2>2 and c3=1 --> first key (c1,c2) --> c1為 '>=' ，加入下邊界界定，繼續比對下一個 -->c2 為 '>'，加入下邊界界定，停止比對

上邊界（last key）和下邊界（first key）類似，首先判斷是否是否是(=,<=)中的一種，如果是，加入界定，繼續下一個索引鍵值比對，如果是(<)，加入界定，停止比對

exp: idx_c1_c2_c3(c1,c2,c3) where c1<=1 and c2=2 and c3<3 --="">first key (c1,c2,c3) --> c1為 '<='，加入上邊界界定，繼續比對下一個 --=""> c2為 '='加入上邊界界定，繼續比對下一個 --> c3 為 '<'，加入上邊界界定，停止比對

注：這裡簡單的記憶是，如果比較符号中包含’=’号，’>=’也是包含’=’，那麼該索引鍵是可以被利用的，可以繼續比對後面的索引鍵值；如果不存在’=’，也就是’>’,’<’，這兩個，後面的索引鍵值就無法比對了。同時，上下邊界是不可以混用的，哪個邊界能利用索引的的鍵值多，就是最終能夠利用索引鍵值的個數。

Index Filter

字面了解就是可以用索引去過濾。也就是字段在索引鍵值中，但是無法用去确定Index Key的部分。

exp: idex_c1_c2_c3 where c1>=1 and c2<=2 and="" c3="1" index="" key="" --=""> c1 index filter--> c2 c3

這裡為什麼index key 隻是c1呢？因為c2 是用來确定上邊界的，但是上邊界的c1沒有出現(<=,=)，而下邊界中，c1是>=,c2沒有出現，是以index key 隻有c1字段。c2,c3 都出現在索引中，被當做index filter.

Table Filter

無法利用索引完成過濾，就隻能用table filter。此時引擎層會将行資料傳回到server層，然後server層進行table filter。

四、Between 和 Like 的處理

那麼如果查詢中存在between 和like，MySQL是如何進行處理的呢？

Between

where c1 between 'a' and 'b'等價于 where c1>='a' and c1 <='b'，是以進行相應的替換，然後帶入上層模型，确定上下邊界即可。

Like

首先需要确認的是%不能是最在最左側，where c1 like '%a' 這樣的查詢是無法利用索引的，因為索引的比對需要符合最左字首原則

where c1 like 'a%' 其實等價于 where c1>='a' and c1<'b' 大家可以仔細思考下。

五、索引的排序

在資料庫中，如果無法利用索引完成排序，随着過濾資料的資料量的上升，排序的成本會越來越大，即使是采用了limit，但是資料庫是會選擇将結果集進行全部排序，再取排序後的limit 記錄，而且 MySQL 針對可以用索引完成排序的limit 有優化，更能減少成本。

原文釋出時間：2020-01-17

本文作者：SQL資料庫開發

本文來自阿裡雲雲栖号合作夥伴“

SQL資料庫開發

”，了解相關資訊可以關注“

”