COUNT(*)计算行数有哪些优化手段

COUNT(*)计算行数有哪些优化手段


--无索引
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语句就可以直接砍掉了。

八、总结


手段
命令
执行计划
主要原理
详细说明
性能情况

全表扫描
 
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 REWRITE

AS  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类型数据库。物化视图是应用在数据要求不怎么及时的场景下。若表频繁更新，则不适合缓存结果集。
优化没有止境，对数据库了解越多，能想到的方法就越多。

你真的会用索引吗？来看看COUNT(*)到底能有多快

https://mp.weixin.qq.com/s?__biz=MjM5MDAxOTk2MQ==&mid=2650272185&idx=1&sn=77808908dbeab10781f647932761f475&chksm=be4869af893fe0b9c7a513059e5d979af476e973c5b9f64f6661d3d3ed64e22e61a4d2ff0814&mpshare=1&scene=23&srcid=0704h21rWZZcKqSvJupXJliK#rd

编辑手记：韩老师在数据库性能优化方面有很丰富的经验，出版书籍《SQL 优化最佳实践》，感谢韩老师和机械工业出版社的授权，我们会在接下来的每周二分享书中的经典篇章，与大家共同成长。

案例说明

一个大表的COUNT，究竟能有多快？除类似物化视图的做法，我们所能做到的极限能有多快？这不是一个真实的案例，而是根据笔者在网上发的一篇帖子整理而来。通过对一条SQL，采用多种方式持续优化过程，表明SQL优化的手段随着优化者掌握的技能增多，其可能存在的手段也在不断增多。

1、数据准备

2、全表扫描

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

由上可知，全表扫描耗时较长。

3、主键索引

主键索引的代码如下：

通过引入索引，执行计划变成索引快速全扫描，因扫描块数较少，因此耗时也大大减少，共用33秒，快多了。

4、常数索引

常数索引的代码如下：

常数索引在存储密度上要高于普通字段索引，因此扫描块数更少，耗时也更少，共耗时29秒。


5、常数压缩索引

常数压缩索引的代码如下

索引压缩进一步减少了扫描规模，耗时缩减到27秒。

6、位图索引

位图索引不同于B树索引，其存储密度更高。这里是采用status字段，如果使用常数索引，其规模将更小。这种手段用时0.9秒，这是质的飞跃。

7、位图索引+并行

并行技术可以较快执行速度。一致性读有所增加，但并行还是能加快整体运行速度，这种手段耗时0.03秒，竟然又快了不少。

结论分析

位图索引可以按很高密度存储数据，因此往往比B树索引小很多。前提是在基数比较小的情况下。

位图索引是保存空值的，因此可以在COUNT中利用。

众所周知，位图索引不太适合OLTP类型数据库。该实例仅为了测试展示

案例启示

优化没有止境，对数据库了解越多，你能想到的方法就越多。


--本文节选自《SQL 优化最佳实践》第一章。

从简单Sql探索优化之道

https://mp.weixin.qq.com/s?__biz=MzI4NTA1MDEwNg==&mid=402867685&idx=1&sn=998efd041af597d3348cc8e74d921a02&mpshare=1&scene=23&srcid=0704kiAC3QEjNgIM454HQ2L9#rd

本文需要优化的语句是select count(*) from t，这简单的统计语句一出，估计不少人纳闷了，能有啥优化空间，还优化之道，什么gui。

哦，其实简单的背后不简单，来，跟作者一起看看如何“不择手段”，让count(*) 飞起来。不过我们用意的关键是让读者去思考，为什么能飞。

为什么能飞？嗯，因为我们掌握了Sql优化之道。那优化之道是什么？不着急，来，随我们来看看Sql飞的过程吧。

一 、优化过程

1. 普通思路

用了啥手段：啥没用！

性能啥情况：逻辑读为1048。

2. 增加索引

用了啥手段：建了一个btree索引。

性能啥情况：逻辑读从1048减少到372。

3. 位图索引

用了啥手段：建了一个位图索引。

性能啥情况：逻辑读从372瞬间缩减为6。

简单地说说位图索引的结构，比如T表有4个字段，分别是ID、NAME、SEX和STATUS，其中SEX取值仅为男或女，有时由于不知道性别，暂时为空，具体如下：

4. 物化视图

用了啥手段：应用了物化视图。

性能啥情况：逻辑读从6缩减为3。

说明：这主要是应用在数据库更新不是非常频繁场景，用的是空间换时间。

5. 缓存结果集

用了啥手段： 利用缓存结果集技术。

性能啥情况：逻辑读从3缩减为0。

在11g中，Oracle提供了结果集缓存特性。该缓存是在共享内存中存储全部的结果集，如果一个查询SQL被执行，且它对应的结果集在缓存中，那么，该SQL的几乎全部开销都可以避免。

6. 业务理解迎来速度之王

用了啥手段： 做了一件很奇怪的事，似乎把Sql改写的看不懂了。


select count(*) from t where rownum=1;


 

和下面的逻辑是等价的。


 

性能啥情况：表不管多大，永远只访问第1条，速度问题还需要纠结吗？ 

二 、优化总结

三、总结

这本是一个简单的语句，却可以神奇的完成一次又一次优化，性能不断提升，优化过程涵盖了Sql执行计划和索引的理解、根据不同场景选择不同技术、根据业务进行等价改写这三大技巧，可谓非常的经典。简单的背后不简单，充满了人生的智慧，还请多多体会。

哦，还没说，Sql优化之道是什么？嗯，请看下面：

  • 优化知识本身+根据场景选择技术+把握业务需求！

 

作者介绍：梁敬彬

  • 【DBA+社群】数据库专家。
  • 福富研究院副理事长；福富软件特级专家。
  • 公司四星级内训师、ITPUB版主及ITPUB社区专家，十余年数据库设计调优及培训相关经验，多次应邀参加中国数据库技术大会任演讲嘉宾。
  • 此外还著有多本畅销数据库技术书籍，其代表作《收获，不止Oracle》极为畅销，上市3个月内就完成第3次印刷。新书《从菜鸟到Sql优化大师》即将上市......

About Me


...............................................................................................................................

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599     微信群：私聊

● 联系我请加QQ好友(646634621)，注明添加缘由

● 于 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。
时间： 2024-10-26 21:04:08

COUNT(*)计算行数有哪些优化手段的相关文章

SQL按type计算行数的三种方法

我有一个叫做TestTable的表,它有一个字段,Type Varchar(5).表中的数据如下: Type A B A C A B 所以在这个表中这个单独的字段里有六条记录.请帮助我写一个查询获得下面的结果: A B C 3 2 1 换句话说我想获得单独的Type字段数据和它们的数量. Rudy Limeback: 有三种方式可以实现它: select ( select count(*) from TestTable where Type = 'A' ) as A , ( select cou

Shell或命令行计算数组或文件的行数和列数

原创作品,允许转载,转载时请务必以超链接形式标明文章 原始出处 .作者信息和本声明.否则将追究法律责任.http://dgd2010.blog.51cto.com/1539422/1630920 (一)闲聊Linux Shell 编程 都说中国文化博大精深(例如汉字),但作为操作系统中的佼佼者,Linux虽然时间并不长,但同样也是博大精深.谁也不敢说自己已经熟练的掌握了Linux中所有的内容,除了知识点众多以外,快速的发展和更新使得Linux越来越强大,也使得Linux在短时间内越来越难掌握.所

用js实现计算代码行数的简单方法附代码_javascript技巧

一段代码可能有几十行,上千个字符,统计其行数可以先把代码字符串化为数组,再返回该数组的长度即可. 传统做法: <textarea name="abc"> 123456 789 </textarea> <script type="text/javascript"> var counter = 0; var str = abc.value; for(i=0; i<str.length; i++) {     if(str.sub

【数据蒋堂】多维分析的后台性能优化手段

" 开篇辞 <数据蒋堂>的作者蒋步星,从事信息系统建设和数据处理长达20多年的时间.他丰富的工程经验与深厚的理论功底相互融合.创新思想与传统观念的相互碰撞,虚拟与现实的相互交织,产生出了一篇篇的沥血之作.此连载的内容涉及从数据呈现.采集到加工计算再到存储以及挖掘等各个方面.大可观数据世界之远景.小可看技术疑难之细节.针对数据领域一些技术难点,站在研发人员的角度从浅入深,进行全方位.360度无死角深度剖析:对于一些业内观点,站在技术人员角度阐述自己的思考和理解.蒋步星还会对大数据的发展

【数据蒋堂】第1期：多维分析的后台性能优化手段

多维分析就是针对一个事先准备好的数据立方体实施旋转.切片(切块).钻取等交互操作的过程,经常也被直接称为OLAP.它的后台运算在结构上很简单,如果用SQL语法描述,大体形式为: SELECT D,..., SUM(M), ... FROM C WHERE D'=d' AND ... GROUP BY D,... 即对立方体按某些维度分组汇总某些测度.其中C是数据立方体,D,...是选出维度,M,...是聚合测度,聚合函数也可以不是SUM.D'是切片维度,切块时条件为D IN (d,...),WH

distinct xx和count(distinct xx)的变态递归优化方法 - 收敛(skip scan)扫描

标签 PostgreSQL , 递归去重 , 递归优化 , count(distinct ), 稀疏列 , 统计 背景 今天要说的这个优化是从前面一篇讲解<performance tuning case :use cursor or trigger replace group by and order by> http://blog.163.com/digoal@126/blog/static/16387704020128142829610/ 的延展. CASE 例如一个表中有一个字段是性别,

SQL Server中关于基数估计如何计算预估行数的一些探讨

   关于SQL Server 2014中的基数估计,官方文档Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator里有大量细节介绍,但是全部是英文,估计也没有几个人仔细阅读.那么SQL Server 2014中基数估计的预估行数到底是怎么计算的呢? 有哪一些规律呢?我们下面通过一些例子来初略了解一下,下面测试案例仅供参考,如有不足或肤浅的地方,敬请指教!   下面实验测试的环境主要为SQL Serve

求助怎么用C# 取一个文件夹中所有的txt的行数并且写入dataGridView

问题描述 计算txt的行数和dataGridView单元格一一对应各个txt文件内容为(例):E00401504EB9FE7DE00401504EB9FE7DE00401504EB9FE7DE00401502BB0D3DFE00401502BB0D3DFtxt第一站第一时段第一站第二时段.........dataGridView站数/时段第1时段第2时段第3时段第4时段第1站第2站第3站本人纯属菜鸟需要各位大神源代码 解决方案 解决方案二:技术差,连描述都不清楚,txt例子和gv的描述看不懂是怎

Python实现代码行数自动统计实例

此功能用到了2个常用的Python标准库os和sys模块就解决. 可能是因为这段时间在做的一个Python项目,用的是一个公司内部的IDE环境,而这个IDE环境产生的py代码并不是以文本方式存储,都是放在二进制文件里面的. 由于这门语言外界几乎接触不到,所以没有针对它的代码统计程序.一个模块完成后要统计代码行数会很困难的,要统计的话只能手工来操作,这不符合我们程序员的一惯风格. 在这期间我一直在关注python语言,但是还没有动手真正的写python程序 .今天就利用中午休息的时间写了一个简单的