利用自动物化视图优化 Amazon Redshift 查询性能

{"value":"#### **前言**\n\n\n\n**[Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail)** 是一个快速、完全托管式云原生数据仓库，可使通过标准 SQL 和商业智能工具对您的数据进行分析变得更具成本效益。[Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) 允许您使用亚马逊云科技设计的硬件和基于自动[机器学习](https://aws.amazon.com/cn/machine-learning/?trk=cndc-detail) (ML) 的调整，分析结构化和半结构化数据，无缝查询数据湖和运营数据库，以大规模提供超高性价比。\n\n\n\n虽然 [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) 提供开箱即用的**出色性价比**，但它提供了额外优化，可以提高这种性能，并允许您缩短数据仓库的查询响应时间。例如，您可以对数据模型中的表进行物理调整，以最大限度地减少集群中扫描和分发的数据量，从而加快表联接和范围限制扫描等操作。[Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) 现在使用**自动表优化 (ATO)** 功能，自动执行此调整。另一项缩短查询运行时间的优化是，以物化视图的形式预先计算查询结果。物化视图存储预先计算的查询结果，供将来类似查询使用。这提高了查询性能，因为可以跳过许多计算步骤并直接返回预先计算的结果。与简单缓存不同，在对基础（基）表应用 DML 更改时，可以增量刷新许多物化视图，并可供其他类似查询（而不仅仅是用于创建物化视图的查询）使用。\n\n\n\n[Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) 于2020年3月推出了物化视图。2020年6月，增加了对外部表的支持。在这些版本中，您可以在本地表和外部表上使用物化视图，通过在查询中使用预先计算的视图来提供低延迟性能。但是，这种方法要求您了解集群上有哪些物化视图可用，及其是否为最新视图。\n\n\n\n2020年11月，增加了物化视图自动刷新和查询重写功能。借助物化视图感知自动重写，数据分析师无需直接查询物化视图，即可获得查询和仪表板物化视图这样的好处，分析师甚至可能不知道物化视图的存在。自动重写功能通过将查询重写为使用物化视图，而查询无需显式引用物化视图，来实现此目的。此外，当基表数据发生更改时，自动刷新会使物化视图保持最新状态，并且存在用于维护物化视图的可用集群资源。\n\n\n\n但是，物化视图仍需由数据工程师或数据库管理员手动创建、监控和维护。为了减少这种开销，[Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) 推出了 **Automated Materialized View** (AutoMV) 功能，它更进一步，自动为具有常见循环联接和聚合的查询创建物化视图。\n\n\n\n本文将解释物化视图的概念、手动物化视图的工作原理及其带来的好处，以及构建和维护手动物化视图以实现性能改进和优化所需的条件。然后，我们将解释如何通过全新自动物化视图功能大大简化此操作。\n\n\n\n- [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) ：\n[https://aws.amazon.com/cn/redshift/](https://aws.amazon.com/cn/redshift/)\n\n- 出色性价比：\n[https://aws.amazon.com/cn/blogs/big-data/amazon-redshift-continues-its-price-performance-leadership/](https://aws.amazon.com/cn/blogs/big-data/amazon-redshift-continues-its-price-performance-leadership/)\n\n- 自动表优化 (ATO) ：\n[https://aws.amazon.com/cn/blogs/big-data/automate-your-amazon-redshift-performance-tuning-with-automatic-table-optimization/](https://aws.amazon.com/cn/blogs/big-data/automate-your-amazon-redshift-performance-tuning-with-automatic-table-optimization/)\n\n- Automated Materialized View：\n\n[https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-auto-mv.html](https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-auto-mv.html)\n\n#### **手动创建物化视图**\n\n物化视图是一个数据库对象，它将预先计算的查询结果存储在物化（持久化）数据集中。类似的查询可以使用物化视图中预先计算的结果，并跳过读取基础表以及执行联接和聚合等代价高昂的任务，从而提高查询性能。\n\n\n\n例如，您可以通过将仪表板的查询结果物化为一个或多个物化视图，来提高仪表板的性能。当仪表板打开或刷新时，它可以使用物化视图中的预计算结果，而不必重读基表和重新处理查询。通过创建一次物化视图并对其进行多次查询，可以避免冗余处理，从而提高查询性能，并释放资源，将其用于数据库的其他处理。\n\n\n\n为了演示这一点，我们使用以下查询，它返回每日订单和销售数字。它联接两个表并在日级别进行聚合。\n\n\n```\\nSET enable_result_cache_for_session TO OFF;\\n\\nSELECT o.o_orderdate AS order_date\\n,SUM(l.l_extendedprice) AS ext_price_total\\nFROM orders o\\nINNER JOIN lineitem l\\nON o.o_orderkey = l.l_orderkey\\nWHERE o.o_orderdate >= '1997-01-01'\\nAND o.o_orderdate < '1998-01-01'\\nGROUP BY o.o_orderdate\\nORDER BY 1;\\n```\n\n在查询的顶部，我们将 enable_result_cache_for_session 设置为 OFF。此设置会禁用结果缓存，因此，我们可以在每次运行查询时看到完整的处理运行时间。与物化视图不同，结果缓存是一种简单的缓存，它将单个查询的结果存储在内存中，它不能供其他类似的查询使用，在修改基表时不会更新，并且由于它不是持久化缓存，可能会被更频繁使用的查询耗尽内存。\n\n\n\n当我们在带有 **TPC-H 3 TB** ([https://github.com/awslabs/amazon-redshift-utils/tree/master/src/CloudDataWarehouseBenchmark/Cloud-DWB-Derived-from-TPCH](https://github.com/awslabs/amazon-redshift-utils/tree/master/src/CloudDataWarehouseBenchmark/Cloud-DWB-Derived-from-TPCH)) 数据集的 10 节点 ra3.4xl 集群上运行此查询时，它将在大约 20 秒后返回。如果我们需要多次运行此查询或类似查询，可以使用 **CREATE MATERIALIZED VIEW** ([https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-create-sql-command.html](https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-create-sql-command.html)) 命令创建物化视图，并直接查询物化视图对象，其结构与表相同：\n\n```\\nCREATE MATERIALIZED VIEW mv_daily_sales\\nAS\\nSELECT o.o_orderdate AS order_date\\n,SUM(l.l_extendedprice) AS ext_price_total\\nFROM orders o\\nINNER JOIN lineitem l\\nON o.o_orderkey = l.l_orderkey\\nWHERE o.o_orderdate >= '1997-01-01'\\nAND o.o_orderdate < '1998-01-01'\\nGROUP BY o.o_orderdate;\\n\\nSELECT order_date\\n,ext_price_total\\nFROM mv_daily_sales\\nORDER BY 1;\\n```\n\n由于联接和聚合已经过预先计算，因此，运行时间约为900毫秒，性能提高了96%。\n\n\n\n如上所述，您可以直接查询物化视图；但是，[Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) 可以自动重写查询以使用一个或多个物化视图。查询重写功能在运行查询时透明地重写查询，以便从物化视图中检索预先计算的结果。此过程在符合条件且最新的物化视图上自动触发，但前提是该查询包含相同的基表和联接，并具有相似的聚合作为物化视图。\n\n\n\n- 查询重写：\n[https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-auto-rewrite.html](https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-auto-rewrite.html)\n\n- 符合条件：\n[https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-auto-rewrite.html#mv_auto-rewrite_limitations](https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-auto-rewrite.html#mv_auto-rewrite_limitations)\n\n\n\n例如，如果我们重新运行销售查询，因为它符合重写条件，会自动重写以使用 mv_daily_sales 物化视图。我们从最初的查询开始：\n\n```\\nSELECT o.o_orderdate AS order_date\\n,SUM(l.l_extendedprice) AS ext_price_total\\nFROM orders o\\nINNER JOIN lineitem l\\nON o.o_orderkey = l.l_orderkey\\nWHERE o.o_orderdate >= '1997-01-01'\\nAND o.o_orderdate < '1998-01-01'\\nGROUP BY o.o_orderdate\\nORDER BY 1;\\n```\n\n\n\n\n\n在内部，查询经过重写，变为以下 SQL 并运行。此过程对用户完全透明。\n\n\n\n```\\nSELECT order_date\\n,ext_price_total\\nFROM mv_daily_sales\\nORDER BY 1;\\n```\n\n\n重写可以通过查看查询的解释计划来确认：\n\n\n\n```\\nEXPLAIN SELECT o.o_orderdate AS order_date\\n,SUM(l.l_extendedprice) AS ext_price_total\\nFROM orders o\\nINNER JOIN lineitem l\\nON o.o_orderkey = l.l_orderkey\\nWHERE o.o_orderdate >= '1997-01-01'\\nAND o.o_orderdate < '1998-01-01'\\nGROUP BY o.o_orderdate;\\n\\n+------------------------------------------------------------------------------------------------+\\n|QUERY PLAN |\\n+------------------------------------------------------------------------------------------------+\\n|XN HashAggregate (cost=5.47..5.97 rows=200 width=31) |\\n| -> XN Seq Scan on mv_tbl__mv_daily_sales__0 derived_table1 (cost=0.00..3.65 rows=365 width=31)|\\n+------------------------------------------------------------------------------------------------+\\n```\n\n\n\n\n计划显示查询已经过重写，并已从 mv_daily_sales 物化视图中检索结果，而不是查询的基表：orders 和 lineitem。\n\n\n\n其他使用相同基表和聚合级别的查询，或者使用从物化视图级别派生的聚合级别的查询也会经过重写。例如：\n\n\n\n```\\nEXPLAIN SELECT date_trunc('month', o.o_orderdate) AS order_month\\n,SUM(l.l_extendedprice) AS ext_price_total\\nFROM orders o\\nINNER JOIN lineitem l\\nON o.o_orderkey = l.l_orderkey\\nWHERE o.o_orderdate >= '1997-01-01'\\nAND o.o_orderdate < '1998-01-01'\\nGROUP BY order_month;\\n\\n+------------------------------------------------------------------------------------------------+\\n|QUERY PLAN |\\n+------------------------------------------------------------------------------------------------+\\n|XN HashAggregate (cost=7.30..10.04 rows=365 width=19) |\\n| -> XN Seq Scan on mv_tbl__mv_daily_sales__0 derived_table1 (cost=0.00..5.47 rows=365 width=19)|\\n+------------------------------------------------------------------------------------------------+\\n```\n\n\n\n\n如果 orders 或 lineitem 表中的数据发生更改，则 mv_daily_sales 会变成 stale；这意味着物化视图不反映其基表的状态。如果我们更新 lineitem 中的一行并检查 stv_mv_info 系统表，则可以看到 is_stale 标记设置为 t (true)：\n\n\n\n```\\nUPDATE lineitem\\nSET l_extendedprice = 5000\\nWHERE l_orderkey = 2362252519\\nAND l_linenumber = 1;\\n\\nSELECT name\\n,is_stale\\nFROM stv_mv_info\\nWHERE name = 'mv_daily_sales';\\n\\n+--------------+--------+\\n|name |is_stale|\\n+--------------+--------+\\n|mv_daily_sales|t |\\n+--------------+--------+\\n```\n\n\n\n\n- stv_mv_info：\n[https://docs.aws.amazon.com/redshift/latest/dg/r_STV_MV_INFO.html](https://docs.aws.amazon.com/redshift/latest/dg/r_STV_MV_INFO.html)\n\n\n\n我们现在可以使用 **REFRESH MATERIALIZED VIEW** 语句手动刷新物化视图：\n\n\n\n```\\nREFRESH MATERIALIZED VIEW mv_daily_sales;\\n\\nSELECT name\\n,is_stale\\nFROM stv_mv_info\\nWHERE name = 'mv_daily_sales';\\n\\n+--------------+--------+\\n|name |is_stale|\\n+--------------+--------+\\n|mv_daily_sales|f |\\n+--------------+--------+\\n```\n\n\n- REFRESH MATERIALIZED VIEW：\n[https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-refresh-sql-command.html](https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-refresh-sql-command.html)\n\n\n\n物化视图刷新有两种类型：完全刷新和增量刷新。完全刷新会重新运行基础 SQL 语句并重新构建整个物化视图。增量刷新仅更新受源数据更改影响的特定行。要查看物化视图是否符合增量刷新的条件，请查看 state 列（位于 stv_mv_info 系统表中）。状态0表示物化视图将完全刷新，状态 1 表示物化视图将以增量方式刷新。\n\n\n\n```\\nSELECT name\\n,state\\nFROM stv_mv_info\\nWHERE name = 'mv_daily_sales';\\n\\n+--------------+--------+\\n|name |state |\\n+--------------+--------+\\n|mv_daily_sales| 1|\\n+--------------+--------+\\n```\n\n\n\n如果您需要按固定的时间段（如每小时一次）刷新物化视图，则可以在 [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) 控制台上安排手动刷新。有关更多信息，请参阅在 [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) 控制台上安排查询([https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor-schedule-query.html](https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor-schedule-query.html))。\n\n\n\n除了能够进行手动刷新外，[Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) 还可以自动刷新物化视图。自动刷新功能可智能地确定何时刷新物化视图，以及是否有多个物化视图，以何种顺序刷新。[Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) 考虑了刷新物化视图的好处（物化视图的使用频率、物化视图实现的性能提升）和成本（刷新所需的资源、当前系统负载、可用的系统资源）。\n\n\n\n这种智能刷新有很多好处。由于并非所有物化视图都同样重要，因此，决定何时以及以何种顺序刷新大型系统上的物化视图对于数据库管理员来说，是一项需要完成的复杂任务。此外，数据库管理员需要考虑系统上运行的其他工作负载，并尽量确保关键工作负载的延迟不会因刷新物化视图的影响而增加。借助自动刷新功能，数据库管理员无需执行这些困难而耗时的任务。\n\n\n\n您可以在 CREATE MATERIALIZED VIEW 语句中通过 AUTO REFRESH YES 参数将物化视图设置为自动刷新：\n\n\n\n```\\nCREATE MATERIALIZED VIEW mv_daily_sales\\nAUTO REFRESH YES\\nAS\\nSELECT ...\\n```\n\n\n\n\n\n现在，当物化视图的源数据发生更改时，物化视图将自动刷新。我们可以在 svl_mv_refresh_status\n([https://docs.aws.amazon.com/redshift/latest/dg/r_SVL_MV_REFRESH_STATUS.html](https://docs.aws.amazon.com/redshift/latest/dg/r_SVL_MV_REFRESH_STATUS.html)) 系统表中查看刷新的状态。例如：\n\n\n\n```\\nUPDATE lineitem\\nSET l_extendedprice = 6000\\nWHERE l_orderkey = 2362252519\\nAND l_linenumber = 1;\\n\\nSELECT mv_name\\n,starttime\\n,endtime\\n,status\\n,refresh_type\\nFROM svl_mv_refresh_status\\nWHERE mv_name = 'mv_daily_sales';\\n\\n+--------------+--------------------------+--------------------------+---------------------------------------------+------------+\\n|mv_name |starttime |endtime |status |refresh_type|\\n+--------------+--------------------------+--------------------------+---------------------------------------------+------------+\\n|mv_daily_sales|2022-05-06 14:07:24.857074|2022-05-06 14:07:33.342346|Refresh successfully updated MV incrementally|Auto |\\n+--------------+--------------------------+--------------------------+---------------------------------------------+------------+\\n```\n\n\n要移除物化视图，我们使用 DROP MATERIALIZED VIEW([https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-drop-sql-command.html](https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-drop-sql-command.html)) 命令：\n\n\n\n```\\nDROP MATERIALIZED VIEW mv_daily_sales;\\n```\n\n\n\n\n\n\n\n现在，您已经了解了物化视图的概念、其优势及其创建、使用和移除方法，接下来，我们来讨论一下其缺点。要设计和实施一组物化视图以帮助提高数据库的整体查询性能，需要熟练的资源来执行多项复杂且耗时的任务：\n\n\n\n- 分析系统上运行的查询\n- 确定哪些查询定期运行并提供业务优势\n- 确定已识别查询的优先级\n- 确定性能改进是否值得创建物化视图并存储数据集\n- 以物理方式创建和刷新物化视图\n- 监控物化视图的使用情况\n- 删除很少使用或从未使用或者由于基表结构更改而无法刷新的物化视图\n\n\n\n设计和创建具有整体优势的物化视图需要具备重要技能，投入大量精力和时间。此外，还需要进行持续监控，以查明设计不当或未充分利用的物化视图，因为这些视图占用了资源却没有带来收益。\n\n\n\n[Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) 目前提供 **Automated Materialized**Views([https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-auto-mv.html](https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-auto-mv.html)) (AutoMVs) 功能来自动执行此过程。在以下部分中，我们将介绍 AutoMV 的工作原理以及如何在集群上使用它们。\n\n\n#### **自动创建物化视图**\n\n在 [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) 集群上启用 AutoMV 功能（默认情况下处于启用状态）时，[Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) 会监控最近运行的查询，并识别任何可通过物化视图提高其性能的查询。然后，从主查询和任何子查询中提取查询中昂贵的部分，例如，可以持久保留到物化视图中并供将来查询重用的聚合和联接。随后，将提取的查询部分重写为 create materialized view 语句（候选物化视图）并存储以供进一步处理。\n\n\n\n候选物化视图不仅仅是查询的一对一副本；还需要进行额外的处理来创建通用物化视图，以供类似于原始查询的查询使用。在以下示例中，结果集受筛选器 o_orderpriority = ‘1-URGENT’ 和 l_shipmode =’AIR’ 的限制。因此，根据此结果集构建的物化视图只能提供选择该范围内有限数据的查询。\n\n\n\n```\\nSELECT o.o_orderdate\\n,SUM(l.l_extendedprice)\\nFROM orders o\\nINNER JOIN lineitem l\\nON o.o_orderkey = l.l_orderkey\\nWHERE o.o_orderpriority = '1-URGENT'\\nAND l.l_shipmode ='AIR'\\nGROUP BY o.o_orderdate;\\n```\n\n\n\n\n[Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) 使用多种技术来创建通用物化视图；其中一种技术称为谓词提升。要将谓词提升应用于此查询，筛选的列 o_orderpriority 和 l_shipmode 将被移到 GROUP BY 子句中，从而存储物化视图中的全部数据，允许类似查询使用相同的物化视图。这种方法由类似仪表板的工作负载驱动，此类工作负载通常使用不同的筛选条件发出相同的查询。\n\n\n\n```\\nSELECT o.o_orderdate\\n,o.o_orderpriority\\n,l.l_shipmode\\n,SUM(l.l_extendedprice)\\nFROM orders o\\nINNER JOIN lineitem l\\nON o.o_orderkey = l.l_orderkey\\nGROUP BY o.o_orderdate\\n,o.o_orderpriority\\n,l.l_shipmode;\\n```\n\n\n\n在下一个处理步骤中，将应用 ML 算法来计算哪些候选物化视图提供最佳性能优势和系统范围的性能优化。这些算法遵循与前面提到的自动刷新功能类似的逻辑。对于每个候选物化视图，[Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) 会计算出一项优势，其与物化视图在工作负载中实现和使用时的预期性能提升相对应。此外，它还计算与创建和维护候选视图所需的系统资源相对应的成本。而且会考虑现有的手动物化视图；如果已存在覆盖相同范围的手动物化视图，并且手动物化视图的自动刷新优先级高于 AutoMV，则不会创建 AutoMV。\n\n\n\n然后，按总体成本效益顺序对物化视图列表进行排序，考虑到工作负载管理 (WLM) 查询优先级，与优先级较高的队列中的查询相关的物化视图排序在与低优先级队列上的查询相关的物化视图之前。物化视图列表完全排序后，系统会自动创建这些视图，并按优先级顺序在后台填充。\n\n\n\n接着，创建的 AutoMV 将由后台进程进行监控，该进程会检查其活动，例如其查询和刷新的频率。如果该进程确定某个 AutoMV 未被使用或刷新（例如由于基表的结构发生变化），则会将其删除。\n\n\n\n- 工作负载管理：\n[https://docs.aws.amazon.com/redshift/latest/dg/cm-c-implementing-workload-management.html](https://docs.aws.amazon.com/redshift/latest/dg/cm-c-implementing-workload-management.html)\n\n- 查询优先级：\n[https://docs.aws.amazon.com/redshift/latest/dg/query-priority.html](https://docs.aws.amazon.com/redshift/latest/dg/query-priority.html)\n\n\n\n**示例**\n\n\n\n为了演示此过程的实际应用，我们使用以下取自 3 TB Cloud DW Benchmark\n([https://aws.amazon.com/cn/blogs/china/big-data_optimize-your-amazon-redshift-query-performance-with-automated-materialized-views/](https://aws.amazon.com/cn/blogs/china/big-data_optimize-your-amazon-redshift-query-performance-with-automated-materialized-views/)) 的查询，这是源自 TPC-H 的性能测试基准。您可以将基准测试数据加载到集群中，然后按照示例进行操作。\n\n\n\n```\\nSET enable_result_cache_for_session TO OFF;\\n\\nSELECT /* TPC-H Q12 */\\nl_shipmode\\n, SUM(CASE\\nWHEN o_orderpriority = '1-URGENT'\\nOR o_orderpriority = '2-HIGH'\\nTHEN 1\\nELSE 0\\nEND) AS high_line_count\\n, SUM(CASE\\nWHEN o_orderpriority '1-URGENT'\\nAND o_orderpriority '2-HIGH'\\nTHEN 1\\nELSE 0\\nEND) AS low_line_count\\nFROM orders\\n, lineitem\\nWHERE o_orderkey = l_orderkey\\nAND l_shipmode IN ('MAIL', 'SHIP')\\nAND l_commitdate < l_receiptdate\\nAND l_shipdate = DATE '1994-01-01'\\nAND l_receiptdate < DATEADD(YEAR, 1, CAST('1994-01-01' AS DATE))\\nGROUP BY l_shipmode\\nORDER BY l_shipmode;\\n```\n\n\n\n\n我们运行了三次查询，然后等待 30 分钟。在 10 节点 ra3.4xl 集群上，查询大约在 8 秒钟内运行。\n\n\n\n在 30 分钟内，[Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) 评估物化候选 AutoMV 的好处。它计算候选物化视图的排序列表，并在启用增量刷新、自动刷新和查询重写的情况下创建最有益的视图。当查询或类似查询运行时，它们会自动透明地重写，以使用一个或多个创建的 AutoMV。\n\n\n\n正在进行，如果基表中的数据经过修改（即 AutoMV 变为陈旧），则会自动运行增量刷新，在 AutoMV 中插入、更新和删除行，使其数据恢复到最新状态。\n\n\n\n重新运行该查询显示它在大约 800 毫秒内运行，性能提高了 90%。我们可以通过查看解释计划来确认查询是否使用了 AutoMV：\n\n\n\n```\\nEXPLAIN SELECT /* TPC-H Q12 */\\nl_shipmode\\n,\\nSUM(CASE\\nWHEN o_orderpriority = '1-URGENT'\\nOR o_orderpriority = '2-HIGH'\\nTHEN 1\\nELSE 0\\nEND) AS high_line_count\\n, SUM(CASE\\nWHEN o_orderpriority <> '1-URGENT'\\nAND o_orderpriority <> '2-HIGH'\\nTHEN 1\\nELSE 0\\nEND) AS low_line_count\\nFROM orders\\n, lineitem\\nWHERE o_orderkey = l_orderkey\\nAND l_shipmode IN ('MAIL', 'SHIP')\\nAND l_commitdate < l_receiptdate\\nAND l_shipdate < l_commitdate\\nAND l_receiptdate >= DATE '1994-01-01'\\nAND l_receiptdate < DATEADD(YEAR, 1, CAST('1994-01-01' AS DATE))\\nGROUP BY l_shipmode\\nORDER BY l_shipmode;\\n\\n+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+\\n|QUERY PLAN |\\n+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+\\n|XN Merge (cost=1000000000354.23..1000000000354.23 rows=1 width=30) |\\n| Merge Key: derived_table1.grvar_1 |\\n| -> XN Network (cost=1000000000354.23..1000000000354.23 rows=1 width=30) |\\n| Send to leader |\\n| -> XN Sort (cost=1000000000354.23..1000000000354.23 rows=1 width=30) |\\n| Sort Key: derived_table1.grvar_1 |\\n| -> XN HashAggregate (cost=354.21..354.22 rows=1 width=30) |\\n| -> XN Seq Scan on mv_tbl__auto_mv_2000__0 derived_table1 (cost=0.00..349.12 rows=679 width=30) |\\n| Filter: ((grvar_2 < '1995-01-01'::date) AND (grvar_2 >= '1994-01-01'::date) AND ((grvar_1 = 'SHIP'::bpchar) OR (grvar_1 = 'MAIL'::bpchar)))|\\n+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+\\n```\n\n\n\n为了演示 AutoMV 如何提高类似查询的性能，我们更改了原始查询的某些筛选器。在以下示例中，我们将 l_shipmode 的筛选器从 IN (‘MAIL’, ‘SHIP’) 更改为 IN (‘TRUCK’, ‘RAIL’, ‘AIR’)，将 l_receiptdate 的筛选器更改为上一年的前 6 个月。查询将在大约 900 毫秒内运行，从解释计划来看，我们确认它使用的是 AutoMV：\n\n\n\n```\\nEXPLAIN SELECT /* TPC-H Q12 modified */\\nl_shipmode\\n, SUM(CASE\\nWHEN o_orderpriority = '1-URGENT'\\nOR o_orderpriority = '2-HIGH'\\nTHEN 1\\nELSE 0\\nEND) AS high_line_count\\n, SUM(CASE\\nWHEN o_orderpriority <> '1-URGENT'\\nAND o_orderpriority <> '2-HIGH'\\nTHEN 1\\nELSE 0\\nEND) AS low_line_count\\nFROM orders\\n, lineitem\\nWHERE o_orderkey = l_orderkey\\nAND l_shipmode IN ('TRUCK', 'RAIL', 'AIR')\\nAND l_commitdate < l_receiptdate\\nAND l_shipdate < l_commitdate\\nAND l_receiptdate >= DATE '1993-01-01'\\nAND l_receiptdate < DATE '1993-07-01'\\nGROUP BY l_shipmode\\nORDER BY l_shipmode;\\n\\n+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\\n|QUERY PLAN |\\n+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\\n|XN Merge (cost=1000000000396.30..1000000000396.31 rows=1 width=30) |\\n| Merge Key: derived_table1.grvar_1 |\\n| -> XN Network (cost=1000000000396.30..1000000000396.31 rows=1 width=30) |\\n| Send to leader |\\n| -> XN Sort (cost=1000000000396.30..1000000000396.31 rows=1 width=30) |\\n| Sort Key: derived_table1.grvar_1 |\\n| -> XN HashAggregate (cost=396.29..396.29 rows=1 width=30) |\\n| -> XN Seq Scan on mv_tbl__auto_mv_2000__0 derived_table1 (cost=0.00..392.76 rows=470 width=30) |\\n| Filter: ((grvar_2 < '1993-07-01'::date) AND (grvar_2 >= '1993-01-01'::date) AND ((grvar_1 = 'AIR'::bpchar) OR (grvar_1 = 'RAIL'::bpchar) OR (grvar_1 = 'TRUCK'::bpchar)))|\\n+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\\n```\n\n\n\nAutoMV 功能对用户透明，完全由系统管理。因此，与手动物化视图不同，AutoMV 对用户不可见，也无法直接查询。它们也不会显示在任何系统表中，例如 stv_mv_info 或 svl_mv_refresh_status。\n\n\n\n最后，如果 AutoMV 已有一段时间没有被工作负载使用，则系统会自动将其删除，以释放存储空间。当我们在此之后重新运行查询时，运行时会恢复到原来的 8 秒，因为查询现在使用的是基表。这可以通过检查解释计划来确认。\n\n\n\n此示例说明了 AutoMV 功能减少创建和维护物化视图所需的工作量和时间。\n\n\n#### **性能测试和结果**\n\n为了了解 AutoMV 在实践中的运作情况，我们使用 1 TB 和 3 TB 版本的源自 TPC-H 的 Cloud DW Benchmark(https://github.com/awslabs/amazon-redshift-utils/tree/master/src/CloudDataWarehouseBenchmark/Cloud-DWB-Derived-from-TPCH) 来运行测试。此测试由一个具有 22 个查询的 power run 脚本组成，该脚本在结果缓存关闭的情况下运行三次。这些测试在两个不同的集群上运行：并发度为 1 和 5 的 4 节点 ra3.4xlarge 和 2 节点 ra3.16xlarge。\n\n\n\nCloud DW Benchmark 源自 TPC-H 基准测试。它无法与公布的 TPC-H 结果进行比较，因为我们的测试结果并不完全符合规范。\n\n\n\n下表显示了我们的结果：\n\n\n\nAutoMV 功能无需任何人工干预，即可将查询性能提高多达 19%。\n\n\n#### **总结**\n\n\n\n在本文中，我们首先介绍了手动物化视图、其各种功能及其利用方式。然后，我们研究了设计、创建和维护物化视图以提高数据仓库性能所需的工作量和时间。\n\n\n\n接下来，我们讨论了 AutoMV 如何帮助克服这些挑战，并无缝提高 SQL 查询和仪表板的性能。我们更深入地探讨了 AutoMV 的工作原理，并讨论了 ML 算法如何根据预测的性能改进以及与创建和维护视图所需的成本相比其总体收益来确定要创建的物化视图。然后，我们介绍了一些内部处理逻辑，如谓词提升如何创建通用物化视图，以供一系列查询使用，而不仅仅是触发创建物化视图的原始查询。\n\n\n\n最后，我们展示了基于行业基准的性能测试结果，其中 AutoMV 功能将性能提高了多达 19％。\n\n\n\n正如我们演示的那样，自动物化视图可以提高数据仓库的性能，而无需任何手动操作或专业知识。它们在后台透明地运行，以优化您的工作负载性能，并在工作负载变化时自动进行调整。\n\n\n\n默认情况下，自动物化视图处于启用状态。我们建议您监控其在您当前集群上的任何性能改进。如果您首次接触 [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail)，可以参阅入门教程，并通过免费试用，创建和预置您的第一个集群并试用该功能。\n\n\n\n- 入门教程：\n[https://docs.aws.amazon.com/redshift/latest/gsg/getting-started.html](https://docs.aws.amazon.com/redshift/latest/gsg/getting-started.html)\n\n- 免费试用：\n[https://aws.amazon.com/cn/redshift/free-trial/](https://aws.amazon.com/cn/redshift/free-trial/)\n\n\n**本篇作者**\n\n**Adam Gatt**\n\n亚马逊云科技分析高级专家解决方案架构师。他在数据和数据仓库方面拥有 20 多年的经验，帮助客户在云中构建强大、可扩展的高性能分析解决方案。\n\n**Rahul Chaturvedi**\n\n亚马逊云科技分析专家解决方案架构师。在担任此职位之前，他曾在 Amazon 广告和 Prime Video 担任数据工程师，在此期间，他帮助构建了 PB 级数据湖，用于自助式分析。","render":"<h4><a id=\\"_0\\"></a><strong>前言</strong></h4>\\n<p><strong>Amazon Redshift</strong> 是一个快速、完全托管式云原生数据仓库，可使通过标准 SQL 和商业智能工具对您的数据进行分析变得更具成本效益。[Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) 允许您使用亚马逊云科技设计的硬件和基于自动[机器学习](https://aws.amazon.com/cn/machine-learning/?trk=cndc-detail) (ML) 的调整，分析结构化和半结构化数据，无缝查询数据湖和运营数据库，以大规模提供超高性价比。</p>\\n<p>虽然 Amazon Redshift 提供开箱即用的<strong>出色性价比</strong>，但它提供了额外优化，可以提高这种性能，并允许您缩短数据仓库的查询响应时间。例如，您可以对数据模型中的表进行物理调整，以最大限度地减少集群中扫描和分发的数据量，从而加快表联接和范围限制扫描等操作。[Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) 现在使用<strong>自动表优化 (ATO)</strong> 功能，自动执行此调整。另一项缩短查询运行时间的优化是，以物化视图的形式预先计算查询结果。物化视图存储预先计算的查询结果，供将来类似查询使用。这提高了查询性能，因为可以跳过许多计算步骤并直接返回预先计算的结果。与简单缓存不同，在对基础（基）表应用 DML 更改时，可以增量刷新许多物化视图，并可供其他类似查询（而不仅仅是用于创建物化视图的查询）使用。</p>\\n<p>Amazon Redshift 于2020年3月推出了物化视图。2020年6月，增加了对外部表的支持。在这些版本中，您可以在本地表和外部表上使用物化视图，通过在查询中使用预先计算的视图来提供低延迟性能。但是，这种方法要求您了解集群上有哪些物化视图可用，及其是否为最新视图。</p>\n<p>2020年11月，增加了物化视图自动刷新和查询重写功能。借助物化视图感知自动重写，数据分析师无需直接查询物化视图，即可获得查询和仪表板物化视图这样的好处，分析师甚至可能不知道物化视图的存在。自动重写功能通过将查询重写为使用物化视图，而查询无需显式引用物化视图，来实现此目的。此外，当基表数据发生更改时，自动刷新会使物化视图保持最新状态，并且存在用于维护物化视图的可用集群资源。</p>\n<p>但是，物化视图仍需由数据工程师或数据库管理员手动创建、监控和维护。为了减少这种开销，Amazon Redshift 推出了 <strong>Automated Materialized View</strong> (AutoMV) 功能，它更进一步，自动为具有常见循环联接和聚合的查询创建物化视图。</p>\\n<p>本文将解释物化视图的概念、手动物化视图的工作原理及其带来的好处，以及构建和维护手动物化视图以实现性能改进和优化所需的条件。然后，我们将解释如何通过全新自动物化视图功能大大简化此操作。</p>\n<ul>\\n<li>\\n<p>Amazon Redshift ：<br />\\n<a href=\\"https://aws.amazon.com/cn/redshift/\\" target=\\"_blank\\">https://aws.amazon.com/cn/redshift/</a></p>\\n</li>\n<li>\\n<p>出色性价比：<br />\\n<a href=\\"https://aws.amazon.com/cn/blogs/big-data/amazon-redshift-continues-its-price-performance-leadership/\\" target=\\"_blank\\">https://aws.amazon.com/cn/blogs/big-data/amazon-redshift-continues-its-price-performance-leadership/</a></p>\\n</li>\n<li>\\n<p>自动表优化 (ATO) ：<br />\\n<a href=\\"https://aws.amazon.com/cn/blogs/big-data/automate-your-amazon-redshift-performance-tuning-with-automatic-table-optimization/\\" target=\\"_blank\\">https://aws.amazon.com/cn/blogs/big-data/automate-your-amazon-redshift-performance-tuning-with-automatic-table-optimization/</a></p>\\n</li>\n<li>\\n<p>Automated Materialized View：</p>\n</li>\\n</ul>\n<p><a href=\\"https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-auto-mv.html\\" target=\\"_blank\\">https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-auto-mv.html</a></p>\\n<h4><a id=\\"_41\\"></a><strong>手动创建物化视图</strong></h4>\\n<p>物化视图是一个数据库对象，它将预先计算的查询结果存储在物化（持久化）数据集中。类似的查询可以使用物化视图中预先计算的结果，并跳过读取基础表以及执行联接和聚合等代价高昂的任务，从而提高查询性能。</p>\n<p>例如，您可以通过将仪表板的查询结果物化为一个或多个物化视图，来提高仪表板的性能。当仪表板打开或刷新时，它可以使用物化视图中的预计算结果，而不必重读基表和重新处理查询。通过创建一次物化视图并对其进行多次查询，可以避免冗余处理，从而提高查询性能，并释放资源，将其用于数据库的其他处理。</p>\n<p>为了演示这一点，我们使用以下查询，它返回每日订单和销售数字。它联接两个表并在日级别进行聚合。</p>\n<pre><code class=\\"lang-\\">SET enable_result_cache_for_session TO OFF;\\n\\nSELECT o.o_orderdate AS order_date\\n,SUM(l.l_extendedprice) AS ext_price_total\\nFROM orders o\\nINNER JOIN lineitem l\\nON o.o_orderkey = l.l_orderkey\\nWHERE o.o_orderdate &gt;= '1997-01-01'\\nAND o.o_orderdate &lt; '1998-01-01'\\nGROUP BY o.o_orderdate\\nORDER BY 1;\\n</code></pre>\\n<p>在查询的顶部，我们将 enable_result_cache_for_session 设置为 OFF。此设置会禁用结果缓存，因此，我们可以在每次运行查询时看到完整的处理运行时间。与物化视图不同，结果缓存是一种简单的缓存，它将单个查询的结果存储在内存中，它不能供其他类似的查询使用，在修改基表时不会更新，并且由于它不是持久化缓存，可能会被更频繁使用的查询耗尽内存。</p>\n<p>当我们在带有 <strong>TPC-H 3 TB</strong> (<a href=\\"https://github.com/awslabs/amazon-redshift-utils/tree/master/src/CloudDataWarehouseBenchmark/Cloud-DWB-Derived-from-TPCH\\" target=\\"_blank\\">https://github.com/awslabs/amazon-redshift-utils/tree/master/src/CloudDataWarehouseBenchmark/Cloud-DWB-Derived-from-TPCH</a>) 数据集的 10 节点 ra3.4xl 集群上运行此查询时，它将在大约 20 秒后返回。如果我们需要多次运行此查询或类似查询，可以使用 <strong>CREATE MATERIALIZED VIEW</strong> (<a href=\\"https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-create-sql-command.html\\" target=\\"_blank\\">https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-create-sql-command.html</a>) 命令创建物化视图，并直接查询物化视图对象，其结构与表相同：</p>\\n<pre><code class=\\"lang-\\">CREATE MATERIALIZED VIEW mv_daily_sales\\nAS\\nSELECT o.o_orderdate AS order_date\\n,SUM(l.l_extendedprice) AS ext_price_total\\nFROM orders o\\nINNER JOIN lineitem l\\nON o.o_orderkey = l.l_orderkey\\nWHERE o.o_orderdate &gt;= '1997-01-01'\\nAND o.o_orderdate &lt; '1998-01-01'\\nGROUP BY o.o_orderdate;\\n\\nSELECT order_date\\n,ext_price_total\\nFROM mv_daily_sales\\nORDER BY 1;\\n</code></pre>\\n<p>由于联接和聚合已经过预先计算，因此，运行时间约为900毫秒，性能提高了96%。</p>\n<p>如上所述，您可以直接查询物化视图；但是，Amazon Redshift 可以自动重写查询以使用一个或多个物化视图。查询重写功能在运行查询时透明地重写查询，以便从物化视图中检索预先计算的结果。此过程在符合条件且最新的物化视图上自动触发，但前提是该查询包含相同的基表和联接，并具有相似的聚合作为物化视图。</p>\n<ul>\\n<li>\\n<p>查询重写：<br />\\n<a href=\\"https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-auto-rewrite.html\\" target=\\"_blank\\">https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-auto-rewrite.html</a></p>\\n</li>\n<li>\\n<p>符合条件：<br />\\n<a href=\\"https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-auto-rewrite.html#mv_auto-rewrite_limitations\\" target=\\"_blank\\">https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-auto-rewrite.html#mv_auto-rewrite_limitations</a></p>\\n</li>\n</ul>\\n<p>例如，如果我们重新运行销售查询，因为它符合重写条件，会自动重写以使用 mv_daily_sales 物化视图。我们从最初的查询开始：</p>\n<pre><code class=\\"lang-\\">SELECT o.o_orderdate AS order_date\\n,SUM(l.l_extendedprice) AS ext_price_total\\nFROM orders o\\nINNER JOIN lineitem l\\nON o.o_orderkey = l.l_orderkey\\nWHERE o.o_orderdate &gt;= '1997-01-01'\\nAND o.o_orderdate &lt; '1998-01-01'\\nGROUP BY o.o_orderdate\\nORDER BY 1;\\n</code></pre>\\n<p>在内部，查询经过重写，变为以下 SQL 并运行。此过程对用户完全透明。</p>\n<pre><code class=\\"lang-\\">SELECT order_date\\n,ext_price_total\\nFROM mv_daily_sales\\nORDER BY 1;\\n</code></pre>\\n<p>重写可以通过查看查询的解释计划来确认：</p>\n<pre><code class=\\"lang-\\">EXPLAIN SELECT o.o_orderdate AS order_date\\n,SUM(l.l_extendedprice) AS ext_price_total\\nFROM orders o\\nINNER JOIN lineitem l\\nON o.o_orderkey = l.l_orderkey\\nWHERE o.o_orderdate &gt;= '1997-01-01'\\nAND o.o_orderdate &lt; '1998-01-01'\\nGROUP BY o.o_orderdate;\\n\\n+------------------------------------------------------------------------------------------------+\\n|QUERY PLAN |\\n+------------------------------------------------------------------------------------------------+\\n|XN HashAggregate (cost=5.47..5.97 rows=200 width=31) |\\n| -&gt; XN Seq Scan on mv_tbl__mv_daily_sales__0 derived_table1 (cost=0.00..3.65 rows=365 width=31)|\\n+------------------------------------------------------------------------------------------------+\\n</code></pre>\\n<p>计划显示查询已经过重写，并已从 mv_daily_sales 物化视图中检索结果，而不是查询的基表：orders 和 lineitem。</p>\n<p>其他使用相同基表和聚合级别的查询，或者使用从物化视图级别派生的聚合级别的查询也会经过重写。例如：</p>\n<pre><code class=\\"lang-\\">EXPLAIN SELECT date_trunc('month', o.o_orderdate) AS order_month\\n,SUM(l.l_extendedprice) AS ext_price_total\\nFROM orders o\\nINNER JOIN lineitem l\\nON o.o_orderkey = l.l_orderkey\\nWHERE o.o_orderdate &gt;= '1997-01-01'\\nAND o.o_orderdate &lt; '1998-01-01'\\nGROUP BY order_month;\\n\\n+------------------------------------------------------------------------------------------------+\\n|QUERY PLAN |\\n+------------------------------------------------------------------------------------------------+\\n|XN HashAggregate (cost=7.30..10.04 rows=365 width=19) |\\n| -&gt; XN Seq Scan on mv_tbl__mv_daily_sales__0 derived_table1 (cost=0.00..5.47 rows=365 width=19)|\\n+------------------------------------------------------------------------------------------------+\\n</code></pre>\\n<p>如果 orders 或 lineitem 表中的数据发生更改，则 mv_daily_sales 会变成 stale；这意味着物化视图不反映其基表的状态。如果我们更新 lineitem 中的一行并检查 stv_mv_info 系统表，则可以看到 is_stale 标记设置为 t (true)：</p>\n<pre><code class=\\"lang-\\">UPDATE lineitem\\nSET l_extendedprice = 5000\\nWHERE l_orderkey = 2362252519\\nAND l_linenumber = 1;\\n\\nSELECT name\\n,is_stale\\nFROM stv_mv_info\\nWHERE name = 'mv_daily_sales';\\n\\n+--------------+--------+\\n|name |is_stale|\\n+--------------+--------+\\n|mv_daily_sales|t |\\n+--------------+--------+\\n</code></pre>\\n<ul>\\n<li>stv_mv_info：<br />\\n<a href=\\"https://docs.aws.amazon.com/redshift/latest/dg/r_STV_MV_INFO.html\\" target=\\"_blank\\">https://docs.aws.amazon.com/redshift/latest/dg/r_STV_MV_INFO.html</a></li>\\n</ul>\n<p>我们现在可以使用 <strong>REFRESH MATERIALIZED VIEW</strong> 语句手动刷新物化视图：</p>\\n<pre><code class=\\"lang-\\">REFRESH MATERIALIZED VIEW mv_daily_sales;\\n\\nSELECT name\\n,is_stale\\nFROM stv_mv_info\\nWHERE name = 'mv_daily_sales';\\n\\n+--------------+--------+\\n|name |is_stale|\\n+--------------+--------+\\n|mv_daily_sales|f |\\n+--------------+--------+\\n</code></pre>\\n<ul>\\n<li>REFRESH MATERIALIZED VIEW：<br />\\n<a href=\\"https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-refresh-sql-command.html\\" target=\\"_blank\\">https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-refresh-sql-command.html</a></li>\\n</ul>\n<p>物化视图刷新有两种类型：完全刷新和增量刷新。完全刷新会重新运行基础 SQL 语句并重新构建整个物化视图。增量刷新仅更新受源数据更改影响的特定行。要查看物化视图是否符合增量刷新的条件，请查看 state 列（位于 stv_mv_info 系统表中）。状态0表示物化视图将完全刷新，状态 1 表示物化视图将以增量方式刷新。</p>\n<pre><code class=\\"lang-\\">SELECT name\\n,state\\nFROM stv_mv_info\\nWHERE name = 'mv_daily_sales';\\n\\n+--------------+--------+\\n|name |state |\\n+--------------+--------+\\n|mv_daily_sales| 1|\\n+--------------+--------+\\n</code></pre>\\n<p>如果您需要按固定的时间段（如每小时一次）刷新物化视图，则可以在 Amazon Redshift 控制台上安排手动刷新。有关更多信息，请参阅在 Amazon Redshift 控制台上安排查询(<a href=\\"https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor-schedule-query.html\\" target=\\"_blank\\">https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor-schedule-query.html</a>)。</p>\\n<p>除了能够进行手动刷新外，Amazon Redshift 还可以自动刷新物化视图。自动刷新功能可智能地确定何时刷新物化视图，以及是否有多个物化视图，以何种顺序刷新。Amazon Redshift 考虑了刷新物化视图的好处（物化视图的使用频率、物化视图实现的性能提升）和成本（刷新所需的资源、当前系统负载、可用的系统资源）。</p>\n<p>这种智能刷新有很多好处。由于并非所有物化视图都同样重要，因此，决定何时以及以何种顺序刷新大型系统上的物化视图对于数据库管理员来说，是一项需要完成的复杂任务。此外，数据库管理员需要考虑系统上运行的其他工作负载，并尽量确保关键工作负载的延迟不会因刷新物化视图的影响而增加。借助自动刷新功能，数据库管理员无需执行这些困难而耗时的任务。</p>\n<p>您可以在 CREATE MATERIALIZED VIEW 语句中通过 AUTO REFRESH YES 参数将物化视图设置为自动刷新：</p>\n<pre><code class=\\"lang-\\">CREATE MATERIALIZED VIEW mv_daily_sales\\nAUTO REFRESH YES\\nAS\\nSELECT ...\\n</code></pre>\\n<p>现在，当物化视图的源数据发生更改时，物化视图将自动刷新。我们可以在 svl_mv_refresh_status<br />\\n(<a href=\\"https://docs.aws.amazon.com/redshift/latest/dg/r_SVL_MV_REFRESH_STATUS.html\\" target=\\"_blank\\">https://docs.aws.amazon.com/redshift/latest/dg/r_SVL_MV_REFRESH_STATUS.html</a>) 系统表中查看刷新的状态。例如：</p>\\n<pre><code class=\\"lang-\\">UPDATE lineitem\\nSET l_extendedprice = 6000\\nWHERE l_orderkey = 2362252519\\nAND l_linenumber = 1;\\n\\nSELECT mv_name\\n,starttime\\n,endtime\\n,status\\n,refresh_type\\nFROM svl_mv_refresh_status\\nWHERE mv_name = 'mv_daily_sales';\\n\\n+--------------+--------------------------+--------------------------+---------------------------------------------+------------+\\n|mv_name |starttime |endtime |status |refresh_type|\\n+--------------+--------------------------+--------------------------+---------------------------------------------+------------+\\n|mv_daily_sales|2022-05-06 14:07:24.857074|2022-05-06 14:07:33.342346|Refresh successfully updated MV incrementally|Auto |\\n+--------------+--------------------------+--------------------------+---------------------------------------------+------------+\\n</code></pre>\\n<p>要移除物化视图，我们使用 DROP MATERIALIZED VIEW(<a href=\\"https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-drop-sql-command.html\\" target=\\"_blank\\">https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-drop-sql-command.html</a>) 命令：</p>\\n<pre><code class=\\"lang-\\">DROP MATERIALIZED VIEW mv_daily_sales;\\n</code></pre>\\n<p>现在，您已经了解了物化视图的概念、其优势及其创建、使用和移除方法，接下来，我们来讨论一下其缺点。要设计和实施一组物化视图以帮助提高数据库的整体查询性能，需要熟练的资源来执行多项复杂且耗时的任务：</p>\n<ul>\\n<li>分析系统上运行的查询</li>\n<li>确定哪些查询定期运行并提供业务优势</li>\n<li>确定已识别查询的优先级</li>\n<li>确定性能改进是否值得创建物化视图并存储数据集</li>\n<li>以物理方式创建和刷新物化视图</li>\n<li>监控物化视图的使用情况</li>\n<li>删除很少使用或从未使用或者由于基表结构更改而无法刷新的物化视图</li>\n</ul>\\n<p>设计和创建具有整体优势的物化视图需要具备重要技能，投入大量精力和时间。此外，还需要进行持续监控，以查明设计不当或未充分利用的物化视图，因为这些视图占用了资源却没有带来收益。</p>\n<p>Amazon Redshift 目前提供 <strong>Automated Materialized</strong>Views(<a href=\\"https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-auto-mv.html\\" target=\\"_blank\\">https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-auto-mv.html</a>) (AutoMVs) 功能来自动执行此过程。在以下部分中，我们将介绍 AutoMV 的工作原理以及如何在集群上使用它们。</p>\\n<h4><a id=\\"_355\\"></a><strong>自动创建物化视图</strong></h4>\\n<p>在 Amazon Redshift 集群上启用 AutoMV 功能（默认情况下处于启用状态）时，Amazon Redshift 会监控最近运行的查询，并识别任何可通过物化视图提高其性能的查询。然后，从主查询和任何子查询中提取查询中昂贵的部分，例如，可以持久保留到物化视图中并供将来查询重用的聚合和联接。随后，将提取的查询部分重写为 create materialized view 语句（候选物化视图）并存储以供进一步处理。</p>\n<p>候选物化视图不仅仅是查询的一对一副本；还需要进行额外的处理来创建通用物化视图，以供类似于原始查询的查询使用。在以下示例中，结果集受筛选器 o_orderpriority = ‘1-URGENT’ 和 l_shipmode =’AIR’ 的限制。因此，根据此结果集构建的物化视图只能提供选择该范围内有限数据的查询。</p>\n<pre><code class=\\"lang-\\">SELECT o.o_orderdate\\n,SUM(l.l_extendedprice)\\nFROM orders o\\nINNER JOIN lineitem l\\nON o.o_orderkey = l.l_orderkey\\nWHERE o.o_orderpriority = '1-URGENT'\\nAND l.l_shipmode ='AIR'\\nGROUP BY o.o_orderdate;\\n</code></pre>\\n<p>Amazon Redshift 使用多种技术来创建通用物化视图；其中一种技术称为谓词提升。要将谓词提升应用于此查询，筛选的列 o_orderpriority 和 l_shipmode 将被移到 GROUP BY 子句中，从而存储物化视图中的全部数据，允许类似查询使用相同的物化视图。这种方法由类似仪表板的工作负载驱动，此类工作负载通常使用不同的筛选条件发出相同的查询。</p>\n<pre><code class=\\"lang-\\">SELECT o.o_orderdate\\n,o.o_orderpriority\\n,l.l_shipmode\\n,SUM(l.l_extendedprice)\\nFROM orders o\\nINNER JOIN lineitem l\\nON o.o_orderkey = l.l_orderkey\\nGROUP BY o.o_orderdate\\n,o.o_orderpriority\\n,l.l_shipmode;\\n</code></pre>\\n<p>在下一个处理步骤中，将应用 ML 算法来计算哪些候选物化视图提供最佳性能优势和系统范围的性能优化。这些算法遵循与前面提到的自动刷新功能类似的逻辑。对于每个候选物化视图，Amazon Redshift 会计算出一项优势，其与物化视图在工作负载中实现和使用时的预期性能提升相对应。此外，它还计算与创建和维护候选视图所需的系统资源相对应的成本。而且会考虑现有的手动物化视图；如果已存在覆盖相同范围的手动物化视图，并且手动物化视图的自动刷新优先级高于 AutoMV，则不会创建 AutoMV。</p>\n<p>然后，按总体成本效益顺序对物化视图列表进行排序，考虑到工作负载管理 (WLM) 查询优先级，与优先级较高的队列中的查询相关的物化视图排序在与低优先级队列上的查询相关的物化视图之前。物化视图列表完全排序后，系统会自动创建这些视图，并按优先级顺序在后台填充。</p>\n<p>接着，创建的 AutoMV 将由后台进程进行监控，该进程会检查其活动，例如其查询和刷新的频率。如果该进程确定某个 AutoMV 未被使用或刷新（例如由于基表的结构发生变化），则会将其删除。</p>\n<ul>\\n<li>\\n<p>工作负载管理：<br />\\n<a href=\\"https://docs.aws.amazon.com/redshift/latest/dg/cm-c-implementing-workload-management.html\\" target=\\"_blank\\">https://docs.aws.amazon.com/redshift/latest/dg/cm-c-implementing-workload-management.html</a></p>\\n</li>\n<li>\\n<p>查询优先级：<br />\\n<a href=\\"https://docs.aws.amazon.com/redshift/latest/dg/query-priority.html\\" target=\\"_blank\\">https://docs.aws.amazon.com/redshift/latest/dg/query-priority.html</a></p>\\n</li>\n</ul>\\n<p><strong>示例</strong></p>\\n<p>为了演示此过程的实际应用，我们使用以下取自 3 TB Cloud DW Benchmark<br />\\n(<a href=\\"https://aws.amazon.com/cn/blogs/china/big-data_optimize-your-amazon-redshift-query-performance-with-automated-materialized-views/\\" target=\\"_blank\\">https://aws.amazon.com/cn/blogs/china/big-data_optimize-your-amazon-redshift-query-performance-with-automated-materialized-views/</a>) 的查询，这是源自 TPC-H 的性能测试基准。您可以将基准测试数据加载到集群中，然后按照示例进行操作。</p>\\n<pre><code class=\\"lang-\\">SET enable_result_cache_for_session TO OFF;\\n\\nSELECT /* TPC-H Q12 */\\nl_shipmode\\n, SUM(CASE\\nWHEN o_orderpriority = '1-URGENT'\\nOR o_orderpriority = '2-HIGH'\\nTHEN 1\\nELSE 0\\nEND) AS high_line_count\\n, SUM(CASE\\nWHEN o_orderpriority '1-URGENT'\\nAND o_orderpriority '2-HIGH'\\nTHEN 1\\nELSE 0\\nEND) AS low_line_count\\nFROM orders\\n, lineitem\\nWHERE o_orderkey = l_orderkey\\nAND l_shipmode IN ('MAIL', 'SHIP')\\nAND l_commitdate &lt; l_receiptdate\\nAND l_shipdate = DATE '1994-01-01'\\nAND l_receiptdate &lt; DATEADD(YEAR, 1, CAST('1994-01-01' AS DATE))\\nGROUP BY l_shipmode\\nORDER BY l_shipmode;\\n</code></pre>\\n<p>我们运行了三次查询，然后等待 30 分钟。在 10 节点 ra3.4xl 集群上，查询大约在 8 秒钟内运行。</p>\n<p>在 30 分钟内，Amazon Redshift 评估物化候选 AutoMV 的好处。它计算候选物化视图的排序列表，并在启用增量刷新、自动刷新和查询重写的情况下创建最有益的视图。当查询或类似查询运行时，它们会自动透明地重写，以使用一个或多个创建的 AutoMV。</p>\n<p>正在进行，如果基表中的数据经过修改（即 AutoMV 变为陈旧），则会自动运行增量刷新，在 AutoMV 中插入、更新和删除行，使其数据恢复到最新状态。</p>\n<p>重新运行该查询显示它在大约 800 毫秒内运行，性能提高了 90%。我们可以通过查看解释计划来确认查询是否使用了 AutoMV：</p>\n<pre><code class=\\"lang-\\">EXPLAIN SELECT /* TPC-H Q12 */\\nl_shipmode\\n,\\nSUM(CASE\\nWHEN o_orderpriority = '1-URGENT'\\nOR o_orderpriority = '2-HIGH'\\nTHEN 1\\nELSE 0\\nEND) AS high_line_count\\n, SUM(CASE\\nWHEN o_orderpriority &lt;&gt; '1-URGENT'\\nAND o_orderpriority &lt;&gt; '2-HIGH'\\nTHEN 1\\nELSE 0\\nEND) AS low_line_count\\nFROM orders\\n, lineitem\\nWHERE o_orderkey = l_orderkey\\nAND l_shipmode IN ('MAIL', 'SHIP')\\nAND l_commitdate &lt; l_receiptdate\\nAND l_shipdate &lt; l_commitdate\\nAND l_receiptdate &gt;= DATE '1994-01-01'\\nAND l_receiptdate &lt; DATEADD(YEAR, 1, CAST('1994-01-01' AS DATE))\\nGROUP BY l_shipmode\\nORDER BY l_shipmode;\\n\\n+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+\\n|QUERY PLAN |\\n+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+\\n|XN Merge (cost=1000000000354.23..1000000000354.23 rows=1 width=30) |\\n| Merge Key: derived_table1.grvar_1 |\\n| -&gt; XN Network (cost=1000000000354.23..1000000000354.23 rows=1 width=30) |\\n| Send to leader |\\n| -&gt; XN Sort (cost=1000000000354.23..1000000000354.23 rows=1 width=30) |\\n| Sort Key: derived_table1.grvar_1 |\\n| -&gt; XN HashAggregate (cost=354.21..354.22 rows=1 width=30) |\\n| -&gt; XN Seq Scan on mv_tbl__auto_mv_2000__0 derived_table1 (cost=0.00..349.12 rows=679 width=30) |\\n| Filter: ((grvar_2 &lt; '1995-01-01'::date) AND (grvar_2 &gt;= '1994-01-01'::date) AND ((grvar_1 = 'SHIP'::bpchar) OR (grvar_1 = 'MAIL'::bpchar)))|\\n+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+\\n</code></pre>\\n<p>为了演示 AutoMV 如何提高类似查询的性能，我们更改了原始查询的某些筛选器。在以下示例中，我们将 l_shipmode 的筛选器从 IN (‘MAIL’, ‘SHIP’) 更改为 IN (‘TRUCK’, ‘RAIL’, ‘AIR’)，将 l_receiptdate 的筛选器更改为上一年的前 6 个月。查询将在大约 900 毫秒内运行，从解释计划来看，我们确认它使用的是 AutoMV：</p>\n<pre><code class=\\"lang-\\">EXPLAIN SELECT /* TPC-H Q12 modified */\\nl_shipmode\\n, SUM(CASE\\nWHEN o_orderpriority = '1-URGENT'\\nOR o_orderpriority = '2-HIGH'\\nTHEN 1\\nELSE 0\\nEND) AS high_line_count\\n, SUM(CASE\\nWHEN o_orderpriority &lt;&gt; '1-URGENT'\\nAND o_orderpriority &lt;&gt; '2-HIGH'\\nTHEN 1\\nELSE 0\\nEND) AS low_line_count\\nFROM orders\\n, lineitem\\nWHERE o_orderkey = l_orderkey\\nAND l_shipmode IN ('TRUCK', 'RAIL', 'AIR')\\nAND l_commitdate &lt; l_receiptdate\\nAND l_shipdate &lt; l_commitdate\\nAND l_receiptdate &gt;= DATE '1993-01-01'\\nAND l_receiptdate &lt; DATE '1993-07-01'\\nGROUP BY l_shipmode\\nORDER BY l_shipmode;\\n\\n+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\\n|QUERY PLAN |\\n+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\\n|XN Merge (cost=1000000000396.30..1000000000396.31 rows=1 width=30) |\\n| Merge Key: derived_table1.grvar_1 |\\n| -&gt; XN Network (cost=1000000000396.30..1000000000396.31 rows=1 width=30) |\\n| Send to leader |\\n| -&gt; XN Sort (cost=1000000000396.30..1000000000396.31 rows=1 width=30) |\\n| Sort Key: derived_table1.grvar_1 |\\n| -&gt; XN HashAggregate (cost=396.29..396.29 rows=1 width=30) |\\n| -&gt; XN Seq Scan on mv_tbl__auto_mv_2000__0 derived_table1 (cost=0.00..392.76 rows=470 width=30) |\\n| Filter: ((grvar_2 &lt; '1993-07-01'::date) AND (grvar_2 &gt;= '1993-01-01'::date) AND ((grvar_1 = 'AIR'::bpchar) OR (grvar_1 = 'RAIL'::bpchar) OR (grvar_1 = 'TRUCK'::bpchar)))|\\n+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\\n</code></pre>\\n<p>AutoMV 功能对用户透明，完全由系统管理。因此，与手动物化视图不同，AutoMV 对用户不可见，也无法直接查询。它们也不会显示在任何系统表中，例如 stv_mv_info 或 svl_mv_refresh_status。</p>\n<p>最后，如果 AutoMV 已有一段时间没有被工作负载使用，则系统会自动将其删除，以释放存储空间。当我们在此之后重新运行查询时，运行时会恢复到原来的 8 秒，因为查询现在使用的是基表。这可以通过检查解释计划来确认。</p>\n<p>此示例说明了 AutoMV 功能减少创建和维护物化视图所需的工作量和时间。</p>\n<h4><a id=\\"_576\\"></a><strong>性能测试和结果</strong></h4>\\n<p>为了了解 AutoMV 在实践中的运作情况，我们使用 1 TB 和 3 TB 版本的源自 TPC-H 的 Cloud DW Benchmark(https://github.com/awslabs/amazon-redshift-utils/tree/master/src/CloudDataWarehouseBenchmark/Cloud-DWB-Derived-from-TPCH) 来运行测试。此测试由一个具有 22 个查询的 power run 脚本组成，该脚本在结果缓存关闭的情况下运行三次。这些测试在两个不同的集群上运行：并发度为 1 和 5 的 4 节点 ra3.4xlarge 和 2 节点 ra3.16xlarge。</p>\n<p>Cloud DW Benchmark 源自 TPC-H 基准测试。它无法与公布的 TPC-H 结果进行比较，因为我们的测试结果并不完全符合规范。</p>\n<p>下表显示了我们的结果：</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/33b915fd63554ceaa5fabec1401d0d8c_image.png\\" alt=\\"image.png\\" /></p>\n<p>AutoMV 功能无需任何人工干预，即可将查询性能提高多达 19%。</p>\n<h4><a id=\\"_593\\"></a><strong>总结</strong></h4>\\n<p>在本文中，我们首先介绍了手动物化视图、其各种功能及其利用方式。然后，我们研究了设计、创建和维护物化视图以提高数据仓库性能所需的工作量和时间。</p>\n<p>接下来，我们讨论了 AutoMV 如何帮助克服这些挑战，并无缝提高 SQL 查询和仪表板的性能。我们更深入地探讨了 AutoMV 的工作原理，并讨论了 ML 算法如何根据预测的性能改进以及与创建和维护视图所需的成本相比其总体收益来确定要创建的物化视图。然后，我们介绍了一些内部处理逻辑，如谓词提升如何创建通用物化视图，以供一系列查询使用，而不仅仅是触发创建物化视图的原始查询。</p>\n<p>最后，我们展示了基于行业基准的性能测试结果，其中 AutoMV 功能将性能提高了多达 19％。</p>\n<p>正如我们演示的那样，自动物化视图可以提高数据仓库的性能，而无需任何手动操作或专业知识。它们在后台透明地运行，以优化您的工作负载性能，并在工作负载变化时自动进行调整。</p>\n<p>默认情况下，自动物化视图处于启用状态。我们建议您监控其在您当前集群上的任何性能改进。如果您首次接触 Amazon Redshift，可以参阅入门教程，并通过免费试用，创建和预置您的第一个集群并试用该功能。</p>\n<ul>\\n<li>\\n<p>入门教程：<br />\\n<a href=\\"https://docs.aws.amazon.com/redshift/latest/gsg/getting-started.html\\" target=\\"_blank\\">https://docs.aws.amazon.com/redshift/latest/gsg/getting-started.html</a></p>\\n</li>\n<li>\\n<p>免费试用：<br />\\n<a href=\\"https://aws.amazon.com/cn/redshift/free-trial/\\" target=\\"_blank\\">https://aws.amazon.com/cn/redshift/free-trial/</a></p>\\n</li>\n</ul>\\n<p><strong>本篇作者</strong></p>\\n<p><strong>Adam Gatt</strong></p>\\n<p>亚马逊云科技分析高级专家解决方案架构师。他在数据和数据仓库方面拥有 20 多年的经验，帮助客户在云中构建强大、可扩展的高性能分析解决方案。</p>\n<p><strong>Rahul Chaturvedi</strong></p>\\n<p>亚马逊云科技分析专家解决方案架构师。在担任此职位之前，他曾在 Amazon 广告和 Prime Video 担任数据工程师，在此期间，他帮助构建了 PB 级数据湖，用于自助式分析。</p>\n"}