罗罗编程学习记录
本页内容 展开查看

05.12.4 宽表与稀疏列场景选型

所属:全栈开发面试题库 / 05 数据库与存储 / 05.12 图、时序与宽表

覆盖范围

  • 宽表建模的收益与代价
  • 稀疏列与稀疏存储
  • EAV 模型 vs JSON 列
  • 动态字段场景选型
  • 列存宽表与行存宽表

高频题速览

编号 题目 难度 频率
05.12.4-001 为什么 OLAP 用宽表 基础 ★★★★
05.12.4-002 稀疏列怎么存储 进阶 ★★★
05.12.4-003 EAV 和 JSON 列怎么选 进阶 ★★★
05.12.4-004 动态字段业务怎么建模 进阶 ★★★
05.12.4-005 宽表是不是越大越好 基础 ★★★

05.12.4-001 为什么 OLAP 用宽表?

频率 ★★★★ | 难度 基础

一句话结论

OLAP 宽表把维度冗余到事实表,查询免 JOIN,列存只读涉及列;以空间换时间,适合分析扫描。


05.12.4-002 稀疏列怎么存储?

频率 ★★★ | 难度 进阶

一句话结论

稀疏列(大量 NULL)在列存中用 bitmap/run-length 编码压缩,NULL 不占空间;行存中 NULL 仍占位,宽表稀疏场景列存更优。


05.12.4-003 EAV 和 JSON 列怎么选?

频率 ★★★ | 难度 进阶

一句话结论

EAV(实体-属性-值)把动态属性拆成行,灵活但 JOIN 复杂;JSON 列在 PG/MySQL 中用 JSONB,半结构化,可索引;属性数量少且固定用 JSON,大量稀疏属性用 EAV 或宽表。


05.12.4-004 动态字段业务怎么建模?

频率 ★★★ | 难度 进阶

一句话结论

业务字段不固定(如商品属性)用 JSONB 或 EAV;字段固定但频繁新增用 JSONB 灵活 schema;不要在 RDBMS 里无限加列。


05.12.4-005 宽表是不是越大越好?

频率 ★★★ | 难度 基础

一句话结论

不是。宽表过宽会导致单行列大、更新成本高;OLAP 宽表按主题域拆分,不要把所有维度堆到一张表。


05.12.4-006 PostgreSQL JSONB 怎么索引?

频率 ★★ | 难度 进阶

一句话结论

JSONB 用 GIN 索引支持 JSON 路径查询(data->>'name');表达式索引对特定字段建 B-tree;不要全列 GIN,索引膨胀。


05.12.4-007 稀疏列在列存里怎么存?

频率 ★★ | 难度 进阶

一句话结论

列存对 NULL 用 bitmap 或 run-length 编码,NULL 不占存储空间;行存中 NULL 仍占字节,稀疏宽表列存比行存省空间。


05.12.4-008 商品属性怎么建模?

频率 ★★ | 难度 基础

一句话结论

固定属性用宽表列;动态属性(不同品类属性不同)用 JSONB 或 EAV;搜索用 ES 把 JSON 展开为多字段索引。


05.12.4-009 宽表更新为什么慢?

频率 ★★ | 难度 进阶

一句话结论

宽表一行列多,更新时整行重写(行存)或重写该列的列存块;频繁更新的字段不要放宽表,拆分冷热表。


05.12.4-010 列存宽表怎么选列?

频率 ★★ | 难度 基础

一句话结论

按查询模式选列:查询常用的维度和指标列放一起,不常用的大字段(文本/图片)拆出去;列存只读涉及列,列太多不会影响查询性能但会影响写入和存储。

典型场景与代码示例

OLAP 宽表结构

-- 订单宽表:事实表 + 常用维度冗余
CREATE TABLE dwd_order_wide (
  order_id BIGINT,
  user_id BIGINT,
  user_name STRING,
  user_city STRING,
  product_id BIGINT,
  product_name STRING,
  category STRING,
  amount DECIMAL(18,2),
  pay_time DATETIME,
  dt STRING
) PARTITIONED BY (dt);
-- 查询直接扫这张表,免 JOIN

EAV vs JSON 对比

EAV 模型:
  entity_id | attr_key | attr_value
  100       | color    | red
  100       | size     | L

JSON 模型:
  {"color": "red", "size": "L"}
-- 动态属性少时用 JSON,大量稀疏属性用 EAV

JSONB 索引

-- PG GIN 索引
CREATE INDEX idx_data ON products USING GIN (data);

-- 查询 JSON 字段
SELECT * FROM products WHERE data->>'color' = 'red';

-- 表达式索引(特定字段)
CREATE INDEX idx_color ON products ((data->>'color'));

面试高频追问

问:宽表是不是列越多越好?

答:不是。列太多单行列大,更新成本高;OLAP 宽表按主题域拆,不要把所有维度堆一张表。

问:稀疏列在行存和列存里有什么区别?

答:行存 NULL 仍占字节,稀疏宽表行存浪费空间;列存用 bitmap 编码 NULL,不占空间。

问:动态字段业务怎么建模?

答:字段不固定用 JSONB 或 EAV;不要在 RDBMS 里无限加列;搜索用 ES 展开 JSON。

问:JSONB 和 EAV 怎么选?

答:属性少且固定用 JSONB;大量稀疏属性用 EAV;EAV 查询需多 JOIN,JSONB 查询简单但索引弱。

核心要点速记

模型 特点 适用
OLAP 宽表 维度冗余到事实表 分析查询免 JOIN
JSONB 半结构化、可索引 动态字段
EAV 实体-属性-值 大量稀疏属性
稀疏列 列存 NULL 不占空间 稀疏宽表

宽表设计要点:

  • 查询常用的维度和指标放一起
  • 不常用的大字段拆出去
  • 列存只读涉及列,列多不影响查询

一句话记忆:OLAP 宽表用空间换时间;动态字段用 JSONB,不要无限加列。

相关概念辨析

宽表 vs 长表

维度 宽表 长表
列数 多 少
行数 少 多
查询 快 慢
存储 稀疏浪费 紧凑

稀疏列 vs EAV

  • 稀疏列:行内列多但很多 NULL。
  • EAV:实体-属性-值,行列混合。
  • 选择:列数固定用稀疏列;列数动态用 EAV。

05.12.4-009 EAV 模型的性能问题是什么?

频率 ★★ | 难度 进阶

一句话结论

EAV(实体-属性-值)模型:列数动态,用 (entity_id, attr_key, attr_value) 三元组存储;查询要多次 JOIN,性能差。

概念解释

  • EAV:实体-属性-值,三元组存储。
  • 性能问题:查询一个实体的所有属性,要多次 JOIN。
  • 适合:列数动态的场景。

原理流程

  1. 存储:(entity_id, attr_key, attr_value)。
  2. 查询:SELECT * FROM eav WHERE entity_id=1,要 JOIN 多次。
  3. 适合:列数动态,如用户画像。

常见误区

  • 误区:EAV 就是宽表。正解:EAV 是行列混合,宽表是行列存储。

追问延伸

  • EAV 劣势:查询复杂,性能差。
  • 选择:列数固定用宽表;列数动态用 EAV。

05.12.4-010 JSON 字段 vs 宽表怎么选?

频率 ★★★ | 难度 进阶

一句话结论

列数固定用宽表;列数动态用 JSON 字段;JSON 字段灵活但查询性能差,宽表性能好但不灵活。

概念解释

  • 宽表:列数固定,性能好。
  • JSON 字段:列数动态,灵活但性能差。
  • 选择:看列数是否固定。

原理流程

  1. 宽表:列数固定,查询性能好。
  2. JSON 字段:列数动态,查询要解析 JSON。
  3. 选择:列数固定用宽表;列数动态用 JSON。

常见误区

  • 误区:JSON 字段就是宽表。正解:JSON 字段灵活但查询性能差。

追问延伸

  • MySQL JSON:支持 JSON 索引,但性能不如宽表。
  • 选择:核心字段用宽表;扩展字段用 JSON。

05.12.4-011 稀疏列怎么索引?

频率 ★★ | 难度 进阶

一句话结论

稀疏列:大量 NULL,关系型索引浪费;用 HBase/Cassandra 宽列存储,或用 JSON 字段。

概念解释

  • 稀疏列:大量 NULL,关系型索引浪费。
  • HBase/Cassandra:宽列存储,稀疏列友好。
  • JSON 字段:灵活,稀疏列不浪费。

原理流程

  1. 关系型:稀疏列大量 NULL,索引浪费空间。
  2. HBase/Cassandra:稀疏列只存有值的列。
  3. JSON 字段:只存有值的 key。

常见误区

  • 误区:稀疏列用关系型就行。正解:稀疏列用 HBase/Cassandra 或 JSON。

追问延伸

  • HBase:宽列存储,稀疏列友好。
  • JSON:MySQL/PostgreSQL 支持 JSON 字段。

05.12.4-012 用户画像表怎么设计?

频率 ★★★ | 难度 进阶

一句话结论

用户画像表:核心字段用宽表(user_id、age、gender),扩展字段用 JSON 或 EAV;列数动态用 JSON。

概念解释

  • 核心字段:固定,用宽表。
  • 扩展字段:动态,用 JSON 或 EAV。
  • 选择:核心字段用宽表;扩展字段用 JSON。

原理流程

  1. 核心字段:user_id、age、gender,用宽表。
  2. 扩展字段:标签列表,用 JSON。
  3. 查询:核心字段走宽表索引;扩展字段走 JSON 解析。

常见误区

  • 误区:用户画像全用宽表。正解:核心字段宽表,扩展字段 JSON。

追问延伸

  • 用户画像:标签动态,用 JSON 存储。
  • 查询:核心字段高频查询,走宽表索引。

待整理清单

  • [ ] PostgreSQL JSONB 索引与 GIN
  • [ ] 宽表更新与部分列选择
  • [ ] 列存宽表的列裁剪