MySQL EXPLAIN - 效能分析語句 | 小殘的程式光廊

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

1	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 之外其他欄位都沒有建立索引，接著我們實際下一些指令來測試

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

結果

1	EXPLAIN SELECT * FROM `users` WHERE id = 3

結果

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