Explain執行計劃詳解

IT王小二發表於2022-01-24
AI

作者：IT王小二

部落格：https://itwxe.com

一條查詢 SQL 語句為什麼會執行很慢？執行的時候走了什麼索引？多表查詢的時候 SQL 大體執行流程是怎麼樣的？Explain 執行計劃詳解給各位客官一一道來。

一、示例MySQL安裝方式和版本

首先需要 MySQL 安裝的客官看這兩篇，小二演示使用的是 Docker 的安裝方式：

小二安裝完的 MySQL 資料庫版本為 5.7.36。

二、Explain工具簡介

通常我們在生產環境會碰到查詢速度比較慢的 SQL 語句，那麼怎麼知道 SQL 語句為什麼執行慢呢？這個時候就需要用到 Explain 執行計劃來分析我們的語句了。

通過使用 Explain 關鍵字可以模擬優化器執行 SQL 查詢語句，執行會返回執行計劃的資訊，而不是執行這條SQL，當然如果 from 中包含子查詢，仍會執行該子查詢，將結果放入臨時表中 。

總的來說，通過 Explain 從而知道 MySQL 是如何處理你的 SQL 語句的，分析查詢語句或是表結構的效能瓶頸。那麼通過 Explain 執行計劃可以知道：

  • 表的讀取順序
  • 資料讀取操作的操作型別
  • 哪些索引可能被使用
  • 哪些索引實際被使用
  • 表之間的引用
  • 每張表估計有多少行會被執行

三、本文用到的示例表

CREATE TABLE `author` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `author` (`id`, `name`, `update_time`) VALUES (1,'itwxe','2022-01-12 19:27:18'), (2,'admin','2022-01-12 19:27:18'), (3,'superAdmin','2022-01-12 19:27:18');

CREATE TABLE `blog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `blog` (`id`, `title`) VALUES (1,'blog1'),(2,'blog2'),(3,'blog3');

CREATE TABLE `author_blog` (
  `id` int(11) NOT NULL,
  `author_id` int(11) NOT NULL,
  `blog_id` int(11) NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_author_blog_id` (`author_id`,`blog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `author_blog` (`id`, `author_id`, `blog_id`) VALUES (1,1,1),(2,2,1),(3,1,2);

四、Explain的變種

Explain 的語法非常簡單，只需要在查詢語句前面加一個 EXPLAIN 關鍵字即可。

explain select * from author where id = 1;

explain語法示例

如果你發現你的版本少了 partitions 或者 filtered 列欄位資訊的話，那是因為在 MySQL5.7 的某個小版本之前的版本是沒有這兩個欄位的。rows * filtered/100 可以估算出將要和 explain 中前一個表進行連線的行數(前一個表指 explain 中的 id 值比當前表 id 值小的表)。

那麼低版本想要看到這兩個欄位也是有辦法的，這就是小二說的 Explain 的兩個變種。

  • explain extended：會在低版本 MySQL explain 的基礎上額外提供一些查詢優化的資訊 filtered 列。
  • explain partitions：會在低版本 MySQL explain 的基礎上額外提供一個 partitions 欄位，如果查詢是基於分割槽表的話，會顯示查詢將訪問的分割槽。

在 explain 緊隨其後執行的 show warnings 命令可以得到優化之後的查詢語句，從而看到優化器優化了什麼東西，當然優化後的 SQL 並不一定符合 SQL的語法，但是 MySQL 可以識別並可以執行。

MySQL優化後的語句

五、Explain中的列

下面小二就來說說 Explain 中每個列的資訊，當然每個列的值只會介紹常見的幾個，如果沒提到各位小夥伴可以檢視 Explain 官方文件：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

1. id

id 列表示的編號是 select 的序列號，有幾個 select 就有幾個 id，並且 id 的順序是按 select 出現的順序增長的，id 列越大執行優先順序越高，id 相同則從上往下執行，id 為 NULL 最後執行。

explain select * from author where id = (select author_id from author_blog where id = 2);

id列

可以看到子查詢 id 為 2，所以先執行子查詢再執行外層查詢。

2. select_type

select_type 列表示對應行查詢的型別。

1）SIMPLE：簡單查詢，不包含子查詢和 union。

explain select * from author where id = 1;

select_type_simple

2）PRIMARY：複雜查詢中最外層的 select。

3）SUBQUERY：包含在 select 中的子查詢(不在 from 子句中)。

4）DERIVED：包含在 from 子句中的子查詢。MySQL 會將結果存放在一個臨時表中，也稱為派生表(derived的英文含義)。

用這個例子來了解 PRIMARY、SUBQUERY 和 DERIVED 型別。

# 關閉 MySQL5.7 新特性對衍生表的合併優化
set session optimizer_switch = 'derived_merge=off';

explain select (select 1 from blog where id = 1) from (select 1 from author where id = 1) tmp;

select_type_primary_subquery_derived_1

別問小二為啥寫這種語句，一切的一切都是為了演示效果啊~~~

開啟 MySQL5.7 新特性預設對衍生表的合併優化，就可以看到不一樣的效果了。

set session optimizer_switch = 'derived_merge=on';

select_type_primary_subquery_derived_2

5）UNION：在 union 中的第二個和隨後的 select。

6）UNION RESULT：從 union 臨時表檢索結果的 select。

用這個例子來了解 UNION、UNION RESULT 型別。

explain select id from author where id = 1 union select id from blog where id = 2;

select_type_simple

3. table

table 列表示 explain 的一行正在訪問哪個表。

當 from 子句中有子查詢時，table 列是 <derivenN> 格式，表示當前查詢依賴 id=N 的查詢，於是先執行 id=N 的查詢。

select_type_primary_subquery_derived_1

當有 union 時，UNION RESULT 的 table 列的值為 <union1,2>，1和2表示參與 union 的 select 行 id。

select_type_simple

4. type

type 列表示關聯型別或訪問型別，即 MySQL 決定如何查詢表中的行，查詢資料行記錄的大概範圍。

依次從最優到最差分別為：system > const > eq_ref > ref > range > index > ALL ，一般來說，得保證查詢達到 range 級別，最好達到 ref 級別。

除了上面幾個擁有優先順序的值之外，NULL 表示 MySQL 能夠在優化階段分解查詢語句，在執行階段用不著再訪問表或索引。例如：在索引列中選取最小值，可以單獨查詢索引來完成，不需要在執行時訪問表。

explain select min(id) from author;

type_null

1）const、system：MySQL 能對查詢的某部分進行優化並將其轉化成一個常量(可以看 show warnings 的結果)。用於 primary key 或 unique key 的所有列與常數比較時，所以表最多有一個匹配行，讀取1次，速度比較快。system是const的特例，表裡只有一條資料匹配時為system

explain select * from (select * from author where id = 1) tmp;

show warnings;

type_const_system

2）eq_ref：primary key 或 unique key 索引的所有部分被連線使用 ，最多隻會返回一條符合條件的記錄。這可能是在 const 之外最好的聯接型別了，簡單的 select 查詢不會出現這種 type。

explain select * from author_blog left join blog on author_blog.blog_id = blog.id;

type_eq_ref

3）ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者聯合索引的部分字首，索引要和某個值相比較，可能會找到多個符合條件的行。

  • . 簡單 select 查詢，title 是普通索引（非唯一索引）
explain select * from blog where title = 'blog1';

type_ref_1

  • 關聯表查詢，idx_author_blog_id 是 author_id 和 blog_id 的聯合索引，這裡使用到了 author_blog 的左邊字首 author_id 部分。
explain select author_id from author left join author_blog on author.id = author_blog.author_id;

type_ref_2

4）range：範圍掃描通常出現在 in，between，>，<，>=，<= 等操作中，使用一個索引來檢索給定範圍的行。

explain select * from author where id > 1;

type_range

5）index：掃描全索引就能拿到結果，一般是掃描某個二級索引或者聯合索引，這種掃描不會從索引樹根節點開始快速查詢，而是直接對二級索引或者聯合索引的葉子節點遍歷和掃描，速度還是比較慢的，這種查詢一般為使用 覆蓋索引(索引覆蓋)，二級索引一般比較小，所以這種通常比 ALL 快一些。

覆蓋索引(索引覆蓋)定義：首先得說明的是覆蓋索引不是一種索引型別，而是二級索引或者聯合索引就包含所需要查詢的所有欄位，不需要再 回表 進行查詢資料行獲取其它欄位值，這種情況一般可以說是用到了覆蓋索引。

當然為啥不需要再回表，相信看過小二 深入理解MySQL索引底層資料結構 這篇文章的都曉得的哈，不知道的小夥伴可以看看哦。

explain select * from blog;

type_index

6）ALL：即全表掃描，掃描你的聚簇索引的所有葉子節點。通常情況下這需要增加索引來進行優化了。

explain select * from author;

type_all

5. possible_keys

possible_keys 列表示查詢 可能使用 哪些索引來查詢，但是最終查詢可能不使用索引。

explain 時可能出現 possible_keys 列有可以使用的索引，但是 key 列顯示 NULL 的情況，這種情況是因為 MySQL 經過查詢成本計算，MySQL 認為索引對此查詢速度不如全表掃描，最終選擇了全表查詢。

如果該列是 NULL，則沒有相關的索引。在這種情況下，可以通過檢查 where 子句看是否可以創造一個適當的索引來提高查詢效能，然後用 explain 檢視效果。

6. key

key 列表示 MySQL 實際使用 哪個索引來對該表進行查詢。

如果沒有使用索引，則該列是 NULL。如果想強制 MySQL 使用或忽視 possible_keys 列中的索引，在查詢中使用 force index (強制走某個索引)、ignore index (強制不走某個索引)。

例如：blog 表強制走 idx_title 索引和強制不走 idx_title 索引。

key

7. key_len

key_ len 列表示 MySQL 在索引裡使用的位元組數，通過這個值可以算出具體使用了索引中的哪些列。

explain select * from author_blog where author_id = 1;

explain select * from author_blog where author_id = 1 and blog_id = 2;

key_len

在 author_blog 表中 idx_author_blog_id 是 author_id 和 blog_id 的聯合索引，author_id 和 blog_id 都為 int 型別，int 型別佔用 4 個位元組。通過 key_len=4 可以推斷出第一條 SQL 使用了 author_id 列，而第二條 SQL 中 key_len=8 可以對推斷出使用了 author_id 列和 blog_id 列。

key_lenth 計算機規則：

  • 字串，char(n) 和 varchar(n)，5.0.3以後版本中，n 均代表字元數，而不是位元組數，如果是 utf-8，一個數字 或字母佔1個位元組，一個漢字佔3個位元組。

    • char(n)：如果存漢字長度就是 3 * n 位元組。
    • varchar(n)：如果存漢字則長度是 3 * n + 2 位元組。因為 varchar 是變長字串，加的2位元組用來儲存字串長度。

  • 數值型別

    • tinyint：1位元組
    • smallint：2位元組
    • int：4位元組
    • bigint：8位元組

  • 時間型別

    • date：3位元組
    • timestamp：4位元組
    • datetime：8位元組

  • 如果欄位允許為 NULL，需要1位元組記錄是否為 NULL。例如當允許 char(n) 為 NULL 時，存漢字長度為 3 * n + 1。

索引最大長度是768位元組，當字串過長時，mysql會做一個類似左字首索引的處理，將前半部分的字元提取出來做索引。

8. ref

ref 列表示在 key 列記錄的索引中，表查詢值所用到的列或常量，常見的有：const(常量)，欄位名(例如film.id)。

9. rows

rows 列表示 MySQL 估計要讀取並檢測的行數，需要注意的是，是估計值，並非最後結果集裡的行數。

10. Extra

Extra 列表示的是一些額外資訊，常見重要值如下：

1）Using index：使用覆蓋索引。

explain select blog_id from author_blog where author_id = 2;

extra_using index

2）Using where：使用 where 語句來處理結果，並且查詢的列未被索引覆蓋。

explain select * from author where name = 'itwxe';

extra_using where

3）Using index condition：查詢的列不完全被索引覆蓋，需要回表查詢。

explain select * from author_blog where author_id > 1;

extra_using index condition

4）Using temporary：MySQL 需要建立一張臨時表來處理查詢。出現這種情況一般是要進行優化的，首先是想到用索引來優化。

  • author.name 沒有索引，此時建立了一張臨時表來 distinct。
explain select distinct name from author;

extra_using temporary

  • blog.title 建立了 idx_title 索引，此時查詢是 extra 是 Using index 而不是 Using temporary。
explain select distinct title from blog;

extra_using temporary

5）Using filesort：將用外部排序而不是索引排序，需要注意的是不要被這個 Using filesort 名字欺騙了，並非出現這個就會使用磁碟排序，而是資料較小時從記憶體排序，否則需要在磁碟排序。這種情況下一般也是要考慮使用索引來優化的。

  • author.name 沒有索引，會查詢整個 author 表，並儲存排序關鍵字和行指標，然後排序關鍵字並按順序檢索行資訊。
explain select * from author order by name;

extra_using filesort

  • blog.title 建立了 idx_title 索引，此時查詢時 extra 是 using index。
explain select * from blog order by title;

extra_using filesort

6）Select tables optimized away：使用某些聚合函式(比如 max、min)來訪問存在索引的某個欄位時出現。

explain select min(id) from author;

type_null

說到這裡 Explain 執行計劃的每個列和常見的值都介紹完了，小夥伴們下期見~~~

都讀到這裡了，來個 點贊、評論、關注、收藏 吧！

相關文章