[Mysql]Explain

Duancf發表於2024-07-05
MySqlAI

Explain執行計劃分析

什麼是執行計劃？

執行計劃 是指一條 SQL 語句在經過 MySQL 查詢最佳化器 的最佳化後，具體的執行方式。
執行計劃通常用於 SQL 效能分析、最佳化等場景。透過 EXPLAIN 的結果，可以瞭解到如資料表的查詢順序、資料查詢操作的操作型別、哪些索引可以被命中、哪些索引實際會命中、每個資料表有多少行記錄被查詢等資訊。

如何獲取執行計劃？

MySQL 為我們提供了 EXPLAIN 命令，來獲取執行計劃的相關資訊。
需要注意的是，EXPLAIN 語句並不會真的去執行相關的語句，而是透過查詢最佳化器對語句進行分析，找出最優的查詢方案，並顯示對應的資訊。
EXPLAIN 執行計劃支援SELECT、DELETE、INSERT、REPLACE 以及 UPDATE 語句。我們一般多用於分析 SELECT 查詢語句，使用起來非常簡單，語法如下：
EXPLAIN + SELECT 查詢語句；
我們簡單來看下一條查詢語句的執行計劃：

mysql> explain SELECT * FROM dept_emp WHERE emp_no IN (SELECT emp_no FROM dept_emp GROUP BY emp_no HAVING COUNT(emp_no)>1);
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys   | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | dept_emp | NULL       | ALL   | NULL            | NULL    | NULL    | NULL | 331143 |   100.00 | Using where |
|  2 | SUBQUERY    | dept_emp | NULL       | index | PRIMARY,dept_no | PRIMARY | 16      | NULL | 331143 |   100.00 | Using index |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+

可以看到，執行計劃結果中共有 12 列，各列代表的含義總結如下表：

列名 含義
id SELECT 查詢的序列識別符號
select_type SELECT 關鍵字對應的查詢型別
table 用到的表名
partitions 匹配的分割槽，對於未分割槽的表，值為 NULL
type 表的訪問方法
possible_keys 可能用到的索引
key 實際用到的索引
key_len 所選索引的長度
ref 當使用索引等值查詢時，與索引作比較的列或常量
rows 預計要讀取的行數
filtered 按表條件過濾後，留存的記錄數的百分比
Extra 附加資訊

分析 EXPLAIN 結果

為了分析 EXPLAIN 語句的執行結果，我們需要搞懂執行計劃中的重要欄位。

id

SELECT 識別符號，是查詢中 SELECT 的序號，用來標識整個查詢中 SELECT 語句的順序。(尤其是有子查詢的時候)
id 如果相同，從上往下依次執行。id 不同，id 值越大，執行優先順序越高，如果行引用其他行的並集結果，則該值可以為 NULL。

select_type

查詢的型別，主要用於區分普通查詢、聯合查詢、子查詢等複雜的查詢，常見的值有：
SIMPLE 簡單查詢，不包含 UNION 或者子查詢。
PRIMARY 查詢中如果包含子查詢或其他部分，外層的 SELECT 將被標記為 PRIMARY。
SUBQUERY 子查詢中的第一個 SELECT。
UNION 在 UNION 語句中，UNION 之後出現的 SELECT。
DERIVED 在 FROM 中出現的子查詢將被標記為 DERIVED。
UNION RESULT UNION 查詢的結果。

union

UNION 是 MySQL 中用於合併兩個或多個 SELECT 語句結果集的關鍵字。它用於將多個查詢的結果合併為一個結果集，且結果集中不包含重複的行。UNION 運算子執行垂直合併，即按行合併結果。

基本語法如下：

SELECT column1, column2, ...
FROM table1
WHERE condition1

UNION [DISTINCT | ALL]

SELECT column1, column2, ...
FROM table2
WHERE condition2;
  • column1, column2, ...: 要選擇的列。
  • table1, table2: 要查詢的表。
  • condition1, condition2: 查詢的條件。
  • DISTINCT: 可選關鍵字，用於去除合併結果中的重複行。
  • ALL: 可選關鍵字，用於保留合併結果中的所有行，包括重複行。

要注意的是，UNION 運算子預設會去除結果中的重複行，如果想要保留重複行，可以使用 UNION ALL。

例如，假設有兩個表 employees1 和 employees2，它們有相同的列結構，可以執行以下查詢：

SELECT employee_id, first_name, last_name
FROM employees1

UNION

SELECT employee_id, first_name, last_name
FROM employees2;

這個查詢將合併 employees1 和 employees2 中符合條件的行，並去除重複的行。如果想要包含重複行，可以使用 UNION ALL 替代 UNION。

SELECT employee_id, first_name, last_name
FROM employees1

UNION ALL

SELECT employee_id, first_name, last_name
FROM employees2;

UNION 可以用於合併任意數量的查詢，只要它們具有相同的列結構。需要確保各個查詢的列順序和資料型別匹配。

總的來說，UNION 是一個方便的工具，可以用於合併多個查詢的結果集，但要謹慎使用，確保你的查詢邏輯正確且效率高。

table

查詢用到的表名，每行都有對應的表名，表名除了正常的表之外，也可能是以下列出的值：

<union M,N> : 本行引用了 id 為 M 和 N 的行的 UNION 結果；(這裡的M,N不是指資料庫表中的M,N而是指執行計劃中的M,N行)
: 本行引用了 id 為 N 的表所產生的的派生表結果。派生表有可能產生自 FROM 語句中的子查詢。
: 本行引用了 id 為 N 的表所產生的的物化子查詢結果。

type（重要）

查詢執行的型別，描述了查詢是如何執行的。所有值的順序從最優到最差排序為：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

常見的幾種型別具體含義如下：

system：如果表使用的引擎對於錶行數統計是精確的（如：MyISAM），且表中只有一行記錄的情況下，訪問方法是 system ，是 const 的一種特例。

const：表中最多隻有一行匹配的記錄，一次查詢就可以找到，常用於使用主鍵或唯一索引的所有欄位作為查詢條件。

eq_ref：當連表查詢時，前一張表的行在當前這張表中只有一行與之對應。是除了 system 與 const 之外最好的 join 方式，常用於使用主鍵或唯一索引的所有欄位作為連表條件。

ref：使用普通索引作為查詢條件，查詢結果可能找到多個符合條件的行。

index_merge：當查詢條件使用了多個索引時，表示開啟了 Index Merge 最佳化，此時執行計劃中的 key 列列出了使用到的索引。

range：對索引列進行範圍查詢，執行計劃中的 key 列表示哪個索引被使用了。

index：查詢遍歷了整棵索引樹，與 ALL 類似，只不過掃描的是索引，而索引一般在記憶體中，速度更快。

ALL：全表掃描。

possible_keys

possible_keys 列表示 MySQL 執行查詢時可能用到的索引。如果這一列為 NULL ，則表示沒有可能用到的索引；這種情況下，需要檢查 WHERE 語句中所使用的的列，看是否可以透過給這些列中某個或多個新增索引的方法來提高查詢效能。

key（重要）

key 列表示 MySQL 實際使用到的索引。如果為 NULL，則表示未用到索引。

key_len

key_len 列表示 MySQL 實際使用的索引的最大長度；當使用到聯合索引時，有可能是多個列的長度和。在滿足需求的前提下越短越好。如果 key 列顯示 NULL ，則 key_len 列也顯示 NULL 。

rows

rows 列表示根據表統計資訊及選用情況，大致估算出找到所需的記錄或所需讀取的行數，數值越小越好。

Extra（重要）

這列包含了 MySQL 解析查詢的額外資訊，透過這些資訊，可以更準確的理解 MySQL 到底是如何執行查詢的。常見的值如下：

Using filesort：在排序時使用了外部的索引排序，沒有用到表內索引進行排序。
Using temporary：MySQL 需要建立臨時表來儲存查詢的結果，常見於 ORDER BY 和 GROUP BY。
Using index：表明查詢使用了覆蓋索引，不用回表，查詢效率非常高。
Using index condition：表示查詢最佳化器選擇使用了索引條件下推這個特性。
Using where：表明查詢使用了 WHERE 子句進行條件過濾。一般在沒有使用到索引的時候會出現。
Using join buffer (Block Nested Loop)：連表查詢的方式，表示當被驅動表的沒有使用索引的時候，MySQL 會先將驅動表讀出來放到 join buffer 中，再遍歷被驅動表與驅動表進行查詢。
這裡提醒下，當 Extra 列包含 Using filesort 或 Using temporary 時，MySQL 的效能可能會存在問題，需要儘可能避免。

在MySQL的EXPLAIN輸出中，Using filesort是一個常見的術語，它指的是MySQL需要進行額外的排序操作來滿足查詢中的ORDER BY語句，而這個排序操作不能完全透過索引來完成。儘管名稱中包含"file"，但這並不意味著排序操作一定會在磁碟上進行；排序可以在記憶體中完成，也可能因為記憶體不足而退化到磁碟上。

為什麼會出現Using filesort？

當查詢包含ORDER BY語句，且以下情況之一成立時，MySQL可能會使用檔案排序：

  • 索引無法滿足排序需求：查詢所使用的索引不能直接用來滿足ORDER BY中指定的排序順序。這可能是因為ORDER BY的列不是索引的一部分，或者即使是索引的一部分，但因為查詢條件的限制，索引無法直接用於排序。
  • 複合索引順序與ORDER BY順序不匹配：即使ORDER BY的列在一個複合索引中，但如果排序的順序（升序/降序）或列的順序與索引中的不一致，MySQL也可能需要進行外部排序。

檔案排序是怎樣進行的？

當MySQL決定使用檔案排序來執行ORDER BY操作時，它會按以下步驟進行：

  1. 掃描表：首先，MySQL掃描表或索引，獲取必要的行。
  2. 排序：然後，根據ORDER BY子句的要求，MySQL在記憶體中對這些行進行排序。如果排序的資料量超過了系統變數sort_buffer_size的設定值，MySQL可能會使用磁碟臨時檔案來輔助排序。
  3. 返回結果：最後，MySQL按照排序後的順序返回結果集。

如何避免Using filesort？

  • 最佳化索引：確保你的查詢能夠更好地利用索引進行排序。為ORDER BY中的列建立適當的索引，尤其是當查詢也包含WHERE子句時，考慮建立複合索引。
  • 調整查詢：有時候，透過調整查詢的結構，可以消除對檔案排序的需求。比如，透過改變ORDER BY中列的順序，或者移除不必要的排序操作。
  • 增加記憶體：增加sort_buffer_size引數的大小可以提高記憶體中排序的能力，減少磁碟IO的需要。然而，這應該謹慎進行，因為過大的排序緩衝區可能會對系統效能產生負面影響。

理解Using filesort的含義和背後的機制可以幫助你更好地最佳化你的SQL查詢，減少排序操作的開銷，從而提高查詢效能。

相關文章