卡卡西：一文詳解explain各欄位含義

Luson發表於2021-09-18
AI

前言

explain有何用處呢：為了知道優化SQL語句的執行，需要檢視SQL語句的具體執行過程，以加快SQL語句的執行效率。
可以使用explain+SQL語句來模擬優化器執行SQL查詢語句，從而知道mysql是如何處理sql語句的。通過檢視執行計劃瞭解執行器是否按照我們想的那樣處理SQL。

explain執行計劃中包含的資訊如下：

id:  查詢序列號
select_type: 查詢型別
table: 表名或者別名
partitions: 匹配的分割槽
type: 訪問型別
possible_keys: 可能用到的索引
key: 實際用到的索引
key_len: 索引長度
ref: 與索引比較的列
rows: 估算的行數
filtered: 按表條件篩選的行百分比
 Extra: 額外資訊

解析

下面說下具體每一列的表示的含義和對應sql.
測試的表結構如下：

CREATE TABLE `demo`.`emp`  (
  `emp_id` bigint(20) NOT NULL,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '姓名',
  `empno` int(20) NOT NULL COMMENT '工號',
  `deptno` int(20) NOT NULL COMMENT '部門編號',
  `sal` int(11) NOT NULL DEFAULT 0 COMMENT '銷售量',
  PRIMARY KEY (`emp_id`) USING BTREE,
  INDEX `u1`(`deptno`) USING BTREE,
  UNIQUE INDEX `u2`(`empno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
CREATE TABLE `demo`.`dept`  (
  `id` bigint(20) NOT NULL,
  `deptno` int(20) NOT NULL COMMENT '部門編碼',
  `dname` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '部門名稱',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `dept_u1`(`deptno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
CREATE TABLE `demo`.`salgrade`  (
  `id` bigint(20) NOT NULL,
  `losal` int(20) NULL DEFAULT NULL,
  `hisal` int(20) NULL DEFAULT NULL,
  `emp_id` bigint(20) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

01、id列

select查詢的序列號(一組數字)，表示查詢中執行select子句或者操作表的順序。

id列分為三種情況：
1、如果id相同，那麼執行順序從上到下

mysql> explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal;

圖片

2、如果id不同，如果是子查詢，id的序號會遞增，id值越大優先順序越高，越先被執行

mysql> explain select * from emp e where e.deptno = (select d.deptno from dept d where d.dname = 'SALES');

圖片

3、id相同和不同的，同時存在：相同的可以認為是一組，從上往下順序執行，在所有組中，id值越大，優先順序越高，越先執行

mysql> explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal wheree.deptno = (select d.deptno from dept d where d.dname = 'SALES');

02、select_type列

主要用來分辨查詢的型別，是普通查詢還是聯合查詢還是子查詢

  1. sample: 簡單的查詢，不包含子查詢和union

    mysql> explain select * from emp;

    圖片

  2. primary: 查詢中若包含任何複雜的子查詢，最外層查詢則被標記為Primary

    mysql> explain select * from emp e where e.deptno = (select d.deptno from dept d where d.dname = 'SALES');

    圖片

  3. union: 在union，union all和子查詢中的第二個和隨後的select被標記為union

    mysql> explain select * from emp where deptno = 10 union select * from emp where sal >2000;

    圖片

  4. dependent union: 在包含UNION或者UNION ALL的大查詢中，如果各個小查詢都依賴於外層查詢的話，那除了最左邊的那個小查詢之外，其餘的小查詢的select_type的值就是DEPENDENT UNION。

    mysql> explain select * from emp e where e.empno  in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000)

    圖片

  5. union result: 從union表獲取結果的select。

    mysql> explain select * from emp where deptno = 10 union select * from emp where sal >2000;

    圖片

  6. subquery: 在select或者where列表中包含子查詢（不在from子句中）

    mysql> explain select * from emp where sal > (select avg(sal) from emp) ;

    圖片

  7. dependent subquery: 子查詢中的第一個select（不在from子句中），而且取決於外面的查詢。

    mysql> explain select e1.* from emp e1 WHERE e1.deptno = (SELECT deptno FROM emp e2 WHERE e1.empno = e2.empno);

    圖片

  8. derived:在FROM列表中包含的子查詢被標記為DERIVED，也叫做派生類

    mysql> explain select * from ( select emp_id,count(*) from emp group by emp_id ) e;

    圖片

  9. UNCACHEABLE SUBQUERY：一個子查詢的結果不能被快取，必須重新評估外連結的第一行對於外層的主表，子查詢不可被物化，每次都需要計算（耗時操作）

    mysql> explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);

    圖片

  10. uncacheable union: 表示union的查詢結果不能被快取：沒找到具體的sql語句驗證.

03、table列

對應行正在訪問哪一個表，表名或者別名，可能是臨時表或者union合併結果集.

1、如果是具體的表名，則表明從實際的物理表中獲取資料，當然也可以是表的別名.

2、表名是derivedN的形式，表示使用了id為N的查詢產生的衍生表.

3、當有union result的時候，表名是union n1,n2等的形式，n1,n2表示參與union的id.

04、type列

type顯示的是訪問型別，訪問型別表示我是以何種方式去訪問我們的資料，最容易想的是全表掃描，直接暴力的遍歷一張表去尋找需要的資料，效率非常低下。

訪問的型別有很多，效率從最好到最壞依次是：

system > **const** > eq_ref > **ref** > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > **range** > **index** > ALL

一般情況下，要保證查詢至少達到range級別，最好能達到ref

  1. all: 全表掃描，需要掃描整張表，從頭到尾找到需要的資料行。一般情況下出現這樣的sql語句而且資料量比較大的話那麼就需要進行優化。

    mysql> explain select * from emp;

    圖片

  2. index：全索引掃描這個比all的效率要好，主要有兩種情況，一種是當前的查詢時覆蓋索引，即我們需要的資料在索引中就可以索取，或者是使用了索引進行排序，這樣就避免資料的重排序

    mysql> explain  select empno from emp;

    圖片

  3. range：表示利用索引查詢的時候限制了範圍，在指定範圍內進行查詢，這樣避免了index的全索引掃描，適用的操作符：=, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() 

    mysql> explain select * from emp where empno between 100 and 200;

    圖片

  4. index_subquery：利用索引來關聯子查詢，不再掃描全表

    mysql> explain select * from emp where deptno not in (select deptno from emp)

    圖片
    但是大多數情況下使用SELECT子查詢時，MySQL查詢優化器會自動將子查詢優化為聯表查詢，因此 type 不會顯示為 index_subquery,而是ref

  5. unique_subquery: 該連線型別類似於index_subquery,使用的是唯一索引

    mysql> explain SELECT * from emp where emp_id not in (select emp.emp_id from emp );

    圖片
    大多數情況下使用SELECT子查詢時，MySQL查詢優化器會自動將子查詢優化為聯表查詢，因此 type 不會顯示為 index_subquery,而是eq_ref

  6. index_merge：在查詢過程中需要多個索引組合使用.
    mysql> 沒有模擬出來

  7. ref_or_null：對於某個欄位即需要關聯條件，也需要null值的情況下，查詢優化器會選擇這種訪問方式.
    mysql> 沒模擬出來

  8. ref：使用了非唯一性索引進行資料的查詢

    mysql> explain select * from emp where  deptno=10;

    圖片

  9. eq_ref ：當進行等值聯表查詢使用主鍵索引或者唯一性非空索引進行資料查詢(實際上唯一索引等值查詢type不是eq_ref而是const)

    mysql> explain select * from salgrade s LEFT JOIN emp e on s.emp_id = e.emp_id;

    圖片

  10. const：最多隻能匹配到一條資料，通常使用主鍵或唯一索引進行等值條件查詢

    mysql> explain select * from emp where empno = 10;

    圖片

  11. system：表只有一行記錄（等於系統表），這是const型別的特例，平時不會出現,不需要進行磁碟io

    mysql> explain SELECT * FROM `mysql`.`proxies_priv`;

    圖片

05、possible_keys列

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

06、key列

實際使用的索引，如果為null，則沒有使用索引，查詢中若使用了覆蓋索引，則該索引和查詢的select欄位重疊。

07、key_len列

表示索引中使用的位元組數，可以通過key_len計算查詢中使用的索引長度，在不損失精度的情況下長度越短越好。
索引越大佔用儲存空間越大，這樣io的次數和量就會增加，影響執行效率

08、ref列

顯示之前的表在key列記錄的索引中查詢值所用的列或者常量

09、rows列

根據表的統計資訊及索引使用情況，大致估算出找出所需記錄需要讀取的行數，此引數很重要，直接反應的sql找了多少資料，在完成目的的情況下越少越好。

10、filtered列

針對表中符合某個條件(where子句或者聯接條件)的記錄數的百分比所做的一個悲觀估算。

11、extra列

包含額外的資訊。

  1. using filesort: 說明mysql無法利用索引進行排序，只能利用排序演算法進行排序，會消耗額外的位置

    mysql> explain select * from emp order by sal;

    圖片

  2. using temporary: 建立臨時表來儲存中間結果，查詢完成之後把臨時表刪除

    mysql> explain select name,count(*) from emp where deptno = 10 group by name;

    圖片

  3. using index : 這個表示當前的查詢是覆蓋索引的，直接從索引中讀取資料，而不用訪問資料表。如果同時出現using where 表名索引被用來執行索引鍵值的查詢，如果沒有，表面索引被用來讀取資料，而不是真的查詢

    mysql> explain select deptno,count(*) from emp group by deptno limit 10;

    圖片

  4. using where: 使用where進行條件過濾

    mysql> explain select * from emp where name = 1;

  5. using join buffer: 使用連線快取

    mysql> explain select * from emp e left join dept d on e.deptno = d.deptno;

    圖片

  6. impossible where：where語句的結果總是false

    mysql> explain select * from emp where 1=0;

    圖片

作者：紀先生的筆記
連結：[https://mp.weixin.qq.com/s?__biz=Mzg5MDYzMzM5MA==&mid=2247483863&idx=1&sn=5f5911b6e4f8e993ec8e7ee1edc0c279&chksm=cfd8ec16f8af6500551efc1e49dee22ac4bca052c5b8d36044007afafc6f591b091f220a0c69&scene=21#wechat_redirect]
來源：微信公眾號

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章