首頁  >  文章  >  資料庫  >  一文帶你快速看懂 MySQL 執行計劃

一文帶你快速看懂 MySQL 執行計劃

青灯夜游
青灯夜游轉載
2022-10-21 20:45:031535瀏覽

一文帶你快速看懂 MySQL 執行計劃

通常查詢慢查詢SQL語句時會使用EXPLAIN指令來查看SQL語句的執行計劃,透過傳回的訊息,可以了解到Mysql優化器是如何執行SQL語句,透過分析可以幫助我們提供最佳化的思路。

1. Explain 作用

explain 指令主要用於檢視SQL 語句的執行計劃,該指令可以模擬最佳化器執行SQL 查詢語句,可以幫助我們編寫和優化SQL。那麼 explain 具體可以提供哪些訊息,幫助我們如何去優化 SQl 的呢?

  • 表格的讀取順序

  • 資料讀取操作的運算類型

  • 哪些索引可以使用

  • 哪些索引被實際使用

  • #表之間的引用

  • 每張表格有多少行被最佳化器查詢

2. Explain 如何使用

使用方式: explain 待執行的sql

一文帶你快速看懂 MySQL 執行計劃

  explain 會傳回一個待執行SQL 的執行計劃列表,列表包含了12 個字段,字段共同描述了SQL 在執行計劃中將會採取何種方式執行。以下清單詳細描述了執行計劃表的欄位意義:

##partitions表分區type存取類型#possible_keyskeykey_lenref#rowsfilteredExtra
欄位名稱 描述
#id 執行select 語句查詢的序號,決定表的讀取順序
select_type 查詢的類型,也就是資料讀取操作的操作類型
table 查詢的表名
##可使用的索引。查詢所涉及的欄位上若存在索引,則該索引將被列出,但不一定被查詢實際使用到。如果這個字段為null 但是字段key 不為null,這種情況就是在查找時沒有可以使用的二級索引樹,但是二級索引中包含了需要查詢的字段,於是就不再查找聚簇索引(聚簇索引比較大),轉而掃描這個二級索引樹(二級索引樹比較小),並且此時一般訪問類型type 為index,及掃描整棵索引樹。
實際掃描使用的索引。如果為null,則沒有使用索引;查詢中若使用了覆蓋索引,則該索引僅出現在key清單中;
#索引中使用的位元組數。可透過此列計算查詢中使用的索引的長度,在不損失精確性的情況下,長度越短越好;key_len 顯示的值為索引欄位的最大可能長度,並非實際使用長度,即key_len 是根據表定義計算而得,不是透過表格內檢索出的;
#顯示索引的哪一列被使用了。如果可能的話,是一個常數,哪些列或常數別用於查找索引列上的值;
根據表統計資訊及索引選用情況,大致估算出找到所需的記錄所需讀取的行數;
搜尋條件過濾後剩餘資料的百分比。
包含不適合在其它列中顯示但十分重要的額外資訊
3. 關鍵字段分析

(1)id

#執行select 語句查詢的序號,包含一組數字,表示查詢中執行select子句或操作表的順序,它有三種情況:

類型名稱id相同#id不同id相同不同,同時存在#

(2)select_type

就是資料讀取運算的運算類型,他一共有以下幾種:

描述
執行順序由上到下
如果是子查詢,id 的序號會遞增, id 值越大優先順序越高,越先被執行
如果id 相同,可以認為是一組,從上往下順序執行,在所有群組中,id值越大,優先權越高,越先執行
類型名稱 描述
simple 簡單的select 查詢,查詢中不包含子查詢或union;
primary 查詢中若包含任何複雜的子查詢,最外層查詢則被標記;
subquery 在select 或where 清單中包含了子查詢;
#dependent subquery 子查詢中的第一個SELECT, 取決於外面的查詢。即子查詢依賴外層查詢的結果。
derived 在from 清單中包含的子查詢被標記為DERIVED(衍生表),mysql 會遞歸執行這些子查詢,把結果放到臨時表中;
union 若第二個select 出現在union 之後,則被標記為union,若union 包含在from 子句的子查詢中,外層select 將被標記為DERIVED;
union result 從union 表(即union 合併的結果集)中取得select 查詢的結果;
meterialized 物化表,當子查詢關聯查詢時,子查詢結果儲存在物化暫存表,然後根據暫存表中的資料去主表匹配。
dependent union UNION 中的第二個或後面的查詢語句,取決於外面的查詢

(3)table

顯示的查詢表名,如果查詢使用了別名,那麼這裡顯示的是別名,如果不涉及對資料表的操作,那麼這顯示為null,也可以是以下之一:

NN#>N
#類型名稱 #描述
>表示這個是臨時表,後邊的N就是執行計畫中的id,表示結果來自於這個查詢產生。 M,
N> ;類似,也是臨時表,表示這個結果來自於union 查詢的id 為M,N 的結果集。
>######該行是指與物化子查詢該行的結果 id 的值 N。 ############

(4)partitions

查詢將符合記錄的分割區。該值NULL用於非分割表。

(5)type

依序從好到差:

system>const >eq_ref>ref>ref_or_null>range>#index> ALL

除了all之外,其他的type都可以使用到索引,除了index_merge之外,其他的type只可以用到一個索引。

我們自己建立一系列表格來實驗下:

一文帶你快速看懂 MySQL 執行計劃

一文帶你快速看懂 MySQL 執行計劃

#
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for goods
-- ----------------------------
DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods`  (
  `id` int(11) NOT NULL,
  `sn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of goods
-- ----------------------------
INSERT INTO `goods` VALUES (1, 'sn123456', '衣服');

-- ----------------------------
-- Table structure for sku
-- ----------------------------
DROP TABLE IF EXISTS `sku`;
CREATE TABLE `sku`  (
  `id` int(11) NOT NULL,
  `goods_id` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  `deleted` int(11) NOT NULL,
  `barcode` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `index_2`(`name`) USING BTREE,
  INDEX `index_1`(`goods_id`, `status`, `deleted`, `barcode`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sku
-- ----------------------------
INSERT INTO `sku` VALUES (1, 1, 1, 0, 'kt123456', '黑色');

SET FOREIGN_KEY_CHECKS = 1;

##system

#表格只有一行記錄(等於系統表),這是const 類型的特例,平時不會出現,這個也可忽略不計;

const

表示透過索引一次就找到了,const 用來比較primary key 或unique 索引。因為只匹配一行記錄,所以很快。如果將主鍵置於where 清單中,mysql 就能將該查詢轉換成一個常數;

EXPLAIN SELECT * FROM sku WHERE id=1;复制代码

一文帶你快速看懂 MySQL 執行計劃

##eq_ref

#唯一性索引掃描,對於每一個索引鍵,表中只有一筆記錄與之匹配,常用於主鍵或唯一索引掃描;此類型通常出現在多表的join 等值查詢,表示對於前表的每一個結果,都只能配對到後表的一行結果,查詢效率較高。

EXPLAIN SELECT * FROM sku,goods WHERE sku.goods_id=goods.id;

一文帶你快速看懂 MySQL 執行計劃

ref

非唯一性索引掃描,傳回符合某個單獨值得所有行,本質上也是一種索引訪問,它返回所有匹配某個單獨值的行,然而,它可能會找到多個符合條件的行,所以它應該屬於查找和掃描的混合體;

EXPLAIN SELECT * FROM sku WHERE goods_id=1;

一文帶你快速看懂 MySQL 執行計劃

#ref_or_null

二級索引等值比較同時限定is null 。

EXPLAIN SELECT * FROM sku WHERE name='123456' or name IS NULL;

一文帶你快速看懂 MySQL 執行計劃

range

只檢索給定範圍的行,使用一個索引來選擇行,key列顯示使用哪個索引,一般就是在你的where 語句中出現了between、、in 等的查詢;這種範圍索引掃描比全表掃描要好,因為它只需要開始於索引的某一個點,結束於另一個點,不用掃描全部索引;

EXPLAIN SELECT * FROM sku WHERE id BETWEEN 1 and 10;

一文帶你快速看懂 MySQL 執行計劃

index

#index 和all 區別為index 類型只遍歷索引樹,這通常比all 快,因為索引檔案通常比資料檔案小;也就是說雖然all 和index 都是讀寫表,但index 是從索引中讀取的,而all 是從硬碟中讀的;

EXPLAIN SELECT barcode FROM sku WHERE deleted=0;

一文帶你快速看懂 MySQL 執行計劃

all

也就是全表掃描;

EXPLAIN SELECT * FROM sku WHERE deleted=0;

一文帶你快速看懂 MySQL 執行計劃

(6)possible_keys

查詢可能使用到的索引都會在這裡列出來。

(7)key

當查詢真正使用到的索引,

select_type

index_merge時,這裡可能出現兩個以上的索引,其他的select_type這裡只會出現一個。

(8)key_len

key_len 表示該列計算查詢中所使用的索引的長度。例如:

SELECT * FROM table where age = 1 and name like 'xx'

,假設 age 是 int 類型且不可為 null;name 是 varchar(20) 類型且可以為 null,編碼為 utf8。若以這兩個欄位為索引查詢,那麼 key_len 的值為 4 3 * 20 2 1 = 67。具體計算規則如下表所示:<table> <thead><tr class="firstRow"> <th>值類型</th> <th>值名稱</th> <th>描述</th> </tr></thead> <tbody> <tr> <td>字串</td> <td>CHAR(n)</td> <td>n 位元組長度</td> </tr> <tr> <td><br></td> <td>VARCHAR(n)</td> <td>如果是utf8 編碼,則是3 n 2位元組;;如果是utf8mb4 編碼,則是4 n 2 位元組。 </td> </tr> <tr> <td>數值類型</td> <td>TINYINT</td> <td>1位元組</td> </tr> <tr> <td><br></td> <td></td> <td></td>1位元組</tr> <tr> <td><br></td> <td></td>SMALLINT<td></td>2位元組</tr> <tr> <td><br></td> <td></td>#MEDIUMINT<td></td>3位元組</tr> <tr> <td><br></td> <td>##INT</td> <td>4位元組</td> </tr> <tr> <td></td> <td></td>BIGINT<td></td>8位元組</tr> <tr> <td> <br>時間類型</td> <td>DATE</td> <td>#3位元組</td> </tr> <tr> <td><br></td> <td>TIMESTAMP</td> <td>4位元組</td> </tr> <tr> <td></td> <td></td>#DATETIME<td> <br>#8位元組</td> </tr> </tbody> </table>欄位屬性######NULL 屬性佔用一個位元組。如果一個欄位是 NOT NULL 的, 則不佔用。 #####################<h3 data-id="heading-19"><strong>(9)ref</strong></h3> <p>如果是使用的常數等值查詢,這裡會顯示<code>const,如果是連接查詢,被驅動表的執行計劃這裡會顯示驅動表的關聯字段,如果是條件使用了表達式或函數,或者條件列發生了內部隱式轉換,這裡可能顯示為func

(10)rows

這裡是執行計畫中估算的掃描行數,不是精確值。

(11)filtered

使用explain extended時會出現這個列,5.7之後的版本預設就有這個字段,不需要使用explain extended了。這個欄位表示儲存引擎傳回的資料在server層過濾後,剩下多少滿足查詢的記錄數量的比例,注意是百分比,不是具體記錄數。

(12)Extra

這個欄位可以顯示的資訊非常多,有數十種,常用的有:

1 、distinct:在select部分使用了distinct關鍵字

2、no tables used:不帶from 字句的查詢或From dual查詢。使用not in()形式子查詢或not exists()運算子的連接查詢,這種叫做反連接。即,一般連接查詢是先查詢內表,再查詢外表,反連接就是先查詢外表,再查詢內表。

3、using filesort:說明mysql會對資料使用一個外部的索引排序,而不是依照表內的索引順序讀取。 mysql中無法利用索引完成的排序操作稱為「檔案排序」。排序時無法使用到索引時,就會出現這個。常見於order by語句中,需要盡快優化

4、using index:查詢時不需要回表查詢,直接透過索引就可以取得查詢的數據。

5、using join buffer(block nested loop),using join buffer(batched key accss)5.6.x之後的版本最佳化關聯查詢的 BNLBKA特性。主要是減少內表的循環數量以及比較順序地掃描查詢。

6、using sort_union,using_union,using intersect,using sort_intersection:

  • using intersect:表示使用and的各個索引的條件時,該資訊表示是從處理結果取得交集
  • using union:表示使用or連接各個使用索引的條件時,該資訊表示從處理結果取得並集
  • using sort_union和using sort_intersection:與前面兩個對應的類似,只是他們是出現在用andor查詢資訊量大時,先查詢主鍵,然後進行排序合併後,才能讀取記錄並回傳。

7、using temporary:表示使用了暫存表儲存中間結果。臨時表可以是記憶體臨時表和磁碟臨時表,執行計劃中看不出來,需要查看status變量,used_tmp_tableused_tmp_disk_table才能看出來。常見於order by和分組查詢group bygroup by一定要遵循所建索引的順序與個數。需要盡快最佳化

8、using where:表示儲存引擎傳回的記錄並不是所有的都符合查詢條件,需要在server層進行篩選。查詢條件中分為限制條件和檢查條件,5.6之前,儲存引擎只能根據限制條件掃描資料並返回,然後server層根據檢查條件進行過濾再返回真正符合查詢的資料。 5.6.x之後支援ICP特性(index condition pushdown,索引下推),可以把檢查條件也下推到儲存引擎層,不符合檢查條件和限制條件的數據,直接不讀取,這樣就大大減少了儲存引擎掃描的記錄數量。 extra列顯示using index condition

#9、firstmatch(tb_name)5.6.x#開始引入的最佳化子查詢的新特性之一,常見於where字句含有in()類型的子查詢。如果內表的資料量比較大,就可能出現這個

10、loosescan(m..n)5.6.x之後引進的最佳化子查詢的新特性之一,在in()類型的子查詢中,當子查詢傳回的可能有重複記錄時,就可能出現這個

4. Explain 主要關注點

總的來說,我們只需要專注在結果中的幾列:

列名 備註
type 本次查詢表聯結類型,從這裡可以看到本次查詢大概的效率
key 最終選擇的索引,如果沒有索引的話,本次查詢效率通常很差
key_len 本次查詢用於結果篩選的索引實際長度
rows 預計需要掃描的記錄數,預計需要掃描的記錄數越小越好
#Extra 額外附加資訊,主要確認是否出現Using filesortUsing temporary這兩種情況

再來看下Extra列中需要注意出現的幾種情況:

##對##Select tables optimized awayMIN()\MAX ()【相關推薦:
關鍵字 備註
Using filesort 將用外部排序而不是按照索引順序排列結果,資料較少時從記憶體排序,否則需要在磁碟完成排序,代價非常高,需要添加合適的索引
#Using temporary 需要建立一個暫存表來儲存結果,這通常發生在對沒有索引的資料列進行GROUP BY時,或 ORDER BY裡的列不都在索引裡,需要加入適當的索引
Using index 表示MySQL 使用覆蓋索引避免全表掃描,不需要再到表中進行二次查找數據,這是比較好的結果之一。注意不要和type中的index類型混淆
#Using where 通常是進行了全表/全索引掃描後再用WHERE子句完成結果過濾,需要加入適當的索引
Impossible WHEREWhere子句判斷的結果總是false而不能選擇任何數據,例如where 1=0,無須過度關注
使用某些聚合函數來存取存在索引的某個欄位時,最佳化器會透過索引直接一次定位到所需的資料行完成整個查詢,例如,這種也是比較好的結果之一
mysql影片教學

以上是一文帶你快速看懂 MySQL 執行計劃的詳細內容。更多資訊請關注PHP中文網其他相關文章!

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