mysql 优化(消除filesort)

针对select * from table where clo1> number order by clo2 desc。
其实按照常规的方法可以这样设计：key(clo1, clo2)
但是这种办法在mysql里不算是理想的，where条件里限定索引前部分是一个范围的情况下后面的order by还是会有filesort。如果where条件里限定索引前部分是一个常量，那么order by就会有效利用索引。例如：select * from table where clo1= number order by clo2 desc，explain的结果就不错。
为了让它能够利用上索引并且消除filesort，可以这样设计
索引：key(clo2,clo1);
select * from table where clo2> min_value and clo1> number order by clo2 desc;
这里where条件里同时执行了索引的两个列，并且为了保证逻辑一致，对clo2列的限定条件等效于无限定。

这样mysql就能很好的利用索引了。这个技巧在mysql high performance2里也有提过.

测试数据库

CREATE TABLE `test` (
  `id` int(10) NOT NULL auto_increment,
  `clo1` int(10) NOT NULL default '0',
  `clo2` int(10) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `cl21` (`clo2`,`clo1`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

条件一
EXPLAIN SELECT * FROM account_list WHERE `clo1` >1 ORDER BY `clo2` DESC ;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	account_list	range	cl12	cl12	4	NULL	80840	Using where; Using index; Using filesort

条件二

EXPLAIN SELECT * FROM account_list WHERE `clo2` >0 AND `clo1` >1 ORDER BY `clo2` DESC ;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	account_list	range	cl21	cl21	4	NULL	80840	Using where; Using index