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。
- 适合:列数动态的场景。
原理流程
- 存储:(entity_id, attr_key, attr_value)。
- 查询:SELECT * FROM eav WHERE entity_id=1,要 JOIN 多次。
- 适合:列数动态,如用户画像。
常见误区
- 误区:EAV 就是宽表。正解:EAV 是行列混合,宽表是行列存储。
追问延伸
- EAV 劣势:查询复杂,性能差。
- 选择:列数固定用宽表;列数动态用 EAV。
05.12.4-010 JSON 字段 vs 宽表怎么选?
频率 ★★★ | 难度 进阶
一句话结论
列数固定用宽表;列数动态用 JSON 字段;JSON 字段灵活但查询性能差,宽表性能好但不灵活。
概念解释
- 宽表:列数固定,性能好。
- JSON 字段:列数动态,灵活但性能差。
- 选择:看列数是否固定。
原理流程
- 宽表:列数固定,查询性能好。
- JSON 字段:列数动态,查询要解析 JSON。
- 选择:列数固定用宽表;列数动态用 JSON。
常见误区
- 误区:JSON 字段就是宽表。正解:JSON 字段灵活但查询性能差。
追问延伸
- MySQL JSON:支持 JSON 索引,但性能不如宽表。
- 选择:核心字段用宽表;扩展字段用 JSON。
05.12.4-011 稀疏列怎么索引?
频率 ★★ | 难度 进阶
一句话结论
稀疏列:大量 NULL,关系型索引浪费;用 HBase/Cassandra 宽列存储,或用 JSON 字段。
概念解释
- 稀疏列:大量 NULL,关系型索引浪费。
- HBase/Cassandra:宽列存储,稀疏列友好。
- JSON 字段:灵活,稀疏列不浪费。
原理流程
- 关系型:稀疏列大量 NULL,索引浪费空间。
- HBase/Cassandra:稀疏列只存有值的列。
- JSON 字段:只存有值的 key。
常见误区
- 误区:稀疏列用关系型就行。正解:稀疏列用 HBase/Cassandra 或 JSON。
追问延伸
- HBase:宽列存储,稀疏列友好。
- JSON:MySQL/PostgreSQL 支持 JSON 字段。
05.12.4-012 用户画像表怎么设计?
频率 ★★★ | 难度 进阶
一句话结论
用户画像表:核心字段用宽表(user_id、age、gender),扩展字段用 JSON 或 EAV;列数动态用 JSON。
概念解释
- 核心字段:固定,用宽表。
- 扩展字段:动态,用 JSON 或 EAV。
- 选择:核心字段用宽表;扩展字段用 JSON。
原理流程
- 核心字段:user_id、age、gender,用宽表。
- 扩展字段:标签列表,用 JSON。
- 查询:核心字段走宽表索引;扩展字段走 JSON 解析。
常见误区
- 误区:用户画像全用宽表。正解:核心字段宽表,扩展字段 JSON。
追问延伸
- 用户画像:标签动态,用 JSON 存储。
- 查询:核心字段高频查询,走宽表索引。
待整理清单
- [ ] PostgreSQL JSONB 索引与 GIN
- [ ] 宽表更新与部分列选择
- [ ] 列存宽表的列裁剪