資料庫查詢優化：使用explain分析sql語句執行效率

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

Explain文法：explain select … from … [where …]

例如：explain select * from news;

輸出：

+----+-------------+-------+-------+-------------------+---------+---------+-------+------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+-------------------+---------+---------+-------+------

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

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：顯示這一行的資料是關于哪張表的

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

結果值從好到壞依次是：

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

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


•system: const 的一種特例，表中隻有一行資料
•const: 當确定最多隻會有一行比對的時候，MySQL優化器會在查詢前讀取它而且隻讀取一次，是以非常快。使用主鍵查詢往往就是 const 級别的，非常高效
•eq_ref: 最多隻傳回一條符合條件的記錄，通過使用在兩個表有關聯字段的時候
•ref: 通過普通索引查詢比對的很多行時的類型
•fulltext: 全文索引
•ref_or_null: 跟 ref 類似的效果，不過多一個列不能 null 的條件
•index_merge: 此連接配接類型表示使用了索引合并優化。在這種情況下，輸出行中的 key 列包含使用的索引清單，key_len包含所用索引的最長 key 部分清單
•unique_subquery: 在使用 in 查詢的情況下會取代 eq_ref
•range: 範圍掃描，一個有限制的索引掃描。key 列顯示使用了哪個索引。當使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符，用常量比較關鍵字列時,可以使用 range
•index: 類似全表掃描，隻是掃描表的時候按照索引次序進行而不是行。主要優點就是避免了排序, 但是開銷仍然非常大。如在Extra列看到Using index，說明正在使用覆寫索引，隻掃描索引的資料，它比按索引次序全表掃描的開銷要小很多
•ALL: 全表掃描
           

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

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

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

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

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

10、Extra：包含MySQL解決查詢的詳細資訊，也是關鍵參考項之一。

Distinct
一旦MYSQL找到了與行相聯合比對的行，就不再搜尋了

Not exists
MYSQL 優化了LEFT JOIN，一旦它找到了比對LEFT JOIN标準的行，

就不再搜尋了

Range checked for each

Record（index map:#）
沒有找到理想的索引，是以對于從前面表中來的每一 個行組合，MYSQL檢查使用哪個索引，并用它來從表中傳回行。這是使用索引的最慢的連接配接之一

Using filesort
看 到這個的時候，查詢就需要優化了。MYSQL需要進行額外的步驟來發現如何對傳回的行排序。它根據連接配接類型以及存儲排序鍵值和比對條件的全部行的行指針來 排序全部行

Using index
列資料是從僅僅使用了索引中的資訊而沒有讀取實際的行動的表傳回的，這發生在對表 的全部的請求列都是同一個索引的部分的時候

Using temporary
看到這個的時候，查詢需要優化了。這 裡，MYSQL需要建立一個臨時表來存儲結果，這通常發生在對不同的列集進行ORDER BY上，而不是GROUP BY上

Using where
使用了WHERE從句來限制哪些行将與下一張表比對或者是傳回給使用者。如果不想傳回表中的全部行，并且連接配接類型ALL或index， 這就會發生，或者是查詢有問題
           

其他一些Tip：

1. 當type 顯示為 “index” 時，并且Extra顯示為“Using Index”， 表明使用了覆寫索引。

在navicat中提供了便捷功能：