MySQL高级数据类型之：`MySQL`的`Generated Columns`：`虚拟列`与`存储列`的底层区别。

MySQL Generated Columns：虚拟列与存储列的深度剖析

大家好，今天我们来深入探讨 MySQL 的一个强大特性：Generated Columns，中文常译为生成列。生成列允许我们基于表中其他列的值，自动计算并生成新的列。它分为两种类型：虚拟列 (Virtual Generated Columns) 和存储列 (Stored Generated Columns)。理解它们的底层区别，能帮助我们更好地利用这个特性，优化数据库设计和性能。

什么是 Generated Columns？

在传统的数据库设计中，如果我们需要计算某个值，通常会在应用程序代码中完成。例如，假设我们有一个 orders 表，包含 price (价格) 和 quantity (数量) 两列，如果需要计算订单总金额，通常会在应用程序中将 price 和 quantity 相乘。

Generated Columns 提供了一种在数据库层面上完成这种计算的方式。我们可以创建一个名为 total_amount 的生成列，它的值会根据 price 和 quantity 自动计算得出。

示例：创建包含生成列的 orders 表

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    price DECIMAL(10, 2) NOT NULL,
    quantity INT NOT NULL,
    total_amount DECIMAL(10, 2) GENERATED ALWAYS AS (price * quantity) VIRTUAL
);

INSERT INTO orders (price, quantity) VALUES
(10.50, 2),
(25.00, 1),
(5.75, 5);

SELECT * FROM orders;

在这个例子中，total_amount 是一个虚拟生成列。它的定义是 GENERATED ALWAYS AS (price * quantity) VIRTUAL。GENERATED ALWAYS AS 关键字表示这是一个生成列，后面的表达式 (price * quantity) 定义了计算方法。VIRTUAL 关键字则指定这是一个虚拟列。

虚拟列 (Virtual Generated Columns)

虚拟列不实际存储数据。当查询包含虚拟列时，MySQL 会在查询时动态地计算该列的值。

特点：

• 不占用存储空间: 因为不存储实际数据，所以不会增加表的存储大小。
• 实时计算: 每次查询时都会重新计算，保证数据的实时性。
• 适用于计算复杂度不高，且对实时性要求高的场景: 由于每次查询都需要计算，如果计算过于复杂，可能会影响查询性能。
• 不能被索引: 虚拟列本身不能被直接索引，但可以被用作表达式索引的一部分（稍后会详细讲解）。

更深入的理解：

可以将虚拟列理解为一个“视图”，它是在查询时动态生成的。每次访问虚拟列，MySQL 都会执行定义它的表达式，并将结果返回。

示例：查询包含虚拟列的表

SELECT order_id, price, quantity, total_amount FROM orders WHERE total_amount > 20;

在这个查询中，total_amount 的值是在执行查询时动态计算的。即使 total_amount 列不存在于表的物理存储中，MySQL 也能根据 price 和 quantity 计算出它的值。

存储列 (Stored Generated Columns)

存储列会实际存储数据。当插入或更新表中的数据时，MySQL 会立即计算该列的值，并将其存储在磁盘上。

特点：

• 占用存储空间: 存储实际数据，会增加表的存储大小。
• 预先计算: 数据在插入/更新时就已经计算好并存储，查询时无需再次计算。
• 适用于计算复杂度高，且对查询性能要求高的场景: 避免了每次查询时的重复计算，提高了查询性能。
• 可以被索引: 可以像普通列一样被索引，进一步提高查询性能。
• 需要维护: 当依赖的列发生变化时，存储列的值需要自动更新，这会带来一些额外的开销。

更深入的理解：

可以将存储列理解为一个“物化视图”，它将计算结果预先存储起来，以便快速访问。

示例：创建包含存储列的 orders 表

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    price DECIMAL(10, 2) NOT NULL,
    quantity INT NOT NULL,
    total_amount DECIMAL(10, 2) GENERATED ALWAYS AS (price * quantity) STORED
);

INSERT INTO orders (price, quantity) VALUES
(10.50, 2),
(25.00, 1),
(5.75, 5);

SELECT * FROM orders;

在这个例子中，total_amount 的定义是 GENERATED ALWAYS AS (price * quantity) STORED。STORED 关键字指定这是一个存储列。当插入数据时，total_amount 的值会被立即计算并存储在表中。

虚拟列 vs. 存储列：底层区别

特性	虚拟列 (Virtual)	存储列 (Stored)
存储空间	不占用	占用
计算时机	查询时	插入/更新时
性能	查询时计算，可能影响性能（取决于计算复杂度）	插入/更新时计算，查询性能更高（无需重复计算）
索引	不能直接索引，但可用于表达式索引	可以像普通列一样索引
数据一致性	实时一致，始终反映最新数据	需要维护，但MySQL会自动维护，保证数据一致性
适用场景	计算简单，对实时性要求高的场景	计算复杂，对查询性能要求高的场景
修改限制	不能直接修改（只能通过修改依赖列来间接修改）	不能直接修改（只能通过修改依赖列来间接修改）

底层原理：

• 虚拟列： MySQL 在执行查询时，会解析 SQL 语句，识别出虚拟列，并将其对应的表达式嵌入到查询执行计划中。当执行到需要访问虚拟列时，会动态地计算表达式的值。
• 存储列： MySQL 在插入或更新数据时，会触发计算存储列的逻辑。计算结果会被存储在数据页中，与表中的其他列一起存储。当查询包含存储列时，MySQL 可以直接从数据页中读取该列的值，而无需进行额外的计算。

深入理解存储列的维护机制：

当存储列所依赖的列发生变化时，MySQL 会自动更新存储列的值。这是通过内部的触发器机制实现的。当 price 或 quantity 列发生更新时，MySQL 会触发一个内部的触发器，该触发器会重新计算 total_amount 的值，并将更新后的值存储在表中。这个过程对用户是透明的。

Generated Columns 的应用场景

Generated Columns 在很多场景下都能发挥重要作用：

1. 数据转换和格式化： 可以将不同格式的数据转换为统一的格式，方便查询和分析。例如，可以将日期时间戳转换为可读的日期字符串。
2. 数据校验： 可以根据其他列的值，自动生成校验列，用于验证数据的完整性和一致性。
3. 数据聚合： 可以根据其他列的值，计算出聚合值，例如总金额、平均值等。
4. 数据分区： 可以根据生成列的值，将表数据进行分区，提高查询性能。
5. 简化应用程序逻辑： 可以将一些复杂的计算逻辑放在数据库层面完成，简化应用程序的代码。

示例：数据转换和格式化

假设我们有一个 users 表，其中包含 birthdate (生日) 列，存储的是日期时间戳。我们可以创建一个虚拟列 age，用于计算用户的年龄。

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    birthdate TIMESTAMP NOT NULL,
    age INT GENERATED ALWAYS AS (TIMESTAMPDIFF(YEAR, birthdate, CURDATE())) VIRTUAL
);

INSERT INTO users (username, birthdate) VALUES
('Alice', '1990-05-15'),
('Bob', '1985-12-20'),
('Charlie', '2000-03-10');

SELECT user_id, username, birthdate, age FROM users;

在这个例子中，age 列的值是根据 birthdate 列的值动态计算的。TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) 函数用于计算 birthdate 到当前日期的年份差，即用户的年龄。

示例：数据校验

假设我们有一个 products 表，其中包含 price (价格) 和 discount (折扣) 两列。我们可以创建一个虚拟列 final_price，用于计算最终价格。同时，我们可以创建一个虚拟列 is_valid，用于校验最终价格是否大于等于 0。

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    discount DECIMAL(5, 2) NOT NULL,
    final_price DECIMAL(10, 2) GENERATED ALWAYS AS (price * (1 - discount)) VIRTUAL,
    is_valid BOOLEAN GENERATED ALWAYS AS (final_price >= 0) VIRTUAL
);

INSERT INTO products (product_name, price, discount) VALUES
('Product A', 100.00, 0.10),
('Product B', 50.00, 0.20),
('Product C', 20.00, 0.00);

SELECT product_id, product_name, price, discount, final_price, is_valid FROM products;

在这个例子中，is_valid 列的值是根据 final_price 列的值动态计算的。如果 final_price 大于等于 0，则 is_valid 的值为 TRUE，否则为 FALSE。

Generated Columns 与索引

虽然虚拟列不能被直接索引，但我们可以使用表达式索引 (Expression Index) 来提高查询性能。表达式索引允许我们基于表达式创建索引，而虚拟列可以作为表达式的一部分。

示例：使用表达式索引优化查询

假设我们有一个 orders 表，其中包含 order_date (订单日期) 和 customer_id (客户ID) 两列。我们需要根据年份和客户ID来查询订单。

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL
);

-- 创建虚拟列 year
ALTER TABLE orders ADD COLUMN year INT GENERATED ALWAYS AS (YEAR(order_date)) VIRTUAL;

-- 创建表达式索引
CREATE INDEX idx_year_customer_id ON orders (year, customer_id);

-- 查询
SELECT * FROM orders WHERE year = 2023 AND customer_id = 123;

在这个例子中，我们首先创建了一个虚拟列 year，用于提取订单日期的年份。然后，我们创建了一个表达式索引 idx_year_customer_id，基于 year 和 customer_id 两列。当执行查询时，MySQL 可以利用这个索引来快速定位符合条件的记录，从而提高查询性能。

对于存储列，我们可以像普通列一样创建索引。

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    year INT GENERATED ALWAYS AS (YEAR(order_date)) STORED
);

CREATE INDEX idx_year_customer_id ON orders (year, customer_id);

SELECT * FROM orders WHERE year = 2023 AND customer_id = 123;

在这个例子中，year 是一个存储列，我们可以直接基于它创建索引。

Generated Columns 的限制

• 循环依赖： 不能创建循环依赖的生成列。例如，A 列依赖于 B 列，B 列又依赖于 A 列，这是不允许的。
• 非确定性函数： 生成列的表达式必须是确定性的，即相同的输入必须产生相同的输出。因此，不能在生成列的表达式中使用 RAND()、UUID() 等非确定性函数。
• 存储函数和存储过程： 不能在生成列的表达式中调用存储函数或存储过程。
• 子查询： 不能在生成列的表达式中使用子查询。
• 自引用： 生成列不能直接引用自身。

选择虚拟列还是存储列？

选择虚拟列还是存储列，取决于具体的应用场景。

• 如果计算简单，对实时性要求高，且存储空间有限，则选择虚拟列。
• 如果计算复杂，对查询性能要求高，且存储空间充足，则选择存储列。

可以使用以下流程图来帮助决策：

graph LR
    A[开始] --> B{计算复杂度高吗？};
    B -- 是 --> C{对查询性能要求高吗？};
    B -- 否 --> D{对实时性要求高吗？};
    C -- 是 --> E{存储空间充足吗？};
    C -- 否 --> F[考虑优化计算逻辑];
    E -- 是 --> G[选择存储列];
    E -- 否 --> F;
    D -- 是 --> H[选择虚拟列];
    D -- 否 --> I[选择存储列];
    G --> J[结束];
    H --> J;
    I --> J;
    F --> J;

代码示例：综合应用

假设我们有一个 products 表，包含 price (价格)、discount (折扣) 和 tax_rate (税率) 三列。我们需要计算最终价格 (包含折扣和税)。

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    discount DECIMAL(5, 2) NOT NULL,
    tax_rate DECIMAL(5, 2) NOT NULL,
    final_price DECIMAL(10, 2) GENERATED ALWAYS AS ((price * (1 - discount)) * (1 + tax_rate)) STORED,
    discounted_price DECIMAL(10, 2) GENERATED ALWAYS AS (price * (1 - discount)) VIRTUAL
);

INSERT INTO products (product_name, price, discount, tax_rate) VALUES
('Product A', 100.00, 0.10, 0.05),
('Product B', 50.00, 0.20, 0.10),
('Product C', 20.00, 0.00, 0.00);

SELECT * FROM products;

CREATE INDEX idx_final_price ON products (final_price);

在这个例子中，final_price 是一个存储列，用于存储包含折扣和税的最终价格。由于计算相对复杂，且对查询性能要求高，因此选择存储列。discounted_price 是一个虚拟列，用于存储折扣后的价格。由于计算相对简单，且只需要在某些场景下使用，因此选择虚拟列。我们还为 final_price 创建了索引，以提高查询性能。

Generated Columns 的未来发展

随着数据库技术的不断发展，Generated Columns 的功能也在不断增强。未来，我们可能会看到以下发展趋势：

• 更复杂的表达式： 支持更复杂的表达式，例如包含用户自定义函数 (UDF) 或存储过程的表达式。
• 更智能的优化： MySQL 能够更智能地优化包含生成列的查询，例如自动选择合适的索引。
• 更广泛的应用场景： Generated Columns 将被应用到更多的场景中，例如数据仓库、数据分析等。

最后说两句

Generated Columns 是 MySQL 提供的一个强大的特性，能够帮助我们优化数据库设计和性能。理解虚拟列和存储列的底层区别，并根据具体的应用场景选择合适的类型，是充分利用 Generated Columns 的关键。希望今天的分享能够帮助大家更好地理解和使用 Generated Columns。

Generated Columns 的两种类型，特性决定其应用场景，合理使用能优化数据库设计。