为什么 MySQL 查询显示'正在使用临时文件”和'正在使用文件排序”以及如何优化它们？

MySQL 优化：理解“使用索引、使用临时、使用文件排序”

执行查询时，MySQL 可能会求助于使用临时表和文件排序，如消息“使用临时”和“使用文件排序”所示。让我们探讨一下这在特定查询的上下文中意味着什么：

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

“使用临时”

根据 MySQL 文档，临时表在以下情况下创建：

• 存在 ORDER BY 子句和不同的 GROUP BY 子句，或者 ORDER BY 或 GROUP BY 包含连接队列中第一个表以外的表中的列。

在给定的查询中，ORDER BY 子句按计算字段“count_rows”排序，该字段不在 GROUP BY 子句中，并且 GROUP BY 子句使用连接表“lookup_event_objects”中的列。因此，MySQL 创建一个临时表来执行分组和排序。

“使用文件排序”

当无法使用索引执行排序操作时，MySQL 会使用文件排序。在这个查询中，计算字段“count_rows”上的排序无法建立索引，因此MySQL使用filesort来执行排序。

优化

要优化这个查询，您可以在“count_rows”上创建索引。但是，由于它是计算字段，因此您需要使用 MySQL 技巧：

CREATE TABLE lookup_event_objects_temp AS
SELECT
    object_id,
    object_desc,
    COUNT(DISTINCT event_id) AS count_rows
FROM
    event_log
GROUP BY
    object_id;

CREATE INDEX idx_count_rows ON lookup_event_objects_temp (count_rows);

ALTER TABLE lookup_event_objects_temp RENAME TO lookup_event_objects;

通过创建带有索引“count_rows”字段的临时表，查询可以使用索引来高效执行排序，消除了文件排序的需要并减少了临时表的使用。

以上是为什么 MySQL 查询显示'正在使用临时文件”和'正在使用文件排序”以及如何优化它们？的详细内容。更多信息请关注PHP中文网其他相关文章！