存在索引时，MySQL 5.1使用文件排序事件 [英] MySQL 5.1 using filesort event when an index is present

问题描述

可能我错过了一些愚蠢的事情……显然，即使存在与ORDER BY子句中的列完全匹配的索引，MySQL 5.1仍会继续执行Filesort.要发布到这里，我已经简化了数据模型，但是问题仍然在发生:

Probably I'm missing some silly thing... Apparently MySQL 5.1 keeps doing a Filesort even when there is an index that matches exactly the column in the ORDER BY clause. To post it here, I've oversimplified the data model, but the issue is still happening:

表定义:

CREATE TABLE `event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `owner_id` int(11) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `owner_id` (`owner_id`),
  KEY `date_created` (`date_created`),
  CONSTRAINT `event_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `user_profile` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

我的问题是，一个简单的SELECT事件显示正在使用文件排序":

My problem is that event a simple SELECT is showing "Using filesort":

explain select * from event order by date_created desc;

查询结果说明:

id  select_type table   type    possible_keys   key key_len ref rows    Extra   
1   SIMPLE      event   ALL NULL        NULL    NULL    NULL    6       Using filesort

这种查询是否可以使用进行文件排序的索引指令?

Is there any way for this type of queries to use the index insteas of doing a filesort?

在此先感谢大家.

推荐答案

由于您的CREATE TABLE语句表明您少于10行(AUTO_INCREMENT=7)，并且在我的安装中使用FORCE INDEX会使MySQL使用索引，我猜想优化器认为表扫描比索引扫描更快(较少的随机I/O)(因为您正在选择所有列，而不仅仅是date_created).确认如下:

Since your CREATE TABLE statement indicates that you have less than 10 rows (AUTO_INCREMENT=7) and using FORCE INDEX on my installation will make MySQL use the index, I'm guessing the optimizer thinks a table scan is faster (less random I/O) than an index scan (since you're selecting all columns, not just date_created). This is confirmed by the following:

mysql> explain select date_created from event order by date_created;
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
|  1 | SIMPLE      | event | index | NULL          | date_created | 9       | NULL |    1 | Using index |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
1 row in set (0.00 sec)

在上述情况下，索引扫描更快，因为仅需要返回索引列.

In the above case, the index scan is faster because only the indexed column needs to be returned.

MySQL文档在某些情况下使用索引的速度较慢:

The MySQL documentation has some cases where using an index is considered slower: http://dev.mysql.com/doc/refman/5.1/en/how-to-avoid-table-scan.html

这篇关于存在索引时，MySQL 5.1使用文件排序事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！