评估CPU时间(CQUBE)

可以简单的借鉴QUBE的方法来评估SQL执行的CPU时间(CQUBE)，用RS代表排序的记录数 :
CPU时间 = 100μs x TR + 5μs x TS + 100μs x F + 10μs x RS

单次顺序访问的CPU时间

按照QUBE方法中的定义，一次顺序访问是指在顺序扫描过程中读取一条记录，然后使用非匹配谓词进行过滤(匹配谓词已被用于定义锁扫描片段的厚度)。当然，如果是全表或者全所有扫描，那么所有的谓词都是非匹配谓词。

单次顺序访问的CPU时间主要跟单条记录的长度有关，因为CPU时间主要耗费在数据页的处理上。另外，非匹配谓词的个数和复杂度是另一个主要因素，还有锁机制和压缩等其他因素。

在具体的平台上，评估单次顺序访问的CPU时间相对简单，接下来我们就展示三个具体测量的例子。

在第一个例子中有两个WHERE条件恒为非真的单表SELECT语句。两个语句的WHERE条件中都只有一个谓词，且谓词为非索引列。这样，优化器不得不为这两个SELECT语句选择使用全表扫描的方式。其中的CUST表有111000个4KB大小的数据页，共1000000条长度为400字节的记录。另外的INVOICE表有77000个数据页，共4000000条长度为80字节的记录。

表没有进行压缩，所有列都是固定长度，两个WHERE条件也足够简单。锁的粒度是数据页级别的，隔离级别是CS。一些额外的谓词，特别是复杂的谓词会增加CPU时间，另外，数据记录长度的增加，记录变长，使用压缩页同样会增加CPU时间。

使用一台旧的繁忙的服务器(单处理器100minps)所测得的运行时间如下:
全部扫描的SQL的CPU时间
CUST(1000000行，111000个4KB大小的页)2.5s，2.5μs每行
INVOICE(4000000行，770000个4KB大小的页)5s，1.25μs每行

假设记录数和页数是唯一的两个重要因素，那么我们可以计算出下面两个变量的系数 :
1000000X + 111000Y = 2.5s
4000000X + 77000Y = 5s

X = 每行的CPU时间 = 1μs
Y = 每页的CPU时间 = 13μs

X和Y的值清楚的表明了列的长度和个数对CPU时间的影响。

在第二个例子中采用了一个大型的繁忙的服务器(单处理器400mips)；扫描一张有222000个4KB大小的页，共13000000行短记录的表，结果集同样是0。该表经过了压缩，而且记录是变长的，但条件谓词依然是简单谓词。

最终测得的CPU时间是9s，平均每行0.7μs。

在第三个例子中使用了一个小型的服务器(单处理器750MHz)，扫描的CUST表有1000000行400字节的长记录，结果集共提取1078行记录，并进行排序操作。在WHERE条件中，有两个简单的谓词。最终测得的CPU时间是10.4s。

TS的CPU时间一共是10.4s - X，X代表1078行记录的FETCH和排序时间，利用默认的CPU系数进行计算 :
X = 1078 x 110μs ≈ 0.1s

于是，长记录的单次顺序访问的CPU时间就变成了10μs。

看起来5μs可以作为一个合理的系数值来使用，但是最好能在具体的平台上，使用长记录和短记录实测一下CPU时间。

单次随机访问的CPU时间

有以下几个原因会导致随机访问的CPU时间高于顺序访问的CPU时间

1. 几乎每次随机读取都会引起数据页请求
2. 如果数据页没有在缓冲池中，I/O相关的CPU时间会比较多。而由于顺序读取每次会读取多个数据页，所以平均每个数据页的I/O代价就相对较低。另外，平均每页的CPU成本能够平摊至许多顺序访问上。
3. 由于随机访问是不可预测的，所以CPU无法将数据页预读到告诉CPU缓存中，从而可能会导致明显的内存等待。
4. 按照QUBE的定义，一次对索引的随机访问忽略了访问非叶子页的成本，因为QUBE假定他们是位于缓冲池中的。然而，从计算CPU时间的角度上看，随机访问一个三层索引上的记录将引起三次数据页请求。这也是为什么索引上的随机访问会比表上的随机访问耗时更多。

计算单次随机访问的CPU时间的一个简单方法是，对比使用半宽或宽索引(以避免不必要的随机读取)前后的CPU时间差异。

另外一个方法是，消除掉低成本的操作(比如顺序访问和排序)。我们通过一个实例来描述这种方法 : 一个嵌套循环连接的SELECT查询语句，有813次对三层索引的随机访问，在一台100mips的机器上运行，各项指标如下 :
TR = 814(中等长度的索引行)
磁盘读 = 845(磁盘随机读取)
TS = 8130(较短长度的索引行)
F = 814
RS = 0
CPU时间 = 401ms

单次随机访问的CPU时间可以通过实测的TS系数及F系数计算得出 :
401ms - [(8130 x 1μs) + (814 x 50μs)] / 814 ≈ 434μs
转化为250mips的机器434μs/2.5 ≈ 173μs

这个CPU时间是在一个缓存命中率为0的测试环境得出的(数据库缓冲池和磁盘缓冲区都没有命中，即845次随机读取都来自磁盘)。在第一次测量完成后不接再次执行同样的事务时，消耗的CPU时间是165ms。在这个例子中，假设其他部分的CPU时间开销是48ms，那么单次随机访问的CPU时间就是 :
165ms - 48ms / 814 ≈ 144μs

在第一个例子中，平均一次随机读取的磁盘物理读个数是845/814 ≈ 1.04，而在第二次例子中完全没有磁盘I/O。这一结果验证了一条重要的原则 : 减少磁盘读缓存或者物理磁盘驱动器上的随机读取的CPU时间的一个重要因素。

根据经验，在当前的硬件条件下，单次随机访问的CPU时间通常在以下之一范围区间里 :
表访问 : 10~100μs
索引访问 : 20~300μs

影响随机访问CPU时间的最大因素还是CPU的高速缓存，如果大量的随机访问内存中的少了数据页，那么单次随机访问的CPU时间可能会小于10μs，实际上可能接近于顺序访问的耗时。

结论

单次随机访问的CPU时间100μs仍然可以用来进行快速评估。但是，按照之前的讨论，CPU时间会受到许多因素的影响，所以，当做出重要决定时(例如基于CPU时间来评估表的反范式化，或添加一个新索引等方案时)，需要使用一个合理的时间范围进行评估。比如，对于一个大索引，其随机访问的CPU时间可以假设在100~300μs之间。

然而，在评估CPU时间的潜在收益时，这可能会导致问题。比如，如果一个涉及10000次随机访问的SQL语句消耗了600ms的CPU时间，但是消除9000次随机访问并不会减少900ms的CPU时间。我们需要评估600ms中有多少消耗在TR上。

单次FETCH调用的CPU时间

单次FETCH调用的CPU时间消耗(除去访问的时间)依赖于平台以及程序与DBMS的连接方式。在一个具有多层结构和拥有事务管理器的应用设计下，发送SQL语句到数据库管理系统(以及将结果集传输回来)的CPU时间大概是50μs~100μs。在其他不同的环境下，尤其是当SQL语句嵌入在数据库服务器上的批处理程序中时，单次FETCH操作的CPU时间大概在10μs~20μs之间。

由批处理程序发起的FETCH访问的代价会相对较低。在一些场景下，100μs这一默认系数可能是悲观的。如下述场景所测得的那样。

例如，在之前的例子中，在一台大型的繁忙的机器上(单核440mips)借助全表扫描，结果集为空，得到的TS的CPU时间是0.7μs。然后又执行了一次该SELECT语句，只是这次所有记录都满足条件。该查询涉及222000个4KB大小的页，共13000000条记录，且SELECT列表只包含一个列。测得的总CPU时间是115s，单次FETCH操作平均耗时808μs(115s/13000000≈8.8μs)。因此，单次FETCH操作的CPU时间就是8μs(808μs - 0.7μs)。这是一个下限值，如果查询的列比较多，那么CPU时间会相对更长。对于中型的服务器(单核250mips)，只查询一个列的SELECT语句的单次FETCH调用的CPU时间下限约为(440mips/250mips)x8μs≈14μs。

每排序一行的平均CPU时间

在内存充足且没有引起磁盘I/O的情况下，排序操作的CPU时间基本和记录数呈线性关系。平台相关的系数很容易测试 : 首先执行一条对至少10000行记录排序的SELECT语句，一段时间后，在执行同样一条没有ORDER BY的SQL语句。平均排序一行的CPU时间是10μs。

CPU评估举例

在许多决策支持过程中，具备CPU需求的评估能力是有用的。

宽索引还是理想索引

单纯从响应时间来看，理想的索引并非具有完全的优势，我们给出了如下结论 :

虽然三星索引有一定的优势，尤其是在结果集为空的情况下，但是这个优势并不也别明显，而且会带来与新增索引相关的额外开销。

宽索引和最佳索引的QUBE如下。
宽索引 : 结果集为空

索引 LNAME, FNAME, CITY, CNO      TR = 1      TS = 10000
提取 0 x 0.1ms
LRT                               TR = 1      TS = 10000
                                  1 x 10ms    10000 x 0.01ms
                                  10ms + 100ms + 0ms = 110ms

最佳索引 : 20行结果记录(1屏)

索引 LNAME, CITY, FNAME, CNO      TR = 1      TS = 20
提取 20 x 0.1ms
LRT                               TR = 1      TS = 20
                                  1 x 10ms    20 x 0.01ms
                                  10ms + 0.2ms + 2ms = 110ms

现在来评估一下宽索引和理想索引的CQUBE值 :
宽索引 100μs x 1 + 5μs x 10000 + 100μs x 0 + 10μs x 0 ≈ 50ms
最佳索引 100μs x 1 + 5μs x 20 + 100μs x 20 + 10μs x 0 ≈ 2ms

从CPU的角度我们可以清楚的看到，使用我们之前得出的CPU系数，理想索引比宽索引的收益要高25倍。下图展示了所有索引的LRT和CPU指标。

增加一个索引，对于CUST表的插入和删除只会增加几毫秒的CPU时间(一次随机访问以及写相关的CPU时间)

嵌套循环(及反范式化)还是MS/HJ

在前面的连接查询的例子中，最终有两种可选方案 :

1. 使用嵌套循环式的BJQ连接(使用反范式化)。
2. 使用理想索引进行合并扫描(MS)或哈希连接(无反范式化)。

第二种方案的性能表现很好 : 在最差输入条件情况下，即返回1000行结果集的情况下，耗时为1.8s。但这个方案的唯一问题就是CPU时间。毕竟，扫描两个索引片，需要120000次随机访问以及对20000 + 100000行记录的合并排序或哈希连接。所以，在确定选择第二个方案之前，还需要慎重评估一下CPU的时间。

与之形成对比的是，用第一种方案获取一屏结果集的CPU时间非常短，且无排序 :
TR = 1 + 20 = 21, TS = 20 + 0 = 20, F = 20

所以，CPU时间 = 21 x 100μs + 20 x 5μs + 20 x 100μs ≈ 4ms(每屏结果集)。

方案2(使用合并扫描)

第一步 : 访问索引(CCTRY, CNO, CNAME, CTYPE)

TR = 1 TR = 100000
CPU时间 = 100μs x 1 + 5μs x 100000 ≈ 500ms

第二步 : 访问索引(IEUR DESC, CNO, INO)

TR = 1 TR = 20000
CPU时间 = 100μs x 1 + 5μs x 20000 ≈ 100ms

第三步 : 合并排序并FETCH结果行

将CUST作为外层扫描使得结果行按照所需的顺序排列。因此，最终的CPU时间如下。
排序及合并的CPU时间为 : 2 x 20000 x 0.01ms = 0.4s
提取的CPU时间为 : 2000 x 0.1ms = 0.2s

CPU时间

在最差输入下，这一访问路径下的CPU时间是上述三部分的总和 :
0.5s + 0.1s + 0.6s = 1.2s

方案2(使用哈希连接)

在这种方式下，第一步和第二步与合并扫描的前两步一样---CPU时间 = 00.5s + 0.1s = 0.6s

第三步 : 使用哈希函数进行匹配并FETCH结果集

优化器优先选择为来自INVOICE表的20000行短记录建立哈希表，表大小可能是20000 x 30字节 = 0.6MB。在生成环境中，该哈希表可以一次性全部缓存在内存当中，但不太可能全部缓存在只有1MB大小的CPU缓存中。

建立好哈希表后，开始扫描CUST表的索引片段。扫描的匹配过程需要随机访问哈希表100000次，单次访问的CPU时间依赖于CPU缓存缓存的命中率。若给定的是一个1MB大小的CPU缓存，那么我们假设单次哈希表访问的CPU时间是1μsμs ~ 50μs，于是，100000次哈希表访问的CPU时间就是
100000 x (1...50μs) = 0.1s ... 5s

最后，提取结果集，提取操作的CPU时间为 :
2000 x 0.1ms = 0.2s

如果哈希连接成本比较高，由于合并排序连接的CPU时间估值为0.4s，那么优化器很可能会选择使用合并扫描。在必要情况下，可以使用相应的MERGE提示来指定该访问路径。

CPU时间

在最差输入条件下，使用这一访问路径的CPU时间是上述三部分的总和，至少为 :
0.5s + 0.1s + 0.3s = 0.9s

结论

第一种方案需要大约4ms的CPU时间来提取一屏结果集，第二种方案需要1s的CPU时间来提取整个结果集(1000条记录，50屏)。对于比较小的结果集，合并扫描的CPU时间是0.6s(扫描，排序，合并20000条INVOICE索引记录)，而哈希连接的CPU时间则是0.2s(扫描20000条INVOICE索引记录)。

所以，如果SELECT语句执行的不是非常频繁，第二种方案很可能是可接受的。在极端情况下，可以创建两个游标，一个使用合并连接(针对大结果集)，一个使用哈希连接(针对小结果集)，从而用最小的代价避免表的反范式改造。