mysql - 限制where子句中的结果集会提高group by和having的性能吗？

Question

我有一个包含超过 2500 万行的 MySQL 表。因此，为了防止关闭整个数据库，我不想执行任何会导致在磁盘上创建临时表的查询，例如在未索引的列上进行排序。

所以如果表格有以下列......

 employee_id
 first_name
 last_name
 hire_date
 manager_id

假设我想看到那些有 3 名或更多员工为他们工作的经理(假设 manager_id 没有索引)。

select count(*), manager_id from employee group by manager_id  having count(id) > 3

如果我将结果集限制为仅 2016 年之后雇用的那些员工，它是否有助于此查询的性能......

select count(*), manager_id from employee where hire_date > ‘2016-01-01’ 
group by manager_id having count(id) > 3

假设 hire_date 也没有索引。那个额外的 where 子句会有帮助吗？

Answer 1

是的，限制 WHERE 子句中的行意味着要分组的行会更少，有些组甚至不会显示，因为该组中的行已经被过滤出。

GROUP BY 可能会在您的查询中创建一个临时表。但至少它会是一个较小的临时表，因为会有更少的组。避免临时表的方法是通过 manager_id 上的索引按索引顺序进行查询扫描。

如果 MySQL 可以确保它按 manager_id 的顺序扫描，它就可以避免使用临时表，因此它可以更容易地计算每个组中的行数，假设它正在扫描每个组连续地。换句话说，当它到达给定 manager_id 的最后一行时，它知道对于相同的 manager_id 不能再有更多的行。因此它不需要保留每个 manager_id 的计数。它可以在完成扫描每组行时输出每个 manager_id 的每个计数。

但是您可能会发现 hire_date 上的索引有更大的好处。如果该条件可以通过从 hire_date > '2016-01-01' 的行开始来避免扫描大部分表，那么临时表的成本可能会低于表扫描的成本.

无法进行既按manager_id 上的索引进行扫描又按hire_date 上的索引进行扫描的查询。哪种策略更好取决于您的表中有多少行匹配不同的条件。