MySQL EXPLAIN語句的使用示例

Posted on by

在MySQL優化的環節上，我們首先需要知道的就是我們當前的這句SQL語句在實際的數據庫中究竟是怎麼執行的，才能談要如何優化它。而在MySQL中，就給我們提供瞭模擬語句執行的一個非常好用的關鍵字：EXPLAIN。EXPLAIN可以用來查看SQL語句的執行效果，可以幫助選擇更好的索引和優化查詢語句，寫出更好的優化語句。因此今天我們就來講一講這個關鍵字的一些基礎的用法與應用。

一、使用方法

EXPLAIN的使用方法非常簡單：

mysql> EXPLAIN SELECT * FROM user;

簡單來說，就是在原有的SQL語句前面加上EXPLAIN關鍵字，或者說是在EXPLAIN關鍵字後跟這你要檢查的SQL語句。

二、輸出結果

EXPLAIN語句的輸出結果才是我們想要的數據，也是我們分析的重點。
我們先來看看上面的語句所給到的對應的結果的形式：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE  | user | NULL  | ALL | NULL   | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

EXPLAIN語句給到我們的數據總共有10列，接下來我們看一下一些在性能優化上有比較重要作用的數據列所代表的意思。

1.id

這個是select查詢的序列號。

2.select_type

當我們的SQL語句是非select語句的時候（即delete，update…），這個字段的值就是對應的操作類型（delete，update…）。

mysql> EXPLAIN INSERT INTO user VAULES(2,'ahong','31');

此時的輸出select_type就是我們對應的INSERT：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | INSERT  | user | NULL  | ALL | NULL   | NULL | NULL | NULL | NULL |  NULL | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

而當SQL語句時select語句的時候，他就是對應的一些詳細的select的類型，可以有如下幾種：

SIMPLE：簡單SELECT(不使用UNION或子查詢等)
PRIMARY：最外面的SELECT
UNION：UNION中的第二個或後面的SELECT語句
DEPENDENT UNION：UNION中的第二個或後面的SELECT語句，取決於外面的查詢
UNION RESULT：UNION的結果。
SUBQUERY：子查詢中的第一個SELECT
DEPENDENT SUBQUERY：子查詢中的第一個SELECT，取決於外面的查詢
DERIVED：導出表的SELECT(FROM子句的子查詢)

下面就是一個最簡單的SIMPLE查詢的例子：

mysql> EXPLAIN SELECT * FROM user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE  | user | NULL  | ALL | NULL   | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

3.table

顯示這一步操作所訪問的數據是關於哪一張表的。

4.partitions

顯示表所使用的分區，如果要統計十年公司訂單的金額，可以把數據分為十個區，每一年代表一個區。這樣可以大大的提高查詢效率。

5.type

這是最重要的一列。顯示瞭連接使用瞭哪種類別，有無使用索引。是分析查詢性能的關鍵。
結果性能從優到差分別有以下的情況：

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

而這幾種情況所代表的意義如下：

  • system、const： 可以將查詢的變量轉為常量. 如id=1; id為 主鍵或唯一鍵.
  • eq_ref： 訪問索引,返回某單一行的數據.(通常在聯接時出現，查詢使用的索引為主鍵或惟一鍵)
  • ref： 訪問索引,返回某個值的數據.(可以返回多行) 通常使用=時發生
  • range： 這個連接類型使用索引返回一個范圍中的行，比如使用>或<查找東西，並且該字段上建有索引時發生的情況(註:不一定好於index)
  • index： 以索引的順序進行全表掃描，優點是不用排序,缺點是還要全表掃描
  • ALL： 全表掃描，應該盡量避免_

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

6.possible_key

顯示查詢語句有可能會使用到的索引列。取值可能為一個，多個或者null。

7.key

key列顯示的是該查詢語句實際使用的索引列。如為null，則表示沒有使用索引。
展示一下possible_key和key的實際效果：
下面是一個在age列上建立索引的數據表，我們進行以下的查詢

mysql> explain select * from user where age = 1;

會得到以下的結果：

+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE  | user | NULL  | ref | age   | age | 5  | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

8.key_len

顯示的是當前的查詢語句所使用的索引的長度。在不損失精確性的情況下，長度越短越好.

9.ref

引用到的上一個表的列。

10.rows

根據表的情況和查詢語句的情況，MySQL會估算出返回最終結果所必須檢查的行的數量。該列的值越大查詢效率越差。

11.filtered

一個百分比的值，和rows 列的值一起使用，可以估計出查詢執行計劃(QEP)中的前一個表的結果集，從而確定join操作的循環次數。小表驅動大表，減輕連接的次數。

12.extra

關於MySQL如何解析查詢的額外信息，主要有以下幾種：

Extra中包含的值：

  • using index： 隻用到索引,可以避免訪問表，性能很高。
  • using where： 使用到where來過濾數據， 不是所有的where clause都要顯示using where. 如以=方式訪問索引。
  • using tmporary： 用到臨時表去處理當前的查詢。
  • using filesort： 用到額外的排序，此時mysql會根據聯接類型瀏覽所有符合條件的記錄，並保存排序關鍵字和行指針，然後排序關鍵字並按順序檢索行。(當使用order by v1,而沒用到索引時,就會使用額外的排序)。
  • range checked for eache record(index map:N)： 沒有好的索引可以使用。
  • Using index for group-by：__表明可以在索引中找到分組所需的所有數據，不需要查詢實際的表。explain select user_id from t_order group by user_id;_

以上就是MySQL EXPLAIN語句的使用示例的詳細內容，更多關於MySQL EXPLAIN語句的資料請關註WalkonNet其它相關文章！

推薦閱讀：