程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> explain分析sql效率的方法,explainsql效率

explain分析sql效率的方法,explainsql效率

編輯:MySQL綜合教程

explain分析sql效率的方法,explainsql效率


Explain命令在解決數據庫性能上是第一推薦使用命令,大部分的性能問題可以通過此命令來簡單的解決,Explain可以用來查看SQL語句的執行效 果,可以幫助選擇更好的索引和優化查詢語句,寫出更好的優化語句。

Explain語法:

EXPLAIN tbl_name或:EXPLAIN [EXTENDED] SELECT select_options

前者可以得出一個表的字段結構等等,後者主要是給出相關的一些索引信息,而今天要講述的重點是後者。

例:

EXPLAIN 
  SELECT sum(amount) 
FROM customer a, payment b 
  WHERE1 = 1 
AND a.customer_id = b.customer_id 
AND a.email = '[email protected]'; 

執行結果:

下面對各個屬性進行了解:

1、id:這是SELECT的查詢序列號

2、select_type:select_type就是select的類型,可以有以下幾種:

SIMPLE:簡單SELECT(不使用UNION或子查詢等)

PRIMARY:最外面的SELECT

UNION:UNION中的第二個或後面的SELECT語句

DEPENDENT UNION:UNION中的第二個或後面的SELECT語句,取決於外面的查詢

UNION RESULT:UNION的結果。

SUBQUERY:子查詢中的第一個SELECT

DEPENDENT SUBQUERY:子查詢中的第一個SELECT,取決於外面的查詢

DERIVED:導出表的SELECT(FROM子句的子查詢)

3、table:顯示這一行的數據是關於哪張表的實際的表名(如select * from customer;) 或表的別名 (如 select * from customer a);

4、type:這列最重要,顯示了連接使用了哪種類別,有無使用索引,是使用Explain命令分析性能瓶頸的關鍵項之一。

結果值從好到壞依次是:

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

一般來說,得保證查詢至少達到range級別,最好能達到ref,否則就可能會出現性能問題。

all: 意味著從表的第1行,往後,逐行做全表掃描.,運氣不好掃描到最後一行.

index: 比all性能稍好一點,
通俗的說: all 掃描所有的數據行,相當於data_all index 掃描所有的索引節點,相當於index_all

注:all是沿著磁盤掃描,index是沿著索引掃描

range: 意思是查詢時,能根據索引做范圍的掃描

explain select * from customer where customer_id > 4;

index_subquery 在子查詢中,基於除唯一索引之外的索引進行掃描;

unique_subquery 在子查詢中,基於唯一索引進行掃描,類似於EQ_REF;

index_merge 多重范圍掃描。兩表連接的每個表的連接字段上均有索引存在且索引有序,結果合並在一起。適用於作集合的並、交操作。

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

fulltext 全文索引

ref 這也是一種索引訪問,它返回所有匹配某個單獨值的行,然而,它可能會找到多個符合條件的行,所以他應該屬於查找和掃描的混合體(也是范圍區間,不過比range更加精確)。

explain select * from payment where customer_id =4;

eq_ref 是指,通過索引列,直接引用某1行數據(精確到一行數據中)常見於連接查詢中

const, system, null 當mysql能對查詢的部分就行優化,並且轉換成一個常量的時候,它就會使用這種訪問類型了。比如你把一行的主鍵當做where條件放進去,那mysql就可以把它轉換成一個常量,然後查詢.

5、possible_keys:列指出MySQL能使用哪個索引在該表中找到行

6、key:顯示MySQL實際決定使用的鍵(索引)。如果沒有選擇索引,鍵是NULL

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

8、ref:顯示使用哪個列或常數與key一起從表中選擇行。

9、rows:顯示MySQL認為它執行查詢時必須檢查的行數。

10、Extra:包含MySQL解決查詢的詳細信息,也是關鍵參考項之一。

using index:出現這個說明mysql使用了覆蓋索引,避免訪問了表的數據行,效率不錯! 
using where:這說明服務器在存儲引擎收到行後將進行過濾。有些where中的條件會有屬於索引的列,當它讀取使用索引的時候,就會被過濾,所以會出現有些where語句並沒有在extra列中出現using where這麼一個說明。 
using temporary:這意味著mysql對查詢結果進行排序的時候使用了一張臨時表。 
using filesort:這個說明mysql會對數據使用一個外部的索引排序,而不是按照表內的索引順序進行讀取。 

除此以外,explain 的extended 擴展能夠在原本explain的基礎上額外的提供一些查詢優化的信息,這些信息可以通過mysql的show warnings命令得到。下面是一個最簡單的例子。 

EXPLAIN EXTENDED
SELECT sum(amount)
FROM customer a, payment b
WHERE 1 = 1
AND a.customer_id = b.customer_id
AND a.email = '[email protected]';

接下來再執行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
[email protected]')) |
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
2 rows in set (0.00 sec)

可以看到優化器自動去除了1=1恆成立的條件。 

MySQL5.1開始支持分區功能,同時explain命令也增加了對分區的支持。可以通過explain partitions 命令查看SQL所訪問的分區。

以上這篇explain分析sql效率的方法就是小編分享給大家的全部內容了,希望能給大家一個參考,也希望大家多多支持幫客之家。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved