實戰(zhàn)講解MySQL的expain執(zhí)行計劃，面試官當(dāng)場要了我

explain或者desc獲取MySQL如何執(zhí)行select語句的信息。

結(jié)果包含很多列

1 各列字段說明

1.1 id

SELECT標(biāo)識符。這是查詢中SELECT的序列號，表示查詢中執(zhí)行select子句或者操作表的順序。如果該行引用其他行的并集結(jié)果，則該值可為NULL。

id號分為三種情況:

id相同，那么執(zhí)行順序從上到下

explain se1ect * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sa1 between sg.1osal and sg.hisal;

1

2

id不同，如果是子查詢，id的序號會遞增，id值越大優(yōu)先級越高，越先被執(zhí)行

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

1

2

id相同和不同的，同時存在：相同的可以認(rèn)為是一組，從上往下順序執(zhí)行，在所有組中，id值越大， 越先執(zhí)行

exp1ain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sa1 between sg.1osal and sg.hisal where e. deptno in (select d.deptno from dept d where d.dname = 'SALES'); select_ type

1

2

3

4

1.2 select_type

主要用來分辨SELECT的類型，是普通查詢還是聯(lián)合查詢還是子查詢:

simple（簡單表，即不用表連接或子查詢）

primary（主查詢，即外部查詢）

union（union中的第二個或者后面的查詢語句）

subquery（子查詢中的第一個select）

1.3 table

輸出結(jié)果集。對應(yīng)行正在訪問哪個表，表名或者別名，可能是臨時表或者union合并結(jié)果集。

如果是具體表名，則表明從實際的物理表中獲取數(shù)據(jù)，當(dāng)然也可是表的別名

表名是derivedN的形式，表示使用了id為N的查詢產(chǎn)生的衍生表

當(dāng)有union result時，表名是union n1,n2等的形式，n1,n2表示參與union的id

1.4 type

type列描述如何連接表。

表示MySQL在表中找到所需行的方式，或者叫訪問類型。

常見類型：all,index,range,ref,eq_ref,const,system,null，性能由差到好。

一般需要保證查詢至少達(dá)到range級，最好能達(dá)到ref。

1.4.1 ALL

最簡單暴力的全表掃描，MySQL遍歷全表找到匹配行，效率最差。

對來自先前表的行的每個組合進(jìn)行全表掃描。若該表是未標(biāo)記為const的第一個表，則通常不好，并且在所有其他情況下通常性能也非常糟糕。

一般可以通過增加索引來避免ALL，這些索引允許基于早期表中的常量值或列值從表中檢索行。

explain select * from film where rating > 9;

1

1.4.2 index

連接類型與ALL相同，除了掃描索引樹外。這發(fā)生于兩種方式：

若索引是查詢的覆蓋索引，并且可用于滿足表中所需的所有數(shù)據(jù)，則僅掃描索引樹

這種情況下，Extra列顯示Using index。僅索引掃描通常比ALL更快，因索引大小通常小于表數(shù)據(jù)

使用對索引的讀取執(zhí)行全表掃描，以按索引順序查找數(shù)據(jù)行。Extra列不顯示 Using index。

當(dāng)查詢僅使用屬于單個索引一部分的列時，MySQL可以使用此連接類型。

explain select title from film;

1

1.4.3 range

使用索引查詢行，僅檢索給定范圍內(nèi)的行。

key列指示使用的哪個索引。key_len包含使用的最長的鍵部分。

此類型的ref=NULL。

當(dāng)使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN()操作符將key列與常量進(jìn)行比較時，可以使用range：

索引范圍掃描，常見<,<=,>,>=,between

SELECT * FROM tbl_name WHERE key_column = 10; SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20; SELECT * FROM tbl_name WHERE key_column IN (10,20,30); SELECT * FROM tbl_name WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

1

2

3

4

5

6

7

8

9

10

11

1.4.4 index_subquery

此連接類型類似于unique_subquery。它代替了IN子查詢，但適用于以下形式的子查詢中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

1

1.4.5 unique_subquery

此類型將eq_ref替換為以下形式的某些IN子查詢：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

1

unique_subquery只是一個索引查找函數(shù)，可以完全替換子查詢以提高效率。

1.4.6 index_merge

此聯(lián)接類型指示使用索引合并優(yōu)化。在這種情況下，輸出行中的鍵列包含使用的索引列表，而key_len包含使用的索引的最長鍵部分的列表。

1.4.7 ref_or_null

這種連接類型類似于ref，但是MySQL會額外搜索包含NULL值的行。此聯(lián)接類型優(yōu)化最常用于解析子查詢。在以下示例中，MySQL可以使用ref_or_null連接來處理ref_table：

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

1

2

1.4.8 fulltext

使用FULLTEXT索引執(zhí)行連接。

1.4.9 ref

對于先前表中的每個行組合，將從該表中讀取具有匹配索引值的所有行。

如果連接僅使用鍵的最左前綴，或者如果該鍵不是PRIMARY KEY（主鍵）或UNIQUE（唯一）索引（即如果連接無法根據(jù)鍵值選擇單行），則會使用ref。

如果使用的鍵僅匹配幾行，則這是一種很好的聯(lián)接類型。

ref可以用于使用= or <=> 運算符進(jìn)行比較的索引列。在以下示例中，MySQL可以使用ref聯(lián)接來處理ref_table：

SELECT * FROM ref_table WHERE key_column=expr; SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

1

2

3

4

5

6

7

8

1.4.10 eq_ref

對于先前表中的每行組合，從此表中讀取一行。除了system和const類型，這是最好的連接類型。

當(dāng)連接使用索引的所有部分并且索引是PRIMARY KEY或UNIQUE NOT NULL索引時，將使用它。

類似ref，區(qū)別在于所用索引是唯一索引，對于每個索引鍵值，表中有一條記錄匹配；

簡單來說就是多表連接使用primary key或者unique index作為關(guān)聯(lián)條件。

eq_ref可用于使用=運算符進(jìn)行比較的索引列。比較值可以是常量，也可以是使用在此表之前讀取的表中列的表達(dá)式。在以下示例中，MySQL可以使用eq_ref連接來處理ref_table：

SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

1

2

3

4

5

6

1.4.11 const

表最多有一個匹配行，該行在查詢開始時讀取。因為只有一行，所以優(yōu)化器的其余部分可以將這一行中列的值視為常量。 const表非常快，因為它們僅讀取一次。

當(dāng)將PRIMARY KEY或UNIQUE索引的所有部分與常量值進(jìn)行比較時，將使用const。在以下查詢中，tbl_name可以用作const表：

SELECT * FROM tbl_name WHERE primary_key=1; SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;

1

2

3

4

1.4.12 system

該表只有一行（系統(tǒng)表）。這是const 連接類型的特例。

type null，MySQL不用訪問數(shù)據(jù)庫直接得到結(jié)果。

1.5 possible_keys

此次查詢中可能選用的索引

1.6 key

此次查詢中確切使用到的索引

1.7 ref

哪個字段或常數(shù)與 key 一起被使用

1.8 rows

此查詢一共掃描了多少行，這個是一個估計值，不準(zhǔn)確。

1.9 filtered

此查詢條件所過濾的數(shù)據(jù)的百分比

1.10 extra

額外信息：

using filesort

性能消耗大，需要額外的一次查詢（排序）。

使用EXPLAIN可以檢查MySQL是否可以使用索引來解析ORDER BY子句：

Extra列不包含Using filesort，則使用索引，并且不執(zhí)行文件排序

Extra列包含Using filesort，則不使用索引，而是執(zhí)行全文件排序

EXPLAIN不能區(qū)分優(yōu)化器是否在內(nèi)存中執(zhí)行文件排序。在優(yōu)化程序trace輸出中可以看到內(nèi)存文件排序的使用。查找filesort_priority_queue_optimization即可。

對于單索引，若

排序和查找是同一字段，則不會出現(xiàn)該方式

不是，則會出現(xiàn)。

對于聯(lián)合索引，不能跨列（最左匹配原則）

using temporary

建立臨時表保存中間結(jié)果，查詢完成之后把臨時表刪除。

using index（性能提升）

表示當(dāng)前的查詢是覆蓋索引，直接從索引中讀取數(shù)據(jù)，而無需訪問原數(shù)據(jù)表（回表）。即需要使用到的列，全部都在索引中。

若同時出現(xiàn)using where，則索引被用來執(zhí)行索引鍵值的查找

若沒有，則索引被用來讀取數(shù)據(jù)，而不是真的查找

using index condition

MySQL 5.6后加入的新特性（Index Condition Pushdown）。會先條件過濾索引，過濾完索引后找到所有符合索引條件的數(shù)據(jù)行，隨后用 WHERE 子句中的其他條件去過濾這些數(shù)據(jù)行。

using where（需回表查詢）

使用where進(jìn)行條件過濾。

假設(shè) age 為索引，如下 SQL

select age,name from ... where age =

1

using join buffer

使用連接緩存

impossible where

where語句的結(jié)果總是false

no matching row in const table

對于具有聯(lián)接的查詢，存在一個空表或沒有滿足唯一索引條件的行的表。

其實還有很多，不再過多描述。

explain extended

MySQL 4.1引入explain extended命令，通過explain extended 加上show warnings可以查看MySQL 真正被執(zhí)行之前優(yōu)化器所做的操作

explain select * from users; show warnings;

1

2

可從warning字段看到，會去除一些恒成立的條件，可以利用explain extended的結(jié)果來迅速的獲取一個更清晰易讀的sql語句。

2 show profile

SHOW PROFILE和SHOW PROFILES語句顯示概要信息，該信息指示在當(dāng)前會話過程中執(zhí)行的語句的資源使用情況。

SHOW PROFILE和SHOW PROFILES語句已被棄用，并將在以后的MySQL版本中刪除，而改用性能模式。此處我們就簡單介紹一下，大家知道有這個東西就行了。

查看是否開啟profile

可見，默認(rèn)profiling是關(guān)閉的。

可通過set語句在session級別啟動profiling：

set profiling=1;

1

可查看執(zhí)行過程中每個線程的狀態(tài)和耗時。

其中的 sendingdata 狀態(tài)表示MySQL線程開始訪問數(shù)據(jù)行并把結(jié)果返回給客戶端，而不僅僅是返回給客戶端，由于在sending data狀態(tài)下，MySQL線程往往需要做大量的磁盤讀取操作；所以經(jīng)常是整個查詢中最耗時的狀態(tài)。

支持選擇all,cpu,block io,context,switch,page faults等明細(xì)，來查看MySQL在使用什么資源上耗費了過高的時間，例如，選擇查看cpu的耗費時間

show profile cpu for query 6;

1

對比MyISAM的操作，同樣執(zhí)行count(*)操作，檢查profile，Innodb表經(jīng)歷了Sending data狀態(tài)，而MyISAM的表完全不需要訪問數(shù)據(jù)

如果對MySQL 源碼感興趣，可以通過show profile source for query查看sql解析執(zhí)行過程的每個步驟對應(yīng)的源碼文件

show profile source for query 6

1

3 trace分析優(yōu)化器

MySQL 5.6提供。通過trace文件能夠進(jìn)一步了解優(yōu)化器的選擇，更好地理解優(yōu)化器的行為。

使用方式

開啟trace，設(shè)置格式為json，設(shè)置trace最大能夠使用的內(nèi)存，避免解析過程中因為默認(rèn)內(nèi)存小而不能完整顯示

set optimizer_trace="enabled=on",end_markers_in_json=on; set optimizer_trace_max_mem_size=1000000;

1

2

接下來執(zhí)行trace的sql語句

select * from ....where....

1

最后檢查information_schema.optimizer_trace就可以知道Mysql如何執(zhí)行sql

select * from information_schema.optimizer_trace

1

參考

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-join-types

MySQL SQL

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。