MySQL中的虚拟列（Virtual Columns）：基于函数计算的索引如何平衡存储与计算成本？

MySQL虚拟列：索引的存储与计算成本平衡之道

各位，大家好！今天我们来聊聊MySQL中一个非常有用的特性：虚拟列（Virtual Columns），以及如何利用它们结合索引，来平衡数据存储成本和查询计算成本。

什么是虚拟列？

虚拟列，也称为计算列或派生列，是一种其值不是实际存储在表中的列。 它的值是根据其他列（可以是同一行或其他表中的列）以及一个表达式计算出来的。 简单来说，虚拟列就像一个“视图列”，但它是表的一部分，而不是一个独立的视图对象。

MySQL支持两种类型的虚拟列：

• VIRTUAL (或GENERATED ALWAYS AS) 列： 这种列的值是在读取时动态计算的。它不会占用任何存储空间，每次访问该列时都会执行计算。

• STORED (或GENERATED ALWAYS AS STORED) 列： 这种列的值是在插入或更新行时计算并存储的。它会占用存储空间，但读取速度更快，因为它不需要实时计算。

虚拟列的语法

创建虚拟列的语法如下：

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    virtual_column data_type GENERATED ALWAYS AS (expression) [VIRTUAL | STORED]
);

• column1, column2: 现有列，用于计算虚拟列。
• virtual_column: 虚拟列的名称。
• data_type: 虚拟列的数据类型。
• expression: 用于计算虚拟列值的表达式。它可以是任何有效的MySQL表达式，包括函数调用。
• VIRTUAL | STORED: 指定虚拟列的类型。VIRTUAL表示动态计算，STORED表示预先计算并存储。如果省略，则默认为VIRTUAL。

为什么要使用虚拟列？

虚拟列的主要优势在于以下几个方面：

• 简化查询： 复杂的计算逻辑可以封装在虚拟列中，从而简化查询语句。
• 提高数据一致性： 通过在虚拟列中定义计算规则，可以确保数据的派生值始终与源数据保持一致。
• 优化查询性能： 通过在STORED虚拟列上创建索引，可以显著提高基于计算值的查询性能。
• 数据模型清晰化： 将计算逻辑明确地定义在表结构中，有助于提高数据模型的清晰度和可维护性。

虚拟列与索引：存储与计算的权衡

现在，让我们深入探讨虚拟列与索引的结合，以及如何在这种结合中权衡存储成本和计算成本。

假设我们有一个订单表 orders，包含以下字段：

• order_id (INT, PRIMARY KEY)
• customer_id (INT)
• order_date (DATE)
• total_amount (DECIMAL(10, 2))
• discount_rate (DECIMAL(5, 2))

我们经常需要根据订单的实际支付金额（扣除折扣后的金额）进行查询和排序。我们可以创建一个虚拟列 payable_amount 来表示实际支付金额：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    discount_rate DECIMAL(5, 2),
    payable_amount DECIMAL(10, 2) GENERATED ALWAYS AS (total_amount * (1 - discount_rate)) VIRTUAL
);

在这个例子中，payable_amount 是一个VIRTUAL虚拟列。每次查询payable_amount时，都会动态计算total_amount * (1 - discount_rate)。

如果我们需要经常根据payable_amount进行查询，例如查找所有实际支付金额大于1000的订单，那么每次查询都需要对每一行数据进行计算，这会影响查询性能。为了优化查询性能，我们可以将payable_amount定义为STORED虚拟列，并在其上创建索引：

ALTER TABLE orders
DROP COLUMN payable_amount;

ALTER TABLE orders
ADD COLUMN payable_amount DECIMAL(10, 2) GENERATED ALWAYS AS (total_amount * (1 - discount_rate)) STORED;

CREATE INDEX idx_payable_amount ON orders (payable_amount);

现在，payable_amount 的值会在插入或更新订单时计算并存储。查询时，MySQL可以直接使用索引idx_payable_amount，而无需进行实时计算，从而显著提高查询性能。

存储成本与计算成本的对比

特性	VIRTUAL 虚拟列	STORED 虚拟列
存储空间	不占用存储空间	占用存储空间
计算时间	每次读取时动态计算	在插入/更新时计算，存储计算结果
查询性能	较低，每次查询都需要计算	较高，可以使用索引，避免实时计算
数据一致性	始终与源数据保持一致，无需额外维护	需要考虑数据一致性，但MySQL会自动维护
适用场景	计算不频繁，对存储空间敏感的场景	计算频繁，需要高性能查询的场景

选择 VIRTUAL 还是 STORED？

如何选择VIRTUAL还是STORED虚拟列，取决于具体的应用场景和需求。以下是一些建议：

• 计算频率： 如果计算不频繁，或者只在少数查询中使用，那么VIRTUAL虚拟列是一个不错的选择。它可以节省存储空间，并且对整体性能的影响较小。
• 查询性能： 如果需要频繁根据计算值进行查询，或者查询性能至关重要，那么STORED虚拟列是更好的选择。通过在STORED虚拟列上创建索引，可以显著提高查询性能。
• 存储空间： 如果存储空间有限，那么VIRTUAL虚拟列可以节省存储空间。但是，需要权衡存储空间和查询性能之间的关系。
• 数据一致性： VIRTUAL虚拟列始终与源数据保持一致，无需额外维护。STORED虚拟列也由MySQL自动维护数据一致性。

更复杂的例子：基于函数的索引

虚拟列的强大之处在于，它们可以与任何有效的MySQL表达式结合使用，包括函数。 我们可以创建一个虚拟列来存储函数的返回值，并在其上创建索引。

例如，假设我们有一个用户表 users，包含以下字段：

• user_id (INT, PRIMARY KEY)
• username (VARCHAR(255))
• email (VARCHAR(255))

我们需要根据邮箱域名进行查询，例如查找所有使用特定域名的用户。我们可以创建一个虚拟列 email_domain 来存储邮箱域名，并在其上创建索引：

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255)
);

ALTER TABLE users
ADD COLUMN email_domain VARCHAR(255) GENERATED ALWAYS AS (SUBSTRING_INDEX(email, '@', -1)) STORED;

CREATE INDEX idx_email_domain ON users (email_domain);

在这个例子中，email_domain 是一个STORED虚拟列，它的值是通过SUBSTRING_INDEX函数从email字段中提取出来的。通过在email_domain上创建索引，我们可以快速查找所有使用特定域名的用户。

SELECT * FROM users WHERE email_domain = 'example.com';

使用 JSON 函数的例子

假设我们有一个产品表 products，其中有一个 properties 列，存储了 JSON 格式的产品属性：

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    properties JSON
);

INSERT INTO products (product_id, product_name, properties) VALUES
(1, 'Laptop', '{"brand": "Dell", "screen_size": 15.6, "memory": "16GB"}'),
(2, 'Smartphone', '{"brand": "Samsung", "screen_size": 6.5, "storage": "128GB"}');

我们需要根据 properties 中的 brand 属性进行查询。我们可以创建一个虚拟列 brand 来提取 brand 属性的值，并在其上创建索引：

ALTER TABLE products
ADD COLUMN brand VARCHAR(255) GENERATED ALWAYS AS (JSON_EXTRACT(properties, '$.brand')) STORED;

CREATE INDEX idx_brand ON products (brand);

现在，我们可以使用索引 idx_brand 来快速查找特定品牌的产品：

SELECT * FROM products WHERE brand = '"Dell"'; -- 注意JSON_EXTRACT返回的是带引号的字符串

虚拟列的限制

虽然虚拟列非常有用，但它们也有一些限制：

• 表达式的复杂性： 虚拟列的表达式不能过于复杂。复杂的表达式可能会导致性能问题。
• 数据类型： 虚拟列的数据类型必须与表达式的返回值类型兼容。
• 自引用： 虚拟列不能引用自身。
• 触发器： 虚拟列不能在触发器中使用。
• 空间占用： STORED 虚拟列会占用存储空间。

最佳实践

以下是一些使用虚拟列的最佳实践：

• 仔细评估需求： 在创建虚拟列之前，仔细评估是否真的需要它。考虑计算频率、查询性能和存储空间等因素。
• 选择合适的类型： 根据实际需求选择VIRTUAL或STORED虚拟列。
• 创建索引： 如果需要频繁根据计算值进行查询，那么在STORED虚拟列上创建索引。
• 简化表达式： 尽量简化虚拟列的表达式，避免使用复杂的计算逻辑。
• 监控性能： 定期监控虚拟列的性能，确保它们没有对整体性能产生负面影响。

代码示例：综合应用

现在，让我们看一个更综合的例子，它结合了多个虚拟列和索引，以优化一个电商平台的订单分析：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATETIME,
    total_amount DECIMAL(10, 2),
    discount_rate DECIMAL(5, 2),
    shipping_address JSON,
    order_status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled')
);

-- 虚拟列：实际支付金额
ALTER TABLE orders
ADD COLUMN payable_amount DECIMAL(10, 2) GENERATED ALWAYS AS (total_amount * (1 - discount_rate)) STORED;

-- 虚拟列：订单年份
ALTER TABLE orders
ADD COLUMN order_year INT GENERATED ALWAYS AS (YEAR(order_date)) STORED;

-- 虚拟列：订单月份
ALTER TABLE orders
ADD COLUMN order_month INT GENERATED ALWAYS AS (MONTH(order_date)) STORED;

-- 虚拟列：收货地址所在城市
ALTER TABLE orders
ADD COLUMN shipping_city VARCHAR(255) GENERATED ALWAYS AS (JSON_EXTRACT(shipping_address, '$.city')) STORED;

-- 创建索引
CREATE INDEX idx_payable_amount ON orders (payable_amount);
CREATE INDEX idx_order_year ON orders (order_year);
CREATE INDEX idx_order_month ON orders (order_month);
CREATE INDEX idx_shipping_city ON orders (shipping_city);
CREATE INDEX idx_order_status ON orders (order_status);

-- 示例查询：查找2023年1月，北京的已发货订单，并且实际支付金额大于1000的订单
SELECT *
FROM orders
WHERE order_year = 2023
  AND order_month = 1
  AND shipping_city = '"北京"' -- JSON_EXTRACT返回带引号的字符串
  AND order_status = 'shipped'
  AND payable_amount > 1000;

在这个例子中，我们创建了多个STORED虚拟列，用于存储订单的实际支付金额、年份、月份和收货城市。通过在这些虚拟列上创建索引，我们可以快速进行各种复杂的订单分析，例如按年份、月份、城市和订单状态进行过滤和排序。

总结：在恰当的场景使用虚拟列

虚拟列是MySQL中一个强大的特性，可以帮助我们简化查询、提高数据一致性和优化查询性能。通过在STORED虚拟列上创建索引，我们可以显著提高基于计算值的查询性能。但是，我们需要权衡存储成本和计算成本之间的关系，并根据具体的应用场景和需求选择合适的虚拟列类型。 选择哪种方式取决于具体的需求，需要在查询性能和存储空间之间做出权衡。