在大数据分析领域，传统的行式数据库在面对海量数据查询时往往力不从心。ClickHouse 作为一款高性能的列式数据库管理系统（DBMS），专为在线分析处理（OLAP）场景设计，以其惊人的查询性能和高效的数据压缩能力，成为大数据分析领域的热门选择。
传统数据库（如 MySQL、PostgreSQL）采用行式存储，数据按行组织：
| ID | Name | Age | City | |----|-------|-----|---------| | 1 | Alice | 25 | Beijing | | 2 | Bob | 30 | Shanghai| | 3 | Carol | 28 | Guangzhou|
存储方式：1|Alice|25|Beijing|2|Bob|30|Shanghai|3|Carol|28|Guangzhou
ClickHouse 采用列式存储，数据按列组织：
ID列: 1|2|3 Name列: Alice|Bob|Carol Age列: 25|30|28 City列: Beijing|Shanghai|Guangzhou
查询性能提升
高压缩比
向量化执行
ClickHouse 提供多种表引擎，适应不同场景：
sql-- MergeTree 系列：核心引擎，支持主键、分区、索引
CREATE TABLE events (
event_date Date,
user_id UInt64,
event_type String,
event_data String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id);
-- ReplacingMergeTree：自动去重
CREATE TABLE users (
user_id UInt64,
name String,
updated_at DateTime
) ENGINE = ReplacingMergeTree(updated_at)
ORDER BY user_id;
-- SummingMergeTree：预聚合
CREATE TABLE stats (
date Date,
metric String,
value UInt64
) ENGINE = SummingMergeTree()
ORDER BY (date, metric);
-- Distributed：分布式表
CREATE TABLE events_all AS events
ENGINE = Distributed(cluster_name, database, events, rand());
sql-- 分区策略：按月分区
PARTITION BY toYYYYMM(event_date)
-- 主键排序
ORDER BY (event_date, user_id)
-- 二级索引
CREATE TABLE events (
event_date Date,
user_id UInt64,
event_type String,
INDEX idx_type event_type TYPE bloom_filter GRANULARITY 4
) ENGINE = MergeTree()
ORDER BY (event_date, user_id);
sql-- Bloom Filter 索引
INDEX idx_bloom column_name TYPE bloom_filter GRANULARITY 1
-- MinMax 索引
INDEX idx_minmax column_name TYPE minmax GRANULARITY 3
-- Set 索引
INDEX idx_set column_name TYPE set(0) GRANULARITY 1
sql-- 批量写入（推荐每次 10000-100000 行）
INSERT INTO events VALUES
('2025-11-01', 1, 'click', 'data1'),
('2025-11-01', 2, 'view', 'data2'),
...;
-- 避免小批量频繁写入
-- 不推荐：每次插入几行
-- 推荐：累积后批量插入
sql-- 使用 PREWHERE 替代 WHERE（只读取需要的列）
SELECT user_id, count()
FROM events
PREWHERE event_date = '2025-11-01'
WHERE event_type = 'click'
GROUP BY user_id;
-- 分区裁剪
SELECT count()
FROM events
WHERE event_date BETWEEN '2025-11-01' AND '2025-11-30'; -- 只扫描11月分区
-- 避免使用 SELECT *
SELECT user_id, event_type FROM events; -- 好
SELECT * FROM events; -- 避免
sql-- 限制内存使用
SET max_memory_usage = 10000000000; -- 10GB
-- 使用外部排序
SET max_bytes_before_external_group_by = 20000000000;
xml<!-- /etc/clickhouse-server/config.xml -->
<remote_servers>
<cluster_name>
<shard>
<replica>
<host>node1</host>
<port>9000</port>
</replica>
<replica>
<host>node2</host>
<port>9000</port>
</replica>
</shard>
<shard>
<replica>
<host>node3</host>
<port>9000</port>
</replica>
<replica>
<host>node4</host>
<port>9000</port>
</replica>
</shard>
</cluster_name>
</remote_servers>
sql-- 创建本地表
CREATE TABLE events_local ON CLUSTER cluster_name (
event_date Date,
user_id UInt64,
event_type String
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id);
-- 创建分布式表
CREATE TABLE events_all ON CLUSTER cluster_name AS events_local
ENGINE = Distributed(cluster_name, default, events_local, rand());
-- 查询分布式表
SELECT count() FROM events_all WHERE event_date = today();
sql-- 创建日志表
CREATE TABLE logs (
timestamp DateTime,
level String,
message String,
source String,
INDEX idx_level level TYPE bloom_filter GRANULARITY 2
) ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(timestamp)
ORDER BY (timestamp, level);
-- 查询错误日志统计
SELECT
toStartOfHour(timestamp) as hour,
level,
count() as count
FROM logs
WHERE timestamp >= now() - INTERVAL 24 HOUR
GROUP BY hour, level
ORDER BY hour, level;
sql-- 用户行为表
CREATE TABLE user_events (
event_date Date,
user_id UInt64,
event_type String,
page_id UInt64,
duration UInt32
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id, event_type);
-- 漏斗分析
SELECT
user_id,
countIf(event_type = 'view') as views,
countIf(event_type = 'click') as clicks,
countIf(event_type = 'purchase') as purchases
FROM user_events
WHERE event_date = today()
GROUP BY user_id
HAVING views > 0;
sql-- 物化视图实现实时聚合
CREATE MATERIALIZED VIEW stats_mv
ENGINE = SummingMergeTree()
ORDER BY (date, metric)
AS SELECT
toDate(timestamp) as date,
metric,
count() as value
FROM metrics
GROUP BY date, metric;
-- 查询实时统计
SELECT * FROM stats_mv WHERE date = today();
sql-- 创建 Kafka 引擎表
CREATE TABLE kafka_queue (
timestamp DateTime,
level String,
message String
) ENGINE = Kafka()
SETTINGS
kafka_broker_list = 'kafka:9092',
kafka_topic_list = 'logs',
kafka_group_name = 'clickhouse',
kafka_format = 'JSONEachRow';
-- 创建物化视图消费数据
CREATE MATERIALIZED VIEW kafka_consumer TO logs AS
SELECT * FROM kafka_queue;
gopackage main
import (
"context"
"fmt"
"time"
"github.com/ClickHouse/clickhouse-go/v2"
)
func main() {
conn, err := clickhouse.Open(&clickhouse.Options{
Addr: []string{"127.0.0.1:9000"},
Auth: clickhouse.Auth{
Database: "default",
Username: "default",
Password: "",
},
Settings: clickhouse.Settings{
"max_execution_time": 60,
},
})
if err != nil {
panic(err)
}
defer conn.Close()
// 批量插入
batch, err := conn.PrepareBatch(context.Background(),
"INSERT INTO events (event_date, user_id, event_type)")
if err != nil {
panic(err)
}
for i := 0; i < 10000; i++ {
batch.Append(
time.Now(),
uint64(i),
"click",
)
}
batch.Send()
// 查询
rows, err := conn.Query(context.Background(),
"SELECT user_id, count() FROM events GROUP BY user_id")
if err != nil {
panic(err)
}
defer rows.Close()
for rows.Next() {
var userID uint64
var count uint64
rows.Scan(&userID, &count)
fmt.Printf("User: %d, Count: %d\n", userID, count)
}
}
| 场景 | MySQL | ClickHouse | 提升 |
|---|---|---|---|
| 亿级数据聚合查询 | 30s | 0.5s | 60x |
| 日志分析（10亿行） | 5min | 3s | 100x |
| 用户行为分析 | 10s | 0.2s | 50x |
| 数据压缩比 | 1:1 | 1:10 | 10x |
数据建模
写入策略
查询优化
集群运维
ClickHouse 的列式存储架构使其在大数据分析场景中展现出卓越的性能。通过合理的表设计、查询优化和集群部署，可以构建高效的数据分析平台。在海量数据处理、实时分析、日志监控等场景中，ClickHouse 是一个值得考虑的优秀选择。
本文介绍了 ClickHouse 列式存储的核心原理和性能优化实践，包括表引擎选择、分区索引策略、集群部署方案以及实际应用案例。
本文作者:yowayimono
本文链接:
版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！