MariaDB 10.11.3超万列数据表的优化存储方案咨询

结合你的时序帧数据场景，以下几个方案可以实现类单表的读写体验，同时规避列数和行大小限制：

1. 宽表转优化型EAV模型+视图封装 #

把原来的10000列宽表拆成主数据表和元数据表，用视图模拟单表查询：

• 元数据表（metrics_meta）：存储列的元信息，结构示例：

  CREATE TABLE metrics_meta (
      metric_id INT PRIMARY KEY AUTO_INCREMENT,
      metric_name VARCHAR(64) UNIQUE NOT NULL,
      data_type ENUM('FLOAT', 'INT') NOT NULL
  );
  

• 主数据表（frame_data）：存储扁平化的时序数据，结构示例：

  CREATE TABLE frame_data (
      record_time DATETIME NOT NULL,
      metric_id INT NOT NULL,
      float_val FLOAT NULL,
      int_val INT NULL,
      PRIMARY KEY (record_time, metric_id),
      FOREIGN KEY (metric_id) REFERENCES metrics_meta(metric_id)
  );
  

• 创建聚合视图模拟单表：

  CREATE VIEW v_frame_data AS
  SELECT 
      record_time,
      MAX(CASE WHEN metric_name = 'col1' THEN float_val END) AS col1,
      MAX(CASE WHEN metric_name = 'col2' THEN int_val END) AS col2,
      -- 依次列出所有10000个metric对应的CASE语句（可通过脚本批量生成）
      MAX(CASE WHEN metric_name = 'col10000' THEN float_val END) AS col10000
  FROM frame_data
  JOIN metrics_meta USING(metric_id)
  GROUP BY record_time;
  

• 优缺点：
  • 优点：完全规避列数限制，视图查询和单表体验一致；适合写入频率高、查询频率低的场景。
  • 缺点：视图聚合会有一定性能损耗，实时查询大量列时性能不如物理表；初始化视图的CASE语句需要批量生成。

2. 垂直拆分+视图联合（类单表读写） #

将10000列按列数限制（比如每4000列一组）拆分成多个物理表，用视图将它们联合成逻辑单表：

• 拆分物理表示例：

  -- 核心表：存储前4000列
  CREATE TABLE frame_core (
      record_time DATETIME PRIMARY KEY,
      col1 FLOAT, col2 INT, ..., col4000 FLOAT
  );
  -- 扩展表1：存储4001-8000列
  CREATE TABLE frame_ext1 (
      record_time DATETIME PRIMARY KEY,
      col4001 INT, col4002 FLOAT, ..., col8000 INT
  );
  -- 扩展表2：存储8001-10000列
  CREATE TABLE frame_ext2 (
      record_time DATETIME PRIMARY KEY,
      col8001 FLOAT, col8002 INT, ..., col10000 FLOAT
  );
  

• 创建联合视图：

  CREATE VIEW v_frame_data AS
  SELECT 
      c.record_time,
      c.col1, c.col2, ..., c.col4000,
      e1.col4001, ..., e1.col8000,
      e2.col8001, ..., e2.col10000
  FROM frame_core c
  LEFT JOIN frame_ext1 e1 ON c.record_time = e1.record_time
  LEFT JOIN frame_ext2 e2 ON c.record_time = e2.record_time;
  

• 读写优化：
  • 写入时通过事务同时插入三个表，保证数据一致性；也可编写存储过程封装写入逻辑，对外暴露单表写入接口。
  • 查询时直接操作视图，MariaDB会自动优化主键关联的JOIN逻辑，性能损耗极低。
• 优缺点：
  • 优点：性能接近物理单表，读写延迟低；完全兼容原有单表的查询语法。
  • 缺点：需要维护多个物理表，新增列时要调整对应子表和视图；需控制每个子表的行大小不超过65536字节限制。

3. 启用MariaDB ColumnStore列式存储引擎 #

MariaDB ColumnStore专为超宽表、时序分析场景设计，天然支持上万列，无需拆分：

• 安装并启用ColumnStore（CentOS8环境）：

  # 安装ColumnStore插件
  yum install mariadb-columnstore-engine
  # 重启MariaDB服务
  systemctl restart mariadb
  # 启用插件
  mysql -u root -p -e "INSTALL SONAME 'ha_columnstore';"
  

• 创建超宽表：

  CREATE TABLE frame_data (
      record_time DATETIME NOT NULL,
      col1 FLOAT, col2 INT, ..., col10000 FLOAT,
      PRIMARY KEY (record_time)
  ) ENGINE=ColumnStore;
  

• 优缺点：
  • 优点：无需任何表拆分，直接单表操作；查询时仅扫描所需列，分析类查询性能远超InnoDB；天然支持上万列。
  • 缺点：写入性能适合批量导入，单条写入性能不如InnoDB；需要额外配置ColumnStore的内存、磁盘等资源。

方案选择建议 #

• 若以实时读写、低延迟为主：选垂直拆分+视图联合方案。
• 若以批量写入、分析查询为主：选ColumnStore列式存储方案。
• 若列数还可能继续增长，且写入频率远高于查询：选优化型EAV模型+视图方案。

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