使用索引，使用临时文件，使用文件排序-如何解决此问题? [英] Using index, using temporary, using filesort - how to fix this?

问题描述

我正在开发一个事件跟踪系统，该系统使用一些查找表以及主日志记录表.在我正在写的报告中，可以选择一个对象来查看统计信息.界面按重要性递减的顺序显示所有对象(即命中).

I'm working on a event tracking system which uses a handful of lookup tables as well as the primary logging table. In a report I'm writing, an object can be selected to view statistics against. The interface shows all objects in order of decreasing importance (ie, hits).

两个表的架构(略有减少，但您能理解以下要点):

The schema for the two tables (slightly trimmed down, but you get the gist):

CREATE TABLE IF NOT EXISTS `event_log` (
  `event_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(5) DEFAULT NULL,
  `object_id` int(5) DEFAULT NULL,
  `event_date` datetime DEFAULT NULL,
  PRIMARY KEY (`event_id`),
  KEY `user_id` (`user_id`),
  KEY `object_id` (`object_id`)
);

CREATE TABLE IF NOT EXISTS `lookup_event_objects` (
  `object_id` int(11) NOT NULL AUTO_INCREMENT,
  `object_desc` varchar(255) NOT NULL,
  PRIMARY KEY (`object_id`)
);

我遇到问题的查询如下.我的约100个条目的表可以正常工作，但是EXPLAIN令我有些担心.

The query I'm having trouble with is below. It works fine with my table of ~100 entries, but the EXPLAIN worries me a little.

    explain SELECT 
            el.object_id, 
            leo.object_desc, 
            COUNT(el.object_id) as count_rows
        FROM 
            event_log el 
            LEFT JOIN lookup_event_objects leo ON leo.object_id = el.object_id
        GROUP BY 
            el.object_id
        ORDER BY 
            count_rows DESC,
            leo.object_desc ASC

返回: Using index; Using temporary; Using filesort

那么-我的模式和/或查询MySQL依赖于temporary和filesort怎么了?还是使用ORDER BY获得的优化?

So -- what's wrong with my schema and/or query for MySQL to fall back on temporary and filesort? Or is it as optimized as it can get using ORDER BY?

推荐答案

好吧，文档给出了使用临时"出现时的确切原因:

Well, the doc gives the exact reasons when "Using temporary" will appear:

可以在以下条件下创建临时表:

Temporary tables can be created under conditions such as these:

如果有一个ORDER BY子句和另一个GROUP BY子句，或者 ORDER BY或GROUP BY包含表中除 连接队列中的第一个表，将创建一个临时表.

If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

DISTINCT与ORDER BY结合使用可能需要一个临时表.

DISTINCT combined with ORDER BY may require a temporary table.

如果使用SQL_SMALL_RESULT选项，则MySQL使用内存中 临时表，除非查询还包含元素(描述 稍后)，需要在磁盘上进行存储.

If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.

快速扫描显示您患有#1.

A quick scan shows that you suffer from #1.

还有此博客(来自2009年)说使用文件排序"意味着不能使用索引执行排序.由于您要按计算字段进行排序，所以这也将是正确的.

And this blog from 2009 says that "using filesort" means that the sort can't be performed with an index. Since you're ordering by a computed field, that's going to be true, too.

所以，这就是错误".

这篇关于使用索引，使用临时文件，使用文件排序-如何解决此问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！