SQL优化——成本计算

文章目录

1、优化SQL需要看COST吗
2、全表扫描成本计算
3、索引范围扫描成本计算
4、SQL优化核心思想

1、优化SQL需要看COST吗

很多人在做SQL优化的时候都会去看Cost。很多人经常问：为什么Cost很小，但是SQL就是跑很久不出结果呢？在这里告诉大家，做SQL优化的时候根本不需要去看Cost，因为Cost是根据统计信息、根据一些数学公式计算出来的。正是因为Cost是基于统计信息、基于数学公式计算出来的，那么一旦统计信息有误差，数学公式有缺陷，Cost就算错了。而一旦Cost计算错误，执行计划也就错了。当SQL需要优化的时候，Cost往往是错误的，既然是错误的Cost，我们干什么还要去看Cost呢？

带领大家手动计算全表扫描以及索引扫描成本，同时由此引出SQL优化核心思想。

2、全表扫描成本计算

全表扫描成本的计算方式如下：

Cost = (
       #SRds * sreadtim +
       #MRds * mreadtim +
       CPUCycles / cpuspeed
       ) / sreadtime

#SRds - number of single block reads 表示单块读次数
#MRds - number of multi block reads  表示多块读次数
#CPUCyles - number of CPU cycles     CPU时钟周期数
sreadtim - single block read time    一次单块读耗时，单位毫秒
mreadtim - multi block read time     一次多块读耗时，单位毫秒
cpuspeed - CPU cycles per second     每秒CPU时钟周期数

注意：如果没有收集过系统统计信息（系统的CPU速度，磁盘I/O速度等），那么Oracle采用非工作量方式来计算成本。如果收集了系统统计信息，那么Oracle采用工作量统计方式来计算成本。一般我们是不会收集系统的统计信息的。所以默认情况下都是采用非工作量（noworkload）方式来计算成本。

全表扫描成本计算公式究竟是什么含义呢？我们再来看一下全表扫描成本计算公式：

Cost = (
       #SRds * sreadtim +
       #MRds * mreadtim +
       CPUCycles / cpuspeed
       ) / sreadtime

因为全表扫描没有单块读，所以#SRds=0，CPU耗费的成本基本上可以忽略不计，所以我们将全表扫描公式变换如下：

Cost = (
       #MRds * mreadtim 
       ) / sreadtime

#MRds表示多块读I/O次数，那么现在我们得到一个结论：全表扫描成本公式的本质含义就是多块读的物理I/O次数乘以多块读耗时与单块读耗时的比值。

全表扫描成本计算公式是在Oracle9i（2000年左右）开始引入的，当时的I/O设备性能远远落后于现在的I/O设备（磁盘阵列），随着SSD的出现，寻道寻址时间已经可以忽略不计，磁盘阵列的性能已经有较大提升，因此认为在现代的I/O设备（磁盘阵列）中，单块读与多块读耗时几乎可以认为是一样的，全表扫描成本计算公式本质含义就是多块读物理I/O次数。

3、索引范围扫描成本计算

索引范围扫描的成本计算公式：

cost =  
 blevel +  
 celiling(leaf_blocks *effective index selectivity) +  
 celiling(clustering_factor * effective table selectivity)

索引扫描成本计算公式中，blevel、leaf_blocks、clustering_factor都可以通过下面查询得到：

SQL> select leaf_blocks, blevel, clustering_factor
  2    from dba_indexes
  3   where owner = 'SCOTT'
  4     and index_name = 'IDX_COST';

LEAF_BLOCKS     BLEVEL CLUSTERING_FACTOR
----------- ---------- -----------------
        161          1              1113

blevel表示索引的二元高度，blevel等于索引高度−1，leaf_blocks表示索引的叶子块个数，clustering_factor表示索引的集群因子，effective index selectivity表示索引有效选择性，effective table selectivity表示表的有效选择性。

在Oracle数据库中，Btree索引是树形结构，索引范围扫描需要从根扫描到分支，再扫描到叶子。叶子与叶子之间是双向指向的。blevel等于索引高度−1，正好是索引根块到分支块的距离。leaf_blocks *effective index selectivity表示可能需要扫描多少叶子块。clustering_factor *effective table selectivity表示回表可能需要耗费多少I/O。

索引范围扫描是单块读，回表也是单块读，因此，我们得到如下结论：索引扫描成本计算公式其本质就是单块读物理I/O次数。

为什么全表扫描成本计算公式要除以单块读耗时呢？上文提到，全表扫描COST=多块读物理I/O次数*多块读耗时/单块读耗时，索引范围扫描COST=单块读物理I/O次数。现在我们对全表扫描COST以及索引范围扫描COST都乘以单块读耗时：

全表扫描COST单块读耗时=多块读物理I/O次数多块读耗时=全表扫描总耗时
索引范围扫描COST单块读耗时=单块读物理I/O次数单块读耗时=索引扫描总耗时

到此，大家应该明白优化器何时选择全表扫描，何时选择索引扫描，就是比较走全表扫描的总耗时与走索引扫描的总耗时，哪个快就选哪个。

4、SQL优化核心思想

现在的IT系统中，CPU的发展日新月异，内存技术的更新也越来越频繁，只有磁盘技术发展最为迟缓，磁盘（I/O）已经成为整个IT系统的瓶颈。全表扫描的成本其本质含义就是多块读的物理I/O次数。索引范围扫描的成本其本质含义就是单块读的物理I/O次数。我们在判断究竟应该走全表扫描还是索引扫描的时候，往往会根据两种不同的扫描方式所耗费的物理I/O次数来做出选择，哪种扫描方式耗费的物理I/O次数少，就选择哪种扫描方式。在进行SQL优化的时候，我们也是根据哪种执行计划所耗费的物理I/O次数最少而选择哪种执行计划。

基于上述理论，我们给出核心观点：SQL优化的核心思想就是想方设法减少SQL的物理I/O次数（不管是单块读次数还是多块读次数）。

秒客网