ClickHouse中Web分析数据的高性能物化视图设计咨询

1. 采用定时刷新机制，彻底隔离原表性能影响 #

因为仅需前一天及更早的历史数据，放弃自动同步的物化视图方案，改用定时任务（如crontab）每日凌晨批量刷新前一天的聚合数据。这种方式下原表写入操作完全不会触发物化视图的计算逻辑，彻底避免性能干扰。

示例定时任务脚本：

# 每日凌晨1点执行，刷新前一天的聚合数据
0 1 * * * clickhouse-client --query "INSERT INTO analytics.Records_Daily_Agg SELECT ... FROM analytics.Records WHERE Created >= toStartOfDay(now() - INTERVAL 1 DAY) AND Created < toStartOfDay(now()) GROUP BY ..."

2. 使用AggregatingMergeTree引擎存储预聚合状态 #

选择AggregatingMergeTree作为聚合表的底层引擎，它专为预聚合场景优化，会将相同GROUP BY键的记录合并存储聚合状态（而非直接计算最终结果），大幅降低存储体积和写入开销，查询时再通过对应*Merge函数计算最终值。

创建目标聚合表的SQL：

CREATE TABLE IF NOT EXISTS analytics.Records_Daily_Agg
(
    Date Date,
    GeoIPAutonomousSystemOrganization LowCardinality(String),
    AvgPageLoadTime AggregateFunction(avg, UInt16),
    MinPageLoadTime AggregateFunction(min, UInt16),
    MaxPageLoadTime AggregateFunction(max, UInt16),
    MedianPageLoadTime AggregateFunction(median, UInt16),
    PageLoadTimeStdDev AggregateFunction(stddevSamp, UInt16),
    AvgDomainLookupTime AggregateFunction(avg, UInt16),
    MinDomainLookupTime AggregateFunction(min, UInt16),
    MaxDomainLookupTime AggregateFunction(max, UInt16),
    MedianDomainLookupTime AggregateFunction(median, UInt16),
    DomainLookupTimeStdDev AggregateFunction(stddevSamp, UInt16),
    AvgTCPConnectTime AggregateFunction(avg, UInt16),
    MinTCPConnectTime AggregateFunction(min, UInt16),
    MaxTCPConnectTime AggregateFunction(max, UInt16),
    MedianTCPConnectTime AggregateFunction(median, UInt16),
    TCPConnectTimeStdDev AggregateFunction(stddevSamp, UInt16),
    AvgServerResponseTime AggregateFunction(avg, UInt16),
    MinServerResponseTime AggregateFunction(min, UInt16),
    MaxServerResponseTime AggregateFunction(max, UInt16),
    MedianServerResponseTime AggregateFunction(median, UInt16),
    ServerResponseTimeStdDev AggregateFunction(stddevSamp, UInt16)
)
ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/tables/{shard}/analytics/Records_Daily_Agg', '{replica}')
ORDER BY (Date, GeoIPAutonomousSystemOrganization)
PARTITION BY toYYYYMM(Date);

3. 利用采样降低计算量，满足误差要求 #

原表已配置SAMPLE BY xxHash32(PublicInstanceID)，可在聚合查询中使用SAMPLE子句抽取10%样本数据计算，既能将误差控制在5%以内，又能将计算量降低一个数量级。

示例聚合插入SQL（用于定时任务）：

INSERT INTO analytics.Records_Daily_Agg
SELECT
    toStartOfDay(Created) AS Date,
    GeoIPAutonomousSystemOrganization,
    avgState(PerfPageLoadTime) AS AvgPageLoadTime,
    minState(PerfPageLoadTime) AS MinPageLoadTime,
    maxState(PerfPageLoadTime) AS MaxPageLoadTime,
    medianState(PerfPageLoadTime) AS MedianPageLoadTime,
    stddevSampState(PerfPageLoadTime) AS PageLoadTimeStdDev,
    avgState(PerfDomainLookupTime) AS AvgDomainLookupTime,
    minState(PerfDomainLookupTime) AS MinDomainLookupTime,
    maxState(PerfDomainLookupTime) AS MaxDomainLookupTime,
    medianState(PerfDomainLookupTime) AS MedianDomainLookupTime,
    stddevSampState(PerfDomainLookupTime) AS DomainLookupTimeStdDev,
    avgState(PerfTCPConnectTime) AS AvgTCPConnectTime,
    minState(PerfTCPConnectTime) AS MinTCPConnectTime,
    maxState(PerfTCPConnectTime) AS MaxTCPConnectTime,
    medianState(PerfTCPConnectTime) AS MedianTCPConnectTime,
    stddevSampState(PerfTCPConnectTime) AS TCPConnectTimeStdDev,
    avgState(PerfServerResponseTime) AS AvgServerResponseTime,
    minState(PerfServerResponseTime) AS MinServerResponseTime,
    maxState(PerfServerResponseTime) AS MaxServerResponseTime,
    medianState(PerfServerResponseTime) AS MedianServerResponseTime,
    stddevSampState(PerfServerResponseTime) AS ServerResponseTimeStdDev
FROM analytics.Records
WHERE Created >= toStartOfDay(now() - INTERVAL 1 DAY) 
  AND Created < toStartOfDay(now())
SAMPLE 0.1
GROUP BY Date, GeoIPAutonomousSystemOrganization;

4. 查询时计算最终聚合结果 #

从聚合表查询时，使用*Merge函数将预存的聚合状态转换为最终值：

SELECT
    Date,
    GeoIPAutonomousSystemOrganization,
    avgMerge(AvgPageLoadTime) AS AvgPageLoadTime,
    minMerge(MinPageLoadTime) AS MinPageLoadTime,
    maxMerge(MaxPageLoadTime) AS MaxPageLoadTime,
    medianMerge(MedianPageLoadTime) AS MedianPageLoadTime,
    stddevSampMerge(PageLoadTimeStdDev) AS PageLoadTimeStdDev,
    avgMerge(AvgDomainLookupTime) AS AvgDomainLookupTime,
    minMerge(MinDomainLookupTime) AS MinDomainLookupTime,
    maxMerge(MaxDomainLookupTime) AS MaxDomainLookupTime,
    medianMerge(MedianDomainLookupTime) AS MedianDomainLookupTime,
    stddevSampMerge(DomainLookupTimeStdDev) AS DomainLookupTimeStdDev,
    avgMerge(AvgTCPConnectTime) AS AvgTCPConnectTime,
    minMerge(MinTCPConnectTime) AS MinTCPConnectTime,
    maxMerge(MaxTCPConnectTime) AS MaxTCPConnectTime,
    medianMerge(MedianTCPConnectTime) AS MedianTCPConnectTime,
    stddevSampMerge(TCPConnectTimeStdDev) AS TCPConnectTimeStdDev,
    avgMerge(AvgServerResponseTime) AS AvgServerResponseTime,
    minMerge(MinServerResponseTime) AS MinServerResponseTime,
    maxMerge(MaxServerResponseTime) AS MaxServerResponseTime,
    medianMerge(MedianServerResponseTime) AS MedianServerResponseTime,
    stddevSampMerge(ServerResponseTimeStdDev) AS ServerResponseTimeStdDev
FROM analytics.Records_Daily_Agg
GROUP BY Date, GeoIPAutonomousSystemOrganization;

5. 额外性能优化点 #

• 低基数类型优化：将GeoIPAutonomousSystemOrganization定义为LowCardinality(String)，利用其重复度高的特性减少存储开销，提升GROUP BY性能。
• 分区过滤：原表和聚合表均按月份分区，查询时通过PARTITION BY指定目标月份，避免全表扫描。
• 避免重复计算：定时任务执行前检查聚合表中是否已存在对应日期的数据，跳过重复刷新操作。

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