数据库性能调优之始： 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字段信息如下

查询表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来查询当前用户有查询权限的扩展统计信息。

表的多个列有相关性且查询中有同时基于这些列的过滤条件、关联条件或者分组操作的时候，可尝试收集多列统计信息。扩展统计信息需要手动进行收集(具体收集方法，下个小节会介绍)，如下为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)性能调优系列基础篇一：万物之始analyze统计信息》，原文作者：譡里个檔。

点击关注，第一时间了解华为云新鲜技术~