一、问题背景

在使用达梦数据库的过程中，有时候会遇到查询性能不高的情况。其中一个重要原因可能是统计信息收集时机不当，导致优化器选择了非最优执行计划。统计信息就像是数据库优化器的“地图”，它能帮助优化器知道数据在表中的分布情况，从而选择最佳的查询执行路径。

比如说，我们有一个电商数据库，里面有一张orders表记录着所有订单信息。如果统计信息不准确，优化器可能会错误估算从表中读取数据的成本，进而选择较慢的执行计划。比如原本可以通过索引快速定位数据，却选择了全表扫描，这会大大降低查询性能。

二、自动收集窗口规划

2.1 确定数据更新规律

在规划自动收集窗口之前，我们需要先了解数据库中数据的更新规律。不同业务场景下，数据更新的频率和时间可能会有很大差异。

例如，一个线上交易系统，白天交易高峰期数据更新频繁，而晚上业务量很少，数据更新也相对较少。那么我们就可以把自动收集统计信息的窗口安排在晚上业务低谷期。

2.2 达梦数据库的自动收集配置

达梦数据库提供了任务调度功能来实现统计信息的自动收集。以下是一个示例，展示如何通过达梦数据库的管理工具创建一个每天凌晨 2 点执行统计信息收集的任务。

-- 创建一个作业
SP_CREATE_JOB('collect_stats_job', 1, 0, '', 0, 0, '');
-- 创建一个步骤，用来收集统计信息
SP_JOB_CONFIG_STEP('collect_stats_job', 1, 6, 'BEGIN 
    DBMS_STATS.GATHER_SCHEMA_STATS(
        ownname => ''your_schema_name'', 
        cascade => TRUE, 
        estimate_percent => 100, 
        method_opt => ''FOR ALL COLUMNS SIZE AUTO''
    );
END;', 0, 1, 0, 0, NULL);
-- 创建一个调度，每天凌晨 2 点执行
SP_JOB_CONFIG_SCHEDULE('collect_stats_job', 1, 1, 1, 0, '00:02:00', NULL, NULL, NULL, NULL);
-- 启动作业
SP_START_JOB('collect_stats_job');

注释：

  • SP_CREATE_JOB：创建一个作业，'collect_stats_job'是作业的名称。
  • SP_JOB_CONFIG_STEP：配置作业的步骤，这里使用DBMS_STATS.GATHER_SCHEMA_STATS来收集指定模式下的统计信息。ownname是模式名，cascade表示是否同时收集索引统计信息，estimate_percent表示采样百分比，method_opt表示统计信息收集的方法。
  • SP_JOB_CONFIG_SCHEDULE：配置作业的调度，这里设置每天凌晨 2 点执行。
  • SP_START_JOB：启动作业。

2.3 自动收集的优缺点

优点：

  • 提高效率：可以在业务低谷期自动收集统计信息，减少对业务的影响。
  • 保持统计信息的及时性：定期收集统计信息，能让优化器始终有最新的“地图”，提高查询性能。

缺点：

  • 可能不准确：如果数据更新非常频繁，自动收集的时间间隔可能无法及时反映数据的最新变化。
  • 资源占用：收集统计信息需要一定的系统资源，如果在不合适的时间收集，可能会影响业务的正常运行。

2.4 注意事项

  • 要根据业务的实际情况合理安排自动收集的时间间隔和采样百分比。
  • 在自动收集统计信息之前，最好先进行测试，确保不会对业务造成负面影响。

三、手动修正异常直方图

3.1 什么是直方图

直方图是达梦数据库中一种统计数据分布的方法。它可以帮助优化器更准确地估算数据的选择性，从而选择更优的执行计划。

例如，在orders表中，order_date字段的取值可能有一定的分布规律。如果我们知道大部分订单都集中在最近几个月，那么直方图就能反映这种分布情况，优化器在查询时就能更准确地估算需要扫描的数据量。

3.2 发现异常直方图

我们可以通过达梦数据库的系统视图来查看直方图的信息。以下是一个示例，查看orders表中order_date字段的直方图信息。

SELECT 
    table_name, 
    column_name, 
    histogram, 
    num_buckets 
FROM 
    SYSCOLUMNS 
WHERE 
    table_name = 'orders' 
    AND column_name = 'order_date';

注释：

  • SYSCOLUMNS是达梦数据库的系统视图，存储着列的相关信息。
  • histogram表示是否使用了直方图，num_buckets表示直方图的桶数。

如果发现直方图的桶数不合理，或者数据分布与实际情况不符，就说明可能存在异常直方图。

3.3 手动修正异常直方图

当发现异常直方图后，我们可以手动重新收集该列的统计信息，并指定合适的直方图参数。以下是一个示例：

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname => 'your_schema_name',
        tabname => 'orders',
        columns => 'order_date',
        cascade => TRUE,
        estimate_percent => 100,
        method_opt => 'FOR COLUMNS SIZE 254 order_date'
    );
END;

注释：

  • DBMS_STATS.GATHER_TABLE_STATS：收集指定表的统计信息。
  • columns指定需要收集统计信息的列。
  • method_opt中的SIZE 254表示为order_date列创建 254 个桶的直方图。

3.4 手动修正的优缺点

优点：

  • 针对性强：可以针对特定的列和表进行统计信息的修正，解决异常问题。
  • 灵活性高：可以根据实际情况调整直方图的参数，使统计信息更准确。

缺点：

  • 工作量大：需要手动分析和修正每个异常直方图，对于大型数据库来说，工作量可能会很大。
  • 容易出错：如果参数设置不当，可能会导致统计信息更加不准确。

3.5 注意事项

  • 在手动修正直方图之前，要充分了解数据的分布情况，合理设置直方图的桶数。
  • 手动修正后，要进行测试，确保查询性能得到了提升。

四、应用场景

这种方法适用于各种使用达梦数据库的场景，尤其是对查询性能要求较高的业务。

例如，在金融系统中，每天会有大量的交易数据产生，数据更新频繁。通过合理规划自动收集窗口和手动修正异常直方图，可以确保优化器始终选择最优执行计划，提高交易查询的响应速度。

在电商系统中，商品信息和订单信息会不断变化，统计信息的准确性对商品搜索和订单查询的性能影响很大。通过上述方法可以有效提升系统的性能。

五、文章总结

在使用达梦数据库时，统计信息收集时机不当会导致优化器选择非最优执行计划，影响查询性能。我们可以通过规划自动收集窗口和手动修正异常直方图来解决这个问题。

规划自动收集窗口需要了解数据的更新规律，合理安排收集时间和采样百分比。手动修正异常直方图需要先发现异常，然后根据数据分布情况合理设置直方图参数。

通过这些方法，可以让达梦数据库的统计信息更加准确，优化器选择更优的执行计划，从而提升数据库的查询性能。