数据库系列-PostgreSQL之统计信息

前言

时至今日，已经时隔两周了。不过没关系，重点是坚持学习。说到这，最近有看到其他的书有些新的感悟：60分知识。核心思想很简单：就是要能够自主认知到某个知识对于目前的自己而言，需要达到什么程度。对于非专业领域的知识，达到60分就差不多了。能够做到借助其简单解释和应用就可以了。例如，Java程序员并不需要很深入的理解DBA的所有知识。但是要能使用基本的原理，并应用其来解释运用提高自己的SQL效率和性能之类的。这就是从《认知升级》提炼出来的，我的见解。所以能够通过别人的博客来学习，是一项很重要的技能。同时，更重要的是，将博客中的零散的知识整合成系统的知识，是更高阶的技能！

统计信息

很多开发同学可能都知道数据库会使用统计信息来进行决策，从而估算推断最优的执行计划。但是对于更具体的，就不太清楚了。今天就稍微再了解多一些吧。

统计信息有哪些

表的统计信息——pg_class

PostgreSQL有一张记录对象信息的系统表——pg_class。我们可以通过这张表来查看某些关于表的统计信息。

select
       -- 当前表所占用的数据页数量       
       relpages,
       -- 当前表一共有多少行组（记录）
       reltuples 
from pg_class 
where relname = #tableName#

单列统计信息——pg_stats

单列统计信息默认都会生成的，相比于多列统计信息。所谓单列统计信息，是指对某个表的某一个属性的值分布及其特征进行统计分析后的结果。而PostgreSQL会对所有的表的所有的列都进行统计，并将其保存在pg_stat表中.

-- 查询某个表的某一列的统计信息
select 
	-- Null值率
	nullfrac,
	-- 去重后的值个数/其与总元组比值的负数
	n_distinct,
	-- 高频值个数，简称MCV，由default_statistics_target（默认100）决定记录多少个
	most_common_vals,
	-- 高频值占比(与MCV一一对应)
	most_common_freqs,
	-- 等频直方图，剔除MCV后，每个区间范围中的元素在总元组中的占比一样
	histogram_bounds,
	-- 物理行序与索引行序的相关性
	correlation,
	-- 平均行宽度，单位Byte
	avg_width
  from pg_stats 
 where tablename = #tableName# 
   and attname = #columnName#
   

其中，与选择率有关的
nullfrac，该字段的Null值率
n_distinct，去重后的值个数/其与总元组比值的负数。
histogram_bounds，等频直方图
most_common_vals，高频值个数-MCV
most_common_freqs，高频值占比-MCF
与索引代价有关
correlation，物理行序与索引行序的相关性。如果该值为0，则代表完全不相关，意味着索引扫描的随机访问代价可能会比较高。
与计算内存有关的
avg_width，平均行宽度。

多列统计信息-——pg_statistic_ext

可能这个大家甚至都不知道这个东西。这个对于多约束条件下的选择操作而言，用来计算选择率会比使用单列统计信息通过概率方法运算得出的结果更加准确。但是这个需要自行创建统计信息：Create statistics xxx on field1,field2 from xxx，来针对多个列的组合情况进行统计分析。

-- 根据统计对象的名称查询
select 
	-- 相当于单列统计信息的n_distinct
	stxndistinct,
	-- 函数依赖度
	stxdependencies
  from pg_statistic_ext
 where stxname = #stxname#

统计信息的采样

采样分成两个步骤：数据采样和数据统计。

• 数据统计自然就是上述统计信息的计算。

• 数据采样，则是基于统计学原理，估算样本反映的整体信息。
  PG默认的样本量为300 * default_statistics_target = 30000

  对于数据量少于30000的表，则意味着样本容量是所有数据行。而对于数据量远大于30000的表，则意味可能会引入误差。而这些误差又会影响到后面统计出来的统计信息。进而对执行计划产生更大的影响。

  实际上采样被分成了2个阶段，第一阶段对数据页进行采样。在这个阶段，数据页是可以准确获得的，使用随机采样法-S算法，相对简单。第二阶段是对元组进行采样由于数据量不可知，采用的是蓄水池算法-Z(Vitter)算法。

选择率

选择率 = 约束条件过滤后的元组数 / 约束条件过滤前的总元组数

这个选择率，最终会通过影响IO cost和CPU cost来影响执行计划。

选择率的计算

1. 等值约束

   非高频值非空值约束(Var = const)
   选择率 = (1 - 列空值率 - 高频值总占比) / (去重后非空值个数 - 高频值个数)
   = (1 - nullfrac - sum(MCF)) / (stadistinct - sum(MCV))
   高频值的选择率即为高频值出现的频率——MCF
   空值的选择率即为空值率——nullfrac

2. 非空值约束 (Var is not null)

   选择率 = 1 - 空值率 = 1 - nullfrac

3. 范围选择(Var > const之类 ) —— 基于等频直方图求解

   选择率可以根据范围落在直方图的区域，区域所占比例即为选择率（无高频值的情况下可以这样简单计算）。

4. And/or

   通过概率运算进行整合
   Or ：P(A + B) = P(A) + P(B) - P(AB)
   And : P(AB) = P(A) x P(B)

   然而，这样简单的概率运算会使得误差被放大。实际上，多列统计信息就是为了提供更准确的信息来估算选择率。函数依赖度！例如，A=Const and B=Const

   P(A=Const,B=Const)
   = P(A=Const) x (d + (1-d) x P(B=Const) )
   d为{A -> B}的函数依赖度
   A=Const and B=Const and C = Const

   P(A=Const,B=Const, C = Const)
   = P(A=Const,B=Const) x (d + (1-d) x P(C=Const) )
   d为{A,B -> C}的函数依赖度.

   选择多列的函数依赖度需要满足一下规则：

   1. 约束条件中所有属性应该能覆盖统计信息中的键值（属性值）
   2. 在覆盖的情况下，选择键值最多的那组统计信息
   3. 如果两个统计信息键值数相同，则选择函数依赖度高的统计信息。

总结：

1. 统计信息分为：表的统计信息（pg_class），单列统计信息(pg_stats)以及多列统计信息(pg_statistic_ext)。
2. 统计信息收集分为2个阶段：采样（数据页-随机采样，元组-蓄水池算法）+统计（各种统计信息运算）
3. 选择率是基于统计信息来估算最终结果集的元组数量占比的。这个信息最终会影响到执行计划。

下集预告

通过统计信息，我们计算出了大概的选择率。那么PG是如何运用选择率的呢？选择率通过影响随机访问代价，最终影响到是否选择索引扫描。

参考：《PostgreSQL技术内幕-查询优化深度探索》