Amazon Redshift
数据库开发人员指南 (API Version 2012-12-01)
AWS 服务或AWS文档中描述的功能,可能因地区/位置而异。请点击 Amazon AWS 入门,可查看中国地区的具体差异

分析表

建议您定期更新查询计划程序用来构建和选择最佳计划的统计元数据。为此,您应分析您的表。

您可以通过运行 ANALYZE 命令来显式分析表。查看

概述

在使用 COPY 命令加载数据时,您可以通过将 STATUPDATE 选项设置为 ON 来自动执行分析。默认情况下,COPY 命令会在将数据加载到空表后执行分析。无论表是否为空,您都可以通过设置 STATUPDATE ON 来强制执行分析。如果您指定 STATUPDATE OFF,则不会执行分析。

仅表所有者或超级用户才可以运行 ANALYZE 命令,或在 STATUPDATE 设置为 ON 时运行 COPY 命令。

如果您对最初加载其数据后未分析的新表运行查询,则会显示一条警告消息;但是,如果您在后续更新或加载后查询表,则不会出现警告。在您对包含未经分析的表的查询运行 EXPLAIN 命令时,会出现相同的行为。

无论何时将数据添加到非空表都将明显更改表的大小,建议您通过运行 ANALYZE 命令或将 STATUPDATE ON 选项与 COPY 命令结合使用来更新统计数据。要查看有关自上次执行 ANALYZE 以来插入或删除的行数的详细信息,请查询 PG_STATISTIC_INDICATOR 系统目录表。

如果出现可能因数据存储不足或数据的统计配置文件发生重大更改导致的性能降级,请运行分析以查看更新后的统计数据是否解决了问题。

要构建或更新统计数据,请对下列项之一运行 ANALYZE 命令:

  • 整个当前数据库

  • 单个表

  • 单个表中的一个或多个特定列

  • 有可能在查询中用作谓词的列

ANALYZE 命令将从表中获取行的采样,执行一些计算,并保存生成的列统计数据。默认情况下,Amazon Redshift 将为 DISTKEY 列运行一个采样过程,并为表中所有其他列运行另一个采样过程。如果您希望为一部分列生成统计数据,则可指定一个逗号分隔的列列表。您还可以让 ANALYZE 跳过未用作谓词的列。

ANALYZE 操作是资源密集型的,因此仅对实际需要统计数据更新的表和列运行此类操作。您无需定期或按相同的计划分析所有表中的所有列。如果数据发生重大更改,请分析在以下操作中常用的列:

  • 排序和分组操作

  • 联接

  • 查询谓词

为了减少处理时间并提高整体系统性能,对于具有较低的更改行数百分比 (由 analyze_threshold_percent 参数决定) 的任何表,Amazon Redshift 将跳过 ANALYZE。默认情况下,分析阈值将设置为 10%。可以通过运行 SET 命令来更改当前会话的分析阈值。

不太可能需要频繁分析的列是表示从未被实际查询的事实和度量以及任何相关属性的列(例如,大量 VARCHAR 列)。例如,请考虑 TICKIT 数据库中的 LISTING 表。

select "column", type, encoding, distkey, sortkey from pg_table_def where tablename = 'listing'; column | type | encoding | distkey | sortkey ---------------+--------------------+----------+---------+--------- listid | integer | none | t | 1 sellerid | integer | none | f | 0 eventid | integer | mostly16 | f | 0 dateid | smallint | none | f | 0 numtickets | smallint | mostly8 | f | 0 priceperticket | numeric(8,2) | bytedict | f | 0 totalprice | numeric(8,2) | mostly32 | f | 0 listtime | timestamp with... | none | f | 0

如果此表每天加载了大量新记录,则需要定期分析查询中频繁用作联接键的 LISTID 列。如果 TOTALPRICE 和 LISTTIME 是查询中的常用约束,则可在每个工作日分析这些列和分配键。

analyze listing(listid, totalprice, listtime);

如果应用程序中的卖家和事件变动小得多,且日期 ID 引用仅涵盖两年或三年的一组固定天数,则这些列的唯一值将不会发生明显更改。但是,每个唯一值的实例数将平稳增加。此外,请考虑查询 NUMTICKETS 和 PRICEPERTICKET 度量的频率低于查询 TOTALPRICE 列的频率这种情况。在这种情况下,您可在每个周末对整个表运行一次 ANALYZE 命令,以便更新未每日分析的 5 个列的统计数据:

谓词列

作为指定列列表的便利替代方法,您可以选择仅分析可能用作谓词的列。当您运行查询时,在联接、筛选条件或 group by 子句中使用的任意列将在系统目录中标记为谓词列。当您使用 PREDICATE COLUMNS 子句运行 ANALYZE 时,分析操作仅包括满足以下标准的列:

  • 标记为谓词列的列。

  • 该列为分配键。

  • 该列为排序键的一部分。

如果未将任何表的列标记为谓词,则即使指定了 PREDICATE COLUMNS,ANALYZE 仍将包括所有列。如果未将任何列标记为谓词列,这可能是因为尚未查询表。

在工作负载的查询模式相对稳定时,您可以选择使用 PREDICATE COLUMNS。当查询模式可变并且不同的列频繁用作谓词时,使用 PREDICATE COLUMNS 可能会临时得到过时的统计数据。过时的统计数据会产生不够理想的查询执行计划和长执行时间。不过,当您下次使用 PREDICATE COLUMNS 运行 ANALYZE 时,将包括新的谓词列。

要查看谓词列的详细信息,请使用以下 SQL 创建名为 PREDICATE_COLUMNS 的视图。

CREATE VIEW predicate_columns AS WITH predicate_column_info as ( SELECT ns.nspname AS schema_name, c.relname AS table_name, a.attnum as col_num, a.attname as col_name, CASE WHEN 10002 = s.stakind1 THEN array_to_string(stavalues1, '||') WHEN 10002 = s.stakind2 THEN array_to_string(stavalues2, '||') WHEN 10002 = s.stakind3 THEN array_to_string(stavalues3, '||') WHEN 10002 = s.stakind4 THEN array_to_string(stavalues4, '||') ELSE NULL::varchar END AS pred_ts FROM pg_statistic s JOIN pg_class c ON c.oid = s.starelid JOIN pg_namespace ns ON c.relnamespace = ns.oid JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum) SELECT schema_name, table_name, col_num, col_name, pred_ts NOT LIKE '2000-01-01%' AS is_predicate, CASE WHEN pred_ts NOT LIKE '2000-01-01%' THEN (split_part(pred_ts, '||',1))::timestamp ELSE NULL::timestamp END as first_predicate_use, CASE WHEN pred_ts NOT LIKE '%||2000-01-01%' THEN (split_part(pred_ts, '||',2))::timestamp ELSE NULL::timestamp END as last_analyze FROM predicate_column_info;

假设您对 LISTING 表运行以下查询。请注意,LISTID、LISTTIME 和 EVENTID 均用于联接、筛选和 group by 子句中。

select s.buyerid,l.eventid, sum(l.totalprice) from listing l join sales s on l.listid = s.listid where l.listtime > '2008-12-01' group by l.eventid, s.buyerid;

当您查询 PREDICATE_COLUMNS 视图时,如下例中所示,可以看到 LISTID、EVENTID 和 LISTTIME 标记为谓词列。

select * from predicate_columns where table_name = 'listing';
schema_name | table_name | col_num | col_name | is_predicate | first_predicate_use | last_analyze ------------+------------+---------+----------------+--------------+---------------------+-------------------- public | listing | 1 | listid | true | 2017-05-05 19:27:59 | 2017-05-03 18:27:41 public | listing | 2 | sellerid | false | | 2017-05-03 18:27:41 public | listing | 3 | eventid | true | 2017-05-16 20:54:32 | 2017-05-03 18:27:41 public | listing | 4 | dateid | false | | 2017-05-03 18:27:41 public | listing | 5 | numtickets | false | | 2017-05-03 18:27:41 public | listing | 6 | priceperticket | false | | 2017-05-03 18:27:41 public | listing | 7 | totalprice | false | | 2017-05-03 18:27:41 public | listing | 8 | listtime | true | 2017-05-16 20:54:32 | 2017-05-03 18:27:41

要维护表的当前统计数据,请执行以下操作:

  • 在运行查询之前运行 ANALYZE 命令。

  • 在每次定期加载或更新循环结束时,常规性地对数据库运行 ANALYZE 命令。

  • 对您创建的任何新表和正在进行重大更改的任何现有表或列运行 ANALYZE 命令。

  • 考虑按不同计划对不同类型的表和列运行 ANALYZE 操作,具体取决于它们在查询中的使用和它们的更改倾向。

  • 为节省时间和群集资源,在运行 ANALYZE 时请使用 PREDICATE COLUMNS 子句。