Explain 做 SQL SELECT 語法效能測試

在MySQL我們在使用 SELECT 做撈取資料的時候,有時候常常會效能低落,撈取資料需要很長的時間,有時候是 SQL 語法下得不好導致沒有使用到正確的索引去撈資料,我們這個時候就必須要檢查我們下的 SQL 語法到底有哪些地方需要改善,我建立的 comment 的資料表並新增幾筆假資料去做示範

-- 建立資料表

-- 留言
CREATE TABLE IF NOT EXISTS `comment` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '編號',
  `content` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT '留言',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

-- 使用者
CREATE TABLE IF NOT EXISTS `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '編號',
  `name` varchar(30) COLLATE utf8_unicode_ci NOT NULL COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

-- 使用者的留言
CREATE TABLE IF NOT EXISTS `user_comment` (
  `user_id` int(10) unsigned NOT NULL COMMENT '使用者編號',
  `comment_id` int(10) unsigned NOT NULL COMMENT '評論編號',
  PRIMARY KEY (`user_id`,`comment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- 新增資料

-- 留言
INSERT INTO `comment` (`id`, `content`) VALUES
(1, '留言1'),
(2, '留言2');

-- 使用者
INSERT INTO `user` (`id`, `name`) VALUES
(1, '使用者1'),
(2, '使用者2');

-- 使用者的留言
INSERT INTO `user_comment` (`user_id`, `comment_id`) VALUES
(1, 1),
(1, 2);




-- 解釋MySQL語法效能

-- 撈取留言資料
EXPLAIN SELECT * FROM  `comment` WHERE  id` =2;

-- 撈取使用者的留言資料
EXPLAIN SELECT *
FROM  `comment` c,  `user` u,  `user_comment` uc
WHERE u.`id` = uc.`user_id`
AND uc.`comment_id` = c.`id`

解釋 MySQL 語法效能:撈取留言資料

Explain MySQL SQL

解釋MySQL語法效能:撈取留言資料

解釋MySQL語法效能:撈取留言資料

而EXPLAIN後的資料有下面這些欄位

select_type

table

關連到的資料表

type

使用關聯查詢的類型(效率由好至壞排序)

  • System
  • const
  • eq_ref
  • ref
  • fulltext
  • ref_or_null
  • index_merge
  • unique_subquery
  • index_subquery
  • range
  • index
  • ALL

possible_keys

可能使用到的索引,從WHERE語法選擇出一個適合的欄位

key

實際使用到的索引,如果為NULL,則是沒有使用索引

key_len

使用索引的長度,長度越短 準確性越高

ref

顯示那一列的索引被使用,一般是一個常數(const)

rows

MySQL用來返回資料的筆數,可以簡單的把rows視為執行效能,越少越好

Extra

MySQL用來解析額外的查詢訊息

  • Distinct

當MySQL找到相關連的資料時,就不再搜尋。

  • Not exists

MySQL優化 LEFT JOIN,一旦找到符合的LEFT JOIN資料後,就不再搜尋。

  • Range checked for each Record(index map:#)

無法找到理想的索引。此為最慢的使用索引。

  • Using filesort

當出現這個值時,表示此SELECT語法需要優化。因為MySQL必須進行額外的步驟來進行查詢。

  • Using index

返回的資料是從索引中資料,而不是從實際的資料中返回,當返回的資料都出現在索引中的資料時就會發生此情況。

  • Using temporary

同Using filesort,表示此SELECT語法需要進行優化。此為MySQL必須建立一個暫時的資料表(Table)來儲存結果,此情況會發生在針對不同的資料進行ORDER BY,而不是GROUP BY。

  • Using where

使用WHERE語法中的欄位來返回結果。

  • System

system資料表,此為const連接類型的特殊情況。

  • Const

資料表中的一個記錄的最大值能夠符合這個查詢。因為只有一行,這個值就是常數,因為MySQL會先讀這個值然後把它當做常數。

  • eq_ref

MySQL在連接查詢時,會從最前面的資料表,對每一個記錄的聯合,從資料表中讀取一個記錄,在查詢時會使用索引為主鍵或唯一鍵的全部。

  • ref

只有在查詢使用了非唯一鍵或主鍵時才會發生。

  • range

使用索引返回一個範圍的結果。例如:使用大於>或小於<查詢時發生。

  • index

此為針對索引中的資料進行查詢。

  • ALL

針對每一筆記錄進行完全掃描,此為最壞的情況,應該盡量避免。

結論

MySQL 的 Explain 可以分析大部份的 SQL 語法效能,但有些語法像是 WHERE IN 則會被歸類為 range 的語法,但實際上則是 Using Where,所以確切的語法分析要再看看文件真正的用法去決定

參考資料

results matching ""

    No results matching ""