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优化之道是什么？嗯，请看下面：

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


©著作权归作者所有：来自51CTO博客作者小麦苗DB宝的原创作品，如需转载，请注明出处，否则将追究法律责任

更多相关文章

  1. 告别躺赢，我靠索引+函数
  2. 神奇的MySQL排序，我把面试者都问蒙了
  3. 【MOS：1549181.1】为何在查询中索引未被使用--为什么索引没有被使
  4. Oracle的簇与簇表
  5. 痞子衡嵌入式：高性能MCU之音视频应用开发那些事 - 索引
  6. 因为我的一个低级错误，生产数据库崩溃了将近半个小时
  7. 悟透前端：javascript数组之includes、reduce
  8. 痞子衡嵌入式：语音处理工具pzh-speech诞生记 - 索引
  9. Python列表操作最全面总结

随机推荐

  1. android前台渲染图片
  2. Android应用程序中的多个Activity的显示
  3. Android：控件AutoCompleteTextView 客户端
  4. 下载android sdk更新包离线安装解决方案
  5. Android实现按钮点击效果（第一次点击变色，
  6. Android网络请求库——android-async-htt
  7. Android 特殊用法
  8. 【翻译】(25)ANDROID ATOMICS OPERATIONS
  9. 几个android实用网站
  10. Android ListView滑动加载