【Doris入门】Doris数据表模型使用指南：核心注意事项与实践

• 在建表时，列类型的选择直接影响查询性能和存储效率，需要特别注意以下几点：

-- 推荐的建表示例
CREATE TABLE IF NOT EXISTS ods_db.optimized_user_profile(user_id BIGINT NOT NULL COMMENT "用户ID",register_date DATE NOT NULL COMMENT "注册日期",user_age TINYINT COMMENT "年龄",city_code SMALLINT COMMENT "城市编码",user_level TINYINT COMMENT "用户等级",last_login_time DATETIME COMMENT "最后登录时间",total_consumption DECIMAL(10,2) COMMENT "总消费金额",login_count INT COMMENT "登录次数",is_active TINYINT COMMENT "是否活跃"
)DUPLICATE KEY(user_id, register_date)
DISTRIBUTED BY HASH(user_id) BUCKETS 1
PROPERTIES ("replication_allocation" = "tag.location.default: 1","compression" = "zstd"
);

• 聚合模型虽然通过预聚合机制提升了查询性能，但也存在一些重要的局限性：

• 导入两批次数据：

• 最终聚合结果：

-- 这个查询在聚合模型中性能很差
SELECT COUNT(*) FROM user_cost_table;
-- 正确结果应该是4，但需要扫描所有Key列才能得到

-- 修改表结构，增加count列
CREATE TABLE ads_db.user_cost_table_optimized(user_id BIGINT,date DATE,cost BIGINT SUM,count BIGINT SUM  -- 用于计算count
)AGGREGATE KEY(user_id, date)
PROPERTIES ("replication_num" = "1"
);

• 导入数据时，count列值恒为1：

-- 导入数据
INSERT INTO ads_db.user_cost_table_optimized VALUES
(10001, '2025-08-20', 50, 1),
(10002, '2025-08-21', 39, 1);INSERT INTO ads_db.user_cost_table_optimized VALUES
(10001, '2025-08-20', 1, 1),
(10001, '2025-08-21', 5, 1),
(10003, '2025-08-22', 22, 1);

• 查询优化：

-- 使用sum(count)替代count(*)
SELECT SUM(count) FROM ads_db.user_cost_table_optimized;
-- 这个查询效率远高于直接使用COUNT(*)
mysql> SELECT SUM(count) FROM ads_db.user_cost_table_optimized;
+--------------+
| sum(`count`) |
+--------------+
|            5 |
+--------------+
1 row in set (0.31 sec)mysql> 

-- 修改表结构，使用REPLACE聚合类型
CREATE TABLE ads_db.user_cost_table_optimized_v2(user_id BIGINT,date DATE,cost BIGINT SUM,count BIGINT REPLACE
) AGGREGATE KEY(user_id, date)
DISTRIBUTED BY HASH(user_id) BUCKETS 1  -- 指定分桶键和桶数量
PROPERTIES ("replication_num" = "1"
);
# 优势： 没有导入重复行的限制，sum(count)和count(*)的结果一致

-- 创建使用写时合并的Unique表
CREATE TABLE dwd_db.user_profile_unique(user_id BIGINT NOT NULL,username VARCHAR(50) NOT NULL,city VARCHAR(20),age INT,last_login_time DATETIME,login_count INT DEFAULT "0"
) UNIQUE KEY(user_id, username)
DISTRIBUTED BY HASH(user_id) BUCKETS 1
PROPERTIES ("enable_unique_key_merge_on_write" = "true","replication_num" = "1"
);-- 数据导入
INSERT INTO dwd_db.user_profile_unique VALUES
(1001, 'tom', 'beijing', 25, '2025-01-01 10:00:00', 10),
(1002, 'jerry', 'shanghai', 30, '2025-01-01 11:00:00', 5);-- 更新数据
INSERT INTO dwd_db.user_profile_unique VALUES
(1001, 'tom', 'beijing', 26, '2025-01-02 10:00:00', 15);-- count(*)查询性能优异
SELECT COUNT(*) FROM dwd_db.user_profile_unique;

-- Duplicate模型：Key列只用于排序
CREATE TABLE ods_db.logs_duplicate(log_time DATETIME,log_type INT,content VARCHAR(1024)
)DUPLICATE KEY(log_time, log_type)
PROPERTIES ("replication_num" = "1"
);-- Aggregate模型：Key列用于聚合
CREATE TABLE ads_db.stats_aggregate(date DATE,user_id BIGINT,amount BIGINT SUM
)AGGREGATE KEY(date, user_id)
PROPERTIES ("replication_num" = "1"
);-- Unique模型：Key列用于去重
CREATE TABLE dwd_db.users_unique(user_id BIGINT,username VARCHAR(50),last_login_time DATETIME REPLACE
)UNIQUE KEY(user_id, username)
PROPERTIES ("replication_num" = "1"
);