mysql order by多列_mysql 多列索和order by

多列索引的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