mysql - 子查询执行时间 : localhost = 2 seconds/server = 98+ seconds

Question

我可以请求一些关于子查询的帮助吗？

当我在本地 XAMP mySQL 数据库上运行查询时，查询需要 2 秒才能完成。然而，在我的网络服务器上使用相同的数据库，相同的查询需要 98 秒以上才能返回相同的结果。

当我说 Identical 时，记录是从 HeidiSQL 导出和插入的，所以我确定数据集没问题。数据库 DDL 也是从 HeidiSQL 创建的，但我猜我可能错过了创建数据库的精确副本的一些关键步骤。

我创建了一个 fiddle同样使用 Heidi 的导出功能。尽管我应该指出查询在 fiddle 上的执行速度比现实生活中快得多。

我正在执行的查询是……

SELECT  d.dayID, d.dayDate, d.item, w.Idx, w.word, w.wordID, w.asize, w.span
FROM words w
    INNER JOIN days d ON w.dayID = d.dayID
    WHERE w.word IN (
            SELECT w1.word
            FROM words w1
                INNER JOIN days d1 ON w1.dayID = d1.dayID
            WHERE d1.dayDate = '2012-02-27'
                AND d1.Item = 'a'
                AND w1.span  = 24
                AND w1.asize = 6
            )
    AND w.span = 24
    AND w.asize = 6
GROUP BY d.dayDate, d.item 
Order by d.dayDate, w.asize DESC, w.Idx;

目的是从days表中返回一个Days和Items的列表，word表中有重复的单词。

上面的查询会返回类似这样的结果……

dayID   dayDate         item    Idx     word        wordID      asize   span
1974    2012-11-22      B       3       item b      1367339     6       24
4370    2015-03-10      B       1       item b      3024989     6       24

使用 phpMyAdmin，我为单词字段添加了一个索引，这使时间从 98 秒减少到 46 秒。但是，46 秒肯定也太长了吧？

其他需要注意的地方，现实中的words表大概有300万条记录。其他查询(非子查询)在眨眼间运行。我想我只是不擅长子查询。

请问有人能给我指出正确的方向，以确定为什么在服务器上执行查询需要这么长时间吗？

Answer 1

作为一般规则，在处理复杂或大的子查询时，您应该避免使用 IN。这是因为 IN 条件必须为数据源中的每一行计算一次。因此，如果您的数据源有 1000 行并且 IN 条件有 1000 个元素，则执行将如下所示:

• 对于第一行，运行子查询并检查该值是否在子查询结果中
• 对于第 2 行，运行子查询并检查该值是否在子查询结果中
• ...
• 等等

(不用说，如果子查询很复杂，那将是一个巨大的性能损失)

因此，您可以采取一些措施来加快速度:

1. 不使用IN，而是使用JOIN
2. 不使用子查询，而是创建一个临时表，添加适当的索引，然后使用JOIN

我将处理选项 2。如果需要，您可以用完整的子查询替换临时表。

那么，让我们创建一个临时表:

drop table if exists temp_words;
create temporary table temp_words
    SELECT w1.word
    FROM words w1
        INNER JOIN days d1 ON w1.dayID = d1.dayID
    WHERE d1.dayDate = '2012-02-27'
      AND d1.Item = 'a'
      AND w1.span  = 24
      AND w1.asize = 6;
alter table temp_words
    add index w(word);

现在，不使用 IN，而是使用 JOIN:

SELECT  d.dayID, d.dayDate, d.item, w.Idx, w.word, w.wordID, w.asize, w.span
FROM words w
    INNER JOIN days d 
            ON w.dayID = d.dayID
    INNER JOIN temp_words as w1 -- Replace 'temp_words' with your subquery 
                                -- if you don't want to use a temp table
            ON w.word = w1.word
WHERE w.span = 24
  AND w.asize = 6
GROUP BY d.dayDate, d.item 
Order by d.dayDate, w.asize DESC, w.Idx;

我想您会发现使用 JOIN 而不是 IN 会大大提高性能。

关于临时表你必须知道的事情:

1. 它们的行为类似于普通表，因此您可以像使用任何其他表一样使用它们:您可以插入、更新和删除行，您可以添加索引或以您想要(或需要)的任何方式更改它们，如果它们不再有用，您可以丢弃它们。
2. 它们只对创建它们的连接可见。这意味着两个连接可以创建具有相同名称(但可能具有不同结构)的临时表，并且每个连接都可以使用它自己的“副本”。
3. 一旦连接关闭或终止，它们就会被删除，因此如果您关闭或终止连接，您将不得不重新创建它们。