MySQL 分组查询的优化方法

Nice MySql

2022-05-15 0 252

MySQL 在处理 GROUP BY 和 DISTINCT 查询的方式在大多数情况下类似，事实上，在优化过程中有时候会把在这两种方式中转换。两类查询都能够从索引中受益，通常，这也是优化这两种查询最为重要的方式。

在无法使用索引时，MySQL 对于 GROUP BY 查询有两种策略：使用临时表或者 filesort 执行分组。对于给定的查询，两种方式都没法更高效。我们可以通过配置 SQL_BIG_RESULT 和 SQL_SMALL_RESULT 来指定优化器选择其中一个方式。

通常，对查询表的id 进行分组比使用值进行分组效率更高，例如下面的查询效率就比较低：

SELECT actor.first_name, actor.last_name, COUNT(*)
FROM sakila.file_actor
INNER JOIN sakila.actor USING(actor_id)
GROUP BY actor.first_name, actor.last_name;

而下面的查询方式则更有效：

SELECT actor.first_name, actor.last_name, COUNT(*)
FROM sakila.file_actor
INNER JOIN sakila.actor USING(actor_id)
GROUP BY film_actor.actor_id;

而使用 actor.actor_id 进行分组会比 film_actor.actor_id更高效。

这个查询能够发挥其优势的依据是演员(actor)的姓名是依赖于 actor_id 的，因此会返回相同的结果，但是如果返回的结果不同的话就不能这么做了。甚至有些时候服务端通过 SQL_MODE 配置禁用了 GROUP BY。此时如果不关心获取的值，而且用于分组的列的值是唯一的，这可以使用 MIN和 MAX 来解决这个问题。

SELECT MIN(actor.first_name), MAX(actor.last_name), ...;

对于完美主义者，他们会认为你的分组是错误的，他们也是对的。一个虚拟的 MIN 或 MAX 的结果是查询并不会正确地组装。然而，有时候你只是为了让 MySQL 更快地执行查询。完美主义者对于下面的查询会满意：

SELECT actor.fisrt_name, actor.last_name, c.cnt
FROM sakila.actor
	INNER JOIN (
    SELECT actor_id, COUNT(*) AS cnt
    FROM sakila.film_actor
    GROUP BY actor_id
  ) AS c USING(actor_id);

然而，子查询中创建和填充临时表的代价可能比理论上看起来的死办法更高。需要记住的是，子查询构建的临时表是没有索引的，这会导致性能上的下降。

通常在分组查询中，选择没有分组的列是一个糟糕的主意。这是因为查询结果是不确定的，一旦改变了索引或优化器使用了不同的策略都会导致结果被改变。事实上，我们建议将服务端的 SQL_MODE 设置为 ONLY_FULL_GROUP_BY，这时写了一个糟糕的分组查询时，系统会产生一个错误而不是直接执行。开启 ONLY_FULL_GROUP_BY 后，SELECT 的字段只能是 GROUP BY 指定的字段，此时可以通过构建分步查询或子查询的方式，先分组查出分组的列，再做二次查询。

MySQL 会根据 GROUP BY 指定的列次序自动分组，除非是使用了 ORDER BY 指定排序规则。如果不在乎次序并且发现了这导致了一个 filesort，这时候可以使用 ORDER BY NULL 来跳过自动排序。也可以通过在 GROUP BY 后面增加 DESC 或 ASC 来指定结果按指定的方向排序。

有时候可以在分组查询时要求 MySQL 在结果中做一次超级聚合。这可以通过在 GROUP BY 后面增加WITH ROLLUP 子句完成，但是这不一定能够达到优化的预期。可以通过 EXPLAIN 检查执行的方法，注意分组有没有通过 filesort 或临时表完成。然后在对相同的查询移除 WITH ROLLUP 后进行对比。通过对比也许可以找到优化的办法。

有些时候通过增加聚合查询会使得效率更高，虽然这种方式会返回更多的行。也可以通过在 FROM 后面嵌套子查询来保持中间查询结果，然后再使用 UNION 获取最终结果。

但是注意的是，在应用程序中最好是移除 WITH ROLLUP，而通过优化来完成分组查询。

结语：使用 GROUP BY 进行分组查询时最好是使用索引列分组，若无需指定次序可以使用 ORDER BY NULL 进行优化。倘若不按索引列分组的时候，则需要考虑变通的办法，并且考虑是否要使用子查询或使用 WITH ROLLUP 检查性能后再做优化。同时，为了防止分组查询出现不可预料的错误，最好是开启 ONLY_FULL_GROUP_BY。

以上就是MySQL 分组查询的优化方法的详细内容，更多关于MySQL 分组查询的优化的资料请关注NICE源码其它相关文章！

微信