深度对比：PostgreSQL 和 SQL Server 在统计信息维护中的关键差异

PgP1zBOe2k3

深度对比：PostgreSQL 和 SQL Server 在统计信息维护中的关键差异

 
数据库统计信息的作用

在数据库系统中，查询优化在决定应用程序性能方面起着至关重要的作用。 高效的查询依赖于最新的数据库统计信息，这些统计信息帮助数据库的查询优化器选择最佳的执行计划。在PostgreSQL和MySQL中，ANALYZE命令是收集这些统计信息的关键工具，尤其是在表中的大量数据被修改时。
本文将深入探讨PostgreSQL如何管理统计信息更新，包括自动和手动更新，并将其与SQL Server在统计信息维护方面的方法进行比较。
PostgreSQL中的统计信息维护方法

ANALYZE命令用于收集关于表的字段数据分布的统计信息。这些统计信息，包括表行数和数值分布等信息，对于查询优化器至关重要。 通过了解数据布局，PostgreSQL可以在执行查询时做出智能决策，选择如索引扫描、顺序扫描和连接方法等不同执行计划。 ANALYZE命令可以手动执行，但PostgreSQL也会在其执行自动清理（autovacuum）过程中自动执行ANALYZE命令。 自动清理不仅管理ANALYZE过程，还会运行VACUUM来回收被删除或过时的数据行所占用的存储空间。
ANALYZE命令自动执行时机

autovacuum自动清理监控表的变化并在大量数据被修改时触发ANALYZE命令。 ANALYZE命令的触发阈值使用以下公式计算（基于postgresql.conf中的配置值）：
分析阈值 = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * 表中的数据行数autovacuum_analyze_threshold：当表中的固定行数被修改时，会自动触发ANALYZE过程（默认值：50 行）。autovacuum_analyze_scale_factor：表中必须更改的行数比例才会自动触发ANALYZE过程（默认值：0.1或10%）。例如，在一个包含1万行数据的表中，默认设置意味着当约1050行数据被修改时，ANALYZE命令会自动开始运行（50 + 10000 * 0.1）。

根据这个算法，PostgreSQL目前对于超大型数据表会造成统计信息更新不及时的问题，到PostgreSQL 18版本都没有解决。
手动执行ANALYZE命令

尽管自动清理autovacuum最终会更新统计信息，但在某些情况下，手动执行ANALYZE命令可能会带来好处，例如：
1、添加新索引后：当创建新的索引时，运行ANALYZE命令会更新统计信息，确保查询优化器在执行计划中考虑到这个新建的索引。
2、大批量插入、更新或删除数据后：大量数据修改可能会导致查询优化器在统计信息过时的情况下暂时做出错误的决策。当在数据更改后立即运行ANALYZE命令可以确保PostgreSQL拥有当前表数据的最准确视图。
3、性能故障排除期间：如果查询变慢，运行ANALYZE命令来刷新统计信息通常可以帮助查询优化器使用最有效的执行计划。
 
SQL Server的统计信息维护方法

SQL Server使用更加友好的方式管理统计信息。与PostgreSQL类似，SQL Server为其查询优化器维护统计信息。然而，二者之间有一些关键的不同点：
1、自动创建统计信息 默认情况下，SQL Server会自动为涉及谓词、表联接和索引的字段创建统计信息。 例如，如果查询语句在没有索引的字段上进行过滤，SQL Server会自动生成该字段的统计信息，以便更好地为查询优化器提供详细信息。
 
2、自动更新统计信息 SQL Server还会根据表数据变化阈值自动更新统计信息。在SQL Server 2016版本之前，表数据的阈值通常为表中大约20%的行数。这个算法对于超大型数据表会造成统计信息更新不及时的问题，好在SQL Server 2016及之后采用了新的自适应阈值系统，表的数据量越多，阈值比例越低。
 
在SQL Server 2016之后，表行数大于500之后：MIN ( 500 + (0.20 * n), SQRT(1,000 * n) )，也即取原算法和1000倍的二次方根的最小值，作为触发阈值。根据上述算法，可以发现
（1）算法修改之前，触发统计信息自动更新的值，是随着表的行数的变化为一条严格的一次函数，旧的阈值触发公式：500 + (0.20 * n)
（2）算法修改之后，触发统计信息自动更新的值，对于这个新的计算公式MIN ( 500 + (0.20 * n), SQRT(1,000 * n) ，可以得知：在表的行数超过200W之后，会采用SQRT(1,000 * n)这一新的算法
（3）算法修改之后，统计信息自动更新阈值的算法SQRT(1,000 * n)是一条随着表的行数的变化，波动率较低的曲线，也就是意味着更倾向于在一个较小的变化之后触发统计信息自动更新。
（4）二次方根（SQRT）是一个非常有魔力的计算规则！！！

 
 
3、后台异步自动更新统计信息 SQL Server提供了异步自动更新统计信息的功能。这允许查询语句在可能是过时的统计信息的情况下运行，同时后台会异步自动更新统计信息。 在SQL Server 2014引入了并行更新统计信息功能并支持设置并行度，加快了更新统计信息的速度。 这与PostgreSQL不同，PostgreSQL目前暂不支持异步更新统计信息。 另外，PostgreSQL 17目前还没有像SQL Server 2016那样的新的自适应阈值算法，对于超大型数据表会造成统计信息更新不及时的问题。
 
统计信息的配置

SQL Server中有三个参数AUTO_CREATE_STATISTICS和AUTO_UPDATE_STATISTICS控制统计信息的自动维护。 对于需要频繁更新统计信息的表，还提供了一个名为AUTO_UPDATE_STATISTICS_ASYNC的设置，启用异步更新。

• AUTO_CREATE_STATISTICS：默认启用 (ON)该参数控制是否自动为没有统计信息的字段创建统计信息。如果没有为某个字段创建统计信息，数据库引擎将会自动创建一个。
  
• AUTO_UPDATE_STATISTICS：默认启用 (ON)该参数控制是否在数据发生变化后自动更新统计信息。当表中数据发生显著变化时，数据库引擎会自动更新相关列的统计信息。
  
• AUTO_UPDATE_STATISTICS_ASYNC：默认启用 (ON)该参数控制是否启用异步统计信息更新。默认情况下，统计信息更新是同步进行的，这意味着数据库引擎在查询执行时会等待统计信息更新完成。然而，启用异步更新之后，数据库引擎将会在后台更新统计信息。
  

此外，SQL Server还可以通过跟踪标志和数据库范围的配置项进行更加细粒度的控制， 使数据库管理员能够在每个数据库甚至查询语句级别上优化统计信息更新。
 
SQL Server中手动更新统计信息

SQL Server允许像PostgreSQL那样使用类似ANALYZE的命令来手动更新统计信息。 通常通过UPDATE STATISTICS命令来完成，在某些情况下，如批量数据操作或索引创建后，用户可以立刻强制更新统计信息。 语法如下：
UPDATE STATISTICS my_table(column_name); 
PostgreSQL和SQL Server统计信息维护的关键区别

1、统计信息创建
PostgreSQL不会自动为每个谓词字段创建统计信息。 SQL Server会自动为查询中使用到的字段自动生成统计信息，即使这些字段没有索引。
2、统计信息更新触发阈值
PostgreSQL的触发ANALYZE命令的阈值是可定制的，基于固定的行数和比例因子。 SQL Server的阈值触发是自适应的，取决于表的大小，即使是超大型数据表也会有更低的百分比阈值来触发自动更新。
3、统计信息异步更新
PostgreSQL不支持异步统计信息更新，这意味着查询始终使用最近收集到的统计信息。 SQL Server提供了统计信息异步自动更新功能，查询可以在统计信息过时的情况下继续执行，同时统计信息的更新会在后台自动异步运行。
4、手动执行统计信息更新命令
PostgreSQL使用 ANALYZE命令来更新统计信息， 而SQL Server使用UPDATE STATISTICS命令，并提供更多内建命令，例如sp_updatestats存储过程可以一次更新多个表或数据库的统计信息。



总结

在PostgreSQL和SQL Server中，保持最新的统计信息对优化查询性能至关重要。PostgreSQL的ANALYZE命令，无论是手动执行还是通过自动清理都确保了查询优化器拥有准确的统计数据，尽管它缺乏SQL Server的异步更新功能。SQL Server的自动创建和自适应更新机制提供了不同的方法，能够更精细地控制统计信息更新的时机和方式。
选择最佳方法取决于数据库和工作负载的具体需求。PostgreSQL的ANALYZE简单易用且高度可配置，而SQL Server的自动化系统则是自适应的并且更加自主。对于数据库管理员来说，理解两者的区别可以帮助他们利用各自系统的优势，保持数据库的最佳性能。
 

本文版权归作者所有，未经作者同意不得转载。