關於Explain

最近在對sql進行性能優化因此對explain相關的知識進行一個簡單的整理歸納。

EXPLAIN：

命令輸出格式

id = 1    
select_type = SIMPLE    
table = clazz    
partitions =     
type = const    
possible_keys = PRIMARY    
key = PRIMARY    
key_len = 98    
ref = const    
rows = 1    
filtered = 100.00    
Extra =     

• id:SELECT 標識符，SQL執行的順序的標識，SQL從大到小的執行
• id相同時，執行順序由上至下
• 如果是子查詢，id的序號會遞增，id值越大優先順序越高，越先被執行
• 如果id相同，則認為是一組，從上往下順序執行；在所有組中，id值越大，優先順序越高，越先執行
• select_type
  • SIMPLE：簡單的SELECT未使用 UNION 查詢或子查詢
  • PRIMARY：表示此查詢是最外層的SELECT(如兩表做UNION或者存在子查詢的外層的表操作為PRIMARY,內層的操作為UNION)
  • UNION：表示UNION操作中，查詢中處於內層的SELECT(內層的SELECT語句與外層的SELECT語句沒有依賴關係)
  • DEPENDENT UNION：表示UNION操作中，查詢中處於內層的SELECT(內層的SELECT語句與外層的SELECT語句有依賴關係)
  • UNION RESULT：UNION 操作的結果,id值通常為null
  • SUBQUERY：子查詢中的第一個 SELECT
  • DEPENDENT SUBQUERY:子查詢中的第一個 SELECT, 子查詢依賴於外層的查詢結果
  • ERIVED：被驅動的SELECT子查詢（子查詢位於from子句）
  • MATERIALZED：物化子查詢（對此查詢會創建臨時表，將制定表物化為臨時表）
  • UNCACHEABLE SUBQUERY：無法緩存子查詢的結果，每次都需要計算
  • UNCACHEABLE UNION：UNION操作紅內層的子查詢無法被物化（類似於UNCACHABLESUBQUERY）

• table:表示查詢涉及的表或衍生表

• type
  • ALL：全表掃描，mysql將便利全表數據直至找到匹配的行
  • index：全索引掃描，遍歷索引樹
  • range:  範圍掃描，基於索引做掃描，如between,in,>=,like等操作
  • ref: 表示上述的連接匹配條件，即哪些列或產量被用於查找索引列上的值
  • eq_ref: 類似ref區別在於索引是唯一索引，對於每個索引鍵值表中只有一條記錄匹配，即多表連接中使用primary key或者unique key作為關聯條件
  • const: 只讀取一次就能獲得數據（如：主鍵）
  • system: const的特例，查詢的表中只有一行的情況下，使用system
  • null: mysql在優化的過程中分解語句，執行時甚至不用訪問表或索引，例如從一個索引列裡面選取最小值可以通過單獨索引查找完成　　

• partitions:記錄與查詢匹配的分區

• possible_keys:表示在查詢時, 能夠使用到的索引具體使用了哪些索引, 由 key 欄位決定.

• key:表示查詢時所真正使用到的索引.

• key_len:表示查詢優化器使用了索引的位元組數. 這個欄位可以評估組合索引是否完全被使用, 或只有最左部分欄位被使用到
  • 字元串
    • char(n): n 位元組長度
    • varchar(n): 如果是 utf8 編碼, 則是 3 n + 2位元組; 如果是 utf8mb4 編碼, 則是 4 n + 2 位元組
  • 數值類型　　
    • TINYINT: 1位元組
    • SMALLINT: 2位元組
    • MEDIUMINT: 3位元組
    • INT: 4位元組
    • BIGINT: 8位元組
  • 時間類型　
    • DATE: 3位元組
    • TIMESTAMP: 4位元組
    • DATETIME: 8位元組
  • 欄位屬性: NULL 屬性 占用一個位元組. 如果一個欄位是 NOT NULL 的, 則沒有此屬性.

• ref：被用來標識那些用來進行索引比較的列或者常量

• rows：估算 SQL 要查找到結果集需要掃描讀取的數據行數

• filterd：給出了一個百分比的值，這個百分比值和 rows 列的值一起使用

• Extra: 附加與操作相關聯的信息
• Using filesort
• 表示 MySQL 需額外的排序操作, 不能通過索引順序達到排序效果,查詢 CPU 資源消耗大建議優化去掉,
• Using index
• "覆蓋索引掃描", 表示查詢在索引樹中就可查找所需數據, 不用掃描表數據文件, 往往說明性能不錯
• Using where
• where條件用於篩選出與下一個表匹配的數據然後返回給客戶端
• Using temporary
• 查詢有使用臨時表, 一般出現於排序, 分組和多表 join 的情況, 查詢效率不高 建議優化.
• Impossible where
• WHERE條件過濾沒有效果，或者是始終選不出任何列(理解為最終是全表掃描)
• Impossible HAVING
• HAVING條件過濾沒有效果，或者是始終選不出任何列(理解為返回已有查詢的結果集)
• unique row not found
• 表中找不到滿足條件唯一索引或主鍵索引的列
• Using sort_union(...)，Using union(...)，Using intersect(...)
• 表示在index_merge的連接類型中索引合併是怎麼樣完成的,及使用了怎樣特別的演算法