MySQL 怎么用生成列保存可索引的计算结果

订单表里经常有“原价 × 折扣”“小计 + 税费”这类可重复计算的字段。每条 SQL 都把表达式写一遍，既容易出现口径不一致，也不方便给计算结果建索引。MySQL 的生成列可以把表达式挂在表结构上；如果结果需要落盘并参与范围查询，可以选择 STORED 后再建立二级索引。

实用做法是：先用生成列固定计算口径，再按读写成本选择 VIRTUAL 或 STORED，最后用 EXPLAIN 确认查询表达式确实能匹配到生成列索引。优化器匹配的不只是“看起来等价”，而是表达式形式和结果类型都要对得上。

先把计算口径固定成生成列

假设订单表保存小计 subtotal 和税率 tax_rate，查询经常按含税金额筛选。生成列定义中的表达式由 MySQL 管理，应用只负责写入基础列：

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  subtotal DECIMAL(12, 2) NOT NULL,
  tax_rate DECIMAL(5, 4) NOT NULL,
  -- 把金额口径固定在表结构中，避免每个调用方各算一遍
  total_amount DECIMAL(12, 2)
    AS (ROUND(subtotal * (1 + tax_rate), 2)) STORED,
  -- 让含税金额可以参与范围查找
  INDEX idx_total_amount (total_amount)
);

这里的 total_amount 不能在 INSERT 或 UPDATE 中直接写具体数值，应用只更新 subtotal、tax_rate 等基础列，MySQL 会重新计算生成列。STORED 会增加写入和存储成本，但适合需要索引、且计算表达式不宜在每次读取时重复执行的字段。

VIRTUAL 和 STORED 怎么选

不写关键字时，生成列默认为 VIRTUAL。它不保存列值，读取行时计算，适合只想统一查询表达式、但不需要把结果作为索引入口的场景。STORED 会在插入或更新时计算并保存，代价是额外存储；生成列值和索引值还会各占一份空间。

这个选择不是“生成列一定更快”。如果基础列更新很频繁而计算结果很少被筛选，保存和维护索引的成本可能不划算；如果读请求反复按同一计算条件过滤，STORED 加索引才有明确收益。

让查询表达式真正匹配生成列索引

查询不一定要直接写 total_amount。MySQL 8.4 文档说明，只要 WHERE、ORDER BY 或 GROUP BY 中的表达式与已索引生成列定义一致，优化器就可能使用该索引：

-- 表达式与生成列定义相同，先用 EXPLAIN 查看计划
EXPLAIN SELECT id, total_amount
FROM orders
WHERE ROUND(subtotal * (1 + tax_rate), 2) >= 1000;

-- 直接引用生成列也更直观，适合应用层统一使用
EXPLAIN SELECT id
FROM orders
WHERE total_amount BETWEEN 1000 AND 2000;

“数学上等价”不代表“优化器一定识别”。例如生成列定义为 subtotal * (1 + tax_rate)，查询写成 (1 + tax_rate) * subtotal，就不要假设它必然命中。比较两种写法时，还要留意整数、字符串和 DECIMAL 之间的结果类型变化。对于 BETWEEN 和 IN()，官方规则还限制了可替换的位置以及比较值的类型。

JSON 计算列最容易漏掉 JSON_UNQUOTE

如果计算列从 JSON 中取字符串，再用它做等值查询，建议在生成列定义里显式去掉 JSON 字符串的引号：

CREATE TABLE customer_events (
  id BIGINT PRIMARY KEY,
  payload JSON NOT NULL,
  -- 去掉 JSON_EXTRACT 返回字符串时附带的 JSON 引号
  customer_name VARCHAR(100)
    AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.customer.name'))) STORED,
  -- 用提取后的普通字符串建立索引
  INDEX idx_customer_name (customer_name)
);

-- 查询时保持与生成列定义相同的提取形式
SELECT id
FROM customer_events
WHERE JSON_UNQUOTE(JSON_EXTRACT(payload, '$.customer.name')) = '林岚';

直接使用 JSON_EXTRACT 可能得到带引号的 JSON 字符串值，生成列定义不做 JSON_UNQUOTE 时，某些字符串比较不容易按预期匹配索引。JSON 路径、返回类型和字符集也要一起固定，不要只看列名。

上线前用 EXPLAIN 做三项复查

不需要为了判断索引而先运行真实业务流量，先检查结构和执行计划即可：

• 看 SHOW CREATE TABLE orders，确认生成表达式、数据类型和索引名没有被迁移脚本改写。
• 看 EXPLAIN 的 possible_keys 与 key。出现索引名说明优化器把它纳入考虑，但最终仍要结合行数和实际访问模式判断。
• 如果没有命中，逐字符比较表达式、常量类型和 JSON 处理函数；不要先盲目增加更多索引。

生成列表达式还不能使用子查询、变量、存储函数等受限构造，非确定性函数如 NOW() 也不适合放进去。确认表达式稳定后，再根据写入比例、磁盘预算和查询收益决定是否保留 STORED 索引。

相关问题

生成列一定要用 STORED 才能索引吗？

不是。MySQL/InnoDB 支持对虚拟生成列建立二级索引，但索引维护、表达式限制和版本条件仍要结合实际环境确认；如果希望值本身落盘，才选择 STORED。

为什么 SQL 能执行却没有使用生成列索引？

最常见原因是表达式不完全一致、结果类型不同、函数写法不同，或者优化器判断另一个索引成本更低。用 EXPLAIN 对照生成列定义和索引选择。

生成列可以直接被应用写入吗？

不能按普通列写入计算值。INSERT、REPLACE、UPDATE 显式涉及生成列时，允许的值是 DEFAULT；应用应更新生成列依赖的基础列。