MySQL 查询在/tmp 中使用了 10G 空间并因 'Errcode: 28 - No space left on device' 而终止，但在本地运行良好

Question

我正在运行一个相当复杂的 SQL 语句，从一个包含原始数据的大表(3800 万行)创建一个汇总表。 (我正在尝试将当前、本季低价、本季高价、本周/月/季节价格已上涨 1 分的百分比放入缓存表中，以便稍后查询。)

INSERT INTO cache (`time`, name, price, low, high, week, month, season)
    SELECT
        MAX(`time`) AS `time`,
        name,
        MIN(CASE WHEN `time` = 1498511444 THEN price ELSE 999999 END) AS price,
        MIN(price) AS low,
        MAX(price) AS high,
        SUM(CASE WHEN `time` > 1497906644 AND price = 1 THEN 1 ELSE 0 END) / SUM(CASE WHEN `time` > 1497906644 THEN 1 ELSE 0 END) AS week,
        SUM(CASE WHEN `time` > 1480367444 AND price = 1 THEN 1 ELSE 0 END) / SUM(CASE WHEN `time` > 1480367444 THEN 1 ELSE 0 END) AS month,
        SUM(CASE WHEN `time` > 1493362800 AND price = 1 THEN 1 ELSE 0 END) / SUM(CASE WHEN `time` > 1493362800 THEN 1 ELSE 0 END) AS season
    FROM
        (SELECT
            `time`,
            name,
            MIN(price) AS price
        FROM price
        WHERE `time` > 1493362800
        GROUP BY `time`, name) AS tmp
    GROUP BY name

在 Price.time 列上添加索引后，我设法将本地时间降低到 0.6 秒(之前需要 30 秒)。在产品上(具有相同的索引)，需要很长时间(30秒以上)，然后失败并显示错误代码:28 - 设备上没有剩余空间。如果我在 df 运行时观看它，我会看到可用空间以大约 3MB/s 的速度从 9.9G 慢慢减少到 9.6G。然后几分钟后，可用空间突然开始下降 500MB/s，直到没有剩余空间并且查询失败。在本地，可用空间中似乎没有任何 Blob ，尽管我猜它可能太快了，以至于我的 df 在 while 循环中看不到它。

如果我尝试首先创建一个包含子查询结果的表，我也会遇到占用磁盘的行为:

INSERT INTO initial_cache (`time`, name, price)
SELECT
    `time`,
    name,
    MIN(price) AS price
FROM price
WHERE `time` > 1493337600
GROUP BY `time`, name

你知道为什么我的查询需要这么多空间来运行吗？为什么它在产品上的表现会如此不同？

谢谢!

Answer 1

子查询在内存不足时往往会使用大量临时空间。然而，有一部分有点多余:初始子查询后的时间检查:重写给出(其中 SUM(1) 很奇怪):

INSERT INTO cache (`time`, name, price, low, high, week, month, season)
SELECT
    MAX(`time`) AS `time`,
    name,
    MIN(price) AS price,
    MIN(price) AS low,
    MAX(price) AS high,
    SUM(CASE WHEN price = 1 THEN 1 ELSE 0 END) / SUM(1) AS week,
    SUM(CASE WHEN price = 1 THEN 1 ELSE 0 END) / SUM(1) AS month,
    SUM(CASE WHEN price = 1 THEN 1 ELSE 0 END) / SUM(1) AS season
FROM
    (SELECT
        `time`,
        name,
        MIN(price) AS price
    FROM price
    WHERE `time` > 1498442022
    GROUP BY `time`, name) AS tmp
GROUP BY name;

这可能相当于:

INSERT INTO cache (`time`, name, price, low, high, week, month, season)
SELECT
    MAX(`time`) AS `time`,
    name,
    MIN(price) AS price,
    MIN(price) AS low,
    MAX(price) AS high,
    SUM(CASE WHEN price = 1 THEN 1 ELSE 0 END) / SUM(1) AS week,
    SUM(CASE WHEN price = 1 THEN 1 ELSE 0 END) / SUM(1) AS month,
    SUM(CASE WHEN price = 1 THEN 1 ELSE 0 END) / SUM(1) AS season
FROM price
WHERE `time` > 1498442022    
GROUP BY name;

但是，由于外部查询的重写看起来很奇怪，我怀疑这是否是您正在寻找的结果:提供数据和预期结果以获得更好的答案。