ClickHouse 分析型数据库

简介

ClickHouse 是由 Yandex 开源的列式存储分析型数据库管理系统（OLAP），专为大规模数据分析场景设计。它能够在毫秒级内对数十亿行数据执行查询，广泛应用于实时数据分析、日志处理、BI 报表、用户行为分析等场景。本文将介绍 ClickHouse 的列存储原理、MergeTree 引擎家族、聚合查询优化和物化视图的使用。

特点

• 1.列式存储 — 数据按列存储，大幅提升分析查询性能
• 2.MergeTree 引擎 — 高性能的数据存储引擎家族
• 3.聚合查询 — 向量化执行，极速完成聚合计算
• 4.物化视图 — 自动预计算和加速查询

列式存储原理

列存储 vs 行存储

-- 列式存储与行式存储的对比
-- | 特性       | 行存储            | 列存储              |
-- |-----------|------------------|--------------------|
-- | 存储方式    | 按行连续存储       | 按列连续存储         |
-- | 查询效率    | 单行查询快         | 聚合查询快          |
-- | 压缩率      | 较低              | 极高（同类型数据）    |
-- | 适用场景    | OLTP 事务型       | OLAP 分析型         |
-- | 数据扫描量  | 全行扫描           | 只扫描需要的列       |

-- ClickHouse 创建表（列式存储）
CREATE TABLE page_views (
    event_date     Date,
    event_time     DateTime,
    user_id        UInt64,
    session_id     String,
    page_url       String,
    referrer       String,
    browser        String,
    os             String,
    country        String,
    city           String,
    load_time_ms   UInt32,
    is_mobile      UInt8
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id, session_id);

-- 列存储的压缩优势
-- 数据类型相同的列存储在一起，压缩率更高
-- 例如：country 列只有几十个不同的值
-- 使用字典编码 + LZ4 压缩，压缩比可达 10:1 甚至更高

-- 查看表的压缩信息
SELECT
    table,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
    round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 2) AS compression_ratio
FROM system.columns
WHERE database = 'analytics' AND table = 'page_views'
GROUP BY table;

数据导入

-- 插入数据
INSERT INTO page_views VALUES
    ('2026-04-12', '2026-04-12 10:00:00', 1001, 'sess_001', '/home', 'https://google.com', 'Chrome', 'Windows', 'China', 'Beijing', 150, 0),
    ('2026-04-12', '2026-04-12 10:01:00', 1002, 'sess_002', '/products', 'https://bing.com', 'Firefox', 'macOS', 'China', 'Shanghai', 230, 1),
    ('2026-04-12', '2026-04-12 10:02:00', 1001, 'sess_001', '/product/123', '/home', 'Chrome', 'Windows', 'China', 'Beijing', 180, 0);

-- 从文件导入（CSV）
-- clickhouse-client --query="INSERT INTO page_views FORMAT CSV" < data.csv

-- 从文件导入（TSV）
-- clickhouse-client --query="INSERT INTO page_views FORMAT TabSeparated" < data.tsv

-- 从其他表导入
INSERT INTO page_views_archive
SELECT * FROM page_views WHERE event_date < '2026-01-01';

-- ClickHouse 最佳实践：批量插入
-- 建议每次插入 10,000 ~ 100,000 行
-- 不要频繁插入少量数据（会产生大量小 Part，影响性能）

MergeTree 引擎家族

MergeTree 引擎类型

-- MergeTree 引擎家族对比
-- | 引擎                    | 特点                    | 适用场景              |
-- |------------------------|------------------------|----------------------|
-- | MergeTree              | 基础引擎                | 通用场景              |
-- | ReplacingMergeTree     | 自动去重                | 需要最新状态的数据     |
-- | SummingMergeTree       | 自动汇总                | 预聚合统计            |
-- | AggregatingMergeTree   | 聚合状态存储             | 增量聚合计算          |
-- | CollapsingMergeTree    | 通过取消行来删除          | 日志/事件流处理       |
-- | VersionedCollapsingMergeTree | 带版本的折叠    | 按版本处理数据        |

-- 1. 基础 MergeTree
CREATE TABLE events (
    event_date  Date,
    event_time  DateTime,
    event_type  String,
    user_id     UInt64,
    properties  String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, event_time, user_id)
TTL event_date + INTERVAL 6 MONTH      -- 数据保留 6 个月
SETTINGS index_granularity = 8192;

-- 2. ReplacingMergeTree（去重）
-- 自动删除重复数据（按 ORDER BY 键去重，保留最新版本）
CREATE TABLE user_states (
    user_id     UInt64,
    updated_at  DateTime,
    username    String,
    email       String,
    status      String,
    version     UInt64
)
ENGINE = ReplacingMergeTree(version)    -- 使用 version 列判断最新
ORDER BY (user_id)
PARTITION BY toYYYYMM(updated_at);

-- 注意：去重在后台合并时执行，查询时可能仍有重复
-- 查询时使用 FINAL 关键字强制去重（性能较差）
SELECT * FROM user_states FINAL WHERE user_id = 1001;

-- 3. SummingMergeTree（自动求和）
CREATE TABLE daily_page_stats (
    stat_date   Date,
    page_url    String,
    visits      UInt64,
    unique_users UInt64,
    total_time  UInt64
)
ENGINE = SummingMergeTree((visits, unique_users, total_time))
ORDER BY (stat_date, page_url)
PARTITION BY toYYYYMM(stat_date);

-- 插入相同 ORDER BY 键的数据会自动求和
INSERT INTO daily_page_stats VALUES ('2026-04-12', '/home', 100, 80, 5000);
INSERT INTO daily_page_stats VALUES ('2026-04-12', '/home', 50, 30, 2500);
-- 合并后：visits=150, unique_users=110, total_time=7500

-- 4. AggregatingMergeTree（聚合状态）
CREATE TABLE hourly_user_stats (
    stat_hour    DateTime,
    user_id      UInt64,
    pv_count     AggregateFunction(count),
    total_amount AggregateFunction(sum, Decimal(10,2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (stat_hour, user_id)
PARTITION BY toYYYYMM(stat_hour);

-- 插入聚合状态
INSERT INTO hourly_user_stats
SELECT
    toStartOfHour(event_time) AS stat_hour,
    user_id,
    countState() AS pv_count,
    sumState(amount) AS total_amount
FROM user_events
GROUP BY stat_hour, user_id;

-- 查询聚合结果
SELECT
    stat_hour,
    user_id,
    countMerge(pv_count) AS pv,
    sumMerge(total_amount) AS total
FROM hourly_user_stats
GROUP BY stat_hour, user_id;

分区与排序

-- 分区策略
-- 按月分区（常用）
PARTITION BY toYYYYMM(event_date)

-- 按日分区（数据量大的场景）
PARTITION BY toDate(event_date)

-- 按小时分区（数据量极大的实时场景）
PARTITION BY toStartOfHour(event_time)

-- 分区操作
-- 查看分区信息
SELECT
    partition,
    name,
    rows,
    formatReadableSize(bytes_on_disk) AS size
FROM system.parts
WHERE database = 'analytics' AND table = 'page_views' AND active = 1;

-- 删除分区（比 DELETE 快得多）
ALTER TABLE page_views DROP PARTITION '202601';

-- 移动分区到冷存储
ALTER TABLE page_views MOVE PARTITION '202601' TO DISK 'cold';

-- TTL（Time To Live）自动过期
-- 创建表时指定 TTL
CREATE TABLE logs (
    event_date  Date,
    event_time  DateTime,
    message     String
)
ENGINE = MergeTree()
ORDER BY (event_date, event_time)
PARTITION BY toYYYYMM(event_date)
TTL event_date + INTERVAL 3 MONTH;  -- 3 个月后自动删除

-- 修改 TTL
ALTER TABLE logs MODIFY TTL event_date + INTERVAL 6 MONTH;

-- 排序键（ORDER BY）设计原则
-- 1. 把过滤条件中高基数的列放在前面
-- 2. 把经常一起查询的列放在相邻位置
-- 3. 排序键不宜太多（一般 3-5 个）

聚合查询优化

高性能聚合查询

-- ClickHouse 的聚合查询性能极快
-- 得益于向量化执行和列式存储

-- 1. 基本 COUNT / SUM / AVG
SELECT
    count() AS total_events,
    count(DISTINCT user_id) AS unique_users,
    sum(load_time_ms) AS total_load_time,
    avg(load_time_ms) AS avg_load_time,
    quantile(0.95)(load_time_ms) AS p95_load_time,
    quantile(0.99)(load_time_ms) AS p99_load_time
FROM page_views
WHERE event_date = '2026-04-12';

-- 2. GROUP BY 聚合
SELECT
    country,
    city,
    count() AS pv_count,
    count(DISTINCT user_id) AS user_count,
    avg(load_time_ms) AS avg_load_time,
    sum(if(is_mobile = 1, 1, 0)) AS mobile_count
FROM page_views
WHERE event_date BETWEEN '2026-04-01' AND '2026-04-12'
GROUP BY country, city
HAVING pv_count > 100
ORDER BY pv_count DESC
LIMIT 20;

-- 3. 时间维度聚合
SELECT
    toStartOfHour(event_time) AS hour,
    count() AS pv_count,
    count(DISTINCT user_id) AS user_count
FROM page_views
WHERE event_date = '2026-04-12'
GROUP BY hour
ORDER BY hour;

-- 4. 留存分析
WITH user_first_day AS (
    SELECT user_id, min(event_date) AS first_day
    FROM page_views
    WHERE event_date BETWEEN '2026-04-01' AND '2026-04-12'
    GROUP BY user_id
)
SELECT
    first_day AS cohort_date,
    count(DISTINCT user_id) AS cohort_size,
    count(DISTINCT IF(event_date = first_day + 1, user_id, NULL)) AS day1_retention,
    count(DISTINCT IF(event_date = first_day + 7, user_id, NULL)) AS day7_retention,
    count(DISTINCT IF(event_date = first_day + 30, user_id, NULL)) AS day30_retention
FROM page_views p
JOIN user_first_day f ON p.user_id = f.user_id
GROUP BY first_day
ORDER BY first_day;

-- 5. 漏斗分析
SELECT
    level,
    count() AS count
FROM (
    SELECT
        user_id,
        windowFunnel(86400)(
            event_date,
            event_type = 'page_view',
            event_type = 'add_to_cart',
            event_type = 'checkout',
            event_type = 'payment'
        ) AS level
    FROM user_events
    WHERE event_date BETWEEN '2026-04-01' AND '2026-04-12'
    GROUP BY user_id
)
GROUP BY level
ORDER BY level;
-- level 0: 只看了页面
-- level 1: 看了页面 + 加购物车
-- level 2: 看了页面 + 加购物车 + 结算
-- level 3: 看了页面 + 加购物车 + 结算 + 支付

查询性能优化

-- 1. 使用分区裁剪
-- 好的查询：指定分区范围
SELECT count() FROM page_views
WHERE event_date = '2026-04-12';  -- 只扫描一个分区

-- 不好的查询：全表扫描
SELECT count() FROM page_views
WHERE load_time_ms > 500;  -- 扫描所有分区

-- 2. 利用主键索引（稀疏索引）
-- ORDER BY (event_date, user_id, session_id)
-- 查询条件与排序键前缀一致时可以使用索引
SELECT * FROM page_views
WHERE event_date = '2026-04-12' AND user_id = 1001;  -- 高效

-- 3. 避免 SELECT *
-- 列存储中只读取需要的列可以大幅减少 I/O
-- 好
SELECT country, count() FROM page_views GROUP BY country;
-- 不好
SELECT * FROM page_views WHERE user_id = 1001;

-- 4. 使用 approximate 函数提升性能
-- 精确去重
SELECT count(DISTINCT user_id) FROM page_views;
-- 近似去重（更快，误差约 1-2%）
SELECT uniq(user_id) FROM page_views;
-- HyperLogLog 近似去重
SELECT uniqHLL12(user_id) FROM page_views;

-- 5. 使用 SAMPLE 采样查询
-- 对大数据集做快速估算
SELECT
    country,
    count() * 100 AS estimated_total  -- 1% 采样 * 100
FROM page_views
SAMPLE 0.01                          -- 采样 1%
WHERE event_date = '2026-04-12'
GROUP BY country;

物化视图

物化视图加速查询

-- 物化视图（Materialized View）
-- 自动将插入的数据按照定义的 SELECT 语句预计算并存储

-- 1. 自动预聚合物化视图
-- 原始表：page_views（按每次访问记录）
-- 目标：按天 + 页面统计 PV 和 UV

CREATE MATERIALIZED VIEW daily_page_stats_mv
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(stat_date)
ORDER BY (stat_date, page_url)
AS SELECT
    event_date AS stat_date,
    page_url,
    count() AS pv_count,
    uniqState(user_id) AS uv_state
FROM page_views
GROUP BY event_date, page_url;

-- 查询物化视图
SELECT
    stat_date,
    page_url,
    pv_count,
    uniqMerge(uv_state) AS uv_count
FROM daily_page_stats_mv
GROUP BY stat_date, page_url
ORDER BY stat_date DESC, pv_count DESC;

-- 2. 实时统计物化视图
CREATE MATERIALIZED VIEW realtime_hourly_stats
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (hour, page_url)
AS SELECT
    toStartOfHour(event_time) AS hour,
    page_url,
    countState() AS pv,
    uniqState(user_id) AS uv,
    avgState(load_time_ms) AS avg_load
FROM page_views
GROUP BY hour, page_url;

-- 查询实时统计
SELECT
    hour,
    page_url,
    countMerge(pv) AS pv_count,
    uniqMerge(uv) AS uv_count,
    avgMerge(avg_load) AS avg_load_time
FROM realtime_hourly_stats
WHERE hour >= now() - INTERVAL 24 HOUR
GROUP BY hour, page_url
ORDER BY hour DESC;

-- 3. 用户行为漏斗物化视图
CREATE MATERIALIZED VIEW user_daily_events_mv
ENGINE = AggregatingMergeTree()
ORDER BY (event_date, user_id)
AS SELECT
    event_date,
    user_id,
    groupArray(event_type) AS event_types,
    count() AS event_count
FROM user_events
GROUP BY event_date, user_id;

-- 4. 管理物化视图
-- 查看物化视图列表
SELECT name, engine FROM system.tables
WHERE database = 'analytics' AND engine = 'MaterializedView';

-- 删除物化视图
DROP VIEW daily_page_stats_mv;

-- 暂停/恢复物化视图（通过 detach/attach）
DETACH TABLE daily_page_stats_mv;
ATTACH TABLE daily_page_stats_mv;

优点

• 1.极致查询性能 — 列式存储 + 向量化执行，毫秒级完成数十亿行聚合
• 2.高压缩比 — 同类型数据连续存储，压缩比通常可达 5:1 到 10:1
• 3.线性可扩展 — 支持分布式集群，性能随节点数线性增长
• 4.丰富的函数 — 内置数百种分析函数，满足各种分析需求

缺点

• 1.不支持事务 — 没有完整的事务支持，不适合 OLTP 场景
• 2.更新删除困难 — 不支持高频单行更新和删除（Mutation 操作代价高）
• 3.Join 性能有限 — 大表 Join 性能较差，建议通过宽表或物化视图避免
• 4.学习曲线陡峭 — 特殊的数据类型和引擎概念需要专门学习

总结

ClickHouse 是一款专为 OLAP 分析场景设计的列式存储数据库，通过列式存储、向量化执行和稀疏索引实现了极致的查询性能。MergeTree 引擎家族提供了丰富的数据管理能力，物化视图可以自动预计算加速查询。在海量数据分析、实时报表、用户行为分析等场景中，ClickHouse 是非常优秀的技术选型，但需要注意它不适合 OLTP 事务处理场景。

关键知识点

• 数据库主题一定要同时看数据模型、读写模式和执行代价。
• 很多性能问题不是 SQL 语法问题，而是索引、统计信息、事务和数据分布问题。
• 高可用、备份、迁移和治理与查询优化同样重要。
• 先把数据模型、访问模式和执行代价绑定起来理解。

项目落地视角

• 所有优化前后都保留执行计划、样本 SQL 和关键指标对比。
• 上线前准备回滚脚本、备份点和校验方案。
• 把连接池、锁等待、慢查询和容量增长纳入日常巡检。
• 保留执行计划、样本 SQL、索引定义和优化前后指标。

常见误区

• 脱离真实数据分布讨论索引或分片。
• 只看单条 SQL，不看整条业务链路的事务和锁。
• 把测试环境结论直接等同于生产环境结论。
• 脱离真实数据分布设计索引。

进阶路线

• 继续向执行计划、存储引擎、复制机制和数据治理层深入。
• 把主题与 ORM、缓存、消息队列和归档策略联动起来思考。
• 沉淀成数据库设计规范、SQL 审核规则和变更流程。
• 继续深入存储引擎、复制机制、归档与冷热分层治理。

适用场景

• 当你准备把《ClickHouse 分析型数据库》真正落到项目里时，最适合先在一个独立模块或最小样例里验证关键路径。
• 适合数据建模、查询优化、事务控制、高可用和迁移治理。
• 当系统开始遇到慢查询、锁冲突、热点数据或容量增长时，这类主题价值最高。

落地建议

• 先分析真实查询模式、数据量级和写入特征，再决定索引或分片策略。
• 所有优化结论都结合执行计划、样本数据和监控指标验证。
• 高风险操作前准备备份、回滚脚本与校验 SQL。

排错清单

• 先确认瓶颈在 CPU、I/O、锁等待、网络还是 SQL 本身。
• 检查执行计划是否走错索引、是否发生排序或全表扫描。
• 排查长事务、隐式类型转换、统计信息过期和参数嗅探。

复盘问题

• 如果把《ClickHouse 分析型数据库》放进你的当前项目，最先要验证的输入、输出和失败路径分别是什么？
• 《ClickHouse 分析型数据库》最容易在什么规模、什么边界条件下暴露问题？你会用什么指标或日志去确认？
• 相比默认实现或替代方案，采用《ClickHouse 分析型数据库》最大的收益和代价分别是什么？

延伸阅读

• ClickHouse engine
• 连接池优化
• 索引原理与设计
• 数据迁移实战