MySQL 生成列怎样配合索引解决 JSON 条件查询

我在订单表里给扩展信息保留 JSON 后，最先遇到的不是写入问题，而是查询越来越慢：业务条件明明只看 status 或 customer_id，SQL 却每次都要从整段 JSON 中重新取值。更实用的做法是，把稳定的 JSON 路径提取成生成列，再给这列建立二级索引。

本文只讨论 MySQL 8.4、InnoDB 和 JSON 中的标量字段。官方生成列文档地址：https://dev.mysql.com/doc/refman/8.4/en/create-table-generated-columns.html。数组包含、动态路径和全文检索不在这次方案里。

JSON 列本身不能直接按普通列建立索引；先把固定路径转换成明确类型的生成列，再索引生成列，才是这类条件查询的稳定落点。

• 路径固定：生成列表达式必须和业务查询的字段语义一致。
• 类型明确：数值条件提取成数值，状态条件提取成合适长度的字符串。
• 代价可控：索引会增加写入维护成本，STORED 生成列还会增加表空间占用。

先把 JSON 路径和比较类型定死

假设订单的扩展信息放在 payload，内容类似 {"status":"paid","customer_id":1001}。状态适合映射为字符串，客户编号则应该映射成数值。这里使用 ->> 取出未带 JSON 引号的标量，再用生成列的声明类型固定结果。

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  payload JSON NOT NULL,
  -- 状态是固定的业务字符串，避免用带 JSON 引号的结果比较
  status_code VARCHAR(32) GENERATED ALWAYS AS (payload->>'$.status') STORED,
  -- 编号按数值参与等值查询，显式转换可以避免字符串比较
  customer_id BIGINT UNSIGNED GENERATED ALWAYS AS (
    CAST(payload->>'$.customer_id' AS UNSIGNED)
  ) STORED,
  -- 只为稳定的高频条件建立索引
  INDEX idx_orders_status (status_code),
  INDEX idx_orders_customer (customer_id)
) ENGINE=InnoDB;

生成列表达式使用确定性的内置函数和运算符；不要在其中放子查询、用户变量或依赖当前时间的函数。JSON 路径不存在时，提取结果会成为 NULL，所以缺失字段是否应该被排除，必须在业务规则里先说清楚。

给现有表补生成列和索引

如果表已经存在，可以用一次 ALTER TABLE 增加列和索引。对写入量很大的表，建议先评估变更窗口和索引构建影响；这一步的重点是让生成列表达式与线上查询使用同一条路径。

ALTER TABLE orders
  -- 先把 JSON 中的稳定字段落到可索引的标量列
  ADD COLUMN status_code VARCHAR(32)
    GENERATED ALWAYS AS (payload->>'$.status') STORED,
  -- 数字字段使用数值类型，避免隐式类型转换
  ADD COLUMN customer_id BIGINT UNSIGNED
    GENERATED ALWAYS AS (CAST(payload->>'$.customer_id' AS UNSIGNED)) STORED,
  -- 生成列创建后再建立对应的二级索引
  ADD INDEX idx_orders_status (status_code),
  ADD INDEX idx_orders_customer (customer_id);

MySQL 也支持给虚拟生成列建立 InnoDB 二级索引。虚拟列不额外保存一份列值，但索引维护时仍要计算并写入索引记录；STORED 列读取更直观，却会同时占用生成列和索引的空间。我的经验是：先按读写比例和磁盘预算选一种，不要把所有 JSON 路径都提前展开。

查询时最容易踩的三个坑

第一，查询条件要匹配生成列的类型。customer_id 是无符号整数，就按数字参数查询；status_code 是字符串，就保持状态值的大小写和长度约定。第二，尽量直接使用生成列名，少在索引列外面再包函数。第三，新增索引后用 EXPLAIN 检查是否出现预期索引，但不要把估算行数当成固定运行结果。

EXPLAIN
SELECT id, payload
FROM orders
WHERE status_code = 'paid'
  AND customer_id = 1001;

-- 只取业务需要的列，避免把 JSON 全文当成查询键
SELECT id
FROM orders
WHERE customer_id = 1001;

官方优化器还可以识别与生成列定义匹配的查询表达式，但实际项目里我仍倾向于直接写生成列名：表达式更短，类型意图更明显，也不容易因为路径、解引号方式或转换写法变化而失去可预期性。

这套方案适合什么场景

它适合 JSON 结构允许扩展，但有少量固定热字段需要稳定过滤、排序或连接的表。生成列让这些字段拥有清晰的数据类型，索引则把查询入口从整段 JSON 缩小到标量键。

最后可以记住一句话：生成列解决“从 JSON 取哪个值、取成什么类型”，索引解决“怎样快速找到这个值”。两者必须一起设计，只有列没有索引，或者索引表达式和查询表达式不一致，都不能稳定解决 JSON 条件查询。

相关问题

生成列用 VIRTUAL 还是 STORED？

VIRTUAL 节省表内列存储，但索引维护仍有计算成本；STORED 读取和调试更直观，却需要额外磁盘空间。按读写比例、索引覆盖情况和磁盘预算选择。

JSON 路径不存在时会命中索引吗？

不存在的路径通常得到 NULL，不会等同于空字符串或零。若业务需要把缺失值和明确的空值区分开，应单独设计条件，不要依赖隐式转换。