mysql - MySQL 中的 UNION ALL 性能不佳

Question

我有一个包含如下行的数据库:

+------------+---------+------------+-------+
| continent  | country | city       | value |
+------------+---------+------------+-------+
| Asia       | China   | Beijing    | 3     |
| ...        | ...     | ...        | ...   |
| N. America | USA     | D.C        | 7     |
| ....       | ....    | ....       | ....  |

为了生成树状图可视化，我需要将其转换为具有以下形状的表格:

+-----+------------+-------+
| uid | parent-uid | value |
+-----+------------+-------+

在本例中，Asia 是 China 的“父级”，而 China 是 Beijing 的“父级”。因此，对于这三个，您将拥有类似的东西:

+---------+--------+-----+
| Beijing | China  | 3   |
| China   | Asia   | ... |
| Asia    | global | ... |
+---------+--------+-----+

China 的“值”需要是所有子值的聚合。同样，Asia 的值需要是所有子值的聚合。

为了纯粹在 SQL 中完成此任务，我创建了以下三个查询并将它们与 UNION ALL 组合起来:

# City-level:
SELECT
     CONCAT(continent, "-", country, "-", city) as uid,
     CONCAT(continent, "-", country) as parentuid,
     value
FROM
     table

UNION ALL

# Country-level
SELECT
     CONCAT(continent, "-", country) as uid,
     continent as parentuid,
     SUM(value) as value
FROM
     table
GROUP BY
     country

UNION ALL

# Continent-level
SELECT
    continent as uid,
    "global" as parentuid,
    SUM(value) as value
FROM
    table
GROUP BY
    continent

每个单独的查询都会在几毫秒内完成。城市级、国家级、大洲级均在 < 0.01 秒内返回结果

当我将它们全部结合在一起时，突然需要 8 秒才能得到结果!

我尝试用谷歌搜索问题，但一切都只是说“使用 UNION ALL 而不是 UNION”(我已经是这样了)

我认为它可能没有足够的 RAM 来构建临时结果表，因此它会造成磁盘垃圾，但我不知道如何增加内存限制。我尝试将 innodb_buffer_pool_size 增加到 1GB (1073741824)，但没有帮助

Answer 1

第一个select，选择表中的所有行，然后获取第一行非常快，但获取所有行将花费很长时间(mysql工作台附加limit 1000 默认情况下到查询结束)。

要测试获取所有行是否需要更多时间，请尝试以下查询并告诉我们它消耗的时间:

select * from (
SELECT
     CONCAT(continent, "-", country, "-", city) as uid,
     CONCAT(continent, "-", country) as parentuid,
     value
FROM
     table
) t1;

如果花费了将近 8 秒，那么你的工会就没有问题。为了提高性能，您必须使用 where 子句来限制行。

希望对您有所帮助。