代码之家 › 专栏 › 技术社区 › Matchu

MySQL声称我可以在SELECT中使用不在GROUP BY中的列,但是我不能使用同等的性能

group-by indexing optimization mysql

Matchu · 技术社区 · 14 年前

MySQL文档状态 section 11.5.3 不管SQL标准怎么说,在SELECT子句中使用不在GROUP BY子句中的列是很好的,只要它们在功能上依赖于分组键。

MySQL扩展了GROUP BY so的使用可以使用非聚合列或选择列表中的计算不出现在分组中的条款。您可以使用此功能通过避免不必要的列排序和分组。例如,你不需要组中的customer.name 以下查询:
SELECT order.custid, customer.name,
MAX(payments)   FROM order,customer  
WHERE order.custid = customer.custid  
GROUP BY order.custid;
在标准中 SQL,您必须添加按条款分组的customer.name。在MySQL中,名称是多余的。

听起来很合理。然而,尽管我可以选择那些列,似乎会对性能产生不利影响。

EXPLAIN SELECT o.id FROM objects o GROUP BY o.id;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | o     | range | NULL          | PRIMARY | 3       | NULL | 5262 | Using index for group-by |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

(我意识到这个查询非常愚蠢;它只是一个更复杂的查询的最简单版本,也有同样的问题。)当只选择我分组依据的主键ID时,MySQL使用主键索引。但是,当我包含其他列时,MySQL不会。

EXPLAIN SELECT o.id, o.name FROM objects o GROUP BY o.id;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | o     | ALL  | NULL          | NULL | NULL    | NULL | 5261 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

使用文件排序而不是索引真的让我很沮丧。我正在寻找 * 因此,希望避免重复组中的所有列并对它们进行索引。有没有什么方法可以让MySQL像我期望的那样使用主键索引?

3 回复 | 直到 14 年前

Jon Black 14 年前

为group by使用派生表,然后重新连接到要从中选择的任何表上

Matchu 14 年前

既然看起来没有一个简单的答案,我现在就用一个便宜的解决方案。

什么我将应该是这样的:

SELECT o1.* FROM objects o1 WHERE o1.id IN (SELECT o2.id FROM objects o2 WHERE mycondition GROUP BY o2.id)

然而,根据它如何得到 EXPLAIN ed,MySQL优化器认为子查询是依赖的,这总是一个非常非常糟糕的性能杀手。我认为这是查询优化器中的一个错误,因为它是同一个表,尽管它有别名。因此,我将使用一个查询来获取id,并将它们放入 IN 获取 o.* . 它得到了合理的表现,而不是太很痛苦。

这个问题仍然可以用更干净的解决方案来回答,如果不是更好的话:)

Rob Van Dam 14 年前

在第一个查询中,您访问的唯一字段位于索引中,因此mysql只需查看索引文件。但是在第二个查询中,您现在从表本身提取一个列,这也需要读取表数据。第一个查询并没有像使用 WHERE 条款。它只对group by使用它,但它仍然在查看索引中的每个条目。

第一个查询和第二个查询的区别在于,第二个查询必须查看完整表中的每一行(也称为表扫描),而不仅仅是索引中的每一个主键值。

就优化而言,如果您的实际查询没有如示例中那样的累积函数(SUM、COUNT等),那么应该会看到一个重大改进:

SELECT DISTINCT o.id, o.name FROM objects o

但是,如果您的简单示例仅如此,并且您的查询确实需要一个组,那么您的下一个最佳选择是增加 tmp_table_size and max_heap_table_size 变量以允许同时在内存中容纳更多行。