MySQL中的虚拟列：索引、存储与计算成本的平衡艺术

大家好！今天我们来深入探讨一个MySQL中非常实用但又容易被忽视的特性：虚拟列（Virtual Columns），也称为生成列（Generated Columns）。我们会从索引、存储和计算成本等多个角度，分析如何巧妙地运用虚拟列，在性能和资源消耗之间找到最佳的平衡点。

1. 什么是虚拟列？

虚拟列是一种特殊的列，它的值不是实际存储在表中的，而是通过表达式计算得出的。这意味着，当您查询包含虚拟列的表时，MySQL会在运行时动态计算虚拟列的值。虚拟列的定义可以基于表中的其他列，也可以基于常量或函数。

虚拟列有两种类型：

Virtual (Stored = NO): 这种类型的虚拟列不会占用任何存储空间。每次查询时，表达式都会被重新计算。这是默认类型。
Stored (Stored = YES): 这种类型的虚拟列会将计算结果存储在表中，就像普通列一样。这意味着查询时不需要重新计算，但会占用额外的存储空间。

2. 虚拟列的语法

创建虚拟列的语法如下：

ALTER TABLE table_name
ADD COLUMN column_name column_definition
[GENERATED ALWAYS] AS (expression)
[VIRTUAL | STORED];

table_name: 要添加虚拟列的表名。
column_name: 虚拟列的名称。
column_definition: 虚拟列的数据类型和约束（例如，INT UNSIGNED NOT NULL）。
GENERATED ALWAYS: 可选，指定该列必须是生成列。
expression: 用于计算虚拟列值的表达式。
VIRTUAL | STORED: 可选，指定虚拟列的类型。如果省略，则默认为 VIRTUAL。

示例：

假设我们有一个 orders 表，包含 price 和 quantity 列，我们希望添加一个虚拟列 total_amount，用于计算订单总金额。

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    price DECIMAL(10, 2) NOT NULL,
    quantity INT UNSIGNED NOT NULL
);

-- 添加 VIRTUAL 类型的虚拟列
ALTER TABLE orders
ADD COLUMN total_amount DECIMAL(10, 2) GENERATED ALWAYS AS (price * quantity) VIRTUAL;

-- 添加 STORED 类型的虚拟列
ALTER TABLE orders
ADD COLUMN total_amount_stored DECIMAL(10, 2) GENERATED ALWAYS AS (price * quantity) STORED;

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

SELECT * FROM orders;

查询结果：

order_id	price	quantity	total_amount	total_amount_stored
1	10.50	2	21.00	21.00
2	25.00	1	25.00	25.00

3. 虚拟列与索引

虚拟列可以像普通列一样进行索引。对虚拟列创建索引可以显著提高查询性能，尤其是当虚拟列被用于 WHERE 子句、ORDER BY 子句或 GROUP BY 子句中时。

示例：

假设我们需要经常根据订单总金额查询订单，我们可以对 total_amount 列创建索引。

CREATE INDEX idx_total_amount ON orders (total_amount);
CREATE INDEX idx_total_amount_stored ON orders (total_amount_stored);

注意：

只有 STORED 类型的虚拟列才能创建普通索引（B-tree 索引）。
VIRTUAL 类型的虚拟列只能创建前缀索引或函数索引（在MySQL 5.7.6及更高版本中可用）。
在MySQL 8.0中，对 VIRTUAL 列的支持得到了增强，可以创建普通索引，优化器可以更好地利用虚拟列上的索引。

4. 虚拟列的存储成本

VIRTUAL 类型的虚拟列不占用任何存储空间。它们的值是在查询时动态计算的。
STORED 类型的虚拟列会将计算结果存储在表中，因此会占用额外的存储空间。存储空间的大小取决于虚拟列的数据类型。

如何选择 VIRTUAL 还是 STORED？

选择哪种类型的虚拟列取决于具体的应用场景和性能需求。

如果计算表达式比较简单，且查询频率不高，或者对存储空间要求非常严格，那么 VIRTUAL 类型可能更合适。 它的优点是不占用存储空间，缺点是每次查询都需要重新计算，可能会增加查询延迟。
如果计算表达式比较复杂，或者查询频率很高，并且对查询性能要求很高，那么 STORED 类型可能更合适。 它的优点是查询速度快，缺点是会占用额外的存储空间。

5. 虚拟列的计算成本

VIRTUAL 类型的虚拟列的计算成本是在查询时动态计算表达式的成本。计算成本取决于表达式的复杂程度。
STORED 类型的虚拟列的计算成本是在数据插入或更新时计算表达式的成本。计算结果会被存储在表中，因此查询时不需要重新计算。

6. 虚拟列的应用场景

虚拟列有很多应用场景，例如：

数据转换： 将数据从一种格式转换为另一种格式。例如，将日期时间戳转换为可读的日期字符串。
数据校验： 根据其他列的值验证数据的有效性。例如，创建一个虚拟列来检查电子邮件地址是否有效。
数据聚合： 基于其他列的值计算聚合值。例如，计算订单的总金额。
简化复杂查询： 将复杂的计算逻辑封装到虚拟列中，从而简化查询语句。
索引优化： 为复杂的表达式创建索引，提高查询性能。

示例1：数据转换

假设我们有一个 users 表，包含一个 birthdate 列，存储用户的出生日期，格式为 YYYY-MM-DD。我们希望添加一个虚拟列 age，用于计算用户的年龄。

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    birthdate DATE NOT NULL
);

ALTER TABLE users
ADD COLUMN age INT GENERATED ALWAYS AS (TIMESTAMPDIFF(YEAR, birthdate, CURDATE())) VIRTUAL;

INSERT INTO users (birthdate) VALUES ('1990-01-01');
INSERT INTO users (birthdate) VALUES ('1995-05-15');

SELECT * FROM users;

查询结果：

user_id	birthdate	age
1	1990-01-01	34
2	1995-05-15	28

示例2：数据校验

假设我们有一个 products 表，包含 price 和 discount 列，我们希望添加一个虚拟列 is_valid_discount，用于验证折扣是否有效（折扣必须在 0 到 1 之间）。

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    price DECIMAL(10, 2) NOT NULL,
    discount DECIMAL(5, 2) NOT NULL
);

ALTER TABLE products
ADD COLUMN is_valid_discount BOOLEAN GENERATED ALWAYS AS (discount >= 0 AND discount <= 1) VIRTUAL;

INSERT INTO products (price, discount) VALUES (100.00, 0.1);
INSERT INTO products (price, discount) VALUES (50.00, 1.2);

SELECT * FROM products;

查询结果：

product_id	price	discount	is_valid_discount
1	100.00	0.10	1
2	50.00	1.20	0

示例3：索引优化复杂表达式

假设我们需要经常根据 price * (1 - discount) 的结果查询产品，我们可以创建一个 STORED 类型的虚拟列，并对其创建索引。

ALTER TABLE products
ADD COLUMN discounted_price DECIMAL(10, 2) GENERATED ALWAYS AS (price * (1 - discount)) STORED;

CREATE INDEX idx_discounted_price ON products (discounted_price);

SELECT * FROM products WHERE discounted_price > 50;

7. 虚拟列的限制

虚拟列不能作为主键或唯一键的一部分，除非它是 STORED 类型的。
虚拟列不能作为外键的目标列。
虚拟列的表达式不能包含子查询、用户自定义函数或存储过程。
虚拟列的表达式不能引用自身或其他虚拟列。（在某些特定情况下，MySQL 5.7.6 及更高版本允许引用其他虚拟列，但需要满足一定的条件，并且可能导致性能问题。）
在 MySQL 5.7.21 之前，expression 中不能使用 UUID() 函数。

8. 实际案例分析

让我们看一个更复杂的实际案例。假设我们有一个电商网站，需要分析用户的订单数据。我们有一个 orders 表，包含 order_id, user_id, order_date, total_amount 等列。

我们经常需要根据订单的月份来分析订单数据。我们可以添加一个虚拟列 order_month，用于提取订单的月份。

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL
);

ALTER TABLE orders
ADD COLUMN order_month INT GENERATED ALWAYS AS (MONTH(order_date)) VIRTUAL;

CREATE INDEX idx_order_month ON orders (order_month); -- MySQL 8.0 可以在 VIRTUAL 列上创建普通索引

-- 插入一些测试数据
INSERT INTO orders (user_id, order_date, total_amount) VALUES
(1, '2023-01-15', 100.00),
(2, '2023-01-20', 200.00),
(1, '2023-02-10', 150.00),
(3, '2023-02-25', 300.00),
(2, '2023-03-05', 250.00);

-- 查询每个月的订单总金额
SELECT order_month, SUM(total_amount) AS total_monthly_amount
FROM orders
GROUP BY order_month;

在这个例子中，我们使用了 MONTH() 函数来提取订单的月份。通过对 order_month 虚拟列创建索引，我们可以显著提高按月份分组查询的性能。

9. 如何选择合适的虚拟列类型

下面是一个简单的表格，总结了 VIRTUAL 和 STORED 类型的虚拟列的优缺点：

特性	`VIRTUAL`	`STORED`
存储空间	不占用存储空间	占用存储空间
查询性能	查询时动态计算，可能较慢	查询速度快
写入性能	写入速度快	写入速度慢，需要计算并存储结果
索引	MySQL 8.0及更高版本支持普通索引	支持普通索引
适用场景	计算简单、查询频率低、存储空间敏感的场景	计算复杂、查询频率高、性能要求高的场景

10. 使用虚拟列的注意事项

表达式的复杂度： 虚拟列的表达式越复杂，计算成本越高。应该尽量使用简单的表达式。
数据类型： 虚拟列的数据类型应该与表达式的计算结果相匹配。
索引选择： 选择合适的索引类型可以显著提高查询性能。
版本兼容性： 虚拟列的一些特性在不同的 MySQL 版本中可能有所不同。需要注意版本兼容性问题。
维护成本： 当表结构发生变化时，需要更新虚拟列的表达式。需要考虑维护成本。

11. 总结与建议

虚拟列是MySQL中一个强大的工具，可以帮助我们提高查询性能、简化查询语句和优化数据模型。但是，虚拟列也存在一些限制和注意事项。

在使用虚拟列时，需要根据具体的应用场景和性能需求，仔细权衡存储成本、计算成本和索引效果，选择合适的虚拟列类型和表达式。

希望今天的讲解能够帮助大家更好地理解和应用MySQL中的虚拟列。谢谢大家！

虚拟列：灵活运用，提升效率

虚拟列是MySQL中一个强大的工具，可以帮助我们提高查询性能、简化查询语句和优化数据模型。合理使用虚拟列，可以在存储、计算和查询性能之间找到平衡点，从而提升数据库的整体效率。

MySQL中的虚拟列：索引、存储与计算成本的平衡艺术

发表回复 取消回复

发表回复取消回复