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

Question

在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的回答：

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

Answer 1

The first one is, "How can I optimize it so that filesort is not needed here?" -- According to your table structure and sql, there is no solution here. Because order by instr('3,2,1,6,5,4',id), order by is followed by a function, and there is no function index in mysql , so filesort must have.

Secondly, you did not use an index here, and it is not because there is IN (value_list) in WHERE, but because your table records are too few, and full table scanning is more efficient than indexing. I tested it. , use your table structure, insert 75 records, and use the index. 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)

Answer 2

filesortThe name is misleading. In fact, it is sort, that is, order by cannot find the corresponding index, so it can only All data that meets the criteria are found and sorted (regardless of the file).

To avoid using filesort, the solution is to provide a valid index. Since your query involves very little data, there is no problem with filesort. However, it is recommended that this kind of sorting be handled on the client side to reduce the pressure on the database.

But the key is that WHERE ... IN (value_list) does not use indexes for this structure, which will lead to a full table scan, which is a big problem; change it to id= 3 or id=2 or ..., you can use the primary key index. This paragraph is wrong, ignore it.