当前位置：首页 > news >正文

数据库中的数组: MySQL与StarRocks的数组操作解析

news 来源：原创 2025/9/21 14:51:12

在现代数据处理中, 数组 (Array) 作为一种高效存储和操作结构化数据的方式, 被广泛应用于日志分析, 用户行为统计, 标签系统等场景. 然而, 不同数据库对数组的支持差异显著. 本文将以MySQL和StarRocks为例, 深入解析它们的数组操作能力, 并对比其适用场景.

文章目录

- 一为什么需要数组操作？
- 二 MySQL: 基于JSON数组的曲线救国
- - 1. 创建与初始化
  - 2. 修改与更新
  - 3. 查询与搜索
  - 4. 性能优化技巧
- 三 StarRocks: 原生数组的高性能实践
- - 1. 原生数组的优势
  - 2. 核心操作示例
- 四如何选择？MySQL vs StarRocks对比
- 五实战建议

一为什么需要数组操作？

数组能够将多个相关值聚合为单个字段, 例如:

用户标签: ["科技爱好者", "程序员", "健身达人"]
订单商品ID列表: [1001, 1003, 1005]
时序数据: [25.3, 24.8, 26.1]

传统关系型数据库 (如MySQL) 通常依赖多表关联处理这类需求, 而现代分析型数据库 (如StarRocks) 通过原生数组类型和丰富的函数, 大幅提升了数据操作的效率. 接下来, 我们将分别探讨两者的实现方式.

二 MySQL: 基于JSON数组的曲线救国

MySQL虽不支持原生数组类型, 但从5.7版本起引入了JSON类型, 通过JSON数组模拟数组操作, 适合轻量级场景.

1. 创建与初始化

JSON_ARRAY(): 快速构造JSON数组

-- 创建包含混合类型的数组
SELECT JSON_ARRAY('手机', 2999, TRUE); 
-- 输出: ["手机", 2999, true]

JSON_ARRAYAGG(): 聚合多行数据为数组

-- 将用户订单的商品ID聚合为数组
SELECT user_id, JSON_ARRAYAGG(product_id) 
FROM orders GROUP BY user_id;

2. 修改与更新

JSON_INSERT(): 在指定位置插入元素

SET @cart = '["笔记本", "钢笔"]';
SELECT JSON_INSERT(@cart, '$[2]', '橡皮'); 
-- 输出: ["笔记本", "钢笔", "橡皮"]

JSON_REMOVE(): 删除指定索引的元素

SELECT JSON_REMOVE('["A", "B", "C"]', '$[1]'); 
-- 输出: ["A", "C"]

3. 查询与搜索

JSON_EXTRACT() (或->运算符) : 提取元素

SELECT tags->'$[0]' FROM user_profile 
WHERE id = 1001; -- 提取第一个标签

JSON_CONTAINS(): 判断是否包含特定值

SELECT * FROM products 
WHERE JSON_CONTAINS(category_ids, '1003');

4. 性能优化技巧

多值索引 (Multi-Valued Indexes) : 加速JSON数组查询

CREATE INDEX idx_tags ON users (CAST(profile->'$.tags' AS CHAR(255) ARRAY)
);

JSON_TABLE(): 将数组转换为临时表进行JOIN操作

SELECT user_id, tag 
FROM users, JSON_TABLE(profile->'$.tags', '$[*]' COLUMNS (tag VARCHAR(50) PATH '$')
) AS tags;

三 StarRocks: 原生数组的高性能实践

StarRocks作为分析型数据库, 从2.5版本起支持原生数组类型 (ARRAY<T>) , 并针对大数据场景优化了性能, 适合复杂计算.

1. 原生数组的优势

存储高效: 二进制编码, 比JSON解析速度更快.
计算优化: 向量化执行引擎加速聚合, 过滤等操作.
类型安全: 强制元素类型一致 (如ARRAY<INT>) .

2. 核心操作示例

创建数组

-- 直接构造数组
SELECT ARRAY(1, 2, 3); -- 输出: [1, 2, 3]-- 从字符串转换
SELECT CAST('[2023-01-01, 2023-01-02]' AS ARRAY<DATE>);

动态修改

-- 追加元素
SELECT ARRAY_APPEND(scores, 95) FROM student;-- 删除所有匹配值
SELECT ARRAY_REMOVE(ARRAY(1,2,2,3), 2); -- 输出: [1,3]

高级分析

-- 计算数组统计值
SELECT ARRAY_SUM(daily_sales) FROM shop; -- 周销量总和-- 生成累加数组
SELECT ARRAY_CUM_SUM(ARRAY(10, 20, 30)); -- 输出: [10, 30, 60]

与Bitmap结合

-- 用户兴趣标签的并集计算
SELECT BITMAP_UNION(ARRAY_TO_BITMAP(tags)) 
FROM user_interest;

四如何选择？MySQL vs StarRocks对比

场景	推荐方案	原因
轻量级业务 (如CMS标签)	MySQL JSON数组	兼容性强, 无需改造表结构, 适合已有MySQL系统的扩展.
实时分析 (如用户画像)	StarRocks原生数组	原生类型性能更高, 支持复杂聚合 (如`ARRAY_UNIQUE_AGG()`) .
混合型数据操作	MySQL多值索引 + `JSON_TABLE()`	兼顾JSON灵活性和查询效率.
海量数据计算	StarRocks数组 + 向量化引擎	分布式架构和列式存储优化, 适合TB级数据分析.

五实战建议

MySQL适用场景
- 数据结构变化频繁 (如动态表单字段) .
- 需要兼容JSON API接口.
- 示例: 电商订单的扩展属性存储.
StarRocks适用场景
- 固定模式的数组分析 (如时序数据, 用户行为序列) .
- 需要高性能聚合计算 (如广告点击流分析) .
- 示例: 分析用户最近30天的登录时间分布.