跳轉到

透過執行計劃判斷是否索引失效

對於執行計劃，參數有：

  • possible_keys 字段表示可能用到的索引
  • key 字段表示實際用的索引，如果這一項為 NULL，說明沒有使用索引
  • key_len 表示索引的長度
  • rows 表示掃描的數據行數
  • type 表示數據掃描類型
  • etc.

select_type

SIMPLE

簡單 SELECT 查詢，不含子查詢和 UNION

PRIMARY

複雜查詢中最外層的查詢，表示主要的查詢

SUBQUERY

SELECT 或 WHERE 中包含了子查詢，且子查詢不依賴於外層查詢

select * from t1 where key1 in (select key1 from t2);

DEPENDENT SUBQUERY

SELECT 或 WHERE 中包含了子查詢，且子查詢依賴於外層查詢

select * from t1 where key1 in (select key1 from t2 where t1.key2 = t2.key2);

UNION

UNION 關鍵字之後的查詢

DEPENDENT UNION

在包含 UNION 或 UNION ALL 的大查詢中，如果各個小查詢都依賴外層查詢的話，那除了最左邊的那個小查詢之外，其餘的小查詢的 select_type 的值就是 DEPENDENT UNION

select * from t1 where key1 in
(  select key1 from t2 where key1 = 'a'
   union
   select key1 from t1 where key1 = 'b'
);

UNION RESULT

從 UNION 後的表格取得結果集

DERIVED

FROM 中包含的子查詢

MATERIALIZED

查詢會建立臨時表

type

system

這種類型要求資料庫表中只有一條數據，是 const 類型的一個特例，一般情況下是不會出現的。

const

使用了主鍵或者唯一索引與常量值進行比較，比如

select name from product where id=1

eq_ref

使用主鍵或唯一索引時產生的訪問方式，通常使用在多表聯查中。比如，對兩張表進行聯查，關聯條件是兩張表的 user_id 相等，且 user_id 是唯一索引，那麼使用 EXPLAIN 進行執行計劃查看的時候，type 就會顯示 eq_ref。

需要說明的是 const 類型和 eq_ref 都使用了主鍵或唯一索引，不過這兩個類型有所區別，const 是與常量進行比較，查詢效率會更快，而 eq_ref 通常用於多表聯查中。

ref

使用了非唯一索引，或者是唯一索引的非唯一性前綴，返回數據可能是多條。因為雖然使用了索引，但該索引列的值並不唯一，有重複。

這樣即使使用索引快速查找到了第一條數據，仍然不能停止，要進行目標值附近的小範圍掃描。

但它的好處是它並不需要掃全表，因為索引是有序的，即便有重複值，也是在一個非常小的範圍內掃描。

range

使用了索引範圍掃描，一般在 where 子句中使用 < 、>、in、between 等關鍵詞，只檢索給定範圍的行，屬於範圍查找。從這一級別開始，索引的作用會越來越明顯，因此我們需要盡量讓 SQL 查詢可以使用到 range 這一級別及以上的 type 訪問方式。

key

該列表示實際用到的索引。

key_len

該欄位表示使用索引的長度。上面的 key 欄位可以看出有沒有使用索引，key_len 欄位則可以更進一步看出索引使用是否充分。

Extra

這個欄位包含有關 MySQL 如何解析查詢的其他信息

Impossible WHERE

表示 WHERE 後面的條件一直都是 false，

執行 sql 如下：

explain select code  from test1 where 'a' = 'b';

Using filesort

表示依檔案排序，一般是在指定的排序和索引排序不一致的情況才會出現。

假設表有聯合索引 (code ,name)，執行 sql 如下：

explain select code  from test1 order by name desc;

Using index

使用了覆蓋索引，查詢時不需要回表查詢，直接透過索引就可以取得查詢的資料。

Using index condition

查詢包含索引列和非索引列，優化器會先解析索引列，並在表中尋找其他條件(索引下推)

索引下推

索引下推能夠減少二級索引在查詢時的回表操作，提高查詢的效率，因為它將 Server 層部分負責的事情，交給 Storage Engine 層去處理了。

假設有一張用戶表，對 age 和 reward 字段建立聯合索引(age，reward)

現在有下面這條查詢語句：

select * from t_user where age > 20 and reward = 100000;

當聯合索引遇到範圍查詢 (>、<) 就會停止匹配，也就是 age 字段能用到聯合索引，但是 reward 字段則無法利用到索引。原因可以看聯合索引範圍查詢。

不使用索引下推（MySQL 5.6 之前的版本）時，執行器與儲存引擎的執行流程是這樣的：

  • Server 層首先調用儲存引擎的接口定位到滿足查詢條件的第一條二級索引記錄，也就是定位到 age > 20 的第一條記錄
  • 儲存引擎根據二級索引的 B+ 樹快速定位到這條記錄後，獲取主鍵值，然後進行回表操作，將完整的記錄返回給 Server 層
  • Server 層再判斷該記錄的 reward 是否等於 100000，如果成立則將其發送給客戶端；否則跳過該記錄
  • 接著，繼續向儲存引擎索要下一條記錄，儲存引擎在二級索引定位到記錄後，獲取主鍵值，然後回表操作，將完整的記錄返回給 Server 層
  • 如此往復，直到儲存引擎把表中的所有記錄讀完

可以看到，沒有索引下推的時候，每查詢到一條二級索引記錄，都要進行回表操作，然後將記錄返回給 Server，接著 Server 再判斷該記錄的 reward 是否等於 100000。

而使用索引下推後，判斷記錄的 reward 是否等於 100000 的工作交給了儲存引擎層，過程如下：

  • Server 層首先調用儲存引擎的接口定位到滿足查詢條件的第一條二級索引記錄，也就是定位到 age > 20 的第一條記錄
  • 儲存引擎定位到二級索引後，先不執行回表操作，而是先判斷一下該索引中包含的列（reward 列）的條件（reward 是否等於 100000）是否成立。如果條件不成立，則直接跳過該二級索引。如果成立，則執行回表操作，將完成記錄返回給 Server 層
  • Server 層再判斷其他的查詢條件（本次查詢沒有其他條件）是否成立，如果成立則將其發送給客戶端；否則跳過該記錄，然後向儲存引擎索要下一條記錄
  • 如此往復，直到儲存引擎把表中的所有記錄讀完

可以看到，使用了索引下推後，雖然 reward 列無法使用到聯合索引，但是因為它包含在聯合索引（age，reward）裡，所以直接在儲存引擎過濾出滿足 reward = 100000 的記錄後，才去執行回表操作獲取整個記錄。相比於沒有使用索引下推，節省了很多回表操作。

Using temporary

使用了臨時表，一般多見於 order by 和 group by 語句。

總結

  1. 先用慢查詢日誌定位具體需要最佳化的 sql

  2. 使用 explain 執行計劃查看索引使用情況

  3. 重點關注：

key（看有沒有使用索引）

key_len（看索引使用是否充分）

type（看索引類型）

Extra（看附加資訊：排序、臨時表、where 條件為 false 等）

一般情況下根據這 4 列就能找到索引問題。

  1. 根據上一步驟找出的索引問題優化 sql

Reference