语法效能测试 Explain SQL

Database 最佳化: 语法效能测试 Explain SQL
Categories:
Tags:

MySQL

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，所以确切的语法分析要再看看文件真正的用法去决定

参考资料