Teradata Vantage大事实表插入及查询过慢的优化咨询

一、当前主键（SK_ID）作为PI的实际作用 #

将自增IDENTITY列SK_ID设为**UNIQUE PRIMARY INDEX（UPI）**几乎没有正向作用，反而会严重拖累性能：

1. 数据分布失衡：自增数值的哈希结果会集中在少数AMP上，导致插入时这些AMP成为热点，负载过高，拖慢整体插入速度。
2. 无查询价值：该字段不会用于JOIN或WHERE过滤，完全无法发挥PI的核心作用——优化查询时的数据定位与本地JOIN。
3. 冗余约束：SK_ID是GENERATED ALWAYS AS IDENTITY列，Teradata已自动保证其唯一性，额外的UNIQUE PI约束属于冗余，会增加插入时的校验开销。

唯一的“作用”仅满足了Teradata表必须定义PI的默认要求，但这个选择完全违背了分布式架构的优化原则。

二、针对大事实表的可行优化方案 #

1. 优化主键（PI）设计 #

PI是Teradata性能的核心，必须匹配表的访问模式：

• 选择查询/关联常用列作为PI：事实表通常与维度表通过外键关联，优先考虑col35、col36这类短字符串列（大概率是维度外键），或col7（日期列）作为PI。若单一列分布不够均匀，可使用组合PI（如(col35, col7)），确保数据均匀分布到所有AMP，消除热点。
• 考虑使用NOPI表：如果该表几乎只做批量插入，且查询多为全表扫描或分区扫描，可改为NO PRIMARY INDEX表。NOPI表的数据按插入顺序分布到各AMP，插入时无需哈希计算，并行性更好，适合海量批量加载场景。注意NOPI表不支持单条记录快速定位，需结合分区优化查询。

2. 优化插入操作 #

• 使用批量加载工具：放弃单行INSERT，改用Teradata专用加载工具：
  • FastLoad：适合一次性加载海量数据（无更新/删除需求），性能远高于普通INSERT。
  • MultiLoad：支持批量插入，也适配后续可能的更新场景（当前无更新需求时，FastLoad更优）。
• 调整插入参数：若使用INSERT ... SELECT，设置合适的PACKSIZE减少网络交互：

  SET SESSION PACKSIZE 64000;
  

• 禁用自动统计收集：插入前关闭自动统计，避免拖慢插入速度，完成后再手动收集：

  SET SESSION AUTOSTAT = OFF;
  

3. 调整表属性 #

• 移除FALLBACK：FALLBACK会在备用AMP存储数据副本，插入时需写入两份数据，大幅增加IO开销。若有其他备份机制（如ETL离线备份），修改表为NO FALLBACK：

  ALTER TABLE MY_TABLE NO FALLBACK;
  

• 设置MERGEBLOCKRATIO=0：该参数用于控制数据块合并，针对无更新的表，设置为0可避免不必要的块合并操作，提升插入速度：

  ALTER TABLE MY_TABLE MERGEBLOCKRATIO = 0;
  

• 创建分区表：按时间列（如col7、col3）创建范围分区，比如按天分区：

  CREATE MULTISET TABLE MY_TABLE ,NO FALLBACK ,
   NO BEFORE JOURNAL,
   NO AFTER JOURNAL,
   CHECKSUM = DEFAULT,
   MERGEBLOCKRATIO = 0,
   MAP = TD_MAP1,
   PARTITION BY RANGE_N(col7 BETWEEN DATE '2020-01-01' AND DATE '2025-12-31' EACH INTERVAL '1' DAY)
  (
   -- 列定义保持不变
  )
  PRIMARY INDEX (col35, col7); -- 替换为优化后的PI
  

  分区表可让插入和查询仅操作目标分区，减少数据扫描范围，同时提升批量插入效率。

4. 其他优化措施 #

• 手动收集统计信息：插入完成后，收集关键列的统计信息，帮助优化器生成高效执行计划：

  COLLECT STATISTICS ON MY_TABLE COLUMN (col35, col36, col7);
  

• 启用列压缩：对重复率高的列（如col17、col30、col31等短字符串列）启用压缩，减少存储空间和IO开销：

  ALTER TABLE MY_TABLE MODIFY COLUMN col17 COMPRESS;
  

• 检查AMP负载：通过系统视图查看AMP使用情况，确认是否存在热点：

  SELECT AMPNO, SUM(CurrentPerm) AS TotalPerm
  FROM DBC.AMPUsage
  WHERE DatabaseName = '你的数据库名' AND TableName = 'MY_TABLE'
  GROUP BY AMPNO
  ORDER BY TotalPerm DESC;
  

内容的提问来源于stack exchange，提问作者Thomas Letellier