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

首先一個, "請問如何優化，能讓這裡不用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)

Answer 2

filesort這個名字有誤解性，其實它就是sort，也就是order by找不到對應的index，只能把所有符合條件的資料找出來排序（與文件無關）。

要避免使用filesort，解決方法是提供一個有效的索引。鑑於你這個查詢牽涉到的資料很少，出現filesort也沒什麼問題，不過這種排序建議放到client端處理，減輕資料庫壓力。

但關鍵是WHERE ... IN (value_list) 這個結構它用不上索引，會導致全表掃描，這個問題比較大；改成id= 3 or id=2 or ...，則可用上primary key索引。 這一段有誤，忽略之。