SQL JSON 数据类型查询与处理优化方法

SQL 中的 JSON 数据类型虽灵活，但直接用 SELECT * 或模糊匹配查 JSON 字段容易慢、难维护。优化核心是：避免全表解析 JSON、优先用生成列 + 索引、合理选择函数与路径表达式。

用生成列（Generated Column）+ 索引加速常用查询

JSON 字段本身无法直接建索引，但可提取关键字段为虚拟或存储型生成列，并在其上创建索引。

• 例如 MySQL 中，提取用户邮箱：
  ALTER TABLE users ADD COLUMN email VARCHAR(255) AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.email'))) STORED;
  再建索引：
  CREATE INDEX idx_user_email ON users(email);
• PostgreSQL 可用表达式索引：
  CREATE INDEX idx_orders_status ON orders ((data->>'status'));
• 虚拟列不占磁盘空间（MySQL），但需确保表达式确定且无副作用；存储列会持久化，适合高频查询场景。

慎用 JSON_CONTAINS / JSON_EXTRACT 等运行时解析函数

这些函数每次查询都会解析整个 JSON 文本，无法利用索引，数据量大时性能明显下降。

• 避免写成：
  WHERE JSON_CONTAINS(tags, '"urgent"') AND created_at > '2024-01-01';
• 改用预提取字段或数组展开（如 PostgreSQL 的 jsonb_array_elements_text 配合物化视图或 CTE 缓存结果）
• MySQL 8.0+ 支持多值索引（JSON_CONTAINS 在特定条件下可走索引），但仅限于 JSON 列上有 MEMBER OF 或 JSON_CONTAINS 检查顶层数组，且需配合生成列才稳定高效。

按数据库选对 JSON 类型与函数

不同数据库对 JSON 的底层实现和优化能力差异大，选型直接影响性能。

• PostgreSQL 推荐用 jsonb（二进制格式，支持索引、路径查询、去重排序），不用 json（文本存储，每次解析开销大）
• MySQL 用 JSON 类型（自动校验 + 优化解析），别用 TEXT 存 JSON 字符串（失去类型保障和函数支持）
• SQL Server 用 NVARCHAR(MAX) + ISJSON() 校验，查询靠 OPENJSON() 展开，适合一次性分析；高频字段仍建议拆到关系列

批量处理 JSON 时减少单行解析次数

对 JSON 数组或嵌套结构做聚合、转换时，避免在 WHERE/HAVING 中反复调用 JSON 函数。

• 用 CTE 或子查询提前展开关键字段：
  WITH parsed AS (SELECT id, JSON_EXTRACT(data, '$.items') AS items FROM orders)
  后续再对 items 做 JSON_TABLE（MySQL 8.0.22+）或 jsonb_path_query（PG）处理
• MySQL 中大量使用 JSON_TABLE 替代多次 JSON_EXTRACT，一次解析、多字段输出
• 导出或 ETL 场景下，优先在应用层解析 JSON，数据库只做结构化字段过滤与聚合