MySQL: "Sending data"与 "Copying to tmp table"

Question

我有两个查询执行相同的操作并给出相同的结果集，但编写方式不同。查看他们的个人资料，他们执行的时间几乎相同:大约 0.075 秒

令人着迷的是，query#1 在“Copying to tmp table”状态下几乎占据了整个流程的执行时间。由于query#2几乎整个流程的执行时间都处于“发送数据”状态。

所以，我的问题是:

对于 MySQL 查询来说，“发送数据”或“复制到临时表”哪种状态更好？

query#1(执行时间处于“复制到临时表”状态)

SELECT ac.album_category_id, ac.name, ac2.album_category_id, ac2.name, ac2.description, t2.total
FROM album_category ac
JOIN album_category ac2 ON ac2.parent_album_category_id = ac.album_category_id
JOIN (
    SELECT a.album_category_id cat_id, SUM(result.count_image) total
    FROM album a
    JOIN (
        SELECT IFNULL(a.parent_album_id, a.album_id) p_album_id, COUNT(ai.image_id) count_image
        FROM album_image ai
        JOIN album a ON a.album_id = ai.album_id
        JOIN image i ON i.image_id = ai.image_id
        WHERE i.is_listed = 1
        GROUP BY p_album_id
    ) result ON result.p_album_id = a.album_id
    GROUP BY a.album_category_id
) t2 ON t2.cat_id = ac2.album_category_id
ORDER BY ac.position, ac2.position

query#2(执行时间处于“发送数据”状态)

SELECT ac.album_category_id, ac.name, ac2.album_category_id, ac2.name, ac2.description, t3.total
FROM album_category ac
JOIN album_category ac2 ON ac2.parent_album_category_id = ac.album_category_id
JOIN (
    SELECT a2.album_category_id cat_id, SUM(t2.sum_image) total
    FROM album a2
    JOIN (
        SELECT IFNULL(a.parent_album_id, a.album_id) p_album_id, SUM(t1.count_image) sum_image
        FROM album a
        JOIN (
            SELECT ai.album_id album_id, COUNT(ai.image_id) count_image
            FROM album_image ai
            JOIN image i ON i.image_id = ai.image_id
            WHERE i.is_listed = 1
            GROUP BY ai.album_id
        ) t1 ON t1.album_id = a.album_id
        GROUP BY p_album_id
    ) t2 ON t2.p_album_id = a2.album_id
    GROUP BY a2.album_category_id
) t3 ON t3.cat_id = ac2.album_category_id
ORDER BY ac.position, ac2.position

编辑

根据 jkavalik 的请求，我添加了说明。我不确定如何将其添加到文本中，所以我添加了图像。希望没问题。

解释查询#1

解释查询#2

Answer 1

Copying to tmp table:服务器正在复制到内存中的临时表。

发送数据:线程正在读取和处理 SELECT 语句的行，并将数据发送到客户端。由于在此状态期间发生的操作往往会执行大量磁盘访问(读取)，因此它通常是给定查询生命周期内运行时间最长的状态。

http://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html

大多数查询都会发送数据，但快速查询的查询状态变化非常快，您甚至不会注意到。然而，既然你注意到了，那么当时可能会发生大量的IO。当您运行该查询时，在终端窗口中还可以查看 iostat -x 10 并观察磁盘 IO 等待(假设您使用的是 Linux/Unix)。如果等待时间增加，则 MySQL 正在从磁盘读取数据，完成其工作，然后准备将数据推送到客户端。

复制到临时表意味着将查询或子查询的一部分结果移动到内存中，以便可以使用该临时数据(临时表)完成更多工作。每当我遇到这种状态时，就有优化的可能性。如果查询需要几秒钟才能返回，我会更关心这两种状态。我会根据您的情况优化查询 1。您可能想尝试增加 innodb_buffer_pool_size (假设您使用的是 innodb 引擎)

https://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html