(新葡京娱乐场) mysql order by instr排序的索引优化问题
澳门新葡京官网
澳门新葡京官网
当前位置 : 澳门新葡京官网 > 新葡京娱乐场

(新葡京娱乐场) mysql order by instr排序的索引优化问题

在mysql中,有表结构如下:

CREATE TABLE `s_cate` 
  `id` int10 unsigned NOT NULL auto_increment,
  `name` char100 NOT NULL default ,
  `alias` char20 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 instr3,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呢" -- 根据你的表结构和 sql, 这里无解. 因为order by instr3,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 instr3,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

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

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

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

栏目列表

广告位

澳门新葡京官网