插入前已排序为何表的平均聚类深度仍居高不下？

DevOps 小满

2026-8-10

问题描述

我有一张大表，建表语句如下：

CREATE OR REPLACE BIG_TABLE(
    EVENT_DATETIME TIMESTAMP_LTZ(9), -- 粒度和基数远高于其他字段
    SOURCE_ID VARCHAR(16777216),     -- 只有两种取值：SOURCE 1 或 SOURCE 2
    COL_TYPE VARCHAR(16777216),      -- 有几百种取值，80%的行属于其中20%的类型
    COL_JSON VARIANT,
    ROW_CREATED_AT TIMESTAMP_LTZ(9),
);

我尝试通过重排表来优化自然聚类，操作如下：

INSERT INTO ORDERED_BIG_TABLE 
SELECT * FROM BIG_TABLE ORDER BY EVENT_DATETIME::DATE, COL_TYPE, COL_JSON:EVENT_ID;

alter table BIG_TABLE swap with ORDERED_BIG_TABLE ;

但在这张包含15000个分区的表中，EVENT_DATETIME::DATE的平均聚类深度约为30。以下是SYSTEM$CLUSTERING_INFORMATION ('BIG_TABLE', '(EVENT_DATETIME::DATE,COL_TYPE)')的输出结果：

{      
  "cluster_by_keys" : "LINEAR(EVENT_DATETIME::DATE, COL_TYPE)",
  "total_partition_count" : 15105,
  "total_constant_partition_count" : 10179,
  "average_overlaps" : 34.70654,
  "average_depth" : 34.27224,
  "partition_depth_histogram" : {
    "00000" : 0,
    "00001" : 10169,
    "00002" : 0,
    "00003" : 1,
    "00004" : 4179,
    "00005" : 32,
    "00006" : 0,
    "00007" : 0,
    "00008" : 0,
    "00009" : 0,
    "00010" : 0,
    "00011" : 0,
    "00012" : 0,
    "00013" : 0,
    "00014" : 0,
    "00015" : 0,
    "00016" : 0,
    "08192" : 722
  }
}

请问为何聚类深度仍处于较高水平？

原因分析

高频COL_TYPE的多微分区重叠：COL_TYPE存在典型的80/20分布，20%的类型占据了80%的行数据。这些高频类型在同一日期下的数据量远超单个Snowflake微分区的容量（16-100MB），因此会被拆分成多个微分区。这些微分区的聚类键（EVENT_DATETIME::DATE, COL_TYPE）完全一致，导致它们的键范围互相重叠，直接推高了平均聚类深度。从分区深度直方图可以看到，10169个深度为1的分区对应低频COL_TYPE（每个日期+类型仅占用1个微分区），而深度为4、5的分区以及那722个深度达8192的分区，正是高频COL_TYPE拆分出的多微分区。
排序键与聚类键不匹配：插入数据时你按EVENT_DATETIME::DATE, COL_TYPE, COL_JSON:EVENT_ID排序，但聚类键仅包含前两列。虽然同一日期+COL_TYPE的数据内部按EVENT_ID有序，但微分区的划分是基于数据量而非第三列，因此同一聚类键值的数据会被拆分成多个微分区，这些微分区的键范围完全重叠，进一步增加了聚类深度。
自动分区与聚类键的协同问题：如果你的表采用Snowflake默认的自动分区（未显式指定分区键），系统会基于EVENT_DATETIME自动创建时间分区，但自动分区的粒度可能与聚类键的日期粒度不完全对齐，或者在排序插入时，大量连续的同一聚类键数据被拆分成多个微分区，这些微分区的键范围重叠，导致深度统计值被拉高。

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