SQL优化（二） 快速计算Distinct Count_郭俊JasonGuo的博客-CSDN博客_distinct count sql
SQL优化（二） 快速计算Distinct Count
郭俊JasonGuo
于 2016-01-04 20:28:05 发布
5595
收藏
分类专栏：
数据库
优化
SQL
文章标签：
数据库
SQL
优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Habren/article/details/50458221
版权
数据库
同时被 3 个专栏收录
12 篇文章
0 订阅
订阅专栏
优化
7 篇文章
0 订阅
订阅专栏
SQL
10 篇文章
0 订阅
订阅专栏
原创文章，转载请务必在文章开头处注明转载自Jason’s Blog，并给出原文链接 http://www.jasongj.com/2015/03/15/count_distinct/
UV vs. PV
在互联网中，经常需要计算UV和PV。所谓PV即Page View，网页被打开多少次（YouTube等视频网站非常重视视频的点击率，即被播放多少次，也即PV）。而UV即Unique Visitor（微信朋友圈或者微信公众号中的文章则统计有多少人看过该文章，也即UV。虽然微信上显示是指明该值是PV，但经笔者测试，实为UV）。这两个概念非常重要，比如淘宝卖家在做活动时，他往往需要统计宝贝被看了多少次，有多少个不同的人看过该活动介绍。至于如何在互联网上唯一标识一个自然人，也是一个难点，目前还没有一个非常准确的方法，常用的方法是用户名加cookie，这里不作深究。
count distinct vs. count group by
很多情景下，尤其对于文本类型的字段，直接使用count distinct的查询效率是非常低的，而先做group by更count往往能提升查询效率。但实验表明，对于不同的字段，count distinct与count group by的性能并不一样，而且其效率也与目标数据集的数据重复度相关。
本节通过几组实验说明了不同场景下不同query的不同效率，同时分析性能差异的原因。 （本文所有实验皆基于PostgreSQL 9.3.5平台） 分别使用count distinct 和 count group by对 bigint, macaddr, text三种类型的字段做查询。 首先创建如下结构的表
ColumnTypeModifiersmac_bigintbigintmac_macaddrmacaddrmac_texttext
并插入1000万条记录，并保证mac_bigint为mac_macaddr去掉冒号后的16进制转换而成的10进制bigint，而mac_text为mac_macaddr的文本形式，从而保证在这三个字段上查询的结果，也及复杂度相同。
count distinct SQL如下
select
count(distinct mac_macaddr)
from
testmac
count group by SQL如下
select
count(*)
from
(select
mac_macaddr
from
testmac
group by
1) foo
对于不同记录数较大的情景（1000万条记录中，有300多万条不同记录），查询时间（单位毫秒）如下表所示。
query/字段类型macaddrbiginttextcount distinct24668.02313890.051149048.911count group by32152.80825929.555159212.700
对于不同记录数较小的情景（1000万条记录中，只有1万条不同记录），查询时间（单位毫秒）如下表所示。
query/字段类型macaddrbiginttextcount distinct20006.6819984.763225208.133count group by2529.4202554.7203701.869
从上面两组实验可看出，在不同记录数较小时，count group by性能普遍高于count distinct，尤其对于text类型表现的更明显。而对于不同记录数较大的场景，count group by性能反而低于直接count distinct。为什么会造成这种差异呢，我们以macaddr类型为例来对比不同结果集下count group by的query plan。 当结果集较小时，planner会使用HashAggregation。
explain analyze select count(*) from (select mac_macaddr from testmac_small group by 1) foo;
QUERY PLAN
Aggregate (cost=668465.04..668465.05 rows=1 width=0) (actual time=9166.486..9166.486 rows=1 loops=1)
-> HashAggregate (cost=668296.74..668371.54 rows=7480 width=6) (actual time=9161.796..9164.393 rows=10001 loops=1)
-> Seq Scan on testmac_small (cost=0.00..572898.79 rows=38159179 width=6) (actual time=323.338..5091.112 rows=10000000 l
oops=1)
而当结果集较大时，无法通过在内存中维护Hash表的方式使用HashAggregation，planner会使用GroupAggregation，并会用到排序，而且因为目标数据集太大，无法在内存中使用Quick Sort，而要在外存中使用Merge Sort，而这就极大的增加了I/O开销。
explain analyze select count(*) from (select mac_macaddr from testmac group by 1) foo;
QUERY PLAN
Aggregate (cost=1881542.62..1881542.63 rows=1 width=0) (actual time=34288.232..34288.232 rows=1 loops=1)
-> Group (cost=1794262.09..1844329.41 rows=2977057 width=6) (actual time=25291.372..33481.228 rows=3671797 loops=1)
-> Sort (cost=1794262.09..1819295.75 rows=10013464 width=6) (actual time=25291.366..29907.351 rows=10000000 loops=1)
Sort Key: testmac.mac_macaddr
Sort Method: external merge Disk: 156440kB
-> Seq Scan on testmac (cost=0.00..219206.64 rows=10013464 width=6) (actual time=0.082..4312.053 rows=10000000 loo
ps=1)
dinstinct count高效近似算法
由于distinct count的需求非常普遍（如互联网中计算UV），而该计算的代价又相比较高，很难适应实时性要求较高的场景，如流计算，因此有很多相关研究试图解决该问题。比较著名的算法有daptive sampling Algorithm，Distinct Counting with a Self-Learning Bitmap，HyperLogLog，LogLog，Probabilistic Counting Algorithms。这些算法都不能精确计算distinct count，都是在保证误差较小的情况下高效计算出结果。本文分别就这几种算法做了两组实验。
数据集100万条，每条记录均不相同，几种算法耗时及内存使用如下。
algorithmresulterrortime(ms)memory (B)count(distinct)10000000%14026？Adaptive Sampling10081280.8%865357627Self-learning Bitmap9916510.9%115165571Bloom filter78805222%24001198164Probalilistic Counting113992514%361395PCSA84173516%842495
数据集100万条，只有100条不同记录，几种近似算法耗时及内存使用如下。
algorithmresulterrortime(ms)memory (B)count(distinct)1000%75306？Adaptive Sampling1000%149157627Self-learning Bitmap1011%103165571Bloom filter1000%16751198164Probalilistic Counting955%361395PCSA982%852495
从上面两组实验可看出，大部分的近似算法工作得都很好，其速度都比简单的count distinct要快很多，而且它们对内存的使用并不多而结果去非常好，尤其是Adaptive Sampling和Self-learning Bitmap，误差一般不超过1%，性能却比简单的count distinct高十几倍乃至几十倍。
distinct count结果合并
如上几种近似算法可以极大提高distinct count的效率，但对于data warehouse来说，数据量非常大，可能存储了几年的数据，为了提高查询速度，对于sum及avg这些aggregation一般会创建一些aggregation table。比如如果要算过去三年的总营业额，那可以创建一张daily/monthly aggregation table，基于daily/monthly表去计算三年的营业额。但对于distinct count，即使创建了daily/monthly aggregation table，也没办法通过其计算三年的数值。这里有种新的数据类型hll，这是一种HyperLogLog数据结构。一个1280字节的hll能计算几百亿的不同数值并且保证只有很小的误差。 首先创建一张表(fact)，结构如下
ColumnTypeModifiersdaydateuser_idintegersalesnumeric
插入三年的数据，并保证总共有10万个不同的user_id，总数据量为1亿条（一天10万条左右）。
insert into fact
select
current_date - (random()*1095)::integer * '1 day'::interval,
(random()*100000)::integer + 1,
random() * 10000 + 500
from
generate_series(1, 100000000, 1);
直接从fact表中查询不同用户的总数，耗时115143.217 ms。 利用hll，创建daily_unique_user_hll表，将每天的不同用户信息存于hll类型的字段中。
create table daily_unique_user_hll
as select
day,
hll_add_agg(hll_hash_integer(user_id))
from
fact
group by 1;
通过上面的daily aggregation table可计算任意日期范围内的unique user count。如计算整个三年的不同用户数，耗时17.485 ms，查询结果为101044，误差为(101044-100000)/100000=1.044%。
explain analyze select hll_cardinality(hll_union_agg(hll_add_agg)) from daily_unique_user_hll;
QUERY PLAN
Aggregate (cost=196.70..196.72 rows=1 width=32) (actual time=16.772..16.772 rows=1 loops=1)
-> Seq Scan on daily_unique_user_hll (cost=0.00..193.96 rows=1096 width=32) (actual time=0.298..3.251 rows=
1096 loops=1)
Planning time: 0.081 ms
Execution time: 16.851 ms
Time: 17.485 ms
而如果直接使用count distinct基于fact表计算该值，则耗时长达 127807.105 ms。 从上面的实验中可以看到，hll类型实现了distinct count的合并，并可以通过hll存储各个部分数据集上的distinct count值，并可通过合并这些hll值来快速计算整个数据集上的distinct count值，耗时只有直接使用count distinct在原始数据上计算的1/7308，并且误差非常小，1%左右。
总结
如果必须要计算精确的distinct count，可以针对不同的情况使用count distinct或者count group by来实现较好的效率，同时对于数据的存储类型，能使用macaddr/intger/bigint的，尽量不要使用text。 另外不必要精确计算，只需要保证误差在可接受的范围之内，或者计算效率更重要时，可以采用本文所介绍的daptive sampling Algorithm，Distinct Counting with a Self-Learning Bitmap，HyperLogLog，LogLog，Probabilistic Counting Algorithms等近似算法。另外，对于data warehouse这种存储数据量随着时间不断超增加且最终数据总量非常巨大的应用场景，可以使用hll这种支持合并dintinct count结果的数据类型，并周期性的（比如daily/weekly/monthly）计算部分数据的distinct值，然后通过合并部分结果的方式得到总结果的方式来快速响应查询请求。
SQL优化系列
SQL优化（一） Merge Join vs. Hash Join vs. Nested LoopSQL优化（二） 快速计算Distinct CountSQL优化（三） PostgreSQL Table PartitioningSQL优化（四） Postgre Sql存储过程
郭俊JasonGuo
关注
关注
点赞
收藏
打赏
评论
SQL优化（二） 快速计算Distinct Count
本文介绍了distinct count的SQL优化方法，以及常用的高效近似算法及其在PostgreSQL上的实现。
复制链接
扫一扫
专栏目录
distinct 、group by 的区别
qq_36061501的博客
05-13
3475
select distinct(university) from user_profile;
select university from user_profile group by university;二者返回的结果式样的。但是既然一样，为什么又要做两个函数呢？来看看它们的区别
————————————————
distinct 支持单列、多列的去重
单列去重的方式简明易懂，即相同值只保留1个；多列的去重则是根据指定的去重的列信息来进行，即只有所有指定的列信息都相同，才会被认为是重复的信息。
dis
ShardingJDBC之sql无法兼容count（Distinct）
最新发布
WillametteA的博客
11-09
558
ShardingJDBC之sql无法兼容count（Distinct）
参与评论
您还未登录，请先
登录
后发表或查看评论
Sql优化（二） 快速计算Distinct Count
技术世界
03-28
883
原创文章，始发自本人个人博客站点，转载请务必注明出自http://www.jasongj.com
个人博客上本文链接http://www.jasongj.com/2015/03/15/count_distinct/
UV vs. PV
在互联网中，经常需要计算UV和PV。所谓PV即Page View，网页被打开多少次（YouTube等视频网站非常重视视频的点击率，即被播放多...
GBase 8c 函数和操作符 - HLL函数和操作符 之 聚合函数
m0_72846978的博客
10-27
24
描述：把哈希后的数据按照分组放到hll中，依次指定参数log2m、log2explicit、log2sparse。描述：把哈希后的数据按照分组放到hll中, 依次制定参数log2m、log2explicit、log2sparse、duplicatecheck，duplicatecheck取值范围是0或者1，表示是否开启该模式，默认情况下该模式会关闭。描述：把哈希后的数据按照分组放到hll中，并指定参数log2m，取值范围是10到16。描述：将多个hll类型数据union成一个hll。
distinct和group by区别
热门推荐
qq_33380252的博客
11-23
2万+
关于去重作用（转载地址：https://blog.csdn.net/ljl890705/article/details/70602442）
distinct简单来说就是用来去重的，而group by的设计目的则是用来聚合统计的，两者在能够实现的功能上有些相同之处，但应该仔细区分，因为用错场景的话，效率相差可以倍计。
单纯的去重操作使用distinct，速度是快于group by的。
dist...
面试突击63：distinct 和 group by有什么区别？
Java中文社群
07-07
1726
作者 | 磊哥来源 | Java面试真题解析（ID：aimianshi666）转载请联系授权（微信ID：GG_Stone）在 MySQL 中，最常见的去重方法有两个：使用 distinct 或使用 group by，那它们有什么区别呢？接下来我们一起来看。1.创建测试数据--创建测试表
droptableifexistspageview;
createtabl...
1.0.1 sql优化 --count(distinct())效率优化实例
day_one
02-19
841
如何提升自身sql效率，更快得到想要的数据，是每一个使用sql的同学都需要学习和关注的事情。
sql作为面向大众的数据提取工具，除了研发、数据分析师，产品经理及业务运营同学也都有应用需求。只要sql无语法错误，保持等待，或长或短都是可以输出结果的。但是在数据量庞大或数据逻辑复杂时，或碰上线上资源紧张，或者好不容易等了3小时、结果发现数据有点异常需要修改后重跑，不知道有没有同学有相同的经历。
低...
mysql count distinct太慢_MySQL COUNT(*)和DISTINCT效率分析
weixin_35681614的博客
01-21
519
MySQL数据库对于COUNT(*)的不同处理会造成不同的结果，比如，执行下面查询时，即使对于千万级别的数据mysql也能非常迅速的返回结果。SELECTCOUNT(*) FROM tablename但如果这样执行，mysql的查询时间开始攀升。SELECT COUNT(*) FROM tablename WHERE…..当没有WHERE语句对于整个mysql的表进行count运算的时候，M...
distinct 和 group by有什么区别
java123456111的博客
07-18
2037
大部分场景下distinct是特殊的groupby，但是二者也有细微的区别，比如他们在查询结果集上，使用的具体业务场景上，以及性能上都是不同的。
distinct 和 group by 的区别
贾廷帅的博客
09-03
3337
前言
我们在sql里面经常看到 distinct 和 group by,那么两者到底有何区别呢？下面就来一探究竟。
首先来看官方描述
链接: Mysql distinct 传送门
其中有这么一段描述：
Generally speaking, the DISTINCT clause is a special case of the GROUP BY clause.
The difference between DISTINCT clause and GROUP BY clause is that the G
distinct效率更高还是group by效率更高？
猾枭的博客
06-29
9159
原创文章，希望多多关注支持，感谢。
目录
00 结论
01 distinct的使用
02 group by的使用
03 distinct和group by原理 *
04 推荐group by的原因
00结论
先说大致的结论（完整结论在文末）：
在语义相同，有索引的情况下
group by和distinct都能使用索引，效率相同。
在语义相同，无索引的情况下：
distinct效率高于group by。原因是distinct 和 group by都会进行分组操作，但group by可能会进行排序，触发fil
Distinct 和Group by的区别
Chelin Tsien的专栏
04-17
1589
其实二者没有什么可比性，但是对于不包含聚集函数的GROUP BY操作来说，和DISTINCT操作是等价的。不过虽然二者的结果是一样的，但是二者的执行计划并不相同。在Oracle9i中：SQL> SELECT * FROM V$VERSION;BANNER--------------------------------------------------------------
数据库中distinct的用法，distinct和聚合函数一起使用，distinct的位置
baomingshu的博客
12-17
1万+
distinct的详细用法distinct的用法distinct和聚合函数distinct的位置
distinct的用法
一，house表，表结构
上图除了id字段，其他字段都有重复的数据，在查询时可以使用distinct过滤重复数据，执行上面红框中的语句
select distinct house_name,floor,address from house
将会过滤字段中的重复数据， 执行结果如下面红框
distinct和聚合函数
distinct和聚合函数使用时，要将distinct放在聚合函数里
Java操作Elasticsearch6实现group by分组查询
Dream it Possible
11-18
4656
引言
通过上篇博客的总结，我们知道了在Elasticsearch6中count、distinct和count(distinct)方法的使用。本篇博客继续聚合查询的学习，也就是对应mysql中的group by的使用。
公共实体
对于下面要介绍的查询，返回结果为统一实体，代码如下：
/**
* 单个字段分组返回结果
* @author : huzhiting
* @date : 2020-11-18 15:02
*/
@Data
public class AggregationForOneDTO
clickhouse数据去重函数介绍（count distinct）
huni的博客
06-09
4970
> clickhouse提供了许多的去重函数，有精确去重的以及非精确去重的，下面介绍下两种
非精确去重函数：uniq、uniqHLL12、uniqCombined
精确去重函数：uniqExact、groupBitmap
ClickHouse调优（二）语法优化
Yuan_CSDF的博客
01-03
1823
1、ClicHouse语法优化规则
ClickHouse的SQL优化规则是基于RBO（Rule Based Optimization），下面是一些优化规则。
1.1、COUNT优化
在调用count函数时，如果使用的是count()或者count(*),且没有where条件，则会直接使用system.tables的total_rows，例如：
注意：Optimized trivial count，这是对count的优化。
1.2、消除子查询重复字段
语...
别再使用count distinct了
王义凯 的博客
06-13
2389
在数仓开发中经常会对数据去重后统计，而对于大数据量来说，count(distinct )操作明显非常的消耗资源且性能很慢。
下面介绍我平时使用最多的一种you'hua
count distinct if 分析
qq_17902567的博客
04-04
5658
我们稍做修改
selectpartition_date,count(user_id),
count(distinctif(user_is_new = 1, user_id, 0)) --注意新增用户量的统计,加了distinct去重
fromdw.nice_live_dw_...
数据库去重汇总 count(distinct xxx) 与distinct ； count（case when ）
yuhui666666的博客
01-09
2589
总括：
SELECT
T.dept_one as "部门",
COUNT(DISTINCT (
CASE
WHEN (is_week='1') THEN
name
ELSE
NULL
end
)) as "周活"
FROM
(SELECT m.*, s.event_property_code,s.event_property_value FROM ...
SQL 中 Count 和Distinct的使用
qqqgg的专栏
10-10
5060
先来复习一下左连接：
select * from tb_driverReportDetails
left join tb_driverReportInput on tb_driverReportInput.id=tb_driverReportDetails.reportid
left join tb_stationMileage on tb_stationMileage.id
“相关推荐”对你有帮助么？
非常没帮助
没帮助
一般
有帮助
非常有帮助
提交
©️2022 CSDN
皮肤主题：编程工作室
设计师：CSDN官方博客
返回首页
郭俊JasonGuo
CSDN认证博客专家
CSDN认证企业博客
码龄14年
暂无认证
52
原创
10万+
周排名
91万+
总排名
12万+
访问
等级
1750
积分
120
粉丝
35
获赞
10
评论
87
收藏
私信
关注
热门文章
分布式事务(一) 两阶段提交及JTA
7163
UML(一) 类图及类间关系
6807
流式处理界的新贵 Kafka Stream - Kafka设计解析（七）
6359
Java设计模式（一） 简单工厂模式不简单
6114
SQL优化（五） PostgreSQL （递归）CTE 通用表表达式
5668
分类专栏
深入浅出SQL调优
5篇
Kafka深度剖析
14篇
跟着Jason学 设计模式
9篇
Java成神之路
5篇
深入浅出Zookeeper
Spark
消息系统
12篇
分布式
15篇
集群
8篇
Kafka
15篇
SQL
10篇
数据库
12篇
优化
7篇
机器学习
4篇
4篇
大数据
13篇
设计模式
11篇
简单工厂模式
1篇
java设计模式
11篇
工厂模式
1篇
设计
1篇
java
16篇
注解
1篇
annotation
1篇
sprint-aop
1篇
jdk动态代理
1篇
cglib
1篇
postgresql
3篇
事务
1篇
mvcc
1篇
多版本并发控制
1篇
多线程
3篇
并发
2篇
uml
1篇
spark
5篇
性能优化
3篇
数据倾斜
1篇
hadoop
1篇
人工智能
1篇
消息队列
2篇
消息系列
2篇
最新评论
Spark SQL 性能优化再进一步 CBO 基于代价的优化
markmarkqiu:
几年过去了，老哥，图片真的不打算救一下？
Spark SQL 性能优化再进一步 CBO 基于代价的优化
as65132:
RBO和CBO篇幅的都看不了
Spark SQL 性能优化再进一步 CBO 基于代价的优化
as65132:
老哥，图片看不了，很难受
Java进阶（五）Java I/O模型从BIO到NIO和Reactor模式
_Kafka_:
多Reactor中 selector.selectNow() 不行啊，这个方法立即返回导致外面的while不停的循环，cpu 100%
Java进阶（五）Java I/O模型从BIO到NIO和Reactor模式
HSJ0170:
一篇让人豁然开朗的好文，解开了我对netty的reactor模式的疑惑
您愿意向朋友推荐“博客详情页”吗？
强烈不推荐
不推荐
一般般
推荐
强烈推荐
提交
最新文章
Spark 灰度发布在十万级节点上的成功实践 CI CD
Spark SQL 性能优化再进一步 CBO 基于代价的优化
Spark CommitCoordinator 保证数据一致性
2018年4篇
2017年8篇
2016年28篇
2015年18篇
目录
目录
分类专栏
深入浅出SQL调优
5篇
Kafka深度剖析
14篇
跟着Jason学 设计模式
9篇
Java成神之路
5篇
深入浅出Zookeeper
Spark
消息系统
12篇
分布式
15篇
集群
8篇
Kafka
15篇
SQL
10篇
数据库
12篇
优化
7篇
机器学习
4篇
4篇
大数据
13篇
设计模式
11篇
简单工厂模式
1篇
java设计模式
11篇
工厂模式
1篇
设计
1篇
java
16篇
注解
1篇
annotation
1篇
sprint-aop
1篇
jdk动态代理
1篇
cglib
1篇
postgresql
3篇
事务
1篇
mvcc
1篇
多版本并发控制
1篇
多线程
3篇
并发
2篇
uml
1篇
spark
5篇
性能优化
3篇
数据倾斜
1篇
hadoop
1篇
人工智能
1篇
消息队列
2篇
消息系列
2篇
目录
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
打赏作者
郭俊JasonGuo
你的鼓励将是我创作的最大动力
¥2
¥4
¥6
¥10
¥20
输入1-500的整数
余额支付
(余额：-- )
扫码支付
扫码支付：¥2
获取中
扫码支付
您的余额不足，请更换扫码支付或充值
打赏作者
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额
抵扣说明：
1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。 2.余额无法直接购买下载，可以购买VIP、C币套餐、付费专栏及课程。
余额充值