我正在使用来自https://github.com/datacharmer/test_db.它的大小适中,为160 Mb。为了运行查询,我使用MySQL工作台。
以下代码在0.015s 中运行
SELECT *
FROM employees INNER JOIN salaries ON employees.emp_no = salaries.emp_no
添加GROUP BY的类似代码运行15.0s
SELECT AVG(salary), gender
FROM employees INNER JOIN salaries ON employees.emp_no = salaries.emp_no
GROUP BY gender
我检查了两个查询的执行计划,发现在这两种情况下,查询成本相似,大约为600k。我应该补充一下,员工表有300K行,工资表大约有3M行。
有人能说出执行时间差异如此之大的原因吗?我需要这个解释来更好地理解SQL的工作方式。
问题解决方案:由于评论和答案,我发现问题与我没有注意到在第一个查询的情况下,我的IDE将结果限制在1000行有关。这就是我获得0.015秒的原因。事实上,在我的情况下,加入需要10.0秒。如果创建了性别索引(数据库中已经存在employees.emp_no和salies.emp_no的索引),则需要10.0s才能加入和分组。如果没有性别索引,则第二次查询需要18.0s。
第一个查询的EXPLAIN显示,它对employees
中的300K行执行表扫描(type=ALL
),并对每一行执行部分主键(type=ref
)查找salaries
中的1行(估计)。
mysql> explain SELECT * FROM employees
INNER JOIN salaries ON employees.emp_no = salaries.emp_no;
+----+-------------+-----------+------+---------------+---------+---------+----------------------------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+---------+---------+----------------------------+--------+-------+
| 1 | SIMPLE | employees | ALL | PRIMARY | NULL | NULL | NULL | 299113 | NULL |
| 1 | SIMPLE | salaries | ref | PRIMARY | PRIMARY | 4 | employees.employees.emp_no | 1 | NULL |
+----+-------------+-----------+------+---------------+---------+---------+----------------------------+--------+-------+
第二个查询的EXPLAIN(实际上是一个计算AVG()的合理查询,正如您在评论中提到的)显示了一些额外的内容:
mysql> EXPLAIN SELECT employees.gender, AVG(salary) FROM employees
INNER JOIN salaries ON employees.emp_no = salaries.emp_no
GROUP BY employees.gender;
+----+-------------+-----------+------+---------------+---------+---------+----------------------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+---------+---------+----------------------------+--------+---------------------------------+
| 1 | SIMPLE | employees | ALL | PRIMARY | NULL | NULL | NULL | 299113 | Using temporary; Using filesort |
| 1 | SIMPLE | salaries | ref | PRIMARY | PRIMARY | 4 | employees.employees.emp_no | 1 | NULL |
+----+-------------+-----------+------+---------------+---------+---------+----------------------------+--------+---------------------------------+
是否看到额外字段中的Using temporary; Using filesort
?这意味着查询必须构建一个临时表来累积每个组的AVG()结果。它必须使用一个临时表,因为MySQL不知道它会一起扫描每种性别的所有行,所以它必须假设它在扫描行时需要独立地维护运行总数。追踪两个(在这种情况下)性别总数似乎不是什么大问题,但假设是邮政编码或类似的东西?
创建临时表是一项非常昂贵的操作。这意味着写入数据,而不仅仅是像第一个查询那样读取数据。
如果我们可以制作一个按性别排序的索引,那么MySQL的优化器就会知道它可以一起扫描所有具有相同性别的行。因此,它可以一次计算一个性别的运行总数,然后在扫描完一个性别后,计算AVG(工资),然后保证不会再扫描该性别的行。因此,它可以跳过构建临时表。
此索引有助于:
mysql> alter table employees add index (gender, emp_no);
现在,同一查询的EXPLAIN显示它将进行索引扫描(type=index
),该扫描访问相同数量的条目,但它将以更有用的顺序扫描,以计算聚合AVG()。
相同的查询,但没有Using temporary
注意:
mysql> EXPLAIN SELECT employees.gender, AVG(salary) FROM employees
INNER JOIN salaries ON employees.emp_no = salaries.emp_no
GROUP BY employees.gender;
+----+-------------+-----------+-------+----------------+---------+---------+----------------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+----------------+---------+---------+----------------------------+--------+-------------+
| 1 | SIMPLE | employees | index | PRIMARY,gender | gender | 5 | NULL | 299113 | Using index |
| 1 | SIMPLE | salaries | ref | PRIMARY | PRIMARY | 4 | employees.employees.emp_no | 1 | NULL |
+----+-------------+-----------+-------+----------------+---------+---------+----------------------------+--------+-------------+
执行这个查询要快得多:
+--------+-------------+
| gender | AVG(salary) |
+--------+-------------+
| M | 63838.1769 |
| F | 63769.6032 |
+--------+-------------+
2 rows in set (1.06 sec)
GROUP BY子句的添加可以很容易地解释您所看到的性能大幅下降。
来自文件:
满足GROUP BY子句的最常见方法是扫描整个表并创建一个新的临时表,其中每个组中的所有行都是连续的,然后使用此临时表来发现组并应用聚合函数(如果有)。
分组过程产生的额外成本可能非常昂贵。此外,即使不使用聚合函数,也会进行分组。
如果您不需要聚合函数,请不要分组。如果您这样做了,请确保您有一个引用所有分组列的索引,如文档所建议的:
在某些情况下,MySQL可以做得更好,并通过使用索引访问避免创建临时表。
PS:请注意«SELECT*。。。自MySQL 5.7.5以来,不支持类似GROUP BY»的语句(除非关闭选项ONLY_FULL_GROUP _BY)
GMB指出的还有另一个原因。基本上,您可能会查看第一个查询的时间,直到它返回第一个行。我怀疑它是否在0.015秒内返回所有行。
使用GROUP BY
的第二个查询需要处理所有数据以导出结果。
如果在第一个查询中添加了ORDER BY
(需要处理所有数据),那么您会看到类似的性能下降。