问与答 mysql-使用索引,使用临时文件,使用文件排序-如何解决此问题?

robert · 2020-02-22 11:00:06 · 热度: 37

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

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

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让我有些担心。

    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

返回值:temporary

所以-我的模式和/或 MySQL 退回到temporaryfilesort怎么了? 还是使用ORDER BY获得的优化?

猜你喜欢:
共收到 3 条回复
twiggy #1 · 2020-02-22 11:00:06

好吧,文档给出了出现“使用临时”时的确切原因:

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

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

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

如果使用SQL_SMALL_RESULT选项,则MySQL使用内存中的   临时表,除非查询还包含元素(描述   稍后)需要磁盘存储。

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

2009年的这个博客说“使用文件排序”意味着不能使用索引执行排序。 由于您要按计算字段排序,所以这也将是正确的。

因此,这就是“错误”。

lanny #2 · 2020-02-22 11:00:07

已针对MySQL 5.7(src)更新:

服务器在以下条件下创建临时表:

  • 评估UNION语句,但稍后会有一些例外。

  • 评估某些视图,例如使用TEMPTABLE算法,UNION或聚合的视图。

  • 派生表的评估(FROM子句中的子查询)。

  • 为子查询或半联接实现创建的表(请参见第8.2.2节“优化子查询,派生表和视图引用”)。

  • 评估包含ORDER BY子句和另一个GROUP BY子句的语句,或者针对ORDER BY或GROUP BY包含联接队列中第一个表以外的表中的列的语句进行评估。

  • 结合ORDER BY对DISTINCT进行评估可能需要一个临时表。

  • 对于使用SQL_SMALL_RESULT修饰符的查询,MySQL使用内存中临时表,除非查询还包含需要磁盘存储的元素(稍后描述)。

  • 为了评估从同一表中选择并插入到该表中的INSERT ... SELECT语句,MySQL创建了一个内部临时表来保存SELECT中的行,然后将这些行插入目标表中。 请参见第13.2.5.1节“ INSERT ... SELECT语法”。

  • 评估多表UPDATE语句。

  • 计算GROUP_CONCAT()或COUNT(DISTINCT)表达式。

randal #3 · 2020-02-22 11:00:08

以下是创建临时表的以下条件。UNION查询使用临时表。

一些视图需要临时表,例如使用TEMPTABLE算法评估的临时表,或使用UNION或聚合的表。

如果有一个ORDER BY子句和另一个GROUP BY子句,或者ORDER BY或GROUP BY包含联接队列中第一个表以外的表中的列,则会创建一个临时表。

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

如果使用SQL_SMALL_RESULT选项,则MySQL使用内存中临时表,除非查询还包含需要磁盘存储的元素(稍后描述)。

通过mysql遵循此链接:[http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html]

需要 登录 后方可回复, 如果你还没有账号请点击这里 注册