learnNote

主题

附录 A：ClickHouse vs MySQL / PostgreSQL / Doris 对比速查表 ​

工具书用法：遇到「CK 和 X 到底哪里不一样」，按目录跳查。 对比版本：ClickHouse 24.x vs MySQL 8.0 (InnoDB) vs PostgreSQL 16 vs Doris / StarRocks 3.x。

目录 ​

  1. 总体定位
  2. 存储模型（行存 vs 列存）
  3. 主键含义
  4. 事务与一致性
  5. 索引体系
  6. UPDATE / DELETE 实现
  7. JOIN 能力
  8. 复制与高可用
  9. 备份与恢复
  10. 生态与客户端
  11. 选型建议（10 句话）
  12. 迁移建议（双向）

1. 总体定位 ​

维度ClickHouseMySQLPostgreSQLDoris/StarRocks
类型OLAP（分析）OLTPOLTP/HTAP（轻分析）OLAP / 实时数仓
一句话定位列存秒级聚合，单查询全力以赴高并发短事务万能型关系数据库MPP 实时数仓
默认场景大屏 / BI / 漏斗 / 留存 / 日志检索订单 / 账户 / 业务库订单 + 半结构化 + 向量 + 地理实时数仓 / 即席查询
协议 / 端口8123(HTTP) / 9000(TCP)330654329030(MySQL 协议)
开源协议Apache 2.0GPLv2PostgreSQL License (BSD-like)Apache 2.0

📌 一句话：MySQL/PG 是「做菜的厨房」，ClickHouse 是「分析菜谱销量的总部」；同一份订单，OLTP 写入靠 MySQL/PG，OLAP 分析靠 CK 或 Doris。

2. 存储模型（行存 vs 列存） ​

维度ClickHouseMySQL InnoDBPostgreSQL 堆表Doris
物理模型列存（Column-oriented）行存 + 聚簇索引行存 + 独立索引列存
单列文件每列一个 .bin+.mrk整行连续存于 page整行连续存于 page每列一个 segment 文件
压缩列级（LZ4/ZSTD/Delta/Gorilla），压缩率 5~20×表级（行内压缩有限）TOAST 离线大字段列级压缩
适合场景大批扫少列聚合单行点查、行更新综合大批扫聚合

差异说明：

  • 列存的优势：扫描 1 列时只读 1/N 的 IO；列内类型一致，向量化指令打满 CPU；同列重复值多，压缩率极高。
  • 行存的优势：单行多列点读 / 写一次落一行最快；事务 / MVCC 更易实现。

迁移建议：从 MySQL 迁聚合查询到 CK，别按行复制表结构——把列拍宽（窄表 → 宽表 / 大宽表）、把高基数列降维（用 LowCardinality）、把 JSON 拆出常用字段单独成列。

3. 主键含义 ​

维度ClickHouseMySQL InnoDBPostgreSQLDoris
主键作用排序 + 稀疏索引，不保证唯一唯一约束 + 聚簇索引唯一约束 + 独立 B-TreeUnique Key Model 时唯一
索引粒度稀疏（每 8192 行一个 Mark）稠密（每行一个）稠密稠密 + Bloom + Bitmap
写入是否检查不检查检查检查Unique 模型检查
是否物理排序是（按 ORDER BY 排好后写盘）是否（堆表）是

差异 2~3 句解释：CK 里写 PRIMARY KEY (id) 实际上是声明「希望按 id 顺序存盘并建稀疏索引」，两条 id=1 完全可以并存；MySQL/PG 里则是「同一个 id 不允许出现两次」。这一条是 MySQL 老司机迁 CK 时最常翻车的点。

4. 事务与一致性 ​

维度ClickHouseMySQLPostgreSQLDoris
完整 ACID❌ 仅有限事务✅✅部分（导入事务）
单分区 INSERT 原子✅✅✅✅
多语句事务实验性（24.x 部分支持）✅✅仅 stream load 内
MVCC❌（靠 Merge 去重）✅ Undo Log✅ 多版本元组❌
隔离级别N/ARR (默认)RC (默认)N/A

迁移建议：迁移业务库（订单、账户）绝对不要搬 CK；CK 只放分析口径的下游表。

5. 索引体系 ​

索引种类ClickHouseMySQLPostgreSQLDoris
主索引稀疏主键索引聚簇 B+ Tree独立 B-Tree排序键稀疏索引
二级索引Skip Index：minmax / set / bloom_filter / ngrambf_v1 / tokenbf_v1B+ Tree / Hash / FULLTEXTB-Tree / Hash / GIN / GiST / BRIN / SP-GiSTInverted / Bitmap / Bloom
字典编码LowCardinality（列级字典）无无Doris bitmap dict
物化视图加速MV + Projection不支持MV（手动 REFRESH）Rollup + 同步 MV
索引能否点查百行⚠️ 不擅长（稀疏）✅✅⚠️

关键差异：CK 的 Skip Index 是「跳数索引」，作用是判断这一段 Granule 能不能整段跳掉，不能定位到具体行。所以 CK 不擅长「按 id 取一行」类点查。

6. UPDATE / DELETE 实现 ​

维度ClickHouseMySQLPostgreSQLDoris
实时性异步（Mutation 后台重写 Part）实时实时（多版本元组）实时（Unique Key 模型）
并发量极差（每次重写整个 Part）高高中等
替代方案DROP PARTITION / REPLACE PARTITION / Lightweight DELETE / ReplacingMergeTree 主键去重直接 UPDATE直接 UPDATEUPDATE / DELETE 直接
影响范围阻塞 Merge锁行锁行 + autovacuum影响 compaction

生活类比：MySQL/PG 改一行像改 Word 文档；CK 改一行像把整本书拿去重新排版印刷。

最佳实践：

  • 批量删旧数据 → DROP PARTITION，秒级；
  • 批量改一列 → ALTER TABLE ... UPDATE 后耐心等；
  • 逻辑删 → Lightweight DELETE 或加 is_deleted 列；
  • 去重需求 → 写入侧排好序 + ReplacingMergeTree，少用 FINAL。

7. JOIN 能力 ​

维度ClickHouseMySQLPostgreSQLDoris
优化器较弱（24.x 起明显改善）中强强（CBO）
默认算法Hash JOIN（右表全内存）Nested Loop / HashNested Loop / Hash / MergeBroadcast / Shuffle / Colocate
大表 JOIN⚠️ 容易 OOM，需 grace_hash 或字典✅✅✅
字典加速Dictionary 替代右表（HASHED / FLAT / IP_TRIE …）无无（用临时表替代）Bitmap join 索引
ASOF / GLOBAL JOIN✅ 时序场景有无无部分

迁移建议：从 MySQL/PG 迁过来时，把高频 JOIN 的小维度表（≤ 千万行）改成 Dictionary；维度表过亿可先预聚合到大宽表里再写 CK。

8. 复制与高可用 ​

维度ClickHouseMySQLPostgreSQLDoris
复制单元表级（每张 Replicated*MergeTree）实例级（binlog）实例级（流复制 / 逻辑复制）表级（多副本自动）
协调组件ClickHouse Keeper / ZooKeeper可选 MGR / Group Replication流复制无第三方；逻辑复制有 publisherFE 高可用 + BE 多副本
同步方式异步 + 强一致写（按 quorum）异步 / 半同步同步 / 异步流复制多副本 quorum 写
主从切换无主从概念，副本平等主从主从FE 选主
多分片Distributed 引擎 + 路由sharding 中间件（Vitess / ProxySQL）Citus 扩展内置 MPP

📌 CK 的复制粒度是表：你可以让 events_raw 双副本，metrics 单副本，灵活度高于 MySQL 实例级复制。

9. 备份与恢复 ​

维度ClickHouseMySQLPostgreSQL
逻辑备份BACKUP / RESTOREmysqldumppg_dump
物理备份ALTER TABLE FREEZE 硬链 + clickhouse-backupXtraBackup / clonepg_basebackup
增量clickhouse-backup（基于 part diff）binlogWAL archive
PITR难（无 WAL）✅✅
云端归档内置 S3/OSS 支持商业物理 + WAL S3

迁移建议：CK 的「PITR 不易」是它最大的短板之一，生产必须结合 Distributed + 副本 + 定期物理快照（FREEZE PARTITION）三层兜底。

10. 生态与客户端 ​

类型ClickHouseMySQLPostgreSQL
CLIclickhouse-clientmysqlpsql
Pythonclickhouse-connect / clickhouse-driver / chdbPyMySQL / mysqlclientpsycopg v3
JavaJDBC clickhouse-jdbcJDBCJDBC
BISuperset / Metabase / Grafana / Tableau全家全家
ETLAirbyte / Debezium / Flink CDC / Kafka 引擎Canal / Debeziumlogical decoding
嵌入式chdb（DuckDB 同类）无DuckDB / Cite
分布式扩展Distributed 内置Vitess / TiDBCitus / CockroachDB

11. 选型建议（10 句话） ​

  1. OLTP 业务库：MySQL / PG，CK 一律不选。
  2. 半结构化 + 地理 + 向量：PG（一招鲜）。
  3. 大屏 / BI / 实时聚合：CK 或 Doris；想要 SQL 通透选 CK，想要 JOIN 强选 Doris。
  4. 日志检索 + 全文：CK + ngrambf_v1 / Doris 倒排 / Elasticsearch。
  5. A/B 实验 + 漏斗 + 留存：CK，windowFunnel / retention / sequenceMatch 函数链是杀手锏。
  6. 流式数仓：Flink → Kafka → CK Kafka 引擎 → MV → Agg 表。
  7. 离线 T+1 数仓：Spark + Iceberg + CK（query 端）。
  8. 多租户 / SaaS：CK 配 Profile / Quota / Row Policy 都能搞。
  9. 嵌入式 / 单机分析：chdb / DuckDB。
  10. 强一致事务：永远选 OLTP 数据库，别给 CK 上事务。

12. 迁移建议（双向） ​

12.1 MySQL → ClickHouse ​

  1. 先做表结构梳理：去掉所有 NOT NULL 约束 → 该用 LowCardinality 的列改 LC、该用 Decimal 的金额别用 Float、时间用 DateTime64(3, 'Asia/Shanghai')。
  2. 过渡架构：用 MaterializedMySQL 引擎把整库实时同步到 CK，或 Debezium → Kafka → CK Kafka 引擎。
  3. 建模转换：MySQL 范式表（订单、订单明细、用户、商品）→ CK 大宽表（pre-join）+ 字典（小维度）。
  4. JOIN 改造：高频 JOIN 改 Dictionary；超大表 JOIN 改预聚合 MV；偶尔 JOIN 用 GLOBAL JOIN。
  5. 业务侧切流：先双写双查 1 周对比 → 再切读 → 最后切写。

12.2 PostgreSQL → ClickHouse ​

  • 优势：PG 的 JSONB / Array 在 CK 都有对应（Map / Array），数据类型迁移更顺；
  • 难点：PostGIS、pgvector、MERGE 这些 PG 强项 CK 没有，要么留一份 PG 当 OLTP，要么放弃这部分能力；
  • 工具：MaterializedPostgreSQL 引擎（基于 logical decoding）做实时同步；或用 clickhouse-jdbc-bridge + postgresql 表函数做小表查询。

12.3 ClickHouse → Doris/StarRocks ​

  • SQL 方言对照：windowFunnel ↔ window_funnel、uniqExact ↔ count(distinct)、AggregatingMergeTree ↔ Aggregate Key Model；
  • 数据搬迁：先 clickhouse-client --query "SELECT ... FORMAT Parquet" dump，再 BROKER LOAD 到 Doris；
  • 双写过渡：Kafka 同时消费两边，对比 1 周；
  • 切换注意：Doris 没有 Projection 概念，但有 Rollup；CK 的字典在 Doris 中用「外部表 + bitmap join」替代。

📌 与本附录配套：appendix_cheatsheet.md（命令速查）· appendix_pitfalls.md（踩坑集）。