OLAP 列式数据库——极速分析引擎 / Columnar OLAP Databases for Fast Analytics
📅 创建时间：2026-05-08 🏷️ 标签：#OLAP #ClickHouse #DuckDB #StarRocks #列式存储 #物化视图 #列压缩 📚 前置知识：[[00-db-overview]] [[01-mysql]]（了解 OLTP 和 OLAP 区别） 📚 相关知识：[[10-time-series]]（时序数据的 OLAP 分析）
定位速览
┌─────────────────────────────────────────────────────────────┐
│ OLAP 列式数据库在系统中的位置 │
├─────────────────────────────────────────────────────────────┤
│ │
│ OLTP vs OLAP： │
│ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ OLTP (MySQL/PostgreSQL) │ OLAP (ClickHouse) │ │
│ │ Online Transaction │ Online Analytical │ │
│ │ Processing │ Processing │ │
│ ├─────────────────────────────┼─────────────────────────┤ │
│ │ 每次查少量行 │ 每次扫大量行 │ │
│ │ 写入频繁 │ 批量写入/定时导入 │ │
│ │ 更新/删除多 │ 很少更新/删除 │ │
│ │ 事务强一致 │ 最终一致即可 │ │
│ │ 行式存储 │ 列式存储 │ │
│ │ 索引优化 │ 压缩优化 │ │
│ └─────────────────────────────┴─────────────────────────┘ │
│ │
│ 为什么 OLAP 快？ │
│ • 列式存储：查询 A 列只需读 A 列，不需要读整行 │
│ • 高压缩率：同类数据（同一列）压缩率极高（10-100x） │
│ • 向量化执行：一次处理一批数据，利用 CPU SIMD 指令集 │
│ • MVCC：读写不互斥 │
│ │
└─────────────────────────────────────────────────────────────┘第1部分：列式存储原理
┌─────────────────────────────────────────────────────────────┐
│ 行式 vs 列式存储 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 行式存储（MySQL / PostgreSQL）： │
│ │
│ Row 1: [张三, 28, 北京, 8000, active] │
│ Row 2: [李四, 30, 上海, 12000, active] │
│ Row 3: [王五, 25, 深圳, 6000, inactive] │
│ │
│ 查询 SELECT name, salary FROM users WHERE age > 25 │
│ → 必须读取所有列（每行 5 个字段），然后过滤 │
│ │
│ 列式存储（ClickHouse）： │
│ │
│ name 列: [张三, 李四, 王五, ...] │
│ age 列: [28, 30, 25, ...] │
│ city 列: [北京, 上海, 深圳, ...] │
│ salary 列: [8000, 12000, 6000, ...] │
│ status 列: [active, active, inactive, ...] │
│ │
│ 查询 SELECT name, salary FROM users WHERE age > 25 │
│ → 只读取 age + name + salary 三列，其他列跳过 │
│ → 如果有索引：age 列用 skip index 过滤 │
│ │
│ 压缩效果： │
│ age 列：[28, 30, 25, 28, 30, 25, 28, 30, 25, ...] │
│ → 游程编码（RLE）：[3×28, 3×30, 3×25, ...] │
│ → 压缩率：10-100x（取决于数据基数） │
│ │
└─────────────────────────────────────────────────────────────┘第2部分：ClickHouse——极速分析
2.1 架构
┌─────────────────────────────────────────────────────────────┐
│ ClickHouse 架构 │
├─────────────────────────────────────────────────────────────┤
│ │
│ ClickHouse = Clickstream + House（Yandex 出品） │
│ 原型：分析 Yandex.Metrica 日志（每天 10 亿+ 事件） │
│ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ ClickHouse Keeper（ZooKeeper 替代） │ │
│ │ 元数据协调 / 分布式 DDL / Leader 选举 │ │
│ └──────────────────────┬──────────────────────────────┘ │
│ │ │
│ ┌────────────────┼────────────────┐ │
│ ▼ ▼ ▼ │
│ ┌───────────┐ ┌───────────┐ ┌───────────┐ │
│ │ Shard 1 │ │ Shard 2 │ │ Shard 3 │ │
│ │ │ │ │ │ │ │
│ │ Replica │ │ Replica │ │ Replica │ │
│ │ A │◄──►│ A │◄──►│ A │ │
│ │ Replica │ │ Replica │ │ Replica │ │
│ │ B │◄──►│ B │◄──►│ B │ │
│ └───────────┘ └───────────┘ └───────────┘ │
│ │
│ 数据分布：同一表的不同分片存储不同数据（水平分片） │
│ 副本：同一分片的多副本保证高可用 │
│ │
│ MergeTree 表引擎家族： │
│ • MergeTree（主表）：分区 + 排序键 + 主键 + 稀疏索引 │
│ • ReplacingMergeTree：去重（保留最后一条） │
│ • SummingMergeTree：预聚合（相同排序键的数值求和） │
│ • AggregatingMergeTree：自定义聚合 │
│ • CollapsingMergeTree：逻辑删除（-sign/+sign 行） │
│ • VersionedCollapsingMergeTree：带版本的逻辑删除 │
│ • GraphiteMergeTree：Graphite 时序数据接入 │
│ │
└─────────────────────────────────────────────────────────────┘2.2 ClickHouse SQL
sql
-- ================== 表创建 ==================
CREATE TABLE user_events (
user_id UInt32,
event_type String,
page String,
revenue Float32,
created_at DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at) -- 按月分区
ORDER BY (user_id, created_at) -- 排序键（主键）
SETTINGS index_granularity = 8192; -- 索引粒度
-- ================== 物化视图 ==================
-- 预计算：每个用户每天的订单总额（自动更新）
CREATE MATERIALIZED VIEW daily_user_revenue
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (user_id, created_at)
AS SELECT
user_id,
toDate(created_at) as created_at,
SUM(revenue) as daily_total
FROM user_events
WHERE event_type = 'purchase'
GROUP BY user_id, toDate(created_at);
-- ================== 分析查询 ==================
-- 用户行为漏斗分析
SELECT
event_type,
COUNT(DISTINCT user_id) as users,
COUNT(*) as events
FROM user_events
WHERE created_at BETWEEN '2026-04-01' AND '2026-05-01'
GROUP BY event_type
ORDER BY events DESC;
-- 留存分析
SELECT
day AS day_0,
COUNT(DISTINCT if(day_1 IS NOT NULL, user_id, NULL)) AS retained,
ROUND(COUNT(DISTINCT if(day_1 IS NOT NULL, user_id, NULL)) * 100.0
/ COUNT(DISTINCT user_id), 2) AS retention_rate
FROM (
SELECT
user_id,
toDate(first_visit) AS day,
toDate(event_1) AS day_1,
toDate(event_7) AS day_7
FROM user_journeys
)
GROUP BY day
ORDER BY day;
-- 窗口函数（ClickHouse 也支持！）
SELECT
user_id,
revenue,
SUM(revenue) OVER (PARTITION BY user_id ORDER BY created_at) as cum_revenue,
AVG(revenue) OVER (PARTITION BY user_id ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as rolling_7d_avg
FROM user_events
WHERE event_type = 'purchase';2.3 Python 连接 ClickHouse
python
# clickhouse-connect
import clickhouse_connect
client = clickhouse_connect.get_client(
host='localhost', port=8123, database='default',
username='default', password='secret'
)
# 插入数据（批量，高效）
data = {
'user_id': list(range(1, 10001)),
'event_type': ['click'] * 5000 + ['purchase'] * 3000 + ['refund'] * 2000,
'revenue': [random.uniform(10, 1000) for _ in range(10000)]
}
client.insert('user_events', data, column_names=['user_id', 'event_type', 'revenue'])
# 查询（自动推断类型）
result = client.query("""
SELECT event_type,
COUNT(*) as count,
ROUND(AVG(revenue), 2) as avg_revenue,
ROUND(SUM(revenue), 2) as total_revenue
FROM user_events
WHERE created_at >= '2026-04-01'
GROUP BY event_type
ORDER BY total_revenue DESC
""")
print(result.result_rows)
# 参数化查询
user_id = 1001
result = client.query(
"SELECT * FROM user_events WHERE user_id = {user_id:UInt32}",
parameters={'user_id': user_id}
)第3部分：DuckDB——嵌入式分析型数据库
3.1 DuckDB 的独特定位
┌─────────────────────────────────────────────────────────────┐
│ DuckDB 的独特定位 │
├─────────────────────────────────────────────────────────────┤
│ │
│ DuckDB = SQLite + PostgreSQL + Pandas 的交集 │
│ │
│ SQLite 的"无服务器"： │
│ → 嵌入式，无需安装，零配置，一个库文件 │
│ │
│ PostgreSQL 的 SQL 能力： │
│ → 完整 SQL 92/99/2003/2011 + OLAP 扩展（窗口函数等） │
│ │
│ Pandas 的数据分析工作流： │
│ → Python / R 直接集成，数据分析脚本随手就跑 │
│ │
│ GitHub Stars: 17K+，DB-Engines 分析型数据库增速第一 │
│ │
│ 最适合的场景： │
│ • 本地数据分析（笔记本跑 PB 级 CSV） │
│ • ETL 预计算层 │
│ • 嵌入式 BI 应用的后端 │
│ • Jupyter Notebook 数据探索 │
│ │
└─────────────────────────────────────────────────────────────┘3.2 DuckDB 实战
python
# DuckDB Python 实战——分析本地 CSV
import duckdb
con = duckdb.connect('analytics.db')
# ================== 直接分析 CSV ==================
# 不需要先导入数据，直接查询本地文件
result = con.execute("""
SELECT
date_trunc('month', order_date) as month,
category,
COUNT(*) as order_count,
ROUND(SUM(amount)::numeric, 2) as revenue
FROM read_csv_auto('/data/orders_2026.csv')
WHERE status = 'completed'
GROUP BY 1, 2
ORDER BY 1, revenue DESC
""").fetchdf()
print(result)
# month category order_count revenue
# 0 2026-01-01 电子 15420 2345678.90
# 1 2026-01-01 服装 8231 1234567.50
# ================== Pandas 无缝集成 ==================
import pandas as pd
# DuckDB 可以直接读写 Pandas DataFrame
df = con.execute("""
SELECT * FROM df WHERE age > 25
""").df()
# 写入 DuckDB
con.execute("CREATE TABLE users AS SELECT * FROM df")
# ================== 物化视图 ==================
con.execute("""
CREATE MATERIALIZED VIEW monthly_stats AS
SELECT
date_trunc('month', created_at) as month,
COUNT(*) as total_users,
COUNTIF(status = 'active') as active_users
FROM read_parquet('/data/users/*.parquet')
GROUP BY 1
""")
# ================== 窗口函数 ==================
con.execute("""
SELECT
department,
name,
salary,
AVG(salary) OVER (PARTITION BY department) as dept_avg,
salary - AVG(salary) OVER (PARTITION BY department) as vs_avg
FROM employees
ORDER BY department, vs_avg DESC
""").fetchdf()
# ================== 扩展（插件生态） ==================
# 安装向量搜索扩展
con.execute("INSTALL parquet; LOAD parquet;")
con.execute("INSTALL iceberg; LOAD iceberg;")
# 支持：PostgreSQL FDW、S3/Parquet/DeltaLake/Iceberg...
# ================== 数据科学：与 Polars/Pandas 协作 ==================
import polars as pl
# DuckDB 作为计算引擎
plan = pl.scan_parquet('/data/events/*.parquet')
result = con.execute("""
SELECT date, SUM(revenue)
FROM plan
GROUP BY 1
""").df()第4部分：StarRocks / Doris
┌─────────────────────────────────────────────────────────────┐
│ StarRocks / Doris 对比 │
├─────────────────────────────────────────────────────────────┤
│ │
│ StarRocks（Doris 的商业化 fork）： │
│ • 字节跳动开源（类似 TiDB 之于 PingCAP） │
│ • 国内互联网实战案例最多（京东/携程/小米） │
│ • 主打：极速多维分析（秒级响应 PB 级数据） │
│ • 支持：物化视图 / Colocate Join / Bucket Shuffle Join │
│ │
│ Doris（Apache 顶级项目）： │
│ • 百度开源，现属 Apache 基金会 │
│ • 架构：FE（Frontend/Query）+ BE（Backend/Storage） │
│ • 适合：实时报表 + 湖仓一体 │
│ │
│ vs ClickHouse： │
│ • ClickHouse：列式 + 向量化 + MergeTree，写入即索引 │
│ • StarRocks/Doris：支持 UPDATE/DELETE + 更强 JOIN 能力 │
│ • 如果需要实时更新 → StarRocks / Doris │
│ • 如果只追加分析 → ClickHouse │
│ │
└─────────────────────────────────────────────────────────────┘学习状态：🟡 开始学习