MySQL Generated Column虚拟列与存储列的索引应用

MySQL 5.7 版本引入了 Generated Column（生成列）功能，允许我们在表中创建一列，其值是由表中其他列的表达式自动计算得出的。这一特性解决了在数据库层面直接对计算结果建立索引的难题，避免了频繁修改应用层代码。

理解两种生成列模式

在开始操作前，必须明确区分生成列的两种存储模式，这直接影响数据库的读写性能。

1. VIRTUAL（虚拟列）：默认模式。列值不占磁盘存储空间，不实际存储在数据文件中。当读取该列时，MySQL 会实时根据表达式计算结果。
2. STORED（存储列）：列值会占用磁盘存储空间，在数据插入或更新时被计算并写入磁盘。读取时直接从磁盘读取，无需重新计算。

第一步：构建包含生成列的表

假设我们要维护一张商品订单明细表 order_items，表中包含商品单价 price 和购买数量 quantity。为了方便查询总金额，我们需要一个 total_price 列，且该列等于 price * quantity。

执行以下 SQL 语句创建包含 VIRTUAL 虚拟列的表：

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    quantity INT NOT NULL,
    -- 定义一个虚拟列，计算总金额
    total_price DECIMAL(10, 2) GENERATED ALWAYS AS (price * quantity) VIRTUAL
);

输入几条测试数据，注意不要尝试为 total_price 插入值，系统会自动处理：

INSERT INTO order_items (product_name, price, quantity) VALUES 
('机械键盘', 200.00, 2),
('游戏鼠标', 150.00, 1),
('高清显示器', 1200.00, 5);

查询表数据，验证 total_price 是否自动计算：

SELECT * FROM order_items;

此时你将看到 total_price 已经包含了正确的计算结果（如 400.00、150.00、6000.00），但该值并未物理存储在磁盘上。

第二步：为生成列创建索引

生成列最大的价值在于可以被索引。例如，业务场景经常需要查询“订单总额大于 1000 元”的记录。在没有生成列之前，我们无法使用索引优化 WHERE price * quantity > 1000 这样的查询。

执行以下命令为虚拟列 total_price 创建普通索引：

CREATE INDEX idx_total_price ON order_items(total_price);

验证索引是否生效：

EXPLAIN SELECT * FROM order_items WHERE total_price > 1000;

观察输出结果中的 type 列和 key 列。如果显示 type: range 且 key: idx_total_price，说明索引成功命中。

第三步：对比 STORED 存储列的行为

为了理解两者的区别，我们创建第二张表 order_items_stored，使用 STORED 模式。

执行以下 SQL 语句：

CREATE TABLE order_items_stored (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    quantity INT NOT NULL,
    -- 定义一个存储列，计算结果会被写入磁盘
    total_price DECIMAL(10, 2) GENERATED ALWAYS AS (price * quantity) STORED
);

插入相同的数据：

INSERT INTO order_items_stored (product_name, price, quantity) VALUES 
('机械键盘', 200.00, 2),
('游戏鼠标', 150.00, 1),
('高清显示器', 1200.00, 5);

此时，如果你查看表的 .ibd 数据文件大小，order_items_stored 通常会比 order_items 略大，因为它将计算结果物理存储了下来。

核心差异与选择策略

在实际应用中，选择虚拟列还是存储列取决于具体的读写频率和计算成本。

生成列索引底层逻辑流程

当 MySQL 执行查询并利用生成列索引时，内部处理流程如下。理解这一流程有助于排查性能问题。

实际应用中的注意事项

在使用生成列索引时，有几个关键点必须时刻警惕。

1. 避免在生成列定义中使用非确定性函数
   定义生成列时，表达式必须是确定的。切勿使用 NOW()、RAND() 或 CONNECTION_ID() 等函数。如果使用 AS (NOW())，MySQL 会报错，因为每次读取该列值都可能变化，导致索引维护极其困难。

2. 主键约束
   早期的 MySQL 版本中，虚拟列不能作为主键。虽然在 MySQL 8.0.13+ 中已经允许虚拟列作为主键，但前提是该虚拟列必须包含在所有唯一索引中，并且不能为 NULL。一般建议对需要作为主键的计算字段使用 STORED 模式。

3. 外键约束
   生成列不能作为外键引用其他表。这是当前 MySQL 版本的硬性限制，如果需要关联查询，必须依赖原有的基础列。

4. 存储空间与计算开销的权衡
   如果计算公式极其复杂（例如涉及复杂的字符串截取或数学运算），建议使用 STORED 模式，用空间换时间，避免每次查询都消耗大量 CPU 进行计算。如果是简单的加减乘除，VIRTUAL 模式则是更优的选择。

调整现有表结构以适应生成列时，如果数据量巨大，添加 STORED 列会导致全表重写，产生锁表风险。此时建议使用 pt-online-schema-change 等工具或在业务低峰期执行变更。