MySQL JSON 文档如何用生成列与索引加速条件查询

MySQL 的 JSON 列适合保存结构会变化的业务属性，但常用筛选条件不能永远靠运行时解析文档。更稳妥的做法是：保留原始 JSON，同时用生成列提取固定路径的标量，再给生成列建立二级索引。字符串路径要用 JSON_UNQUOTE(JSON_EXTRACT(...)) 去掉 JSON 字符串的引号，数值路径则明确声明数值类型，最后用 EXPLAIN 检查查询表达式是否与生成列定义匹配。

JSON 路径与可索引标量的边界

先把查询条件分成两类：用户经常按 $.tenant_id、$.status 筛选的标量字段，适合提取；数组、任意深度通配路径或结构经常变化的对象，不适合直接当作一个普通 B-Tree 键。MySQL 文档明确说明 JSON 列不能直接建立普通索引，因此索引对象应是生成列，而不是原始文档。

下面的例子把订单属性放在 attrs 中，同时抽出租户编号和金额。tenant_id 使用字符串生成列，amount 使用定点数生成列，类型边界在表结构中固定下来。

-- 保留灵活的 JSON 文档，并把高频查询路径声明成标量
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  attrs JSON NOT NULL,
  tenant_id VARCHAR(64)
    GENERATED ALWAYS AS (
      JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.tenant_id'))
    ) STORED,
  amount DECIMAL(12, 2)
    GENERATED ALWAYS AS (
      JSON_EXTRACT(attrs, '$.amount')
    ) STORED,
  INDEX idx_orders_tenant (tenant_id),
  INDEX idx_orders_amount (amount)
);

用生成列保存可查询值

字符串路径最容易踩坑。JSON_EXTRACT(attrs, '$.tenant_id') 返回的是 JSON 值，字符串可能带 JSON 引号；用 JSON_UNQUOTE 或等价的 attrs->>'$.tenant_id'，才能让生成列成为普通字符串语义。金额、数量这类字段则要考虑非法值、缺失路径和精度，不能把所有内容都当成字符串比较。

如果使用虚拟生成列，查询时可以按需计算；使用 STORED 会把结果物化并在写入或更新时维护，换取更直观的索引数据。这里选择 STORED 只是示例，真实项目应结合写入频率、表大小和回归结果决定。

给生成列建立索引并写条件查询

索引建立后，业务查询可以直接写生成列名，表达式边界更清晰；也可以在表达式与生成列定义完全一致时让优化器识别。推荐先用列名写法落地，减少路径字符串、返回类型和函数组合不一致的问题。

-- 使用生成列做等值和范围条件，避免每行临时解析整份文档
SELECT id, attrs, amount
FROM orders
WHERE tenant_id = 'tenant-a'
  AND amount >= 100.00
ORDER BY id;

-- 只有在表达式与生成列定义一致时，才把表达式匹配交给优化器判断
SELECT id
FROM orders
WHERE JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.tenant_id')) = 'tenant-a';

不要为了一个偶发筛选路径就给 JSON 中的每个键增加生成列。每个二级索引都会占用空间，并增加 INSERT、UPDATE、DELETE 的维护工作；如果条件选择性很低，优化器也可能认为全表扫描更划算。

用 EXPLAIN 做回归检查

建完索引不等于查询一定变快。先运行 EXPLAIN，重点看 possible_keys 是否包含目标索引、key 是否实际选择它，以及 rows 是否明显小于表的总规模。若 key 为空，优先核对三件事：JSON 路径是否完全一致、字符串是否缺少 JSON_UNQUOTE、条件值的类型和索引列是否一致。

-- 回归检查索引候选与实际选择结果
EXPLAIN
SELECT id
FROM orders
WHERE tenant_id = 'tenant-a'
  AND amount >= 100.00;

-- 检查生成列表达式的实际类型与缺失路径结果
SELECT id, tenant_id, amount,
       JSON_TYPE(JSON_EXTRACT(attrs, '$.amount')) AS amount_json_type
FROM orders
WHERE id = 1;

测试数据还要覆盖路径缺失、JSON null、整数与小数混用、超长字符串和极端金额。不要把一次开发机上的执行计划当成生产结论，数据分布变化后应重新检查。

迁移清单与适用边界

如果某个字段已经稳定、强约束并参与大量组合查询，直接拆成普通列通常比长期维护 JSON 生成列更清楚。JSON 适合承载扩展属性，生成列适合承载少量明确的查询入口，两者并不是二选一。

相关问题

JSON 列能不能直接创建普通索引？

不能按普通列方式直接索引整个 JSON 文档。应提取需要筛选的标量到生成列，或重新建模为普通列。

为什么字符串生成列查不到预期结果？

优先检查 JSON_EXTRACT 返回值是否带引号，以及生成列和 WHERE 条件是否使用了同一条路径与同一套解包函数。

生成列一定比直接 JSON_EXTRACT 快吗？

不一定。只有查询条件稳定、索引选择性合适且写入维护成本可接受时，生成列索引才有实际收益，应以 EXPLAIN 和真实数据分布为准。