mysql查看sql執行進度，mysql 執行計劃 視頻_實戰講解MySQL執行計劃，面試官當場要了我

全是干貨的技術號：

本文已收錄在github，歡迎 star/fork：

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

結果包含很多列

1 各列字段說明

mysql查看sql執行進度、1.1 id

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

id號分為三種情況:

id相同，那么執行順序從上到下

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;

mysql where 執行順序？id不同，如果是子查詢，id的序號會遞增，id值越大優先級越高，越先被執行

explain select * from emp e where e.deptno in

(select d.deptno from dept d where d.dname = 'SALES');

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

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

執行計劃 sql。d.dname = 'SALES');

select_ type

1.2 select_type

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

simple(簡單表，即不用表連接或子查詢)

primary(主查詢，即外部查詢)

mysql可視化，union(union中的第二個或者后面的查詢語句)

subquery(子查詢中的第一個select)

1.3 table

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

如果是具體表名，則表明從實際的物理表中獲取數據，當然也可是表的別名

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

mysql45。當有union result時，表名是union n1,n2等的形式，n1,n2表示參與union的id

1.4 type

type列描述如何連接表。

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

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

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

mysql？1.4.1 ALL

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

對來自先前表的行的每個組合進行全表掃描。如果該表是未標記為const的第一個表，則通常不好，并且在所有其他情況下通常性能也非常糟糕。一般來說，可以通過添加索引來避免ALL，這些索引允許基于早期表中的常量值或列值從表中檢索行。

explain select * from film where rating > 9;

1.4.2 index

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

mysql實戰教程？如果索引是查詢的覆蓋索引，并且可用于滿足表中所需的所有數據，則僅掃描索引樹。

在這種情況下，Extra列顯示Using index。僅索引掃描通常比ALL更快，因為索引的大小通常小于表數據。

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

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

explain select title from film;

1.4.3 range

林曉斌mysql。使用索引查詢行，僅檢索給定范圍內的行。輸出行中的key列指示使用的哪個索引。key_len包含使用的最長的鍵部分。此類型的ref列為NULL。

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

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

SELECT * FROM tbl_name

WHERE key_column = 10;

SELECT * FROM tbl_name

mysql項目實戰教程，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.4.4 index_subquery

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

value IN (SELECT key_column FROM single_table WHERE some_expr)

1.4.5 unique_subquery

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

value IN (SELECT primary_key FROM single_table WHERE some_expr)

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

mysql數據庫實戰項目？1.4.6 index_merge

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

1.4.7 ref_or_null

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

SELECT * FROM ref_table

WHERE key_column=expr OR key_column IS NULL;

mysql執行計劃詳解。1.4.8 fulltext

使用FULLTEXT索引執行連接。

1.4.9 ref

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

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

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

查看sql語句執行計劃、ref可以用于使用= or <=> 運算符進行比較的索引列。在以下示例中，MySQL可以使用ref聯接來處理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

mysql入門？AND ref_table.key_column_part2=1;

1.4.10 eq_ref

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

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

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

簡單來說就是多表連接使用primary key或者unique index作為關聯條件。

mysql實戰45講？eq_ref可用于使用=運算符進行比較的索引列。比較值可以是常量，也可以是使用在此表之前讀取的表中列的表達式。在以下示例中，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;

MySQL定時任務，1.4.11 const

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

當將PRIMARY KEY或UNIQUE索引的所有部分與常量值進行比較時，將使用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;

MySQL面試。1.4.12 system

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

type null，MySQL不用訪問數據庫直接得到結果。

1.5 possible_keys

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

1.6 key

Mysql教程。此次查詢中確切使用到的索引

1.7 ref

哪個字段或常數與 key 一起被使用

1.8 rows

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

1.9 filtered

mysql基本使用，此查詢條件所過濾的數據的百分比

1.10 extra

額外的信息。

using filesort

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

如果EXPLAIN輸出的Extra列不包含Using filesort，則使用索引，并且不執行文件排序

如果EXPLAIN輸出的Extra列包含正在使用文件排序，則不使用索引，而是執行全文件的排序

EXPLAIN不能區分優化器是否在內存中執行文件排序。在優化程序trace輸出中可以看到內存文件排序的使用。查找filesort_priority_queue_optimization即可。

using temporary

建立臨時表保存中間結果，查詢完成之后把臨時表劇除

using index

表示當前的查詢是覆蓋索引，直接從索明中讀取數據，而不用訪問數據表。

如果阿時出現using where表示索引被用來執行索引健值的查找；

如果沒有，表示索引被用來讀取數據，而不是真的查找

using where

使用where進行條件過濾

using join buffer

使用連接緩存

impossible where

where語句的結果總是false

no matching row in const table

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

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

explain extended

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

explain select * from users;

show warnings;

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

2 show profile

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

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

查看是否開啟profile

可見，默認profiling是關閉的。

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

set profiling=1;

可查看執行過程中每個線程的狀態和耗時。

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

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

show profile cpu for query 6;

對比MyISAM的操作，同樣執行count(*)操作，檢查profile，Innodb表經歷了Sending data狀態，而MyISAM的表完全不需要訪問數據

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

show profile source for query 6

3 trace分析優化器

MySQL 5.6提供。通過trace文件能夠進一步了解優化器的選擇，更好地理解優化器的行為。

使用方式

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

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

set optimizer_trace_max_mem_size=1000000;

接下來執行trace的sql語句

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

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

select * from information_schema.optimizer_trace

參考