AI 助理
文档备案控制台
官方文档
首页 云数据库 SelectDB 版 最佳实践 查询优化实践 优化表Schema设计

优化表Schema设计

更新时间：
复制 MD 格式
产品详情
我的收藏

表Schema设计直接影响数据分布、查询并行度与排序效率。不合理的设计常导致数据倾斜、排序失效、计算开销增大。本文通过典型案例说明如何选择表模型、分桶列、Key列及字段类型，并提供诊断命令帮助排查性能问题。

调优Checklist

在创建或优化表结构时，建议逐项检查：

  • 是否选择了与业务匹配的表模型（Duplicate / Unique / Aggregate）？

  • 分桶列是否散列均匀，无null或固定值倾斜？

  • 高频等值或范围查询列是否定义为Key列？

  • 字段类型是否遵循「定长优先、精确优先」原则？

  • 分区策略是否支持有效裁剪？

案例1：选择合适的表模型

阿里云SelectDB支持以下数据模型，不同模型在查询性能和数据更新能力上各有侧重：

表模型

查询性能

是否支持更新

典型场景

Duplicate

最高。

不支持。

日志、明细数据的高性能查询。

Unique（Merge-on-Write）

较高。

支持。

需要主键去重且对查询性能要求较高。

Unique（Merge-on-Read）

一般。

支持。

需要主键去重且写入频繁。

Aggregate

一般。

聚合更新。

预聚合报表、指标汇总。

查询性能排序：Duplicate > Merge-on-Write > Merge-on-Read ≈ Aggregate。

-- Duplicate模型：适用于明细数据查询，性能最优
CREATE TABLE user_events (
    event_time DATETIME NOT NULL,
    user_id BIGINT NOT NULL,
    event_type VARCHAR(32),
    event_data TEXT
)
DUPLICATE KEY(event_time, user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS AUTO;

-- Aggregate模型：适用于预聚合报表
CREATE TABLE site_traffic_summary (
    site_id INT,
    visit_date DATE,
    pv BIGINT SUM,
    uv BIGINT MAX
)
AGGREGATE KEY(site_id, visit_date)
DISTRIBUTED BY HASH(site_id) BUCKETS AUTO;

优化建议

  • 业务无数据更新需求且对查询性能要求高时，优先使用Duplicate模型。

  • 需要数据更新（如CDC同步、Upsert场景）时，使用Unique模型。Unique模型默认启用Merge-on-Write模式，读取性能接近Duplicate模型。如果写入频率极高且可接受较低的读取性能，可通过设置enable_unique_key_merge_on_write = false切换为Merge-on-Read模式。

  • 固定维度的聚合报表场景使用Aggregate模型，数据在导入时即完成聚合计算，减少存储和查询时的计算量。

案例2：选择合适的分桶列

分桶列决定数据在集群内的分布方式。选择不当会导致数据倾斜，严重影响查询并行度和性能。

反例：分桶列导致数据倾斜

当分桶列存在大量null值或固定值时，所有数据会集中到少数桶中，其余桶为空。以下示例中，c2列全部为null，导致10000行数据全部集中在8个桶中的1个桶内：

-- 反例：分桶列c2全为null，所有数据集中在1个桶中
CREATE TABLE t1 (c1 INT, c2 INT)
DUPLICATE KEY(c1)
DISTRIBUTED BY HASH(c2) BUCKETS 8;

INSERT INTO t1 SELECT number, null FROM numbers('number'='10000');

-- 检查数据分布：结果仅1行，count为10000，说明所有数据集中在同一个桶
SELECT c2, count(*) cnt FROM t1 GROUP BY c2 ORDER BY cnt DESC LIMIT 10;

优化方案

改用散列度高的列（如用户ID、订单ID）作为分桶列，确保数据均匀分布在所有桶中：

-- 优化：使用散列度高的user_id作为分桶列
CREATE TABLE t1 (c1 INT, user_id BIGINT NOT NULL)
DUPLICATE KEY(c1)
DISTRIBUTED BY HASH(user_id) BUCKETS AUTO;

排查数据倾斜

对已有表执行以下查询，检查分桶列的值分布是否均匀：

-- 将 bucket_col 替换为分桶列名，table_name 替换为表名
SELECT bucket_col, count(*) cnt
FROM table_name
GROUP BY bucket_col
ORDER BY cnt DESC
LIMIT 10;

如果排名靠前的值对应的count远高于其他值，说明存在数据倾斜，需要更换分桶列。

分桶列选择原则

  • 避免使用存在大量null或固定值的列。

  • 优先选择散列度高（基数大）的字段，如用户ID、订单ID。

  • 使用BUCKETS AUTO让系统根据数据量自动调整分桶数。手动设置时，建议每个桶的数据量在100 MB～1 GB之间。

  • 如果两张表经常进行Join查询，使用相同的分桶列和分桶数可以启用Colocate Join，避免数据Shuffle开销。

案例3：优化Key列排列顺序

SelectDB按Key列的定义顺序对数据排序存储，并基于前缀构建前缀索引（取前36字节）。Key列的排列顺序直接影响前缀索引的命中率和查询过滤效率。

业务场景

假设业务查询经常按user_id做等值查询或范围查询：

-- 高频查询模式
SELECT * FROM user_events WHERE user_id = 12345;
SELECT * FROM user_events WHERE user_id IN (123, 456, 789);
SELECT * FROM user_events WHERE user_id = 12345 AND event_time >= '2024-01-01';

优化方案

将高频等值查询列user_id放在Key定义的首位，范围查询列event_time放在其后：

-- 推荐：高频等值查询列在前，范围查询列在后
CREATE TABLE user_events (
    user_id BIGINT NOT NULL,
    event_time DATETIME NOT NULL,
    event_type VARCHAR(32),
    payload TEXT
)
DUPLICATE KEY(user_id, event_time)
DISTRIBUTED BY HASH(user_id) BUCKETS AUTO;

Key列设计原则

  • 高频过滤列在前：将查询中最常出现在WHERE条件中的列放在Key定义的前面位置。

  • 高基数列在前：在查询频率相近时，优先将区分度高的列放前面，提升索引过滤效果。

  • 控制Key列数量：Key列过多会增加写入时排序和compaction的开销。建议控制在3～5个。

  • 前缀索引限制：前缀索引仅取Key列的前36字节。超出部分不参与索引过滤。VARCHAR类型的Key列会占用较多索引字节，建议将定长类型列放在前面。

案例4：选择合适的字段类型

精确的字段类型能减少存储空间和计算开销。遵循「定长优先、精确优先、最小满足」原则选择类型：

原则

推荐

避免

定长优先

INT、BIGINT、DATE。

用VARCHAR或STRING存储可用定长类型表示的值。

精确优先

DECIMAL(p,s)用于金额类字段。

DOUBLE存储金额（存在精度丢失）。

最小满足

值域允许时使用INT或SMALLINT。

值域不需要时使用BIGINT或LARGEINT。

常见优化场景

  • 用BIGINT替代用于存储数值的VARCHAR列。定长类型比较效率更高，且占用更少的索引字节。

  • 用DATE或DATETIME替代字符串形式的日期。DATE类型支持分区裁剪和范围查询优化。

  • 金额、价格等需要精确计算的字段使用DECIMAL(p,s)，避免DOUBLE类型的浮点精度问题。

  • 合理设置VARCHAR的最大长度，避免使用STRING类型替代固定格式的短字段。

分区策略

合理的分区设计能够有效减少查询扫描的数据量。SelectDB提供两种推荐的分区方式：

  • AUTO PARTITION（推荐）：通过AUTO PARTITION在数据写入时按需自动创建分区，无需预创建。适合分区键取值范围不确定或分区粒度灵活的场景。

  • 动态分区：适合对分区生命周期有明确管理需求的时序数据，支持自动创建新分区和清理过期分区。

-- AUTO PARTITION示例（推荐）：写入时按天自动创建分区
CREATE TABLE user_events (
    event_time DATETIME NOT NULL,
    user_id BIGINT NOT NULL,
    event_type VARCHAR(32),
    event_data TEXT
)
DUPLICATE KEY(event_time, user_id)
AUTO PARTITION BY RANGE (date_trunc(event_time, 'day')) ()
DISTRIBUTED BY HASH(user_id) BUCKETS AUTO;

详细的分区裁剪优化方法请参考使用分区裁剪优化扫表文档。

常见问题

建表后发现分桶列不合理，如何调整？

分桶列在建表后无法直接修改。需要使用新的分桶列创建一张新表，然后通过INSERT INTO new_table SELECT * FROM old_table迁移数据。

Key列越多越好吗？

不是。Key列过多会增加数据写入时的排序开销和compaction负担。前缀索引仅取Key列的前36字节，超出部分不参与索引过滤。建议将Key列数量控制在3～5个，优先覆盖最高频的查询过滤条件。

什么时候必须使用Unique或Aggregate模型？

当业务需要数据更新（如CDC同步、Upsert）时，必须使用Unique模型。当业务查询固定在某些维度上做聚合（如每日各地区销售汇总），且无需查看明细数据时，Aggregate模型可以在导入时完成预聚合，减少存储并提升查询性能。其他场景优先使用Duplicate模型。

如何判断当前表是否存在数据倾斜？

执行以下SQL检查分桶列的值分布。如果排名靠前的值对应的count远高于其他值（例如占总行数的50%以上），说明存在显著的数据倾斜：

-- 将 bucket_col 替换为分桶列名，table_name 替换为表名
SELECT bucket_col, count(*) cnt
FROM table_name
GROUP BY bucket_col
ORDER BY cnt DESC
LIMIT 10;

上一篇：查询优化实践下一篇：优化索引设计和使用