快捷導(dǎo)航

explain分析sql效率的方法

更新時(shí)間：2017年03月24日 10:03:10 投稿：jingxian

下面小編就為大家?guī)?lái)一篇explain分析sql效率的方法。小編覺(jué)得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧

Explain命令在解決數(shù)據(jù)庫(kù)性能上是第一推薦使用命令，大部分的性能問(wèn)題可以通過(guò)此命令來(lái)簡(jiǎn)單的解決，Explain可以用來(lái)查看SQL語(yǔ)句的執(zhí)行效果，可以幫助選擇更好的索引和優(yōu)化查詢語(yǔ)句，寫出更好的優(yōu)化語(yǔ)句。

Explain語(yǔ)法：

EXPLAIN tbl_name或：EXPLAIN [EXTENDED] SELECT select_options

前者可以得出一個(gè)表的字段結(jié)構(gòu)等等，后者主要是給出相關(guān)的一些索引信息，而今天要講述的重點(diǎn)是后者。

例：

EXPLAIN 
  SELECT sum(amount) 
FROM customer a, payment b 
  WHERE1 = 1 
AND a.customer_id = b.customer_id 
AND a.email = 'JANE.BENNETT@sakilacustomer.org';

執(zhí)行結(jié)果：

下面對(duì)各個(gè)屬性進(jìn)行了解：

1、id：這是SELECT的查詢序列號(hào)

2、select_type：select_type就是select的類型，可以有以下幾種：

SIMPLE：簡(jiǎn)單SELECT(不使用UNION或子查詢等)

PRIMARY：最外面的SELECT

UNION：UNION中的第二個(gè)或后面的SELECT語(yǔ)句

DEPENDENT UNION：UNION中的第二個(gè)或后面的SELECT語(yǔ)句，取決于外面的查詢

UNION RESULT：UNION的結(jié)果。

SUBQUERY：子查詢中的第一個(gè)SELECT

DEPENDENT SUBQUERY：子查詢中的第一個(gè)SELECT，取決于外面的查詢

DERIVED：導(dǎo)出表的SELECT(FROM子句的子查詢)

3、table：顯示這一行的數(shù)據(jù)是關(guān)于哪張表的實(shí)際的表名（如select * from customer;）或表的別名（如 select * from customer a）;

4、type：這列最重要，顯示了連接使用了哪種類別,有無(wú)使用索引，是使用Explain命令分析性能瓶頸的關(guān)鍵項(xiàng)之一。

結(jié)果值從好到壞依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般來(lái)說(shuō)，得保證查詢至少達(dá)到range級(jí)別，最好能達(dá)到ref，否則就可能會(huì)出現(xiàn)性能問(wèn)題。

all: 意味著從表的第1行,往后,逐行做全表掃描.,運(yùn)氣不好掃描到最后一行.

index: 比all性能稍好一點(diǎn)，
通俗的說(shuō): all 掃描所有的數(shù)據(jù)行,相當(dāng)于data_all index 掃描所有的索引節(jié)點(diǎn),相當(dāng)于index_all

注：all是沿著磁盤掃描，index是沿著索引掃描

range: 意思是查詢時(shí),能根據(jù)索引做范圍的掃描

explain select * from customer where customer_id > 4;

index_subquery 在子查詢中，基于除唯一索引之外的索引進(jìn)行掃描;

unique_subquery 在子查詢中，基于唯一索引進(jìn)行掃描，類似于EQ_REF;

index_merge 多重范圍掃描。兩表連接的每個(gè)表的連接字段上均有索引存在且索引有序，結(jié)果合并在一起。適用于作集合的并、交操作。

ref_or_null 類似REF，只是搜索條件包括：連接字段的值可以為NULL的情況，比如 where col = 2 or col is null

fulltext 全文索引

ref 這也是一種索引訪問(wèn)，它返回所有匹配某個(gè)單獨(dú)值的行，然而，它可能會(huì)找到多個(gè)符合條件的行，所以他應(yīng)該屬于查找和掃描的混合體（也是范圍區(qū)間，不過(guò)比range更加精確）。

explain select * from payment where customer_id =4;

eq_ref 是指,通過(guò)索引列,直接引用某1行數(shù)據(jù)（精確到一行數(shù)據(jù)中）常見(jiàn)于連接查詢中

const, system, null 當(dāng)mysql能對(duì)查詢的部分就行優(yōu)化，并且轉(zhuǎn)換成一個(gè)常量的時(shí)候，它就會(huì)使用這種訪問(wèn)類型了。比如你把一行的主鍵當(dāng)做where條件放進(jìn)去，那mysql就可以把它轉(zhuǎn)換成一個(gè)常量，然后查詢.

5、possible_keys：列指出MySQL能使用哪個(gè)索引在該表中找到行

6、key：顯示MySQL實(shí)際決定使用的鍵（索引）。如果沒(méi)有選擇索引，鍵是NULL

7、key_len：顯示MySQL決定使用的鍵長(zhǎng)度。如果鍵是NULL，則長(zhǎng)度為NULL。使用的索引的長(zhǎng)度。在不損失精確性的情況下，長(zhǎng)度越短越好

8、ref：顯示使用哪個(gè)列或常數(shù)與key一起從表中選擇行。

9、rows：顯示MySQL認(rèn)為它執(zhí)行查詢時(shí)必須檢查的行數(shù)。

10、Extra：包含MySQL解決查詢的詳細(xì)信息，也是關(guān)鍵參考項(xiàng)之一。

using index：出現(xiàn)這個(gè)說(shuō)明mysql使用了覆蓋索引，避免訪問(wèn)了表的數(shù)據(jù)行，效率不錯(cuò)！ 
using where：這說(shuō)明服務(wù)器在存儲(chǔ)引擎收到行后將進(jìn)行過(guò)濾。有些where中的條件會(huì)有屬于索引的列，當(dāng)它讀取使用索引的時(shí)候，就會(huì)被過(guò)濾，所以會(huì)出現(xiàn)有些where語(yǔ)句并沒(méi)有在extra列中出現(xiàn)using where這么一個(gè)說(shuō)明。 
using temporary：這意味著mysql對(duì)查詢結(jié)果進(jìn)行排序的時(shí)候使用了一張臨時(shí)表。 
using filesort：這個(gè)說(shuō)明mysql會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取。

除此以外，explain 的extended 擴(kuò)展能夠在原本explain的基礎(chǔ)上額外的提供一些查詢優(yōu)化的信息，這些信息可以通過(guò)mysql的show warnings命令得到。下面是一個(gè)最簡(jiǎn)單的例子?！?/p>

EXPLAIN EXTENDED
SELECT sum(amount)
FROM customer a, payment b
WHERE 1 = 1
AND a.customer_id = b.customer_id
AND a.email = 'JANE.BENNETT@sakilacustomer.org';

接下來(lái)再執(zhí)行Show Warnings　　

mysql> show warnings;
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
| Level | Code | Message

|
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future rele
ase.

|
| Note | 1003 | /* select#1 */ select sum(`sakila`.`b`.`amount`) AS `sum(amou
nt)` from `sakila`.`customer` `a` join `sakila`.`payment` `b` where ((`sakila`.`
b`.`customer_id` = `sakila`.`a`.`customer_id`) and (`sakila`.`a`.`email` = 'JANE
.BENNETT@sakilacustomer.org')) |
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
2 rows in set (0.00 sec)

可以看到優(yōu)化器自動(dòng)去除了1=1恒成立的條件?！?/p>

MySQL5.1開(kāi)始支持分區(qū)功能，同時(shí)explain命令也增加了對(duì)分區(qū)的支持?？梢酝ㄟ^(guò)explain partitions 命令查看SQL所訪問(wèn)的分區(qū)。