程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> Explain 命令用於查看執行效果

Explain 命令用於查看執行效果

編輯:關於MYSQL數據庫
      MySQL的Explain命令用於查看執行效果。雖然這個命令只能搭配select類型語句使用,如果你想查看update,delete類型語句中的索引效果,也不是太難的事情,只要保持條件不變,把類型轉換成select就行了。

explain的語法如下:

explain [extended] select ... from ... where ...

如果使用了extended,那麼在執行完explain語句後,可以使用show warnings語句查詢相應的優化信息。

mk-visual-explain工具擴展了explain,它提供了一種更直觀的樹形表現形式,使用方法很簡單:


mk-visual-explain <file_containing_explain_output>
mk-visual-explain -c <file_containing_query>
mysql -e "explain select * from MySQL.user" | mk-visual-explain

也可以在MySQL命令行裡通過設置pager的方式來執行:

MySQL> pager mk-visual-explain
MySQL> explain [extended] select ... from ... where ...

進入正題,為了讓例子更具體化,我們先來建一個表,插入一點測試數據:

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'); 

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');

缺省只建了一個主鍵,沒有建其他的索引。測試時,如果你時間充裕,應該盡可能插入多一點的測試數據,怎麼說也應該保證幾千條。如果數據量過少,可能會影響MySQL在索引選擇上的判斷。如此一來,一旦產品上線,數據量增加。索引往往不會按照你的預想工作。

下面讓我們設置一個任務:查詢category_id為1且comments大於1的情況下,vIEws最多的article_id。

問題很簡單,SQL也很簡單:

SELECT author_id  
FROM `article`  
WHERE category_id = 1 AND comments > 1  
ORDER BY vIEws DESC 
LIMIT 1 

SELECT author_id
FROM `article`
WHERE category_id = 1 AND comments > 1
ORDER BY vIEws DESC
LIMIT 1

下面讓我們用explain命令查看索引效果:


EXPLAIN SELECT author_id  
FROM `article`  
WHERE category_id = 1  
AND comments > 1  
ORDER BY vIEws DESC 
LIMIT 1 

EXPLAIN SELECT author_id
FROM `article`
WHERE category_id = 1
AND comments > 1
ORDER BY vIEws DESC
LIMIT 1

這時explain部分結果如下:


type: ALL
key: NULL
Extra: Using where; Using filesort

顯示數據庫進行了全表掃描,沒有用到索引,並且在過程中文件排序。這樣的結果肯定是糟糕的,下面讓我們通過建立索引優化一下它:


ALTER TABLE `article` ADD INDEX x ( `category_id` , `comments`, `vIEws` ) ; 

ALTER TABLE `article` ADD INDEX x ( `category_id` , `comments`, `vIEws` ) ;

這時explain部分結果如下:


type: range
key: x
Extra: Using where; Using filesort

雖然不再是全表掃描了,但是仍然存在文件排序。一般來說,文件排序都是由於ORDER BY語句一起的,而我們已經把views字段放到了聯合索引裡面,為什麼沒有效果呢?這是因為按照B-Tree的工作原理,先排序category_id,如果遇到相同的category_id則再排序comments,如果遇到相同的comments則再排序views。當comments字段在聯合索引裡處於中間位置時,因為comments > 1條件是一個范圍值,所以導致views部分索引無效。從這個意義上來說,此時的category_id, comments, vIEws聯合索引的效果不會比category_id, comments聯合索引的效果好。

文件排序是否有問題要視數據分布而定。一般來說應該盡可能避免出現它。可以這樣設置索引:


ALTER TABLE `article` ADD INDEX y ( `category_id` , `vIEws` ) ; 

ALTER TABLE `article` ADD INDEX y ( `category_id` , `vIEws` ) ;

這時explain部分結果如下:


type: range
key: x
Extra: Using where; Using filesort

很奇怪,系統無視我們剛建立的y索引,還使用x索引。導致仍然存在文件排序。

如果你也出現了類似的情況,可以使用強制索引:


EXPLAIN SELECT author_id  
FROM `article`  
FORCE INDEX ( y )  
WHERE category_id =1  
AND comments >1  
ORDER BY vIEws DESC 
LIMIT 1 

EXPLAIN SELECT author_id
FROM `article`
FORCE INDEX ( y )
WHERE category_id =1
AND comments >1
ORDER BY vIEws DESC
LIMIT 1

這時explain部分結果如下:


type: ref
key: y
Extra: Using where

當然,也可以刪除x索引,那樣系統會自動使用y索引。

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