两个查询比一个查询更快吗？

Question

我有一个表格，其中包含列：

CREATE TABLE aggregates (
    a VARHCAR,
    b VARCHAR,
    c VARCHAR,
    metric INT
    KEY test (a, b, c, metric)
);

如果我执行这样的查询：

SELECT b, c, SUM(metric) metric
FROM aggregates
WHERE a IN ('a', 'couple', 'of', 'values')
GROUP BY b, c
ORDER BY b, c

这个查询需要10秒钟，解释如下：

+----+-------------+------------+-------+---------------+------+---------+------+--------+-----------------------------------------------------------+
| id | select_type | table      | type  | possible_keys | key  | key_len | ref  | rows   | Extra                                                     |
+----+-------------+------------+-------+---------------+------+---------+------+--------+-----------------------------------------------------------+
|  1 | SIMPLE      | aggregates | range | test          | test | 767     | NULL | 582383 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+------------+-------+---------------+------+---------+------+--------+-----------------------------------------------------------+

如果我还按列a分组/排序，这样就不需要临时/文件排序，但是然后在另一个查询中我自己做同样的事情：

SELECT b, c, SUM(metric) metric
FROM (
    SELECT a, b, c, SUM(metric) metric
    FROM aggregates
    WHERE a IN ('a', 'couple', 'of', 'values')
    GROUP BY a, b, c
    ORDER BY a, b, c
) t
GROUP BY b, c
ORDER BY b, c

查询耗时1秒，执行计划如下：

+----+-------------+------------+-------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table      | type  | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+------------+-------+---------------+------+---------+------+--------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL | NULL    | NULL |    252 | Using temporary; Using filesort |
|  2 | DERIVED     | aggregates | range | test          | test | 767     | NULL | 582383 | Using where; Using index        |
+----+-------------+------------+-------+---------------+------+---------+------+--------+---------------------------------+

为什么会这样呢？如果我在一个查询中进行分组，而不是在外部查询中进行分组，为什么后者更快呢？

- Jaka Jančar

---

这两个查询结果相同吗？你在第二个查询中忘记了metric吗？ - ypercubeᵀᴹ

在我看来，这很可能与MySQL的实现方式有关，而在其他数据库引擎上可能不会像这样工作。 - m0skit0

你确定性能差别不仅仅是数据库缓存的问题吗？ - Nick Clark

在MySQL中，当你使用GROUP BY b,c时，不需要声明ORDER BY b,c。 - ypercubeᵀᴹ

关于缓存：在刚重启的服务器上，先执行查询 #2，然后再执行查询 #1，第二个查询仍然要快得多。 - Jaka Jančar

Answer 1

SQL的工作方式是，每个步骤所拥有的数据越少，查询速度就越快。因为你首先在内部查询中进行分组，所以可以消除许多外部查询不再需要处理的数据。 SQL优化应该能回答你一些问题。但最重要的是要记住，在查询的早期消除更多的内容，查询将运行得更快。

数据库还有一部分会尝试不同的查询方式。这个服务器的部分大部分时间会选择最快的路径，但是在查询中更具体的指定条件可以帮助它更快地完成。更多信息请参见数据库系统阅读材料。

从你的解释中可以看出，在如此庞大的行数上进行文件排序可能会严重影响查询。因为主查询（第二个查询的外部范围）中的行将使用一个内存表。

- Serdalis

Answer 2

在第一种情况下，索引用于查找匹配记录，但不能用于排序，因为您没有在组/按子句中包括最左边的列。我很想看到两个查询的概要：

set profiling =1;

run query 1;

run query 2;

show profile for query 1;

show profile for query 2;

- ggiroux

---

有趣的……: http://pastebin.com/fDJhPe4x （显然，第一个是慢的） - Jaka Jančar

Answer 3

只是出于好奇，你能否尝试这个版本？

SELECT b, c, SUM(metric) metric
FROM aggregates
WHERE a = 'some-value'
GROUP BY b, c

还有这个：

SELECT b, c, metric
FROM (
    SELECT a, b, c, SUM(metric) metric
    FROM aggregates
    WHERE a = 'some-value'
    GROUP BY a, b, c
) t
ORDER BY b, c

还有这个：

SELECT b, c, SUM(metric) metric
FROM aggregates
WHERE a = 'some-value'
GROUP BY a, b, c

- ypercubeᵀᴹ

---

第一个：相同（慢）速度，第二个：快（但只有一行），第三个：快（但当然有更多行） - Jaka Jančar

顺便提一下，按(a, b, c)分组返回252行，按(b, c)分组返回83行。 - Jaka Jančar

很抱歉，我无法理解这种情况可能发生。你显然没有告诉我们完整的故事，或者你没有正确翻译这些查询语句。我无法想象第一个或第二个查询将返回1行。 - ypercubeᵀᴹ

我并没有说第一个查询只返回了一行，但你是对的，我翻译第二个查询时出现了错误。我们都会犯错 :) （当然，我没有讲述整个故事，这里对查询进行了大量简化）。 - Jaka Jančar

有更多的行，因为我不是使用'a =“某个值”'，而是使用'a IN（...）'。这肯定是相关的，我担心可能省略了太多。我已经更新了问题。 - Jaka Jančar

Answer 4

**** 编辑：由于我没有看到where部分，所以答案不好。

我认为问题很简单，第二个查询使用了索引，而第一个没有。 如果您创建一个像(b,c,metric)这样的索引，我相信第一个查询将比第二个查询更快。

编辑后更详细：

第一个查询：

• 没有一个好的索引来执行查询。
• 测试索引是在(a,b,c,metric)上的，您需要在(b,c)上建立索引((b,c,metric)也可以)。
• 也许MySQL正在使用测试索引，但它不是一个好的索引，所以就像全表扫描一样。

第二个查询：

• 使用了索引(a,b,c)
• 在第二个实例中执行了一个非索引查询，但数据比第一个查询少。

- DavidEG

---

两个查询都使用了名为“test”的索引，正如EXPLAIN输出所显示的那样。 - ypercubeᵀᴹ

我不太信任EXPLAIN。索引从“a”列开始，因此我不知道如何在没有“a”列的情况下应用于分组。结果证实了我的理论 :) （也许正在使用索引，但使用方法不正确） - DavidEG

请参考此处了解如何在GROUP BY（紧凑索引扫描）中使用索引：http://dev.mysql.com/doc/refman/5.1/en/group-by-optimization.html。 - ypercubeᵀᴹ

你说得对，我没有注意到where子句... 我认为你粘贴的链接中就有答案：松散索引扫描是“处理GROUP BY的最有效方式”。因此，将GROUP BY b, c改为GROUP BY a, b, c可能更快。 - DavidEG

我也曾这样想，但是当使用SUM()时无法使用松散索引扫描。只有在使用MIN()和MAX()时才能使用。所以，这不是问题的原因。 - ypercubeᵀᴹ