深度对比：PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
数据库统计信息的作用
在数据库系统中，查询优化在决定应用程序性能方面起着至关重要的作用。 高效的查询依赖于最新的数据库统计信息，这些统计信息帮助数据库的查询优化器选择最佳的执行计划。在 PostgreSQL 和 MySQL 中，ANALYZE 命令是收集这些统计信息的关键工具，尤其是在表中的大量数据被修改时。
本文将深入探讨 PostgreSQL 如何管理统计信息更新，包括自动和手动更新，并将其与 SQL Server 在统计信息维护方面的方法进行比较。
PostgreSQL 中的统计信息维护方法
ANALYZE 命令用于收集关于表的字段数据分布的统计信息。这些统计信息，包括表行数和数值分布等信息，对于查询优化器至关重要。 通过了解数据布局，PostgreSQL 可以在执行查询时做出智能决策，选择如索引扫描、顺序扫描和连接方法等不同执行计划。 ANALYZE 命令可以手动执行，但 PostgreSQL 也会在其执行自动清理（autovacuum）过程中自动执行 ANALYZE 命令。 自动清理不仅管理 ANALYZE 过程，还会运行 VACUUM 来回收被删除或过时的数据行所占用的存储空间。
ANALYZE 命令自动执行时机
autovacuum 自动清理监控表的变化并在大量数据被修改时触发 ANALYZE 命令。 ANALYZE 命令的触发阈值使用以下公式计算（基于 postgresql.conf 中的配置值）：
例如，在一个包含 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 命令来完成，在某些情况下，如批量数据操作或索引创建后，用户可以立刻强制更新统计信息。 语法如下：
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 的自动化系统则是自适应的并且更加自主。对于数据库管理员来说，理解两者的区别可以帮助他们利用各自系统的优势，保持数据库的最佳性能。
文章转载自：桦仔
还未添加个人签名 2023-06-19 加入
还未添加个人简介
评论