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

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

在数据库性能调优的过程中，统计信息扮演着至关重要的角色。它是数据库优化器进行代价估算和选择最优执行计划的基础。以下是对统计信息的详细解析，包括为什么需要统计信息、统计信息包含哪些内容、统计信息存储在哪里、如何生成统计信息以及何时收集统计信息。

一、为什么需要统计信息

数据库查询优化器分为基于规则的优化器（RBO）和基于代价的优化器（CBO）。目前，大多数现代数据库系统，如GaussDB，采用的是CBO模型。CBO模型通过代价模型和统计信息来估算每种执行计划的代价，并选择代价最小的执行方式。统计信息是CBO模型中代价计算的数据输入，如果统计信息缺失或不准确，优化器可能会选择非最优的执行计划，从而影响SQL语句的性能。

二、统计信息包含哪些内容

统计信息是指数据库描述表或索引数据特征的信息。常见的统计信息包括：

• 表规模信息：如表的记录条数、页面数等，用于估算扫描结果集行数和表从存储接口扫描数据的代价。
• 单列统计信息：如列的NULL值比例、高频非NULL值（MCV）、直方图（HISTOGRAM）、相关性（CORRELATION）等，用于估算基于单列条件的过滤行数。
• 扩展统计信息：如多列之间的相关性统计，用于估算基于多列条件的过滤、关联或分组操作的行数。

三、统计信息存储在哪里

统计信息存储在系统表中，具体如下：

• 表规模信息：存储在系统表pg_class的reltuples和relpages字段中。
• 单列统计信息：存储在系统表pg_statistic中，但通常推荐使用查询系统视图pg_stats来查询，因为pg_stats的可读性更强。
• 扩展统计信息：存储在系统表pg_statistic_ext中，同样推荐使用查询系统视图pg_ext_stats来查询。

四、如何生成统计信息

生成统计信息的主要方法是使用ANALYZE命令。以下是具体的操作方法：

• 显式收集统计信息：

  单列统计信息：使用ANALYZE table_name (column_name);命令收集指定列的统计信息。但通常建议对全表收集统计信息，以确保优化器有足够的数据进行代价估算。

  扩展统计信息：使用ANALYZE table_name ((column_1_name, column_2_name));命令收集多列统计信息。需要注意的是，当前只支持在百分比采样模式下生成扩展统计信息。

• 提升统计信息质量：

  可以通过配置参数default_statistics_target来控制采样数。该参数的取值范围为-100~10000，默认值为100。

  当default_statistics_target > 0时，采样的样本数为300 * default_statistics_target。取值越大，采样的样本也越大，但计算耗时和内存占用也会增加。

  当default_statistics_target < 0时，采样的样本数为(default_statistics_target) / 100 * 表的总行数。此时称为百分比采样，样本会下盘存储，不存在内存占用问题，但计算耗时同样存在。

• 自动收集统计信息：

  当配置参数autoanalyze打开时，查询语句走到优化器发现表不存在统计信息，会自动触发统计信息收集。但需要注意的是，只有对统计信息敏感的复杂查询动作（如多表关联）的SQL语句执行时才会触发自动收集统计信息。

五、何时收集统计信息

收集统计信息的时机非常重要，以下是一些建议：

• 大规模数据变化：如大规模的数据导入、UPDATE或DELETE操作后，需要重新收集统计信息，以确保优化器有准确的数据进行代价估算。
• 查询新增数据：在业务表新增数据查询场景中，如果新增数据入库之后没有做ANALYZE，优化器可能会因为统计信息不准确而导致估算行数失真。因此，在查询新增数据之前，建议对相关表进行ANALYZE操作。

六、谁来收集统计信息

在AP场景下，业务表数据量通常很大，单次导入的数据量也比较大，而且经常是数据导入即用。因此，建议在业务开发过程中，根据数据变化量和查询特征在需要的地方主动对相关表进行ANALYZE操作。这样可以确保优化器有准确的统计信息进行代价估算，从而选择最优的执行计划，提高SQL语句的性能。

综上所述，统计信息在数据库性能调优中起着至关重要的作用。通过合理收集和利用统计信息，可以显著提高数据库查询的性能。