20K Java+AI 岗位高频考点 — ClickHouse是实时OLAP分析的事实标准,大厂数据团队必问。覆盖列存储原理、MergeTree引擎家族、分布式集群、Materialized View、Java集成、性能优化、与StarRocks/Doris对比等。
列式存储:只读取查询涉及的列,减少IO。
向量化执行:利用SIMD指令,一次处理一批数据(Column-oriented batch processing)。
并行处理:多核并行扫描、分布式查询。
主键索引(稀疏索引):Skip Index加速过滤。
数据压缩:列式存储压缩比高(LZ4/ZSTD),减少磁盘IO。
C++实现:零拷贝、内存映射、精细的内存管理。
MergeTree家族(最核心):
MergeTree:基础引擎,按主键排序ReplacingMergeTree:去重引擎(后台合并时删除重复)SummingMergeTree:预聚合引擎(自动汇总数值列)AggregatingMergeTree:聚合引擎(支持复杂聚合函数)CollapsingMergeTree:折叠引擎(通过sign标记删除行)VersionedCollapsingMergeTree:带版本的折叠引擎其他引擎:
Distributed:分布式表(代理层)ReplicatedMergeTree:副本引擎Memory:内存表Kafka:Kafka引擎表MySQL:MySQL引擎表
CREATE TABLE orders (
order_id UInt64,
user_id UInt64,
amount Decimal(12,2),
order_time DateTime,
status String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(order_time)
ORDER BY (user_id, order_time)
TTL order_time + INTERVAL 6 MONTH
SETTINGS index_granularity = 8192;
核心概念:
| 维度 | Partition | Order By |
|---|---|---|
| 作用 | 数据物理隔离 | 分区内数据排序 |
| 查询优化 | 分区裁剪 | 稀疏索引+范围扫描 |
| 操作 | 支持DROP/ATTACH/DETACH分区 | 不支持直接操作 |
| 设计原则 | 按时间/业务维度 | 按查询的高频过滤条件 |
最佳实践:Partition按月(toYYYYMM),Order By按查询维度(如user_id, event_time)。
主键索引(Primary Key / Sparse Index):
跳数索引(Skip Index):
CREATE TABLE events (
event_time DateTime,
user_id UInt64,
event_type String,
data String,
INDEX idx_type event_type TYPE set(100) GRANULARITY 4,
INDEX idx_bloom user_id TYPE bloom_filter(0.01) GRANULARITY 4
) ENGINE = MergeTree()
ORDER BY event_time;
跳数索引类型:
minmax:存储每块的最大最小值set(N):存储每块中不重复的值(最多N个)bloom_filter:布隆过滤器ngrambf_v1:N-gram布隆过滤器(文本搜索)
Client → Distributed表 → 本地分片(Shard 1)
→ 本地分片(Shard 2)
→ 远程分片(Shard 3)
每个Shard可以有多个副本(Replica)
配置文件:config.xml 中的 <remote_servers> 配置分片和副本。
查询流程:
1. 接收查询的节点成为Coordinator
2. 解析SQL,确定涉及的分片
3. 将查询分发到各分片
4. 各分片并行执行本地查询
5. Coordinator汇总结果返回
基于ReplicatedMergeTree引擎 + ZooKeeper:
CREATE TABLE orders_local ON CLUSTER '{cluster}' (
-- 列定义
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/orders', '{replica}')
PARTITION BY toYYYYMM(order_time)
ORDER BY (user_id, order_time);
ZooKeeper存储:
注意:ZooKeeper是性能瓶颈,大规模集群考虑用ClickHouse Keeper(内置替代方案)。
| 维度 | ZooKeeper | ClickHouse Keeper |
|---|---|---|
| 语言 | Java | C++ |
| 性能 | 中 | 高 |
| 运维 | 独立部署 | 可内嵌 |
| 内存 | 需要JVM调优 | 原生内存管理 |
| 一致性协议 | ZAB | Raft |
建议:新集群直接用ClickHouse Keeper。
方式一:写Distributed表(简单但有风险)
方式二:写本地表(推荐)
方式三:通过负载均衡器
水平扩容(加Shard):
1. 修改集群配置,新增Shard
2. 新数据写入新Shard
3. 历史数据需要重新分布(ALTER TABLE ... RESHARD)
垂直扩容(加副本):
1. 新增Replica节点
2. 自动从其他副本同步数据
3. 不影响在线服务
注意:ClickHouse的Reshard目前还不太成熟,建议提前规划好分片数量。
ClickHouse的MV本质是一个INSERT Trigger:
1. 当源表有新数据INSERT时
2. 触发MV定义的SELECT查询
3. 将查询结果写入MV的目标表
-- 目标表
CREATE TABLE order_stats_daily (
stat_date Date,
total_amount AggregateFunction(sum, Decimal(12,2)),
order_count AggregateFunction(count)
) ENGINE = AggregatingMergeTree()
ORDER BY stat_date;
-- 物化视图
CREATE MATERIALIZED VIEW mv_order_stats TO order_stats_daily AS
SELECT
toDate(order_time) AS stat_date,
sumState(amount) AS total_amount,
countState() AS order_count
FROM orders
GROUP BY stat_date;
sumState() → 写入聚合中间状态sumMerge() → 读取时合并中间状态countState() / countMerge()avgState() / avgMerge()uniqState() / uniqMerge()(近似去重)完整用法:
-- 写入(通过MV自动触发)
INSERT INTO orders VALUES (1, 100.00, '2024-01-01 10:00:00');
-- 查询(手动Merge)
SELECT
stat_date,
sumMerge(total_amount) AS total_amount,
countMerge(order_count) AS order_count
FROM order_stats_daily
GROUP BY stat_date;
Kafka → ClickHouse Kafka Engine表 → Materialized View → 本地表
↓
聚合MV → 预聚合表
-- Kafka引擎表(消费Kafka数据)
CREATE TABLE kafka_orders (
order_id UInt64,
user_id UInt64,
amount Decimal(12,2),
order_time DateTime
) ENGINE = Kafka()
SETTINGS kafka_broker_list = 'broker:9092',
kafka_topic_list = 'orders',
kafka_group_name = 'ch_consumer',
kafka_format = 'JSONEachRow';
-- MV:Kafka表 → 本地表
CREATE MATERIALIZED VIEW mv_kafka_to_local TO orders AS
SELECT * FROM kafka_orders;
挑战:MV只处理新INSERT的数据,历史数据更新时MV不会重新计算。
解决方案:
1. Window视图(实验性):支持延迟触发
2. 定期重算:定时清空聚合表,从源表全量重算
3. VersionedCollapsingMergeTree:通过版本号处理修正
1. 分区裁剪:查询条件包含分区键
2. 主键索引:ORDER BY的前缀列用于过滤
3. 避免SELECT *:只查需要的列
4. PREWHERE:先过滤再读列数据
5. 避免JOIN:大表JOIN性能差,用字典或宽表替代
6. IN查询优化:用IN (subquery)代替多表JOIN
-- PREWHERE优化
SELECT user_id, count()
FROM events
PREWHERE event_type = 'click' -- 先用稀疏索引过滤
WHERE event_time > now() - INTERVAL 1 HOUR
GROUP BY user_id;
-- 替代JOIN:用字典
CREATE DICTIONARY user_dict (
user_id UInt64,
user_name String,
level UInt32
) SOURCE(HTTP(url 'http://api/users' format 'JSONEachRow'))
LIFETIME(MIN 300 MAX 3600);
SELECT dictGet('user_dict', 'user_name', user_id) AS name
FROM events;
1. 批量写入:每次INSERT至少1000行,最好10000-100000行
2. 控制INSERT频率:每秒不超过1-2次INSERT(每次INSERT生成一个Part)
3. 分区合理:不要过度分区(每月/每天一个分区即可)
4. 异步INSERT:async_insert=1 + wait_for_async_insert=0
5. 避免小批量:小批量导致大量小Part,触发频繁Merge
-- 异步INSERT配置
SET async_insert = 1;
SET wait_for_async_insert = 0;
SET async_insert_max_data_size = '10M';
SET async_insert_busy_timeout_ms = 10000;
查询内存:
max_memory_usage:单查询最大内存(默认10GB)max_bytes_before_external_group_by:Group By溢出到磁盘的阈值max_bytes_before_external_sort:Order By溢出到磁盘的阈值服务器内存:
mark_cache_size:标记缓存(建议为总数据的10-20%)uncompressed_cache_size:未压缩数据缓存mmap_cache_size:内存映射缓存OOM排查:查询system.query_log,找出内存消耗最大的查询。
方案一:字典替代JOIN
-- 维表小(<千万行)→ 用字典
SELECT dictGet('product_dict', 'name', product_id)
FROM orders;
方案二:宽表冗余
方案三:Colocate JOIN
方案四:Subquery代替JOIN
-- 用 IN 代替 JOIN
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE level > 5);
| 客户端 | 特点 | 推荐 |
|---|---|---|
| clickhouse-jdbc | 官方JDBC驱动 | ✅ 通用 |
| clickhouse-client | 官方新版客户端 | ✅ 高性能 |
| MyBatis + JDBC | ORM集成 | ✅ 常用 |
// JDBC连接
String url = "jdbc:clickhouse://localhost:8123/default";
try (Connection conn = DriverManager.getConnection(url, "default", "");
Statement stmt = conn.createStatement()) {
ResultSet rs = stmt.executeQuery("SELECT count() FROM events");
if (rs.next()) {
System.out.println("Total: " + rs.getLong(1));
}
}
// 批量写入
try (PreparedStatement ps = conn.prepareStatement(
"INSERT INTO events (event_time, user_id, event_type) VALUES (?, ?, ?)")) {
for (Event e : events) {
ps.setTimestamp(1, Timestamp.valueOf(e.getTime()));
ps.setLong(2, e.getUserId());
ps.setString(3, e.getType());
ps.addBatch();
}
ps.executeBatch();
}
<!-- pom.xml -->
<dependency>
<groupId>com.clickhouse</groupId>
<artifactId>clickhouse-jdbc</artifactId>
<version>0.6.x</version>
</dependency>
<!-- application.yml -->
spring:
datasource:
url: jdbc:clickhouse://localhost:8123/default
driver-class-name: com.clickhouse.jdbc.ClickHouseDriver
@Mapper
public interface EventMapper {
@Select("SELECT * FROM events WHERE user_id = #{userId} LIMIT 100")
List<Event> getByUserId(@Param("userId") Long userId);
@Insert("INSERT INTO events (event_time, user_id, event_type, data) " +
"VALUES (#{eventTime}, #{userId}, #{eventType}, #{data})")
void insert(Event event);
}
@Service
public class ReportService {
@Autowired JdbcTemplate clickhouseJdbc;
// 实时PV/UV统计
public DailyStats getDailyStats(LocalDate date) {
return clickhouseJdbc.queryForObject(
"SELECT " +
" count() AS pv, " +
" uniq(user_id) AS uv, " +
" sum(amount) AS total_amount " +
"FROM orders " +
"WHERE toDate(order_time) = ?",
(rs, rowNum) -> new DailyStats(
rs.getLong("pv"),
rs.getLong("uv"),
rs.getBigDecimal("total_amount")),
date);
}
// 漏斗分析
public List<FunnelStep> getFunnel(LocalDate date) {
return clickhouseJdbc.query(
"SELECT " +
" windowFunnel(86400)( " +
" event_time, " +
" event_type = 'view', " +
" event_type = 'cart', " +
" event_type = 'pay' " +
" ) AS step " +
"FROM events " +
"WHERE toDate(event_time) = ? " +
"GROUP BY user_id",
// ...
);
}
}
-- HTTP字典(实时拉取)
CREATE DICTIONARY product_dict (
id UInt64,
name String,
category String,
price Decimal(10,2)
) SOURCE(HTTP(
url 'http://api-service:8080/api/products'
format 'JSONEachRow'
))
LIFETIME(MIN 300 MAX 3600)
LAYOUT(COMPLEX_KEY_HASHED());
-- 使用
SELECT
dictGet('product_dict', 'name', product_id) AS product_name,
dictGet('product_dict', 'category', product_id) AS category
FROM orders;
字典类型:
HASHED / COMPLEX_KEY_HASHED:全量加载到内存CACHE:按需缓存RANGE_HASHED:范围查询ClickHouse支持标准窗口函数:
-- 用户留存分析
SELECT
first_day,
count(DISTINCT user_id) AS day0,
count(DISTINCT IF(datediff = 1, user_id, NULL)) AS day1,
count(DISTINCT IF(datediff = 7, user_id, NULL)) AS day7
FROM (
SELECT
user_id,
min(toDate(event_time)) AS first_day,
dateDiff('day', first_day, toDate(event_time)) AS datediff
FROM events
GROUP BY user_id
)
GROUP BY first_day
ORDER BY first_day;
-- 数组操作
SELECT
arrayJoin([1, 2, 3]) AS x; -- 展开数组为多行
-- JSON提取
SELECT
JSONExtractString(data, 'name') AS name,
JSONExtractFloat(data, 'amount') AS amount
FROM events;
-- 正则提取
SELECT extractAll(text, '\\d+') AS numbers FROM logs;
-- 条件聚合
SELECT
countIf(status = 'success') AS success_count,
countIf(status = 'fail') AS fail_count,
ratioToReport(amount) OVER () AS amount_ratio
FROM orders;
| 类型 | 使用建议 |
|---|---|
UInt8/16/32/64 | 无符号整数,比有符号快 |
String | 文本,UTF-8 |
FixedString(N) | 定长字符串(如UUID) |
DateTime | 秒精度时间戳 |
DateTime64(3) | 毫秒精度 |
Decimal(P,S) | 精确小数(金额) |
LowCardinality(String) | 低基数字符串(枚举类) |
Nullable(T) | 允许NULL(性能差,尽量避免) |
Array(T) | 数组 |
Map(K,V) | 映射 |
最佳实践:
UInt不用IntLowCardinality(String)NullableDecimal| 维度 | ClickHouse | StarRocks | Doris |
|---|---|---|---|
| 核心优势 | 单表查询极快 | 多表JOIN强 | 均衡 |
| JOIN能力 | 弱 | 强(Colocate/Cost-based) | 强 |
| 实时更新 | 支持但弱 | 强(MVCC) | 强 |
| 运维复杂度 | 高(ZK依赖) | 中 | 中 |
| 生态 | 丰富 | 快速追赶 | 成熟 |
| 典型用户 | 字节/Yandex | 美团/京东 | 百度/京东 |
| 语言 | C++ | C++ | C++/Java |
选型建议:
| 维度 | ClickHouse | Elasticsearch |
|---|---|---|
| 核心用途 | OLAP分析 | 全文搜索 + 分析 |
| 写入性能 | 极高 | 高 |
| 查询类型 | SQL聚合分析 | 全文检索 + 聚合 |
| 实时性 | 秒级 | 近实时(1秒) |
| 分词 | 不支持 | 强(IK等分词器) |
| 典型场景 | 报表/BI/数据仓库 | 搜索/日志/指标 |
结论:分析报表用ClickHouse,全文搜索用ES,两者可以配合使用。
| 维度 | ClickHouse | Druid |
|---|---|---|
| 部署复杂度 | 中 | 高(多个组件) |
| 实时摄入 | Kafka Engine | 原生支持 |
| 查询延迟 | 低 | 极低 |
| SQL支持 | 完善 | 一般 |
| 社区 | 活跃 | 一般 |
结论:大多数场景ClickHouse更简单高效。
MySQL(Binlog) → Flink CDC → Kafka → ClickHouse(Kafka Engine + MV)
↓
API服务查询
↓
WebSocket推送大屏
ClickHouse表设计:
-- 实时订单统计
CREATE TABLE order_realtime (
event_time DateTime,
user_id UInt64,
amount Decimal(12,2),
product_id UInt64
) ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(event_time)
ORDER BY event_time;
-- MV: 每分钟聚合
CREATE MATERIALIZED VIEW mv_minute_stats TO minute_stats AS
SELECT
toStartOfMinute(event_time) AS minute,
count() AS order_count,
sum(amount) AS total_amount,
uniq(user_id) AS uv
FROM order_realtime
GROUP BY minute;
-- 用户路径分析(使用sequenceMatch)
SELECT
count() AS total,
sequenceMatch('(?1)(?2)')(event_time, event_type = 'view', event_type = 'pay') AS view_to_pay,
sequenceMatch('(?1)(?2)')(event_time, event_type = 'view', event_type = 'cart') AS view_to_cart
FROM events
WHERE event_time > now() - INTERVAL 7 DAY
GROUP BY user_id;
-- 漏斗分析(使用windowFunnel)
SELECT
level,
count() AS count
FROM (
SELECT
user_id,
windowFunnel(86400)(
event_time,
event_type = 'view',
event_type = 'click',
event_type = 'cart',
event_type = 'pay'
) AS level
FROM events
WHERE event_time > today() - 7
GROUP BY user_id
)
GROUP BY level
ORDER BY level;
MySQL → ClickHouse:
ClickHouse → ClickHouse:
ReplicatedMergeTree副本同步REMOTE函数跨集群查询ClickHouse → 外部:
CLICKHOUSE函数关键指标:
system.metrics:当前指标(Query/Insert/PartCount)system.events:累计事件统计system.query_log:查询日志system.part_log:Part操作日志
-- 慢查询
SELECT query_duration_ms, query
FROM system.query_log
WHERE type = 'QueryFinish'
ORDER BY query_duration_ms DESC
LIMIT 10;
-- Part数量监控
SELECT table, count() AS part_count
FROM system.parts
WHERE active
GROUP BY table
HAVING part_count > 100; -- Part过多影响性能
告警项:
方案一:FREEZE PARTITION
ALTER TABLE orders FREEZE PARTITION '202401';
-- 生成硬链接到 /var/lib/clickhouse/shadow/
方案二:clickhouse-backup工具
clickhouse-backup create backup_20240101
clickhouse-backup upload backup_20240101 # 上传到S3
clickhouse-backup restore backup_20240101
方案三:远程副本恢复
ODS → Kafka → ClickHouse(本地表) — 明细数据
↓ (MV聚合)
DWS → ClickHouse(聚合表) — 聚合数据
↓ (API)
ADS → Spring Boot API → 前端大屏/报表 — 应用数据
ClickHouse优势:
1. 频繁小INSERT:导致Part爆炸 → 批量写入
2. ORDER BY设计不当:查询慢 → 按查询条件设计
3. JOIN性能差:避免大表JOIN → 用字典/宽表
4. Nullable滥用:性能差 → 用默认值替代
5. 分区过多:Part数量爆炸 → 按月分区
6. ZooKeeper压力:大规模集群瓶颈 → 用ClickHouse Keeper
7. 内存不足:大查询OOM → 设置溢出磁盘
-- 同比环比
SELECT
month,
amount,
amount / lagInFrame(amount) OVER (ORDER BY month) - 1 AS month_over_month,
amount / lagInFrame(amount, 12) OVER (ORDER BY month) - 1 AS year_over_year
FROM monthly_stats;
-- Top N
SELECT user_id, sum(amount) AS total
FROM orders
GROUP BY user_id
ORDER BY total DESC
LIMIT 100;
-- 分位数
SELECT
quantile(0.5)(response_time) AS p50,
quantile(0.95)(response_time) AS p95,
quantile(0.99)(response_time) AS p99
FROM api_logs;
SELECT
first_day,
count(DISTINCT user_id) AS cohort_size,
count(DISTINCT IF(day_diff = 1, user_id, NULL)) AS d1,
count(DISTINCT IF(day_diff = 7, user_id, NULL)) AS d7,
count(DISTINCT IF(day_diff = 30, user_id, NULL)) AS d30
FROM (
SELECT
user_id,
min(toDate(event_time)) AS first_day,
dateDiff('day', first_day, toDate(event_time)) AS day_diff
FROM events
WHERE event_time >= today() - 60
GROUP BY user_id
)
GROUP BY first_day
ORDER BY first_day;
-- 使用AggregatingMergeTree
CREATE TABLE user_rank (
rank_date Date,
user_id UInt64,
total_amount AggregateFunction(sum, Decimal(12,2))
) ENGINE = AggregatingMergeTree()
ORDER BY (rank_date, user_id);
-- MV自动聚合
CREATE MATERIALIZED VIEW mv_user_rank TO user_rank AS
SELECT
toDate(order_time) AS rank_date,
user_id,
sumState(amount) AS total_amount
FROM orders
GROUP BY rank_date, user_id;
-- 查询Top 100
SELECT
user_id,
sumMerge(total_amount) AS amount
FROM user_rank
WHERE rank_date = today()
GROUP BY user_id
ORDER BY amount DESC
LIMIT 100;
ClickHouse不擅长数据更新(Mutation代价大):
-- ALTER UPDATE(重量级操作,异步执行)
ALTER TABLE orders UPDATE status = 'cancelled' WHERE order_id = 123;
-- 查看Mutation进度
SELECT * FROM system.mutations WHERE is_done = 0;
更好的方案:
1. ReplacingMergeTree:后台合并时自动去重
2. CollapsingMergeTree:通过sign字段标记删除
3. VersionedCollapsingMergeTree:带版本号
4. 设计为Append-Only,不更新
-- 表级TTL
CREATE TABLE logs (
event_time DateTime,
data String
) ENGINE = MergeTree()
ORDER BY event_time
TTL event_time + INTERVAL 30 DAY; -- 30天后自动删除
-- 列级TTL
ALTER TABLE logs MODIFY COLUMN data String TTL event_time + INTERVAL 7 DAY;
-- 移动到冷存储
ALTER TABLE logs MODIFY TTL event_time + INTERVAL 7 DAY TO DISK 'cold',
event_time + INTERVAL 30 DAY TO DELETE;
1. Part数量:每秒INSERT次数过多导致Part爆炸
2. ZooKeeper:副本同步时ZK成为瓶颈
3. 磁盘IO:Merge和INSERT争抢IO
4. 网络:分布式写入时的网络带宽
优化:批量写入 + 异步INSERT + 控制INSERT频率 + SSD。
-- 当前存储
SELECT
database,
table,
formatReadableSize(sum(bytes_on_disk)) AS size,
sum(rows) AS rows,
count() AS parts
FROM system.parts
WHERE active
GROUP BY database, table;
-- 压缩比
SELECT
table,
formatReadableSize(sum(data_compressed_bytes)) AS compressed,
formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
sum(data_uncompressed_bytes) / sum(data_compressed_bytes) AS ratio
FROM system.columns
GROUP BY table;
max_concurrent_queries = 100Profile限制不同用户的查询资源
-- 限制查询资源
CREATE SETTINGS PROFILE reporting
SETTINGS max_memory_usage = '4GB', max_execution_time = 60;
方案一:按数据库隔离
CREATE DATABASE tenant_a;
CREATE DATABASE tenant_b;
方案二:行级隔离(RBAC)
CREATE ROW POLICY policy_a ON orders USING tenant_id = 'A' FOR SELECT;
方案三:视图隔离
CREATE VIEW tenant_a_orders AS SELECT * FROM orders WHERE tenant_id = 'A';
数据源 → Flink CDC → Kafka → ClickHouse
↓
Flink聚合 → ClickHouse聚合表
↓
Flink Window → ClickHouse时序表
Flink JDBC Sink:
JdbcSink.sink(
"INSERT INTO events (event_time, user_id, event_type) VALUES (?, ?, ?)",
(ps, event) -> {
ps.setTimestamp(1, Timestamp.valueOf(event.getTime()));
ps.setLong(2, event.getUserId());
ps.setString(3, event.getType());
},
JdbcExecutionOptions.builder()
.withBatchSize(10000)
.withBatchIntervalMs(5000)
.build(),
new JdbcConnectionOptions.JdbcConnectionOptionsBuilder()
.withUrl("jdbc:clickhouse://ch-host:8123/default")
.build()
);
导入:
# CSV
clickhouse-client --query="INSERT INTO events FORMAT CSV" < data.csv
# Parquet
clickhouse-client --query="INSERT INTO events FORMAT Parquet" < data.parquet
# JSON
clickhouse-client --query="INSERT INTO events FORMAT JSONEachRow" < data.json
导出:
SELECT * FROM events INTO OUTFILE 'output.parquet' FORMAT Parquet;
<!-- config.xml -->
<users>
<readonly_user>
<password_sha256_hex>...</password_sha256_hex>
<profile>readonly</profile>
<networks>
<ip>10.0.0.0/8</ip>
</networks>
</readonly_user>
</users>
<profiles>
<readonly>
<readonly>1</readonly>
<max_memory_usage>2000000000</max_memory_usage>
</readonly>
</profiles>
安全最佳实践:
集群规划:
硬件:
表设计:
PARTITION BY toYYYYMM(time)ORDER BY (tenant_id, user_id, time)ClickHouse三板斧:列存+向量化→快、MergeTree→核心引擎、MV→预聚合管道
>
写入三原则:大批量、低频率、异步INSERT
>
查询三优化:分区裁剪、主键过滤、避免JOIN用字典
>
选型三对比:分析用CH、搜索用ES、均衡用StarRocks/Doris