多列索引的select * where * order by * 查询条件索引很难建立,需要不断调试,最后找出规则:
三原则:
1. 将经常要查询的字段放到多列索引的前面
2. 将要进行等值查询的字段放到多列索引前面
3. order by field的列放到多列索引的最后
以下是测试
先来一张表:
CREATE TABLE IF NOT EXISTS `article` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`uid` 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`
(`uid`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2'),
(1, 1, 3, 3, '3', '3');
需求:
查询category_id为1且comments大于1的情况下,views最多的uid。
先查查试试看:
EXPLAINSELECT uid FROM `article` WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
看看部分输出结果:
很显然,type是ALL,即最坏的情况。Extra里还出现了Using filesort,也是最坏的情况。优
化是必须的。
嗯,那么最简单的解决方案就是加索引了。好,我们来试一试。查询的条件里即where之
后共使用了category_id,comments,views三个字段。那么来一个联合索引是最简单的了。
ALTER TABLE `article` ADD INDEX x ( `category_id` , `comments`, `views` );
结果有了一定好转,但仍然很糟糕:
type变成了range,这是可以忍受的。但是extra里使用Using filesort仍是无法接受的。
但是我们已经建立了索引,为啥没用呢?
这是因为按照BTree索引的工作原理,先排序category_id,如果遇到相同的category_id则再
排序comments,如果遇到相同的comments则再排序views。当comments字段在联合索引
里处于中间位置时,因为comments > 1条件是一个范围值(所谓range),MySQL无法利用
索引再对后面的views部分进行检索,即range类型查询字段后面的索引无效。
那么我们需要抛弃comments,删除旧索引DROP INDEX x ON article;
然后建立新索引:ALTER TABLE `article` ADD INDEX y ( `category_id` , `views` ) ;
接着再运行查询:
可以看到,type 变为了ref,Extra中的Using filesort也消失了,结果非常理想。
http://hi.baidu.com/jqxw4444/item/e3cf91393fa89f9bb80c0388