深度对比：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%）。
根据这个算法，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的自动化系统则是自适应的并且更加自主。对于数据库管理员来说，明白两者的区别可以资助他们利用各自系统的优势，保持数据库的最佳性能。
本文版权归作者所有，未经作者同意不得转载。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
