MySql之EXPLAN詳解

程式設計師的貓發表於2020-10-30
MySql

使用 explain 查詢和分析SQl的執行記錄，可以進行sql的效能優化！

explain用法

mysql> explain select * from students;
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
1 row in set

mysql> explain extended select * from students;
+----+-------------+----------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |    3 |      100 |       |
+----+-------------+----------+------+---------------+------+---------+------+------+----------+-------+
1 row in set

explain結果欄位的含義介紹

id

  • SELECT識別符。這是SELECT的查詢序列號，表示查詢中執行select子句或操作表的順序！

select_type

SELECT型別,可以為以下任何一種:

  • SIMPLE：簡單SELECT(不使用UNION或子查詢)
  • PRIMARY：最外面的SELECT
  • UNION：UNION中的第二個或後面的SELECT語句
  • DEPENDENT UNION：UNION中的第二個或後面的SELECT語句,取決於外面的查詢
  • UNION RESULT：UNION 的結果
  • SUBQUERY：子查詢中的第一個SELECT
  • DEPENDENT SUBQUERY：子查詢中的第一個SELECT,取決於外面的查詢
  • DERIVED：匯出表的SELECT(FROM子句的子查詢)

table

輸出的行所引用的表！

type

聯接型別。下面給出各種聯接型別,按照從最佳型別到最壞型別進行排序:

  • system：表僅有一行(=系統表)。這是const聯接型別的一個特例。
  • const：表最多有一個匹配行,它將在查詢開始時被讀取。因為僅有一行,在這行的列值可被優化器剩餘部分認為是常數。const表很快,因為它們只讀取一次!
  • eq_ref：對於每個來自於前面的表的行組合,從該表中讀取一行。這可能是最好的聯接型別,除了const型別。
  • ref：對於每個來自於前面的表的行組合,所有有匹配索引值的行將從這張表中讀取。
  • ref_or_null：該聯接型別如同ref,但是新增了MySQL可以專門搜尋包含NULL值的行。
  • index_merge：該聯接型別表示使用了索引合併優化方法。
  • unique_subquery：該型別替換了下面形式的IN子查詢的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一個索引查詢函式,可以完全替換子查詢,效率更高。
  • index_subquery：該聯接型別類似於unique_subquery。可以替換IN子查詢,但只適合下列形式的子查詢中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
  • range：只檢索給定範圍的行,使用一個索引來選擇行。
  • index：該聯接型別與ALL相同,除了只有索引樹被掃描。這通常比ALL快,因為索引檔案通常比資料檔案小。
  • ALL：對於每個來自於先前的表的行組合,進行完整的表掃描。

possible_keys

指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的欄位上若存在索引，則該索引將被列出，但不一定被查詢使用

key

顯示MySQL在查詢中實際使用的索引，若沒有使用索引，顯示為NULL

key_len

表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度（key_len顯示的值為索引欄位的最大可能長度，並非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出的）

ref

表示上述表的連線匹配條件，即哪些列或常量被用於查詢索引列上的值

rows

顯示MySQL認為它執行查詢時必須檢查的行數。多行之間的資料相乘可以估算要處理的行數

filtered

顯示了通過條件過濾出的行數的百分比估計值。

Extra

該列包含MySQL解決查詢的詳細資訊

  • Distinct：MySQL發現第1個匹配行後,停止為當前的行組合搜尋更多的行。
  • Not exists：MySQL能夠對查詢進行LEFT JOIN優化,發現1個匹配LEFT JOIN標準的行後,不再為前面的的行組合在該表內檢查更多的行。
    range checked for each record (index map: #):MySQL沒有發現好的可以使用的索引,但發現如果來自前面的表的列值已知,可能部分索引可以使用。
  • Using filesort：MySQL需要額外的一次傳遞,以找出如何按排序順序檢索行。
  • Using index：從只使用索引樹中的資訊而不需要進一步搜尋讀取實際的行來檢索表中的列資訊。
  • Using temporary：為了解決查詢,MySQL需要建立一個臨時表來容納結果。
  • Using where:WHERE 子句用於限制哪一個行匹配下一個表或傳送到客戶。
  • Using sort_union(…), Using union(…), Using intersect(…):這些函式說明如何為index_merge聯接型別合併索引掃描。
  • Using index for group-by：類似於訪問表的Using index方式,Using index for group-by表示MySQL發現了一個索引,可以用來查 詢GROUP BY或DISTINCT查詢的所有列,而不要額外搜尋硬碟訪問實際的表。

explain使用例項

mysql> show keys from students;
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY           |            1 | stud_id     | A         |           3 | NULL     | NULL   |      | BTREE      |         |               |
| students |          1 | index_create_date |            1 | create_date | A         |           3 | NULL     | NULL   | YES  | BTREE      |         |               |
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set

mysql> explain  select * from students where stud_id = '1';
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | students | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set

mysql> explain  select * from students where create_date = '2010-01-01';
+----+-------------+----------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table    | type | possible_keys     | key               | key_len | ref   | rows | Extra       |
+----+-------------+----------+------+-------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | students | ref  | index_create_date | index_create_date | 4       | const |    1 | Using where |
+----+-------------+----------+------+-------------------+-------------------+---------+-------+------+-------------+
1 row in set

總結

  • EXPLAIN不會告訴你關於觸發器、儲存過程的資訊或使用者自定義函式對查詢的影響情況
  • EXPLAIN不考慮各種Cache
  • EXPLAIN不能顯示MySQL在執行查詢時所作的優化工作
  • 部分統計資訊是估算的，並非精確值
  • EXPALIN只能解釋SELECT操作，其他操作要重寫為SELECT後檢視執行計劃。
本作品採用《CC 協議》，轉載必須註明作者和本文連結
你還差得遠吶！

相關文章