有時候在開發程式會遇到一些效能上的瓶頸,可能是SQL語法下的不好或者是索引建立不當所造成,不過在初期開發資料量不大的時候,可能無法明顯的感覺到效能上的差異,我們可以透過EXPLAIN語句來分析查詢語句的效能,確保在開發初期就能使用有效率的SQL語句。EXPLAIN用法如下

 

EXPLAIN [EXTENDED] SELECT select_options

簡單的說,就是在你的查詢SELECT語法之前加上EXPLAIN就可以了

現在我們建立一個資料表users與一些測試資料如下

CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `sex` tinyint(4) NOT NULL,
  `age` tinyint(4) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

-- 
-- 列出以下資料庫的數據: `users`
-- 

INSERT INTO `users` VALUES (1, 'tom', 'tompw', 1, 18);
INSERT INTO `users` VALUES (2, 'john', 'johnpw', 1, 20);
INSERT INTO `users` VALUES (3, 'mary', 'marypw', 0, 17);
INSERT INTO `users` VALUES (4, 'claire', 'clairepw', 0, 24);

一開始我們除了id使用PRIMARY KEY之外其他欄位都沒有建立索引,接著我們實際下一些指令來測試

EXPLAIN SELECT * FROM `users` WHERE name = 'tom'

結果

 01.png     

EXPLAIN SELECT * FROM `users` WHERE id = 3

結果

 02.png

使用EXPLAIN語句後,會輸出一些分析數據,其中最主要看的是rows這個欄位,當我們搜索name時,由於沒有建立索引,所以他SCAN整個資料表總共跑了4次,相反的搜索id時則只跑了1次;所以可以簡單的把rows視為執行效能,越少越好。

我們進一步建立另一個資料表來做關聯測試

CREATE TABLE `articles` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) NOT NULL,
  `content` text NOT NULL,
  `user_id` int(11) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

-- 
-- 列出以下資料庫的數據: `articles`
-- 

INSERT INTO `articles` VALUES (1, 'Tom''s Post', 'Hello', 1, '2011-09-01');
INSERT INTO `articles` VALUES (2, 'Claire''s Post', 'Haha', 4, '2011-11-10');
INSERT INTO `articles` VALUES (3, 'Tom''s New Post', 'Good', 1, '2011-11-11');

假設我們想要找到使用者ID=1的人2011年10月之後有哪些文章,我們用了下面兩種不同語句來查詢

EXPLAIN SELECT * FROM `users` a, `articles` b WHERE b.date > '2011-10' AND a.id = b.user_id AND b.user_id = 1

結果

 03.png

EXPLAIN SELECT * FROM `users` a, (SELECT * FROM `articles` WHERE date > '2011-10') b WHERE a.id = b.user_id AND b.user_id = 1

結果

 04.png

雖然這兩個查詢的結果一樣,但是分析他們的效能會發現有明顯的不同,所以透過EXPLAIN的語句可以比較不同方式的查詢以得到改進。

文章標籤
創作者介紹

小殘的程式光廊

emn178 發表在 痞客邦 PIXNET 留言(0) 人氣()