通常查詢慢查詢SQL語句時會使用explain命令來查看sql語句的執行計劃,通過返回的信息,可以了解到mysql優化器是如何執行SQL語句,通過分析可以幫助我們提供優化的思路。
1. Explain 作用
explain 命令主要用於查看 SQL 語句的執行計劃,該命令可以模擬優化器執行 SQL 查詢語句,可以幫助我們編寫和優化 SQL。那麼 explain 具體可以提供哪些信息,幫助我們如何去優化 SQl 的呢?
- 表的讀取順序
- 數據讀取操作的操作類型
- 哪些索引可以使用
- 哪些索引被實際使用
- 表之間的引用
- 每張表有多少行被優化器查詢
2. Explain 如何使用
使用方式: explain + 待執行的sql
explain 會返回一個待執行 SQL 的執行計劃列表,列表包含了 12 個欄位,欄位共同描述了 SQL 在執行計劃中將會採取何種方式執行。以下列表詳細描述了執行計劃表的欄位含義:
欄位名稱 |
描述 |
id |
執行 select 語句查詢的序列號,決定表的讀取順序 |
select_type |
查詢的類型,也就是數據讀取操作的操作類型 |
table |
查詢的表名 |
partitions |
表分區 |
type |
訪問類型 |
possible_keys |
可使用的索引。查詢涉及到的欄位上若存在索引,則該索引將被列出,但不一定被查詢實際使用到。如果這個欄位為 null 但是欄位 key 不為 null,這種情況就是在查找時沒有可以使用的二級索引樹,但是二級索引中包含了需要查詢的欄位,於是就不再查找聚簇索引(聚簇索引比較大),轉而掃描這個二級索引樹(二級索引樹比較小),並且此時一般訪問類型 type 為 index,及掃描整棵索引樹。 |
key |
實際掃描使用的索引。如果為 null,則沒有使用索引;查詢中若使用了覆蓋索引,則該索引僅出現在key列表中; |
key_len |
索引中使用的字節數。可通過該列計算查詢中使用的索引的長度,在不損失精確性的情況下,長度越短越好;key_len 顯示的值為索引欄位的最大可能長度,並非實際使用長度,即 key_len 是根據表定義計算而得,不是通過表內檢索出的; |
ref |
顯示索引的哪一列被使用了。如果可能的話,是一個常數,哪些列或常量別用於查找索引列上的值; |
rows |
根據表統計信息及索引選用情況,大致估算出找到所需的記錄所需要讀取的行數; |
filtered |
搜索條件過濾後剩餘數據的百分比。 |
Extra |
包含不適合在其它列中顯示但十分重要的額外信息 |
3. 關鍵欄位分析
(1)id
執行 select 語句查詢的序列號,包含一組數字,表示查詢中執行 select 子句或操作表的順序,它有三種情況:
類型名稱 |
描述 |
id相同 |
執行順序由上至下 |
id不同 |
如果是子查詢,id 的序號會遞增,id 值越大優先級越高,越先被執行 |
id相同不同,同時存在 |
如果 id 相同,可以認為是一組,從上往下順序執行,在所有組中,id值越大,優先級越高,越先執行 |
(2)select_type
就是數據讀取操作的操作類型,他一共有以下幾種:
類型名稱 |
描述 |
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,也可以是以下之一:
類型名稱 |
描述 |
<derivedN> |
表示這個是臨時表,後邊的N就是執行計劃中的 id,表示結果來自於這個查詢產生。 |
<unionM,N> |
與<derivedN>類似,也是一個臨時表,表示這個結果來自於 union 查詢的 id 為 M,N 的結果集。 |
<subqueryN> |
該行是指與物化子查詢該行的結果 id 的值 N。 |
(4)partitions
查詢將匹配記錄的分區。該值NULL用於非分區表。
(5)type
依次從好到差:
system>const>eq_ref>ref>ref_or_null>range>index>ALL
除了all之外,其他的type都可以使用到索引,除了index_merge之外,其他的type只可以用到一個索引。
我們自己創建一系列表來實驗下:
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;
eq_ref
唯一性索引掃描,對於每一個索引鍵,表中只有一條記錄與之匹配,常用於主鍵或唯一索引掃描;此類型通常出現在多表的 join 等值查詢,表示對於前表的每一個結果,都只能匹配到後表的一行結果,查詢效率較高。
EXPLAIN SELECT * FROM sku,goods WHERE sku.goods_id=goods.id;
ref
非唯一性索引掃描,返回匹配某個單獨值得所有行,本質上也是一種索引訪問,它返回所有匹配某個單獨值的行,然而,它可能會找到多個符合條件的行,所以它應該屬於查找和掃描的混合體;
EXPLAIN SELECT * FROM sku WHERE goods_id=1;
ref_or_null
二級索引等值比較同時限定 is null 。
EXPLAIN SELECT * FROM sku WHERE name='123456' or name IS NULL;
range
只檢索給定範圍的行,使用一個索引來選擇行,key列顯示使用哪個索引,一般就是在你的 where 語句中出現了 between、<、>、in 等的查詢;這種範圍索引掃描比全表掃描要好,因為它只需要開始於索引的某一個點,結束於另一個點,不用掃描全部索引;
EXPLAIN SELECT * FROM sku WHERE id BETWEEN 1 and 10;
index
index 和 all 區別為 index 類型只遍歷索引樹,這通常比 all 快,因為索引文件通常比數據文件小;也就是說雖然 all 和 index 都是讀寫表,但 index 是從索引中讀取的,而 all 是從硬碟中讀的;
EXPLAIN SELECT barcode FROM sku WHERE deleted=0;
all
也就是全表掃描;
EXPLAIN SELECT * FROM sku WHERE deleted=0;
(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。具體計算規則如下表所示:
值類型 |
值名稱 |
描述 |
字符串 |
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 的, 則不占用。 |
|
(9)ref
如果是使用的常數等值查詢,這裡會顯示const,如果是連接查詢,被驅動表的執行計劃這裡會顯示驅動表的關聯欄位,如果是條件使用了表達式或者函數,或者條件列發生了內部隱式轉換,這裡可能顯示為func。
(10)rows
這裡是執行計劃中估算的掃描行數,不是精確值。
(11)filtered
使用explain extended時會出現這個列,5.7之後的版本默認就有這個欄位,不需要使用explain extended了。這個欄位表示存儲引擎返回的數據在server層過濾後,剩下多少滿足查詢的記錄數量的比例,注意是百分比,不是具體記錄數。
(12)Extra
這個列可以顯示的信息非常多,有幾十種,常用的有:
- distinct:在select部分使用了distinct關鍵字
- no tables used:不帶from字句的查詢或者From dual查詢。使用not in()形式子查詢或not exists()運算符的連接查詢,這種叫做反連接。即,一般連接查詢是先查詢內表,再查詢外表,反連接就是先查詢外表,再查詢內表。
- using filesort:說明mysql會對數據使用一個外部的索引排序,而不是按照表內的索引順序進行讀取。mysql中無法利用索引完成的排序操作稱為「文件排序」。排序時無法使用到索引時,就會出現這個。常見於ORDER BY語句中,需要儘快優化
- using index:查詢時不需要回表查詢,直接通過索引就可以獲取查詢的數據。
- using join buffer(block nested loop),using join buffer(batched key accss) :5.6.x之後的版本優化關聯查詢的BNL,BKA特性。主要是減少內表的循環數量以及比較順序地掃描查詢。
- using sort_union,using_union,using intersect,using sort_intersection:
- using intersect:表示使用and的各個索引的條件時,該信息表示是從處理結果獲取交集
- using union:表示使用or連接各個使用索引的條件時,該信息表示從處理結果獲取並集
- using sort_union和using sort_intersection:與前面兩個對應的類似,只是他們是出現在用and和or查詢信息量大時,先查詢主鍵,然後進行排序合併後,才能讀取記錄並返回。
- using temporary:表示使用了臨時表存儲中間結果。臨時表可以是內存臨時表和磁碟臨時表,執行計劃中看不出來,需要查看status變量,used_tmp_table,used_tmp_disk_table才能看出來。常見於order by和分組查詢group by。group by一定要遵循所建索引的順序與個數。需要儘快優化
- using where:表示存儲引擎返回的記錄並不是所有的都滿足查詢條件,需要在server層進行過濾。查詢條件中分為限制條件和檢查條件,5.6之前,存儲引擎只能根據限制條件掃描數據並返回,然後server層根據檢查條件進行過濾再返回真正符合查詢的數據。5.6.x之後支持ICP特性(index condition pushdown,索引下推),可以把檢查條件也下推到存儲引擎層,不符合檢查條件和限制條件的數據,直接不讀取,這樣就大大減少了存儲引擎掃描的記錄數量。extra列顯示using index condition
- firstmatch(tb_name) :5.6.x開始引入的優化子查詢的新特性之一,常見於where字句含有in()類型的子查詢。如果內表的數據量比較大,就可能出現這個
- loosescan(m..n) :5.6.x之後引入的優化子查詢的新特性之一,在in()類型的子查詢中,子查詢返回的可能有重複記錄時,就可能出現這個
4. Explain 主要關注點
總的來說,我們只需要關注結果中的幾列:
列名 |
備註 |
type |
本次查詢表聯接類型,從這裡可以看到本次查詢大概的效率 |
key |
最終選擇的索引,如果沒有索引的話,本次查詢效率通常很差 |
key_len |
本次查詢用於結果過濾的索引實際長度 |
rows |
預計需要掃描的記錄數,預計需要掃描的記錄數越小越好 |
Extra |
額外附加信息,主要確認是否出現Using filesort、Using temporary這兩種情況 |
再來看下Extra列中需要注意出現的幾種情況:
關鍵字 |
備註 |
Using filesort |
將用外部排序而不是按照索引順序排列結果,數據較少時從內存排序,否則需要在磁碟完成排序,代價非常高,需要添加合適的索引 |
Using temporary |
需要創建一個臨時表來存儲結果,這通常發生在對沒有索引的列進行GROUP BY時,或者ORDER BY里的列不都在索引里,需要添加合適的索引 |
Using index |
表示MySQL使用覆蓋索引避免全表掃描,不需要再到表中進行二次查找數據,這是比較好的結果之一。注意不要和type中的index類型混淆 |
Using where |
通常是進行了全表/全索引掃描後再用WHERE子句完成結果過濾,需要添加合適的索引 |
Impossible WHERE |
對Where子句判斷的結果總是false而不能選擇任何數據,例如where 1=0,無需過多關注 |
Select tables optimized away |
使用某些聚合函數來訪問存在索引的某個欄位時,優化器會通過索引直接一次定位到所需要的數據行完成整個查詢,例如MIN()\MAX(),這種也是比較好的結果之一 |