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

Question

在mysql中，有表结构如下：CREATETABLE`s_cate`(`id`int(10)unsignedNOTNULLauto_increment,`name`char(100)NOTNULLdefault'',`alias`char(20)NOTNULLdefault'',PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=9DEFAULTCHARSET=utf8比如有下面的数据在其中：+----+------+-------+|id|name|alias|+----+------+-------+|1|xxxx|||2|xxxx|||3|xxxx|||4|xxxx|||5|xxxx|||6|xxxx|||7|xxxx|||8|xxxx||+----+------+-------+使用下面的语句查询时，结果如下：explainselect*froms_catewhereidin(3,2,1,6,5,4)orderbyinstr('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|Usingwhere;Usingfilesort|+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------------+请问如何优化，能让这里不用到filesort呢？补充一下请教公司dba的回答：函数返回的结果是动态的，静态索引不起作用

Answer 1

filesort这个名字有误解性，实际上它就是sort，即orderby找不到对应的index，只能把所有符合条件的数据找出来排序（与文件无关）。
要避免使用filesort，解决办法是提供一个有效的索引。鉴于你这个查询涉及到的数据很少，出现filesort也没什么问题，不过这种排序建议放到client端处理，减轻数据库压力。
但关键是WHERE...IN(value_list)这个结构它用不上索引，会导致全表扫描，这个问题比较大；改成id=3orid=2or...，则可以用上primarykey索引。这一段有误，忽略之。