从MySQL迁移到Doris，如何高效处理JSON和数组数据？实战对比JSONB与ARRAY类型

从MySQL迁移到Doris：JSON与数组数据处理实战指南

在数据架构升级的浪潮中，越来越多的企业开始从传统关系型数据库转向现代分析型数据库。Doris作为一款高性能的MPP分析型数据库，在处理半结构化数据方面展现出独特优势。本文将深入探讨如何将MySQL中的JSON和数组数据高效迁移到Doris，并充分利用其JSONB和ARRAY类型的特性。

1. 理解Doris的半结构化数据类型

Doris提供了两种专门用于处理半结构化数据的数据类型：JSONB和ARRAY 。这两种类型在设计理念和使用场景上各有侧重。

JSONB类型采用二进制格式存储JSON数据，具有以下核心优势：

• 写入时自动进行JSON格式校验
• 查询时通过专用函数高效提取嵌套字段
• 存储空间比普通STRING类型更节省

-- 创建包含JSONB字段的表示例 CREATE TABLE user_profiles ( user_id BIGINT, profile JSONB ) ENGINE=OLAP DUPLICATE KEY(user_id) DISTRIBUTED BY HASH(user_id) BUCKETS 10;

ARRAY 类型则专门用于存储同类型元素的集合，支持的基础类型包括：

• 数值类型：INT, BIGINT, DOUBLE等
• 字符串类型：VARCHAR, STRING
• 时间类型：DATE, DATETIME

-- 创建包含ARRAY字段的表示例 CREATE TABLE product_tags ( product_id BIGINT, tags ARRAY<VARCHAR(50)> ) ENGINE=OLAP DUPLICATE KEY(product_id) DISTRIBUTED BY HASH(product_id) BUCKETS 10;

2. 数据迁移策略对比

从MySQL迁移到Doris时，针对JSON和数组数据，我们需要根据不同的场景选择合适的迁移策略。

2.1 批量迁移方案

对于大规模数据迁移，Stream Load是最佳选择。这种方式通过HTTP协议直接导入数据文件，效率极高。

# 使用curl进行Stream Load导入JSON数据 curl --location-trusted -u username:password \ -H "format: json" \ -H "strip_outer_array: true" \ -T data.json \ http://fe_host:8030/api/db_name/table_name/_stream_load

关键参数说明：

2.2 增量同步方案

对于需要实时同步的场景，可以使用INSERT语句或通过CDC工具实现：

-- 单条INSERT示例 INSERT INTO user_profiles VALUES (1, '{"name":"张三","age":30,"address":{"city":"北京"}}');

注意：频繁的小批量INSERT操作会影响Doris性能，建议批量提交或使用Stream Load进行微批处理。

3. 查询优化技巧

Doris为JSONB和ARRAY类型提供了丰富的查询函数，合理使用这些函数能显著提升查询效率。

3.1 JSONB查询函数族

Doris提供了一系列类型安全的JSONB提取函数：

-- 提取不同类型的数据 SELECT jsonb_extract_int(profile, '$.age') AS age, jsonb_extract_string(profile, '$.name') AS name, jsonb_extract_isnull(profile, '$.address') AS has_address FROM user_profiles;

常用JSONB函数性能对比：

3.2 ARRAY操作技巧

针对ARRAY类型，Doris提供了丰富的操作函数：

-- 数组元素访问 SELECT tags[1] AS primary_tag FROM product_tags; -- 数组长度查询 SELECT array_length(tags) AS tag_count FROM product_tags; -- 数组包含判断 SELECT product_id FROM product_tags WHERE array_contains(tags, '促销');

4. 实战案例：电商数据分析迁移

让我们通过一个完整的电商数据分析案例，演示如何将MySQL中的复杂数据迁移到Doris并进行高效分析。

4.1 原始MySQL表结构

-- MySQL中的订单表 CREATE TABLE mysql_orders ( order_id BIGINT, user_info JSON, items JSON, create_time DATETIME );

4.2 Doris目标表设计

-- Doris中的优化表结构 CREATE TABLE doris_orders ( order_id BIGINT, user_id BIGINT, user_name VARCHAR(100), user_tags ARRAY<VARCHAR(50)>, items ARRAY<JSONB>, order_time DATETIMEV2, province VARCHAR(50) ) ENGINE=OLAP DUPLICATE KEY(order_id) PARTITION BY RANGE(order_time) ( PARTITION p202301 VALUES LESS THAN ('2023-02-01'), PARTITION p202302 VALUES LESS THAN ('2023-03-01') ) DISTRIBUTED BY HASH(order_id) BUCKETS 16;

4.3 数据转换与导入

使用Python脚本进行数据转换：

import json import pymysql from doris import DorisClient # 从MySQL提取数据 mysql_conn = pymysql.connect(host='mysql_host', user='user', password='pwd', db='db') cursor = mysql_conn.cursor() cursor.execute("SELECT * FROM mysql_orders") # 转换并导入Doris doris = DorisClient('doris_host') for row in cursor: user_info = json.loads(row[1]) items = json.loads(row[2]) transformed = { 'order_id': row[0], 'user_id': user_info.get('id'), 'user_name': user_info.get('name'), 'user_tags': user_info.get('tags', []), 'items': items, 'order_time': row[3], 'province': user_info.get('address', {}).get('province') } doris.insert('doris_orders', transformed)

4.4 分析查询示例

迁移完成后，我们可以执行复杂的分析查询：

-- 查询每个省份的畅销商品类别 SELECT province, item->>'$.category' AS category, COUNT(*) AS order_count, SUM(CAST(item->>'$.price' AS DOUBLE)) AS total_amount FROM doris_orders, UNNEST(items) AS t(item) WHERE order_time BETWEEN '2023-01-01' AND '2023-01-31' GROUP BY province, item->>'$.category' ORDER BY total_amount DESC LIMIT 100;

5. 性能优化建议

为了充分发挥Doris处理半结构化数据的优势，以下是几个关键的性能优化建议：

1. 数据模型设计：

   • 将频繁查询的JSON字段提取为独立列
   • 对ARRAY类型设置合理的元素数量上限
   • 避免过度嵌套的JSON结构（建议不超过3层）

2. 分区与分桶策略：

   • 按时间范围分区，便于历史数据管理
   • 根据查询模式选择合适的分桶键
   • 控制单个Tablet的大小在1-5GB之间

3. 查询优化：

   • 为JSONB字段的常用访问路径创建物化视图
   • 使用EXPLAIN分析查询计划，优化JOIN顺序
   • 对ARRAY字段的等值查询使用array_contains函数

4. 资源调配：

   • 为JSONB操作分配足够的计算资源
   • 监控BE节点的内存使用情况
   • 调整query_timeout参数以适应复杂查询

通过本文介绍的方法和技巧，您可以顺利地将MySQL中的半结构化数据迁移到Doris，并充分利用其高性能分析能力。在实际项目中，建议先在小规模数据上验证迁移方案，再逐步扩大迁移范围。