分区分桶选错了，查询很慢？——分区策略和动态分区怎么设计？

-- 错误的分区设计：按user_id前4位分区
CREATE TABLE user_profile (
  user_id STRING,
  age INT,
  gender STRING,
  country STRING,
  ...
)
PARTITIONED BY (user_id_prefix STRING);  -- user_id前4位
 
-- 插入数据时计算user_id前4位
INSERT INTO TABLE user_profile PARTITION (user_id_prefix)
SELECT user_id, age, gender, country,
       SUBSTR(user_id, 1, 4) as user_id_prefix  -- 前4位
FROM source_user_profile;

-- 正确的分区设计示例：电商订单表
CREATE TABLE orders (
  order_id STRING,
  user_id STRING,
  product_id STRING,
  amount DECIMAL,
  ...
)
PARTITIONED BY (dt STRING, country STRING);  -- 按日期+国家分区
 
-- 查询示例：可以充分利用分区剪枝
SELECT * FROM orders
WHERE dt='2024-03-19'              -- 只扫描2024-03-19的分区
  AND country='US'                 -- 只扫描US的分区
  AND amount > 100;                -- 再用amount过滤

-- 分桶示例：按user_id分100个桶
CREATE TABLE user_behavior (
  user_id STRING,
  event_type STRING,
  event_time TIMESTAMP,
  ...
)
CLUSTERED BY (user_id) INTO 100 BUCKETS;  -- 按user_id哈希分100桶
 
-- 插入数据时自动分桶
INSERT INTO TABLE user_behavior
SELECT user_id, event_type, event_time, ...
FROM source_data;
 
-- 查询时可以利用分桶加速
SELECT * FROM user_behavior
WHERE user_id='12345';  -- 只需要扫描1个桶（1/100的数据）

分桶 vs 分区：分区是'按列值划分目录'（如dt=2024-03-19是一个目录），分桶是'按哈希值划分文件'（如user_id哈希值%100=0的在一个文件）。分区适合'基数小、查询频繁'的列（如dt、country），分桶适合'基数大、经常Join'的列（如user_id、order_id）。

实战经验：我们在出海硬件品牌做过测试：'按dt分区+按user_id分100桶'的表，查询'WHERE dt='2024-03-19' AND user_id='12345''只需要扫描1个分区的1个桶（1/36500的数据），而'只按dt分区'需要扫描整个分区的所有数据。查询延迟从5分钟降到20秒。

动态分区覆盖：只覆盖'有数据的分区'

动态分区覆盖（Dynamic Partition Overwrite）是Delta Lake/Iceberg的功能，可以'只覆盖有数据的分区'，不影响其他分区。这在'部分数据更新'场景非常有用。

-- 传统Hive：INSERT OVERWRITE会覆盖整个分区
INSERT OVERWRITE TABLE user_behavior
PARTITION (dt='2024-03-19')
SELECT * FROM staging_data WHERE dt='2024-03-19';
-- 问题：如果staging_data只有hour=10的数据，
-- hour=11、12...的数据也会被删除（整个分区被覆盖）
 
-- Delta Lake：动态分区覆盖
SET spark.sql.sources.partitionOverwriteMode=dynamic;
INSERT OVERWRITE TABLE user_behavior
SELECT * FROM staging_data WHERE dt='2024-03-19' AND hour='10';
-- 只覆盖dt=2024-03-19/hour=10的子分区
-- 其他子分区（hour=11、12...）不会被影响

实战案例：我们在出海硬件品牌用动态分区覆盖做'数据修复'：某个小时的数据有问题，从备份数据修复后重新插入，只覆盖该小时的分区，不影响其他小时。避免了'整个天的数据被覆盖'的问题。

常见分区设计错误

错误设计	问题	正确做法
按user_id分区	百万级分区，每个分区几MB	按dt分区+user_id分桶
按timestamp（精确到秒）分区	无限增长，每个分区几行	按dt（天）或dt+hour分区
按status分区（只有3个值）	基数太小，无法剪枝	不用分区，用索引
按dt+hour+minute分区	1440个分区/天，太多	按dt或dt+hour分区
不分区	全表扫描，慢	按高频查询列分区

面试高频追问

追问一：分区数越多越好吗？

不是。分区数太多（>1万）会导致三个问题：1）Hive Metastore压力大（需要存储大量分区元数据）；2）查询时需要'打开大量目录'，小文件问题；3）Spark任务会产生大量小任务（每个分区一个任务）。理想的分区数是100-10000个，每个分区1-10GB。

追问二：什么时候用分桶？什么时候用分区？

分区适合'基数小、高频查询'的列（如dt、country），分桶适合'基数大、经常Join'的列（如user_id、order_id）。例如：电商订单表，按dt分区（365个），按user_id分100桶；查询'WHERE dt='...' AND user_id='...''可以只扫描1个分区的1个桶。如果Join两个表都按user_id分桶，可以直接'桶Join'（Bucket Join），避免Shuffle。

追问三：动态分区覆盖和普通INSERT OVERWRITE有什么区别？

普通INSERT OVERWRITE会'覆盖整个表或整个分区'，即使新数据只有部分分区，其他分区也会被清空。动态分区覆盖只会'覆盖有数据的分区'，其他分区保持不变。例如：INSERT OVERWRITE ... WHERE dt='2024-03-19' AND hour='10'，如果只开启hour=10的数据，动态分区覆盖只会覆盖dt=2024-03-19/hour=10，不会影响hour=11、12...

实战踩坑：分区爆炸导致查询超时

2023年在某出海硬件品牌，我们遇到过一次'分区爆炸导致查询超时'的问题。当时我们上线了一个'用户行为分析'项目，表设计是'dt+hour+event_type'三列分区：

-- 错误的分区设计：三级分区
CREATE TABLE user_behavior (
  user_id STRING,
  event_time TIMESTAMP,
  ...
)
PARTITIONED BY (dt STRING, hour STRING, event_type STRING);
 
-- 结果：分区数 = 365天 × 24小时 × 50个事件类型 = 438000个分区

运行3个月后，分区数增长到44万个，每次查询Spark UI显示'收集分区元数据'就要5分钟，查询还没开始就超时了。

排查发现三个问题：1）分区数太多（44万），Hive Metastore压力大；2）大部分分区只有几十KB数据（小文件问题）；3）Spark任务产生大量小任务（每个分区一个任务），任务调度开销巨大。

解决方案：我们做了三件事：1）简化分区：从'dt+hour+event_type'改为'dt+event_type'（分区数从44万降到1.8万）；2）定期合并小文件：每周OPTIMIZE；3）增加分区监控：如果分区数>2万，报警。优化后查询延迟从超时（>10分钟）降到1分钟。

-- 优化后的分区设计：只按dt+event_type分区
CREATE TABLE user_behavior_optimized (
  user_id STRING,
  event_time TIMESTAMP,
  hour STRING,  -- hour变成普通列，不分区
  ...
)
PARTITIONED BY (dt STRING, event_type STRING);
 
-- 分区数 = 365天 × 50个事件类型 = 18250个分区（合理范围）
-- 查询时仍然可以用hour过滤
SELECT * FROM user_behavior_optimized
WHERE dt='2024-03-19'
  AND event_type='page_view'
  AND hour='10';  -- hour是普通列，也可以过滤

总结

分区设计是数据湖性能的基础，核心原则是：基数适中（100-10000个分区）、每个分区大小合理（1-10GB）、分区列要是高频查询的过滤列。常见错误是：按user_id分区（太多分区）、按timestamp精确到秒（无限增长）、不分区（全表扫描）。分桶可以解决数据倾斜和优化Join。动态分区覆盖可以只覆盖有数据的分区。面试时能说清楚'分区设计原则、分桶vs分区的区别、实战踩坑经验'，就很稳。

下一篇聊一个很多人纠结的问题：Parquet、ORC、Avro选哪个？——列存、压缩、谓词下推怎么权衡？

你们生产环境的分区设计是什么样的？评论区聊聊～

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