从Oracle索引的Clustering Factor看PG的Correlation

十多年前我为某企业的集采招标组织了一次PC SERVER的基准测试，参测的包括IBM、HP、华为、曙光、浪潮等。实际上我们对各厂商提出的配置要求是一致的，使用的CPU,磁盘，内存都差不多。虽然各个厂商调教产品的水平不同会导致一些差异。因此对于大多数性能测试用例来说测试成绩应该差不多，在功耗和耐力测试上才能看出差距来。不过实际测试时，IBM在性能测试上的分数就比其他厂商高出很多。

这让我十分疑惑，检查了多次也没有发现IBM有作弊的情况。我们的检查工具会对数据做严格的检查，一旦出现篡改测试数据等情况肯定是能发现的。就在我百思不得其解的时候，我看到IBM的测试区的桌上放着一本我写的《ORACLE 优化日记》，其中书签页放在Clustering Factor相关的案例上。于是我眼前一亮，让同事对几张大表中的数据顺序做了检查。其中两张在测试中大量做范围扫描的表确实被他们重新做了排序。我们以前的检查工具只检查是否有数据被删除，并没有检查数据的顺序，因此就没有发现这个作弊的行为。说实在的前些年做基准测试，IBM的测试团队的技术水平还是很高的，他们总是能够在不违反测试规则的情况下找到测试用例的漏洞。他们靠的是自身的优化能力来利用测试漏洞，这比某些靠野蛮作弊的厂商要高出不少。

我介绍的这个例子中有个概念索引的“Clustering Factor”，集簇因子。什么是集群因子（Clustering Factor）呢？集群因子反映的是如果通过一个索引范围扫描一张表，需要访问的表数据块的数量。集群因子的计算方法如下：

(1) 扫描一个索引；

(2) 比较某行的ROWID和前一行的ROWID，如果这两个ROWID不属于同一个数据块，那么集群因子增加1；

(3) 整个索引扫描完毕后，就得到了该索引的集群因子。

从上面集簇因子的计算方式我们可以看出，集簇因子反映了索引范围扫描可能带来的对整个表访问过程的开销情况，特别是IO开销。实际上哪怕所有的块都在DB CACHE里，如果这些行存在于少数数据块中，扫描操作依然可以因为更少的闩锁访问而变得更快。

如果集簇因子接近于表存储的块数，说明这张表是按照索引字段的顺序存储的。如果集群因子接近于行的数量，那说明这张表不是按索引字段顺序存储的。在计算索引扫描的访问成本时，集群因子十分有用。集群因子乘以选择性参数就是访问索引的开销。如果这个统计数据不能反映出索引的真实情况，那么可能会造成优化器错误选择执行计划，全表扫描、索引范围扫描还是快速索引扫描。另外，如果某张表上的大多数访问是按照某个索引做索引扫描，那么将该表的数据按照索引字段的顺序重新组织，可以提高该表的访问性能。

PostgreSQL数据库的索引没有Oracle Clustering Factor的概念，那么PostgreSQL是根据什么来判断索引扫描的效率的呢？在pg_stats视图里，有一个字段：correlation（列数据相关性）。PostgreSQL在做表的Analyze的时候，也会分析字段的顺序，通过correlation来告知DBA某个字段的物理存储顺序和逻辑顺序之间的相关性是多少。

列的相关性是介于 -1 和 1 之间的值，代表了这个字段逻辑顺序和物理顺序之间的匹配程度。如果相关性为 1，则表中数据行的物理存储是按升序存储在表文件中的;如果为 -1，则按降序存储。越接近-1或者+1，说明数据越有序。值为 0 表示物理顺序和逻辑顺序之间没有联系。

大家通过Oracle 的CF和PG的Correlation在算法上的差异可以看出，Oracle的CF能够更为精准地反映出索引扫描的效率，PG有Correlation，也可以部分解决问题。下面我们通过一个例子来进一步说明。

我们来做个试验，首先创建两张测试表，都是从一个统一的基表数据来创建，只不过一个是按照ID排序的，一个是随机选取的。

我们来看看这两张表的ID字段的correlation，查看pg_stats之前一定要做一次analyze，否则是看不到数据的。

接下来我们对这两张表做一次范围扫描，看看效率有何不同。如上图，我们看到按照ID排序的表的访问效率要高不少。对于实际生产环境，如果数据块不在内存里，这个差异还会放大，因为IO的成本更高。

从上面的案例我们可以学到些什么呢？首先我们可以学到一个优化的小技巧。对于经常会使用范围扫描来访问数据的表，如果大多数范围扫描都是基于某个索引，那么将表数据针对索引顺序进行重新排序，会大大提升扫描的效率。十多年前我们经常使用这个方法来优化用户的系统。如果表的数据变化不是特别大，那么这种优化一年做一次就可以管用好久，如果是分区表的话，只需要对部分分区做就行了。这个方法对于绝大多数HEAP结构存储数据的数据库是有效的，对于B+树或者LSM-TREE存储结构的数据库没什么用处。因此对于Oracle、PG，这个小技巧是有用的。

对于PostgreSQL的用户和DBA来说，我们也可以学会通过Correlation字段去分析某个字段的数据关联性，从而分析一些范围扫描的性能问题。

对于国产数据库厂商来说，我想Oracle的Clustering Factor肯定比PG的Correlation更加精准，如果在做索引分析的时候，采集类似Oracle 集簇因子的属性，对于CBO生成更为精准的执行计划是有益的。

今天就写这么多，希望我今天介绍的这个小知识点，能够对大家有所帮助，有所启发。