从 MySQL/PostgreSQL 迁移的 SQL 差异

1. 迁移的认知前提

从 MySQL/PostgreSQL 迁到 ClickHouse，最容易踩的坑不是语法，而是心智模型。行式数据库假设「数据可原地更新、主键唯一、事务强一致」，而 ClickHouse 假设「数据只追加、主键只用于排序、查询高度并行」。把 OLTP 的表结构原样搬过来，往往会得到一张「能建、能写、但查询奇慢」的表。

迁移前先明确三件事：

• 目标是什么：是替换在线业务库，还是承接分析负载？后者才是 ClickHouse 的主场；
• 哪些查询要保留：逐条列出必须支持的查询，据此反推 Schema，而不是照搬原表；
• 能不能接受最终一致：ClickHouse 没有跨行事务，涉及强一致的逻辑必须在应用层补偿。

如果只是想在 ClickHouse 里直接查 MySQL/PG 的数据而不搬迁，可以先看 /clickhouse-federated-queries-external/ 的联邦查询方案——它适合做过渡与对账，但不适合承载高频分析。

2. 数据类型映射

2.1 常见映射表

2.2 Nullable 的代价

行式数据库里 NULL 是免费的，但在 ClickHouse 里 Nullable(T) 会额外存储一个掩码列，并且会拖慢大部分函数（它们需要先检查 null 掩码）。因此：

-- 不推荐：几乎全部列都 Nullable
CREATE TABLE t_bad (
    id Nullable(UInt64),
    name Nullable(String),
    amount Nullable(Decimal(18,2))
) ENGINE = MergeTree ORDER BY id;

-- 推荐：用默认值代替 NULL，只在语义必需时用 Nullable
CREATE TABLE t_good (
    id UInt64,
    name String DEFAULT '',
    amount Decimal(18,2) DEFAULT 0
) ENGINE = MergeTree ORDER BY id;

经验法则：只有「空」与「零」有明确语义区别时才用 Nullable，例如「未评分」与「评分为 0」。

2.3 LowCardinality 与枚举

MySQL 的 ENUM、PG 的 VARCHAR 低基数列，在 ClickHouse 里优先用 LowCardinality(String)：

event_type LowCardinality(String),
country     LowCardinality(String)

LowCardinality 会做字典编码，存储与过滤都更快。但注意：基数高的列不要用（如 user_id、request_id），字典本身会成为负担。基数阈值大致是万级以下。

3. 建表与主键语义

3.1 主键不是唯一约束

这是最容易误解的一点：

-- MySQL：PRIMARY KEY 保证唯一
CREATE TABLE events (id BIGINT PRIMARY KEY, ...);

-- ClickHouse：ORDER BY 只定义排序，不保证唯一
CREATE TABLE events (
    id UInt64,
    event_time DateTime,
    payload String
) ENGINE = MergeTree
ORDER BY (id, event_time);

在 ClickHouse 里，相同 id 可以存在多行。ORDER BY 决定的是数据在 part 内如何排序，进而决定主键索引（稀疏索引）的裁剪能力。所以选择 ORDER BY 列的依据是查询的过滤模式，而不是业务唯一性。

若确实需要唯一性，用 ReplacingMergeTree 或查询时 GROUP BY 去重。

3.2 主键列的选择原则

ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (tenant_id, event_time)

原则：

1. 低基数在前：把等值过滤的低基数列（tenant_id、event_type）放前面；
2. 时间列在后：时间范围过滤依赖排序键的后续列；
3. 与 PARTITION BY 配合：分区列通常是时间，ORDER BY 里也应包含时间以便分区内裁剪；
4. 不超过 4~5 列：过长的排序键会增大索引与合并成本。

这与 /clickhouse-schema-modeling-best-practices/ 里讨论的建模原则完全一致，迁移时应把它当成一次重新建模，而不是字段搬运。

3.3 分区策略

PARTITION BY toYYYYMM(event_time)   -- 按月，适合中大数据量
PARTITION BY toDate(event_time)    -- 按天，适合超大数据量或需要按天清理
PARTITION BY tuple()               -- 不分区，适合小表

MySQL 的分区键习惯不能直接套用：ClickHouse 分区过多会拖慢合并（每个分区独立维护 part）。经验上单表分区数控制在几百到几千以内。分区键要选能配合 TTL / DROP PARTITION 做数据清理的列。

4. SQL 语法差异

4.1 JOIN 语法

MySQL 的 JOIN 会按优化器自由重排，而 ClickHouse 严格按书写顺序执行，右表会被构建成内存哈希表：

-- ClickHouse 中右表（dim_users）会被加载进内存
SELECT e.event_type, u.region, count()
FROM events AS e
LEFT JOIN dim_users AS u ON e.user_id = u.user_id
GROUP BY e.event_type, u.region;

因此：

• 小表放右边，大表放左边；
• 多表 JOIN 时从左到右依次探测；
• 分布式表 JOIN 需要 GLOBAL JOIN 或把维表做成字典。

ClickHouse 不支持 RIGHT JOIN 的部分场景，FULL OUTER JOIN 语义也有差异，迁移时逐条验证。

4.2 GROUP BY 与别名

ClickHouse 允许在 GROUP BY / WHERE 里直接引用 SELECT 中的别名，这比 MySQL 宽松，但比 PG 更灵活：

SELECT
    toStartOfHour(event_time) AS hour,
    count() AS cnt
FROM events
GROUP BY hour          -- 直接引用别名
ORDER BY hour;

注意 HAVING 与 WHERE 的执行顺序与行式数据库一致，但 ClickHouse 对 GROUP BY 的基数控制更敏感，聚合前应尽量用 WHERE 减少输入行。

4.3 子查询与 CTE

ClickHouse 支持标准 CTE（WITH ... AS (...)），也支持 MySQL 的派生表：

WITH hourly AS (
    SELECT toStartOfHour(event_time) AS h, count() AS c
    FROM events GROUP BY h
)
SELECT avg(c) FROM hourly;

差异点：

• ClickHouse 有标量 CTE：WITH (SELECT max(x) FROM t) AS mx SELECT ... WHERE x = mx；
• 相关子查询（correlated subquery）支持有限，通常需改写为 JOIN；
• IN (subquery) 支持，但大集合建议改用 JOIN 或字典。

4.4 窗口函数

基本语法与标准 SQL 一致，但支持范围更窄：

SELECT
    user_id,
    event_time,
    row_number() OVER (PARTITION BY user_id ORDER BY event_time) AS rn,
    sum(value) OVER (PARTITION BY user_id ORDER BY event_time
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum
FROM events;

已知差异：RANGE 帧的部分形态支持有限，GROUPS 帧通常不支持。迁移前对每个窗口函数做一次结果比对。窗口函数的高级用法可参考 /clickhouse-window-functions-advanced-sql/。

5. 常用函数对照

其中 uniq() 这类近似函数是 ClickHouse 的特色：它用 HyperLogLog 估算基数，误差约 0.5%，但速度与内存占用远优于 uniqExact()。迁移时应主动把 COUNT(DISTINCT x) 换成 uniq(x)。

6. UPDATE / DELETE 与事务

行式数据库里随手写的 UPDATE ... WHERE id = ?，在 ClickHouse 里是昂贵且异步的操作：

-- 轻量删除（推荐）
DELETE FROM events WHERE user_id = 10086;

-- 重量级 mutation（异步，重写 part）
ALTER TABLE events DELETE WHERE user_id = 10086;
ALTER TABLE events UPDATE value = 0 WHERE user_id = 10086;

ClickHouse 不支持：

• BEGIN / COMMIT / ROLLBACK 跨语句事务；
• 行级锁；
• 外键约束。

因此任何依赖事务的业务逻辑都要在应用层重新设计。数据变更的完整语义（掩码、合并、ReplacingMergeTree）见 /clickhouse-mutation-ttl-deep-dive/。

7. 迁移工具与流程

7.1 工具选型

用 MySQL 引擎表做一次性搬迁是最直接的：

CREATE TABLE events AS mysql_source_table
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (tenant_id, event_time);

INSERT INTO events SELECT * FROM mysql('host:3306', 'db', 'source', 'user', 'pass');

注意这里 CREATE TABLE AS 只复制列，引擎与排序键由你显式指定——这正是重新建模的机会。

7.2 校验清单

迁移完成后必须逐项核对：

-- 行数核对
SELECT count() FROM events;                    -- ClickHouse
-- SELECT COUNT(*) FROM source_table;          -- 原库

-- 抽样比对
SELECT tenant_id, count(), sum(amount)
FROM events
WHERE event_time >= '2026-01-01'
GROUP BY tenant_id
ORDER BY tenant_id
LIMIT 20;

-- 关键聚合比对（用 uniqExact 保证精确）
SELECT uniqExact(user_id) FROM events;

对每个业务查询，用相同输入跑新旧两套 SQL，比对结果集。数值类型（Decimal/Float）与空值处理是最常出偏差的地方，务必逐列核对。

7.3 回滚预案

迁移期间保持双写或原库只读，确认 ClickHouse 侧查询正确后再切流。保留原库至少一个完整的业务周期（如一个月），以便随时回退。更多迁移工程化实践可参考 数据库迁移策略 。

7.4 用引擎表做实时联邦核对

迁移的过渡期里，可以用 MySQL/PG 引擎表直接对账，无需额外 ETL：

CREATE TABLE mysql_events
ENGINE = MySQL('host:3306', 'db', 'events', 'user', 'pass');

-- 同一时间窗的聚合对比
SELECT
    (SELECT count() FROM events WHERE event_date = today())        AS ch_cnt,
    (SELECT count() FROM mysql_events WHERE event_date = today())  AS src_cnt;

引擎表按需拉取远端数据，适合小批量核对；大批量对账仍建议导出为文件后 INSERT 比对，避免远端库压力过大。

8. 常见坑

• 照搬 PRIMARY KEY 做唯一键：ClickHouse 主键不唯一，业务唯一性需自行保证。
• 滥用 Nullable：性能与存储双输，改用默认值。
• COUNT(DISTINCT) 不换 uniq：大表上会慢一个数量级。
• JOIN 顺序随意：右表进内存，写反了会 OOM。
• 把 ORDER BY 当选唯一列：应根据过滤模式选，不是照抄主键。
• 依赖事务：ClickHouse 无跨行事务，需应用层补偿。
• 字符串长度沿用 VARCHAR(255) 心智：ClickHouse String 无长度限制，定长才用 FixedString。
• 把自增主键当排序键：自增 ID 作为唯一排序键会失去时间维度的裁剪能力，应改为 (维度, 时间) 组合。
• 沿用 LIMIT offset, n 深分页：ClickHouse 的深分页同样低效，建议改用游标（WHERE id > last_id）或 LIMIT n BY。

小结

从 MySQL/PostgreSQL 迁移到 ClickHouse，本质是一次从「行式事务模型」到「列式分析模型」的思维切换。数据类型要重选（Nullable 慎用、LowCardinality 善用）、主键要重定义（按过滤模式选排序键）、SQL 要重写（JOIN 顺序、近似函数、窗口限制）、变更逻辑要重设计（无事务、异步 mutation）。把迁移当成「重新建模 + 逐查询比对」的工程，而不是字段搬运，才能既拿到 ClickHouse 的性能，又避免线上事故。

落地节奏建议分三步走：先迁一张只读的分析表验证链路与结果正确性，再迁写入量大但变更少的日志/事件表积累信心，最后才动涉及更新删除的业务表。每一步都保留回退路径，用真实查询做灰度比对，而不是一次性全量切换。

最后提醒一点：迁移不是一次性工程，而是一次建模能力的升级。把原库里那些「为了迁就行式引擎而做的反范式设计」重新审视一遍，往往能在 ClickHouse 里找到更简单、更省空间的表达。