如果我需要查一张数据表中某一天内几个字段重复的数据,我会这样来查询:
SELECT date,ip,group_concat(ip) FROM monitorday where date = '2020-08-10' group by date,ip HAVING count(ip) > 1;
这里我对这个having和where的区别有了一点疑问,那就是它们的区别在哪里?然后我开始百度,看了一些答案其中最重要的一句点睛之笔是:总之一条sql中有where having group by的时候,顺序是 where group by having
。当然前面有讲到并不是只有在group by的时候才能用having,在普通的过滤中也能用,知识相对于where的性能不好,下面做一个使用的对比。
SELECT date,ip FROM monitorday where date = '2020-08-10' and module1 = 'XXX' # 没问题
SELECT date,ip FROM monitorday having date = '2020-08-10' and module1 = 'XXX' # Unknown column 'module1' in 'having clause'
SELECT date,ip,module1 FROM monitorday having date = '2020-08-10' and module1 = 'XXX' # 没问题
当你使用having过滤时,如果最终显示的字段中没有这个字段那么having就会报错,只有你在前面加上这个字段才能完成查询。而且having和where的性能对比,having的性能不如where的性能好,这里我只查看简单查询的执行计划。
EXPLAIN SELECT date,ip,module1 FROM aiops_monitorday having date = '2020-08-10' and module1 = 'XXX'
#id|select_type|table |partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra|
#--|-----------|----------------|----------|----|-------------|---|-------|---|-------|--------|-----|
# 1|SIMPLE |monitorday | |ALL | | | | |3952576| 100.0| |
EXPLAIN SELECT date,ip FROM monitorday where date = '2020-08-10' and module1 = 'XXX'
#id|select_type|table |partitions|type |possible_keys |key |key_len|ref|rows|filtered|Extra |
#--|-----------|----------------|----------|-----------|----------------------------------------------------------------|----------------------------------------------------------------|-------|---|----|--------|----------------------------------------------------------------------------------------------|
# 1|SIMPLE |monitorday | |index_merge| monitorday_date_05337d12, monitorday_module1_17aaec72|monitorday_date_05337d12,monitorday_module1_17aaec72 |3,195 | | 20| 75.0|Using intersect(monitorday_date_05337d12,monitorday_module1_17aaec72); Using where |
type性能从左到右性能递增
性能:all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
推荐看这一篇文章mysql where和having的区别,讲过滤讲的非常详细;推荐这一篇文章MySQL 查看执行计划,教你如何查看执行计划判断性能;