查询从30秒优化到2秒——分区分桶设计和索引机制全解析

-- 按天分区（最常用）
CREATE TABLE ods_user_event (
    event_time DATETIME NOT NULL,
    event_id VARCHAR(64) NOT NULL,
    user_id BIGINT,
    event_name VARCHAR(128),
    properties JSON
) ENGINE=OLAP
DUPLICATE KEY(event_time, event_id)
PARTITION BY RANGE(event_time) (
    -- 静态分区
    PARTITION p20240115 VALUES LESS THAN ('2024-01-16'),
    PARTITION p20240116 VALUES LESS THAN ('2024-01-17')
)
DISTRIBUTED BY HASH(user_id) BUCKETS 16;
 
-- 动态分区（推荐！自动创建和删除分区）
-- 保留最近30天的分区，提前创建未来3天的分区
ALTER TABLE ods_user_event SET (
    'dynamic_partition.enable' = 'true',
    'dynamic_partition.time_unit' = 'DAY',
    'dynamic_partition.start' = '-30',   -- 保留30天
    'dynamic_partition.end' = '3',       -- 提前创建3天
    'dynamic_partition.prefix' = 'p',
    'dynamic_partition.buckets' = '16'
);

-- 分桶数计算公式
-- 桶数 = 单个分区的数据量 / 单个Tablet的目标大小
-- 例：每日分区5000万条 × 每条约200字节 = 10GB/天
-- 目标单个Tablet = 500MB
-- 桶数 = 10GB / 500MB = 20个
 
-- 创建表时指定分桶
DISTRIBUTED BY HASH(user_id) BUCKETS 20;
 
-- ⚠️ 常见错误：
-- ① 桶数太少（3-5个） → 单个Tablet太大，并行度低
-- ② 桶数太多（100+）   → Tablet太多太小，元数据开销大
-- ③ 分桶列选gender     → 只有2-3个值，数据严重倾斜
-- ④ 分桶列选不在查询条件里的列 → 查询无法做桶裁剪

分桶列的选择技巧：选最常出现在WHERE条件里的高基数列。比如user_id——大部分查询都会带WHERE user_id = xxx，如果user_id是分桶列，Doris可以直接定位到具体的Tablet，跳过其他所有Tablet。这叫'桶裁剪'，效果和分区裁剪一样显著。

索引机制——第三层加速

Doris有三种索引机制，面试必须知道：

索引类型	原理	适用场景	自动/手动
前缀索引	按Key列前缀建稀疏索引	按Key的前几列做等值/范围查询	自动
Bloom Filter	按列建布隆过滤器	高基数列的等值查询（user_id/device_id）	手动指定
Bitmap索引	按列建位图索引	低基数列的过滤（status/gender/country）	手动创建

-- Bloom Filter索引（高基数列的等值查询加速）
CREATE TABLE ods_user_event (
    event_time DATETIME,
    user_id BIGINT,
    device_id VARCHAR(64),
    event_name VARCHAR(128)
) DUPLICATE KEY(event_time, user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 16
PROPERTIES (
    'bloom_filter_columns' = 'device_id,event_name'
);
 
-- Bitmap索引（低基数列的过滤加速）
CREATE INDEX idx_country ON ods_user_event (country) USING BITMAP;
 
-- 前缀索引：自动基于Key列生成
-- DUPLICATE KEY(event_time, user_id) → 前缀索引覆盖event_time和user_id
-- WHERE event_time = '2024-01-15' → 命中前缀索引，极快
-- WHERE user_id = 123（没有event_time条件）→ 无法命中前缀索引

前缀索引的坑：前缀索引只对Key列的前缀有效。如果Key是(event_time, user_id)，那WHERE event_time = xxx能命中索引，WHERE user_id = xxx（不带event_time）就命中不了。这就是为什么Key列的顺序很重要——最常查询的列要放在最前面。

实战经验：从30秒优化到2秒的全过程

在某元宇宙社交公司，用户行为明细表一开始的建表方案是这样的：

优化前：没有分区，分桶列选了event_id（每条都不一样，虽然均匀但查询从不按event_id过滤），只有3个桶。全表扫描30秒。

优化后的三步操作：

① 加按天分区 → WHERE dt = '2024-01-15'的查询只扫描1天数据，数据量减少97%。

② 分桶列改为user_id、桶数从3改为16 → 单Partition 16个Tablet并行扫描，WHERE user_id = xxx可以做桶裁剪。

③ 给device_id加Bloom Filter → WHERE device_id = xxx的查询速度提升5倍。

最终效果：同样的查询从30秒降到2秒以内。其中分区裁剪贡献最大（减少97%数据量），分桶优化其次（16路并行），索引锦上添花。

面试高频追问

追问一：分区和分桶有什么区别？

分区是宏观的数据裁剪，按时间或范围把数据分成大块，查询时跳过无关分区。分桶是微观的数据分散，按Hash把一个分区内的数据均匀分到多个Tablet上，实现并行查询。分区是减少数据量，分桶是提高并行度，两者配合使用效果最好。

追问二：桶数设多少合适？怎么算？

经验公式：桶数 = 单个分区数据量 / 单个Tablet目标大小。Tablet大小建议100MB-1GB，太小了元数据开销大、太大了并行度低。日增1000万条、每条200字节的表，一天2GB数据，设10-20个桶比较合适。

追问三：Doris的前缀索引最长多少？

前缀索引最长36字节。如果Key列的前几列总字节数超过36字节就截断了。所以不要把VARCHAR(256)这种大字段放在Key的前面，优先放INT/BIGINT/DATE这种短字段。这个细节很多人不知道，面试加分点。

总结

分区分桶和索引是Doris查询优化的三板斧。面试时记住这个优先级：① 分区裁剪效果最大（减少97%+数据量）；② 分桶优化并行度（桶数=分区数据量/500MB）；③ 索引锦上添花（前缀索引自动生效、Bloom Filter加速等值查询、Bitmap加速低基数过滤）。能讲出从30秒优化到2秒的完整过程和每一步的量化效果，面试官会很满意。

下一篇聊Doris的数据导入：Stream Load、Broker Load、Insert Into分别什么时候用？维度表每天更新怎么搞？

你们Doris表的分区分桶是怎么设计的？优化效果如何？评论区聊聊～

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