distinct xx和count(distinct xx)的变态递归优化方法

RDS PostgreSQL Serverless，0.5-4RCU 50GB 3个月

RDS SQL Server Serverless，2-4RCU 50GB 3个月

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

http://blog.163.com/digoal@126/blog/static/16387704020128142829610/

CASE

select count(distinct sex) from table; 得到的结果当然是2. 但是如果数据量很大的情况下, 这种运算就非常耗时, 下面来测试一下 :

PostgreSQL

digoal=> create table sex (sex char(1), otherinfo text);
CREATE TABLE  

digoal=> insert into sex select 'm', generate_series(1,10000000)||'this is test';
INSERT 0 10000000
digoal=> insert into sex select 'w', generate_series(1,10000000)||'this is test';
INSERT 0 10000000  

digoal=> \timing on
digoal=> select count(distinct sex) from sex;
count
-------
2
(1 row)
Time: 47254.221 ms  

digoal=> select sex from sex t group by sex;
sex
-----
w
m
(2 rows)
Time: 6534.433 ms  

digoal=> explain select count(distinct sex) from sex;
QUERY PLAN
---------------------------------------------------------------------
Aggregate  (cost=377385.25..377385.26 rows=1 width=2)
->  Seq Scan on sex  (cost=0.00..327386.00 rows=19999700 width=2)

digoal=> explain select sex from sex t group by sex;
QUERY PLAN
-----------------------------------------------------------------------
HashAggregate  (cost=377385.25..377385.27 rows=2 width=2)
->  Seq Scan on sex t  (cost=0.00..327386.00 rows=19999700 width=2)  

digoal=> create index idx_sex_1 on sex(sex);
CREATE INDEX
digoal=> set enable_seqscan=off;
SET  

digoal=> explain select count(distinct sex) from sex;
QUERY PLAN
--------------------------------------------------------------------------------------------
Aggregate  (cost=532235.01..532235.02 rows=1 width=2)
->  Index Only Scan using idx_sex_1 on sex  (cost=0.00..482234.97 rows=20000016 width=2)

digoal=> explain select sex from sex t group by sex;
QUERY PLAN
----------------------------------------------------------------------------------------------
Group  (cost=0.00..532235.01 rows=2 width=2)
->  Index Only Scan using idx_sex_1 on sex t  (cost=0.00..482234.97 rows=20000016 width=2)  

digoal=> select count(distinct sex) from sex;
count
-------
2
(1 row)
Time: 49589.947 ms

digoal=> select sex from sex t group by sex;
sex
-----
m
w
(2 rows)
Time: 6608.053 ms  

O

SQL> create table sex(sex char(1), otherinfo varchar2(64));
Table created.  

SQL> insert into sex select 'm', rownum||'this is test' from dual connect by level <=10000001;
10000001 rows created.
SQL> commit;
Commit complete.
SQL> insert into sex select 'w', rownum||'this is test' from dual connect by level <=10000001;
10000001 rows created.
SQL> commit;
Commit complete.  

SQL> set autotrace on
SQL> set timing on
SQL> select count(distinct sex) from sex;
COUNT(DISTINCTSEX)
------------------
2
Elapsed: 00:00:03.62

Execution Plan
----------------------------------------------------------
Plan hash value: 2096505595
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 | 13106   (3)| 00:02:38 |
|   1 |  SORT GROUP BY     |      |     1 |     3 |            |          |
|   2 |   TABLE ACCESS FULL| SEX  |    24M|    69M| 13106   (3)| 00:02:38 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
74074  consistent gets
0  physical reads
0  redo size
525  bytes sent via SQL*Net to client
487  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
1  rows processed  

SQL> select sex from sex t group by sex;
S
-
w
m
Elapsed: 00:00:03.23
Execution Plan
----------------------------------------------------------
Plan hash value: 2807610159
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    24M|    69M| 14908  (14)| 00:02:59 |
|   1 |  HASH GROUP BY     |      |    24M|    69M| 14908  (14)| 00:02:59 |
|   2 |   TABLE ACCESS FULL| SEX  |    24M|    69M| 13106   (3)| 00:02:38 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
74074  consistent gets
0  physical reads
0  redo size
563  bytes sent via SQL*Net to client
487  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
2  rows processed  

SQL> create index idx_sex_1 on sex(sex);
Index created.
Elapsed: 00:00:33.40  

SQL> select count(distinct sex) from sex;
COUNT(DISTINCTSEX)
------------------
2
Elapsed: 00:00:04.32
Execution Plan
----------------------------------------------------------
Plan hash value: 1805173869
-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |     3 |  6465   (3)| 00:01:18 |
|   1 |  SORT GROUP BY        |           |     1 |     3 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_SEX_1 |    24M|    69M|  6465   (3)| 00:01:18 |
-----------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5  recursive calls
0  db block gets
36421  consistent gets
36300  physical reads
0  redo size
525  bytes sent via SQL*Net to client
487  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
1  rows processed
SQL> select sex from sex t group by sex;
S
-
w
m
Elapsed: 00:00:03.21
Execution Plan
----------------------------------------------------------
Plan hash value: 2807610159
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    24M|    69M| 14908  (14)| 00:02:59 |
|   1 |  HASH GROUP BY     |      |    24M|    69M| 14908  (14)| 00:02:59 |
|   2 |   TABLE ACCESS FULL| SEX  |    24M|    69M| 13106   (3)| 00:02:38 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5  recursive calls
0  db block gets
74170  consistent gets
0  physical reads
0  redo size
563  bytes sent via SQL*Net to client
487  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
2  rows processed  

digoal=> select count(*) from (select sex from sex t group by sex) t;
count
-------
2
(1 row)
Time: 6231.965 ms  

开始优化咯

create table user_download_log (user_id int not null, listid int not null, apkid int not null, get_time timestamp(0) not null, otherinfo text);  

insert into user_download_log select generate_series(0,10000000),generate_series(0,10000000),generate_series(0,10000000),generate_series(clock_timestamp(),clock_timestamp()+interval '10000000 min',interval '1 min'), 'this is test';  

create index i1 on user_download_log (user_id);
create index i2 on user_download_log (otherinfo);  

select count(distinct user_id), count(distinct otherinfo) from user_download_log;
count   | count
----------+-------
10000001 |     1  

digoal=> explain analyze select count(distinct otherinfo) from user_download_log;
QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------
----
Aggregate  (cost=208334.36..208334.37 rows=1 width=13) (actual time=6295.493..6295.494 rows=1 loops=1)
->  Seq Scan on user_download_log  (cost=0.00..183334.29 rows=10000029 width=13) (actual time=0.014..1612.333 rows=10000001 loops
=1)
Total runtime: 6295.550 ms  

digoal=> with recursive skip as (
digoal(>   (
digoal(>     select min(t.otherinfo) as otherinfo from user_download_log t where t.otherinfo is not null
digoal(>   )
digoal(>   union all
digoal(>   (
digoal(>     select (select min(t.otherinfo) from user_download_log t where t.otherinfo > s.otherinfo and t.otherinfo is not null)
digoal(>       from skip s where s.otherinfo is not null
digoal(>   )  -- 这里的where s.otherinfo is not null 一定要加,否则就死循环了.
digoal(> )
digoal-> select count(distinct otherinfo) from skip;
count
-------
1
(1 row)  

digoal=> explain analyze with recursive skip as (
(
select min(t.otherinfo) as otherinfo from user_download_log t where t.otherinfo is not null
)
union all
(
select (select min(t.otherinfo) from user_download_log t where t.otherinfo > s.otherinfo and t.otherinfo is not null)
from skip s where s.otherinfo is not null
)  -- 这里的where s.otherinfo is not null 一定要加,否则就死循环了.
)
select count(distinct otherinfo) from skip;
QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------
Aggregate  (cost=10.55..10.56 rows=1 width=32) (actual time=0.094..0.094 rows=1 loops=1)
CTE skip
->  Recursive Union  (cost=0.03..8.28 rows=101 width=32) (actual time=0.044..0.073 rows=2 loops=1)
->  Result  (cost=0.03..0.04 rows=1 width=0) (actual time=0.042..0.042 rows=1 loops=1)
InitPlan 1 (returns $1) -> Limit (cost=0.00..0.03 rows=1 width=13) (actual time=0.038..0.039 rows=1 loops=1) -> Index Only Scan using i2 on user_download_log t (cost=0.00..296844.61 rows=10000029 width=13) (actual time=0.037..0.037 rows=1 loops=1) Index Cond: (otherinfo IS NOT NULL) Heap Fetches: 1 -> WorkTable Scan on skip s (cost=0.00..0.62 rows=10 width=32) (actual time=0.013..0.013 rows=0 loops=2) Filter: (otherinfo IS NOT NULL) Rows Removed by Filter: 0 SubPlan 3 -> Result (cost=0.03..0.04 rows=1 width=0) (actual time=0.018..0.018 rows=1 loops=1) InitPlan 2 (returns$3)
->  Limit  (cost=0.00..0.03 rows=1 width=13) (actual time=0.017..0.017 rows=0 loops=1)
->  Index Only Scan using i2 on user_download_log t  (cost=0.00..107284.96 rows=3333343 width=13) (
actual time=0.015..0.015 rows=0 loops=1)
Index Cond: ((otherinfo > s.otherinfo) AND (otherinfo IS NOT NULL))
Heap Fetches: 0
->  CTE Scan on skip  (cost=0.00..2.02 rows=101 width=32) (actual time=0.047..0.077 rows=2 loops=1)
Total runtime: 0.173 ms
(21 rows)  

digoal=> explain analyze select count(distinct user_id) from user_download_log;
QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------
---
Aggregate  (cost=208334.36..208334.37 rows=1 width=4) (actual time=4008.858..4008.858 rows=1 loops=1)
->  Seq Scan on user_download_log  (cost=0.00..183334.29 rows=10000029 width=4) (actual time=0.014..1606.607 rows=10000001 loops=
1)
Total runtime: 4008.916 ms  

digoal=> explain analyze with recursive skip as (
(
select min(t.user_id) as user_id from user_download_log t where t.user_id is not null
)
union all
(
select (select min(t.user_id) from user_download_log t where t.user_id > s.user_id and t.user_id is not null)
from skip s where s.user_id is not null
)  -- 这里的where s.user_id is not null 一定要加,否则就死循环了.
)
select count(distinct user_id) from skip;
QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------
Aggregate  (cost=10.44..10.45 rows=1 width=4) (actual time=186741.338..186741.339 rows=1 loops=1)
CTE skip
->  Recursive Union  (cost=0.03..8.17 rows=101 width=4) (actual time=0.047..178296.238 rows=10000002 loops=1)
->  Result  (cost=0.03..0.04 rows=1 width=0) (actual time=0.046..0.046 rows=1 loops=1)
InitPlan 1 (returns $1) -> Limit (cost=0.00..0.03 rows=1 width=4) (actual time=0.042..0.042 rows=1 loops=1) -> Index Only Scan using i1 on user_download_log t (cost=0.00..285759.50 rows=10000029 width=4) (actual t ime=0.040..0.040 rows=1 loops=1) Index Cond: (user_id IS NOT NULL) Heap Fetches: 1 -> WorkTable Scan on skip s (cost=0.00..0.61 rows=10 width=4) (actual time=0.017..0.017 rows=1 loops=10000002) Filter: (user_id IS NOT NULL) Rows Removed by Filter: 0 SubPlan 3 -> Result (cost=0.03..0.04 rows=1 width=0) (actual time=0.016..0.016 rows=1 loops=10000001) InitPlan 2 (returns$3)
->  Limit  (cost=0.00..0.03 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=10000001)
->  Index Only Scan using i1 on user_download_log t  (cost=0.00..103588.85 rows=3333343 width=4) (a
ctual time=0.014..0.014 rows=1 loops=10000001)
Index Cond: ((user_id > s.user_id) AND (user_id IS NOT NULL))
Heap Fetches: 10000000
->  CTE Scan on skip  (cost=0.00..2.02 rows=101 width=4) (actual time=0.050..183449.391 rows=10000002 loops=1)
Total runtime: 186909.323 ms
(21 rows)
Time: 186910.482 ms  

SQL> create table test (id int, otherinfo varchar2(32)) nologging;
Table created.
SQL> insert into test select rownum,'this is test' from dual connect by level <=10000001;
10000001 rows created.
SQL> commit;
SQL> create index i1 on test(id);
SQL> create index i2 on test(otherinfo);
SQL> explain plan for select count(distinct id) from test;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1403727100
------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    13 |  4178   (3)| 00:00:51 |
|   1 |  SORT GROUP BY        |      |     1 |    13 |            |          |
|   2 |   INDEX FAST FULL SCAN| I1   |  9834K|   121M|  4178   (3)| 00:00:51 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
13 rows selected.
SQL> explain plan for select count(distinct otherinfo) from test;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2603667166
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    18 |  5837   (3)| 00:01:11 |
|   1 |  SORT GROUP BY     |      |     1 |    18 |            |          |
|   2 |   TABLE ACCESS FULL| TEST |  9834K|   168M|  5837   (3)| 00:01:11 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
13 rows selected.
SQL> set timing on
SQL> select count(distinct otherinfo) from test;
COUNT(DISTINCTOTHERINFO)
------------------------
1
Elapsed: 00:00:02.49
SQL> select count(distinct id) from test;
COUNT(DISTINCTID)
-----------------
10000001
Elapsed: 00:00:07.13  

补充

with recursive skip as (
(
select min(t.otherinfo) as otherinfo from user_download_log t where t.otherinfo is not null
)
union all
(
select min(t.otherinfo) from user_download_log t, skip s
where t.otherinfo > s.otherinfo
and t.otherinfo is not null
and s.otherinfo is not null
)  -- 这里的where s.otherinfo is not null 一定要加,否则就死循环了.
)
select * from skip;
ERROR:  aggregate functions not allowed in a recursive query's recursive term
LINE 7:     select min(t.otherinfo) from user_download_log t, skip s...
^
Time: 0.581 ms  

with recursive skip as (
(
select min(t.otherinfo) as otherinfo from user_download_log t where t.otherinfo is not null
)
union all
(
select (select min(t.otherinfo) from user_download_log t where t.otherinfo > s.otherinfo and t.otherinfo is not null)
from skip s where s.otherinfo is not null
)  -- 这里的where s.otherinfo is not null 一定要加,否则就死循环了.
)
select * from skip;  

SQL> analyze table sex estimate statistics for all columns sample 10 percent;
Table analyzed.
SQL> analyze index idx_sex_1 estimate statistics sample 10 percent;
Index analyzed.
SQL> select sex from sex t group by sex;

S
-
w
m

Elapsed: 00:00:03.17

Execution Plan
----------------------------------------------------------
Plan hash value: 2807610159

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |     2 | 14519  (12)| 00:02:55 |
|   1 |  HASH GROUP BY     |      |     2 |     2 | 14519  (12)| 00:02:55 |
|   2 |   TABLE ACCESS FULL| SEX  |    20M|    19M| 13062   (2)| 00:02:37 |
---------------------------------------------------------------------------

Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
74074  consistent gets
0  physical reads
0  redo size
563  bytes sent via SQL*Net to client
487  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
2  rows processed

SQL> select count(distinct sex) from sex;

COUNT(DISTINCTSEX)
------------------
2

Elapsed: 00:00:03.85

Execution Plan
----------------------------------------------------------
Plan hash value: 1805173869

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |     1 |  6454   (3)| 00:01:18 |
|   1 |  SORT GROUP BY        |           |     1 |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_SEX_1 |    20M|    19M|  6454   (3)| 00:01:18 |
-----------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
36325  consistent gets
0  physical reads
0  redo size
525  bytes sent via SQL*Net to client
487  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
1  rows processed  

O在这类应用场景中还有一个选择，使用位图索引。

|
3月前
|
SQL 算法 大数据

41 0
count去重和distinct去重
count去重和distinct去重
6874 0
|

Select Count 聚合函数三种不同写法区别与效率性能比较
Select Count 聚合函数三种不同写法区别与效率性能比较
464 0
|

select distinct去掉重复查询结果|学习笔记

240 0

1122 0
|