排序时与where子句不同，有什么避免文件排序的方法? [英] Any way to avoid a filesort when order by is different to where clause?

问题描述

我有一个非常简单的查询(表类型为InnoDb)，并且EXPLAIN说MySQL必须做额外的遍历才能找出如何按排序顺序检索行.

I have an incredibly simple query (table type InnoDb) and EXPLAIN says that MySQL must do an extra pass to find out how to retrieve the rows in sorted order.

SELECT * FROM `comments` 
WHERE (commentable_id = 1976) 
ORDER BY created_at desc LIMIT 0, 5

确切的解释输出:

table   select_type     type    extra                          possible_keys   key             key length   ref     rows
comments   simple      ref   using where; using filesort    common_lookups  common_lookups  5   const   89

commentable_id已建立索引.注释没有任何技巧，只是内容字段.

commentable_id is indexed. Comments has nothing trick in it, just a content field.

该手册建议，如果排序依据与位置不同，则无法避免文件排序.

The manual suggests that if the order by is different to the where, there is no way filesort can be avoided.

http://dev.mysql.com /doc/refman/5.0/en/order-by-optimization.html

我还尝试了按id排序以及等效的排序，但是即使我将id添加为索引也没有区别(我知道这不是必需的，因为id在MySQL中是隐式索引的.)

I also tried order by id as well as it's equivalent but makes no difference, even if I add id as an index (which I understand is not required as id is indexed implicitly in MySQL).

提前感谢您的任何想法！

thanks in advance for any ideas!

要标记-这是显示创建表

To Mark -- here's SHOW CREATE TABLE

CREATE TABLE `comments` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) default NULL,
  `commentable_type` varchar(255) default NULL,
  `commentable_id` int(11) default NULL,
  `content` text,
  `created_at` datetime default NULL,
  `updated_at` datetime default NULL,
  `hidden` tinyint(1) default '0',
  `public` tinyint(1) default '1',
  `access_point` int(11) default '0',
  `item_id` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `created_at` (`created_at`),
  KEY `common_lookups` (`commentable_id`,`commentable_type`,`hidden`,`created_at`,`public`),
  KEY `index_comments_on_item_id` (`item_id`),
  KEY `index_comments_on_item_id_and_created_at` (`item_id`,`created_at`),
  KEY `index_comments_on_user_id` (`user_id`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31803 DEFAULT CHARSET=latin1

推荐答案

请注意，MySQL术语 filesort 不一定表示它已写入磁盘.这只是意味着它将不使用索引而进行排序.如果结果集足够小，MySQL将在内存中对其进行排序，这比磁盘I/O快了几个数量级.

Note that the MySQL term filesort doesn't necessarily mean it writes to the disk. It just means it's going to sort without using an index. If the result set is small enough, MySQL will sort it in memory, which is orders of magnitude faster than disk I/O.

您可以使用

You can increase the amount of memory MySQL allocates for in-memory filesorts using the sort_buffer_size server variable. In MySQL 5.1, the default sort buffer size is 2MB, and the maximum you can allocate is 4GB.

更新:关于乔纳森·莱夫勒(Jonathan Leffler)关于测量排序所需时间的评论，您可以学习如何使用

update: Regarding Jonathan Leffler's comment about measuring how long the sorting takes, you can learn how to use SHOW PROFILE FOR QUERY which will give you the breakdown of how long each phase of query execution takes.

这篇关于排序时与where子句不同，有什么避免文件排序的方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！