為什麼 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中文網其他相關文章！