MySQL:在列列表中使用 COUNT(column_name)，并在 HAVING 子句中再次使用。这是否会导致 COUNT(column_name) 操作运行两次？

Question

我对在单个查询中使用 COUNT(column_name) 两次的性能感到好奇。这是有问题的查询:

SELECT
    employee_name,
    COUNT(employee_name)
FROM
    employee
GROUP BY
    employee_name
HAVING
    COUNT(employee_name) > 1;

会

COUNT(employee_name)

被处决两次？此外，当我将来遇到这样的问题时，我如何能够自己检查幕后情况的表现？

谢谢!

Answer 1

您可以使用优化器跟踪来获取有关优化器如何执行查询及其原因的更多信息。对于这种特殊情况，跟踪不会明确告诉计算计数的次数，但我们可以获得有关用于执行聚合的临时表的信息:

<小时/>

mysql> SET optimizer_trace='enabled=on';                                               
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT c2, COUNT(c2) FROM temp GROUP BY c2 HAVING COUNT(c2) > 1;
+------+-----------+
| c2   | COUNT(c2) |
+------+-----------+
|    1 |         2 |
|    2 |         2 |
+------+-----------+
2 rows in set (0,00 sec)

mysql> SELECT trace->'$.steps[*].join_execution.steps[*].creating_tmp_table'
    -> FROM information_schema.optimizer_trace;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| trace->'$.steps[*].join_execution.steps[*].creating_tmp_table'                                                                                                               |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"tmp_table_info": {"table": "intermediate_tmp_table", "location": "memory (heap)", "key_length": 5, "row_length": 23, "unique_constraint": false, "row_limit_estimate": 729444}}] |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,01 sec)

mysql> SELECT c2, COUNT(c2) AS c FROM temp GROUP BY c2 HAVING c > 1;
+------+---+
| c2   | c |
+------+---+
|    1 | 2 |
|    2 | 2 |
+------+---+
2 rows in set (0,00 sec)

mysql> SELECT trace->'$.steps[*].join_execution.steps[*].creating_tmp_table'           -> FROM information_schema.optimizer_trace;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| trace->'$.steps[*].join_execution.steps[*].creating_tmp_table'                                                                                                                       |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"tmp_table_info": {"table": "intermediate_tmp_table", "location": "memory (heap)", "key_length": 5, "row_length": 14, "unique_constraint": false, "row_limit_estimate": 1198372}}] |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

<小时/>

对于上面的内容，我们看到当使用别名而不是重复 COUNT 表达式时，临时表的行大小较小(14 字节与 23 字节)。这表明对于您的查询，在聚合期间计数完成了两次。