关于数据库:数据库性能调优之始-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 列的 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 统计信息》，原文作者：譡里个檔。

点击关注，第一工夫理解华为云陈腐技术~