本文是關於在學習《高性能 Mysql》附錄 D 中關於 Explain 如何獲取執行計劃信息相關總結。MySQL 提供了一個 EXPLAIN 命令,它可以對 SELECT 語句進行分析，獲取優化器對當前查詢的執行計劃，以供開發人員針對相關 SQL 進行優化。在 SELECT 語句前加上 Explain 就可以查看到相關信息, 例如:

EXPLAIN SELECT * from user_info WHERE id < 300;

Explain 特點

  • explain 返回的結果是以表為粒度的，每個表輸出一行，這裡的表示廣義上的表，可以是一個子查詢，也可以是一個 UNION 後的結果。
  • 並不是所有的 explain 都不執行查詢，如果 FROM 子句裏包含了子查詢，那麼 MySql 實際上會執行子查詢以外層對於外層查詢的優化。
  • explain 無法告訴我們觸發器，存儲過程以及 UDF 是如何影響查詢的
  • explain 對於內存排序和臨時文件排序都使用 「filesort」
  • explain 對於磁碟上的臨時表和內存上的臨時表都使用 「Using temporary」
  • explain 只能解析 Select 查詢，對於 update，insert 等都不支持，我們可以使用 select 來模擬 update 操作近似獲取 update 的執行過程

Explain 中的列

id

SELECT 查詢的標識符. 每個 SELECT 都會自動分配一個唯一的標識符.

select_type

select_type 表示了查詢的類型, 它的常用取值有:

  • SIMPLE：表示此查詢不包含 UNION 查詢或子查詢
  • SUBQUERY：包含在 Select 列表中的子查詢，也就是不在 FROM 子句中的子查詢
  • DERIVED：表示包含在 From 子句中的 Select 查詢
  • UNION：表示此查詢是 UNION 的第二和隨後的查詢
  • UNION RESULT： 從 UNION 匿名臨時表檢索結果的 SELECT
  • PRIMARY, 表示此查詢是最外層的查詢
  • DEPENDENT UNION： UNION 中的第二個或後面的查詢語句, DEPENDENT 意味著 Select 依賴於外層查詢中發現的數據
  • DEPENDENT SUBQUERY: 包含在 Select 列表中的子查詢， 但子查詢依賴於外層查詢的結果.

table

查詢的是哪個表，mysql 查詢優化器執行的關聯順序並不和我們寫 SQL 時關聯的順序一致，下面我們講一下 Mysql 是如何對關聯查詢作優化的：

  • Mysql 查詢執行計劃總是按照左側深度優先樹的規則去執行，也就是從一個表開始一直嵌套循環，並不會類似平衡二叉樹一樣兩個分支同時執行
  • 在多表關聯時，可以通過多種不同的關聯順序獲取相同的執行結果，查詢優化器會評估不同的順序選擇一個代價最小的關聯查詢
  • 如果你不想要優化器改變你的關聯順序，可以使用 STRAIGHT JOIN 關鍵字強制使用你的關聯順序去執行
  • 如果關聯表特別多時，超過 optimizer_search_depth 的限制時，優化器評估每一種關聯順序的執行成本太高，這時會選擇「貪婪」的搜索模式

type

type 欄位比較重要, 它提供了判斷查詢是否高效的重要依據依據. 通過 type 欄位, 我們判斷此次查詢是全表掃描還是索引掃描等，type 類型的性能比較，通常來說, 不同的 type 類型的性能關係如下:

ALL < index < range < ref < eq_ref < const < system < NULL

  • NULL：這種訪問意味著 Mysql 能在優化階段分解查詢語句，在執行階段不需要訪問表或者索引
  • system: 預先知道整個表中只有一條數據. 這個類型是特殊的 const 類型

# 因為表中backend_user是主鍵，所以子查詢裏最多可以選出一條數據，所以最外層查詢的type是system，裡層查詢的type是const
explain select * from (select * from backend_user where id = 1) a;

  • const：針對主鍵或唯一索引的等值查詢掃描, 最多隻返回一行數據. const 查詢速度非常快, 因為它僅僅讀取一次即可
  • eq_ref: 此類型通常出現在多表的 join 查詢, 表示對於前表的每一個結果, 都只能匹配到後表的一行結果. 並且查詢的比較操作通常是 =, 查詢效率較高.
  • ref：此類型通常出現在多表的 join 查詢, 針對於非唯一或非主鍵索引, 或者是使用了最左前綴規則索引的查詢，可能會查詢出多個值
  • range: 表示使用索引範圍查詢, 通過索引欄位範圍獲取表中部分數據記錄. 這個類型通常出現在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。但是對於同樣的 type = range 的查詢，性能上還是有區別的：

# 雖然是都是範圍查詢，其實第二個查詢時多個等值條件查詢
# 對於第一個查詢，mysql 無法再使用該列後面的其它查詢索引了，而第二個則可以繼續使用索引
select id from actor where id > 45 and class_id = 3;
select id from actor where id in (44, 47, 48) and class_id = 3;

  • index: 表示通過索引進行全表掃描和 ALL 類型類似, 有點是避免了排序，確定是需要承擔按照索引次序讀取表的開銷。如果 Extra 列中出現了 「Using index」 表明是用了覆蓋索引，此時開銷非常小。
  • ALL: 表示全表掃描, 這個類型的查詢是性能最差的查詢之一。一般情況下都會從頭到尾掃描所有行，除非使用了 Limit 或者 Extra 列中顯示 「Using distinct/not exists」。

possible_keys

此次查詢中可能選用的索引，這些索引列是根據查詢的列以及比較操作符來判斷的，可能在後續的真實查詢中沒有用到也有可能

key

此次查詢中確切使用到的索引，如果在 possible_keys 中沒有出現而在 key 中出現，說明優化器可能出於另外原因比如選擇覆蓋索引，所以 possiable_keys 揭示了哪一個索引有助於高效進行查找，而 key 顯示了採用哪一個索引可以最小化查詢成本。

key_len

表示查詢優化器使用了索引的位元組數. 這個欄位可以評估組合索引是否完全被使用, 或只有最左部分欄位被使用到，比如我們建了一個組合索引(col1, col2)，那麼如下兩條查詢雖然用到的都是這個組合索引，但是對應的key_len的只是不一樣的。key_len 顯示了在索引欄位中可能的最大長度，而不是數據使用的實際位元組數

select * from table1 where col1 = 1;
select * from table1 where col1 = 1 and col2 = 2;

ref

這一列顯示了之前的表在 key 列記錄的索引中查找值所用的列或者常量

rows

rows 也是一個重要的欄位. MySQL 查詢優化器根據統計信息, 估算 SQL 要查找到結果集需要掃描讀取的數據行數。這個值非常直觀顯示 SQL 的效率好壞, 原則上 rows 越少越好。

  • 通過把每個表的 rows 值相乘可以粗略的估算出整個查詢要檢查的行數
  • 這個值只是一個估算的值，不是實際查出來的值

filtered

filtered 是在 MYSQL 5.1 中加進來的，在使用 EXPLAIN EXTENDED 時出現，表示此查詢條件所過濾的數據的百分比，將 rows 除以 filtered 可以估算出整個表數據行數。

Extra

EXplain 中的很多額外的信息會在 Extra 欄位顯示, 常見的有以下幾種內容:

  • Using filesort

當 Extra 中有 Using filesort 時, 表示 MySQL 需額外的排序操作, 不能通過索引順序達到排序效果. 一般有 Using filesort, 都建議優化去掉, 因為這樣的查詢 CPU 資源消耗大。 但是 Explain 不會告訴你 Mysql 將使用文件排序還是內存排序：

-- 比如我們建立索引為：KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)，那麼如下兩個查詢

EXPLAIN SELECT * FROM order_info ORDER BY product_name； -- Using filesort，不能通過索引進行排序，需要優化
EXPLAIN SELECT * FROM order_info ORDER BY user_id, product_name；-- 無 Using filesort，通過索引進行排序，優化成功

  • Using index

"覆蓋索引掃描", 表示查詢在索引樹中就可查找所需數據, 不用掃描表數據文件, 往往說明性能不錯

  • Using where

這意味著 Mysql 伺服器在存儲引擎檢索行後再進行過濾，一般出現 「Using where」 會受益於不同的索引

  • Using temporary

查詢有使用臨時表, 一般出現於排序, 分組和多表 join 的情況, 臨時表可能是內存臨時表或者文件臨時表

partitions

在 Mysql 5.1 版本中引入了 EXPLAIN PARTITIONS 可以顯示查詢將訪問的分區情況

參考文獻

  • MySQL 性能優化神器 Explain 使用分析

推薦閱讀：

相關文章