SQL吧 网站源码下载 企业网站源码下载 免费网站源码下载

当前位置: 主页 > 教程 > 数据库 > Mysql教程 >

MySQL Explain命令用于查看执行效果

时间:2013-04-24 15:47来源:网络整理 作者:SQL吧 点击:
MySQL Explain命令用于查看执行效果
企业站建365全包!先制作!后付款!600多套模版任你选择!晴网www.138.la专注企业站建仿站、域名、空间、云主机、服务器, 咨询电话:020-29031983 qq:2769485357

  MySQL的Explain命令用于查看执行效果。虽然这个命令只能搭配select类型语句使用,如果你想查看update,delete类型语句中的索引效果,也不是太难的工作,只要保持条件不变,把类型转换成select就行了。

  explain的语法如下:

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

  如果使用了extended,那么在执行完explain语句后,可以使用show warnings语句查询相应的优化信息。

  mk-visual-explain对象扩展了explain,它提供了一种更直观的树形表示形式,使用方法很简单:

  mk-visual-explain

  mk-visual-explain -c

  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索引。

(责任编辑:编辑部)
顶一下
(0)
0%
踩一下
(0)
0%
0
------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
栏目列表
推荐内容