一、前言：OLAP 江湖新势力

如果说 ClickHouse 是 OLAP 界的"老炮儿"，那 Doris 和 StarRocks 就是正在崛起的"新生代"。

关系图谱：

本文核心问题：

Doris/StarRocks 比 ClickHouse 好在哪？又差在哪？

二、架构对比：从根源理解差异

2.1 ClickHouse 架构回顾

ClickHouse 特点：

• Shared-Nothing（完全无共享）
• 依赖 ZooKeeper 协调
• 运维复杂度高

2.2 Doris/StarRocks 架构

核心组件：

2.3 架构差异总结

三、Doris 深度解析

3.1 Doris 整体架构

3.2 数据模型

Doris 支持三种数据模型：

Duplicate 模型（明细模型）

-- 适用场景：日志、行为数据，需要保留所有明细
CREATE TABLE user_behavior (
    user_id BIGINT,
    event_time DATETIME,
    event_type VARCHAR(50),
    page_url VARCHAR(500),
    duration INT
)
DUPLICATE KEY(user_id, event_time)  -- 允许重复
DISTRIBUTED BY HASH(user_id) BUCKETS 32
PROPERTIES ("replication_num" = "3");

Aggregate 模型（聚合模型）

-- 适用场景：实时汇总，如PV/UV统计
CREATE TABLE page_view_stats (
    page_url VARCHAR(500),
    visit_date DATE,
    -- 聚合字段
    pv BIGINT SUM,           -- 自动SUM
    uv BIGINT BITMAP_UNION,  -- 自动去重
    duration BIGINT MAX      -- 取最大值
)
AGGREGATE KEY(page_url, visit_date)
DISTRIBUTED BY HASH(page_url) BUCKETS 16
PROPERTIES ("replication_num" = "3");

-- 插入时自动聚合
INSERT INTO page_view_stats VALUES 
    ('/home', '2024-01-15', 1, to_bitmap(1001), 30),
    ('/home', '2024-01-15', 1, to_bitmap(1002), 45),
    ('/home', '2024-01-15', 1, to_bitmap(1001), 20);  -- 同一用户

-- 查询结果：pv=3, uv=2（1001去重）, duration=45

Unique 模型（主键模型）

-- 适用场景：需要实时更新的维度表
CREATE TABLE user_profile (
    user_id BIGINT,
    name VARCHAR(100),
    age INT,
    city VARCHAR(50),
    update_time DATETIME
)
UNIQUE KEY(user_id)  -- 按user_id去重
DISTRIBUTED BY HASH(user_id) BUCKETS 32
PROPERTIES (
    "replication_num" = "3",
    "enable_unique_key_merge_on_write" = "true"  -- 写时合并，实时更新
);

3.3 物化视图

-- 基表
CREATE TABLE sales_detail (
    order_id BIGINT,
    order_date DATE,
    product_id BIGINT,
    category_id INT,
    region VARCHAR(50),
    amount DECIMAL(10,2)
)
DUPLICATE KEY(order_id)
DISTRIBUTED BY HASH(order_id) BUCKETS 32;

-- 创建物化视图：按天按品类汇总
CREATE MATERIALIZED VIEW mv_daily_category_sales AS
SELECT 
    order_date,
    category_id,
    SUM(amount) AS total_amount,
    COUNT(*) AS order_count
FROM sales_detail
GROUP BY order_date, category_id;

-- 查询自动路由到物化视图
SELECT category_id, SUM(amount)  -- 自动命中mv
FROM sales_detail 
WHERE order_date = '2024-01-15'
GROUP BY category_id;

3.4 Doris 2.0 新特性

四、StarRocks 深度解析

4.1 StarRocks vs Doris 差异

4.2 StarRocks 查询优化器

4.3 存算分离架构（StarRocks 3.0+）

4.4 Primary Key 表深度解析

-- StarRocks Primary Key表：支持实时更新
CREATE TABLE orders (
    order_id BIGINT,
    user_id BIGINT,
    status VARCHAR(20),
    amount DECIMAL(10,2),
    update_time DATETIME
)
PRIMARY KEY(order_id)
DISTRIBUTED BY HASH(order_id) BUCKETS 32
PROPERTIES (
    "replication_num" = "3",
    "enable_persistent_index" = "true"  -- 持久化索引，加速更新
);

-- 支持实时UPDATE/DELETE
UPDATE orders SET status = 'COMPLETED' WHERE order_id = 10001;
DELETE FROM orders WHERE order_id = 10002;

Primary Key 实现原理：

五、实战：构建实时数仓

5.1 典型实时数仓架构

5.2 Routine Load：从 Kafka 实时入库

-- Doris/StarRocks 支持直接从Kafka消费数据
CREATE ROUTINE LOAD orders_load ON orders
COLUMNS(order_id, user_id, status, amount, update_time),
COLUMNS TERMINATED BY ","
PROPERTIES (
    "desired_concurrent_number" = "3",
    "max_error_number" = "1000",
    "strict_mode" = "false",
    "format" = "json",
    "jsonpaths" = "[\"$.order_id\",\"$.user_id\",\"$.status\",\"$.amount\",\"$.update_time\"]"
)
FROM KAFKA (
    "kafka_broker_list" = "kafka1:9092,kafka2:9092,kafka3:9092",
    "kafka_topic" = "orders_topic",
    "property.group.id" = "doris_consumer_group",
    "property.kafka_default_offsets" = "OFFSET_BEGINNING"
);

-- 查看导入状态
SHOW ROUTINE LOAD FOR orders_load;

5.3 Stream Load：批量导入

# 从文件批量导入
curl --location-trusted -u root:password \
  -H "Expect:100-continue" \
  -H "column_separator:," \
  -H "columns: order_id, user_id, status, amount, update_time" \
  -T orders_data.csv \
  http://fe_host:8030/api/database/orders/_stream_load

# 导入JSON数据
curl --location-trusted -u root:password \
  -H "format: json" \
  -H "strip_outer_array: true" \
  -T orders_data.json \
  http://fe_host:8030/api/database/orders/_stream_load

5.4 Flink 写入 Doris/StarRocks

// Flink SQL 写入 Doris
CREATE TABLE doris_sink (
    order_id BIGINT,
    user_id BIGINT,
    status STRING,
    amount DECIMAL(10,2),
    update_time TIMESTAMP(3)
) WITH (
    'connector' = 'doris',
    'fenodes' = 'fe_host:8030',
    'table.identifier' = 'database.orders',
    'username' = 'root',
    'password' = 'password',
    'sink.properties.format' = 'json',
    'sink.properties.strip_outer_array' = 'true'
);

INSERT INTO doris_sink
SELECT order_id, user_id, status, amount, update_time
FROM kafka_source;

六、高级特性对比

6.1 索引支持

6.2 实时更新能力

6.3 JOIN 性能

-- StarRocks Colocate Join 示例
-- 建表时指定相同的Colocate Group
CREATE TABLE orders (
    order_id BIGINT,
    user_id BIGINT,
    amount DECIMAL(10,2)
)
DISTRIBUTED BY HASH(user_id) BUCKETS 32
PROPERTIES ("colocate_with" = "user_group");

CREATE TABLE users (
    user_id BIGINT,
    name VARCHAR(100)
)
DISTRIBUTED BY HASH(user_id) BUCKETS 32
PROPERTIES ("colocate_with" = "user_group");

-- 相同分桶键的JOIN，数据本地关联，无需Shuffle
SELECT u.name, SUM(o.amount)
FROM orders o JOIN users u ON o.user_id = u.user_id
GROUP BY u.name;

七、运维实战

7.1 集群部署架构

资源规划建议：

7.2 监控指标

-- 查看BE节点状态
SHOW BACKENDS;

-- 查看导入任务
SHOW LOAD;

-- 查看慢查询
SELECT * FROM information_schema.slow_query 
WHERE query_time > 10 
ORDER BY query_time DESC 
LIMIT 20;

-- 查看Tablet状态
SHOW TABLET FROM database.table_name;

7.3 常见问题排查

八、性能调优清单

8.1 建表优化

-- ❌ 不好的建表
CREATE TABLE bad_table (
    id BIGINT,
    name VARCHAR(500),  -- 长度过大
    data TEXT,          -- 避免TEXT
    status VARCHAR(50)  -- 应该用枚举优化
)
DISTRIBUTED BY HASH(id) BUCKETS 10;  -- 分桶数太少

-- ✅ 好的建表
CREATE TABLE good_table (
    id BIGINT,
    name VARCHAR(100),
    data VARCHAR(5000),
    status TINYINT      -- 用数字代替字符串
)
DISTRIBUTED BY HASH(id) BUCKETS 64  -- 合理的分桶数
PROPERTIES (
    "replication_num" = "3",
    "bloom_filter_columns" = "id"  -- 常查询字段加布隆过滤
);

8.2 查询优化

-- ❌ 不好的查询
SELECT * FROM orders 
WHERE DATE(order_time) = '2024-01-15';  -- 函数导致无法裁剪分区

-- ✅ 好的查询
SELECT order_id, amount FROM orders 
WHERE order_time >= '2024-01-15 00:00:00' 
  AND order_time < '2024-01-16 00:00:00';

九、选型建议

9.1 场景匹配

9.2 综合对比表

9.3 最终建议

十、总结

一句话总结：

Doris：Apache 基金会背书，社区活跃，国内用户多，2.0 版本支持全文检索

StarRocks：从 Doris 分支而来，CBO 优化器更强，Primary Key 更成熟，存算分离是亮点

两者都是 ClickHouse 之外的优秀选择，尤其适合追求运维简单、需要实时更新、有复杂 JOIN 需求的场景。