MySQL explain

小松聊PHP进阶發表於2024-03-07
MySqlAI

簡介

mysql explain（或desc）用於分析SQL語句的執行計劃，多用於測試查詢效能。語法：explain sql...

注意

  1. explain執行DML語句，資料不發生變化。
  2. explain執行的結果可以有多條資料，一條資料對應一個表，如果涉及union，MySQL內部會產生一個臨時表，就會導致結果多一行資料。
  3. union all不會建立臨時表，所以就不會有多一行的資料。

id

這是select編號，不是返回結果的自增id。
值越大優先順序越高。
如果涉及多表join，id就會重複，因為就一個select。
涉及子查詢，MySQL Server 最佳化器 可能會把子查詢轉化為join，所以聯調select id可能都是1
涉及union，臨時表的那一列，id值為null。

select_type

操作型別，包括簡單表查詢、聯合查詢、子查詢等。

  • SIMPLE: 簡單表查詢，不包含union或者子查詢。
  • PRIMARY: 主查詢，複雜查詢中的最外層查詢，或者union中左邊的表。
  • SUBQUERY: 不相關子查詢
  • DEPENDENT SUBQUERY: 依賴外部查詢的子查詢，或者稱之為相關子查詢。
  • DERIVED: 派生表，從查詢結果派生的臨時表，from後面的子查詢。
  • UNION: UNION 查詢中各個子查詢的操作型別。
  • UNION RESULT: UNION 查詢的結果集。
  • DEPENDENT UNION: 依賴外部查詢的 UNION 查詢，也就是子查詢中有union
  • UNCACHEABLE SUBQUERY: 無法被快取的子查詢。
  • UNCACHEABLE UNION: 無法被快取的 UNION 查詢。

table

被操作的表，用於顯示被操作的物件。
實際表名：表示查詢語句中直接引用的表名。

  • <derivedN>：表示派生表，它是從查詢結果中派生出來的臨時表，派生表的編號可能是 1、2、3 等。
  • <unionM-N>：表示 UNION 查詢中的結果集，其中 M 表示結果集的編號，N 表示 UNION 查詢中的子查詢編號。
  • <temporary>：表示臨時表，這是在查詢過程中建立的臨時儲存表。
  • <subqueryN>：表示子查詢結果，其中 N 是子查詢的編號。

partitions

分析 MySQL 查詢語句如何利用分割槽表的工具，可以檢視 MySQL 執行查詢時會涉及到哪些分割槽，以及查詢最佳化器如何選擇分割槽來執行查詢。

type

返回在執行查詢時使用的訪問方法，和索引相關， 已做排序。

  • system，當使用MyiSAM或Memory引擎的表只有一條記錄的時候是system，不要奢望。
  • const：使用主鍵索引，或唯一索引等值匹配。
  • eq_ref：使用join時，被驅動表透過主鍵或者唯一索引列進行等值關聯的方式，例如select * from a left join b on a.id = b.id;，b是eq_ref，a是all。
  • ref: 使用非唯一索引等值匹配。
  • range：對新增過任意索引的列，進行範圍匹配，<、<=、>、>=、in、between。
  • index：MySQL 使用索引來掃描整個索引樹，不需要回表，通常會發生在覆蓋索引的情況。
  • ALL：全表掃描。

不常見型別：

  • fulltext：全文索引。
  • ref_or_null：對二級索引進行等值匹配，並新增or = null的條件。
  • index_merge：使用了多個單列索引來執行查詢。當在查詢條件中存在多個列，且每個列都有單獨的索引時。
  • unique_subquery：不容易遇見，in 後面跟子查詢，查詢最佳化器將in轉換為exists子查詢，且這些自查徐你可以使用到主鍵進行等值匹配才可以。如explain select * from table1
    where field in (select id from table2 where talbe1.fieldn = 'string') or field2 = 'string'
  • index_subquery：使用子查詢的結果作為索引來訪問另一個表。

possible_keys

根據where/group by修飾的欄位，可能使用的索引，並不保證實際執行時一定會使用這些索引，可以有多個。

key

表示實際選擇的索引。

keylen

索引欄位的最大長度，單位位元組，值越大越好，主要對於聯合索引有參考意義。
注意這裡的越大越好，是跟當前欄位的情況比較，如int型別佔4個位元組，顯示4或者5（包含null）就很好。如果是varchar(100)，則顯示400，或403（utf8mb4+null+記錄長度的位元組 ==> 100 * 4 + 1 + 2，如果欄位不為null，則省掉一個位元組），比顯示260更好。

ref

索引列做等值查詢時，與索引列進行等值匹配的物件資訊，對效能最佳化參考意義不大。
例如等值匹配到了，返回const。

rows

預估本次查詢要掃描的行數，值越小越好，小了代表範圍精準，進而實現快速查詢。

filtered

該filtered列指示按表條件過濾的錶行的估計百分比。越大越好。最大值為 100，這意味著沒有發生行過濾。
從 100 開始遞減的值表示過濾量的增加。。例如如果 rows為 1000， filtered為 50.00 (50%)，則與下表連線的行數為 1000 × 50% = 500。

Extra

用於備註補充。

  • Using temporary：表示SQL使用union建立的臨時表所在行。
  • Using index 表示查詢使用了覆蓋索引，即只使用了索引而沒有訪問實際的資料行，這通常發生在查詢條件中包含了索引覆蓋的所有列。
  • Using where 表示查詢使用了 WHERE 子句過濾資料。
  • Using temporary 表示查詢需要建立臨時表來處理結果集，這可能發生在使用了臨時表進行排序操作或者連線操作時。
  • Using filesort 表示查詢需要對結果集進行檔案排序操作，這通常發生在無法使用索引完成排序時。
  • Using join buffer 表示查詢使用了連線緩衝區來處理連線操作。
  • Impossible WHERE 表示 WHERE 子句的條件總是為 false，因此查詢將返回空結果集。
  • Select tables optimized away表示由於某些最佳化，查詢將跳過不必要的表訪問。
  • Full table scan 表示查詢將對整個表進行全表掃描，沒有使用任何索引。
  • Using index condition:表示使用了索引下推。
  • Using index for group-by:表示查詢使用了索引進行分組操作。
  • Using index for order by:表示查詢使用了索引進行排序操作。
  • Using index for distinct 表示查詢使用了索引進行去重操作。
  • Using index for limit:表示查詢使用了索引來執行 LIMIT 操作。
  • Using index for merge:表示查詢使用了索引合併進行連線操作。
  • Using where with pushed condition:表示查詢的 WHERE 條件中的部分條件被推遲執行。
  • Loose index scan: 表示在一些情況下，MySQL 可能對索引的列進行寬鬆掃描，而不是精確匹配。
  • Range checked for each record: 表示對於每個匹配的記錄，都需要進一步檢查範圍條件。
  • Using index for LIKE: 表示使用索引執行了 LIKE 查詢。
  • Distinct: 表示在處理查詢資料時會進行去重操作。
  • Order by: 表示查詢需要進行排序操作。
  • Table is marked as crashed and should be repaired: 表示表已標記為損壞，需要修復。
  • No tables used: 表示查詢使用了某種最佳化方式，無需訪問任何表。
  • Range checked for each record (index map: N): 和 Range checked for each record 類似，但額外說明了哪個表進行了進一步的範圍檢查。

擴充套件

explain 支援使用原生SQL進行json格式輸出，用法explain format=json sql...。

相關文章