优化select count(*) from t1

最新推荐文章于 2023-02-13 19:30:00 发布
最新推荐文章于 2023-02-13 19:30:00 发布
阅读量333 收藏
点赞数

分享下如何优化一条简单的SQL语句，数据库优化无止境，学习数据库优化，我们先从一条最简单的SQL语句开始。
select count(*) from t1;
这条语句虽然比较简单，但很有玄机！对这句话运行的理解，反映了你对数据库的理解深度！
好！我们开始我们的实验。
建立实验的使用的大表他t1

SQL> conn scott/tiger
已连接。
SQL> drop table t1 purge;

表已删除。

SQL> create table t1 as select * from emp where 0=9;

表已创建。

SQL> insert into t1 select * from emp;

已创建14行。

SQL>  insert into t1 select * from t1;

已创建14行。

SQL> /

已创建28行。

SQL> /

已创建56行。

SQL> /

已创建112行。

SQL> /

已创建224行。

SQL> /

已创建448行。

SQL> /

已创建896行。

SQL> /

已创建1792行。

SQL> /

已创建3584行。

SQL> /

已创建7168行。

SQL> /

已创建14336行。

SQL> /

已创建28672行。

SQL> /

已创建57344行。

SQL> commit;

提交完成。

收集统计信息
SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');

PL/SQL 过程已成功完成。

SQL> SET AUTOT TRACE EXP
SQL> SELECT COUNT(*) FROM T1;

执行计划
--------------------------------------------------                               
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |                                
-------------------------------------------------------------------                                
|   0 | SELECT STATEMENT   |      |     1 |   124 (4)| 00:00:02 |                                
|   1 |  SORT AGGREGATE    |      |     1 |          |          |                                
|   2 |   TABLE ACCESS FULL| T1   |   116K|   124 (4)| 00:00:02 |                                
-----------------------------------------------------
代价为124，运行的计划为全表扫描。 
那么我们删除表中所有的数据，是不是代价就能少了那？按照我们的正常思维是这样的，但是oracle数据库
没有这么做，没有那么智能，毕竟它只是一个程序。
我们删除T1的所有数据，我们来看看。            
SQL> DELETE T1 WHERE DEPTNO=10;

已删除24576行。

SQL> COMMIT;

提交完成。

SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*) FROM T1;

执行计划
-----------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |                                
-------------------------------------------------------------------                                
|   0 | SELECT STATEMENT   |      |     1 |   123 (3)| 00:00:02 |                                
|   1 |  SORT AGGREGATE    |      |     1 |          |          |                                
|   2 |   TABLE ACCESS FULL| T1   | 90286 |  123  (3)| 00:00:02 |                                
-----------------------------------------------------
我们看到了，代价为123几乎没有变化。
第一种优化的手段：
--1.降低高水位
SQL> alter table t1 move tablespace users;

表已更改。

SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*) FROM T1;

执行计划
-----------------------------------------------------
| Id  | Operation          | Name | Rows  |Cost (%CPU)| Time   |                                
-------------------------------------------------------------------                                
|   0 | SELECT STATEMENT   |      |     1 |   102 (3)| 00:00:02 |                                
|   1 |  SORT AGGREGATE    |      |     1 |          |          |                                
|   2 |   TABLE ACCESS FULL| T1   | 90667 |   102 (3)| 00:00:02 |                                
-----------------------------------------------------                         
代价为102，降低了，但是不多。

第二个手段是：
2.修改pctfree
SQL> alter table t1 pctfree 0;

表已更改。

SQL> alter table t1 move tablespace users;

表已更改。

SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*) FROM T1;

执行计划
----------------------------------------------------------                                         
Plan hash value: 3724264953                                                                        
                                                                                                   
-------------------------------------------------------------------                                
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |                                
-------------------------------------------------------------------                                
|   0 | SELECT STATEMENT   |      |     1 |    92   (4)| 00:00:02 |                                
|   1 |  SORT AGGREGATE    |      |     1 |            |          |                                
|   2 |   TABLE ACCESS FULL| T1   | 91791 |    92   (4)| 00:00:02 |                                
-------------------------------------------------------------------                                
代价为92，降低了10%
第三种手段是：

--3.参数db_file_multiblock_read_count=64
该参数是一次性读的数据库块数。一次性读的块数越多。数据库的性能就越高。
第四种手段是：
--4.建立b*tree类型的索引
SQL> create index i1 on t1(empno);

索引已创建。

SQL> execute dbms_stats.gather_index_stats('SCOTT','I1');

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*) FROM T1;

执行计划
----------------------------------------------------------                                         
Plan hash value: 3724264953                                                                        
                                                                                                   
-------------------------------------------------------------------                                
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |                                
-------------------------------------------------------------------                                
|   0 | SELECT STATEMENT   |      |     1 |    92   (4)| 00:00:02 |                                
|   1 |  SORT AGGREGATE    |      |     1 |            |          |                                
|   2 |   TABLE ACCESS FULL| T1   | 91791 |    92   (4)| 00:00:02 |                                
-------------------------------------------------------------------                                
为什么没有使用我们建立的索引，因为null不进入普通的索引！

SQL> alter table t1 modify(empno not null);

表已更改。

SQL> SELECT COUNT(*) FROM T1;

执行计划
----------------------------------------------------------                                         
Plan hash value: 129980005                                                                         
                                                                                                   
----------------------------------------------------------------------                             
| Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |                             
----------------------------------------------------------------------                             
|   0 | SELECT STATEMENT      |      |     1 |    36   (6)| 00:00:01 |                             
|   1 |  SORT AGGREGATE       |      |     1 |            |          |                             
|   2 |   INDEX FAST FULL SCAN| I1   | 91791 |    36   (6)| 00:00:01 |                             
----------------------------------------------------------------------                             
代价为36，我们的索引起到了很大的作用！

第五种手段是：

SQL> --5.使用并行查询的特性
                               
强制全表扫描，屏蔽索引

SQL> select /*+ full(t1) parallel(t1 2) */ COUNT(*) FROM T1;

执行计划
----------------------------------------------------------------------------------------
| Id  | Operation    | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |     
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |    |     1 |    51   (4)| 00:00:01 |    |      |    | 
|   1 |  SORT AGGREGATE        |          |     1 |    |    |        |      |    |   
|   2 |   PX COORDINATOR       |   |       |            |          |        |    |    |           
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |    |   |  Q1,00 | P->S | QC(RAND)  |        
|   4 |     SORT AGGREGATE     |          |     1 |    |  |  Q1,00 | PCWP |  |                  
|   5 |      PX BLOCK ITERATOR |          | 91791 |    51   (4)| 00:00:01 |  Q1,00 | PCWC|  |   
|   6 |       TABLE ACCESS FULL| T1       | 91791 |    51   (4)| 00:00:01 |  Q1,00 | PCWP |  |    
-----------------------------------------------------------------------------------------------
并行度越高，代价越低

SQL> alter table t1 parallel 4;

表已更改。
也可以通过使用表的属性来定义并行度，但是影响比较大，不如语句级别限制并行！

SQL> select count(*) from t1;

执行计划
-----------------------------------------------------------------------------------------
| Id  | Operation      | Name     | Rows  | Cost (%CPU)| Time   |    TQ  |IN-OUT| PQDistrib |    
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    25   (0)| 00:00:01 |    |    |    |        
|   1 |  SORT AGGREGATE        |       |     1 |      |          |        |      |     |        
|   2 |   PX COORDINATOR       |          |       |        |      |        |      |    |        
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |       |   |  Q1,00 | P->S | QC(RAND)  |     
|   4 |     SORT AGGREGATE     |          |     1 |      |      |  Q1,00 | PCWP |    |         
|   5 |      PX BLOCK ITERATOR |          | 91791 |    25   (0)| 00:00:01 |  Q1,00 | PCWC |   |  
|   6 |       TABLE ACCESS FULL| T1       | 91791 |    25   (0)| 00:00:01 |  Q1,00 | PCWP |   | 
---------------------------------------------------------------------------------------------
代价为25，代价比两个的又少一半！
第六种手段是：

SQL> --6.建立位图索引来避免全表扫描
SQL> create bitmap index i2 on t1(deptno);

索引已创建。

SQL> execute dbms_stats.gather_index_stats('SCOTT','I2');

PL/SQL 过程已成功完成。

SQL> select count(*) from t1;

执行计划
----------------------------------------------------------                                         
Plan hash value: 3738977131                                                                        
                                                                                                   
------------------------------------------------------------------------------                     
| Id  | Operation                     | Name | Rows  | Cost (%CPU)| Time     |                     
------------------------------------------------------------------------------                     
|   0 | SELECT STATEMENT              |      |     1 |     4   (0)| 00:00:01 |                     
|   1 |  SORT AGGREGATE               |      |     1 |            |          |                     
|   2 |   BITMAP CONVERSION COUNT     |      | 91791 |     4   (0)| 00:00:01 |                     
|   3 |    BITMAP INDEX FAST FULL SCAN| I2   |       |            |          |                     
------------------------------------------------------------------------------                     

SQL> alter index i2 parallel 4;

索引已更改。

SQL> select count(*) from t1;
执行计划
----------------------------------------------------------------------------------------
| Id  | Operation       | Name   | Rows  | Cost (%CPU)| Time   |   TQ  |IN-OUT| PQ Distrib |    
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |     2   (0)| 00:00:01 |      | |       |          
|   1 |  SORT AGGREGATE   |   |     1 |            |          |        |  |      |               
|   2 |   PX COORDINATOR   |      |       |       |          |        |  |    |               
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |    |          |  Q1,00 | P->S | QC (RAND) |   
|   4 |     SORT AGGREGATE  |    |     1 |   |      |  Q1,00 | PCWP |        |        
|   5 |      PX BLOCK ITERATOR |  | 91791 |     2   (0)| 00:00:01 |  Q1,00 |PCWC |     |        
|   6 |       BITMAP CONVERSION COUNT  |   | 91791 |  2   (0)| 00:00:01 |  Q1,00 |PCWP |    |  
|   7 |        BITMAP INDEX FAST FULL SCAN| I2    |   |     |     |  Q1,00 | PCWP |    |        
--------------------------------------------------------------------------------------------
代价为2，原来为124，优化无止境呀！
只有你把握原理，一切尽在掌握！

要去开早会了，今天就到这里吧，希望大家能学到一点有用的知识。在工作中能用到。


pxboracle@live.com
2014.08.12 08:24
share you knowledge with the world. 


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

转载于:http://blog.itpub.net/12798004/viewspace-1249555/

cuchou5321
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
SQL 查找是否“存在“，别再 count 了，很耗费时间的！
程序员白楠楠的博客
07-11 783
目前多数人的写法 优化方案 总结 根据某一条件从数据库表中查询 『有』与『没有』，只有两种状态，那为什么在写SQL的时候，还要SELECT count(*) 呢？ 无论是刚入道的程序员新星，还是精湛沙场多年的程序员老白，都是一如既往的count 目前多数人的写法 多次REVIEW代码时，发现如现现象： 业务代码中，需要根据一个或多个条件，查询是否存在记录，不关心有多少条记录。普遍的SQL及代码写法如下 #### SQL写法: SELECT count(*) FROM table WHERE a = 1 .
select count的优化
dianchantuo7921的博客
08-13 398
select count的优化 2011-08-02 12:01:36 分类： Oracle 一般情况下，select count语句很难避免走全表扫描，对于上百万行的表这个语句使用起来就比较吃力了，今天上午群里一帮oracle工程师讨论了很久，这是个很好的研究课题，我在网上找到一篇文章，贴在这应该也可以给大家起到一定的启发作用。 以下为转帖了： selec...
笔试强训(十八)
m0_60631323的博客
10-07 929
笔试强训(十八)
mysql 5.7中关于count(*)的优化
jackyrongvip的专栏
09-20 442
在mysql 5.7中，对于select count(*) from table 的优化，可以 设置为： select count(*) from table where uid>0 其中uid为辅助索引，辅助索引不存放数据，而是有一个指针指向对应的数据块，因此速度更快； 但注意到http://www.ywnds.com/?p=10369中提到的问题，要引起注意...
mysql中count(1)会报错么,在同一select子句中使用count（*）和*会产生错误。为什么？...
weixin_42467508的博客
02-08 520
mysql> select COUNT(*), * from help_keyword;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near...
Oracle随机抽样sample使用说明
imlpf的专栏
10-12 7362
下面是SAMPLE语法的使用示例： 　　选择10%的记录 　　select * from t1 sample(10) 　　选择0.1%的记录 　　select * from t1 sample(0.1) 　　根据数据块选择1%的记录 　　select * from t1 sample block(1) 　　使用数据块选择与使用记录行选择的区别：使用数据块选
将count(*)值写入另一个表中的方法
09-14
这里我们将使用`count(*)`函数计算`updata`表中`id`为'A0101'的记录数，并将结果存入`t1`表的`Qty`字段中。 ```sql INSERT INTO t1 (item, prov, qty) SELECT '原名', 'xx省', (SELECT COUNT(*) FROM updata ...
MySQL优化案例系列-mysql分页优化
09-10
SELECT COUNT(*) FROM t1; +----------+ | count(*) | +----------+ | 994584 | +----------+ ``` 从执行计划中可以发现，当`start`值较大时，需要扫描的记录数非常多，例如： ```sql EXPLAIN SELECT * FROM t1 ...
mysql进阶优化
08-12
SELECT SUM(T1.C1) FROM T1 WHERE (SELECT COUNT(*) FROM T2 WHERE T2.C2 = T1.C2) > 0; ``` 2. **优化后：** ```sql SELECT SUM(T1.C1) FROM T1 WHERE EXISTS (SELECT * FROM T2 WHERE T2.C2 = T1.C2); ``` *...
总结SQL执行进展优化方法
09-10
当执行`SELECT COUNT(1) FROM t1 GROUP BY c1`时，SQL Server可能会使用哈希匹配来处理数据。哈希匹配是处理连接的一种方式，特别是当一个表的数据量远小于另一个表时，效率较高。但要注意，哈希匹配可能导致性能...
【数据库】sql函数和多表关联查询
weixin_48814356的博客
02-13 3868
聚合函数（count函数；AVG函数；SUM函数； MAX函数； MIN函数）；数值型函数；字符串函数；日期和时间函数；流程控制函数；内连接；左连接；右连接；自连接；交叉连接
牛客 数据库
test_wyf_go的博客
06-23 1万+
1. （1）基本概念    ①　属性和域：     每个事物有很多属性，每个属性对应的取值范围叫做域，所有对域都是原子数据（第一范式）    ②　相关名词     n元关系：R(D1,D2,D3...Dn)是n元关系，其中关系属性的个数称为“元数”，元组的个数称为“基 数”，也就是记录值。     候选码：若关系中某一个属性或者属性组的值可以唯一的标识一个元组，
GBASE 8C——SQL参考6 sql语法（12）
Joe72的博客
07-29 418
GBASE 8C——SQL参考6 sql语法（12）
select count(*) from返回的类型_从零开始学 MySQL - SELECT 语句详解
weixin_39654619的博客
11-26 3485
1、内容在数据库操作语句中，使用最频繁，也被认为最重要的是 SELECT 查询语句。在之前的学习中，我们已经在不少地方用到了SELECT * FROM table_name;这条语句用于查看一张表中的所有内容。 而 SELECT 与各种限制条件关键词搭配使用，具有各种丰富的功能，这次实验就进行详细介绍。2、知识点SELECT 基本语法数学符号条件AND OR IN通配符排序SQL 内置函数和计算子...
select count(*)、count(1)、count(主键列)和count(包含空值的列)有何区别？
congdingfu1756的博客
02-13 856
下班路上看见网上有人问一个问题： oracle 10g以后count(*)和count(非空列)性能方面有什么区别？ 乍一看，确实有些含糊，Oracle...
select * from 表名 和select count (*) from 表名的区别
热门推荐
不想起床
08-09 1万+
select * from page 查询page里所有的信息，包括字段名，数据 select count (*) from page 查询表page的行数
leftjoin中打印sql为什么会输出SELECT COUNT(*) AS think_count FROM
最新发布
07-15
在执行第一个查询时，会自动生成一个`SELECT COUNT(*) AS think_count FROM`的SQL语句，用于统计符合条件的记录总数。这个SQL语句的目的是为了方便进行分页等操作。 然后，在执行第二个查询时，会生成实际的数据...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值