未记录事实核验 · 状态：维护中

记录仅说明核验时间与维护状态，不保证适用于所有环境；使用前请留意正文中的版本、适用条件与风险提示。

Doris表结构设计与优化 - 性能提升的艺术

📊 业务价值体现

在追求业务增长的过程中，Doris表结构设计与优化能够带来显著的效率提升和成本节约。

在实际工作环境中，这样的挑战每天都在发生。理解相关知识不仅能解决当前问题，更能提升整体的专业能力。

💡 表结构设计核心原则

1. 数据类型选择原则

数据类型	适用场景	优化建议
TINYINT/SMALLINT/INT/BIGINT	整数数据	根据数值范围选择最小的类型
DECIMAL	精确小数	指定合适的精度和小数位
VARCHAR	变长字符串	设置合理的长度限制
CHAR	定长字符串	适用于长度固定的字段
DATE/DATETIME	日期时间	仅需日期时使用DATE，需要时分秒时使用DATETIME
BOOLEAN	布尔值	使用BOOLEAN表达布尔语义
STRING	大文本	Doris使用STRING表示大字符串；是否拆分到单独表需结合查询与存储需求评估

-- 优化前
CREATE TABLE user_info_bad (
    id VARCHAR(50),           -- 过大的字符串类型
    age VARCHAR(10),          -- 应该用整型
    create_time VARCHAR(30),  -- 应该用日期类型
    is_active VARCHAR(5),     -- 应该用布尔型
    balance DECIMAL(18, 6)    -- 精度过高
);

-- 优化后
CREATE TABLE user_info_good (
    id BIGINT,                -- 适合ID的整型
    age SMALLINT,             -- 年龄用小整型
    create_time DATE,         -- 仅适用于业务只需日期、不需时分秒的情况
    is_active BOOLEAN,       -- 布尔值
    balance DECIMAL(10, 2)    -- 仅适用于金额范围和两位小数满足业务要求的情况
);

2. 排序键设计原则

排序键（即DUPLICATE KEY、UNIQUE KEY或AGGREGATE KEY中的列）决定了数据物理存储顺序，对查询性能影响巨大。

设计原则：

• 高频查询条件放在前面
• 在匹配高频过滤条件的前提下评估低基数列前置，不能仅按基数决定顺序
• 避免排序键包含过多列
• 考虑查询的"前缀匹配"原则

-- 优化前：不考虑查询模式的排序键
CREATE TABLE sales_data_bad (
    order_id BIGINT,
    user_id BIGINT,
    product_id BIGINT,
    order_time DATETIME,
    amount DECIMAL(10, 2)
) DUPLICATE KEY(order_id);  -- 仅按订单ID排序

-- 优化后：根据查询模式设计排序键
CREATE TABLE sales_data_good (
    user_id BIGINT,
    order_time DATETIME,
    order_id BIGINT,
    product_id BIGINT,
    amount DECIMAL(10, 2)
) DUPLICATE KEY(user_id, order_time, order_id);  -- 排序键列按相同顺序声明在前，匹配常用查询条件

3. 分区策略设计

分区是Doris水平切分数据的主要方式，合理的分区设计可以显著提升查询性能。

常用分区策略：

1. 按时间分区（最常用）

-- 按天分区
CREATE TABLE daily_stats (
    stat_date DATE,
    metric_name VARCHAR(50),
    metric_value BIGINT SUM
) AGGREGATE KEY(stat_date, metric_name)
PARTITION BY RANGE(stat_date) (
    PARTITION p20230101 VALUES [('2023-01-01'), ('2023-01-02')),
    PARTITION p20230102 VALUES [('2023-01-02'), ('2023-01-03'))
);

-- 按月分区
CREATE TABLE monthly_stats (
    stat_month DATE,
    region VARCHAR(50),
    sales DECIMAL(15, 2) SUM
) AGGREGATE KEY(stat_month, region)
PARTITION BY RANGE(stat_month) (
    PARTITION p202301 VALUES [('2023-01-01'), ('2023-02-01')),
    PARTITION p202302 VALUES [('2023-02-01'), ('2023-03-01'))
);

1. 动态分区（自动管理分区）

-- 配置动态分区
ALTER TABLE daily_stats SET (
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-7",  -- 设置相对当前日期的历史保留边界；边界之外的旧分区可能被自动删除
    "dynamic_partition.end" = "3",     -- 设置相对当前日期向后3天的分区创建边界
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "10"
);

1. 按区域/类别分区（List分区）

-- 按地区分区
CREATE TABLE regional_sales (
    region VARCHAR(50),
    city VARCHAR(50),
    sales DECIMAL(15, 2) SUM
) AGGREGATE KEY(region, city)
PARTITION BY LIST(region) (
    PARTITION north VALUES IN ("北京", "天津", "河北"),
    PARTITION east VALUES IN ("上海", "江苏", "浙江"),
    PARTITION south VALUES IN ("广东", "广西", "福建")
);

4. 分桶策略设计

分桶是分区内的进一步数据划分，影响并行度和查询性能。

分桶数量估算参考：

• 可按每个分区的数据量(GB) / 单桶期望大小(GB)估算初始桶数，再按实际负载调整
• 原稿单桶大小建议为1GB-10GB，未提供适用负载及验证依据，仅作经验起点
• 原稿列举2的幂次（8/16/32/64），这不是必须遵守的规则
• 原稿以BE节点数 * [4-8]作估算，未提供验证依据；需结合节点资源、并发、倾斜与Tablet数量测试

-- 小表（<1GB）：少量分桶
CREATE TABLE small_table (
    id BIGINT,
    name VARCHAR(50)
) DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 4;  -- 4个桶足够

-- 中表（1GB-100GB）：适中分桶
CREATE TABLE medium_table (
    id BIGINT,
    data VARCHAR(200)
) DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 16;  -- 16个桶适中

-- 大表（>100GB）：多分桶
CREATE TABLE large_table (
    id BIGINT,
    data VARCHAR(500)
) DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 64;  -- 64个桶提高并行度

5. 索引设计策略

Doris支持多种索引类型，合理使用可大幅提升查询性能。

前缀索引：

• 自动构建稀疏前缀索引，前缀最多36字节；遇到VARCHAR列时前缀会截断，不能简单理解为完整覆盖排序键的前36个字节
• 排序键顺序直接影响索引效率

Bloom Filter索引：

• 适用于高基数列的等值查询

-- 为高基数列添加Bloom Filter
ALTER TABLE user_behaviors SET ("bloom_filter_columns" = "user_id");

Bitmap索引：

• 适用于低基数列的等值查询

-- 为低基数列添加Bitmap索引
ALTER TABLE user_behaviors ADD INDEX idx_gender (gender) USING BITMAP;

🚀 性能优化技巧

1. 表压缩优化

-- 设置压缩算法
CREATE TABLE compressed_table (
    id BIGINT,
    data VARCHAR(500)
) DUPLICATE KEY(id)
PROPERTIES (
    "compression" = "zstd"  -- 可选：lz4, zlib, zstd, snappy
);

2. 冷热数据分离

-- 设置数据存储介质
CREATE TABLE tiered_storage_table (
    id BIGINT,
    create_time DATETIME,
    data VARCHAR(200)
) DUPLICATE KEY(id)
PARTITION BY RANGE(create_time) (
    PARTITION p202201 VALUES [('2022-01-01'), ('2022-02-01')) ("storage_medium" = "SSD"),
    PARTITION p202202 VALUES [('2022-02-01'), ('2022-03-01')) ("storage_medium" = "SSD"),
    PARTITION p202101 VALUES [('2021-01-01'), ('2021-02-01')) ("storage_medium" = "HDD")
);

-- 设置冷却时间
ALTER TABLE tiered_storage_table SET ("storage_cooldown_time" = "2022-01-01 00:00:00");

3. 分区裁剪优化

-- 利用分区裁剪提升查询性能
EXPLAIN SELECT * FROM monthly_stats
WHERE stat_month >= '2023-01-01' AND stat_month < '2023-02-01';
-- 检查执行计划中的分区扫描信息，确认是否仅扫描p202301分区

4. 数据倾斜处理

-- 处理Hash分桶的数据倾斜
-- 不好的分桶列选择（可能导致倾斜）
CREATE TABLE skewed_table_bad (
    user_id BIGINT,
    gender TINYINT,  -- 低基数列，容易倾斜
    data VARCHAR(200)
) DUPLICATE KEY(user_id, gender)
DISTRIBUTED BY HASH(gender) BUCKETS 16;  -- 不好的分桶列

-- 优化后的分桶设计
CREATE TABLE skewed_table_good (
    user_id BIGINT,
    gender TINYINT,
    data VARCHAR(200)
) DUPLICATE KEY(user_id, gender)
DISTRIBUTED BY HASH(user_id) BUCKETS 16;  -- 高基数列分桶

⚠️ 常见问题与解决方案

1. 查询性能差

• 问题：某些查询特别慢
• 解决方案：
  • 检查排序键是否与查询条件匹配
  • 确认分区裁剪是否生效
  • 考虑添加适当的索引
  • 使用EXPLAIN分析查询计划

2. 写入性能差

• 问题：数据导入速度慢
• 解决方案：
  • 减少排序键列数
  • 优化分桶策略，避免热点
  • 批量导入代替单条插入
  • 调整BE节点参数

3. 存储空间占用大

• 问题：表占用空间过大
• 解决方案：
  • 选择合适的数据类型
  • 使用更高效的压缩算法
  • 设置合理的分区策略，及时删除旧数据
  • 对于Aggregate模型，仅在业务允许改变聚合粒度时减少维度列，避免丢失所需明细

✅ 掌握检查

• [ ] 能根据业务场景选择合适的数据类型
• [ ] 掌握排序键设计的核心原则
• [ ] 能设计合理的分区分桶策略
• [ ] 了解如何处理数据倾斜问题

🔗 学习连接

前置知识: 数据模型设计 下一步: 数据导入方案

---

#Doris #表设计 #分区分桶 #索引优化 #性能调优