mysql order by instr排序的索引优化问题

在mysql中，有表结构如下：

CREATE TABLE `s_cate` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` char(100) NOT NULL default '',
  `alias` char(20) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

比如有下面的数据在其中：

+----+------+-------+
| id | name | alias |
+----+------+-------+
|  1 | xxxx |       | 
|  2 | xxxx |       | 
|  3 | xxxx |       | 
|  4 | xxxx |       | 
|  5 | xxxx |       | 
|  6 | xxxx |       | 
|  7 | xxxx |       | 
|  8 | xxxx |       | 
+----+------+-------+

使用下面的语句查询时，结果如下：

explain select * from s_cate where id in (3,2,1,6,5,4) order by instr('3,2,1,6,5,4',id);
+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | s_cate | ALL  | PRIMARY       | NULL | NULL    | NULL |    8 | Using where; Using filesort | 
+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------------+

请问如何优化，能让这里不用到filesort呢？

补充一下请教公司dba的回答：

函数返回的结果是动态的，静态索引不起作用

---

filesort这个名字有误解性，实际上它就是sort，即order by找不到对应的index，只能把所有符合条件的数据找出来排序（与文件无关）。

要避免使用filesort，解决办法是提供一个有效的索引。鉴于你这个查询涉及到的数据很少，出现filesort也没什么问题，不过这种排序建议放到client端处理，减轻数据库压力。

但关键是 WHERE ... IN (value_list) 这个结构它用不上索引，会导致全表扫描，这个问题比较大；改成 id=3 or id=2 or ...，则可以用上primary key索引。这一段有误，忽略之。

---

首先一个, "请问如何优化，能让这里不用到filesort呢" -- 根据你的表结构和 sql, 这里无解. 因为order by instr('3,2,1,6,5,4',id), order by后面是一个函数, 而mysql里是没有函数索引的, 所以filesort必须有.

其次, 你这里没有用上索引, 也不是因为 WHERE 里有 IN (value_list), 而是你的表记录是在是太少了,全表扫描比走索引效率要高. 我测了一下, 用你的表结构, insert75条记录, 就走索引了. mysql 5.5.24

mysql> explain select * from s_cate where id in (3,2,1,6,5,4) order by instr('3,2,1,6,5,4',id);
+----+-------------+--------+-------+---------------+---------+---------+------------+-----------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra          |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | s_cate | range | PRIMARY       | PRIMARY | 4       | NULL |    6 | Using where; Using filesort |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from s_cate;
+----------+
| count(*) |
+----------+
|       75 |
+----------+
1 row in set (0.03 sec)