本文共 3008 字,大约阅读时间需要 10 分钟。
前记:很多东西看似简单,那是因为你并未真正了解它。
Explain命令用于查看执行效果。虽然这个命令只能搭配select类型语句使用,如果你想查看update,delete类型语句中的索引效果,也不是太难的事情,只要保持条件不变,把类型转换成select就行了。explain的语法如下:explain [extended] select ... from ... where ...如果使用了extended,那么在执行完explain语句后,可以使用show warnings语句查询相应的优化信息。==============================================================工具扩展了explain,它提供了一种更直观的树形表现形式,使用方法很简单:mk-visual-explain <file_containing_explain_output>mk-visual-explain -c <file_containing_query>mysql -e "explain select * from mysql.user" | mk-visual-explain也可以在MySQL命令行里通过设置pager的方式来执行:mysql> pager mk-visual-explainmysql> 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');缺省只建了一个主键,没有建其他的索引。测试时,如果你时间充裕,应该尽可能插入多一点的测试数据,怎么说也应该保证几千条。如果数据量过少,可能会影响MySQL在索引选择上的判断。如此一来,一旦产品上线,数据量增加。索引往往不会按照你的预想工作。下面让我们设置一个任务:查询category_id为1且comments大于1的情况下,views最多的article_id。问题很简单,SQL也很简单:SELECT author_idFROM `article`WHERE category_id = 1 AND comments > 1ORDER BY views DESCLIMIT 1下面让我们用explain命令查看索引效果:EXPLAIN SELECT author_idFROM `article`WHERE category_id = 1AND comments > 1ORDER BY views DESCLIMIT 1
这时explain部分结果如下:type: ALLkey: NULLExtra: Using where; Using filesort显示数据库进行了全表扫描,没有用到索引,并且在过程中文件排序。这样的结果肯定是糟糕的,下面让我们通过建立索引优化一下它:ALTER TABLE `article` ADD INDEX x ( `category_id` , `comments`, `views` ) ;这时explain部分结果如下:type: rangekey: xExtra: 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` ) ;这时explain部分结果如下:type: rangekey: xExtra: Using where; Using filesort很奇怪,系统无视我们刚建立的y索引,还使用x索引。导致仍然存在文件排序。如果你也出现了类似的情况,可以使用强制索引:EXPLAIN SELECT author_idFROM `article`FORCE INDEX ( y )WHERE category_id =1AND comments >1ORDER BY views DESCLIMIT 1这时explain部分结果如下:type: refkey: yExtra: Using where也可以删除x索引,那样系统会自动使用y索引(有时候MySQL比较傻,所以你得会使用FORCE INDEX)。后记:Explain的type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL一般来说,得保证查询至少达到range级别,最好能达到ref,否则就可能会出现性能问题。Explain的Extra信息也相当重要,如果此信息显示Using filesort或者Using temporary的话,噩梦即将开始。具体含义参见此链接: 转载地址:http://awszn.baihongyu.com/