浅谈聚集因子

我们往往会讨论什么时候用索引，什么时候用全表:大家可能说的最多的是需要的数据量和表里面的数据量占比，有些人说20%以下用索引，有些人说10%以上就不能用索引了，

其实除了和数据量有关还有一个非常关键因数就是聚簇因子。

      什么是聚簇因子？

      我们先看我们的常用的表又叫堆表。堆表的最大特征就是数据的存储独立性，即数据的存储与数据值没有任何关联地被存储在磁盘的任意位置上。从另外一个侧面来看，该特征也就意味着为了查询我们所需要的数据必然要在磁盘的多个位置上进行查找。

      所谓"任意位置"的深层含义是指能够把数据物理地存储在磁盘上的方法多种多样。然而，从另外一个角度来看，随机存储方式就是数据所占据的位置分散在不同的数据块上。

      在这种存储状态下，查询相同数据所执行的物理读取数量会随着查询数据的分散程度而不同。例如，数据行1~10被分散存储在10个数据块与两个数据块相比较，虽然两种情况逻辑读取的数据行数（都是10行）是相同的，但在物理（I/O）读取的数据块数上却相差5倍。在关系型数据库中，不论在何种情况下，每次最少都需要读取一个数据块。尽管我们每次要求读取的是行，但是实际上每次读取的却是数据块。因此，如果能够在内存中命中我们所需要查询的数据行，则在很大程度上就能够减少物理I/O的数量。尽管在不同系统环境下会略有一些差异，但在一般情况下，从内存块上查询数据的速度比从磁盘块上查询数据的速度至少快30倍左右（有些可能数百倍），聚簇因子主要影响着索引的读取。

      聚簇因子是指，按照索引列值进行了排序的索引行序和对应表中数据行序的相似程度。这就好似把孩子与父母的相像程度用数值来表示一样。由于聚簇因子大小对数据读取效率有着直接的影响。假设数据存储如下图:

则index1访问其中7行，需要2个块，而index2访问3行就需要3个块，

可见index2的聚簇因子是很不好的，我们假想下：加入有一个表t有1000万行，有10万个块,我们有个provcode(省)是索引，provcode的distinct有32个，那么如果我们取其中的一个省份如果按照平均来计算是不是就是1/32的数据，这个比例接近3%，很多人认为这个应该走provode索引，但是如果这个32个值是平均分布，也就是说很可能导致我们取其中一个省份，由于他分布在所有的数据块里面，导致我们相当于要读取整个表，这个性能是非常差的，这个时候全表就效果更好（这里有多块读等因数）。改变聚簇因子的办法不是建立什么索引，而是改变数据的存储方式，如果一个表的数据已经固定了，我们怎么去使他相对某一列的聚簇因子好呢，我们可以在插入的时候将数据进行那一列的order by ，这样会使得数据是按照顺序的插入。好的聚簇因子的例子:

上图可以看到clustering_factor和块数是相等的，这个是最好情况，最坏的情况是clustering_factor等于num_rows。

对于究竟应当按照哪个列的顺序存储数据，这个也需要和业务相关，有些我们是控制不了的，但是我们需要理解存储顺序对我们读取的影响,如果我们想获得好的聚簇因子只需要定期对表进行CTAS(create table… as … order by )即可,不过表重构的代价也是不小的,而且表的重构操作也不是随心所欲的事情，所以我们需要理解聚簇因子并很好的使用它。