程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQLEXPLAINSQL輸出信息描述

MySQLEXPLAINSQL輸出信息描述

編輯:MySQL綜合教程

MySQLEXPLAINSQL輸出信息描述


    EXPLAIN語句能夠被用於獲取一些關於SQL執行時的相關信息,比如表的連接順序,對表的方式方式等等。通過對該相關信息進行進一步的分析,我們
    可以通過對表添加適當的索引,以及優化連接順序,使用提示等等手段來達到使SQL高效運行的目的。本文描述了EXPLAIN的用法並給出了相關示例。

一、EXPLAIN概述

EXPLAIN 語句主要是用於解析SQL執行計劃,通過分析執行計劃采取適當的優化方式提高SQL運行的效率。
EXPLAIN 語句輸出通常包括id列,select_type,table,type,possible_keys,key等等列信息
MySQL 5.6.3後支持SELECT, DELETE, INSERT,REPLACE, and UPDATE. 
EXPLAIN EXTENDED支持一些額外的執行計劃相關的信息
EXPLAIN PARTITIONS支持基於分區表查詢執行計劃的相關信息

二、EXPLAIN輸出列描述

-- 下面通過示例來展示EXPLAIN輸出列
(root@localhost) [sakila]> explain select sum(amount) from customer a,
    -> payment b where 1=1 and a.customer_id=b.customer_id and
    -> email='[email protected]'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 590
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: sakila.a.customer_id
         rows: 14
        Extra: 

1、各列表示的意義

Column        Meaning
------        ------------------------------------
id            The SELECT identifier                       
select_type   The SELECT type
table         The table for the output row
partitions    The matching partitions
type          The join type
possible_keys The possible indexes to choose
key           index actually chosen
key_len       The length of the chosen key
ref           The columns compared to the index
rows          Estimate of rows to be examined
filtered      Percentage of rows filtered by table condition
Extra         Additional information

2、各列上的具體描述

id:
    包含一組數字,表示查詢中執行select子句或操作表的順序
    id相同,執行順序由上至下,否則id值越大(通常子查詢會產生)優先級越高,越先被執行
    id如果相同,可以認為是一組,從上往下順序執行;在所有組中,id值越大,優先級越高,越先執行

select_type:  
    表示查詢中每個select子句的類型(簡單 OR復雜)
    select_type              Value Meaning
    -------------            -----------------------------------------------
    SIMPLE                   Simple SELECT (not using UNION or subqueries)
    PRIMARY                  Outermost SELECT 最外層select
    UNION                    Second or later SELECT statement in a UNION
    DEPENDENT UNION          Second or later SELECT statement in a UNION, dependent on outer query
    UNION                    RESULT Result of a UNION.
    SUBQUERY                 First SELECT in subquery
    DEPENDENT SUBQUERY       First SELECT in subquery, dependent on outer query(通常為相關子查詢)
    DERIVED                  Derived table SELECT (subquery in FROM clause)
    MATERIALIZED             Materialized subquery
    UNCACHEABLE SUBQUERY     A subquery for which the result cannot be cached and must be reevaluated
                             for each row of the outer query
    UNCACHEABLE UNION        The second or later select in a UNION that belongs to an uncacheable
                             subquery (see UNCACHEABLE SUBQUERY)    

table:  
    從哪個表(表名)上輸出行記錄,也可能是下列值: 
    ? : The row refers to the union of the rows with id values of M and N.
    ? : The row refers to the derived table result for the row with an id value of N. 
                  A derived table may result, for example, from a subquery in the FROM clause.
    ? : The row refers to the result of a materialized subquery for the row with an id value of N. 

partitions: 
    查詢匹配的記錄來自哪一個分區,當使用EXPLAIN,分區PARTITIONS關鍵字被指定時

type:
    連接類型
    system          表只有一行
    const           表最多只有一行匹配,通用用於主鍵或者唯一索引比較時
    eq_ref          每次與之前的表合並行都只在該表讀取一行,這是除了system,const之外最好的一種,
                    特點是使用=,而且索引的所有部分都參與join且索引是主鍵或非空唯一鍵的索引
    ref             如果每次只匹配少數行,那就是比較好的一種,使用=或<=>,可以是左覆蓋索引或非主鍵或非唯一鍵
    fulltext        全文搜索
    ref_or_null     與ref類似,但包括NULL
    index_merge     表示出現了索引合並優化(包括交集,並集以及交集之間的並集),但不包括跨表和全文索引。
                    這個比較復雜,目前的理解是合並單表的范圍索引掃描(如果成本估算比普通的range要更優的話)
    unique_subquery 在in子查詢中,就是value in (select...)把形如“select unique_key_column”的子查詢替換。
                    PS:所以不一定in子句中使用子查詢就是低效的!
    index_subquery  同上,但把形如”select non_unique_key_column“的子查詢替換
    range           常數值的范圍
    index           a.當查詢是索引覆蓋的,即所有數據均可從索引樹獲取的時候(Extra中有Using Index);
                    b.以索引順序從索引中查找數據行的全表掃描(無 Using Index);
                    c.如果Extra中Using Index與Using Where同時出現的話,則是利用索引查找鍵值的意思;
                    d.如單獨出現,則是用讀索引來代替讀行,但不用於查找
    all             全表掃描

possible_keys:
    指出MySQL能使用哪個索引在表中找到行。
    查詢涉及到的字段上若存在索引則該索引將被列出,但不一定被查詢使用。
    如果改列為NULL,說明該查詢不會使用到當前表上的相關索引,考慮是否有必要添加索引

key
    顯示MySQL在查詢中實際使用的索引,若沒有使用索引,顯示為NULL
    也可能存在key不等於possible_keys的情形,即possible_keys不適合提取所需的行
    而查詢所選擇的列在使用其他索引時更高效
    TIPS:查詢中若使用了覆蓋索引,則該索引僅出現在key列表中

key_len
    表示索引中使用的字節數,可通過該列計算查詢中使用的索引的長度

ref
    表示上述表的連接匹配條件,即哪些列或常量被用於查找索引列上的值

rows
    表示MySQL根據表統計信息及索引選用情況,估算的找到所需的記錄所需要讀取的行數
    對於InnoDB,該值為預估,不一定精確

Extra
    包含不適合在其他列中顯示但十分重要的額外信息

三、使用EXPLAIN EXTENDED 示例

(root@localhost) [sakila]> explain extended select * from city where country_id in
    -> ( select country_id from country where country='China') and 1=1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: country
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 109
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
         type: ref
possible_keys: idx_fk_country_id
          key: idx_fk_country_id
      key_len: 2
          ref: sakila.country.country_id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

(root@localhost) [sakila]> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `city`.`city_id` AS `city_id`,`city`.`city` AS `city`,`city`.`country_id` 
AS `country_id`,`city`.`last_update` AS `last_update` from `sakila`.`country` join `sakila`.`city` where
 ((`city`.`country_id` = `country`.`country_id`) and (`country`.`country` = 'China'))
1 row in set (0.00 sec)

-- 從上面的extended使用可以看出,查詢中多出了filtered列
-- 其次原來的SQL語句真正在執行的時候被改寫,即原來的1=1的條件被去掉
-- 對於復雜的SQL語句我們可以通過該方式獲得一個比較清晰的真正被執行的SQL語句

四、EXPLAIN PARTITIONS示例

(root@localhost) [sakila]> CREATE TABLE `actor_part` (
    ->   `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    ->   `first_name` varchar(45) NOT NULL,
    ->   `last_name` varchar(45) NOT NULL,
    ->   `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`actor_id`),
    ->   KEY `idx_actor_last_name` (`last_name`)
    -> ) partition by hash(actor_id) partitions 4;
Query OK, 0 rows affected (0.11 sec)

(root@localhost) [sakila]> insert into actor_part select * from actor;
Query OK, 200 rows affected (0.02 sec)
Records: 200  Duplicates: 0  Warnings: 0

(root@localhost) [sakila]> explain select * from actor_part where actor_id=10;  -- 未使用partitions時
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | actor_part | const | PRIMARY       | PRIMARY | 2       | const |    1 | NULL  |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

(root@localhost) [sakila]> explain partitions select * from actor_part where actor_id=10; -- 使用partitions時
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | actor_part | p2         | const | PRIMARY       | PRIMARY | 2       | const |    1 | NULL  |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
-- 多出了partitions列

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved