在 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 基础教程》介绍得更加详细。