MySQL的Explain總結

大隊長11發表於2022-05-26
MySqlAI

Explain簡介

MySQL優化器在基於成本的計算和基於規則的SQL優化會生成一個所謂的執行計劃，我們就可以使用執行計劃檢視MySQL對該語句具體的執行方式。

介紹這個好囉嗦就是了，我們可以通過這個優化器展示的執行計劃，檢視優化器對我們的SQL進行優化的步驟，連線轉換成單表訪問時的優化。以及對於之前知識的複習了屬於是，比如訪問方式，索引的選擇，半連線等SQL語句優化。

mysql> explain select * from mall.ums_admin;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | ums_admin | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

上述是我們使用explain使用的簡單小例子。下面是每列的簡單描述。

image

我們接下來就是對於Explain中展示的列進行介紹，回涉及到之前的SQL成本和語句優化。

table列

對於每個SQL語句來說，不管是不是使用連線的多表查詢，我們最後都會變成執行多個單表查詢的語句。所以table列就指明瞭當前執行某個計劃的表是哪一個。就比如上面例子的單表查詢的SQL。

ID列

會有以下幾種情況

  1. 單表查詢，此時的ID列就為1。

  2. 連線查詢，此時對於兩個表的連線查詢ID列都為1，MySQL視在前面的表就是作為驅動表，在後面的表就是作為被驅動表。

image

  1. 子查詢，有多種情況

    1. 當子查詢不能被優化（前面提到的物化錶轉連線，半連線）時，每次出現一次SELECT關鍵字時，就會再分配一個獨立的ID，即ID加一。

    image

    1. 當子查詢能被優化為連線時（物化表不能轉連線的情況也不能優化哦），就會對SQL語句進行重寫優化，就會被視為連線，這是ID列就和外層相等咯。

    image

  2. UNION連表，就是出現一個select就再分配一個ID值。稍微有點特殊對於UNION ALL。

image

​ 首先呢是因為UNION關鍵字會對合並的結果集進行去重，進行去重就得建立一個臨時表來進行去重，第三行是一個臨時表。但是呢UNION ALL不去重就不會出現第三行記錄。

image

select_type列

image

Simple

就是簡單的單表查詢或者連線查詢。

PRIMARY

就是UNION或UNION ALL或子查詢的情況下，最左邊的select就是PRIMARY型別的。

image

UNION

就是UNION的時候除了最左邊的SELECT是PRIMARY型別，其他的SELECT都是UNION。

UNION RESULT

UNION的結果就會建立一個臨時表進行去重，臨時表就是這個select_type。

SUBQUERY

  1. 子查詢不能轉換為半連線
  2. 該子查詢是不相關子查詢
  3. 子查詢就會考慮使用物化的方案(如果數量很少就不會物化)

以上三個條件成立，此時子查詢的第一個select部分的select_type就是SUBQUERY。如果物化的話，子查詢只會執行一次，應該不用多說了。

DEPENDENT SUBQUERY

  1. 子查詢不能轉換為半連線
  2. 子查詢是相關子查詢

以上條件成立，此時子查詢的第一個select部分的select_type就是DEPENDENT SUBQUERY。相關外層查詢會不斷傳參然後一直進行子查詢。

DEPENDENT UNION

UNION中除了最左邊的select部分，被union連線的小查詢的select_type 都是DEPENDENT UNION。

DERIVED

採用物化的方式執行的派生表，其中的子查詢的select_type就是DERIVED。

image

MATERIALIZED

當子查詢是物化後轉連線的方式，就是首先子查詢是不相關子查詢，然後子查詢執行物化，優化成連線的方式和外層查詢。此時子查詢的select_type就是MATERIALIZED。

image

partitions列

一般情況下都為null。

type列

就是介紹訪問當前行的表的訪問方式。

我們之前學習的有

  • const 主鍵或唯一索引等值查詢
  • ref 二級索引等值查詢
  • ref_or_null 二級索引等值查詢或null值
  • range 範圍查詢
  • index 使用二級索引進行結果覆蓋
  • all 全表掃描

新的

  • system 當表中只有一條記錄，且該表的儲存引擎的統計資料是精確的（MYISAM、MEMORY），此時訪問就是system。
  • eq_ref 連線查詢時，對於被驅動表是使用主鍵或唯一索引進行訪問的，被驅動表的訪問方式就是eq_ref。
  • index_merge 索引合併，就是intersection索引交集或者union索引並集，sort_union排序並集。
  • unique_subquery 就是優化器將外層查詢的in優化為exists，子查詢可以使用主鍵或唯一索引進行查詢，此時子查詢就是unique_subquery 。？？？
  • index_subquery 就是上面的變種，此時子查詢使用普通的二級索引就是這個訪問方式。

possible_key和key列

possible_key就是SQL可以用到的索引。

key就是優化器計算成本後決定使用的索引列。

key_len列

就是使用的索引的索引記錄的長度。有三部分相加而成。

  • 如果是固定長度的，int這類的就是其的固定長度4。如果是變長的，比如varchar(100)且字符集utf8的就是最大長度300。
  • 能不能為null值，如果可以需要1位元組。
  • 記錄變長欄位的長度，預設就是2位元組，變長欄位需要新增（字符集變長也需要新增）。

對於上面三部分，前面已經講得很清楚了。

如果是varchar(100)可以為null的列，就需要300位元組資料最大長度+1位元組的非null+2位元組記錄長度，總長度就是303位元組。

對於聯合索引來說，用幾個索引這個值就根據索引進行疊加上去，606就是倆varchar(100)可以為null的欄位的聯合索引。

ref列

就是當我們使用索引進行匹配的時候，索引列具體等值匹配的是什麼東西，常數值還是一個列等等。

image

rows列

代表優化器經過預測具體要掃描表或者索引多少行。

filter列

代表驅動表的扇出比例，比如驅動表經過自身查詢條件後預計會有多少記錄數符合條件，輸出出來。

對於單表來說這個欄位沒有意義，但是對於連線查詢來說，可以預計被驅動表會執行多少次，即驅動表的扇出值。

extra列

就是優化器為我們提供額外資訊，來幫助我們分析語句。

  • No tables used

    • 表示查詢語句沒有from，沒有從表被使用到。

  • Impossible where

    • 顧名思義，不可能滿足的where語句，如1 != 1這種永遠不可能滿足

  • No matching min/max row

    • 使用min和max聚集函式但是where語句沒有能匹配的記錄。

  • Using index

    • 就是索引覆蓋嘛。忘了可以回前面SQL優化文章看看

  • Using index condition

    • 就是索引一個條件能使用，一個條件不能使用，導致我們使用索引先進行一個條件的查詢，然後再對查詢結果進行另外一個條件的過濾，然後再回表。
    • image

  • Using where

    • 使用全表掃描或索引掃描，然後語句中有where的普通條件需要一條一條進行過濾，通常是沒有使用索引的列，就會出現Using where的備註。

  • Using join buffer

    • 我們使用join buffer加快驅動表對於被驅動表的連線。

  • Not exists

    • 當進行外連線的時候，被驅動表的搜尋條件為某個列 is null 但是列本身是not null的，這時就會出現not exists

  • Using intersect(...) ，Using union(...)，Using sort_union(...)

    • 當出現著這個備註就表示使用了索引合併。

  • Zero limit

    • 出現limit 0，這種不要結果的SQL。

  • Using filesort

    • 當無法使用索引排序，就會將資料在記憶體(資料量少的時候)或者在磁碟(資料量多的時候)進行對應條件的排序。這是十分耗時的，這是能用索引儘量用索引。

  • Using temporary

    • 使用了臨時表

  • Start temporary , End temporary

    • 就是查詢優化器使用半連線優化時，使用的是臨時表對連線結果去重的方案實現半連線的時候，驅動表就會在extra中顯示Start temporary，而被驅動表顯示End temporary。

  • LooseScan

    • 就是我們使用半連線的LooseScan的策略實現時，就會在驅動表中出現這個。

  • FirstMatch

    • 忘了去SQL優化看了一下，就是最笨的那個驅動表一條一條匹配被驅動表的方式，就會在被驅動表顯示此註釋。

相關文章