基于MongoDB的用户自定义表格存储方案技术咨询

背景与现有设计 #

我计划基于MongoDB实现可持久化的用户自定义表格（类电子表格）存储，支持用户自定义任意数量的列和行，列的名称、数据类型及元数据（唯一性、默认值、显示顺序等）均无法预先确定。

经过调研，已排除以下两种方案：

• SQL的EAV（实体-属性-值）方案
• 使用JSON/JSONB列存储数据的SQL方案（因预期存在大量单元格更新操作）

最终选择MongoDB作为适配方案，且需支持多租户（不同组织用户创建各自表格）。

示例表格 #

假设有租户创建的2×2表格，表名为“停车位”，描述为“我们的停车位列表”，结构如下：

MongoDB设计方案 #

创建单个名为tables的集合，每个文档对应一个租户的一张表格，包含tenantId字段，示例文档如下：

{
  "_id":"507f1f77bcf86cd799439011",
  "name":"Parking spots",
  "description":"List of our parking spots",
  "tenantId":1,
  "createdAt":"2023-03-08T15:47:49.382Z",
  "updatedAt":"2023-03-08T15:47:49.382Z",
  "columns":[
    {
      "name":"parkingType",
      "displayName":"Parking type",
      "order":1,
      "unique":false,
      "defaultValue":"public",
      "dataType":"string",
      "cells":[
        {
          "rowIndex":1,
          "value":"garage"
        },
        {
          "rowIndex":2,
          "value":"public"
        }
      ]
    },
    {
      "name":"numberOfSpots",
      "displayName":"Number of spots",
      "order":2,
      "unique":false,
      "defaultValue":100,
      "dataType":"number",
      "cells":[
        {
          "rowIndex":1,
          "value":10
        },
        {
          "rowIndex":2,
          "value":100
        }
      ]
    }
  ]
}

技术问题解答 #

1. 该方案对列重排、行重排、按单列排序等表格操作的适应性如何？ #

• 列重排：仅需修改columns数组中对应元素的order字段，再对columns按order重新排序即可，操作简单，性能开销低。
• 行重排：需要遍历所有columns下的cells数组，逐个修改单元格的rowIndex值，行数越多操作复杂度越高，且必须保证所有列的rowIndex同步修改，否则会出现数据错位。
• 按单列排序：需先提取目标列的cells数组按value排序，再同步调整其他所有列的cells顺序及rowIndex值。无法利用MongoDB内置排序能力，需在应用层处理，行数越多操作耗时和出错风险越高。

2. 该设计与MongoDB聚合框架及表格标准操作（如MIN、AVG等）的兼容性如何？ #

兼容性较差，核心问题如下：

• 要实现MIN、AVG等统计操作，需先通过$unwind拆解columns数组，再拆解目标列的cells数组，过滤后才能执行统计，聚合管道冗长，性能随列数、行数增加快速下降。
• 行数据分散在不同列的cells数组中，而非以完整行形式存储，无法直接利用MongoDB内置聚合运算符对行数据做操作，跨数组关联的统计逻辑复杂度极高。

3. 如何为指定列创建唯一索引，确保该列所有单元格值唯一？ #

MongoDB无法直接针对嵌套数组内的特定字段做条件唯一约束，因为所有列的单元格都嵌套在columns.cells下，无法区分所属列。可行方案：

• 应用层校验：写入单元格前，先查询目标列的所有cells.value检查重复，再执行写入。需配合单文档事务（天然支持）或多文档事务（MongoDB 4.0+）避免竞态风险。
• 调整文档结构：新增rows数组存储完整行的键值对，保留columns元数据，此时可针对rows.{columnName}创建包含tenantId和tableId的复合唯一索引，但会改变现有设计结构。

4. 该方案存在哪些潜在瓶颈？ #

除已知的16MB单文档大小限制外，还有以下瓶颈：

• 单元格更新性能：每次更新单个单元格，需定位到columns数组的对应列，再定位到cells数组的对应行，操作复杂度随列数、行数增加而上升，大文档更新会占用更多资源。
• 查询与统计效率：跨行/列的统计操作需要复杂的聚合管道，性能随数据规模增长急剧下降。
• 行操作原子性风险：行重排、多行更新需修改多个列的cells数组，操作中途失败会导致数据不一致，依赖事务会增加性能开销。
• 扩展性受限：行/列数量接近文档大小限制时，无法继续扩容，只能拆分文档，引入额外复杂度。

5. 还有哪些可替代的MongoDB设计方案？ #

方案1：按行存储（单文档对应一行数据）

创建两个集合：

• table_metadata：存储表格租户信息、列元数据，示例：

  {
    "_id": "table_1",
    "tenantId": 1,
    "name": "Parking spots",
    "description": "List of our parking spots",
    "columns": [
      {"name": "parkingType", "displayName": "Parking type", "dataType": "string", "unique": false},
      {"name": "numberOfSpots", "displayName": "Number of spots", "dataType": "number", "unique": false}
    ],
    "createdAt": "2023-03-08T15:47:49.382Z"
  }
  

• table_rows：存储每行数据，通过tableId关联元数据，示例：

  {
    "_id": "row_1",
    "tableId": "table_1",
    "tenantId": 1,
    "data": {
      "parkingType": "garage",
      "numberOfSpots": 10
    },
    "createdAt": "2023-03-08T15:47:49.382Z"
  }
  

优势：支持MongoDB原生排序、聚合统计，单元格更新简单，可针对列创建索引，扩展性好；劣势：需关联两个集合查询完整表格，多租户需在索引中加入tenantId保证隔离性。

方案2：单文档对应多行（按批次分组）

将多行数据分组存储在单个文档中，每个文档包含tenantId、tableId、rows数组（每行是键值对），列元数据可单独存在table_metadata集合。
优势：平衡单文档操作便捷性与行存储灵活性，减少集合文档数量；劣势：批次划分增加查询和更新复杂度，仍受单文档大小限制。

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