《Mysql學習MySQL查詢優化之explain的深入解析》要點：
本文介紹了Mysql學習MySQL查詢優化之explain的深入解析，希望對您有用。如果有疑問，可以聯系我們。
MYSQL學習在分析查詢性能時,考慮EXPLAIN關鍵字同樣很管用.EXPLAIN關鍵字一般放在SELECT查詢語句的前面,用于描述MySQL如何執行查詢操作、以及MySQL成功返回結果集需要執行的行數.explain 可以幫助我們分析 select 語句,讓我們知道查詢效率低下的原因,從而改進我們查詢,讓查詢優化器能夠更好的工作.
MYSQL學習一、MySQL 查詢優化器是如何工作的
MySQL 查詢優化器有幾個目標,但是其中最主要的目標是盡可能地使用索引,并且使用最嚴格的索引來消除盡可能多的數據行.最終目標是提交 SELECT 語句查找數據行,而不是排除數據行.優化器試圖排除數據行的原因在于它排除數據行的速度越快,那么找到與條件匹配的數據行也就越快.如果能夠首先進行最嚴格的測試,查詢就可以執行地更快.
EXPLAIN 的每個輸出行提供一個表的相關信息,并且每個行包括下面的列:?
| 項 |
說明 |
| id |
MySQL Query Optimizer 選定的執行計劃中查詢的序列號.表示查詢中執行 select 子句或操作表的順序,id 值越大優先級越高,越先被執行.id 相同,執行順序由上至下. |
| select_type 查詢類型 |
說明 |
| SIMPLE |
簡單的 select 查詢,不使用 union 及子查詢 |
| PRIMARY |
最外層的 select 查詢 |
| UNION |
UNION 中的第二個或隨后的 select 查詢,不 依賴于外部查詢的結果集 |
| DEPENDENT UNION |
UNION 中的第二個或隨后的 select 查詢,依 賴于外部查詢的結果集 |
| SUBQUERY |
子查詢中的第一個 select 查詢,不依賴于外 部查詢的結果集 |
| DEPENDENT SUBQUERY |
子查詢中的第一個 select 查詢,依賴于外部 查詢的結果集 |
| DERIVED |
用于 from 子句里有子查詢的情況. MySQL 會 遞歸執行這些子查詢, 把結果放在臨時表里. |
| UNCACHEABLE SUBQUERY |
結果集不能被緩存的子查詢,必須重新為外 層查詢的每一行進行評估. |
| UNCACHEABLE UNION |
UNION 中的第二個或隨后的 select 查詢,屬 于不可緩存的子查詢 |
| type 重要的項,顯示連接使用的類型,按最 優到最差的類型排序 |
說明 |
| system |
表僅有一行(=系統表).這是 const 連接類型的一個特例. |
| const |
const 用于用常數值比較 PRIMARY KEY 時.當 查詢的表僅有一行時,使用 System. |
| eq_ref |
const 用于用常數值比較 PRIMARY KEY 時.當 查詢的表僅有一行時,使用 System. |
| ref |
連接不能基于關鍵字選擇單個行,可能查找 到多個符合條件的行. 叫做 ref 是因為索引要 跟某個參考值相比較.這個參考值或者是一 個常數,或者是來自一個表里的多表查詢的 結果值. |
| ref_or_null |
如同 ref, 但是 MySQL 必須在初次查找的結果 里找出 null 條目,然后進行二次查找. |
| index_merge |
說明索引合并優化被使用了. |
| unique_subquery |
在某些 IN 查詢中使用此種類型,而不是常規的 ref:value IN (SELECT primary_key FROM single_table WHERE some_expr) |
| index_subquery |
在 某 些 IN 查 詢 中 使 用 此 種 類 型 , 與 unique_subquery 類似,但是查詢的是非唯一 性索引: value IN (SELECT key_column FROM single_table WHERE some_expr) |
| range |
只檢索給定范圍的行,使用一個索引來選擇 行.key 列顯示使用了哪個索引.當使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比較關鍵字列時,可 以使用 range. |
| index |
全表掃描,只是掃描表的時候按照索引次序 進行而不是行.主要優點就是避免了排序, 但是開銷仍然非常大. |
| all |
最壞的情況,從頭到尾全表掃描. |
| 項 |
說明 |
| possible_keys |
指出 MySQL 能在該表中使用哪些索引有助于 查詢.如果為空,說明沒有可用的索引. |
| 項 |
說明 |
| key |
MySQL 實際從 possible_key 選擇使用的索引. 如果為 NULL,則沒有使用索引.很少的情況 下,MYSQL 會選擇優化不足的索引.這種情 況下,可以在 SELECT 語句中使用 USE INDEX (indexname)來強制使用一個索引或者用 IGNORE INDEX(indexname)來強制 MYSQL 忽略索引 |
| 項 |
說明 |
| key_len |
使用的索引的長度.在不損失精確性的情況 下,長度越短越好. |
| 項 |
說明 |
| rows |
MYSQL 認為必須檢查的用來返回請求數據的行數 |
| 項 |
說明 |
| rows |
MYSQL 認為必須檢查的用來返回請求數據的行數 |
MYSQL學習extra 中出現以下 2 項意味著 MYSQL 根本不能使用索引,效率會受到重大影響.應盡可能對此進行優化.
| extra 項 |
說明 |
| Using filesort |
表示 MySQL 會對結果使用一個外部索引排序,而不是從表里按索引次序讀到相關內容.可能在內存或者磁盤上進行排序.MySQL 中無法利用索引完成的排序操作稱為“文件排序” |
| Using temporary |
表示 MySQL 在對查詢結果排序時使用臨時表.常見于排序 order by 和分組查詢 group by. |
MYSQL學習下面來舉一個例子來說明下 explain 的用法.
先來一張表:
代碼如下:
CREATE TABLE IF NOT EXISTS `article` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`author_id` int(10) unsigned NOT NULL,
`category_id` int(10) unsigned NOT NULL,
`views` int(10) unsigned NOT NULL,
`comments` int(10) unsigned NOT NULL,
`title` varbinary(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`)
);
再插幾條數據:
代碼如下:
INSERT INTO `article`
(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2'),
(1, 1, 3, 3, '3', '3');
需求:
查詢 category_id 為 1 且 comments 大于 1 的情況下,views 最多的 article_id.
先查查試試看:
代碼如下:
EXPLAIN
SELECT author_id
FROM `article`
WHERE category_id = 1 AND comments > 1
ORDER BY views DESC
LIMIT 1\G
看看部分輸出結果:
代碼如下:
*************************** 1. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: article
???????? type: ALL
possible_keys: NULL
????????? key: NULL
????? key_len: NULL
????????? ref: NULL
???????? rows: 3
??????? Extra: Using where; Using filesort
1 row in set (0.00 sec)
很顯然,type 是 ALL,即最壞的情況.Extra 里還出現了 Using filesort,也是最壞的情況.優化是必須的.
MYSQL學習嗯,那么最簡單的解決方案就是加索引了.好,我們來試一試.查詢的條件里即 where 之后共使用了 category_id,comments,views 三個字段.那么來一個聯合索引是最簡單的了.
代碼如下:
ALTER TABLE `article` ADD INDEX x ( `category_id` , `comments`, `views` );
結果有了一定好轉,但仍然很糟糕:
代碼如下:
*************************** 1. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: article
???????? type: range
possible_keys: x
????????? key: x
????? key_len: 8
????????? ref: NULL
???????? rows: 1
??????? Extra: Using where; Using filesort
1 row in set (0.00 sec)
type 變成了 range,這是可以忍受的.但是 extra 里使用 Using filesort 仍是無法接受的.但是我們已經建立了索引,為啥沒用呢?這是因為按照 BTree 索引的工作原理,先排序 category_id,如果遇到相同的 category_id 則再排序 comments,如果遇到相同的 comments 則再排序 views.當 comments 字段在聯合索引里處于中間位置時,因comments > 1 條件是一個范圍值(所謂 range),MySQL 無法利用索引再對后面的 views 部分進行檢索,即 range 類型查詢字段后面的索引無效.
那么我們需要拋棄 comments,刪除舊索引:
代碼如下:
?DROP INDEX x ON article;
然后建立新索引:
代碼如下:
ALTER TABLE `article` ADD INDEX y ( `category_id` , `views` ) ;
接著再運行查詢:
代碼如下:
*************************** 1. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: article
???????? type: ref
possible_keys: y
????????? key: y
????? key_len: 4
????????? ref: const
???????? rows: 1
??????? Extra: Using where
1 row in set (0.00 sec)
可以看到,type 變為了 ref,Extra 中的 Using filesort 也消失了,結果非常理想.
再來看一個多表查詢的例子.
首先定義 3個表 class 和 room.
代碼如下:
CREATE TABLE IF NOT EXISTS `class` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`card` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `book` (
`bookid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`card` int(10) unsigned NOT NULL,
PRIMARY KEY (`bookid`)
);
CREATE TABLE IF NOT EXISTS `phone` (
`phoneid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`card` int(10) unsigned NOT NULL,
PRIMARY KEY (`phoneid`)
) engine = innodb;
然后再分別插入大量數據.插入數據的php腳本：
代碼如下:
<?php
$link = mysql_connect("localhost","root","870516");
mysql_select_db("test",$link);
for($i=0;$i<10000;$i++)
{
??? $j?? = rand(1,20);
??? $sql = " insert into class(card) values({$j})";
??? mysql_query($sql);
}
for($i=0;$i<10000;$i++)
{
??? $j?? = rand(1,20);
??? $sql = " insert into book(card) values({$j})";
??? mysql_query($sql);
}
for($i=0;$i<10000;$i++)
{
??? $j?? = rand(1,20);
??? $sql = " insert into phone(card) values({$j})";
??? mysql_query($sql);
}
mysql_query("COMMIT");
?>
然后來看一個左連接查詢:
代碼如下:
explain select * from class left join book on class.card = book.card\G
分析結果是:
代碼如下:
*************************** 1. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: class
???????? type: ALL
possible_keys: NULL
????????? key: NULL
????? key_len: NULL
????????? ref: NULL
???????? rows: 20000
??????? Extra:
*************************** 2. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: book
???????? type: ALL
possible_keys: NULL
????????? key: NULL
????? key_len: NULL
????????? ref: NULL
???????? rows: 20000
??????? Extra:
2 rows in set (0.00 sec)
顯然第二個 ALL 是需要我們進行優化的.
建立個索引試試看:
代碼如下:
ALTER TABLE `book` ADD INDEX y ( `card`);
代碼如下:
*************************** 1. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: class
???????? type: ALL
possible_keys: NULL
????????? key: NULL
????? key_len: NULL
????????? ref: NULL
???????? rows: 20000
??????? Extra:
*************************** 2. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: book
???????? type: ref
possible_keys: y
????????? key: y
????? key_len: 4
????????? ref: test.class.card
???????? rows: 1000
??????? Extra:
2 rows in set (0.00 sec)
可以看到第二行的 type 變為了 ref,rows 也變成了 1741*18,優化比較明顯.這是由左連接特性決定的.LEFT JOIN 條件用于確定如何從右表搜索行,左邊一定都有,所以右邊是我們的關鍵點,一定需要建立索引.
刪除舊索引:
代碼如下:
DROP INDEX y ON book;
建立新索引.
代碼如下:
ALTER TABLE `class` ADD INDEX x ( `card`);
結果
代碼如下:
*************************** 1. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: class
???????? type: ALL
possible_keys: NULL
????????? key: NULL
????? key_len: NULL
????????? ref: NULL
???????? rows: 20000
??????? Extra:
*************************** 2. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: book
???????? type: ALL
possible_keys: NULL
????????? key: NULL
????? key_len: NULL
????????? ref: NULL
???????? rows: 20000
??????? Extra:
2 rows in set (0.00 sec)
基本無變化.
?????? 然后來看一個右連接查詢:
代碼如下:
explain select * from class right join book on class.card = book.card;
分析結果是:
代碼如下:
*************************** 1. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: book
???????? type: ALL
possible_keys: NULL
????????? key: NULL
????? key_len: NULL
????????? ref: NULL
???????? rows: 20000
??????? Extra:
*************************** 2. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: class
???????? type: ref
possible_keys: x
????????? key: x
????? key_len: 4
????????? ref: test.book.card
???????? rows: 1000
??????? Extra:
2 rows in set (0.00 sec)
優化較明顯.這是因為 RIGHT JOIN 條件用于確定如何從左表搜索行,右邊一定都有,所以左邊是我們的關鍵點,一定需要建立索引.
刪除舊索引:
代碼如下:
DROP INDEX x ON class;
建立新索引.
代碼如下:
ALTER TABLE `book` ADD INDEX y ( `card`);
結果
代碼如下:
*************************** 1. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: class
???????? type: ALL
possible_keys: NULL
????????? key: NULL
????? key_len: NULL
????????? ref: NULL
???????? rows: 20000
??????? Extra:
*************************** 2. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: book
???????? type: ALL
possible_keys: NULL
????????? key: NULL
????? key_len: NULL
????????? ref: NULL
???????? rows: 20000
??????? Extra:
2 rows in set (0.00 sec)
基本無變化.
MYSQL學習最后來看看 inner join 的情況:
代碼如下:
explain select * from class inner join book on class.card = book.card;
結果:
代碼如下:
*************************** 1. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: book
???????? type: ALL
possible_keys: NULL
????????? key: NULL
????? key_len: NULL
????????? ref: NULL
???????? rows: 20000
??????? Extra:
*************************** 2. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: class
???????? type: ref
possible_keys: x
????????? key: x
????? key_len: 4
????????? ref: test.book.card
???????? rows: 1000
??????? Extra:
2 rows in set (0.00 sec)
刪除舊索引:
代碼如下:
DROP INDEX y ON book;
結果
代碼如下:
*************************** 1. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: class
???????? type: ALL
possible_keys: NULL
????????? key: NULL
????? key_len: NULL
????????? ref: NULL
???????? rows: 20000
??????? Extra:
*************************** 2. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: book
???????? type: ALL
possible_keys: NULL
????????? key: NULL
????? key_len: NULL
????????? ref: NULL
???????? rows: 20000
??????? Extra:
2 rows in set (0.00 sec)
建立新索引.
代碼如下:
ALTER TABLE `class` ADD INDEX x ( `card`);
結果
代碼如下:
*************************** 1. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: class
???????? type: ALL
possible_keys: NULL
????????? key: NULL
????? key_len: NULL
????????? ref: NULL
???????? rows: 20000
??????? Extra:
*************************** 2. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: book
???????? type: ALL
possible_keys: NULL
????????? key: NULL
????? key_len: NULL
????????? ref: NULL
???????? rows: 20000
??????? Extra:
2 rows in set (0.00 sec)
綜上所述,inner join 和 left join 差不多,都需要優化右表.而 right join 需要優化左表.
MYSQL學習我們再來看看三表查詢的例子
MYSQL學習添加一個新索引：
代碼如下:
ALTER TABLE `phone` ADD INDEX z ( `card`);
ALTER TABLE `book` ADD INDEX y ( `card`);
代碼如下:
explain select * from class left join book on class.card=book.card left join phone on book.card = phone.card;
代碼如下:
*************************** 1. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: class
???????? type: ALL
possible_keys: NULL
????????? key: NULL
????? key_len: NULL
????????? ref: NULL
???????? rows: 20000
??????? Extra:
*************************** 2. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: book
???????? type: ref
possible_keys: y
????????? key: y
????? key_len: 4
????????? ref: test.class.card
???????? rows: 1000
??????? Extra:
*************************** 3. row ***************************
?????????? id: 1
? select_type: SIMPLE
??????? table: phone
???????? type: ref
possible_keys: z
????????? key: z
????? key_len: 4
????????? ref: test.book.card
???????? rows: 260
??????? Extra: Using index
3 rows in set (0.00 sec)
后 2 行的 type 都是 ref 且總 rows 優化很好,效果不錯.
MySql 中的 explain 語法可以幫助我們改寫查詢,優化表的結構和索引的設置,從而最大地提高查詢效率.當然,在大規模數據量時,索引的建立和維護的代價也是很高的,往往需要較長的時間和較大的空間,如果在不同的列組合上建立索引,空間的開銷會更大.
因此索引最好設置在需要經常查詢的字段中.
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/1661.html
