ClickHouse 生产案例与最佳实践

-- 使用 STORING 策略实现冷热分离
CREATE TABLE events (
    event_time DateTime,
    user_id UInt64,
    event_type String,
    payload String CODEC(ZSTD(3))  -- 冷数据压缩率更高
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id)
TTL event_time + INTERVAL 7 DAY TO VOLUME 'warm',
    event_time + INTERVAL 30 DAY TO VOLUME 'cold',
    event_time + INTERVAL 90 DAY DELETE;

-- 原始表：秒级数据，保留 7 天
CREATE TABLE metrics_raw (
    timestamp DateTime,
    metric_name LowCardinality(String),
    value Float64
) ENGINE = MergeTree()
ORDER BY (metric_name, timestamp)
TTL timestamp + INTERVAL 7 DAY DELETE;

-- 分钟级聚合：保留 30 天
CREATE TABLE metrics_minute (
    timestamp DateTime,
    metric_name LowCardinality(String),
    avg_value Float64,
    max_value Float64,
    min_value Float64,
    count UInt64
) ENGINE = MergeTree()
ORDER BY (metric_name, timestamp)
TTL timestamp + INTERVAL 30 DAY DELETE;

CREATE MATERIALIZED VIEW metrics_minute_mv TO metrics_minute AS
SELECT
    toStartOfMinute(timestamp) AS timestamp,
    metric_name,
    avg(value) AS avg_value,
    max(value) AS max_value,
    min(value) AS min_value,
    count() AS count
FROM metrics_raw
GROUP BY metric_name, timestamp;

-- 小时级聚合：保留 1 年
CREATE TABLE metrics_hour (
    timestamp DateTime,
    metric_name LowCardinality(String),
    avg_value Float64,
    max_value Float64,
    min_value Float64,
    count UInt64
) ENGINE = MergeTree()
ORDER BY (metric_name, timestamp)
TTL timestamp + INTERVAL 1 YEAR DELETE;

CREATE MATERIALIZED VIEW metrics_hour_mv TO metrics_hour AS
SELECT
    toStartOfHour(timestamp) AS timestamp,
    metric_name,
    avg(value) AS avg_value,
    max(value) AS max_value,
    min(value) AS min_value,
    count() AS count
FROM metrics_raw
GROUP BY metric_name, timestamp;

-- 目标表
CREATE TABLE events (
    event_time DateTime,
    user_id UInt64,
    event_type String
) ENGINE = MergeTree()
ORDER BY (event_time, user_id);

-- Buffer 表（内存缓冲）
CREATE TABLE events_buffer AS events
ENGINE = Buffer(
    default,           -- 数据库名
    events,            -- 目标表名
    16,                -- 分区缓冲区数
    10,                -- 最少秒数
    100,               -- 最多秒数
    100000,            -- 最少行数
    1000000,           -- 最多行数
    10000000,          -- 最少字节数
    100000000          -- 最多字节数
);

-- 写入 Buffer 表（内存中）
INSERT INTO events_buffer VALUES (now(), 1, 'click');

-- 满足条件时自动刷写到目标表

CREATE TABLE events_kafka (
    user_id UInt64,
    event_type String,
    event_time DateTime
) ENGINE = Kafka()
SETTINGS
    kafka_broker_list = 'kafka:9092',
    kafka_topic_list = 'events',
    kafka_group_name = 'clickhouse',
    kafka_format = 'JSONEachRow',
    kafka_max_block_size = 500000;  -- 每批 50 万行

CREATE MATERIALIZED VIEW events_mv TO events AS
SELECT * FROM events_kafka;

-- 开启查询缓存（ClickHouse 22.6+）
-- config.xml:
-- <query_cache>
--     <max_size_in_bytes>1073741824</max_size_in_bytes>
--     <max_entries>1024</max_entries>
-- </query_cache>

-- 查询时使用缓存
SELECT /*+ QUERY_CACHE() */ *
FROM daily_report
WHERE report_date = yesterday();

-- 原始表：数十亿行
CREATE TABLE events (
    event_time DateTime,
    user_id UInt64,
    event_type String,
    platform String,
    value Float64
) ENGINE = MergeTree()
ORDER BY (event_time, user_id);

-- 预聚合：按天/类型/平台汇总（从数十亿降到数百万行）
CREATE TABLE events_daily (
    event_date Date,
    event_type LowCardinality(String),
    platform LowCardinality(String),
    event_count UInt64,
    total_value Float64,
    unique_users UInt64
) ENGINE = SummingMergeTree()
ORDER BY (event_date, event_type, platform);

CREATE MATERIALIZED VIEW events_daily_mv TO events_daily AS
SELECT
    toDate(event_time) AS event_date,
    event_type,
    platform,
    count() AS event_count,
    sum(value) AS total_value,
    uniqExact(user_id) AS unique_users
FROM events
GROUP BY event_date, event_type, platform;

-- 报表查询从秒级降到毫秒级
SELECT * FROM events_daily WHERE event_date = today();

-- 为每个租户创建独立数据库
CREATE DATABASE tenant_a;
CREATE DATABASE tenant_b;

-- 租户 A 的表
CREATE TABLE tenant_a.events (...);

-- 租户 B 的表
CREATE TABLE tenant_b.events (...);

-- 限制用户权限
CREATE USER tenant_a_user IDENTIFIED BY 'password';
GRANT ALL ON tenant_a.* TO tenant_a_user;

-- 共享大表，tenant_id 做第一排序键
CREATE TABLE events (
    tenant_id UInt32,
    event_time DateTime,
    user_id UInt64,
    event_type String
) ENGINE = MergeTree()
ORDER BY (tenant_id, event_time, user_id);

-- 行级安全性（通过视图实现）
CREATE VIEW tenant_a_events AS
SELECT * FROM events WHERE tenant_id = 1;

<!-- /etc/clickhouse-server/config.d/security.xml -->
<clickhouse>
    <!-- 启用 SSL -->
    <openSSL>
        <server>
            <certificateFile>/etc/clickhouse-server/server.crt</certificateFile>
            <privateKeyFile>/etc/clickhouse-server/server.key</privateKeyFile>
        </server>
    </openSSL>

    <!-- 限制连接 -->
    <listen_host>127.0.0.1</listen_host>
    <listen_host>::1</listen_host>
    <listen_host>10.0.0.0</listen_host>

    <!-- 密码认证 -->
    <users_config>users.xml</users_config>
</clickhouse>

<!-- /etc/clickhouse-server/users.d/limited_user.xml -->
<clickhouse>
    <users>
        <readonly_user>
            <password_sha256_hex>abc123...</password_sha256_hex>
            <profile>readonly</profile>
            <networks>
                <ip>10.0.0.0/24</ip>
            </networks>
            <quota>default</quota>
        </readonly_user>
    </users>
    
    <profiles>
        <readonly>
            <readonly>1</readonly>
            <max_execution_time>30</max_execution_time>
            <max_memory_usage>2000000000</max_memory_usage>
        </readonly>
    </profiles>
</clickhouse>

-- 模拟生产环境的典型查询模式
-- 1. 并发写入测试
-- 使用多个客户端同时执行 INSERT

-- 2. 查询并发测试
-- SELECT count() FROM events WHERE event_time > today() - 7
-- SELECT event_type, count() FROM events GROUP BY event_type
-- SELECT uniqExact(user_id) FROM events WHERE event_time > today() - 30

-- 3. 复杂 JOIN 测试
-- 测试物化视图查询 vs 原始表查询的性能对比

-- 4. 极限数据量测试
-- 验证在目标数据量（如 10 亿行）下的查询响应时间

clickhouse-benchmark \
    --query="SELECT event_type, count() FROM events WHERE event_time > today() - 7 GROUP BY event_type" \
    --concurrency=10 \
    --iterations=100

-- 跨副本行数校验
SELECT
    database,
    table,
    hostName() AS host,
    sum(rows) AS row_count,
    sum(bytes) AS byte_count
FROM remote('replica{1,2,3}', system, parts)
WHERE active
GROUP BY database, table, host
ORDER BY database, table, host;

-- 校验表：记录关键聚合指标的参考值
CREATE TABLE consistency_check (
    check_time DateTime,
    table_name String,
    check_type String,
    expected_value UInt64,
    actual_value UInt64,
    is_passed UInt8
) ENGINE = MergeTree()
ORDER BY (table_name, check_time);

-- 插入校验结果（定期执行）
INSERT INTO consistency_check
SELECT
    now() AS check_time,
    'events' AS table_name,
    'total_rows' AS check_type,
    (SELECT count() FROM events) AS expected_value,
    (SELECT sum(rows) FROM system.parts WHERE table = 'events' AND active) AS actual_value,
    expected_value = actual_value AS is_passed;

-- 问题：写入过于频繁，part 数量过多
-- 解决：增大批次大小或使用 Buffer 引擎

-- 检查 part 数量
SELECT database, table, count() AS parts
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY parts DESC;

-- 问题：查询消耗内存过大
-- 解决：优化查询、限制并发、增加内存

-- 设置查询内存限制
SET max_memory_usage = 2000000000;  -- 2GB

-- 使用外部排序
SET max_bytes_before_external_sort = 1000000000;
SET max_bytes_before_external_group_by = 1000000000;

-- 检查副本延迟
SELECT
    table,
    replica_name,
    absolute_delay,
    queue_size
FROM system.replicas
WHERE absolute_delay > 60
ORDER BY absolute_delay DESC;

-- 强制同步
SYSTEM SYNC REPLICA table_name;