MySQL高级特性之：`MySQL`的`Generated Columns`：其在数据冗余和索引优化中的应用。

MySQL Generated Columns：数据冗余与索引优化的利器

大家好，今天我们要深入探讨MySQL中一个强大的特性——Generated Columns（生成列）。它允许我们基于表中的其他列计算并存储新的列，从而在数据冗余和索引优化方面发挥重要作用。我们将通过实际案例，详细讲解Generated Columns的语法、使用场景以及性能考量。

什么是 Generated Columns？

Generated Columns，顾名思义，是其值由其他列计算生成的列。与普通列不同，Generated Columns不直接存储数据，而是根据预定义的表达式动态计算。这为我们提供了一种在数据库层面进行数据转换和聚合的便捷方式。

Generated Columns有两种类型：

• Virtual Generated Columns (虚拟生成列): 虚拟生成列不占用实际存储空间。每次读取时，其值都会被动态计算。
• Stored Generated Columns (存储生成列): 存储生成列会将计算结果存储在磁盘上，类似于普通列。

选择哪种类型取决于性能需求和存储空间限制。虚拟生成列节省了存储空间，但每次读取都需要进行计算，可能会影响性能。存储生成列牺牲了存储空间，但读取速度更快。

Generated Columns 的语法

创建包含Generated Columns的表的语法如下：

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

• table_name: 表名。
• column1, column2: 普通列及其数据类型。
• generated_column: 生成列的名称。
• data_type: 生成列的数据类型。
• expression: 定义生成列值的计算表达式。可以使用表中的其他列、内置函数和运算符。
• VIRTUAL | STORED: 指定生成列的类型。如果省略，默认为 VIRTUAL。

示例：

假设我们有一个products表，包含price和discount列。我们可以创建一个final_price存储生成列来表示折后价格。

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    price DECIMAL(10, 2),
    discount DECIMAL(5, 2),
    final_price DECIMAL(10, 2) AS (price * (1 - discount)) STORED
);

INSERT INTO products (name, price, discount) VALUES
('Product A', 100.00, 0.10),
('Product B', 200.00, 0.20),
('Product C', 50.00, 0.05);

SELECT * FROM products;

执行以上SQL语句后，products表的内容如下：

id	name	price	discount	final_price
1	Product A	100.00	0.10	90.00
2	Product B	200.00	0.20	160.00
3	Product C	50.00	0.05	47.50

可以看到，final_price列的值是根据price和discount列自动计算出来的，并且存储在表中。

Generated Columns 的应用场景

Generated Columns 在以下场景中特别有用：

1. 数据冗余和一致性：

   在某些情况下，我们可能需要在表中存储一些冗余数据，以便加快查询速度或简化应用程序逻辑。但是，直接存储冗余数据可能会导致数据不一致。使用Generated Columns可以避免这个问题，因为生成列的值是根据其他列动态计算的，始终保持一致。

   示例：

   假设我们有一个orders表，包含order_date列。我们可能需要经常按年份查询订单。我们可以创建一个order_year虚拟生成列来提取年份。

   CREATE TABLE orders (
       id INT PRIMARY KEY AUTO_INCREMENT,
       order_date DATE,
       order_year INT AS (YEAR(order_date)) VIRTUAL
   );
   
   INSERT INTO orders (order_date) VALUES
   ('2023-01-15'),
   ('2023-02-20'),
   ('2024-03-10');
   
   SELECT * FROM orders WHERE order_year = 2023;

   在这个例子中，order_year列的值是根据order_date列提取的年份，并且是虚拟的，不占用存储空间。我们可以直接在WHERE子句中使用order_year列进行查询，而无需每次都进行日期函数计算。

2. 索引优化：

   Generated Columns可以用于创建更有效的索引，从而提高查询性能。例如，我们可以创建一个基于生成列的索引，以便更快地查询基于计算结果的数据。

   示例：

   继续上面的products表，如果我们需要经常按折后价格查询产品，我们可以创建一个基于final_price列的索引。

   CREATE INDEX idx_final_price ON products (final_price);
   
   SELECT * FROM products WHERE final_price > 100;

   由于final_price列是一个存储生成列，MySQL可以直接使用索引来加速查询，而无需每次都计算折后价格。

   更复杂的索引优化示例：JSON字段索引

   假设我们有一个users表，其中包含一个profile JSON列，存储用户的详细信息，例如地址。我们想要根据地址中的城市进行查询。

   CREATE TABLE users (
       id INT PRIMARY KEY AUTO_INCREMENT,
       username VARCHAR(255),
       profile JSON
   );
   
   INSERT INTO users (username, profile) VALUES
   ('user1', '{"address": {"city": "New York", "country": "USA"}}'),
   ('user2', '{"address": {"city": "Los Angeles", "country": "USA"}}'),
   ('user3', '{"address": {"city": "London", "country": "UK"}}');

   直接在JSON列上创建索引效率不高。我们可以创建一个city存储生成列来提取城市信息，并在此列上创建索引。

   ALTER TABLE users ADD COLUMN city VARCHAR(255) AS (profile ->> '$.address.city') STORED;
   CREATE INDEX idx_city ON users (city);
   
   SELECT * FROM users WHERE city = 'New York';

   在这个例子中，city列的值是根据profile JSON列提取的城市信息，并且存储在表中。我们可以直接在WHERE子句中使用city列进行查询，并且MySQL可以使用索引来加速查询。 ->> 运算符从JSON文档提取值，并将其作为字符串返回。

3. 数据转换和格式化：

   Generated Columns可以用于在数据库层面进行数据转换和格式化，以便更方便地在应用程序中使用。

   示例：

   假设我们有一个employees表，包含first_name和last_name列。我们可以创建一个full_name虚拟生成列来表示员工的全名。

   CREATE TABLE employees (
       id INT PRIMARY KEY AUTO_INCREMENT,
       first_name VARCHAR(255),
       last_name VARCHAR(255),
       full_name VARCHAR(512) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL
   );
   
   INSERT INTO employees (first_name, last_name) VALUES
   ('John', 'Doe'),
   ('Jane', 'Smith');
   
   SELECT * FROM employees;

   在这个例子中，full_name列的值是根据first_name和last_name列拼接的全名，并且是虚拟的，不占用存储空间。我们可以直接在应用程序中使用full_name列，而无需每次都进行字符串拼接。

4. 数据验证：

   虽然 Generated Columns 的主要目的是生成数据，但它们也可以间接用于数据验证。你可以创建一个生成列，该列的值在特定条件下会返回一个特定的值，然后在查询中使用这个生成列来过滤数据。

   示例：

   假设我们有一个 orders 表，包含 amount 和 status 列。我们只希望查询 amount 大于 100 且 status 为 ‘active’ 的订单。 可以创建一个生成列 is_valid，如果订单满足条件，则为 1，否则为 0。

   CREATE TABLE orders (
       id INT PRIMARY KEY AUTO_INCREMENT,
       amount DECIMAL(10, 2),
       status VARCHAR(20),
       is_valid INT AS (CASE WHEN amount > 100 AND status = 'active' THEN 1 ELSE 0 END) STORED
   );
   
   INSERT INTO orders (amount, status) VALUES
   (120.00, 'active'),
   (80.00, 'active'),
   (150.00, 'inactive');
   
   SELECT * FROM orders WHERE is_valid = 1;

   虽然这不是直接的数据验证，但 is_valid 列允许你使用更简洁的查询来过滤符合特定条件的数据。

Generated Columns 的限制

Generated Columns 虽然强大，但也存在一些限制：

• 自引用限制： 生成列不能引用自身或其他生成列。也就是说，生成列的表达式只能使用普通列。
• 不确定性函数： 生成列的表达式不能包含不确定性函数，例如 RAND() 或 UUID()。这是因为生成列的值必须是可预测的。 NOW() 和 CURDATE() 在MySQL 5.7 之后也允许在生成列中使用，但要注意它们的影响。
• 存储引擎限制： 并非所有的MySQL存储引擎都支持Generated Columns。例如，MyISAM存储引擎不支持Generated Columns。推荐使用InnoDB存储引擎。
• 性能影响： 虽然存储生成列可以提高查询性能，但也会增加存储空间，并且在插入和更新数据时需要进行计算。虚拟生成列节省了存储空间，但每次读取都需要进行计算，可能会影响性能。因此，需要根据实际情况进行权衡。
• 嵌套限制: 生成列的表达式不能包含子查询或者存储过程调用。
• 不能作为主键或唯一索引的一部分 (在某些情况下): 虽然可以对生成列创建索引，但在某些情况下，不能将生成列直接作为主键或唯一索引的一部分。 通常，可以创建基于生成列的索引，但如果生成列的表达式过于复杂或涉及到不允许的函数，可能会受到限制。

如何选择 Virtual 或 Stored Generated Columns

选择 VIRTUAL 还是 STORED 生成列取决于你的具体需求和性能考量。

特性	Virtual Generated Columns	Stored Generated Columns
存储空间	不占用存储空间	占用存储空间
读取性能	每次读取都需要计算	读取速度更快
写入性能	写入速度更快	写入速度较慢
数据一致性	始终与基础数据保持一致	需要维护数据一致性

• 如果存储空间有限，并且对读取性能要求不高，可以选择 Virtual Generated Columns。 例如，用于临时计算或不经常使用的辅助列。
• 如果需要频繁读取生成列的值，并且对写入性能要求不高，可以选择 Stored Generated Columns。 例如，用于创建索引以加速查询。

建议：

• 对于简单的计算，例如字符串拼接或简单的数学运算，可以使用Virtual Generated Columns。
• 对于复杂的计算或需要在多个查询中使用的生成列，可以使用Stored Generated Columns。
• 在创建生成列之前，先进行性能测试，以确定哪种类型最适合你的需求。

Generated Columns 的其他注意事项

• 修改 Generated Columns： 可以使用 ALTER TABLE 语句修改生成列的表达式或类型。
• 删除 Generated Columns： 可以使用 ALTER TABLE 语句删除生成列。
• Generated Columns 和触发器： Generated Columns 可以简化某些原本需要触发器才能实现的功能。例如，可以使用Generated Columns自动更新某个列的值，而无需编写触发器。

案例分析：电商平台订单分析

让我们结合一个电商平台的订单分析场景，来进一步理解Generated Columns的应用。

假设我们有一个orders表，包含以下列：

• order_id (INT, PRIMARY KEY)
• customer_id (INT)
• order_date (DATETIME)
• total_amount (DECIMAL)
• payment_method (VARCHAR)
• shipping_address (JSON)

我们希望进行以下分析：

1. 按月份统计订单数量。
2. 按城市统计订单金额。
3. 统计每个客户的首次购买日期。

使用Generated Columns，我们可以简化这些分析：

ALTER TABLE orders
ADD COLUMN order_month INT AS (MONTH(order_date)) VIRTUAL,
ADD COLUMN order_year INT AS (YEAR(order_date)) VIRTUAL,
ADD COLUMN shipping_city VARCHAR(255) AS (shipping_address ->> '$.city') STORED;

CREATE INDEX idx_order_month ON orders (order_month);
CREATE INDEX idx_shipping_city ON orders (shipping_city);
CREATE INDEX idx_customer_id ON orders (customer_id, order_date);

-- 按月份统计订单数量
SELECT order_year, order_month, COUNT(*) AS order_count
FROM orders
GROUP BY order_year, order_month
ORDER BY order_year, order_month;

-- 按城市统计订单金额
SELECT shipping_city, SUM(total_amount) AS total_order_amount
FROM orders
GROUP BY shipping_city
ORDER BY total_order_amount DESC;

-- 统计每个客户的首次购买日期
SELECT customer_id, MIN(order_date) AS first_order_date
FROM orders
GROUP BY customer_id;

通过创建order_month和shipping_city生成列，我们可以方便地进行按月份和城市统计分析，并且可以使用索引来加速查询。而idx_customer_id 复合索引 则可以加速查找每个用户的最小订单日期，提高性能。

总结：利用Generated Columns优化数据模型

Generated Columns 是MySQL中一个强大的特性，可以在数据冗余、索引优化和数据转换等方面发挥重要作用。通过合理使用Generated Columns，我们可以提高查询性能、简化应用程序逻辑并保持数据一致性。 在实际应用中，需要根据具体情况选择 Virtual 或 Stored Generated Columns，并注意其限制和性能影响。 掌握Generated Columns的使用，可以帮助我们构建更高效、更健壮的数据库应用。