MySQL 8.0+存储每日数据绘制年图表的最优存储方案选型咨询

一、检索性能对比 #

方案A（行式存储） #

表结构示例：

CREATE TABLE customer_data_points (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    data_date DATE NOT NULL,
    value INT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    UNIQUE KEY idx_customer_date (customer_id, data_date)
);

• 核心优势：通过(customer_id, data_date)联合索引，查询单个客户最近365条数据时，数据库能直接定位目标时间范围，无需扫描无关数据。即使是1000个客户、每个存5年数据（共182.5万条记录），查询延迟也极低且稳定。
• 小劣势：查询返回多行结构化数据，前端需做一次简单格式转换，将每行的data_date和value拼接成绘图所需的数组格式。

方案B（JSON存储） #

表结构示例：

CREATE TABLE customer_data_json (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    dataset JSON NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    UNIQUE KEY idx_customer (customer_id)
);

• 表面优势：单个客户的所有数据存在一条记录里，查询时直接返回整个JSON字段，无需多行聚合。
• 实际劣势：
  1. 若要获取最近365条数据，要么在数据库中解析JSON做过滤（无法对JSON内部的日期字段建立高效索引，性能极差），要么把5年的完整JSON数据传回前端再过滤——会浪费大量带宽，数据量越大越明显。
  2. 每日更新时，需先读取整个JSON、修改后再写入，并发场景下易出现锁冲突，而方案A的单行插入操作无此问题。

二、扩展性分析 #

当客户数量增至1000、每个客户存储5年数据时：

• 方案A：总数据量约180万条，MySQL对这种规模的结构化数据处理毫无压力，联合索引维护成本极低，查询性能不会随数据增长出现明显衰减。
• 方案B：每条记录的JSON字段体积持续增大，数据库存储开销上升，读写时的序列化/反序列化耗时逐渐增加，且无法通过索引优化查询，性能衰减速度远快于方案A。

三、前端适配性 #

针对JavaScript绘图库：

• 方案B返回的JSON可直接解析为JS数组，但如果需要筛选最近365条数据，仍需前端循环处理；若由数据库提前过滤，又要承担JSON解析的性能损耗。
• 方案A返回的结构化数据，仅需几行代码即可转换为绘图所需格式，例如：

// 假设后端返回的是[{data_date: '2022-11-10', value: 35}, ...]
const chartData = rawData.map(item => [item.data_date, item.value]);

这个转换逻辑简单，耗时可忽略，反而避免了传输冗余数据。

最终结论 #

优先选择方案A（行式存储），理由如下：

1. 检索性能更优且稳定，数据量和客户数增长后仍能保持高效查询。
2. 每日插入操作更简单高效，并发性能更可靠。
3. 前端转换成本极低，不会成为性能瓶颈。

内容的提问来源于stack exchange，提问作者flyingbot91