用 ClickHouse 替换 MySQL 做数据分析：查询速度提升 100 倍
Replacing MySQL with ClickHouse for Analytics: 100x Query Speed Improvement
| Ryan Zhang | 2026-08-12T11:16:17
业务分析的 SQL 查询在 MySQL 上动辄几十秒，换成 ClickHouse 后大部分查询在 1 秒内返回。这篇分享迁移过程。
Business analytics SQL queries taking tens of seconds on MySQL now return within 1 second on ClickHouse.
## 痛点 我们的业务分析需求越来越多，但分析查询全跑在 MySQL 上。典型的痛点： ```sql -- 统计过去 30 天每天每个渠道的 GMV SELECT DATE(created_at) AS day, channel, SUM(amount) AS gmv FROM orders WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY day, channel ORDER BY day; ``` orders 表 5000 万行，这条 SQL 在 MySQL 上要跑 **38 秒**。 更复杂的分析（多表 JOIN + 多维聚合）直接超过 2 分钟。分析师抱怨等不起，DBA 担心分析查询影响线上 OLTP 性能。 ## 为什么选 ClickHouse | 特性 | MySQL | ClickHouse | |------|-------|------------| | 存储模型 | 行式 | 列式 | | 压缩比 | ~2:1 | ~10:1 | | 聚合查询 | 慢（全表扫描） | 极快（列裁剪+向量化） | | 写入模式 | 单行事务 | 批量追加 | | 适合场景 | OLTP | OLAP | 列式存储的优势：聚合查询只读取需要的列。如果表有 50 列但 SQL 只用到 3 列，ClickHouse 只扫描这 3 列的数据。加上 LZ4 压缩，IO 减少 90%+。 ## 架构设计 ``` MySQL (OLTP) → Canal → Kafka → ClickHouse (OLAP) ↑ 分析师直连查询 ``` 不是替换 MySQL，是在旁边加一个分析型数据库。MySQL 继续处理在线事务，ClickHouse 专门跑分析。用 Canal 做 binlog 同步，通过 Kafka 做缓冲。 ## ClickHouse 建表 ```sql CREATE TABLE orders_analytics ( id UInt64, user_id UInt64, channel LowCardinality(String), -- 低基数列用 LowCardinality amount Decimal(18, 2), status UInt8, created_at DateTime, updated_at DateTime ) ENGINE = MergeTree() PARTITION BY toYYYYMM(created_at) -- 按月分区 ORDER BY (created_at, channel) -- 排序键 TTL created_at + INTERVAL 2 YEAR; -- 自动过期 ``` 关键设计： - **分区**：按月分区，查询特定时间范围时只扫描相关分区 - **排序键**：按查询最常用的过滤和聚合列排序 - **TTL**：超过 2 年的数据自动清理 - **LowCardinality**：枚举类字段用字典编码，极大提升压缩率 ## 同步配置 用 Kafka Connect + ClickHouse Sink Connector，配置简化为： ```json { "connector.class": "com.clickhouse.kafka.connect.ClickHouseSinkConnector", "topics": "mysql-orders", "hostname": "clickhouse-host", "database": "analytics", "table": "orders_analytics" } ``` 同步延迟通常在 **1-3 秒**，对分析场景完全可以接受。 ## 性能对比 | 查询 | MySQL | ClickHouse | 提升 | |------|-------|------------|------| | 30 天日维度 GMV | 38s | 0.3s | 127x | | 用户渠道分布 | 25s | 0.15s | 167x | | 多表 JOIN 聚合 | 120s+ | 2.1s | 57x | | 全表 COUNT | 12s | 0.05s | 240x | 最快的提升 240 倍，最慢的也有 57 倍。平均 **100 倍以上**。 ## 存储对比 5000 万行订单数据： - MySQL：约 12GB - ClickHouse：约 1.8GB（压缩比 6.7:1） ## 踩坑 1. **不要用 ClickHouse 做 OLTP**：单行 UPDATE/DELETE 性能极差 2. **批量写入**：每次至少写入 1000+ 行，不要一行行插入 3. **不支持事务**：没有 BEGIN/COMMIT，不适合需要强一致性的场景 4. **JOIN 性能一般**：大表 JOIN 建议先做预聚合或用物化视图 ## 总结 ClickHouse 是我用过的性价比最高的 OLAP 引擎。如果你的分析查询在 MySQL 上跑得慢，强烈建议试试。
Moved analytics queries from MySQL to ClickHouse with Canal+Kafka sync pipeline. 50M row order table queries improved 57-240x (average 100x+). Storage reduced from 12GB to 1.8GB (6.7:1 compression). Key design: monthly partitions, optimized sort keys, LowCardinality for enum columns, 2-year TTL. Pitfalls: don't use for OLTP, always batch writes (1000+ rows), no transaction support, JOINs need pre-aggregation.