【DB筆試面試622】在Oracle中，說說COUNT(*)計算行數有哪些優化手段？
- 2019 年 10 月 10 日
- 筆記
題目部分
在Oracle中，說說COUNT(*)計算行數有哪些優化手段？
♣
答案部分
|
手段 |
命令 |
執行計劃 |
主要原理 |
詳細說明 |
性能情況 |
|---|---|---|---|---|---|
|
全表掃描 |
|
TABLE ACCESS FULL |
全表掃描 |
OLTP中，通常是最慢的方式。 |
邏輯讀為1139 |
|
增加普通索引 |
CREATE INDEX IDX_OBJECT_NAME ON T(OBJECT_NAME); |
INDEX FAST FULL SCAN |
從全表掃描轉成全索引掃描。 |
因為索引一般比表小的多，所以全表掃描轉成全索引掃描，性能能大幅度提升。 |
邏輯讀為400 |
|
常數索引 |
CREATE INDEX IDX_OBJECT_NAME ON T(0); |
INDEX FAST FULL SCAN |
從全表掃描轉成全索引掃描。 |
常數索引比普通索引更小。 |
邏輯讀為151 |
|
常數壓縮索引 |
CREATE INDEX IDX_OBJECT_NAME ON T(0) COMPRESS; |
INDEX FAST FULL SCAN |
從全表掃描轉成全索引掃描。 |
常數壓縮索引比常數索引更小。 |
邏輯讀為129 |
|
位圖索引 |
CREATE BITMAP INDEX IDX_OBJECT_NAME ON T(OBJECT_NAME); |
BITMAP INDEX FAST FULL SCAN |
從BTREE索引掃描轉成位圖索引掃描。 |
位圖索引的大小比BTREE索引要小的多，所以位圖索引掃描快。 |
邏輯讀為5 |
|
物化視圖 |
CREATE MATERIALIZED VIEW MV_COUNT_T BUILD IMMEDIATE REFRESH ON COMMIT ENABLE QUERY REWRITEAS SELECT COUNT(*) FROM T; |
MAT_VIEW REWRITE ACCESS FULL |
空間換時間。 |
要注意，如果數據要求比較實時，就不適用。 |
邏輯讀為3 |
|
緩存結果 |
SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T; |
RESULT CACHE |
直接把查詢結果拿來用。 |
要注意，如果數據頻繁更新，就不適用。 |
邏輯讀為0 |
|
業務理解 |
SELECT COUNT(*) FROM T WHERE ROWNUM=1; |
|
如果COUNT(*)只是為了判斷條數，就加上ROWNUM=1來判斷是否為1。 |
業務需求轉換，獲取條數有的時候，只是為了看看錶是否為空，這時候是否是1條和是否大於0其實是一樣的。 |
不言而喻 |
|
分析需求 |
據說，這個COUNT(*)統計條數語句，是多餘的！直接砍了這條語句，這裡沒有SQL! |
無敵！ |
|||
位圖索引可以按很高密度存儲數據，因此往往比B樹索引小很多，前提是在基數比較小（列重複度比較高）的情況下。位圖索引是保存空值的，因此可以在COUNT中利用。位圖索引不太適合OLTP類型數據庫。物化視圖是應用在數據要求不怎麼及時的場景下。若表頻繁更新，則不適合緩存結果集。
優化沒有止境，對數據庫了解越多，能想到的方法就越多。
--無索引 DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; SET AUTOTRACE TRACEONLY SET LINESIZE 1000 SET TIMING ON SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; --普通索引 DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; CREATE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(OBJECT_NAME); SET AUTOTRACE TRACEONLY SET TIMING ON SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; --唯一索引 DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; UPDATE T_20170704_COUNT_LHR_01 T SET T.OBJECT_NAME=T.OBJECT_NAME||ROWNUM; CREATE unique INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(OBJECT_NAME); SET AUTOTRACE TRACEONLY SET TIMING ON SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; --常數索引 DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; CREATE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(0); SET AUTOTRACE TRACEONLY SET TIMING ON SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; --常數壓縮索引 DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; CREATE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(0) COMPRESS; SET AUTOTRACE TRACEONLY SET TIMING ON SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; --位圖索引 DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='lhr'; UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='ldd' WHERE ROWNUM<=20000; CREATE BITMAP INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(OBJECT_NAME); SET AUTOTRACE TRACEONLY SET TIMING ON SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; ALTER INDEX IDX_OBJECT_NAME PARALLEL 8; SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; -- 物化視圖 DROP MATERIALIZED VIEW MV_COUNT_T_LHR; DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='lhr'; UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='ldd' WHERE ROWNUM<=20000; CREATE MATERIALIZED VIEW MV_COUNT_T_LHR BUILD IMMEDIATE REFRESH ON COMMIT ENABLE QUERY REWRITE AS SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; SET AUTOTRACE TRACEONLY SET LINESIZE 1000 SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; --緩存結果集 DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; SET LINESIZE 1000 SET AUTOTRACE TRACEONLY SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T_20170704_COUNT_LHR_01; --業務分析 SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01 WHERE ROWNUM=1;
一、 普通表（無索引）
DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; COUNT(*) ---------- 79300 LHR@orclasm > SET AUTOTRACE TRACEONLY LHR@orclasm > SET LINESIZE 1000 LHR@orclasm > SET TIMING ON LHR@orclasm > LHR@orclasm > LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 1395805058 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 317 (1)| 00:00:04 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T_20170704_COUNT_LHR_01 | 91429 | 317 (1)| 00:00:04 | -------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1139 consistent gets 0 physical reads 0 redo size 527 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
二、 普通索引
DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; CREATE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(OBJECT_NAME); SET AUTOTRACE TRACEONLY SET TIMING ON LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; Elapsed: 00:00:00.20 Execution Plan ---------------------------------------------------------- Plan hash value: 1178070731 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 114 (1)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IDX_OBJECT_NAME | 91429 | 114 (1)| 00:00:02 | --------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 400 consistent gets 0 physical reads 0 redo size 527 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
--1、主鍵索引（唯一索引） DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; CREATE UNIQUE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(OBJECT_NAME); SET AUTOTRACE TRACEONLY SET TIMING ON SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; --2、常數索引 DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; CREATE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(0); SET AUTOTRACE TRACEONLY SET TIMING ON SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 1178070731 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 45 (3)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IDX_OBJECT_NAME | 86597 | 45 (3)| 00:00:01 | --------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 151 consistent gets 0 physical reads 0 redo size 528 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed --3、常數壓縮索引 DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; CREATE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(0) COMPRESS; SET AUTOTRACE TRACEONLY SET TIMING ON SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; LHR@orclasm > LHR@orclasm > LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1178070731 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IDX_OBJECT_NAME | 86597 | 38 (0)| 00:00:01 | --------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 129 consistent gets 0 physical reads 0 redo size 528 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
三、 位圖索引
試驗如下：
DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='lhr'; UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='ldd' WHERE ROWNUM<=20000; CREATE BITMAP INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(OBJECT_NAME); SET AUTOTRACE TRACEONLY SET TIMING ON SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1696023018 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | BITMAP CONVERSION COUNT | | 91429 | 5 (0)| 00:00:01 | | 3 | BITMAP INDEX FAST FULL SCAN| IDX_OBJECT_NAME | | | | ----------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 527 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
1、 位圖索引+並行
ALTER INDEX IDX_OBJECT_NAME PARALLEL 8; SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01;
並行技術可以加快執行速度，但一致性讀有所增加，但並行還是能加快整體運行速度。
四、 物化視圖
這主要是應用在數據庫更新不是非常頻繁場景，用的是空間換時間。
DROP MATERIALIZED VIEW MV_COUNT_T_LHR; DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='lhr'; UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='ldd' WHERE ROWNUM<=20000; CREATE MATERIALIZED VIEW MV_COUNT_T_LHR BUILD IMMEDIATE REFRESH ON COMMIT ENABLE QUERY REWRITE AS SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; SET AUTOTRACE TRACEONLY SET LINESIZE 1000 SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; Elapsed: 00:00:00.08 Execution Plan ---------------------------------------------------------- Plan hash value: 571421573 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 | | 1 | MAT_VIEW REWRITE ACCESS FULL| MV_COUNT_T_LHR | 1 | 13 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 531 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
五、 緩存結果
在Oracle 11g中提供了結果集緩存特性。該緩存是在共享內存中存儲全部的結果集。如果一個查詢SQL被執行，且它對應的結果集在緩存中，那麼，該SQL的幾乎全部開銷都可以避免。
DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; SET LINESIZE 1000 SET AUTOTRACE TRACEONLY SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T_20170704_COUNT_LHR_01; LHR@orclasm > LHR@orclasm > SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T_20170704_COUNT_LHR_01; Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1395805058 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 317 (1)| 00:00:04 | | 1 | RESULT CACHE | 6pp2f468gdjnj9v3s3mfwffd7t | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | TABLE ACCESS FULL| T_20170704_COUNT_LHR_01 | 86597 | 317 (1)| 00:00:04 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; dependencies=(LHR.T_20170704_COUNT_LHR_01); attributes=(single-row); name="SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T_20170704_COUNT_LHR_01" Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 528 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
六、 根據業務規則判斷
若統計行數只是為了判斷表中是否有記錄，則可以使用ROWNUM=1，所以改寫後的SQL變為：
SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01 WHERE ROWNUM=1;
該SQL無論表中數據多大，性能都不會太差。
八、 分析需求
仔細分析需求後，可能會發現，統計行數這條SQL根本就是多餘的，那麼這條SQL語句就可以直接砍掉了。
& 說明：
有關COUNT的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2141601/
本文選自《Oracle程序員面試筆試寶典》，作者：李華榮。
