作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
所以我被分配去估计两个查询的性能并得出了一个令人惊讶的结果。我事先被告知 HAVING
比 WHERE
慢因为它只在访问行后过滤结果。这似乎很合理,而且this question on SQL clause execution order加强了这一点。
但是,我根据一些假设估计了以下查询的性能,并且似乎使用了 HAVING
执行实际上更快!
SELECT status, count(status)
FROM customer
GROUP BY status
HAVING status != 'Active' AND status != 'Dormant'
SELECT status, count(status)
FROM customer
WHERE status != 'Active' AND status != 'Dormant'
GROUP BY status
假设是:
CUSTOMER
有 100 000 条记录 First query:
Accessing all rows, FROM: 100 000 * 0.01ms = 1000ms
GROUP BY: 100 000 * 0.005ms = 500ms
HAVING (2 conditions, 3 groups): 2 * 3 * 0.005ms = 0.03ms
SELECT and COUNT results: 15 000 * 0.01ms = 150ms
Total execution time: 1.65003s
Second query:
Accessing all the rows, FROM: 1000ms
WHERE: 2 * 100 000 * 0.005ms = 1000ms
GROUP BY: 15 000 * 0.005ms = 75ms
SELECT and COUNT results: 15 000 * 0.01ms = 150ms
Total execution time: 2.225s
结果来自
GROUP BY
只产生三组,很容易过滤,而
WHERE
必须一一浏览和过滤记录。
GROUP BY
也是如此用 HAVING
表现得像这样导致执行时间减少?
PLAN_TABLE_OUTPUT /* With HAVING */
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 35 | 4 (25)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 5 | 35 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS STORAGE FULL| CUSM | 5 | 35 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"<>'Active' AND "STATUS"<>'Dormant')
PLAN_TABLE_OUTPUT /* With WHERE */
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 7 | 4 (25)| 00:00:01 |
|* 2 | TABLE ACCESS STORAGE FULL| CUSM | 1 | 7 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("STATUS"<>'Active' AND "STATUS"<>'Dormant')
filter("STATUS"<>'Active' AND "STATUS"<>'Dormant')
最佳答案
事情是这样的:
关于sql - WHERE 与 HAVING 性能与 GROUP BY,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/49758446/
我是一名优秀的程序员,十分优秀!