案例5　COUNT（*）到底能有多快

1.案例說明

一個大表的COUNT，究竟能有多快？除類似物化視圖的做法，我們所能做到的極限能有多快？這不是一個真實的案例，而是根據(jù)筆者在網(wǎng)上發(fā)的一篇帖子整理而來。通過對一條SQL，采用多種方式持續(xù)優(yōu)化過程，表明SQL優(yōu)化的手段隨著優(yōu)化者掌握的技能增多，其可能存在的手段也在不斷增多。

（1）數(shù)據(jù)準備

數(shù)據(jù)準備的代碼如下：

create table t2 select * from dba_objects；
insert into t2 select * from t2；
...
select count（*） from t2； =>102400000      --數(shù)據(jù)量有1億多條
select bytes/1024/1024 from user_segments where segment_name='T2'；  => 10972    --數(shù)據(jù)對象大小有10多GB

（2）全表掃描

全表掃描的代碼如下（共用124秒，好慢呀）：

select count（*） from t2；
Elapsed： 00：02：04.09
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost （%CPU）| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   381K （1） | 01：16：19 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T2   |   102M|   381K  （1）| 01：16：19 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
1400379  consistent gets
1068862  physical reads

由上可知，全表掃描耗時較長。

（3）主鍵索引

主鍵索引的代碼如下：

alter table t2 add constraint pk_t2 primary key（id）；
execdbms_stats.gather_index_stats（'hf'， 'pk_t2'， estimate_percent =>10）；
select count（*） from t2；
Elapsed： 00：00：33.18
-----------------------------------------------------------------------
| Id  | Operation             | Name  | Rows | Cost （%CPU） | Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |    1 | 64271   （2） | 00：12：52 |
|   1 |  SORT AGGREGATE       |       |    1 |             |          |
|   2 |   INDEX FAST FULL SCAN| PK_T2 |  102M| 64271   （2） | 00：12：52 |
-----------------------------------------------------------------------
Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
228654  consistent gets
205137  physical reads

通過引入索引，執(zhí)行計劃變成索引快速全掃描，因掃描塊數(shù)較少，因此耗時也大大減少，共用33秒，快多了。

（4）常數(shù)索引

常數(shù)索引的代碼如下：

create index idx_0 on t2（0）；
execdbms_stats.gather_index_stats（'hf'， 'idx_0'， estimate_percent =>10）；
select count（*） from t2；
Elapsed： 00：00：28.92
-----------------------------------------------------------------------
| Id  | Operation             | Name  | Rows | Cost （%CPU） | Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |    1 | 49601   （2） | 00：09：56 |
|   1 |  SORT AGGREGATE       |       |    1 |             |          |
|   2 |   INDEX FAST FULL SCAN| IDX_0 |  102M| 49601   （2） | 00：09：56 |
-----------------------------------------------------------------------
Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
185899  consistent gets
167726  physical reads

常數(shù)索引在存儲密度上要高于普通字段索引，因此掃描塊數(shù)更少，耗時也更少，共耗時29秒。

（5）常數(shù)壓縮索引

常數(shù)壓縮索引的代碼如下：

create index idx_0 on t2（0） compress；
execdbms_stats.gather_index_stats（'hf'， 'idx_0'， estimate_percent =>10）；
select count（*） from t2；
Elapsed： 00：00：27.85
-----------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Cost （%CPU）| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 | 43812   （3）| 00：08：46 |
|   1 |  SORT AGGREGATE       |       |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_0 |   102M| 43812   （3）| 00：08：46 |
-----------------------------------------------------------------------
Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
     157636  consistent gets     //壓縮后，減少了
141651  physical reads

索引壓縮進一步減少了掃描規(guī)模，耗時縮減到27秒。

（6）位圖索引

位圖索引的代碼如下：

create bitmap index idx_status2 on t2（status）；
execdbms_stats.gather_index_stats（'hf'， 'idx_status2'， estimate_percent=> 10）；
select count（*） from t2；
Elapsed： 00：00：00.9
--------------------------------------------------------------------------------
| Id  | Operation                | Name        |  Rows  | Cost（%CPU）| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |     1  | 2262  （1） | 00：00：28 |
|   1 |  SORT AGGREGATE          |             |     1  |           |          |
|   2 |   BITMAP CONVERSION COUNT
                                 |             |   102M |  2262  （1）| 00：00：28 |
|   3 |    BITMAP INDEX FAST FULL SCAN
                                 | IDX_STATUS2 |        |           |          |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
       2502  consistent gets     //大大減少
351  physical reads

位圖索引不同于B樹索引，其存儲密度更高。這里是采用status字段，如果使用常數(shù)索引，其規(guī)模將更小。這種手段用時0.9秒，這是質(zhì)的飛躍。

（7）位圖索引+并行

alter index idx_status2 parallel 8；
select count（*） from t2；
Elapsed： 00：00：00.03
--------------------------------------------------------------------------------
| Id  | Operation         | Name       |Rows  |Time    |  TQ |IN-OUT|PQ Distrib|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |    1 |00：00：27|     |      |          |
|   1 |  SORT AGGREGATE   |            |    1 |        |     |      |          |
|   2 |   PX COORDINATOR  |            |      |        |     |      |          |
|   3 |    PX SEND QC （RANDOM）
                          | ：TQ10000   |    1 |        |Q1，00| P->S | QC （RAND）|
|   4 |     SORT AGGREGATE
                          |            |    1 |        |Q1，00| PCWP |          |
|   5 |      PX BLOCK ITERATOR 
                          |            |  102M|00：00：27|Q1，00| PCWC |          |
|   6 |       BITMAP CONVERSION COUNT 
                          |            |  102M|00：00：27|Q1，00| PCWP |          |
|   7 |        BITMAP INDEX FAST FULL SCAN
                          |IDX_STATUS2 |      |        |Q1，00| PCWP |          |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
265  recursive calls
3  db block gets
3059  consistent gets
0  physical reads

并行技術(shù)可以較快執(zhí)行速度。一致性讀有所增加，但并行還是能加快整體運行速度，這種手段耗時0.03秒，竟然又快了不少。

（8）分析結(jié)論

• 位圖索引可以按很高密度存儲數(shù)據(jù)，因此往往比B樹索引小很多。前提是在基數(shù)比較小的情況下。
• 位圖索引是保存空值的，因此可以在COUNT中利用。
• 眾所周知，位圖索引不太適合OLTP類型數(shù)據(jù)庫。該實例僅為了測試展示。

2.給我們的啟示

優(yōu)化沒有止境，對數(shù)據(jù)庫了解越多，你能想到的方法就越多。