关键字:
统计信息、直方图、autovacuum
1.统计信息简介
KingbaseES 优化器支持基于成本的物理优化(CBO),而物理优化的核心是代价评估。在不真正执行语句之前,提前对代价给出较为合理的估算则是优化器的一项必要的能力。 为了支持代价评估,KingbaseES 提供了统计信息,以采样的方式收集信息,来反应表和索引等数据的概览情况,方便优化器在短时间内对代价进行评估。
2.统计信息内容
优化器进行代价评估时主要依赖以下信息,也是统计信息需要采集的主要内容:
- 表级统计信息:包括页面数、元组数
- 列级统计信息:直方图、MVC等
列级统计信息主要反应数据的分布情况,进而帮助计算选择率。
单表扫描操作的代价估算公式如下所示
顺序扫描: N_page * a_tuple_IO_time + N_tuple * a_tuple_CPU_time 索引扫描: C_index + N_page_index * a_tuple_IO_time 说明如下: N_page:数据页面数。 a_page_IO_time:一个页面的 IO 代价。 N_page_index:索引页面数。 a_tuple_CPU_time:一个元组从页面中解析的 CPU 代价。 N_tuple:元组数。 C_index:索引的 IO 代价,C_index = N_page_index×a_page_IO_time。 N_tuple_index:索引作用下的可用元组数,N_tuple_index = N_tuple× 索引选择率。 |
2.1.表级统计信息
表和索引是数据库里最基本的对象,它们的基本信息对优化器来说也是最重要的。优化器需要知道每一个表和索 引占用的磁盘块数,以及表和索引包含的记录数。这些信息主要保存在 sys_class 表中:
Select * from sys_class;
其中,比较重要的 2 个字段为:
• Relpages:表或者索引占用的磁盘页面数。
• Reltuples:表或者索引记录的总数(预估的)。
优化器需要根据表或者索引的记录总数、磁盘页面数来估算对他们进行扫描的时候的成本,为访问路径的选择来 提供决策基础。
2.2列级统计信息
列统计信息是指对每个表的每一个列(也叫属性)的统计信息,它主要存储在 sys_statistic 系统表中,它负责从多个角度描述该列的数据概况信息,内容主要分 2 部分: 1) 基本统计信息–主要是 NULL 值率、列的平均宽度、列消重后的数据个数或比例
2) 数据分布信息–主要是高频值、直方图、相关系数等能描述数据分布情况的统计信息
3.统计信息的收集
KingbaseES内部存在着一个统计进程,以采样的方式收集统计信息。该统计进程不是使用全 部数据作为样本进行统计,而是随机地采集表中的一部分元组作为样本来生成统计信息。通过调整 default_statistics_target 的值可以改变样本容量,目前 default_statistics_target 的默认值是 100,
采样的方式包括:
• 自动收集
• 主动收集
3.1自动收集
KingbaseES 会默认自动收集统计信息,在配置参数 autovacuum 开启的情况下,会有一个后台进程自动检测。 autovacuum 是 kingbase 提供的一个 deamon 进程,在 kingbase 中,事务提交/回滚时会发消息给进程 sysstat, sysstat 会汇总这份信息并记录到文件中,autovacuum launcher 会定期读取文件,获得当某个表的改动超过阈值时便 会触发一次统计信息的更新操作。autovacuum 会执行 analyze 操作更新统计信息,analyze 操作对表进行随机采样更 新统计信息,而不是读取表的每一行。这些阈值等一系列配置参数保存在 kingbse.conf 里面,主要包括:
1、总控 autovacuum 参数:
参数名 | 默认值 | 描述 |
autovacuum | 默认值是 on | 是否允许自动 vacuum |
log_autovacuum_min_duration | 默认值为-1,不记录 vacuum 日志。0 记录所有 vacuumlog。 | 正整数,单位为微秒,表示 vacuum 执行时间小于该值的不记录,大于该值的的记录 log。 |
autovacuum_max_workers | 默认值为 3。 | 最大的 autovacuum 进程的数量 |
autovacuum_naptime | 默认为 1 分钟 | 检查数据库的时间间隔 |
2、自动 vacuum 参数:
参数名 | 默认值 | 描述 |
autovacuum_vacuum_threshold | 默认值 50 | 激活自动 vaccum 的最小 DML 操作的行数 |
autovacuum_vacuum_scale_factor | 默认值为 20% | 该参数采用百分比的方式设定阀值 |
3、自动analyze参数
参数名 | 默认值 | 描述 |
autovacuum_analyze_threshold | 默认值 50。 | 激活自动 analyze 操作的最小行数 |
autovacuum_analyze_scale_factor | 默认值为 10%。 | 到达阀值时自动激活 analyze 操作 |
4、事务 ID 相关参数
参数名 | 默认值 | 描述 |
autovacuum_freeze_max_age | 默认为 2 亿。 | 为防止事务 ID 的重置,在启用 vacuum 操作之前,表的 sys_class.relfrozenxid 字段的最大值 |
autovacuum_vacuum_by_snapshotcs | 默认值为-1,表示参数无效;用户可设置的范围为 0 到 2147483647,设置为 0 将一直触发功能。 | 用于 VACUUMAUTOVACUUM 在执行清理操作时,可以越过长事务阻碍,正常清理垃圾数据。对于常出现长事务的 业务场景,因数据膨胀导致性能下降时可以开启此参数。 |
3.1主动收集
主动收集是指用户通过定时脚本或者人工执行 analyze 命令来进行。此命令将触发数据库对统计信息进行收集并更新。
主动收集统计时机的信息一般为:
• 装载大量数据后
• CREATE INDEX 操作后
• 在大量更改底层数据的 INSERT、UPDATE 以及 DELETE 操作之后
• 执行计划的代价估算不准确时
注意: ANALYZE 仅在表上要求一个读锁,因此它可以与其他数据库活动并行运行。但不要在执行装载、INSERT、 UPDATE、DELETE 以及 CREATE INDEX 操作期间运行 ANALYZE。因为在执行完上面的操作后,表的信息发生 了改变,需要重新做 ANALYZE。
4.统计信息的局限性
对于统计信息,由于是以采样的方式进行收集,因此必然会存在着一个不可回避的问题:统计信息的准确性(或 者说不确定性),以及由不准确所带来的问题:采样导致的误差、统计信息收集不够及时导致的误差、多次选择率计 算叠加产生的误差,进而导致最后选择计划的偏差。当这些偏差发生时,可以先对统计信息做主动收集。依然不能解决的情况下,可以通过 HINT 注释去控制执行计划。