程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql explain 用法詳解

mysql explain 用法詳解

編輯：MySQL綜合教程

mysql explain 可以用來分析你要查詢的sql語句的各項參數值，下面我們有詳細的實例有需要的可以看看。代碼如下復制代碼

EXPLAIN table == DESC table == SHOW COLUMNS FORM table

EXPLAIN [EXTENDED|PARTITIONS] SELECT... --顯示該語句將使用哪一個索引以及何時進行多表查詢與使用到的表順序

代碼如下復制代碼

mysql> EXPLAIN SELECT * FROM BOOKS WHERE BOOK_ID=1;

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

1 row in set (0.00 sec)

POSSIBLE_KEYS字段列舉出了用於查找數據的索引，而KEY字段指示我們實際上用到了XX索引。如果POSSIBLE_KEYS字段的值顯示NULL，那麼說明沒有用到索引。

SELECT_TYPE

SIMPLE 指示簡單SELECT語句，沒有子查詢或者UNION

PRIMARY 當使用子查詢時，這是主要的SELECT語句

UNION 當使用子查詢時，這是主要的SELECT語句

DEPENDENT UNION 當使用UNION時，這並不是第一個SELECT語句，取決於主查詢

UNION RESULT UINON查詢

SUBQUERY 子查詢中的第一個SELECT語句

DEPENDENT SUBQUERY 子查詢中的第一個SELECT語句,取決於主查詢

DERIVED 來自於子查詢的表

UNCACHEABLE SUBQUERY 指示子查詢中的結果不能緩存,因此必須對主查詢中的每一行重新評價

UNCACHEABLE UNION 指示子查詢的UNION中，結果不能緩存，因此必須對主查詢中的每一行重新評價

這是在官網上的說明

代碼如下復制代碼 EXPLAIN Syntax
EXPLAIN [EXTENDED] SELECT select_options
Or:
EXPLAIN tbl_name

The EXPLAIN statement can be used either as a way to obtain information about how MySQL executes a statement, or as a synonym for DESCRIBE:
When you precede a SELECT statement with the keyword EXPLAIN, MySQL displays information from the optimizer about the query execution plan. That is, MySQL explains how it would process the statement, including information about how tables are joined and in which order. EXPLAIN EXTENDED can be used to provide additional information.

例如:

很顯然這條SQL是從裡向外的執行,就是從id=3 向上執行.

2. select_type

就是select類型,可以有以下幾種

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

代碼如下復制代碼 mysql> explain select * from t3 where id=3952602;
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+

(2). PRIMARY

我的理解是最外層的select.例如:

代碼如下復制代碼

(3).UNION

UNION中的第二個或後面的SELECT語句.例如

(4).DEPENDENT UNION

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

代碼如下復制代碼

(4).UNION RESULT

UNION的結果。

代碼如下復制代碼

(5).SUBQUERY

子查詢中的第一個SELECT.

代碼如下復制代碼

mysql> explain select * from t3 where id = (select id from t3 where id=3952602 ) ;
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys     | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY     | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4       | const |    1 |             |
| 2 | SUBQUERY    | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4       |       |    1 | Using index |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+

(6). DEPENDENT SUBQUERY

子查詢中的第一個SELECT，取決於外面的查詢

代碼如下復制代碼

(7).DERIVED

派生表的SELECT(FROM子句的子查詢)

代碼如下復制代碼

3.table

顯示這一行的數據是關於哪張表的.
有時不是真實的表名字,看到的是derivedx(x是個數字,我的理解是第幾步執行的結果)

+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

4.type

這列很重要,顯示了連接使用了哪種類別,有無使用索引.
從最好到最差的連接類型為const、eq_reg、ref、range、indexhe和ALL

(1).system

這是const聯接類型的一個特例。表僅有一行滿足條件.如下(t3表上的id是 primary key)

代碼如下復制代碼

(2).const

表最多有一個匹配行，它將在查詢開始時被讀取。因為僅有一行，在這行的列值可被優化器剩余部分認為是常數。const表很快，因為它們只讀取一次！

const用於用常數值比較PRIMARY KEY或UNIQUE索引的所有部分時。在下面的查詢中，tbl_name可以用於const表：

代碼如下復制代碼 SELECT * from tbl_name WHERE primary_key=1；
SELECT * from tbl_name WHERE primary_key_part1=1和 primary_key_part2=2；

例如:

(3). eq_ref

對於每個來自於前面的表的行組合，從該表中讀取一行。這可能是最好的聯接類型，除了const類型。它用在一個索引的所有部分被聯接使用並且索引是UNIQUE或PRIMARY KEY。

eq_ref可以用於使用= 操作符比較的帶索引的列。比較值可以為常量或一個使用在該表前面所讀取的表的列的表達式。

在下面的例子中，MySQL可以使用eq_ref聯接來處理ref_tables：

代碼如下復制代碼

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> create unique index idx_t3_id on t3(id) ;
Query OK, 1000 rows affected (0.03 sec)
Records: 1000 Duplicates: 0 Warnings: 0

mysql> explain select * from t3,t4 where t3.id=t4.accountid;
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
| id | select_type | table | type   | possible_keys     | key       | key_len | ref                  | rows | Extra |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
| 1 | SIMPLE      | t4    | ALL    | NULL              | NULL      | NULL    | NULL                 | 1000 |       |
| 1 | SIMPLE      | t3    | eq_ref | PRIMARY,idx_t3_id | idx_t3_id | 4       | dbatest.t4.accountid |    1 |       |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+

(4).ref

對於每個來自於前面的表的行組合，所有有匹配索引值的行將從這張表中讀取。如果聯接只使用鍵的最左邊的前綴，或如果鍵不是UNIQUE或PRIMARY KEY（換句話說，如果聯接不能基於關鍵字選擇單個行的話），則使用ref。如果使用的鍵僅僅匹配少量行，該聯接類型是不錯的。

ref可以用於使用=或<=>操作符的帶索引的列。

在下面的例子中，MySQL可以使用ref聯接來處理ref_tables：

代碼如下復制代碼

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
AND ref_table.key_column_part2=1;

例如:

mysql> drop index idx_t3_id on t3;
Query OK, 1000 rows affected (0.03 sec)
Records: 1000 Duplicates: 0 Warnings: 0

mysql> create index idx_t3_id on t3(id) ;
Query OK, 1000 rows affected (0.04 sec)
Records: 1000 Duplicates: 0 Warnings: 0

mysql> explain select * from t3,t4 where t3.id=t4.accountid;
+----+-------------+-------+------+-------------------+-----------+---------+----------------------+------+-------+
| id | select_type | table | type | possible_keys     | key       | key_len | ref                  | rows | Extra |
+----+-------------+-------+------+-------------------+-----------+---------+----------------------+------+-------+
| 1 | SIMPLE      | t4    | ALL | NULL              | NULL      | NULL    | NULL                 | 1000 |       |
| 1 | SIMPLE      | t3    | ref | PRIMARY,idx_t3_id | idx_t3_id | 4       | dbatest.t4.accountid |    1 |       |
+----+-------------+-------+------+-------------------+-----------+---------+----------------------+------+-------+
2 rows in set (0.00 sec)

(5). ref_or_null

該聯接類型如同ref，但是添加了MySQL可以專門搜索包含NULL值的行。在解決子查詢中經常使用該聯接類型的優化。

在下面的例子中，MySQL可以使用ref_or_null聯接來處理ref_tables：

代碼如下復制代碼

SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;

(6). index_merge

該聯接類型表示使用了索引合並優化方法。在這種情況下，key列包含了使用的索引的清單，key_len包含了使用的索引的最長的關鍵元素。

例如:

(7). unique_subquery

該類型替換了下面形式的IN子查詢的ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery是一個索引查找函數，可以完全替換子查詢，效率更高。

(8).index_subquery