PostgreSQL和Oracle物化视图比较


对于最终用户来说，物化视图基本上只是一个表，物化视图只是将结果缓存在磁盘上，这样就不需要每次都运行底层查询。

您可以使用类似的方法为分析师设置一些历史销售数据，他们不需要实时信息，只需要最近 5 年的销售额。它可能会占用大量磁盘空间，但最终与对生产数据运行实时查询相比，这在服务器上会更容易。

以下几个是物化视图几个特点：

  • 查询优化 - 对于物化视图，由于数据是预先计算和存储的，优化器可以直接使用它，并且某些查询可以更快。
  • 写入操作可能会增加开销 - 物化视图通常是只读的，基于数据快照。因此，对于提交刷新，将基表的更改同步到 mview 通常比动态视图更复杂。但是，如果您不断调整物化视图，那么它可能不是达到此目的的最佳选择。
  • 存储空间可能会增加 - Mview 使用更多存储空间，因为它们保存了数据的优化副本。它会在刷新过程中产生开销并占用空间。从表中读取的视图不存在此问题。这没有好坏之分，而是根据您的用例进行的设计选择。你不会用划艇横渡大海，也不会用游艇穿过池塘。适合工作的工具等等。
  • 数据的一致性——视图是实时的，它们总是提供最新的数据。如果不定期刷新，Mview 可能会提供陈旧的数据。小心你所做的假设。Mviews 可能会以这种方式给你带来麻烦。
  • 索引注意事项 - 尽管视图来自的表可能具有可用的索引，但由于未存储数据，因此您无法在视图数据上创建索引。在物化视图上，如果您有特定的用例，您可以根据需要创建其他索引。预先计算的数据可能不够好的一个例子是，如果您在 where 子句中包含诸如函数之类的内容，则可以构建一个与该特定函数配合使用的索引。
  • 分区 - 您可以对物化视图进行分区。它们被存储。您无法对视图进行分区，因为相比之下它是短暂的。这对于非常大的数据库或使用一个或多个具有不同磁盘吞吐量和延迟的 SAN 的数据库来说可能很重要。回想一下，一些数据库是全球范围的。您可能在美国有分区，在中国有其他分区，而您在英国。
  • 设置/维护开销 - mview 更复杂。您必须管理刷新过程。您可能需要处理依赖项或复杂的依赖项链。您可能需要处理其他操作的时间安排。您可能正在等待传入的数据，或者您可能正在等待数据库中其他对象的计划。

PostgreSQL和Oracle物化视图比较
Postgres 物化视图几乎没有任何作用。
Oracle 物化视图可以做一些非常棒的事情：

  • 自动刷新：您不需要手动调用任何刷新函数（尽管您可以根据需要手动调用）。
    • 按计划刷新- Oracle 有一个内置的调度程序，并且物化视图与之集成，因此我可以说，当我创建 MV 时“每晚凌晨 2 点刷新”，它就可以正常工作。这不是一个巨大的卖点，但不需要一些外部调度程序（例如 cron），很方便。
    • 更新依赖项（基表）时刷新：假设我基于查询创建了一个 MV，例如 - - 现在，每当在或SELECT x, y, z FROM sales JOIN country ... WHERE country.id IN (...)上运行 INSERT/UPDATE/DELETE 时，MV 都会自动更新。无需在任何地方调用任何刷新函数。但这也很聪明 - 在这种情况下，因为我只关心特定的国家/地区列表（在我的 where 子句中），如果我更新具有country.i 的行不在该列表中，则不会触发 MV 上的刷新。所以基本上它只在必要时刷新。在复杂的用例中，您的里程可能会有所不同。salescountry
  • 查询重写又称为使用 MV 作为自动索引：这允许您创建一个 MV 作为索引。查询不需要知道 MV 的存在，但如果 MV 可以回答查询所询问的问题，查询优化器会自动将查询重新路由到 MV。示例：应用程序正在发出查询SELECT sum(qty*price) FROM sales，但它变得非常慢，因为每次运行时它都会重新计算数百万销售额的总数。我可以在该精确查询上创建一个 MV sales_mv，并且查询重写功能会自动将查询从sales带到sales_mv无需对查询或应用程序进行任何修改。重写甚至可以在许多 MV 查询不完全匹配的情况下发挥作用。例如，如果我将上述查询修改为 GROUP BY state，则 MV 现在具有每个州的每个 sum(qty*price)。如果我随后运行类似 的查询SELECT sum(qty*price) FROM sales WHERE state = 'CA'，则查询重写功能足够智能，可以知道 MV 是我请求的数据的超集，并且它知道可以使用 MV 的哪个子集来获取我想要的结果。
    • 我什至可以为 MV 提供维度元数据，以帮助查询优化器了解何时可以应用重写。如果我有相同的销售查询，但按月份细分 - 那么类似：SELECT sum(qty*price), month_num FROM sales group by month_num- 我可以创建一个维度，声明月份汇总为季度以及哪些季度。所以现在如果我询问数据库- 如果我正确定义了维度，查询优化器可以计算出 Q2 可以通过将 MV 中的 s 4、5 和 6 的总数相加来确定 - 与从细节级别向上求和相比，这是一个最小的工作量 - 它会以这种方式找到我的答案SELECT sum(qty*price) FROM sales where quarter='Q2'。month_num
  • “快速”刷新：（Oracle 称之为“快速”，尽管我更喜欢增量刷新这个术语。）假设销售 MV 刷新需要 30 秒，因为有大量数据。如果我们每分钟有大约一笔销售，我们就会花费大量时间和精力来反复刷新整个事情 - 我们每分钟花费 30 秒进行刷新。但想象一下，如果您作为一个人的工作是在纸上跟踪销售总额。一整天，销售人员都会对你大喊新的促销活动，“我们刚刚又促销了 100 美元！” - 您不会每次都根据整个历史记录重新计算整个销售总额；相反，您可以将当前总金额（假设为 10,000 美元）加上 100 美元，这样现在就是 10,100 美元。所以，这类似于“快速刷新”的工作原理 - 只需应用增量即可。此功能对于加快刷新速度非常重要。
    • 许多用例之一：多行约束：如果您曾经希望可以在表中的多行上应用一些验证，那么您也许可以使用物化视图来实现这一目标。由于您可以在 MV 上添加检查约束，因此您可以定义一个 MV 来运行某些聚合，并对该聚合的结果施加约束。例如，您有一个表示退休基金分配的表。每个客户的分配加起来必须达到 100%。因此，您可以在查询上创建一个 MV SELECT SUM(percent_allocated) AS total_allocated, customer_id FROM allocation_table GROUP BY customer_id- 添加一个检查约束，total_allocated 必须等于 100，这样就不会出现不良数据了allocation_table。现在，如果每次更新基表时都需要刷新完整的 MV 查询，这可能会太慢且笨重，但再次...快速刷新！
  • Oracle 还允许您选择推迟应用快速刷新的“增量”来读取物化视图的时间。因此，如果我不想减慢对基表的写入速度，那么通过当时处理 MV 刷新，我可以告诉 Oracle 自动对这些更改进行排队，并推迟将它们应用到物化视图，直到我实际从中读取数据。所以这是一个权衡 - 如果我有一个由 MV 支持的大型报告查询，用户可能不介意我添加它，比方说，在读取时平均增加半秒。这可能比每次增加几毫秒要好如果我对写入有严格的业务限制，则写入基础表。因此，这个功能允许我做到这一点，而且它很简单且具有声明性 - 输入一些神奇的单词，它就可以工作。
    • 推迟应用增量类比：继续以纸面总销售额进行类比：这就像在列出每个新销售的单独纸张（让我们称之为增量日志）上跟踪新的 100 美元销售。也许你太忙了，没有足够的精力去不断地进行加法运算。又出现了 100 美元的销售，因此您在增量日志上记下了“100 美元”。销售金额为 200 美元，您就写 200 美元。售价 1042.59 美元……等等。现在，您的经理问道：“嘿，我现在需要当前的总数。” 所以在这一点上，你会追上数学：你添加 10,000 + 100 + 200 + 1042.59 = 11,342.59，即新的运行总数。经理谢谢你，你扔掉三角洲纸并开始新的，重复。
    • 缓存连接：现在考虑将快速刷新与查询写入结合使用 - 这是一个非常强大的索引功能。您可以使用它来预先计算连接。（每个人都抱怨 SQL 中的 JOINS 缓慢而痛苦 - Oracle 实际上有多种预连接表的解决方案。MV 就是其中之一。）如果我有 10 亿个 WIDGET，每个 WIDGET 都是在 FACTORY 中创建的（通过 WIDGET.FACTORY_ID 链接），那么连接这两个表的查询可能会变得很慢。因此，我可以预先计算连接，并且查询重写可能能够将 MV 用于我的慢速查询，而无需任何人修改查询。每当更新/插入/删除小部件或工厂时，快速刷新仅应用增量，因此整个内容不会刷新。
    • 这里有很多潜在的用例。有时我会看到有人说Postgres有部分索引，而Oracle没有，那么Oracle不是很蹩脚吗？对于上下文，部分索引允许您使用 where 子句创建索引，以预先过滤掉您不关心查询范围的一堆数据。例如，CREATE INDEX my_index ON order(id, order_date, etc.) WHERE status = 'ACTIVE';现在，如果您在任何给定时间有 99% 的非活跃订单，则该索引可能可用于更快地搜索活跃订单。顺便说一句，PG 功能很棒，但是使用 Oracle，您可以通过创建 MV 来完成基本相同的事情：CREATE MATERIALIZED VIEW ... SELECT id, order_date, etc. WHERE status = 'ACTIVE'只需确保启用快速刷新和查询重写，这几乎是同一件事。（您甚至可以将 MV 定义为“索引组织”，使其物理形状像 B*Tree 索引，而不是堆表，使其更接近“同一件事”。）当然，您可能会在这个用例中争论，Oracle 解决方案更复杂，但重点是这个功能可以应用于大量用例 - 我可以一整天都想出一些用例。:)
  • 过时容忍：这与查询重写密切相关。如果我使用查询重写，我可以定义是否可以容忍过时。因此，想象一下我再次运行SELECT sum(qty*price) FROM sales，并且我已将其定义为按每晚计划刷新（更新依赖项时不会自动刷新）。这意味着在一天中的任何给定时刻，如果刷新后发生更新/插入/删除，MV 的内容可能会“过时”。因此，我可以根据每个会话（如果需要）决定是否希望在 MV 过时时进行查询重写以将我路由到 MV。如果我需要实时数据，请针对保证最新的销售表运行正常总和，即使它可能会更慢。如果昨晚的数据足够好，我声明可以容忍陈旧，并且我可以以这种方式使用 MV。
  • 同步物化视图组：基本上，您可以将许多物化视图分组和刷新在一起，以保证它们同步。考虑原子性。
  • 与分区/子分区配合良好。

Oracle 和 SQL Server 中的物化视图之间存在差异：

  • 在 Oracle 中，存储在磁盘上的视图会定期刷新，以提高运行缓慢的视图的性能。
  • 在 SQL Server 中，当向视图添加索引时，就会创建物化视图。SQL Server 将使 MV 保持最新状态，而无需安排定期刷新。在 SQLServer 中，物化视图可以被认为更像是由（不可见的）触发器维护的表。