Oracle里count(1)、count(*)和count(主鍵)哪個(gè)更快

這兩天聽了將近20場演講，感覺收獲很多，最深的感覺就是自己還有很長的路要走。有幾個(gè)點(diǎn)記錄一下：

昨天聽老貓講，提到一個(gè)普遍的問題就是Oracle里count(*)、count(1)和count(主鍵)到底哪個(gè)快的問題。這個(gè)問題看起來很簡單，每個(gè)人都會(huì)有自己的答案，去百度上搜會(huì)出來一大堆帖子來講哪個(gè)更快。但是老貓說了它們?nèi)齻€(gè)其實(shí)是一樣的，我聽到之后也覺得挺詫異的，因?yàn)槲矣浀脛e人跟我說過count(主鍵)會(huì)快，然后自己簡單想了一下，覺得好像是那么回事的就沒有深入去追究。接著老貓說官方有這樣的說法這三個(gè)其實(shí)是等價(jià)的。晚上回來之后到MOS上查了一下，居然被我找到了How the Oracle CBO Chooses a Path for the SELECT COUNT(*) Command (文檔 ID 124717.1)。這篇文檔講的就是在CBO優(yōu)化器模式下，Oracle怎樣去評(píng)估沒有where條件select count(*)和select count(colum)語句的最優(yōu)路徑。

1、創(chuàng)建測試表并設(shè)計(jì)測試場景：

--創(chuàng)建測試表
sys@ORCL>create table journal_entries 
  2  (id_je number(8) ,
  3  date_je date not null,
  4  balanced number ,
  5  constraint indx_ecr_id_je primary key(id_je)
  6  );

Table created.
--創(chuàng)建索引
sys@ORCL>create index indx_ecr_date_je_balanced on journal_entries(date_je,balanced);

Index created.

sys@ORCL>create index indx_ecr_balanced_date_je on journal_entries(balanced,date_je);

Index created.

sys@ORCL>create index indx_ecr_balanced on journal_entries(balanced);

Index created.
--插入測試數(shù)據(jù)
sys@ORCL>insert into journal_entries values(1,sysdate,11);

1 row created.

sys@ORCL>insert into journal_entries values(2,sysdate,21);

1 row created.

sys@ORCL>insert into journal_entries values(3,sysdate,31);

1 row created.

sys@ORCL>insert into journal_entries values(4,sysdate,41);

1 row created.

sys@ORCL>insert into journal_entries values(5,sysdate,51);

1 row created.

sys@ORCL>insert into journal_entries values(6,sysdate,61);

1 row created.

sys@ORCL>insert into journal_entries values(7,sysdate,71);

1 row created.

sys@ORCL>insert into journal_entries values(8,sysdate,81);

1 row created.

sys@ORCL>insert into journal_entries values(9,sysdate,91);

1 row created.

sys@ORCL>commit;

Commit complete.
--收集統(tǒng)計(jì)信息
sys@ORCL>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'JOURNAL_ENTRIES',cascade=>true);

PL/SQL procedure successfully completed.

設(shè)計(jì)四個(gè)場景進(jìn)行對(duì)比：

Sel4 : Select count(balanced) from journal_entries;

1、場景1和場景2等價(jià)

For CBO, Sel1 and Sel2 are strictly equivalent

sys@ORCL>alter session set statistics_level=all;

Session altered.

sys@ORCL>select count(*) from journal_entries;

  COUNT(*)
----------
         9

sys@ORCL>select * from table(dbms_xplan.display_cursor(null,null,'runstats_last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5ja3ukp4wd73p, child number 0
-------------------------------------
select count(*) from journal_entries

Plan hash value: 42135099

---------------------------------------------------------------------------------------------
| Id  | Operation        | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |      1 |        |      1 |00:00:00.01 |       1 |
|   1 |  SORT AGGREGATE  |                |      1 |      1 |      1 |00:00:00.01 |       1 |
|   2 |   INDEX FULL SCAN| INDX_ECR_ID_JE |      1 |      9 |      9 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------


14 rows selected.

sys@ORCL>select count(1) from journal_entries;

  COUNT(1)
----------
         9

sys@ORCL>select * from table(dbms_xplan.display_cursor(null,null,'runstats_last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gbxjjuqj9j7ww, child number 0
-------------------------------------
select count(1) from journal_entries

Plan hash value: 42135099

---------------------------------------------------------------------------------------------
| Id  | Operation        | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |      1 |        |      1 |00:00:00.01 |       1 |
|   1 |  SORT AGGREGATE  |                |      1 |      1 |      1 |00:00:00.01 |       1 |
|   2 |   INDEX FULL SCAN| INDX_ECR_ID_JE |      1 |      9 |      9 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------


14 rows selected.

可以看到兩個(gè)語句的執(zhí)行計(jì)劃是完全相同的。

2、場景3也與前兩個(gè)場景等價(jià)，因?yàn)閕d_je有NOT NULL約束

For Sel3, CBO does the same as for Sel1 and Sel2 since "id_je" has a NOT NULL constraint.

sys@ORCL>select count(id_je) from journal_entries;

COUNT(ID_JE)
------------
           9

sys@ORCL>select * from table(dbms_xplan.display_cursor(null,null,'runstats_last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b1p4v15dwx7hs, child number 0
-------------------------------------
select count(id_je) from journal_entries

Plan hash value: 42135099

---------------------------------------------------------------------------------------------
| Id  | Operation        | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |      1 |        |      1 |00:00:00.01 |       1 |
|   1 |  SORT AGGREGATE  |                |      1 |      1 |      1 |00:00:00.01 |       1 |
|   2 |   INDEX FULL SCAN| INDX_ECR_ID_JE |      1 |      9 |      9 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------


14 rows selected.

可以看到執(zhí)行計(jì)劃與前兩個(gè)也是完全相同的。

4、場景4跟前邊3個(gè)不同，因?yàn)閎alanced列上沒有NOT NULL約束，但是balanced列上有索引，那會(huì)走這個(gè)列上的索引么？我們來看一下執(zhí)行計(jì)劃：

sys@ORCL>select count(balanced) from journal_entries;

COUNT(BALANCED)
---------------
              9

sys@ORCL>select * from table(dbms_xplan.display_cursor(null,null,'runstats_last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bc3bc8c0fg14z, child number 0
-------------------------------------
select count(balanced) from journal_entries

Plan hash value: 3638043346

--------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                           |      1 |        |      1 |00:00:00.01 |       1 |
|   1 |  SORT AGGREGATE  |                           |      1 |      1 |      1 |00:00:00.01 |       1 |
|   2 |   INDEX FULL SCAN| INDX_ECR_DATE_JE_BALANCED |      1 |      9 |      9 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------------


14 rows selected.

我們看到這個(gè)執(zhí)行計(jì)劃沒有走balanced列上的索引，而是走了和date_je的聯(lián)合索引。這個(gè)可以查看另一篇文檔：Note:67522.1 Why is my index not used?

小結(jié)一下：

我這里只是簡單的從執(zhí)行計(jì)劃上看count(*)、count(1)和count(主鍵)其實(shí)是一致，MOS的文檔中詳細(xì)的講解了Oracle是如何評(píng)估執(zhí)行計(jì)劃的，也可以使用10053 event查看CBO優(yōu)化器是如何做出選擇的。由于我的功力還不夠，對(duì)于10053事件還不是很明白，暫時(shí)就先不做演示了，要不哪說錯(cuò)了就不好了，這也可以做為以后博客分享的內(nèi)容。

從這個(gè)事情上來看，我們對(duì)于一件事情應(yīng)該做一個(gè)深入的研究，有充足的證據(jù)來證明，尤其是想要在某一方面有深入發(fā)展的時(shí)候。