如果你正在搭建一个实时数仓或者准备从传统 MySQL 分库分表迁移到更专业的分析型数据库那么“如何设计表”这个问题很可能就是你遇到的第一个、也是最关键的技术门槛。很多开发者第一次接触 Apache Doris 时会习惯性地沿用 MySQL 或 PostgreSQL 的建表思维结果在数据导入、查询性能、存储成本上处处碰壁。Doris 作为一款高性能的实时分析数据库其表设计理念与事务型数据库有本质区别。它不追求极致的单行写入速度而是通过精巧的数据模型、预聚合和索引设计来换取海量数据下的亚秒级查询响应。本文将聚焦于 Doris 数据表创建这一核心操作但不止步于简单的语法罗列。我们会深入探讨为什么 Doris 的表结构直接决定了查询的生死时速面对不同的业务场景如用户行为日志、订单明细、聚合报表应该如何选择最合适的表模型聚合、唯一、明细那些看似复杂的PARTITION BY、DISTRIBUTED BY子句背后究竟在解决什么问题通过一个从零开始的电商数据分析案例你将不仅学会CREATE TABLE命令的每个参数更能掌握一套适用于 Doris 的“表设计方法论”。这套方法能帮助你在项目初期就避开性能陷阱让后续的数据开发和查询优化事半功倍。1. 这篇文章真正要解决的问题在数据平台项目中建表往往被视为一个简单的、一次性的初始化动作。但在 Doris 的语境下这是一个战略性的决策点。一个糟糕的表设计会让 Doris 的高性能引擎无从发挥查询慢、存储膨胀、导入卡顿等问题会接踵而至。本文要解决的核心问题有三个认知转换帮助习惯了 OLTP 数据库的开发者理解 Doris 作为 MPP 架构的 OLAP 数据库其表设计的核心目标是什么是快速分析而非高频事务。模型选型面对纷繁的业务需求如何清晰地判断该使用聚合模型Aggregate、唯一模型Unique还是明细模型Duplicate选错模型的代价极高。分布式设计理解分区Partition和分桶Bucket这两个核心概念。它们如何影响数据分布、查询并行度以及存储管理如何设置合理的分区键和分桶数以避免数据倾斜和资源浪费我们将通过一个贯穿全文的“电商用户行为分析”场景将抽象的概念转化为具体的 SQL 和设计抉择。读完本文你将能独立完成一个兼顾性能、成本和可维护性的 Doris 表结构设计。2. 基础概念与核心原理Doris 表模型剖析在动手写 SQL 之前必须理解 Doris 提供的三种数据模型。这是用好 Doris 的基石。2.1 明细模型 (Duplicate)是什么保留所有写入的原始数据即使存在完全相同的数据行也会全部保留。类似于日志表没有主键不支持行级更新。解决了什么问题适用于需要存储原始明细数据的场景如用户操作日志、交易流水、IoT 设备上报数据。你无法预知未来会基于哪些维度进行分析因此需要保留所有细节。类比就像一台高速摄像机记录下事件发生的每一帧画面不做任何剪辑。典型场景用户点击流日志、服务器访问日志、传感器时序数据。2.2 聚合模型 (Aggregate)是什么定义了维度和指标。相同维度的数据行会根据指定的聚合函数如 SUM、MAX、MIN、REPLACE在导入时进行预聚合。这是 Doris 性能优势的核心来源。解决了什么问题极大地提升聚合查询如 SUM、COUNT、AVG的性能并大幅降低存储成本。数据在导入阶段就完成了聚合查询时无需扫描大量原始数据。关键点聚合发生在数据导入阶段。如果业务需要查询明细此模型不适用。类比就像一个实时更新的报表每天的新数据都会自动累加到总计中你看到的是最新的汇总结果而不是每一笔流水。典型场景PV/UV 统计、销售额汇总、库存总量。2.3 唯一模型 (Unique)是什么定义主键Unique Key。对于主键相同的数据行后导入的数据会替换先导入的数据。实现了基于主键的 Upsert更新/插入操作。解决了什么问题在需要实时更新维度表或状态表的场景下提供了高效的解决方案。比如用户画像表用户的属性如等级、标签会随时间变化。与聚合模型 REPLACE 的区别唯一模型的“替换”是行级别的整行数据被新行覆盖。聚合模型的 REPLACE 是列级别的只替换指定的聚合列。类比就像一份员工花名册每个员工主键只有一条最新记录当员工信息变更时直接更新这条记录。典型场景用户主数据表、商品信息表、实时更新的配置表。为了更直观地对比我们用一个表格来总结特性明细模型 (Duplicate)聚合模型 (Aggregate)唯一模型 (Unique)核心能力存储原始明细预聚合加速分析主键唯一支持更新是否有主键无有维度列有Unique Key数据去重不去重按维度列聚合按主键去重后到优先存储开销大小聚合后中等适用场景日志、流水、事件统计报表、汇总分析维度表、状态表、需要更新的表查询优势任意维度明细查询聚合查询极快点查基于主键快3. 环境准备与前置条件在开始创建表之前请确保你有一个可用的 Doris 环境。你可以通过以下方式之一获得单机部署推荐用于学习参照 Doris 官网或社区教程使用 Docker 或下载安装包在单台机器上部署 FEFrontend和 BEBackend。本文假设你已部署成功。集群部署用于生产环境。云托管服务部分云厂商提供了托管的 Doris 服务。本文演示环境Doris 版本2.0.x 核心语法在 1.x 版本同样适用建议使用较新稳定版连接工具MySQL 客户端如mysql命令、DBeaver 或任何支持 MySQL 协议的图形化工具。连接信息假设你的 FE 节点 IP 是127.0.0.1查询端口是9030用户名/密码是root/空。首先通过 MySQL 客户端连接 Dorismysql -h 127.0.0.1 -P 9030 -u root连接成功后创建一个用于演示的数据库CREATE DATABASE IF NOT EXISTS demo_db; USE demo_db;4. 核心流程拆解创建你的第一张 Doris 表我们将围绕一个电商场景展开需要分析用户的页面浏览行为。原始数据包含用户ID、时间、页面、设备等信息。4.1 第一步选择数据模型我们的需求是什么需要按天、按用户、按页面统计 PV页面访问量。也需要能查询某个用户在特定时间段的详细浏览记录用于问题排查。分析需求1是典型的聚合查询需求2是明细查询。这似乎产生了矛盾。在 Doris 中一种常见的解决方案是混合使用两种表方案A常用创建一张聚合模型表用于快速的聚合分析需求1。再创建一张明细模型表或通过外部表存储原始日志用于偶尔的明细查询需求2。通过定期导入将明细数据同步到聚合表。方案B2.0新特性使用物化视图在明细模型表上构建聚合预计算。为了演示最核心的模型选择我们先采用方案A。本章先创建聚合表明细表将在后续章节作为扩展内容。结论对于核心的聚合分析需求我们选择聚合模型Aggregate。4.2 第二步设计表结构Schema确定模型后需要设计字段维度列用于分组和过滤的列在聚合模型中充当“主键”。例如dt日期、user_id、page。指标列需要被聚合计算的列。例如pv访问次数。在聚合模型中所有非维度列都必须指定聚合函数如SUM、MAX、MIN、REPLACE。我们的初步设计如下dt日期DATE分区字段方便按天管理数据。user_id用户IDBIGINT。page页面标识VARCHAR。pv页面访问次数BIGINT使用SUM聚合。4.3 第三步设计数据分布Partition Bucket这是 Doris 表设计中最关键的一步直接影响性能和稳定性。分区PARTITION BY RANGE将表的数据按范围通常是时间划分成不同的子集。好处是管理便捷可以按分区进行数据生命周期管理删除、备份。查询加速查询时可以通过分区键快速过滤掉无关数据分区裁剪。我们按dt字段进行范围分区。分桶DISTRIBUTED BY HASH在分区内数据进一步被划分到多个 Tablet数据分片中。每个 Tablet 是数据移动、复制和计算的最小单元。分桶键选择数据分布均匀、常用于查询条件的列如user_id。分桶数量必须谨慎设置。建议单个 Tablet 的数据量在 100MB 到 1GB 之间。数量太少不利于并发数量太多会增加元数据开销和调度负担。对于初始数据量不大的测试表可以设置为 8 或 16。4.4 第四步编写完整的 CREATE TABLE 语句综合以上分析我们来编写创建聚合表的完整 SQL。CREATE TABLE IF NOT EXISTS user_page_view_agg ( -- 维度列 dt DATE NOT NULL COMMENT 数据分区日期格式yyyy-MM-dd, user_id BIGINT NOT NULL COMMENT 用户ID, page VARCHAR(50) NOT NULL COMMENT 页面标识如 ‘/home‘, ‘/product‘, -- 指标列 (必须指定聚合函数) pv BIGINT SUM NOT NULL DEFAULT 0 COMMENT 页面访问次数求和聚合, -- 可以添加更多指标列例如 -- total_duration BIGINT SUM COMMENT 总停留时长, -- last_access_time DATETIME REPLACE COMMENT 最后访问时间 ) -- 指定引擎和副本数单机部署副本数只能为1 ENGINEolap DUPLICATE KEY(dt, user_id, page) -- 注意聚合模型也使用DUPLICATE KEY指定维度列顺序 -- 按日期范围分区 PARTITION BY RANGE(dt)() -- 指定分桶方式 DISTRIBUTED BY HASH(user_id) BUCKETS 8 -- 表属性 PROPERTIES ( replication_num 1, -- 副本数单机为1生产集群通常为3 storage_medium SSD, -- 存储介质 storage_cooldown_time 9999-12-31 23:59:59 -- SSD到期降级到HDD的时间可设置很远 );关键点解释DUPLICATE KEY(dt, user_id, page)在聚合模型中这指定了维度列的顺序。这个顺序会影响前缀索引将最常用于查询过滤的列放在前面有利于提升查询性能。PARTITION BY RANGE(dt)()这里先定义了分区方式但未添加具体分区。更常见的做法是使用动态分区后面会介绍。DISTRIBUTED BY HASH(user_id) BUCKETS 8数据按user_id的哈希值分散到 8 个桶中。PROPERTIES设置了副本数、存储介质等高级属性。执行上述 SQL你的第一张 Doris 聚合表就创建成功了。可以使用SHOW CREATE TABLE user_page_view_agg;查看完整的建表语句。5. 动态分区与数据生命周期管理手动管理每天的分区非常繁琐。Doris 的动态分区功能可以自动创建和删除分区。让我们修改一下建表策略使用动态分区。首先删除刚才的表如果存在DROP TABLE IF EXISTS user_page_view_agg;然后创建支持动态分区的表CREATE TABLE IF NOT EXISTS user_page_view_agg ( dt DATE NOT NULL COMMENT 数据分区日期, user_id BIGINT NOT NULL COMMENT 用户ID, page VARCHAR(50) NOT NULL COMMENT 页面标识, pv BIGINT SUM NOT NULL DEFAULT 0 COMMENT 页面访问次数 ) ENGINEolap DUPLICATE KEY(dt, user_id, page) -- 使用动态分区规则 PARTITION BY RANGE(dt)() DISTRIBUTED BY HASH(user_id) BUCKETS 8 PROPERTIES ( replication_num 1, storage_medium SSD, -- 动态分区规则 dynamic_partition.enable true, dynamic_partition.time_unit DAY, dynamic_partition.start -7, -- 保留最近7天的分区包含今天 dynamic_partition.end 3, -- 预先创建未来3天的分区 dynamic_partition.prefix p, -- 分区名前缀 dynamic_partition.buckets 8, -- 动态创建的分区分桶数继承表设置时可省略 dynamic_partition.replication_num 1 );动态分区属性解析time_unit分区单位可以是DAY、WEEK、MONTH。start删除多少天之前的分区。-7表示只保留最近7天今天过去6天的数据更早的会自动删除。end提前创建多少天的分区。3表示会提前创建今天、明天、后天共3天的分区。prefix自动创建的分区名前缀例如p20240315。这样你就拥有了一个可以自动滚动、管理生命周期的表。无需担心数据无限膨胀。6. 完整示例从数据导入到查询验证表建好了我们来模拟数据写入和查询。6.1 使用 INSERT INTO 导入测试数据-- 向聚合表插入数据Doris会在内部进行聚合 INSERT INTO user_page_view_agg (dt, user_id, page, pv) VALUES (2024-03-15, 10001, /home, 1), (2024-03-15, 10001, /product, 2), (2024-03-15, 10002, /home, 1), (2024-03-16, 10001, /home, 1), (2024-03-16, 10001, /home, 1), -- 同一天同一用户同一页面pv会累加为2 (2024-03-16, 10003, /cart, 1);执行后可以查询数据SELECT * FROM user_page_view_agg ORDER BY dt, user_id;预期结果dtuser_idpagepv2024-03-1510001/home12024-03-1510001/product22024-03-1510002/home12024-03-1610001/home22024-03-1610003/cart1可以看到(2024-03-16, 10001, /home)这组维度对应的pv被自动求和了。这就是聚合模型的效果。6.2 执行聚合查询现在执行我们最初设想的业务查询-- 1. 查询2024-03-15全站的页面PV Top 2 SELECT page, SUM(pv) as total_pv FROM user_page_view_agg WHERE dt 2024-03-15 GROUP BY page ORDER BY total_pv DESC LIMIT 2; -- 2. 查询用户10001在最近两天的总访问量 SELECT dt, user_id, SUM(pv) as daily_pv FROM user_page_view_agg WHERE user_id 10001 AND dt 2024-03-15 GROUP BY dt, user_id ORDER BY dt;由于数据在导入时已经预聚合这些查询会非常快尤其是当数据量巨大时优势更加明显。6.3 创建明细模型表作为对比为了满足明细查询需求我们创建一张明细模型表CREATE TABLE IF NOT EXISTS user_page_view_detail ( event_time DATETIME NOT NULL COMMENT 事件发生时间, dt DATE NOT NULL COMMENT 分区日期, user_id BIGINT NOT NULL COMMENT 用户ID, page VARCHAR(50) NOT NULL COMMENT 页面标识, device VARCHAR(20) COMMENT 设备类型, session_id VARCHAR(32) COMMENT 会话ID ) ENGINEolap DUPLICATE KEY(event_time, dt, user_id, page) -- 明细模型所有列都是排序键的一部分 PARTITION BY RANGE(dt)() DISTRIBUTED BY HASH(user_id) BUCKETS 8 PROPERTIES ( replication_num 1, dynamic_partition.enable true, dynamic_partition.time_unit DAY, dynamic_partition.start -30, -- 明细数据保留更久 dynamic_partition.end 3 );向明细表插入数据包含更详细的原始信息INSERT INTO user_page_view_detail VALUES (2024-03-15 10:00:00, 2024-03-15, 10001, /home, iOS, sess_abc), (2024-03-15 10:01:00, 2024-03-15, 10001, /product, iOS, sess_abc), (2024-03-15 10:00:30, 2024-03-15, 10002, /home, Android, sess_def);现在你可以在这张表上进行任意的明细查询例如查找某个会话的所有行为SELECT * FROM user_page_view_detail WHERE session_id sess_abc ORDER BY event_time;7. 常见问题与排查思路在创建和管理 Doris 表时你可能会遇到以下问题问题现象可能原因排查方式解决方案建表失败报错Failed to create partition动态分区配置错误或历史分区已存在冲突。1. 检查PROPERTIES中动态分区参数语法。2. 执行SHOW PARTITIONS FROM table_name;查看现有分区。1. 修正参数如时间单位、起止范围。2. 删除冲突的旧表或旧分区。数据导入后查询结果与预期不符聚合值不对1. 表模型选择错误。2. 维度列定义不完整导致非预期的聚合。1. 确认CREATE TABLE语句中指标列是否定义了聚合函数如SUM。2. 检查导入的数据中是否本应作为维度的列值不同却被合并了。1. 重新审视业务需求选择正确的模型。2. 将需要区分不同行的列加入DUPLICATE KEY中。查询速度慢即使数据量不大1. 没有有效利用分区裁剪。2. 分桶数设置不合理导致数据倾斜或并发度低。3. 前缀索引未命中。1. 使用EXPLAIN查看查询计划确认分区过滤是否生效 (partitions)。2. 检查数据分布SHOW DATA FROM table_name;。3. 检查查询条件是否使用了DUPLICATE KEY的前几列。1. 确保查询条件中包含分区键。2. 调整分桶键和分桶数使数据均匀分布。3. 调整DUPLICATE KEY的列顺序将高频过滤列放前面。ALTER TABLE添加列后导入失败新添加的列在导入数据文件中没有对应值且未设置默认值。查看错误日志确认是否为列不匹配错误。1. 在导入数据文件中补充该列。2. 或者在ALTER TABLE时指定列的默认值ADD COLUMN col_name INT DEFAULT 0。磁盘空间增长过快1. 明细模型表数据未设置合理的生命周期。2. 聚合模型未有效聚合存储了大量中间状态。1. 检查表的动态分区策略start值是否过小。2. 检查聚合表的数据是否维度组合太多导致未能有效压缩。1. 调整动态分区start参数自动删除旧数据。2. 对于聚合表考虑是否可以使用更高粒度的维度如去掉一些低基数维度。8. 最佳实践与工程建议表模型选择三原则有聚合需求且不需要明细-聚合模型。需要主键唯一支持数据更新-唯一模型。需要保留所有原始记录用于 Ad-hoc 查询或审计-明细模型。对于既有聚合又有明细的需求采用“明细表 物化视图/聚合表”的架构。分区与分桶设计黄金法则分区键首选时间字段便于冷热数据管理和分区裁剪。单个分区数据量建议在1GB - 10GB之间。分桶键选择高基数、常用于查询条件或 Join 的字段如user_id,order_id。避免选择低基数字段如“性别”会导致数据倾斜。分桶数估算公式分桶数 ≈ 数据总量 / 每个 Tablet 理想大小。初期可按每个 Tablet 约1GB估算。对于集群分桶数可设置为 BE 节点数的整数倍以充分利用集群资源。建议在8 到 128之间后续可通过ALTER TABLE ... ORDER BY (new_hash_key) BUCKETS new_bucket_num重建分桶2.0版本支持。字段类型与索引优化尽量使用精确的类型如INT代替BIGINT如果值域允许VARCHAR指定合适长度。DUPLICATE KEY的顺序就是前缀索引的顺序。将最常出现在 WHERE 条件中的列放在最前面。对于明细模型如果某些列很少用于过滤或聚合可以将其放在DUPLICATE KEY的最后甚至定义为VALUE类型2.0特性以减少索引开销。生产环境注意事项副本数生产集群务必设置replication_num 3保证数据高可用。资源隔离为不同业务的数据创建不同的数据库便于权限和资源管理。变更管理表结构变更如加列是轻量级操作但修改分区和分桶属性是重操作需要在业务低峰期进行。监控密切关注SHOW DATA、SHOW PROC /statistic等命令的输出监控数据量增长、副本状态和查询负载。掌握 Doris 建表本质上是掌握一种面向分析的数据建模思维。从模型选择到分布策略每一步都影响着未来数据平台的查询性能、存储成本和运维复杂度。建议你在自己的测试环境中反复练习本文的案例并尝试设计不同的业务场景如订单分析、用户画像、日志监控体会不同设计带来的差异。真正的熟练源于在具体问题中做出的每一次权衡和选择。
