GaussDB(DWS)性能调优系列基础篇一：万物之始analyze统计信息

本文简单介绍一下什么是统计信息、统计信息记录了什么、为什么要收集统计信息、怎么收集统计信息以及什么时候收集统计信息。

1      WHY：为什么需要统计信息

1.1      query执行流程

下图描述了GaussDB的SQL引擎从接收客户端SQL语句到执行SQL语句需要经历的关键步骤，以及各个流程中可能对执行产生影响的因素

1)  词法&语法解析

按照约定的SQL语句规则，把输入的SQL语句从字符串转化为格式化结构(Stmt)，如果SQL语句存在语法错误，都会在这个环节报错。

2)  语义解析

语义解析类似一个翻译器，把外部输入的可视化的对象翻译为数据库内部可识别的对象（比如把Stmt中以字符串记录的表名称转化为数据库内部可识别的oid），如果语句存在语义错误(比如查询的表对象不存在)，数据库会在这个环节报错。

3)   查询重写

根据规则将“语义解析”的输出等价转化为执行上更为优化的结构，比如把查询语句中的视图逐层展开至最低层的表查询。

4)  查询优化

数据库确认SQL执行方式、生成执行计划的过程

5)  查询执行

根据执行计划执行SQL并输出结果的过程

整个执行流程中，优化器决定了查询语句的具体执行方式，对SQL语句的性能起着关键性的作用。数据库查询优化器分为两类：基于规则的优化器(Rule-Based Optimizer，RBO) 和基于代价的优化器(Cost-Based Optimizer，CBO)。RBO是一种基于规则的优化，对于指定的场景采用指定的执行方式，这种优化模型对数据不敏感；SQL的写法往往会影响执行计划，不了解RBO的细则的人员开发的SQL性能不可控，因此RBO逐渐被抛弃，目前GaussDB等数据库厂商的优化器都是CBO模型。CBO模型是根据SQL语句生成一组可能被使用的执行计划，并估算出每种执行计划的代价，最终选择选择一个代价最小的执行方式。

1.2      CBO模型

数据库执行SQL语句的时候，会把执行拆分为若干步骤，如下SQL

select *

from t1 join t2 on t1.a=t2.b

where t1.b = 2 and t2.a = 3;

在具体执行的时候会拆分为表扫描和表关联两个主要查询动作。这两个查询动作都存在多种执行方式，比如表扫描均存在SeqScan、IndexScan、IndexOnlyScan、BitmapScan等多种执行方式、表关联存在NestLoop、HashJoin、MergeJoin三种执行方式，那么在具体的业务场景下什么样的查询动作才是代价最小的执行方式，这就是优化器的核心工作。

CBO主要工作原理是通过代价模型(Cost Model)和统计信息估算每种执行方式的代价，然后选择一种执行代价最优的执行方式。这里面代价模型是核心算法逻辑，统计信息是cost计算的数据源，二者配合完成cost计算；如果统计信息缺失，计算时代价模型会使用默认值来计算cost，当然这时cost会跟真实值存在较大偏差，大概率会出现选择非最优执行计划的情况，因此统计信息是CBO模型中 cost计算的数据输入，是CBO最核心的科技之一。

2      WHAT：都有哪些统计信息

统计信息是指数据库描述表或者索引数据特征的信息，常见的有表记录条数、页面数等描述表规模的信息，以及描述数据分布特征的MCV(高频非NULL值)、HISTOGRAM（直方图）、CORRELATION等信息。

本文中通过如下用例来展示统计信息是如何表现表的数据特征的

DROP TABLE public.test;

CREATE TABLE public.test(a int, b int, c int[]);

INSERT INTO public.test VALUES (generate_series(1, 20), generate_series(1, 1200));

INSERT INTO public.test VALUES (generate_series(1, 1200), generate_series(1, 1200));

UPDATE public.test SET c = ('{' || a || ','|| a || '}')::int[] WHERE b <= 1000;

UPDATE public.test SET c = ('{' || a || ','|| b || '}')::int[] WHERE b > 1000;

ANALYZE public.test;

3      WHERE：统计信息在哪里

3.1      表规模信息

系统表pg_class中的reltuples和relpages两个字段能够反映表规模信息信息，其中relpages记录了表数据存储到几个page页里面，主要用于表从存储接口扫描数据的代价计算；reltuples记录了表记录条数，主要用于扫描结果集行数估算。

查询pg_class中的表规模估算信息，显示表为2400行

单表全量数据查询，通过explain查看表规模估算，显示表扫描输出行数估算为2400

3.2      单列统计信息

单列统计信息是指表的单列的数据特征信息，存储在系统表pg_statistic中。因为pg_statistic会存储一些关键采样值来描述数据特征，因此pg_statistic数据是敏感的，只有超级用户才可以访问pg_statistic。通常我们推荐用户使用查询系统视图pg_stats来查询当前用户有查询权限的表的统计信息，同时pg_stats信息的可读性更强，pg_stats字段信息如下

名称

描述

schemaname

统计对象所在的namespace名

tablename

统计对象名

attname

统计列的名称

inherited

如果为真，那么统计分析时采样样本包括继承表数据

null_frac

该字段NULL值的个数比率

avg_width

该字段非NULL值的平均字节宽度

n_distinct

字段中非NULL值的distinct值。如果大于0，则表示实际distinct值个数；如果小于0，则它的绝对值表示distinct值占全部非NULL值个数比例。例如-1表示distinct值的数目与行数相同

n_dndistinct

第一个DN上该字段非NULL值的distinct值，取值含义与n_distinct一样

most_common_vals

高频非空值按照出现的频次排序的列表，列表中的值我们一般简称为MCV值

most_common_freqs

对应每个MCV值出现的频率列表，列表中的每个值表示对应的MCV值出现的次数与表的总记录数(包含NULL值)的比例

histogram_bounds

去除NULL值和most_common_vals之外的其它值按照’<’操作符排序，然后按照个数等分的边界值。如果此字段的数据类型没有<操作符或取值都在most_common_vals中出现过，则这个字段为NULL

correlation

字段值的物理行序和按照’<’排序的逻辑行序的相关性，我们一般称之为排序相关性，取值范围为-1到+1；数据越按照’<’操作符排序，取值越接近1；数据越按照’>’操作符排序，取值越接近-1。取值越接近于-1或者+1，说明索引扫描时引入的随机IO开销越小，索引扫描的随机IO的cost值也越小。如果字段数据类型没有<操作符，则这个字段为NULL。

most_common_elems

数组类型的最常用的非空元素的列表，类似most_common_vals，但记录的不是字段值，而是构成数组字段的元素

most_common_elem_freqs

most_common_elems中每个元素出现的频次与该字段非NULL值的记录数的比例，同时还在字段尾部依次追加了元素的最小值、最大值、NULL值个数的比例，所以此字段元素的个数总是比most_common_elems元素的个数多3个

elem_count_histogram

数组类型非NULL distinct值的直方图信息，末尾为平均唯一值个数

查询表public.test的a列的数据特征信息如下

通过统计新可以看出public.test的a列的NULL值比例为0，存在120个distinct值， 1~20是MCV值，每个出现的概率是0.0254167；21~1200出现在在直方图统计信息中；

以查询语句“SELECT count(1) FROM public.test WHERE a < 44;”为例说明统计信息在优化过程中行数估算场景下的作用

a)   所有MCV值均满足a < 44，所有MCV值的比例为0.0254167 * 20 = 0.5083340

b)   44为直方图中第三个边界，直方图中满足a < 44的值的比例为(1-0.5083340)/100 *(3-1)= .0098333200

那么表中满足a<56的tuples的个数为1243.6015680 ≈1244，通过explain打印执行计划如下

3.3      扩展统计信息

扩展统计信息存储在系统表pg_statistic_ext里面，当前只支持多列统计信息这一种扩展统计信息类型。pg_statistic_ext会存储一些关键采样值来描述数据特征，因此pg_statistic_ext数据是敏感的，只有超级用户才可以访问pg_statistic_ext，通常我们推荐用户使用查询系统视图pg_ext_stats来查询当前用户有查询权限的扩展统计信息。

名称

描述

schemaname

统计对象所在的namespace名

tablename

统计对象名

attname

扩展统计信息涉及列编号的数组

inherited

如果为真，那么统计分析时采样样本包括继承表数据

null_frac

该字段组合中所有字段均为NULL值的个数比率

avg_width

该字段组合的平均字节宽度

n_distinct

字段中非NULL值的distinct值。大于零的数值是多字段组合的不同值的实际数，小于零是多字段组合的distinct值占全部非NULL值个数比例的负数

n_dndistinct

第一个DN上的n_distinct值

most_common_vals

字段组合里最常用数值的列表，此字段要求多列的每个列都不存在NULL值

most_common_freqs

most_common_vals中每个值出现的频率列表，列表中的每个元素描述了most_common_vals中对应值出现的次数与表的总记录数(包含NULL值)的比例

most_common_vals_null

高频非空值按照出现的频次排序的列表，此字段要求多列中的至少1列包含NULL值，但又不全部是NULL值

most_common_freqs_null

most_common_vals_null中每个值出现的频率列表

表的多个列有相关性且查询中有同时基于这些列的过滤条件、关联条件或者分组操作的时候，可尝试收集多列统计信息。扩展统计信息需要手动进行收集(具体收集方法，下个小节会介绍)，如下为test表(a,b)两列的统计信息

4      HOW：如何生成统计信息

4.1      显式收集统计信息

4.1.1 单列统计信息

通过如下命令收集单列统计信息：

{ ANALYZE | ANALYSE } [ VERBOSE ]  [ table_name [ ( column_name [, ...] ) ] ];

如语法描述，我们支持对指定列做统计信息，但是实际上我们很难统计实际业务SQL中到底使用了当前哪些表的列进行了代价估算，因此建议通常情况下对全表收集统计信息。

4.1.2 扩展统计信息

通过如下命令收集多列统计信息：

{ANALYZE | ANALYSE} [ VERBOSE ] table_name (( column_1_name, column_2_name [, ...] ));

需要注意的是，当前只支持在百分比采样模式下生成扩展统计信息，因此在收集扩展统计信息之前请确保GUC参数default_statistics_target为负数

4.2      提升统计信息质量

analyze是按照随机采样算法从表上采样，根据样本计算表数据特征。采样数可以通过配置参数default_statistics_target进行控制，default_statistics_target取值范围为-100~10000，默认值为100。

1） 当default_statistics_target > 0时；采样的样本数为300*default_statistics_target，default_statistics_target取值越大，采样的样本也越大，样本占用的内存空间也越大，统计信息计算耗时也越长

2） 当default_statistics_target < 0时，采样的样本数为 (default_statistics_target)/100*表的总行数，default_statistics_target取值越小，采样的样本也越大。但是default_statistics_target < 0时会把采样数据下盘，不存在样本占用的内存空间的问题，但是因为样本过大，计算耗时长的问题同样存在

default_statistics_target < 0时，实际采样数是(default_statistics_target)/100*表的总行，所以我们又称之为百分比采样。

4.3      自动收集统计信息

当配置参数autoanalyze打开时，查询语句走到优化器发现表不存在统计信息，会自动触发统计信息收集，以满足优化器的需求。以文档的case为列

注：只有对统计信息敏感的复杂查询动作(多表关联等操作)的SQL语句执行时才会触发自动收集统计信息；简单查询(比如单点，单表聚合等) 不会触发自动收集统计信息

5      WHEN：什么时候收集统计信息

5.1      大规模数据变化

大规模数据导入/UPDATE/DELETE等操作，会导致表数据行数变化，新增的大量数据也会导致数据特征发生大的变化，此时需要对表重新收集统计信息

5.2      查询新增数据

常见于业务表新增数据查询场景，这个也是收集业务中最常见、最隐蔽的统计信息没有及时更新的问题，这种场景最主要的特征如下

1)       存在一个按照时间增长的业务表

2)       业务表每天入库新一天的数据

3)       数据入库之后查询新增数据进行数据加工分析

在最后步骤的数据加工分析时，最长的方法就是使用Filter条件从分区表中筛选数据，如passtime > ‘2020-01-19 00:00:00’ AND pastime < ‘2020-01-20 00:00:00’，假如新增数据入库之后没有做analyze，优化器发现Filter条件中的passtime取值范围超过了统计信息中记录的passtime值的上边界，会把估算满足passtime > ‘2020-01-19 00:00:00’ AND pastime < ‘2020-01-20 00:00:00’的tuple个数为1条，导致估算行数验证失真

6      WHO：谁来收集统计信息

AP场景下业务表数据量一般都很大，单次导入的数据量也比较大，而且经常是数据导入即用，因此建议在业务开发过程中，根据数据变化量和查询特征在需要的地方主动对相关表做analyze。

数据仓库服务 GaussDB(DWS)

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。