国产数据库创新集锦⓵——小count也有大智慧
收
梁敬彬 梁敬弘约 6 分钟
梁敬彬 梁敬弘约 6 分钟引言
问，select count(*) from t 能否优化？有人纳闷了，如此简单，还能有优化空间？是的，简单的背后不简单。
本文将通过Oracle数据库的各种既有手段，展示count(*)从单车变火箭，踏上性能腾飞的梦幻之旅。然而，火箭并非尽头，因为创新永无止境，小小count也能蕴藏大智慧！接下来，国产原创数据库的创新大表演正式拉开序幕。
注：手机端脚本展现不全，可左右滑动观看。
1
顺其自然 ，性能很拉垮
大饼 彬老师，select count(*) from t 这种简单的SQL，还有优化空间吗？
彬老师，select count(*) from t 这种简单的SQL，还有优化空间吗？
先看看什么都不做的情况，测试如下：
DROP TABLE t PURGE;CREATE TABLE t AS SELECT * FROM dba_objects;ALTER TABLE T MODIFY OBJECT_NAME NOT NULL;SELECT COUNT(*) FROM t;SET AUTOTRACE TRACEONLY;SET LINESIZE 1000;SET TIMING ON;SELECT COUNT(*) FROM T;-- 执行计划Plan hash value: 2966233522-------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU) | Time || 0 | SELECT STATEMENT | | 1 | 292 (1)| 00:00:04 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL| T | 92256 | 292 (1)| 00:00:04 |---------------------------------------------------------------------- 统计信息 0 recursive calls 0 db block gets 1048 consistent gets
执行计划走的是全表扫描，逻辑读高达1048，看起来性能的确很拉垮。
2
普通索引，单车变摩托
drop table t purge;create table t as select * from dba_objects;alter table T modify OBJECT_NAME not null;create index idx_object_name on t(object_name);set autotrace traceonly;set timing on ;select count(*) from t;执行计划---------------------------------Plan hash value: 1178070731--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 105 (1)| 00:00:02 || 1 | SORT AGGREGATE | | 1 | | || 2 | INDEX FAST FULL SCAN| IDX_OBJECT_NAME | 92256 | 105 (1)| 00:00:02 |--------------------------------------------------------------------------------------统计信息--------------------------------- 0 recursive calls 0 db block gets 372 consistent gets
执行计划从全表扫描变成了全索引扫描，逻辑读从1048降到372，单车变摩托了！
效果很炸裂啊！不过，count(*) 这种遍历所有记录的查询，不是一般不建议使用索引吗？
这个问题问得好。索引的本质是索引列值加上对应的rowid。当我们只需要计数时，访问索引比访问完整的表数据要小得多，IO开销自然大幅降低。
原来如此！通过访问更小的数据结构来完成相同任务，确实是个聪明的方法。
3
位图索引，摩托变赛车彬老师 
当然！我们来尝试位图索引的威力： drop table t purge;create table t as select * from dba_objects;Update t Set object_name='abc';Update t Set object_name='evf' Where rownum<=20000;create bitmap index idx_object_name on t(object_name);set autotrace traceonly;set timing on;select count(*) from t;执行计划----------------------------------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| | 92256 | 5 (0)| 00:00:01 || 3 | BITMAP INDEX FAST FULL SCAN| IDX_OBJECT_NAME | | | |-------------------------------------------------------------------------------------------统计信息---------------------------------- 0 recursive calls 0 db block gets 6 consistent gets
drop table t purge;create table t as select * from dba_objects;Update t Set object_name='abc';Update t Set object_name='evf' Where rownum<=20000;create bitmap index idx_object_name on t(object_name);set autotrace traceonly;set timing on;select count(*) from t;执行计划----------------------------------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| | 92256 | 5 (0)| 00:00:01 || 3 | BITMAP INDEX FAST FULL SCAN| IDX_OBJECT_NAME | | | |-------------------------------------------------------------------------------------------统计信息---------------------------------- 0 recursive calls 0 db block gets 6 consistent gets
4
物化视图，赛车变飞机
create table t as select * from dba_objects;Update t Set object_name='abc';Update t Set object_name='evf' Where rownum<=20000;create materialized view mv_count_tbuild immediaterefresh on commitenable query rewriteasselect count(*) FROM T;select COUNT(*) FROM T;执行计划---------------------------------Plan hash value: 3655891017---------------------------------------------------------------------------------------|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 | 1 | 13 | 3 (0)| 00:00:01 |----------------------------------------------------------------------------------------0 recursive calls0 db block gets3 consistent gets
厉害，手段真多啊！
5
缓存结果，飞机变火箭
drop table t purge;create table t as select * from dba_objects;select count(*) from t;set linesize 1000;set autotrace traceonly;select /*+ result_cache */ count(*) from t;执行计划---------------------------------Plan hash value: 2966233522----------------------------------------------------------------------------------|Id | Operation | Name | Rows | Cost (%CPU)| Time |----------------------------------------------------------------------------------| 0 | SELECT STATEMENT| | 1 | 23 (0)| 00:00:01 || 1 | RESULT CACHE | 034w33ddtzk0mc7kvwt84zayre | | | || 2 | SORT AGGREGATE| | 1 | | || 3 | TABLE ACCESS FULL| T | 10000 | 23 (0)| 00:00:01 |----------------------------------------------------------------------------------统计信息--------------------------------- 0 recursive calls 0 db block gets 0 consistent gets
6
等价改写，平地起惊雷
begin select count(*) into v_cnt from t1; if v_cnt > 0 then ...A逻辑... -- 表中有记录时执行 else ...B逻辑... -- 表中没有记录时执行 end if;end;