count(distinct())效率优化

如何提升自身sql效率，更快得到想要的数据，是每一个使用sql的同学都需要学习和关注的事情。

sql作为面向大众的数据提取工具，除了研发、数据分析师，产品经理及业务运营同学也都有应用需求。只要sql无语法错误，保持等待，或长或短都是可以输出结果的。但是在数据量庞大或数据逻辑复杂时，或碰上线上资源紧张，或者好不容易等了3小时、结果发现数据有点异常需要修改后重跑，不知道有没有同学有相同的经历。

低效是每位同学都不乐见的，而避免这个问题就要求我们学习优化sql的方法，从而减少自己等数的焦虑时光。

而其中最常见的低效sql，就是count(distinct)。

故本文，就来结合本人学习及自身实践经验，给大家几点优化的建议，并配上实际效率以供参考。

常规的count(distinct)通常都跟着group by一起进行。

示例如下：

select

  b.name,

  count(distinct a.user_id)

from table_a a

join table_b b on a.dashboard_id = b.id

group by name

order by count desc

常规优化点如下：

1.先聚集，后join。

具体指先将表内的内容进行简化和聚集计算，join是基于一层聚集以后再进行的操作

如下：

就是先将table_a按照dashboard_id进行了一次聚集，后续的join关联上name即可

    select

      b.name,

      new_a.ct

    from table_b as b

    join (

      select

        dashboard_id,

        count(distinct user_id) as ct

      from table_a as a  

      group by dashboard_id

    ) as new_a

    on new_a.dashboard_id = b.id

    order by new_a.ct desc

2.缩小group计算的数据集，提前对数据表进行限制、处理；

先做处理，如下：

就是先对a表中每一个user_id进行distinct，然后在上层计数。即把count和distinct拆成两步走。

*注意，这点尽量选择关联id与去重字段关系为1:n 且n越大效率越高。当数据大多为1:1 这一步优化效率不明显

    select

      b.name,

      log_counts.ct

    from table_b as b

    join (

      select distinct_a.dashboard_id,

      count(1) as ct

      from (

        select distinct dashboard_id, user_id

        from table_a as a

      ) as distinct_a

      group by distinct_a.dashboard_id

    ) as log_counts

    on log_counts.dashboard_id = b.id

    order by log_counts.ct desc

同时此点，还可以在join条件中添加多个限制条件，使匹配上的数据字段更少，缩小数据集，提高数据处理效率。如join on 条件1 and 条件2 and 条件3）

实际用例：

1. 使用最基本的count(distinct())搭配group by组合耗时9.85s

2.在left_join中添加多层and条件，耗时9.81s；收效不明显

3.调整为先聚集后，耗时2.45s，直接缩减为原始时间的25%，减少3/4的时间消耗。

4.最后缩小数据集，提前限制处理耗时2.74s

此处时间消耗要大于上一步优化结果。主要是由数据特性决定的。在数据量大，或者去重字段本身分类值多的情况下 分拆先distinct反而会有所耗时。

总结上述例子：

在优化过程中，最明显有效的是尽量先聚集，再关联。再每部优化操作上，还需要了解数据情况、做好探查，才能找到适合的优化方式。否则可能反而做了无用功消耗资源。

另外，在使用count(distinct())时，需要注意后续是否有重复值计算，以防处理后得到了翻倍的数据值！

了解业务真正需求，探查数据表逻辑结构，才能最快的拿到正确的数。共勉。