MySQL 5.1 在存在索引时使用 filesort 事件

发布于 2024-11-09 18:44:23 字数 956 浏览 2 评论 0原文

也许我错过了一些愚蠢的事情......显然，即使有一个索引与 ORDER BY 子句中的列完全匹配，MySQL 5.1 也会继续执行文件排序。在这里发布它，我已经过度简化了数据模型，但问题仍然发生：

表定义：

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 事件显示“使用文件排序”：

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

这种类型的查询有什么方法可以使用索引而不是进行文件排序吗？

预先感谢大家。

原文

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:

Table definition:

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;

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

explain select * from event order by date_created desc;

And the result for the query explain:

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?

Thanks in advance to everybody.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

怎会甘心 2024-11-16 18:44:23

由于您的 CREATE TABLE 语句表明您的行数少于 10 行 (AUTO_INCRMENT=7)，并且在我的安装中使用 FORCE INDEX 将使 MySQL 使用索引，我猜测优化器认为表扫描比索引扫描更快（随机 I/O 更少）（因为您选择的是所有列，而不仅仅是 date_created）。以下情况证实了这一点：

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)

在上述情况下，索引扫描速度更快，因为只需要返回索引列。

MySQL 文档在某些情况下使用索引被认为较慢： http://dev.mysql.com/doc/refman/5.1/en/how-to-avoid-table-scan.html

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.

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

回复收藏 0 原文

吻泪 2024-11-16 18:44:23

问：有没有办法让这种类型的查询使用索引而不是进行文件排序？

答：如果可能的话，要让 MySQL 使用索引，请尝试：

EXPLAIN SELECT * FROM event FORCE INDEX (date_created) ORDER BY date_created DESC;

通过使用 FORCE INDEX (index_name)，这告诉 MySQL 在可能的情况下使用索引。如果没有该指令，MySQL 将选择最有效的方式返回结果集。文件排序可能比使用索引更有效。

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

A: To have MySQL use the index if at all possible, try:

EXPLAIN SELECT * FROM event FORCE INDEX (date_created) ORDER BY date_created DESC;

By using the FORCE INDEX (index_name), this tells MySQL to make use of the index if it's at all possible. Absent that directive, MySQL will choose the most efficient way to return the result set. A filesort may be more efficient than using the index.

回复收藏 0 原文

~没有更多了~