mysql - 我如何解决此 SQL 的 "Using filesort"？

Question

有表格:

CREATE TABLE `deal_keyword` (
  `deal_id` int(11) unsigned NOT NULL default '0',
  `keyword_id` int(11) unsigned NOT NULL default '0',
  `area_id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`deal_id`,`keyword_id`),
  KEY `area_id` (`area_id`,`keyword_id`,`deal_id`)
) TYPE=MyISAM;

并输入一些记录进行测试:

insert into deal_keyword values(1,2,3);
insert into deal_keyword values(2,2,3);
insert into deal_keyword values(3,2,3);
insert into deal_keyword values(4,1,3);
insert into deal_keyword values(5,1,3);
insert into deal_keyword values(6,1,3);
insert into deal_keyword values(7,3,3);
insert into deal_keyword values(8,3,3);

当我运行这个 SQL 时:

explain select * from `deal_keyword` where `area_id`=3 && `keyword_id` in(1,3) order by `deal_id`;

+--------------+-------+---------------+---------+---------+------+------+------------------------------------------+
| table        | type  | possible_keys | key     | key_len | ref  | rows | Extra                                    |
+--------------+-------+---------------+---------+---------+------+------+------------------------------------------+
| deal_keyword | range | area_id       | area_id |       8 | NULL |    4 | Using where; Using index; Using filesort |
+--------------+-------+---------------+---------+---------+------+------+------------------------------------------+

如何解决此 SQL 的“使用文件排序”问题？

感谢您的帮助:)

Answer 1

在 (area_id, deal_id, keyword_id) 上创建复合索引(按此顺序)

请注意，如果您的表中有其他列，对于没有 LIMIT 的 ORDER BY，索引扫描不一定比文件排序更好，因为遍历索引需要表查找，这意味着随机查找并且效率不高。