MySQL 中 WHERE 和 HAVING 的区别

白日梦想家

发布于 2021-03-11 15:39:17

1.6K0

发布于 2021-03-11 15:39:17

文章被收录于专栏：SQL实现SQL实现

在 MySQL 中，WHERE 子句和 HAVING 子句都有过滤的作用，它们有什么区别呢？

mysql> SELECT * FROM employees HAVING emp_no = 10001;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.09 sec)

单从结果来看，WHERE 能做的，HAVING 也能做。

实际上，WHERE 子句和 HAVING 子句的区别还蛮大。

从功能上说，WHERE 用于过滤行，而 HAVING 用来过滤分组。WHERE 在数据分组前进行过滤，即 WHERE 过滤掉的数据不包含在分组中，HAVING 在数据分组后才过滤。

在性能方面，如果要过滤的字段上有索引，并且条件满足走索引的规则，放在 WHERE 子句中可以走索引，而放在 HAVING 子句中不能走索引。

请看例子：

mysql> EXPLAIN SELECT * FROM employees WHERE emp_no = 10001;
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | employees | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)



mysql> EXPLAIN SELECT * FROM employees HAVING emp_no = 10001;
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299290 | NULL  |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.00 sec)

可以看到，条件 emp_no = 10001 在 WHERE 子句中，执行计划显示用到了主键，而条件在 HAVING 子句中，执行计划显示的是全表扫描。

即使字段上没有索引，在执行聚合操作时，当表的数据量比较大，从执行速度方面也能看出两者的区别。

employees 表总共有 30w 条数据，表的部分数据如下：

mysql> SELECT * FROM employees LIMIT 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.00 sec)

在 WHERE 子句中指定条件明显比在 HAVING 子句中执行速度快。

mysql> SELECT
    ->   last_name,
    ->   COUNT(*) AS cnt
    -> FROM
    ->   employees
    -> WHERE last_name = 'Facello'
    -> GROUP BY last_name ;
+-----------+-----+
| last_name | cnt |
+-----------+-----+
| Facello   | 186 |
+-----------+-----+
1 row in set (0.05 sec)


mysql> SELECT
    ->   last_name,
    ->   COUNT(*) AS cnt
    -> FROM
    ->   employees
    -> GROUP BY last_name
    -> HAVING last_name = 'Facello';
+-----------+-----+
| last_name | cnt |
+-----------+-----+
| Facello   | 186 |
+-----------+-----+
1 row in set (0.26 sec)

关于 WHERE 子句和 HAVING 子句的区别，在《MySQL 必知必会》和《SQL 基础教程》里边都有介绍，《SQL 基础教程》介绍得更加详细。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2021-02-25，如有侵权请联系 cloudcommunity@tencent.com 删除

sql