站長(zhǎng)資訊網(wǎng)
最全最豐富的資訊網(wǎng)站

一文帶你快速看懂 MySQL 執(zhí)行計(jì)劃

一文帶你快速看懂 MySQL 執(zhí)行計(jì)劃

程序員必備接口測(cè)試調(diào)試工具:立即使用
Apipost = Postman + Swagger + Mock + Jmeter
Api設(shè)計(jì)、調(diào)試、文檔、自動(dòng)化測(cè)試工具
后端、前端、測(cè)試,同時(shí)在線協(xié)作,內(nèi)容實(shí)時(shí)同步

通常查詢慢查詢SQL語(yǔ)句時(shí)會(huì)使用EXPLAIN命令來(lái)查看SQL語(yǔ)句的執(zhí)行計(jì)劃,通過(guò)返回的信息,可以了解到Mysql優(yōu)化器是如何執(zhí)行SQL語(yǔ)句,通過(guò)分析可以幫助我們提供優(yōu)化的思路。

1. Explain 作用

explain 命令主要用于查看 SQL 語(yǔ)句的執(zhí)行計(jì)劃,該命令可以模擬優(yōu)化器執(zhí)行 SQL 查詢語(yǔ)句,可以幫助我們編寫(xiě)和優(yōu)化 SQL。那么 explain 具體可以提供哪些信息,幫助我們?nèi)绾稳?yōu)化 SQl 的呢?

  • 表的讀取順序

  • 數(shù)據(jù)讀取操作的操作類型

  • 哪些索引可以使用

  • 哪些索引被實(shí)際使用

  • 表之間的引用

  • 每張表有多少行被優(yōu)化器查詢

2. Explain 如何使用

使用方式: explain + 待執(zhí)行的sql

一文帶你快速看懂 MySQL 執(zhí)行計(jì)劃

  explain 會(huì)返回一個(gè)待執(zhí)行 SQL 的執(zhí)行計(jì)劃列表,列表包含了 12 個(gè)字段,字段共同描述了 SQL 在執(zhí)行計(jì)劃中將會(huì)采取何種方式執(zhí)行。以下列表詳細(xì)描述了執(zhí)行計(jì)劃表的字段含義:

字段名稱 描述
id 執(zhí)行 select 語(yǔ)句查詢的序列號(hào),決定表的讀取順序
select_type 查詢的類型,也就是數(shù)據(jù)讀取操作的操作類型
table 查詢的表名
partitions 表分區(qū)
type 訪問(wèn)類型
possible_keys 可使用的索引。查詢涉及到的字段上若存在索引,則該索引將被列出,但不一定被查詢實(shí)際使用到。如果這個(gè)字段為 null 但是字段 key 不為 null,這種情況就是在查找時(shí)沒(méi)有可以使用的二級(jí)索引樹(shù),但是二級(jí)索引中包含了需要查詢的字段,于是就不再查找聚簇索引(聚簇索引比較大),轉(zhuǎn)而掃描這個(gè)二級(jí)索引樹(shù)(二級(jí)索引樹(shù)比較?。?,并且此時(shí)一般訪問(wèn)類型 type 為 index,及掃描整棵索引樹(shù)。
key 實(shí)際掃描使用的索引。如果為 null,則沒(méi)有使用索引;查詢中若使用了覆蓋索引,則該索引僅出現(xiàn)在key列表中;
key_len 索引中使用的字節(jié)數(shù)??赏ㄟ^(guò)該列計(jì)算查詢中使用的索引的長(zhǎng)度,在不損失精確性的情況下,長(zhǎng)度越短越好;key_len 顯示的值為索引字段的最大可能長(zhǎng)度,并非實(shí)際使用長(zhǎng)度,即 key_len 是根據(jù)表定義計(jì)算而得,不是通過(guò)表內(nèi)檢索出的;
ref 顯示索引的哪一列被使用了。如果可能的話,是一個(gè)常數(shù),哪些列或常量別用于查找索引列上的值;
rows 根據(jù)表統(tǒng)計(jì)信息及索引選用情況,大致估算出找到所需的記錄所需要讀取的行數(shù);
filtered 搜索條件過(guò)濾后剩余數(shù)據(jù)的百分比。
Extra 包含不適合在其它列中顯示但十分重要的額外信息

3. 關(guān)鍵字段分析

(1)id

執(zhí)行 select 語(yǔ)句查詢的序列號(hào),包含一組數(shù)字,表示查詢中執(zhí)行 select 子句或操作表的順序,它有三種情況:

類型名稱 描述
id相同 執(zhí)行順序由上至下
id不同 如果是子查詢,id 的序號(hào)會(huì)遞增,id 值越大優(yōu)先級(jí)越高,越先被執(zhí)行
id相同不同,同時(shí)存在 如果 id 相同,可以認(rèn)為是一組,從上往下順序執(zhí)行,在所有組中,id值越大,優(yōu)先級(jí)越高,越先執(zhí)行

(2)select_type

就是數(shù)據(jù)讀取操作的操作類型,他一共有以下幾種:

類型名稱 描述
simple 簡(jiǎn)單的 select 查詢,查詢中不包含子查詢或者 union;
primary 查詢中若包含任何復(fù)雜的子查詢,最外層查詢則被標(biāo)記;
subquery 在 select 或者 where 列表中包含了子查詢;
dependent subquery 子查詢中的第一個(gè) SELECT, 取決于外面的查詢。 即子查詢依賴于外層查詢的結(jié)果。
derived 在 from 列表中包含的子查詢被標(biāo)記為 DERIVED(衍生表),mysql 會(huì)遞歸執(zhí)行這些子查詢,把結(jié)果放臨時(shí)表中;
union 若第二個(gè) select 出現(xiàn)在 union 之后,則被標(biāo)記為 union,若 union 包含在 from 子句的子查詢中,外層 select 將被標(biāo)記為 DERIVED;
union result 從 union 表(即 union 合并的結(jié)果集)中獲取 select 查詢的結(jié)果;
meterialized 物化表,子查詢關(guān)聯(lián)查詢時(shí),子查詢結(jié)果存儲(chǔ)在物化臨時(shí)表,然后根據(jù)臨時(shí)表中的數(shù)據(jù)去主表匹配。
dependent union UNION 中的第二個(gè)或后面的查詢語(yǔ)句,取決于外面的查詢

(3)table

顯示的查詢表名,如果查詢使用了別名,那么這里顯示的是別名,如果不涉及對(duì)數(shù)據(jù)表的操作,那么這顯示為 null,也可以是以下之一:

類型名稱 描述
<derivedN> 表示這個(gè)是臨時(shí)表,后邊的N就是執(zhí)行計(jì)劃中的 id,表示結(jié)果來(lái)自于這個(gè)查詢產(chǎn)生。
<unionM,N> 與<derivedN>類似,也是一個(gè)臨時(shí)表,表示這個(gè)結(jié)果來(lái)自于 union 查詢的 id 為 M,N 的結(jié)果集。
<subqueryN> 該行是指與物化子查詢?cè)撔械慕Y(jié)果 id 的值 N。

(4)partitions

查詢將匹配記錄的分區(qū)。該值NULL用于非分區(qū)表。

(5)type

依次從好到差:

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

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

我們自己創(chuàng)建一系列表來(lái)實(shí)驗(yàn)下:

一文帶你快速看懂 MySQL 執(zhí)行計(jì)劃

一文帶你快速看懂 MySQL 執(zhí)行計(jì)劃

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;
登錄后復(fù)制

system

表只有一行記錄(等于系統(tǒng)表),這是 const 類型的特例,平時(shí)不會(huì)出現(xiàn),這個(gè)也可忽略不計(jì);

const

表示通過(guò)索引一次就找到了,const 用于比較 primary key 或者 unique 索引。因?yàn)橹黄ヅ湟恍杏涗?,所以很快?如果將主鍵置于 where 列表中,mysql 就能將該查詢轉(zhuǎn)換成一個(gè)常量;

EXPLAIN SELECT * FROM sku WHERE id=1;復(fù)制代碼
登錄后復(fù)制

一文帶你快速看懂 MySQL 執(zhí)行計(jì)劃

eq_ref

唯一性索引掃描,對(duì)于每一個(gè)索引鍵,表中只有一條記錄與之匹配,常用于主鍵或唯一索引掃描;此類型通常出現(xiàn)在多表的 join 等值查詢,表示對(duì)于前表的每一個(gè)結(jié)果,都只能匹配到后表的一行結(jié)果,查詢效率較高。

EXPLAIN SELECT * FROM sku,goods WHERE sku.goods_id=goods.id;
登錄后復(fù)制

一文帶你快速看懂 MySQL 執(zhí)行計(jì)劃

ref

非唯一性索引掃描,返回匹配某個(gè)單獨(dú)值得所有行,本質(zhì)上也是一種索引訪問(wèn),它返回所有匹配某個(gè)單獨(dú)值的行,然而,它可能會(huì)找到多個(gè)符合條件的行,所以它應(yīng)該屬于查找和掃描的混合體;

EXPLAIN SELECT * FROM sku WHERE goods_id=1;
登錄后復(fù)制

一文帶你快速看懂 MySQL 執(zhí)行計(jì)劃

ref_or_null

二級(jí)索引等值比較同時(shí)限定 is null 。

EXPLAIN SELECT * FROM sku WHERE name='123456' or name IS NULL;
登錄后復(fù)制

一文帶你快速看懂 MySQL 執(zhí)行計(jì)劃

range

只檢索給定范圍的行,使用一個(gè)索引來(lái)選擇行,key列顯示使用哪個(gè)索引,一般就是在你的 where 語(yǔ)句中出現(xiàn)了 between、<、>、in 等的查詢;這種范圍索引掃描比全表掃描要好,因?yàn)樗恍枰_(kāi)始于索引的某一個(gè)點(diǎn),結(jié)束于另一個(gè)點(diǎn),不用掃描全部索引;

EXPLAIN SELECT * FROM sku WHERE id BETWEEN 1 and 10;
登錄后復(fù)制

一文帶你快速看懂 MySQL 執(zhí)行計(jì)劃

index

index 和 all 區(qū)別為 index 類型只遍歷索引樹(shù),這通常比 all 快,因?yàn)樗饕募ǔ1葦?shù)據(jù)文件?。灰簿褪钦f(shuō)雖然 all 和 index 都是讀寫(xiě)表,但 index 是從索引中讀取的,而 all 是從硬盤(pán)中讀的;

EXPLAIN SELECT barcode FROM sku WHERE deleted=0;
登錄后復(fù)制

一文帶你快速看懂 MySQL 執(zhí)行計(jì)劃

all

也就是全表掃描;

EXPLAIN SELECT * FROM sku WHERE deleted=0;
登錄后復(fù)制

一文帶你快速看懂 MySQL 執(zhí)行計(jì)劃

(6)possible_keys

查詢可能使用到的索引都會(huì)在這里列出來(lái)。

(7)key

查詢真正使用到的索引,select_typeindex_merge時(shí),這里可能出現(xiàn)兩個(gè)以上的索引,其他的select_type這里只會(huì)出現(xiàn)一個(gè)。

(8)key_len

key_len 表示該列計(jì)算查詢中使用的索引的長(zhǎng)度。例如:SELECT * FROM table where age = 1 and name like 'xx',假設(shè) age 是 int 類型且不可為 null;name 是 varchar(20) 類型且可以為 null,編碼為 utf8。若以這兩個(gè)字段為索引查詢,那么 key_len 的值為 4 + 3 * 20 + 2 + 1 = 67。具體計(jì)算規(guī)則如下表所示:

值類型 值名稱 描述
字符串 CHAR(n) n 字節(jié)長(zhǎng)度
VARCHAR(n) 如果是 utf8 編碼,則是 3 n + 2字節(jié);;如果是 utf8mb4 編碼,則是 4 n + 2 字節(jié)。
數(shù)值類型 TINYINT 1字節(jié)
SMALLINT 2字節(jié)
MEDIUMINT 3字節(jié)
INT 4字節(jié)
BIGINT 8字節(jié)
時(shí)間類型 DATE 3字節(jié)
TIMESTAMP 4字節(jié)
DATETIME 8字節(jié)
字段屬性 NULL 屬性 占用一個(gè)字節(jié)。如果一個(gè)字段是 NOT NULL 的, 則不占用。

(9)ref

如果是使用的常數(shù)等值查詢,這里會(huì)顯示const,如果是連接查詢,被驅(qū)動(dòng)表的執(zhí)行計(jì)劃這里會(huì)顯示驅(qū)動(dòng)表的關(guān)聯(lián)字段,如果是條件使用了表達(dá)式或者函數(shù),或者條件列發(fā)生了內(nèi)部隱式轉(zhuǎn)換,這里可能顯示為func

(10)rows

這里是執(zhí)行計(jì)劃中估算的掃描行數(shù),不是精確值。

(11)filtered

使用explain extended時(shí)會(huì)出現(xiàn)這個(gè)列,5.7之后的版本默認(rèn)就有這個(gè)字段,不需要使用explain extended了。這個(gè)字段表示存儲(chǔ)引擎返回的數(shù)據(jù)在server層過(guò)濾后,剩下多少滿足查詢的記錄數(shù)量的比例,注意是百分比,不是具體記錄數(shù)。

(12)Extra

這個(gè)列可以顯示的信息非常多,有幾十種,常用的有:

1、distinct:在select部分使用了distinct關(guān)鍵字

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

3、using filesort:說(shuō)明mysql會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序,而不是按照表內(nèi)的索引順序進(jìn)行讀取。mysql中無(wú)法利用索引完成的排序操作稱為“文件排序”。排序時(shí)無(wú)法使用到索引時(shí),就會(huì)出現(xiàn)這個(gè)。常見(jiàn)于order by語(yǔ)句中,需要盡快優(yōu)化

4、using index:查詢時(shí)不需要回表查詢,直接通過(guò)索引就可以獲取查詢的數(shù)據(jù)。

5、using join buffer(block nested loop),using join buffer(batched key accss)5.6.x之后的版本優(yōu)化關(guān)聯(lián)查詢的BNLBKA特性。主要是減少內(nèi)表的循環(huán)數(shù)量以及比較順序地掃描查詢。

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

  • using intersect:表示使用and的各個(gè)索引的條件時(shí),該信息表示是從處理結(jié)果獲取交集
  • using union:表示使用or連接各個(gè)使用索引的條件時(shí),該信息表示從處理結(jié)果獲取并集
  • using sort_union和using sort_intersection:與前面兩個(gè)對(duì)應(yīng)的類似,只是他們是出現(xiàn)在用andor查詢信息量大時(shí),先查詢主鍵,然后進(jìn)行排序合并后,才能讀取記錄并返回。

7、using temporary:表示使用了臨時(shí)表存儲(chǔ)中間結(jié)果。臨時(shí)表可以是內(nèi)存臨時(shí)表和磁盤(pán)臨時(shí)表,執(zhí)行計(jì)劃中看不出來(lái),需要查看status變量,used_tmp_table,used_tmp_disk_table才能看出來(lái)。常見(jiàn)于order by和分組查詢group by。group by一定要遵循所建索引的順序與個(gè)數(shù)。需要盡快優(yōu)化

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

9、firstmatch(tb_name)5.6.x開(kāi)始引入的優(yōu)化子查詢的新特性之一,常見(jiàn)于where字句含有in()類型的子查詢。如果內(nèi)表的數(shù)據(jù)量比較大,就可能出現(xiàn)這個(gè)

10、loosescan(m..n)5.6.x之后引入的優(yōu)化子查詢的新特性之一,在in()類型的子查詢中,子查詢返回的可能有重復(fù)記錄時(shí),就可能出現(xiàn)這個(gè)

4. Explain 主要關(guān)注點(diǎn)

總的來(lái)說(shuō),我們只需要關(guān)注結(jié)果中的幾列:

列名 備注
type 本次查詢表聯(lián)接類型,從這里可以看到本次查詢大概的效率
key 最終選擇的索引,如果沒(méi)有索引的話,本次查詢效率通常很差
key_len 本次查詢用于結(jié)果過(guò)濾的索引實(shí)際長(zhǎng)度
rows 預(yù)計(jì)需要掃描的記錄數(shù),預(yù)計(jì)需要掃描的記錄數(shù)越小越好
Extra 額外附加信息,主要確認(rèn)是否出現(xiàn)Using filesort、Using temporary這兩種情況

再來(lái)看下Extra列中需要注意出現(xiàn)的幾種情況:

關(guān)鍵字 備注
Using filesort 將用外部排序而不是按照索引順序排列結(jié)果,數(shù)據(jù)較少時(shí)從內(nèi)存排序,否則需要在磁盤(pán)完成排序,代價(jià)非常高,需要添加合適的索引
Using temporary 需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)存儲(chǔ)結(jié)果,這通常發(fā)生在對(duì)沒(méi)有索引的列進(jìn)行GROUP BY時(shí),或者ORDER BY里的列不都在索引里,需要添加合適的索引
Using index 表示MySQL使用覆蓋索引避免全表掃描,不需要再到表中進(jìn)行二次查找數(shù)據(jù),這是比較好的結(jié)果之一。注意不要和type中的index類型混淆
Using where 通常是進(jìn)行了全表/全索引掃描后再用WHERE子句完成結(jié)果過(guò)濾,需要添加合適的索引
Impossible WHERE 對(duì)Where子句判斷的結(jié)果總是false而不能選擇任何數(shù)據(jù),例如where 1=0,無(wú)需過(guò)多關(guān)注
Select tables optimized away 使用某些聚合函數(shù)來(lái)訪問(wèn)存在索引的某個(gè)字段時(shí),優(yōu)化器會(huì)通過(guò)索引直接一次定位到所需要的數(shù)據(jù)行完成整個(gè)查詢,例如MIN()MAX(),這種也是比較好的結(jié)果之一

贊(0)
分享到: 更多 (0)
網(wǎng)站地圖   滬ICP備18035694號(hào)-2    滬公網(wǎng)安備31011702889846號(hào)
久久精品www人人爽人人| 思思99re66在线精品免费观看| 久久亚洲日韩看片无码| 久久亚洲精品11p| 无码精品人妻一区二区三区人妻斩| 亚洲日韩亚洲另类激情文学| 国产精品白浆在线观看无码专区| 亚洲精品电影在线| 无码日韩人妻精品久久蜜桃| 久久91精品国产91久久户| CHINESE中国精品自拍| 国产精品秘入口福利姬网站| 日本免费精品一区二区三区| 国产精品视频一区| 一区二区三区精品| 久久这里只有精品66| 91麻豆国产福利精品 | 亚洲国产精品综合一区在线| 久久久久久久久毛片精品| 国产精品视频第一区二区三区| 乱人伦精品视频在线观看| 日韩在线看片中文字幕不卡| 中文字幕无码亚洲欧洲日韩| 亚洲欧美日韩综合久久久 | 四虎影院国产精品| 日韩精品中文字幕视频一区| 日韩色图在线观看| 在线日韩麻豆一区| 国产亚洲美女精品久久| 国产免费无遮挡精品视频| 动漫精品一区二区三区3d| 日韩制服国产精品一区| 亚洲国产日韩综合久久精品| 日本精品视频在线播放| 日韩国产精品99久久久久久| 国产日韩精品一区二区在线观看 | 精品久久人人爽天天玩人人妻| 日韩丰满少妇无码内射| 精品剧情v国产在线麻豆| 国产精品二区三区免费播放心| 亚洲AV永久无码精品一福利|