首頁  >  文章  >  資料庫  >  MySQL中explain用法和結果分析(詳解)

MySQL中explain用法和結果分析(詳解)

青灯夜游
青灯夜游轉載
2020-07-07 16:04:1313183瀏覽

MySQL中explain用法和結果分析(詳解)

1. EXPLAIN簡介

使用EXPLAIN關鍵字可以模擬優化器執行SQL查詢語句,從而知道MySQL是如何處理你的SQL語句的。分析你的查詢語句或是表格結構的效能瓶頸。
➤  透過EXPLAIN,我們可以分析出以下結果:

  • #表的讀取順序
  • 資料讀取操作的操作類型
  • 哪些索引可以使用
  • 哪些索引被實際使用
  • 表之間的引用
  • #每張表有多少行被優化器查詢

#➤ 使用方式如下:

EXPLAIN SQL語句

EXPLAIN SELECT * FROM t1

執行計劃包含的資訊
MySQL中explain用法和結果分析(詳解)

2.執行計劃各欄位意義

2.1 id

select查詢的序號,包含一組數字,表示查詢中執行select子句或操作表的順序

id的結果共有3中情況

  • id相同,執行順序由上到下
    MySQL中explain用法和結果分析(詳解)
    [總結] 載入表的順序如上圖table列所示:t1  t3  t2

  • id不同,如果是子查詢,id的序號會遞增,id值越大優先權越高,越先被執行

MySQL中explain用法和結果分析(詳解)

  • id相同不同,同時存在
    MySQL中explain用法和結果分析(詳解)
  • #id相同不同,同時存在

##如上圖所示,在id為1時,table顯示的是

,這裡指的是指向id為2的表,即t3表的衍生表。
MySQL中explain用法和結果分析(詳解)
2.2 select_type

    常見和常用的值有以下幾種:
  • 分別用來表示查詢的類型,主要是用來區別普通查詢、聯合查詢、子查詢等的複雜查詢。

    SIMPLE
  • 簡單的select查詢
  • ,查詢中

    不包含子查詢或UNION## PRIMARY 查詢中若

    包含任何複雜的
  • 子部分,
  • 最外層查詢則被標記為PRIMARY

  • SUBQUERY
  • 在SELECT或WHERE清單中包含了子查詢

    DERIVED 在FROM清單中包含的

    子查詢被標記為DERIVED
  • (衍生),MySQL會遞歸執行這些子查詢,把
  • 結果放在暫存表

  • UNION 若第二個SELECT出現在UNION之後,則標示為UNION:若UNION包含在FROM子句的子查詢中,外層SELECT會被標記為:DERIVED

#UNION RESULT 從UNION表取得結果的SELECT

2.3 table

指的就是目前執行的表格
MySQL中explain用法和結果分析(詳解)2.4 type

type所顯示的是查詢使用了哪種類型,type包含的類型包括如下圖所示的幾種:

從最好到最差依序是:

system > const > eq_ref > ref > range > index > all
#######一般來說,得保證查詢至少達到range級別,最好能達到ref。 ######
  • system 表只有一行記錄(等於系統表),這是const類型的特列,平常不會出現,這個也可以忽略不計
  • const 表示透過索引一次就找到了,const用來比較primary key 或unique索引。因為只匹配一行數據,所以很快。如將主鍵置於where清單中,MySQL就能將該查詢轉換為一個常數。
    MySQL中explain用法和結果分析(詳解)
    先進行子查詢得到一個結果的d1臨時表,子查詢條件為id = 1 是常數,所以type是const,id為1的相當於只查詢一筆記錄,所以type為system。
  • eq_ref  唯一性索引掃描,對於每個索引鍵,表中只有一筆記錄與之相符。常見於主鍵或唯一索引掃描
  • ref 非唯一性索引掃描,返回匹配某個單獨值的所有行,本質上也是一種索引訪問,它返回所有匹配某個單獨值的行,然而,它可能會找到多個符合條件的行,所以他應該屬於查找和掃描的混合體。
    MySQL中explain用法和結果分析(詳解)
  • range  只檢索給定範圍的行,使用一個索引來選擇行,key列顯示使用了哪個索引,一般就是在你的where語句中出現between、、in等的查詢,這種範圍掃描索引比全表掃描要好,因為它只需要開始於索引的某一點,而結束於另一點,不用掃描全部索引。
    MySQL中explain用法和結果分析(詳解)
  • index  Full Index Scan,Index與All區別為index型別只遍歷索引樹。這通常比ALL快,因為索引檔案通常比資料檔案小。 (也就是說雖然all和Index都是讀全表,但index是從索引中讀取的,而all是從硬碟讀取的)
    MySQL中explain用法和結果分析(詳解)
    id是主鍵,所以存在主鍵索引
  • all  Full Table Scan  將遍歷全表以找到符合的行
    MySQL中explain用法和結果分析(詳解)

2.5 possible_keys 和key

#possible_keys 顯示可能套用在這張表中的索引,一個或多個。查詢所涉及的欄位上若存在索引,則該索引將會被列出,但不一定會被查詢實際使用

key

  • 實際使用的索引,如果為NULL,則沒有使用索引。 (可能原因包括沒有建立索引或索引失效)
    MySQL中explain用法和結果分析(詳解)
  • 查詢中若使用了覆蓋索引(select 後要查詢的欄位剛好和建立的索引欄位完全相同),則該索引僅出現在key列表中
    MySQL中explain用法和結果分析(詳解)
    MySQL中explain用法和結果分析(詳解)

#2.6 key_len

表示索引中使用的位元組數,可透過該列計算查詢中所使用的索引的長度,在不損失精確性的情況下,長度越短越好。 key_len顯示的值為索引欄位的最大可能長度,並非實際使用長度,即key_len是根據表定義計算而得,不是透過表內檢索出的。
MySQL中explain用法和結果分析(詳解)

2.7 ref

顯示索引的那一列被使用了,如果可能的話,最好是一個常數。哪些列或常數被用來尋找索引列上的值。
MySQL中explain用法和結果分析(詳解)

2.8 rows

根據表格統計資訊及索引選用情況,大致估算出找到所需的記錄所需要讀取的行數,也就是說,用的越少越好
MySQL中explain用法和結果分析(詳解)

2.9 Extra

包含不適合在其他欄位中顯式但十分重要的額外資訊

2.9.1 Using filesort(九死一生)

說明mysql會對資料使用一個外部的索引排序,而不是按照表內的索引順序進行讀取。 MySQL中無法利用索引完成的排序操作稱為「檔案排序」。
MySQL中explain用法和結果分析(詳解)

#

2.9.2 Using temporary(十死无生)

使用了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
MySQL中explain用法和結果分析(詳解)

2.9.3 Using index(发财了)

表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错。如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。
MySQL中explain用法和結果分析(詳解)
MySQL中explain用法和結果分析(詳解)

2.9.4 Using where

表明使用了where过滤

2.9.5 Using join buffer

表明使用了连接缓存,比如说在查询的时候,多表join的次数非常多,那么将配置文件中的缓冲区的join buffer调大一些。

2.9.6 impossible where

where子句的值总是false,不能用来获取任何元组

SELECT * FROM t_user WHERE id = '1' and id = '2'

2.9.7 select tables optimized away

在没有GROUPBY子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。

2.9.8 distinct

优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作

3. 实例分析

MySQL中explain用法和結果分析(詳解)

  • 执行顺序1:select_type为UNION,说明第四个select是UNION里的第二个select,最先执行【select name,id from t2】
  • 执行顺序2:id为3,是整个查询中第三个select的一部分。因查询包含在from中,所以为DERIVED【select id,name from t1 where other_column=’’】
  • 执行顺序3:select列表中的子查询select_type为subquery,为整个查询中的第二个select【select id from t3】
  • 执行顺序4:id列为1,表示是UNION里的第一个select,select_type列的primary表示该查询为外层查询,table列被标记为<derived3></derived3>,表示查询结果来自一个衍生表,其中derived3中的3代表该查询衍生自第三个select查询,即id为3的select。【select d1.name …】
  • 执行顺序5:代表从UNION的临时表中读取行的阶段,table列的表示用第一个和第四个select的结果进行UNION操作。【两个结果union操作】

推荐学习:mysql教程

以上是MySQL中explain用法和結果分析(詳解)的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文轉載於:csdn.net。如有侵權,請聯絡admin@php.cn刪除