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的回答：
函数返回的结果是动态的，静态索引不起作用
PengUin
11 years, 2 months ago
Answers
首先一个, "请问如何优化，能让这里不用到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)
压力好大V
answered 11 years, 2 months ago