数据库性能调优之始： analyze 统计信息

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

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

1.1 query 执行流程

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

image

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 行

image

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

3.2 单列统计信息

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

image

image

通过统计新可以看出 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 打印执行计划如下

image

3.3 扩展统计信息

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

image

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

image

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 为列

image

注：只有对统计信息敏感的复杂查询动作(多表关联等操作)的 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 统计信息》，原文作者：譡里个檔。

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