使用索引，使用临时，使用文件排序 - 如何解决这个问题?

2021-11-20 00:00:00 optimization select mysql temporary filesort

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

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

返回:使用索引；使用临时；使用文件排序

那么——我的模式和/或 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.

所以，这就是错误".

相关文章