MySQL高阶讲座之：`MySQL`的`Generated Columns`：其在索引优化与数据冗余中的应用。

各位观众老爷们，大家好！今天咱们聊聊MySQL里一个挺有意思的小家伙——Generated Columns，这玩意儿在索引优化和数据冗余方面，能玩出不少花样。别怕，保证通俗易懂，咱们边说边练。

开场白：啥是Generated Columns？

简单来说，Generated Columns就是MySQL里一种特殊的列。它的值不是你手动插入的，而是根据其他列的值自动计算出来的。你可以把它想象成Excel里的公式列，或者编程语言里的computed property。

Generated Columns的两种类型：VIRTUAL和STORED

Generated Columns有两种类型：

• VIRTUAL： 虚拟列，只在查询时计算，不占用存储空间。就像一个临时的计算结果，用完就丢。
• STORED： 存储列，计算结果会实际存储在磁盘上，每次基表发生变化，存储列都会自动更新。

语法结构

创建一个包含Generated Columns的表，语法大概是这样：

CREATE TABLE 表名 (
  列名1 数据类型,
  列名2 数据类型,
  ...
  生成列名 数据类型 AS (计算表达式) [VIRTUAL | STORED]
);

举个例子，假设我们有个products表，包含price和discount两列，我们想计算出折后价final_price：

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

INSERT INTO products (name, price, discount) VALUES
('T恤', 100, 0.1),
('牛仔裤', 200, 0.2),
('帽子', 50, 0.05);

SELECT * FROM products;

执行上面的SQL后，你会发现final_price列的值是根据price和discount自动计算出来的，而且不需要我们手动插入。

Generated Columns在索引优化中的应用

这才是今天的重头戏！想象一下，如果你的查询经常需要用到某个复杂的计算结果，每次都实时计算会很耗费资源。这时候，Generated Columns就可以派上大用场了。

1. 复杂条件查询加速

假设我们有一个orders表，包含order_date（订单日期）和product_category（商品类别）两列。我们经常需要查询某个季度某个类别的订单数量。

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_date DATE,
  product_category VARCHAR(255)
);

INSERT INTO orders (order_date, product_category) VALUES
('2023-01-15', '服装'),
('2023-02-20', '电子产品'),
('2023-04-10', '服装'),
('2023-05-25', '电子产品'),
('2023-07-01', '服装'),
('2023-08-15', '电子产品'),
('2023-10-20', '服装'),
('2023-11-25', '电子产品');

通常情况下，我们会这样查询：

SELECT COUNT(*)
FROM orders
WHERE YEAR(order_date) = 2023
AND QUARTER(order_date) = 1
AND product_category = '服装';

每次查询都要计算年份和季度，效率比较低。我们可以创建一个quarter的Generated Column，并在此列上建立索引：

ALTER TABLE orders
ADD COLUMN quarter INT AS (QUARTER(order_date)) STORED;

CREATE INDEX idx_quarter_category ON orders (quarter, product_category);

现在，查询就可以改成这样：

SELECT COUNT(*)
FROM orders
WHERE quarter = 1
AND product_category = '服装';

由于quarter列已经预先计算好并存储，而且有索引，查询速度会大大提升。

代码解释：

• ALTER TABLE orders ADD COLUMN quarter INT AS (QUARTER(order_date)) STORED; 添加一个名为quarter的存储列，它的值是order_date的季度。
• CREATE INDEX idx_quarter_category ON orders (quarter, product_category); 在quarter和product_category两列上创建一个联合索引。

性能对比

查询方式	性能
YEAR(order_date) = 2023 AND QUARTER(order_date) = 1	慢
quarter = 1 (Generated Column + 索引)	快

2. 字符串处理加速

假设我们有一个users表，包含email列。我们经常需要根据邮箱的域名来查询用户。

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255)
);

INSERT INTO users (email) VALUES
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]');

通常情况下，我们会这样查询：

SELECT *
FROM users
WHERE SUBSTRING_INDEX(email, '@', -1) = 'example.com';

每次查询都要进行字符串截取，效率比较低。我们可以创建一个domain的Generated Column，并在此列上建立索引：

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

CREATE INDEX idx_domain ON users (domain);

现在，查询就可以改成这样：

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

查询速度同样会大大提升。

代码解释：

• ALTER TABLE users ADD COLUMN domain VARCHAR(255) AS (SUBSTRING_INDEX(email, '@', -1)) STORED; 添加一个名为domain的存储列，它的值是email中@符号后面的部分（域名）。
• CREATE INDEX idx_domain ON users (domain); 在domain列上创建一个索引。

3. JSON字段提取加速

假设我们有一个products表，包含一个attributes JSON字段，存储了商品的各种属性。我们经常需要根据某个属性的值来查询商品。

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255),
  attributes JSON
);

INSERT INTO products (name, attributes) VALUES
('商品1', '{"color": "red", "size": "M"}'),
('商品2', '{"color": "blue", "size": "L"}'),
('商品3', '{"color": "red", "size": "S"}');

通常情况下，我们会这样查询：

SELECT *
FROM products
WHERE JSON_EXTRACT(attributes, '$.color') = 'red';

每次查询都要解析JSON字段，效率比较低。我们可以创建一个color的Generated Column，并在此列上建立索引：

ALTER TABLE products
ADD COLUMN color VARCHAR(255) AS (JSON_EXTRACT(attributes, '$.color')) STORED;

CREATE INDEX idx_color ON products (color);

现在，查询就可以改成这样：

SELECT *
FROM products
WHERE color = 'red';

查询速度同样会大大提升。

代码解释：

• ALTER TABLE products ADD COLUMN color VARCHAR(255) AS (JSON_EXTRACT(attributes, '$.color')) STORED; 添加一个名为color的存储列，它的值是attributes JSON字段中color属性的值。
• CREATE INDEX idx_color ON products (color); 在color列上创建一个索引。

Generated Columns在数据冗余中的应用

Generated Columns也可以用来减少数据冗余，保持数据的一致性。

1. 统一数据格式

假设我们有一个users表，包含first_name和last_name两列。我们希望有一个full_name列，存储完整的姓名。但是，如果我们手动维护full_name列，可能会出现不一致的情况。

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(255),
  last_name VARCHAR(255)
);

INSERT INTO users (first_name, last_name) VALUES
('张', '三'),
('李', '四');

我们可以创建一个full_name的Generated Column：

ALTER TABLE users
ADD COLUMN full_name VARCHAR(255) AS (CONCAT(first_name, ' ', last_name)) STORED;

现在，full_name列的值会自动根据first_name和last_name计算出来，保证数据的一致性。

2. 数据校验

Generated Columns还可以用来进行数据校验，防止非法数据插入。

假设我们有一个orders表，包含quantity（数量）和price（单价）两列。我们希望quantity和price都必须大于0。

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  quantity INT,
  price DECIMAL(10, 2)
);

我们可以创建一个check_quantity和check_price的Generated Column，并设置约束：

ALTER TABLE orders
ADD COLUMN check_quantity INT AS (IF(quantity > 0, 1, NULL)) STORED,
ADD COLUMN check_price DECIMAL(10,2) AS (IF(price > 0, price, NULL)) STORED;

ALTER TABLE orders
ADD CONSTRAINT check_quantity_constraint CHECK (check_quantity IS NOT NULL),
ADD CONSTRAINT check_price_constraint CHECK (check_price IS NOT NULL);

现在，如果尝试插入quantity或price小于等于0的数据，会报错。

代码解释：

• ALTER TABLE orders ADD COLUMN check_quantity INT AS (IF(quantity > 0, 1, NULL)) STORED; 添加一个名为check_quantity的存储列，如果quantity大于0，则值为1，否则为NULL。
• ALTER TABLE orders ADD COLUMN check_price DECIMAL(10,2) AS (IF(price > 0, price, NULL)) STORED; 添加一个名为check_price的存储列，如果price大于0，则值为price，否则为NULL。
• ALTER TABLE orders ADD CONSTRAINT check_quantity_constraint CHECK (check_quantity IS NOT NULL); 添加一个约束，要求check_quantity不能为NULL。
• ALTER TABLE orders ADD CONSTRAINT check_price_constraint CHECK (check_price IS NOT NULL); 添加一个约束，要求check_price不能为NULL。

注意事项

• 性能： VIRTUAL类型的Generated Columns在查询时需要实时计算，可能会影响性能。STORED类型的Generated Columns虽然占用存储空间，但可以提高查询速度。你需要根据实际情况选择合适的类型。
• 表达式限制： Generated Columns的计算表达式有一些限制，不能包含子查询、存储过程、用户自定义函数等。
• 循环依赖： Generated Columns不能循环依赖，否则会报错。
• 版本要求： Generated Columns是MySQL 5.7.6版本及以上才支持的特性。

总结

Generated Columns是一个非常有用的特性，可以用来优化查询性能、减少数据冗余、保持数据一致性。但是，在使用Generated Columns时，需要仔细考虑性能、表达式限制等因素，选择合适的类型和表达式。

Generated Columns 的优缺点总结

特性	优点	缺点
VIRTUAL	– 不占用额外存储空间。 – 实时计算，数据始终保持最新。 – 适用于不经常查询的计算列。	– 每次查询都需要计算，可能影响性能。 – 不能用于索引，除非 MySQL 8.0.13 及以上版本，且满足一定条件。
STORED	– 预先计算并存储，查询速度快。 – 可以用于索引，提高查询效率。 – 适用于经常查询的计算列。	– 占用额外存储空间。 – 需要在基表发生变化时更新，可能影响写入性能。 – 如果计算表达式复杂，更新时可能会消耗更多资源。
总体	– 可以简化复杂查询，提高可读性。 – 可以减少数据冗余，保持数据一致性。 – 可以用于数据校验，防止非法数据插入。	– 计算表达式有限制，不能包含子查询、存储过程、用户自定义函数等。 – 不能循环依赖。 – 需要仔细考虑性能、存储空间等因素。
索引优化	– 可以对计算结果建立索引，加速复杂条件查询、字符串处理、JSON字段提取等操作。 – 可以避免在 WHERE 子句中使用函数，充分利用索引。	– 存储列占用额外存储空间。 – 需要在基表发生变化时更新索引，可能影响写入性能。 – 索引维护会增加数据库的负担。
数据冗余	– 可以避免手动维护计算列，减少数据冗余。 – 可以保证计算列的值始终与基表的值保持一致。 – 可以用于数据校验，防止非法数据插入。	– 存储列占用额外存储空间。 – 如果计算表达式复杂，更新时可能会消耗更多资源。 – 需要谨慎设计计算表达式，避免引入错误。

总的来说，Generated Columns是一个强大的工具，但需要根据实际情况权衡利弊，合理使用。

结束语

好了，今天的讲座就到这里。希望大家有所收获，下次有机会再和大家分享更多MySQL的奇技淫巧。谢谢大家！