只满足某个条件的数据统计SQL各类写法性能分析

最近遇到一个需求，简要描述为：统计出只订购了A软件的用户数；
需求看似简单，写出这条SQL这并不难，但是SQL性能呢？下面就介绍实现该需求的几种SQL写法：
首先假设我们的订购记录表为APP_SUBSC_CTRL，核心字段有：

1. SQL> desc app_subsc_ctrl;
2. Name                Type           Nullable Default Comments           
3. ------------------- -------------- -------- ------- --------------
4. APP_SUBSC_CTRL_ID   VARCHAR2(20)                    服务订阅流水号
5. SUBSC_USER_ID       VARCHAR2(20)                    用户id
6. CORP_ID             VARCHAR2(20)   Y                公司id
7. APP_ID              VARCHAR2(20)                    服务id
8. TRIBE_ID            VARCHAR2(20)   Y                群组id

PS：1、列出CORP_ID、TRIBE_ID是为了说明SUBSC_USER_ID + APP_ID不是唯一的；
2、SUBSC_USER_ID、APP_ID两个字段上有组合index；
3、APP_ID、XX两个字段上有组合index；

方案1：NOT EXISTS

1. select count(*)
2.   from app_subsc_ctrl a
3.  where a.app_id = '1021'
4.    and not exists (select 1
5.           from app_subsc_ctrl sc
6.          where a.subsc_user_id = sc.subsc_user_id
7.            and sc.app_id <> '1021');

该方案是最为简单、直接、易懂的SQL写法，首先将订购了A软件的所有用户查询出来，然后再对每个用户重新查询一次，用NOT EXISTS保证该用户没有订购过其他软件；
从执行计划上看，该语句会进行两次index scan，整个查询都在index内部完成，效率还不错，但是默认会对app_id = '1021'查询出来的结果集进行逐条过滤，若是没有两个index，或者是查询条件中还有其他不在index中的字段，那效率就会很差！
在此可能一些同学会有疑问，<>怎么可能还用到index呢？那是因为SUBSC_USER_ID、APP_ID两个字段上有组合index的缘故，首先会根据SUBSC_USER_ID进行过滤，然后在index内部对APP_ID进行比较；

方案2：GROUP BY ... [HAVING ...]

首先看以下两条SQL语句：

1. select count(*)
2.   from app_subsc_ctrl a,
3.        (select t.subsc_user_id, count(*) cnt
4.           from app_subsc_ctrl t
5.          group by t.subsc_user_id) b
6.  where a.app_id = '1021'
7.    and b.cnt = 1
8.    and a.subsc_user_id = b.subsc_user_id;

1. select count(*)
2.   from app_subsc_ctrl a,
3.        (select t.subsc_user_id, count(*) cnt
4.           from app_subsc_ctrl t
5.          group by t.subsc_user_id
6.         having count(*) = 1) b
7.  where a.app_id = '1021'
8.    and a.subsc_user_id = b.subsc_user_id;

其实这两条语句的执行效果完全一样，第一条SQL中where条件中的and b.cnt = 1在Oracle执行过程中会自动优化，提前到内层子查询一起完成，所以两者效果完全一样；
该方案是首先对每个用户订购的软件数量进行统计，然后过滤出只订购了1个软件的所有用户，最后判断这些用户订购的这个软件是否就是我们指定的APP；
从执行计划上看，该语句也会进行两次index scan，整个查询也在index内部完成，但由于子查询已将待关联的结果集变小，而且最外层的关联查询默认会以hash join的方式进行，所以该方案要比第一种更优、效率更高；不管整表和待查APP实际订购用户数量的数据量大小，该方案的执行效率不会受到太大影响，所以该方案适应场景较广，推荐使用；

方案3：count(*) over(partition by ...)

1. select count(*)
2.   from (select t.subsc_user_id,
3.                t.app_id,
4.                count(*) over(partition by t.subsc_user_id) cnt
5.           from app_subsc_ctrl t)
6.  where app_id = '1021'
7.    and cnt = 1;

该方案主要是利用分析函数（对分析函数不明白的可以参考《ORACLE进阶之三：分析函数》）来完成；
首先对全表进行扫描，并利用count(*) over(partition by ...)统计出针对每个用户的软件订购总数；然后对该表进行app_id和订阅总数进行过滤；
从执行计划上看，该语句只会在分析函数执行时进行一次index scan，他的执行效率严重依赖于该表中通过app_id = '1021'查询出来的记录数多少，若结果集很大，那么该方案的执行效率会很高；若结果集很小，那执行效率就会很低，因为至少要有一次全表扫描的时间；

综上所述，我们可以看出，对于要实现某个具体需求时，SQL的执行效率是会依赖很多外部因素的，全表数据量的大小、查询结果集的大小、占全表数据量的比例、索引……；到底采用哪种方案，对于查询条件中的不同值，各条SQL的效率也会相差很多，所以具体执行过程中还要具体分析，以便找出最合适的SQL；
需要注意的是：以上的几条SQL中的count(*)可能需要调整，因为SUBSC_USER_ID + APP_ID不是唯一的；严谨的做法是直接写成：count(distinct subsc_user_id)；