从Count看Oracle执行计划的选择 | idouba

从Count看Oracle执行计划的选择

一、 前言

在调查一个性能问题的时候,一个同事问道,为什么数据库有些时候这么不聪明,明明表上有索引,但是在执行一个简单的count的时候居然全表扫描了!难道不知道走索引更快么?

试图从最简单的count来重新了解oracle查询计划的选择,以及最终产生的结果。虽然有些结果会让人觉得有些意外,并且可能会鄙视,这个查询计划选择真的不够聪明。但稍微用心点的去了解,做的已经足够细致了。大多数情况下,根据我们输入的信息,来自输入的SQL、表结构、索引状况、统计信息,会得出一个比较优的计划。所以和前面一直试图讲到索引和join方式一样,所有这样的选择不是因为数据库厂商这样规定的,而是基于存储的数据的实际情况,就应该(甚至说不得不)这么去选择。

二、实验条件

表中有20001000条记录,有二十多个字段,包括几个长度比较大的字段。为了实验测试,只是讲IP字段设置为not null。在UpdateTime上建了默认的B树索引。

三、输入

分为三种场景来讨论,然后比较其结果,然后分析该结果。说明数据库的执行计划的选择其实是足够聪明的。

场景一:

  • 表结构:只是在有一个可以为null的列UpdateTime上建了一个索引
  • SQL:最简单的Count。

场景二:

  • 表结构:和场景一完全相同,只是在有一个可以为null的列UpdateTime上建了一个索引
  • SQL:多了个Where子句,在Where子句中涉及到建了索引的可以为null的列。

场景三:

  • 表结构:除了有一个可以为null的列UpdateTime上建了一个索引外,设置另外一列IpNum列为not null,并且在其上创建索引。
  • SQL:最简单的Count。

四、实验结果

场景/Count类型 Count(*) Count(PolicyId) 非索引可null的列 Count(IP)非索引not null 的列 Count(Updatetime)可以null的索引列 Count(IPNum) not null 的索引列
执行时间() 物理读 执行操作 执行时间() 物理读 执行操作 执行时间() 物理读 执行操作 执行时间() 物理读 执行操作 执行时间() 物理读 physical reads 执行操作
场景一 50.98 51671 表扫描 47.43 451672 表扫描 47.73 451672 表扫描 8.7 82393 UpdateTime上快速索引扫描
场景二 9.28 82395 UpdateTime上快速索引扫描 48.12 451673 表扫描 9.65 82428 UpdateTime上快速索引扫描 9.03 82394 UpdateTime上快速索引扫描
场景三 5.14 50224 IpNum上快速索引扫描 47.89 451672 表扫描 2.25 15153 IpNum上快速索引扫描 9.06 82393 UpdateTime上快速索引扫描 3.2 82393 IpNum上快速索引扫描

五、结果分析

其实观察查询计划,也就两种,一种快速索引扫描(INDEX FAST FULL SCAN),另外一种是全表扫描(TABLE ACCESS FULL|)。因为实验表中数据量还是不算小,有两千多万,两者的对比还是比较明显。主要体现在总的执行时间,和物理读取上面,差着一个量级。下面对表中的执行结果逐个进行总结和解释。

场景一:

当表中只在一个可以null的列上建索引的情况下。观察到count(*) 、count not null的列count(IP),count 可以null的列PolicyId的count(PolicyId)的效果一样都是走全表扫描(TABLE ACCESS FULL|);而对于count建了索引并且列可以null的列count(UpdateTime),走快速索引扫描(INDEX FAST FULL SCAN)。即只在索引列UpdateTime上, count会走快速索引扫描(INDEX FAST FULL SCAN),其他都是全表扫描。

解释:因为oracle不会索引null,因此扫描该列上的索引只能知道该列上非null的值,而count(*) 和count(非空列)都是统计总行数,从索引上不能获得该信息,只能全表扫描。而count(可空列)就更不能参照该索引了,建了索引的可空列上非null的行数,和在count的可空列上非null的行数没有任何关系,因此也只能全表扫描了。

场景二:

比场景一where子句中包含了可以null的列update的条件where updatetime > to_date(’2013-05-01 10:23:44′, ‘yyyy-mm-dd hh24:mi:ss’) 。观察到count(*)和count not null的列count(IP)和count UpdateTime一样都走updatetime上的快速索引扫描(INDEX FAST FULL SCAN);而count 可null列PolicyId的count(PolicyId)还是全表扫描。

解释:满足类似于pdatetime > to_date(’2013-05-01 10:23:44′, ‘yyyy-mm-dd hh24:mi:ss’)这样可空列上的条件,其实隐含的意思是满足该条件并且该列上取值不为null的行的行数。则在这列上的count,count(*),在其他非null列上的count,表达的都是这个意思,因此可以利用该索引来做索引扫描。而在另外一个可null的列上的count是表示满足该where条件同时在count列上值非null的记录行数,索引列上不为空的行可能在该列上为空,因此不能参照那个索引,只能全表扫描。

场景三:

当表中存在一个not null的列IpNum上建了索引。观察到count(*)和count(IP)在非空索引上快速索引扫描(INDEX FAST FULL SCAN),但是count(ProjectId)还是走原来的全表扫描。即:当表上在一个not null列上建了索引,则只有可Null的列的count走全表扫描,其他的都会走这个建了索引的no null 列的快速索引扫描(INDEX FAST FULL SCAN)。

解释:有一个not null的列上建了索引,则这个索引上的记录数就是表的行数,count(*),count(非空列)都是数行数。但是count(可null 列)是数这个列上不为空的记录数,因此不能参照索引,只能全表扫描了。

六、总结

在Oracle中,Count(Column)是计算Column上不为该列取值不为null的行数,Count一个not null的列其实就是总行数。而Count(*)是不区分null或者not null就是所有记录行数。因此二者语义是一样的,实验也证明了在各种情况下其执行计划总是一样的。对于这两种Count,表上无论哪个索引能提供这样的语义(在例子中场景二和场景三的两个不同索引分别提供了这样的语义),就会走这个索引。如果没有索引能提供这个语义,就不得不走全表扫描了。而Count一个可以为null的列,因为要数本列上到底有多少行的值不为null,因此不能参照别的列,必须在该列上数数,如果该列上有索引,则会在该列的索引上扫描,如果该列上没有索引,则不得不全表扫描。

七、附:各种场景的执行计划

1. 只是在有一个可以为null的列UpdateTime上建了一个索引的Count(*)

2. 只是在有一个可以为null的列UpdateTime上建了一个索引的Count一个可以null的列policyId

3. 只是在有一个可以为null的列UpdateTime上建了一个索引的Count一个可以null的索引列updatetime:Count(UpdateTime)

4. 只是在有一个可以为null的列UpdateTime上建了一个索引的Count一个not null的非索引列IP:Count(IP)

5. 只是在有一个可以为null的列UpdateTime上建了一个索引,Query的Where子句中包含该UpdateTime列的条件where updatetime > to_date(’2013-05-01 10:23:44′, ‘yyyy-mm-dd hh24:mi:ss’) ,Count(*)

6. 只是在有一个可以为null的列UpdateTime上建了一个索引,Query的Where子句中包含该UpdateTime列的条件where updatetime > to_date(’2013-05-01 10:23:44′, ‘yyyy-mm-dd hh24:mi:ss’) 。Count一个可以null的列PolicyId:Count(PolicyId)

7. 只是在有一个可以为null的列UpdateTime上建了一个索引,Query的Where子句中包含该UpdateTime列的条件where updatetime > to_date(’2013-05-01 10:23:44′, ‘yyyy-mm-dd hh24:mi:ss’) 。Count一个not null的列IP:Count(IP)

 

8. 只是在有一个可以为null的列UpdateTime上建了一个索引,Query的Where子句中包含该UpdateTime列的条件where updatetime > to_date(’2013-05-01 10:23:44′, ‘yyyy-mm-dd hh24:mi:ss’) 。Count该可以 null的索引列UpdateTime:Count(UpdateTime)

9. 在一个not null的列IPNum创建索引count(*)

 

10 在一个not null的列IPNum创建索引。Count可null的列PolicyId:Count(PolicyId)

11 在一个not null的列IPNum创建索引。Count not null的列IP:Count(IP)

12 在一个not null的列IPNum创建索引。Count另外一个建了索引的可null的列UpdateTime:Count(UpdateTime)

 

完。

原创文章。为了维护文章的版本一致、最新、可追溯,转载请注明: 转载自idouba

本文链接地址: 从Count看Oracle执行计划的选择


,

No comments yet.

发表评论