更新一条数据要全量重写整个分区？——MERGE INTO怎么高效更新删除？

-- 传统Hive的'全量重写'方式（笨重且慢）
-- 第一步：读取旧表 + 新数据，Join后去重
CREATE TABLE dim_user_new AS
SELECT COALESCE(n.user_id, o.user_id) AS user_id,
       COALESCE(n.email, o.email) AS email,
       COALESCE(n.phone, o.phone) AS phone,
       COALESCE(n.updated_at, o.updated_at) AS updated_at
FROM dim_user o
FULL OUTER JOIN user_update n ON o.user_id = n.user_id;
 
-- 第二步：删除旧表，重命名新表
DROP TABLE dim_user;
ALTER TABLE dim_user_new RENAME TO dim_user;

-- Delta Lake的MERGE INTO：高效且安全
MERGE INTO dim_user AS target
USING user_update AS source
ON target.user_id = source.user_id
WHEN MATCHED THEN
  UPDATE SET
    email = source.email,
    phone = source.phone,
    updated_at = source.updated_at
WHEN NOT MATCHED THEN
  INSERT *;

# MERGE INTO的文件操作示例
# 假设dim_user有100个Parquet文件，变更数据只涉及文件3、7、21
 
MERGE INTO前：
  文件1-100 → 所有文件都是v1版本
 
MERGE INTO后：
  文件1、2、4-6、8-20、22-100 → 仍然是v1版本（复用！）
  文件3、7、21 → 重写为v2版本（只有这3个文件！）
 
  事务日志会记录：
    add: 文件1v1、文件2v1、...、文件3v2、文件7v2、文件21v2、...
    remove: 文件3v1、文件7v1、文件21v1

-- 反例：变更数据分散，MERGE INTO退化为'全量重写'
-- 假设按date分区，但更新数据涉及所有分区
MERGE INTO fact_attribution target
USING attribution_update source
ON target.user_id = source.user_id
  AND target.event_date = source.event_date
WHEN MATCHED THEN UPDATE SET last_click_time = source.last_click_time;
 
-- 如果source数据涉及100个分区中的95个，
-- 那么MERGE INTO要重写95%的文件，性能很差

-- 反例：按date分区，但更新数据涉及所有日期
CREATE TABLE fact_attribution (
  user_id BIGINT,
  event_date DATE,  -- 按日期分区
  last_click_time TIMESTAMP
) PARTITIONED BY (event_date);
 
-- 问题：每天的更新可能涉及'过去30天'的历史数据修正，
-- 导致30个分区都要重写
 
-- 正例：按user_id分区，更新集中在少数用户
CREATE TABLE fact_attribution (
  user_id BIGINT,
  event_date DATE,
  last_click_time TIMESTAMP
) PARTITIONED BY (user_id_bucket);  -- 按user_id哈希分区
 
-- 优势：每天的更新可能只涉及5%的活跃用户，
-- 只需重写5%的分区

在我们的广告归因场景中，把分区字段从event_date改为user_id后（按user_id % 1000分桶），每天的MERGE FROM从涉及95个分区降到只有52个分区（5.2%），性能提升约3倍。

优化技巧2：Z-Ordering让相关数据聚在一起

Z-Ordering是Delta Lake特有的数据布局优化技术。它不是按单列排序，而是'按多列联合排序'，让相关数据在物理存储上尽量靠近。

-- Z-Ordering：按user_id和updated_at联合排序
OPTIMIZE dim_user ZORDER BY (user_id, updated_at);
 
-- 效果：同一个user_id的数据会被聚在一起
-- 更新user_id=123时，只会涉及少数几个文件

为什么Z-Ordering有效？传统排序只能按一列排序（如ORDER BY user_id），但MERGE INTO的JOIN条件可能涉及多列。Z-Ordering通过'多维空间填充曲线（Z-order curve）'算法，让多列的值在物理存储上尽量接近。

# 传统排序 vs Z-Ordering对比
 
传统排序（按user_id）：
  文件1: user_id 1-1000, updated_at分散
  文件2: user_id 1001-2000, updated_at分散
 
Z-Ordering（按user_id, updated_at）：
  文件1: (user_id=1, updated_at=2024-01-01), (user_id=1, updated_at=2024-01-02)
  文件2: (user_id=2, updated_at=2024-01-01), (user_id=2, updated_at=2024-01-02)
 
更新时，传统排序可能要打开很多文件，
Z-Ordering只需打开少数文件

我在出海硬件品牌实践过：对一张用户行为表（10TB）进行Z-Ordering后，MERGE INTO的性能从25分钟降到12分钟，提升约50%。代价是OPTIMIZE本身需要时间（我们选择在周末低峰期运行）。

优化技巧3：控制文件大小和数量

文件大小和数量直接影响MERGE INTO的效率。如果文件太小（几MB），会有大量小文件，元数据开销大；如果文件太大（几GB），即使只更新少量数据，也要重写整个大文件。

-- 控制Delta Lake的文件大小
-- 目标：每个文件128MB-256MB（根据数据量调整）
SET spark.sql.files.maxRecordsPerFile = 1000000;  -- 每个文件最多100万行
SET spark.sql.adaptive.coalescePartitions.enabled = true;
 
-- 定期OPTIMIZE合并小文件
OPTIMIZE dim_user;
 
-- 或者在每次写入后自动OPTIMIZE（Databricks Runtime支持）
SET spark.databricks.delta.autoOptimize.optimizeWrite = true;
SET spark.databricks.delta.autoOptimize.autoCompact = true;

最佳实践：我们通常把目标文件大小设为128MB。对于500GB的表，大约有4000个文件。如果MERGE INTO只涉及10%的文件，也就是400个，每次重写约50GB数据，这个规模是可以接受的。

优化技巧4：使用增量标记代替全量MERGE

某些场景下，可以用'增量标记 + 定期合并'代替频繁的MERGE INTO。

-- 方案A：频繁MERGE（适合实时性要求高）
-- 每小时运行一次MERGE INTO，更新少量变更
MERGE INTO dim_user ...
 
-- 方案B：增量标记 + 定期合并（适合批量更新）
-- 第一步：追加变更数据到临时表
INSERT INTO dim_user_staging SELECT * FROM user_update;
 
-- 第二步：查询时UNION最新数据
SELECT * FROM dim_user
UNION ALL
SELECT * FROM dim_user_staging;
 
-- 第三步：每天凌晨定期MERGE，合并staging到主表
MERGE INTO dim_user USING dim_user_staging ...
TRUNCATE TABLE dim_user_staging;

方案B的优势是：避免了频繁的MERGE操作，减少了文件重写次数。代价是'查询时要UNION'，稍微复杂一些。我们会在'实时性要求不高的维度表（如国家代码、地区信息）'上用这个方案。

Delta Lake vs Iceberg：MERGE实现对比

维度	Delta Lake	Apache Iceberg
语法	MERGE INTO（标准SQL）	MERGE INTO（标准SQL）
定位方式	文件级Min/Max统计	Partition + Manifest的文件级统计
文件重写	只重写包含变更的文件	只重写包含变更的文件
性能优化	Z-Ordering（多维聚类）	Sort Order + Partition Evolution
小文件处理	AUTO OPTIMIZE自动合并	Rewrite Files API手动合并
删除优化	Copy-on-Write + Delete Marker	Copy-on-Write + Merge-on-Read（Hudi有）
最佳场景	Databricks/Spark生态	多引擎（Spark/Flink/Trino）

面试高频追问

追问一：MERGE INTO失败了会怎么办？会部分更新吗？

这是面试官常问的问题，考察对ACID的理解。答案是：不会部分更新。MERGE INTO是原子操作——要么全部成功，要么全部失败。如果失败，事务日志不会提交，读者仍然读到旧版本的数据，新数据文件会被后续的Vacuum清理。

我在出海硬件品牌遇到过：MERGE INTO执行到80%时集群宕机，再次运行时，Delta Lake自动检测到'上次提交未完成'，从头开始重试，没有出现'部分数据已更新、部分未更新'的脏数据情况。

追问二：什么情况下用DELETE/UPDATE，什么情况用MERGE INTO？

DELETE/UPDATE适合'批量删除或更新'的场景，如'删除某个分区的数据'或'批量修正某个字段'。MERGE INTO适合'基于另一张表的增删改'场景，如'维度表的缓慢变化维（SCD Type 2）'。

-- 场景A：批量删除（用DELETE更直接）
DELETE FROM dim_user WHERE updated_at < '2020-01-01';
 
-- 场景B：批量更新（用UPDATE更直接）
UPDATE dim_user SET email = CONCAT(email, '@old.com') WHERE status = 'inactive';
 
-- 场景C：基于另一张表的增删改（用MERGE INTO）
MERGE INTO dim_user target USING user_update source ON ...

一般来说，DELETE/UPDATE的语法更简洁，但MERGE INTO更灵活——一个SQL解决'既有更新又有插入'的场景。

追问三：MERGE INTO可以自关联吗？

可以。MERGE INTO的USING子句可以是任意查询，包括自关联。典型场景是'去重'——对同一张表去重，保留最新记录。

-- MERGE INTO自关联：去重示例
MERGE INTO fact_attribution target
USING (
  SELECT user_id, event_date, MAX(last_click_time) AS last_click_time
  FROM fact_attribution
  GROUP BY user_id, event_date
) source
ON target.user_id = source.user_id
  AND target.event_date = source.event_date
WHEN MATCHED AND target.last_click_time < source.last_click_time THEN
  UPDATE SET last_click_time = source.last_click_time;
 
-- 这个SQL会保留每个(user_id, event_date)组合的最新记录

自关联MERGE INTO的注意事项是'确保JOIN条件唯一'，否则可能出现'一行匹配多行'的错误。

实战踩坑：MERGE INTO的'文件爆炸'问题

2023年我在某短剧出海公司遇到过这个问题。我们的用户活跃表（user_activity）每天用MERGE INTO更新，数据量约500GB。运行两个月后，我发现这个表的文件数量从4000个增长到了15000个，查询性能明显下降。

排查后发现：每次MERGE INTO都会'创建新文件、标记旧文件删除'，但没有及时物理删除旧文件。而且每次MERGE INTO产生的小文件（几十MB）没有被合并，导致文件数量激增。

-- 问题代码：只MERGE，不OPTIMIZE
MERGE INTO user_activity ...
 
-- 导致：每次MERGE产生约500个新文件，
-- 旧文件标记删除但未物理删除，
-- 2个月后：500个文件/天 × 60天 = 30000个文件
 
-- 解决方案：
-- ① 每次MERGE后自动OPTIMIZE
OPTIMIZE user_activity;
 
-- ② 定期VACUUM清理旧文件（保留7天）
VACUUM user_activity RETAIN 7 HOURS;
 
-- ③ 开启Auto Optimize（Databricks Runtime）
SET spark.databricks.delta.autoOptimize.optimizeWrite = true;

优化效果：配置Auto Optimize + 每周VACUUM后，文件数量稳定在4000个左右，查询性能恢复到初始水平。

这个坑的本质是：'MERGE INTO只负责逻辑更新，不负责物理清理'。如果没有定期OPTIMIZE和VACUUM，表会变得越来越'碎片化'。

总结

MERGE INTO是数据湖最实用的功能，避免了Hive时代的全量重写。核心原理是'文件级Min-Info + Selective Rewrite'，只重写包含变更的文件。性能关键是'让变更数据集中'——合理的分区策略、Z-Ordering、控制文件大小。面试时能说清楚'为什么比重写快、什么情况下会慢、怎么优化'，这道题就稳了。

下一篇聊一个很多人担心的问题：业务改表结构，历史数据不兼容？——Schema演进怎么处理？

你们生产环境的MERGE INTO性能怎么样？有没有遇到过'全量重写'的情况？评论区聊聊～

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长