MySQL 使用索引，使用临时，使用文件排序 - 如何解决这个问题？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13633406/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using index, using temporary, using filesort - how to fix this?
提问by a coder
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`)
);
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.
我遇到问题的查询如下。它适用于我的约 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
Returns:
Using index; Using temporary; Using filesort
返回：
Using index; Using temporary; Using filesort
So -- what's wrong with my schema and/or query for MySQL to fall back on temporaryand filesort? Or is it as optimized as it can get using ORDER BY?
那么-有什么错我的架构和/或查询MySQL来依傍temporary和filesort？或者它是否像使用 ORDER BY 一样优化？
回答by Alain Collins
Well, the docgives the exact reasons when "Using temporary" will appear:
好吧，该文档给出了出现“使用临时”的确切原因：
Temporary tables can be created under conditions such as these:
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 combined with ORDER BY may require a temporary table.
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.
可以在以下条件下创建临时表：
如果有一个 ORDER BY 子句和一个不同的 GROUP BY 子句，或者如果 ORDER BY 或 GROUP BY 包含来自连接队列中第一个表以外的表的列，则会创建一个临时表。
DISTINCT 与 ORDER BY 结合可能需要临时表。
如果您使用 SQL_SMALL_RESULT 选项，MySQL 将使用内存中临时表，除非查询还包含需要磁盘存储的元素（稍后描述）。
A quick scan shows that you suffer from #1.
快速扫描显示您患有#1。
And this blogfrom 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.
而这个博客从2009年说：“使用文件排序”指的是那种不能用指数来进行。由于您是按计算域进行排序，因此也是如此。
So, that's what's "wrong".
所以，这就是“错误”。
回答by Erwin Wessels
Updated for MySQL 5.7 (src):
针对 MySQL 5.7 ( src)更新：
The server creates temporary tables under conditions such as these:
Evaluation of UNION statements, with some exceptions described later.
Evaluation of some views, such those that use the TEMPTABLE algorithm, UNION, or aggregation.
Evaluation of derived tables (subqueries in the FROM clause).
Tables created for subquery or semi-join materialization (see Section 8.2.2, “Optimizing Subqueries, Derived Tables, and View References”).
Evaluation of statements that contain an ORDER BY clause and a different GROUP BY clause, or for which the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue.
Evaluation of DISTINCT combined with ORDER BY may require a temporary table.
For queries that use the SQL_SMALL_RESULT modifier, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.
To evaluate INSERT ... SELECT statements that select from and insert into the same table, MySQL creates an internal temporary table to hold the rows from the SELECT, then inserts those rows into the target table. See Section 13.2.5.1, “INSERT ... SELECT Syntax”.
Evaluation of multiple-table UPDATE statements.
Evaluation of GROUP_CONCAT() or COUNT(DISTINCT) expressions.
服务器在以下条件下创建临时表：
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 节，“插入 ... SELECT 语法”。
多表 UPDATE 语句的评估。
GROUP_CONCAT() 或 COUNT(DISTINCT) 表达式的评估。
回答by Aman Aggarwal
These are the following conditions under which temporary tables are created. UNION queries use temporary tables.
这些是创建临时表的以下条件。UNION 查询使用临时表。
Some views require temporary tables, such those evaluated using the TEMPTABLE algorithm, or that use UNION or aggregation.
某些视图需要临时表，例如使用 TEMPTABLE 算法评估的表，或者使用 UNION 或聚合的视图。
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.
如果有一个 ORDER BY 子句和一个不同的 GROUP BY 子句，或者如果 ORDER BY 或 GROUP BY 包含来自连接队列中第一个表以外的表的列，则会创建一个临时表。
DISTINCT combined with ORDER BY may require a temporary table.
DISTINCT 与 ORDER BY 结合可能需要临时表。
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.
如果您使用 SQL_SMALL_RESULT 选项，MySQL 将使用内存中临时表，除非查询还包含需要磁盘存储的元素（稍后描述）。
Follow this link by mysql: http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html
按照 mysql 的这个链接：http: //dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html
