JSON 类型的优劣？什么时候应该用 JSON 列，什么时候应该拆成关系表？

考察要点

这道题面试官想看你是否深刻理解关系型数据库的设计哲学与现代 NoSQL 特性融合的边界。考察的核心是：

1. Schema 设计的权衡能力：你是否理解“范式化设计 (Schema-on-Write)”与“半结构化设计 (Schema-on-Read)”各自的优劣和适用场景。
2. 性能优化意识：你是否知道 JSON 类型的存储原理、查询方式，以及最重要的——如何对其进行索引和优化，避免全表扫描。
3. 数据库特性掌握深度：你是否了解特定数据库（如 MySQL 5.7 vs 8.0, PostgreSQL）在 JSON 支持上的差异和演进。

核心概念

1. JSON 数据类型

它是一种原生数据类型，用于存储和操作 JSON (JavaScript Object Notation) 格式的文档。与将其存储为 TEXT 或 VARCHAR 字符串相比，原生 JSON 类型有巨大优势：

• 自动验证：在插入或更新时，数据库会校验数据是否为合法的 JSON 格式，非法数据会直接报错，保证了数据基本有效性。
• 优化存储格式：以 MySQL 5.7.8+ 的 InnoDB 为例，JSON 不以纯文本存储，而是采用一种优化的二进制格式。这允许服务器快速地通过 key 或数组下标直接定位到子元素，而无需解析整个文档。
• 专用函数和操作符：提供了丰富的函数（如 JSON_EXTRACT, JSON_CONTAINS）和操作符（如 ->, ->>）来高效地提取、修改和查询 JSON 内容。

2. Schema-on-Write vs. Schema-on-Read

这是理解 JSON 类型适用场景的理论基础。

3. JSON 索引机制

直接在 JSON 列上创建标准 B-Tree 索引是无效的。必须使用特殊方法：

• MySQL 5.7：基于虚拟生成列的索引 (Generated Columns) 你需要定义一个虚拟列，该列的值从 JSON 文档中提取，然后在这个虚拟列上创建索引。

  sql

  ⧉⛶

   1-- 假设有个 products 表，其中有个 attributes 的 JSON 列
   2-- attributes 的内容：'{"color": "red", "weight_kg": 1.5}'
   3CREATE TABLE products (
   4  id INT PRIMARY KEY,
   5  name VARCHAR(255),
   6  attributes JSON
   7);
   8
   9-- 为 JSON 中的 'color' 字段创建索引
  10ALTER TABLE products
  11  ADD COLUMN color_generated VARCHAR(30) AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.color'))),
  12  ADD INDEX idx_color (color_generated);
  13
  14-- 这样，查询就可以利用索引了
  15-- EXPLAIN SELECT name FROM products WHERE color_generated = 'red';

• MySQL 8.0+：多值索引 (Multi-Valued Indexes) MySQL 8.0 引入了多值索引，可以直接为 JSON 数组中的元素创建索引，而无需生成列。这是对 JSON 数组查询场景的巨大优化。

• PostgreSQL：GIN 索引 (Generalized Inverted Index) PostgreSQL 的 JSONB 类型（优化的二进制 JSON）支持 GIN 索引，可以对 JSON 文档中所有的键或键值对进行索引，提供了非常强大的全文搜索和任意字段查询能力。

  sql

  ⧉⛶

   1-- PostgreSQL 示例
   2CREATE INDEX idx_gin_attributes ON products USING GIN (attributes);
   3
   4-- 可以高效查询包含特定键值对的记录
   5SELECT name FROM products WHERE attributes @> '{"color": "red"}';

常见低分回答

1. “JSON 就是把字符串存进去，查询时自己解析”

   • 错误点：混淆了原生 JSON 类型和 TEXT 类型。原生 JSON 有二进制优化存储和格式校验，远优于 TEXT。这表明候选人对数据类型底层实现缺乏了解。

2. “JSON 查询很慢，没法用索引，应该永远不用”

   • 错误点：过于绝对，且忽略了索引技术。表明候选人知识停留在旧版本或不了解 JSON 的高级用法，如生成列索引或 GIN 索引。高分回答应是“有性能代价，但可以通过...方式优化”。

3. “为了灵活，我把用户表的核心信息（如用户名、手机号）也放进 JSON 里”

   • 错误点：滥用 JSON，违反了数据库设计基本原则。核心、稳定、需要强约束和频繁查询的字段，必须是独立列。这种回答会给面试官留下“缺乏设计常识”的印象。

4. 在 WHERE 子句中对 JSON 列使用函数，但没建对应的索引

   • 反例 SQL：SELECT * FROM products WHERE JSON_EXTRACT(attributes, '$.color') = 'red';
   • 错误点：在没有为 JSON_EXTRACT 的结果创建生成列索引的情况下，这条 SQL 会导致全表扫描。正确的做法是查询建立好的生成列 color_generated。

工程实践与优化

什么时候应该用 JSON 列 (优点与场景)

1. 存储稀疏或动态的属性：例如电商商品的规格参数。笔记本有“屏幕尺寸”，衣服有“尺码”，为每种可能的属性都创建一列是不现实的。将这些非共性的属性放入一个 attributes JSON 列是完美选择。
2. Schema 快速迭代的业务：创业初期或新功能探索，需求频繁变更。使用 JSON 可以避免频繁的 ALTER TABLE 操作，后者在表数据量大时会造成长时间锁表或主从延迟。
3. 存储非结构化的日志或事件数据：从客户端或第三方 API 接收到的数据结构多变，可以先将其完整存入 JSON 列，便于后续异步处理和分析，保证数据不丢失。
4. 配置信息或设置：用户的个性化设置、应用的 feature flags 等，其结构本身就是键值对或嵌套对象，用 JSON 存储非常自然。

什么时候应该拆成关系表 (缺点与场景)

1. 核心业务实体：如 users 的 id, username, email；orders 的 id, user_id, amount, status。这些字段结构稳定、查询频繁、需要强一致性。
2. 需要强数据完整性约束的字段：如果某个属性需要是 NOT NULL，或者需要作为外键关联到另一张表（如 order 中的 user_id），那么它必须是独立列。JSON 内部无法实现数据库级别的外键约束。
3. 频繁用于 WHERE、JOIN、GROUP BY 或 ORDER BY 的字段：即使可以为 JSON 内部字段创建索引，其性能通常仍略逊于原生列索引。如果一个字段是查询的核心条件，应将其提升为独立列。
4. 需要进行数值聚合计算：对大量行进行 SUM(json_field) 或 AVG(json_field) 的性能远不如对原生 DECIMAL 或 INT 列进行聚合。

最佳实践：混合模式 (Hybrid Model)

在真实生产中，最佳方案往往是“关系型为主，JSON 为辅”的混合模式。

示例：设计一个 products 表

 1CREATE TABLE products (
 2    -- 核心、稳定、频繁查询的字段作为独立列
 3    id BIGINT AUTO_INCREMENT PRIMARY KEY,
 4    sku VARCHAR(100) NOT NULL UNIQUE,
 5    name VARCHAR(255) NOT NULL,
 6    brand_id INT NOT NULL,
 7    price DECIMAL(10, 2) NOT NULL,
 8    status TINYINT DEFAULT 0,
 9
10    -- 稀疏、多变的非核心属性放入 JSON 列
11    attributes JSON,
12
13    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
14    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
15
16    INDEX idx_brand_id (brand_id),
17    INDEX idx_price (price)
18);

这种设计兼具了关系型数据库的性能、一致性与 JSON 类型的灵活性。

常见追问与延伸题目

1. 追问：如果我要把一个现有的、存储 JSON 字符串的 TEXT 列迁移到原生的 JSON 类型，需要注意什么？

   • 回答要点 1 (数据清洗)：TEXT 列可能包含非法的 JSON 字符串。在迁移前，必须先用 JSON_VALID() 函数找出所有非法数据并进行修复或清理。SELECT id FROM my_table WHERE JSON_VALID(text_col) = 0;
   • 回答要点 2 (迁移操作)：ALTER TABLE my_table MODIFY COLUMN text_col JSON; 这个 DDL 操作在数据量大时会锁表很久。应使用 pt-online-schema-change 或 gh-ost 等在线 DDL 工具。如果不能用工具，则可以采用“创建新列 -> 触发器同步数据 -> 分批回填历史数据 -> 切换应用 -> 删除旧列”的无停机方案。

2. 追问：PostgreSQL 中的 JSON 和 JSONB 有什么区别？我应该用哪个？

   • 回答要点 1 (存储)：JSON 类型存储的是输入文本的精确副本，保留空格、重复键和键的顺序。JSONB (B for Binary) 存储的是二进制优化格式，它会去重、排序键，并且不保留多余空格。
   • 回答要点 2 (性能)：JSON 插入稍快（无需转换），但查询慢（每次查询都要重新解析）。JSONB 插入稍慢（需要转换成二进制），但查询快得多，因为它能利用优化的内部结构。
   • 回答要点 3 (索引)：JSONB 支持强大的 GIN 索引，可以极大地加速内部字段的查询。JSON 类型则不支持。
   • 结论：除非有特殊需求要保留原始 JSON 文本格式，否则在 PostgreSQL 中 永远优先使用 JSONB。

3. 追问：如果 JSON 中的某个字段需要像外键一样引用另一张表，该如何设计？

   • 回答要点 1 (识别问题)：首先要明确，JSON 类型本身不提供数据库级别的外键约束。这是一个设计上的权衡。
   • 回答要点 2 (错误方案)：依赖应用层来保证数据一致性。比如，在 products 表的 attributes JSON 中存一个 {"brand_id": 123}，然后由应用代码来确保 brands 表中存在 ID 为 123 的记录。这种方式很脆弱，容易产生脏数据。
   • 回答要点 3 (正确方案)：这正是“何时拆分”的经典信号。如果一个字段具有引用完整性（Referential Integrity）的需求，它就不应该存在于 JSON 中。正确的做法是将其 提升为一个独立的、带有 FOREIGN KEY 约束的列。例如，products 表应该有一个 brand_id 列，并建立 FOREIGN KEY (brand_id) REFERENCES brands(id)。