将 SQL Server 的索引视图迁移到 Amazon Aurora PostgreSQL 或 A

将 SQL Server 索引视图迁移到 Amazon Aurora 中的物化视图

关键要点

在这篇文章中，我们讨论如何将 SQL Server 索引视图 迁移到 Amazon Aurora PostgreSQL 兼容版 或 Amazon RDS for PostgreSQL 中的 物化视图。通过这种迁移，可以显著提高查询性能。同时，要注意数据类型的兼容性、支持的聚合函数、存储空间要求及刷新时间等限制与注意事项。

这是两篇系列文章的第一篇。在本文中，我们将探讨如何将 SQL Server 索引视图 迁移到 物化视图 中，使用的是 Amazon Aurora PostgreSQL 兼容版 或 Amazon RDS for PostgreSQL。系列的第二篇文章将指导您如何将 SQL Server 索引视图迁移到 Babelfish for Aurora PostgreSQL 中。

SQL Server 索引视图用于提高从多个表中聚合数据的查询性能；而物化视图则在 PostgreSQL 数据库中用于存储查询结果，以便快速检索。两者的关键区别在于：物化视图使用与视图相同的规则系统，但在磁盘上以表的形式持久保存结果；而索引视图则仅存储表的定义，并在查询时直接读取这些表。

在复杂的索引视图上创建唯一簇集索引可以显著改善频繁查询的性能。索引视图像一个表一样存储结果集，允许查询从中受益，而无需直接访问视图。唯一簇集索引确保源表的更改会迅速反映到视图中。尽管可以通过索引视图直接修改数据，但通常不推荐这么做。此外，您还可以在视图上创建非簇集索引以进一步提升查询性能。与此不同的是，您可以直接在 PostgreSQL 的物化视图上创建数据库索引，从而提高访问该物化视图的查询性能。

解决方案概述

要将 SQL Server 索引视图迁移到 PostgreSQL 物化视图，请执行以下步骤：

分析 SQL Server 索引视图 了解索引视图在 SQL Server 中的结构和逻辑，包括涉及的基础表、联接和聚合。在 PostgreSQL 中创建物化视图 将 SQL Server 索引视图的查询转换为对应的 PostgreSQL 查询，并使用 CREATE MATERIALIZED VIEW 语句创建新的物化视图。确保列名、表名和联接条件符合 PostgreSQL 语法。在物化视图上定义索引 确定需要建立索引的相关列以提高查询性能。使用 CREATE INDEX 语句在物化视图上创建索引，考虑查询的具体要求。设置刷新机制 确定物化视图的更新方式。PostgreSQL 目前不支持物化视图的自动刷新，因此您必须定期使用 REFRESH MATERIALIZED VIEW 语句手动刷新该视图。可以设置计划使用 cron 作业或 pgcron 扩展或触发器来启动刷新过程。修改依赖查询 检查并修改依赖于 SQL Server 索引视图的查询或应用程序，以使用新的 PostgreSQL 物化视图。更新查询，以引用物化视图，并调整 SQL Server 和 PostgreSQL 之间的语法差异。测试与优化 彻底测试已迁移的物化视图及其依赖查询，以验证它们是否产生预期的结果。监控查询性能，并在必要时考虑额外的优化技术，例如更多的索引或查询重写。

以下图表概述了迁移过程中涉及的实体的关键步骤和决策。请遵循这一可视化流程，确保数据的完整性和 PostgreSQL 中物化视图的性能优势得以保留，借助预计算的数据、减少的行大小、预选的行以及可能较少的膨胀。

前提条件

完成以下前提步骤：

需求项目详情AWS 账户注册 AWS 账户SQL Server 数据库版本 20162022 企业版Aurora PostgreSQL 兼容版或者 Amazon RDS for PostgreSQLSQL 开发工具如 SQL Server Management StudioSSMS和 DBeaverSQL 经验对 SQL Server 和 PostgreSQL 的基本了解

分析 SQL Server 索引视图

第一步是分析 SQL Server 索引视图，识别涉及的表列和聚合函数。这将有助于创建等效的 PostgreSQL 视图。

作为示例，假设我们有一个产品库存表，并创建如下 SQL Server 索引视图：

sql 创建视图CREATE VIEW InventorySummaryView WITH SCHEMABINDING ASSELECT ProductID COUNTBIG() AS TotalCount SUM(Quantity) AS TotalQuantityFROM ProductionProductInventoryGROUP BY ProductID

在视图上创建聚集索引CREATE UNIQUE CLUSTERED INDEX IDXInventorySummaryView ON InventorySummaryView (ProductID)

通过以下查询在 SQL Server 中分析索引视图：

sql 查询以识别创建的视图 SELECT OBJECTNAME(objectid) AS viewname name AS indexname indexid typedescisunique dataspaceid ignoredupkey isprimarykeyisuniqueconstraint fillfactor ispadded isdisabledishypothetical allowrowlocks allowpagelockshasfilter filterdefinition compressiondelayFROM sysindexesWHERE objectproperty(objectidIsIndexed) = 1ORDER BY objectname(objectid) indexid

这将为您提供 SQL Server 数据库中的索引视图列表。

将 SQL Server 索引视图转换为 PostgreSQL 物化视图

下一步是通过以下步骤将 SQL Server 索引视图转换为 PostgreSQL 物化视图：

使用以下脚本提取 SQL Server 索引视图定义：

sqlSELECT OBJECTNAME(objectid) AS viewname definitionFROM syssqlmodulesWHERE OBJECTPROPERTY(objectid IsView) = 1

在 PostgreSQL 中使用结果集创建物化视图：

sqlCREATE MATERIALIZED VIEW InventorySummaryView ASSELECT ProductID COUNT() AS TotalCount SUM(Quantity) AS TotalQuantityFROM adventureworks2k7ProductionProductInventoryGROUP BY ProductID

使用以下语法索引物化视图以提高性能：

sqlCREATE UNIQUE INDEX IDXInventorySummaryView ON InventorySummaryView (ProductID)

在 PostgreSQL 中配置刷新或调度刷新窗口：

sqlALTER MATERIALIZED VIEW InventorySummaryView CLUSTER ON IDXInventorySummaryViewREFRESH MATERIALIZED VIEW CONCURRENTLY InventorySummaryView

清理

为避免产生持续费用，请清理以下资源：

删除提供的 Aurora PostgreSQL 兼容版或 Amazon RDS for PostgreSQL 资源。删除为 SQL 开发工具如 SSMS 和 DBeaver创建的 Amazon Elastic Compute Cloud 实例。

该解决方案的已知限制

数据类型 PostgreSQL 和 SQL Server 支持不同的数据类型，因此您可能需要修改 SQL Server 索引视图以保持与 PostgreSQL 的兼容性。 详细信息请参见 链接聚合 PostgreSQL 的物化视图 不支持 SQL Server 索引视图支持的所有聚合函数，因此您可能需要相应地调整查询。更新可用性 对基础表所做的更改不会在物化视图中自动反映，直到物化视图被刷新。存储空间 物化视图可能会占用额外的存储空间，确保您的 PostgreSQL 实例有足够的存储可用。刷新时间 根据物化视图的大小和基础查询的复杂性，刷新时间可能会有所不同。您可以监测基表的 “vacuumcount” 和 “vacuumcost” 来估算更新频率和潜在的刷新成本。此外，可用的 CPU、内存和磁盘 I/O 也可能影响刷新速度。升级 物化视图的定义和存储机制可能在 PostgreSQL 版本之间发生变化。如果您计划升级到新版本的 PostgreSQL，请确保您的物化视图与新版本兼容。使用 “pgupgradesupport” 函数或手动检查来识别潜在的兼容性问题。

使用 PostgreSQL 物化视图作为索引视图时的注意事项

考虑物化视图的以下方面非常重要。

一元机场.cn官网性能 虽然物化视图可以提高查询性能，但这会导致额外的存储空间需求和较慢的刷新时间。需要仔细评估保持物化视图与基础数据同步的优势和劣势，这需要定期刷新。对于大型数据集，这些刷新可能会很慢，可能在此过程中影响应用性能。查询优化 当物化视图用于提高常用查询的性能时，其效果最佳。物化视图通过预计算经常访问的数据来提高性能，减轻基础表的负载，提供优化的访问路径和缓存。然而，评估相关的权衡是至关重要的，例如额外的存储空间和刷新时间。数据刷新 必须定期刷新物化视图，以确保其反映基础表的最新数据。根据保持数据更新的要求，可以决定刷新频率。索引 可以在物化视图上建立索引以提高查询性能，但必须确保这些索引经过优化以符合您的用例。通过分析查询模式、选择在 WHERE 和 ORDER BY 子句中使用的相关列、索引类型选择，并检查索引利用率统计信息，可以实现最佳的物化视图索引。兼容性 在从 SQL Server 迁移之前，请确保您的应用程序与 PostgreSQL 物化视图的实现兼容。测试 在将 SQL Server 索引视图迁移到 PostgreSQL 的物化视图之前，必须彻底测试您的查询，以确保它们产生预期的结果并表现良好。

结论

本文展示了如何将 SQL Server 索引视图迁移到 Aurora PostgreSQL 或 Amazon RDS for PostgreSQL 中的物化视图，这可以显著提高应用程序的查询性能。然而，还有一些限制和注意事项，比如数据类型兼容性、聚合函数支持、事务控制、存储空间要求、刷新时间与查询优化。通过评估这些因素并测试查询，您可以实现成功迁移，并为应用程序提升性能。

欢迎在评论区留给我们您的反馈。

关于作者

Yugundhar Kalikapuram 是 AWS 专业服务的首席顾问，专注于将数据库迁移到 Amazon RDS。他拥有 15 年的经验，并对关系数据库管理系统和 NoSQL 技术充满热情。

Sawan Saxena 是 AWS 专业服务团队的首席顾问，主要帮助客户架构、现代化和迁移他们的数据库解决方案到 AWS。