保姆级教程：从MySQL到Doris，如何迁移表结构并设计高效分区方案

从MySQL到Doris：数据迁移与高效分区设计实战指南

在数据分析领域，传统关系型数据库如MySQL虽然擅长事务处理，但在海量数据分析场景下往往力不从心。Apache Doris作为新一代MPP分析型数据库，凭借其列式存储、向量化引擎和分布式架构，正在成为企业实时分析的首选。本文将手把手带你完成从MySQL到Doris的数据迁移全流程，重点解析如何将OLTP表结构转化为适合OLAP的分区设计方案。

1. 理解核心差异：MySQL与Doris的架构对比

MySQL作为关系型数据库代表，采用行式存储和B+树索引，适合高并发点查询和事务处理。而Doris作为分析型数据库，其核心优势在于：

• 列式存储：仅读取查询所需的列，大幅降低IO消耗
• 预聚合：支持SUM、MAX等聚合模型，减少实时计算开销
• 分布式计算：通过分区(Partition)和分桶(Bucket)实现并行处理

关键差异对比表：

迁移过程中最常见的误区是直接照搬MySQL的表结构。我曾见过一个案例，团队将包含50个字段的订单表原样迁移到Doris，结果查询性能反而下降。问题出在没有根据分析场景重新设计数据模型。

2. 表结构迁移：从关系模型到分析模型

2.1 数据类型映射与优化

Doris支持与MySQL相似的数据类型，但有以下优化建议：

-- MySQL原始表结构 CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id INT, amount DECIMAL(10,2), create_time DATETIME, status VARCHAR(20), -- ...其他字段 ); -- Doris优化后的表结构 CREATE TABLE orders ( user_id LARGEINT COMMENT "用户ID", create_date DATE COMMENT "订单日期", create_time DATETIME COMMENT "订单时间", province VARCHAR(10) COMMENT "省份", city VARCHAR(10) COMMENT "城市", status SMALLINT COMMENT "状态编码", amount BIGINT COMMENT "金额(单位:分)", order_count BIGINT SUM DEFAULT "0" COMMENT "订单数" ) ENGINE=olap AGGREGATE KEY(user_id, create_date, create_time, province, city, status) PARTITION BY RANGE(create_date) ( PARTITION p202301 VALUES LESS THAN ("2023-02-01"), PARTITION p202302 VALUES LESS THAN ("2023-03-01") ) DISTRIBUTED BY HASH(user_id) BUCKETS 32 PROPERTIES ( "replication_num" = "3", "storage_medium" = "SSD" );

关键改造点：

1. 将DECIMAL转为BIGINT存储分单位金额，避免浮点计算
2. 状态字段从VARCHAR改为SMALLINT编码
3. 增加日期维度字段便于分区
4. 使用AGGREGATE KEY模型预聚合订单数

2.2 索引设计原则

Doris的索引与MySQL完全不同：

• 前缀索引：默认对前36字节建立稀疏索引
• 智能匹配：自动选择匹配前缀索引的查询条件

优化建议：

• 将高频过滤条件放在建表语句的前列
• 避免过长的字符串作为Key列
• 对于超过36字节的索引需求，考虑使用Bitmap索引

3. 分区与分桶策略设计

3.1 分区策略选择

Range分区最适合时间序列数据，例如按天分区：

PARTITION BY RANGE(create_date) ( PARTITION p202301 VALUES LESS THAN ("2023-02-01"), PARTITION p202302 VALUES LESS THAN ("2023-03-01"), PARTITION p202303 VALUES LESS THAN ("2023-04-01"), PARTITION p_current VALUES LESS THAN ("2023-05-01"), PARTITION p_future VALUES LESS THAN MAXVALUE )

List分区适合离散值，如地区划分：

PARTITION BY LIST(province) ( PARTITION p_east VALUES IN ("Shanghai", "Jiangsu", "Zhejiang"), PARTITION p_north VALUES IN ("Beijing", "Tianjin"), PARTITION p_south VALUES IN ("Guangdong", "Fujian") )

3.2 分桶设计实战

分桶数量建议遵循以下公式：

分桶数 ≈ 集群BE节点数 × 3 × (1～1.5)

例如6节点集群可设置32-64个分桶。分桶列的选择要考虑：

1. 高并发点查询：使用用户ID等区分度高的列
2. 大范围扫描：使用多个低区分度列组合

-- 高并发场景 DISTRIBUTED BY HASH(user_id) BUCKETS 32 -- 全表扫描场景 DISTRIBUTED BY HASH(create_date, province) BUCKETS 64

4. 数据迁移与性能调优

4.1 迁移方案对比

推荐组合方案：

1. 使用Spark进行历史数据全量迁移
2. 通过Routine Load同步增量数据
3. 对特殊表使用Binlog同步保证实时性

4.2 性能调优技巧

常见问题处理：

• 数据倾斜：调整分桶列或增加分桶数
• 小文件问题：设置适当的压缩算法和合并策略
• 热点查询：合理设计物化视图

-- 创建物化视图预聚合 CREATE MATERIALIZED VIEW mv_order_stats DISTRIBUTED BY HASH(create_date) BUCKETS 32 REFRESH ASYNC AS SELECT create_date, province, SUM(amount) AS total_amount, COUNT(*) AS order_count FROM orders GROUP BY create_date, province;

5. 典型场景实战案例

5.1 电商订单分析

需求特点：

• 按时间分析销售趋势
• 按地区/用户分层统计
• 实时监控异常订单

设计方案：

CREATE TABLE orders ( order_id LARGEINT, user_id LARGEINT, create_date DATE, province VARCHAR(10), city VARCHAR(10), payment_type TINYINT, amount BIGINT, discount_amount BIGINT, status TINYINT, -- 其他字段... payment_amount BIGINT SUM, order_count BIGINT SUM ) ENGINE=olap PARTITION BY RANGE(create_date) ( PARTITION p2023 VALUES LESS THAN ("2024-01-01"), PARTITION p2024 VALUES LESS THAN ("2025-01-01") ) DISTRIBUTED BY HASH(user_id) BUCKETS 64 PROPERTIES ( "enable_persistent_index" = "true", "replication_num" = "3" );

5.2 用户行为分析

特殊考虑：

• 超高基数用户ID
• 稀疏事件属性
• 快速漏斗分析

CREATE TABLE user_events ( event_date DATE, user_id LARGEINT, event_type SMALLINT, page_id INT, stay_duration INT MAX, event_count BIGINT SUM ) ENGINE=olap PARTITION BY RANGE(event_date) ( PARTITION p_current VALUES LESS THAN ("2023-06-01") ) DISTRIBUTED BY HASH(user_id, event_type) BUCKETS 128 PROPERTIES ( "storage_format" = "v2", "light_schema_change" = "true" );

在最近的一个新零售项目中，我们通过合理设计分区策略，将原本需要3小时的日终报表缩短到15分钟内完成。关键是将热数据(最近3个月)与冷数据分开存储，并为热数据配置更多副本和SSD存储。