MySQL声称我可以在SELECT中使用列，但不能具有相同的性能

Question

MySQL文档在第11.5.3款中声明，尽管SQL标准可能会说什么，但在SELECT子句中使用列并不是grouped子句中的列，只要它们在功能上依赖于分组键就行了。

MySQL扩展了GROUP BY的使用，以便您可以使用未出现在GROUP子句中的select列表中的非聚合列或计算。通过避免不必要的列排序和分组，您可以使用此特性获得更好的性能。例如，您不需要在以下查询中对customer.name进行分组： 从订单中选择order.custid、customer.name、最大值(支付)，客户所在的order.custid = customer.custid组按order.custid； 在标准SQL中，必须将customer.name添加到GROUP子句中。在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 i组by时，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像我期望的那样使用主键索引吗？

Answer 1

因为看起来没有一个简单的答案，所以我现在要用一个廉价的解决方案。

我要做的事情如下：

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

但是，根据如何获得EXPLAIN编辑，MySQL优化器认为子查询是依赖的，这始终是一个非常非常糟糕的性能杀手。我认为这是查询优化器中的一个bug，因为它是同一个表，尽管它是别名的。因此，我将使用一个查询来获取I，并将它们放到IN中，第二个查询将获取o.*。它得到了合理的表现，而且不太痛苦。

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

Answer 2

为group使用派生表，并在您想要从中选择的任何表上重新联接

Answer 3

在第一个查询中，您要访问的唯一字段是索引，因此mysql只需要查看索引文件。但是在第二个查询中，您现在正在从表本身中提取一个列，该列还需要读取表数据。第一个查询实际上并不像使用WHERE子句时那样使用主键索引。它仅用于组by，但它仍在查看索引中的每个条目。

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

就优化而言，如果真正的查询没有累积函数(和、计数等)，那么应该看到一个很大的改进：

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

但是，如果这仅适用于您的简单示例，并且您的查询确实需要一个组，那么您的下一个最佳选择是增加大小变量，以便同时容纳更多的行。