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

最新推荐文章于 2024-07-01 11:30:48 发布
最新推荐文章于 2024-07-01 11:30:48 发布
阅读量312 收藏 1
点赞数
文章标签： 数据库 面试

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、数据准备

640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1


2、全表扫描

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

640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1

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


3、主键索引

主键索引的代码如下：640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1


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


4、常数索引

常数索引的代码如下：

640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1


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


5、常数压缩索引

常数压缩索引的代码如下

640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1


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


6、位图索引

640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1


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


7、位图索引+并行

640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1


并行技术可以较快执行速度。一致性读有所增加，但并行还是能加快整体运行速度，这种手段耗时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. 普通思路


用了啥手段：啥没用！


640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1


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


2. 增加索引


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


640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1


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


3. 位图索引


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


640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1


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


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


640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1

640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1


4. 物化视图


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


640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1


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


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


5. 缓存结果集


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


640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1


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


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


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


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


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


640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1
 

和下面的逻辑是等价的。


640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1
 

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


二 、优化总结


640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=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群，学习最实用的数据库技术。

ico_mailme_02.png
 DBA笔试面试讲解
 欢迎与我联系

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-2141601/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26736162/viewspace-2141601/

coco3600
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle使用max提升效率,小小小的问题------>>关于count(*) 和 max(rownum) 效率问题
weixin_34434843的博客
04-03 801
该楼层疑似违规已被系统折叠隐藏此楼查看此楼SQL> create table test01.tt as select * from dba_objects;表已创建。已用时间: 00: 00: 03.14SQL> select count(1) from test01.tt;COUNT(1)----------71893已用时间: 00: 00: 00.06执行计划-------...
计算代码的行数
11-16
- **有效行数**：仅计算有意义的代码行，排除模板、框架、库等重复部分。 4. **常用的代码行数统计工具**： - **cloc**：跨平台的开源工具，支持多种编程语言，可以区分代码、注释和空行。 - **SLOCCount**：另...
改写优化SQL（7）：尽量不用count(*)来判断是否有数据
SQL Server探索
03-08 2641
一、首先说一下，判断表里是否有记录，常见的写法（伪代码）：定义变量a a = (select count(*) from tb) if a &gt;0 ... else ...这么写是没错，看上去也好理解，就是统计一下tb表中的记录数，然后判断这个记录数是否大于0。可能绝大多数人在实现这个需求的时候，都会这么写。二、接下来，想想这个语句在执行时，会做什么处理：1、如果tb表没索...
MySQL实战45讲—— 14count(*)这么慢， 我该怎么办？
meser88的博客
10-27 4242
文章摘抄自林晓斌老师《MySQL实战45讲》。在开发系统的时候，你可能经常需要计算一个表的行数，比如一个交易系统的所有变更记录总数。这时候你可能会想，一条select count(*) from t 语句不就解决了吗？ 但是，你会发现随着系统中记录数越来越多，这条语句执行得也会越来越慢。然后你可能就想了，MySQL怎么这么笨啊，记个总数，每次要查的时候直接读出来，不就好了吗。 那么今天，我们就来聊聊count(*)语句到底是怎样实现的，以及MySQL为什么会这么实现。然后，我会再和你说说，如果应用中有这
PG案例系列1:优化大表的count(*)
只是甲的博客
05-30 2192
为了缓解这个问题，PostgreSQL引入了可见性映射（visibility map），这是一种数据结构，用于存储每个人是否都可以看到表块中的所有元组。对于count(*) 操作，完全可以借助索引，因为索引占的空间比表小太多，那么几十M的索引扫描起来为什么那么慢呢?遇到的问题是，第一次执行会很慢，6秒左右，如果间隔时间段第二次执行，因为缓存了第一次的数据，所以查询很快。开启4个并行，并调整参数后，我进行了5次测试，以示公平，每次测试后，休眠5分钟。前端页面需要分页展示，经常需要查询总数，然后做分页展示。
count(*) 怎么优化？
leeta的博客
08-04 2596
前言 哈喽，我是狗哥。小伙伴都知道我最近换工作了，薪资、工作内容什么的都是我比较满意的。五月底也面试了有 6、7 家公司，应该拿了有 5 个 offer。这段时间也被问了很多面试题，我打算写一个专题分享出来，希望对你们有所帮助～ 这期面试官提的问题是： count (1) 和 count (*) 有啥区别？你更推荐用哪个？数据量很大的情况下怎么优化？ 国际惯例先上思维导图： 1.1 往期精彩 MySQL 查询语句是怎么执行的？ MySQL 索引 MySQL 日志 MySQL .
MySQL中聚合函数count的使用和性能优化技巧
12-16
它不关心列的具体值，只是计算行数。 - 注意，COUNT()不能同时作用于多列，如`COUNT(id, name, country, province, city)`是错误的。 2. COUNT(DISTINCT ...): 此用法用于统计不同且非NULL的值的数量。例如，`...
代码行数统计工具
11-16
总的来说，代码行数统计工具是软件工程中不可或缺的辅助工具，它们提供了量化项目规模和复杂性的手段，帮助团队更好地管理和优化代码。CLOC作为其中的一员，以其易用性和强大的功能，深受开发者和项目经理的喜爱。
代码行统计工具(CountLines)
12-06
总的来说，“代码行统计工具(CountLines)”是一款便捷实用的工具，它为Java开发者提供了一种量化分析代码的手段，有助于提升开发效率和代码质量。正确理解和使用这类工具，对于现代软件工程的实践者来说，无疑是提升...
VC++linecount3.6.2.rar
09-26
总的来说，"VC++ Line Count 3.6.2"作为一款实用的行数统计工具，能够为开发者提供方便快捷的代码分析手段，帮助他们更好地管理和优化项目。无论是在软件开发的初期阶段，还是在项目维护的过程中，它都能发挥其独特...
mysql千万级别的数据使用count(*)查询比较慢怎么解决？
Harry的博客
08-04 7671
当MySQL中的数据量达到千万级别时，使用COUNT()查询可能会变得比较慢。一些优化COUNT(*)查询的方法：使用索引、使用近似值、使用缓存、分批查询、数据分片等技术来解决问题
【mysql学习篇】count()优化分析
一个勤勤恳恳的程序猿
02-28 2万+
而是专门做了优化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)来替代 count(*)。还可以走主键索引，所以count(主键 id)>count(字段)以上4条sql只有根据某个字段count时不会去统计字段为。四个sql的执行计划一样，说明这四个sql执行效率应该差不多。count(*) 是例外，mysql 并不会把全部字段取出来。，所以理论上count(1)比count(字段)会快一点。count(1)跟count(字段)执行过程类似，，就用常量1做统计，
MySQL COUNT函数的使用与优化
最新发布
My_wife_QBL的博客
07-01 1079
COUNT函数用于统计表中满足条件的记录数量。其中，expression可以是列名、常量、表达式或星号（
面试官：count(*) 怎么优化？
weixin_43398645的博客
07-18 207
count (主键) 可能会选择最小的索引来遍历，而 count (字段) 的话，如果字段上没有索引，就只能选主键索引，所以性能上 count (字段) < count (主键)上面出现数据丢失或计算不精确的原因在于：MySQL 和 Redis 的事务不是同一体系的，我们并不能保证两者事务的原子性，而把 Redis 也换成 MySQL 这就迎刃而解了。看到这里，有没有清晰一点？count 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。
count查询优化
shareing
08-18 4158
count查询优化 这里的所说的count查询优化是针对MyISAM引擎进行的。 例子1：假设有t_ve_school表，共有61868行数据 INNODB 引擎 explain select count(*) from t_ve_school where id&amp;amp;amp;amp;gt;5; explain select count(*) from t_ve_school ; expla...
Mysql性能优化：为什么count(*)这么慢？
码猿技术专栏
04-02 3万+
导读在开发中一定会用到统计一张表的行数，比如一个交易系统，老板会让你每天生成一个报表，这些统计信息少不了sql中的count函数。但是随着记录越来越多，查询的速度会越来越慢，为什么会这样...
为什么 mysql 的 count() 方法这么慢？
EDDYCJY的博客
07-08 3728
兄弟们。浅浅的炫个富吧。说出来你们可能不信。手机你们有吗？我有。短信，知道吧？一条一毛钱，我天天发。你敢想吗？所以说，年轻人，有钱是真的好。今天，我们就以短信为话题聊起。短信，它又叫SMS。比如说，你有一张短信表(sms)，里面放了各种需要发送的短信信息。sms建表sqlsms表需要注意的是state字段，为0的时候说明这时候短信还未发送。此时还会有一个异步线程不断的捞起...
mysql查询所有表的记录数_获取MySQL数据库中所有表的记录计数
weixin_29001419的博客
01-19 1245
就像@Venkatramanan和其他人一样，我发现INFORMATION_SCHEMA.TABLES不可靠(使用InnoDB，MySQL 5.1.44)，每次我在静默表上运行时都会给出不同的行数。这是一种相对hacky(但灵活/适应性)的生成大型SQL语句的方法，您可以将其粘贴到新查询中，而无需安装Ruby gems和东西。SELECT CONCAT('SELECT "',table_name,...
COUNT()函数详解
热门推荐
Java修炼记
11-24 13万+
COUNT 顾名思义，就是计数的意思，此函数用处就是对表中记录数进行计数。 MySQL COUNT()函数介绍 COUNT()函数返回表中的行数。COUNT()函数允许您对表中符合特定条件的所有行进行计数。 COUNT()函数的语法如下 - COUNT(expression) SQL COUNT()函数的返回类型为BIGINT。 如果没有找到匹配的行，则COUNT()函数返
InnoDB引擎下count(*)性能优化：原因与解决方案
对于这种情况，如果你的业务需求确实需要频繁地统计表行数，并且数据量非常大，有以下几种优化策略： 1. **预计算缓存**：在业务空闲时期，定期进行全表扫描并更新缓存中的行数。这样可以减少查询时的实时计算，但...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值