好的，我们开始今天的主题：MySQL的Generated Columns，以及它们在数据冗余和索引优化中的应用。
引言：Generated Columns的引入
在数据库设计中，我们常常面临着数据冗余和查询性能之间的权衡。为了提高查询效率，我们有时会引入冗余列，存储那些可以通过其他列计算得到的值。然而，这种做法带来了数据一致性的维护负担。MySQL的Generated Columns（生成列）的出现，为我们提供了一种优雅的解决方案，它既能提升查询性能，又能保证数据的一致性。
什么是Generated Columns?
Generated Columns是一种特殊的列，它的值不是直接存储的，而是通过一个表达式计算得到的。这个表达式可以引用同一表中的其他列。Generated Columns分为两种类型：
- Virtual Generated Columns (虚拟生成列): 虚拟生成列不占用存储空间，其值在查询时动态计算。
- Stored Generated Columns (存储生成列): 存储生成列的值会被物理存储，类似于普通列。
语法和示例
创建一个包含Generated Columns的表，我们需要使用GENERATED ALWAYS AS子句。
CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
full_name VARCHAR(100) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
email VARCHAR(100),
email_domain VARCHAR(50) GENERATED ALWAYS AS (SUBSTRING_INDEX(email, '@', -1)) STORED
);在这个例子中：
full_name是一个虚拟生成列，它的值是first_name和last_name的拼接，并在查询时动态计算。email_domain是一个存储生成列，它的值是从email提取的域名部分，并会被物理存储。
插入数据
插入数据时，不需要显式地为Generated Columns赋值，MySQL会自动计算并填充它们的值。
INSERT INTO employees (id, first_name, last_name, email) VALUES
(1, 'John', 'Doe', '[email protected]'),
(2, 'Jane', 'Smith', '[email protected]');
SELECT * FROM employees;查询结果：
| id | first_name | last_name | full_name | email_domain | |
|---|---|---|---|---|---|
| 1 | John | Doe | John Doe | [email protected] | example.com |
| 2 | Jane | Smith | Jane Smith | [email protected] | company.org |
Generated Columns在数据冗余中的应用
Generated Columns可以有效地减少数据冗余，同时避免数据不一致的问题。 例如，考虑一个订单表，其中包含商品单价和数量，我们可能需要频繁查询订单的总金额。
CREATE TABLE orders (
order_id INT PRIMARY KEY,
product_id INT,
unit_price DECIMAL(10, 2),
quantity INT,
total_amount DECIMAL(10, 2) GENERATED ALWAYS AS (unit_price * quantity) VIRTUAL
);
INSERT INTO orders (order_id, product_id, unit_price, quantity) VALUES
(1, 101, 25.50, 2),
(2, 102, 10.00, 5),
(3, 103, 50.00, 1);
SELECT * FROM orders;查询结果：
| order_id | product_id | unit_price | quantity | total_amount |
|---|---|---|---|---|
| 1 | 101 | 25.50 | 2 | 51.00 |
| 2 | 102 | 10.00 | 5 | 50.00 |
| 3 | 103 | 50.00 | 1 | 50.00 |
在这个例子中，total_amount是一个虚拟生成列，它的值是unit_price和quantity的乘积。我们不需要手动维护total_amount的值，MySQL会自动计算，保证了数据的一致性。
如果我们选择将total_amount存储为普通列，那么每次修改unit_price或quantity时，都需要同时更新total_amount，这增加了复杂性和出错的风险。Generated Columns避免了这个问题。
Generated Columns在索引优化中的应用
Generated Columns可以用于索引优化，特别是在以下场景：
- 复杂表达式的查询: 如果查询条件涉及到复杂的表达式，可以将表达式的结果存储在Generated Column中，并对该列创建索引。
- 数据类型转换: 如果需要对某个列进行数据类型转换才能进行有效查询，可以将转换后的值存储在Generated Column中，并对该列创建索引。
- 部分匹配查询: 如果需要对某个列进行部分匹配查询（例如，模糊查询），可以将匹配的部分存储在Generated Column中，并对该列创建索引。
示例1: 复杂表达式的查询
假设我们有一个存储JSON数据的表，我们需要频繁查询JSON数据中某个特定字段的值。
CREATE TABLE json_data (
id INT PRIMARY KEY,
data JSON
);
INSERT INTO json_data (id, data) VALUES
(1, '{"name": "Alice", "age": 30}'),
(2, '{"name": "Bob", "age": 25}'),
(3, '{"name": "Charlie", "age": 35}');如果我们需要查询所有年龄大于28的人，我们可以使用以下查询：
SELECT * FROM json_data WHERE JSON_EXTRACT(data, '$.age') > 28;这个查询的性能可能不高，因为JSON_EXTRACT函数需要对每一行数据进行计算。为了优化查询性能，我们可以创建一个Generated Column来存储JSON数据中的年龄，并对该列创建索引。
ALTER TABLE json_data ADD COLUMN age INT GENERATED ALWAYS AS (JSON_EXTRACT(data, '$.age')) VIRTUAL;
CREATE INDEX idx_age ON json_data (age);
SELECT * FROM json_data WHERE age > 28;通过创建一个Generated Column age，并将JSON_EXTRACT函数的结果存储在其中，我们可以对age列创建索引，从而加速查询。
示例2: 数据类型转换
假设我们有一个存储日期的表，日期以字符串的形式存储，我们需要频繁按日期范围查询数据。
CREATE TABLE events (
id INT PRIMARY KEY,
event_date VARCHAR(10)
);
INSERT INTO events (id, event_date) VALUES
(1, '2023-01-15'),
(2, '2023-02-20'),
(3, '2023-03-10');如果我们需要查询2023年2月之后的所有事件，我们可以使用以下查询：
SELECT * FROM events WHERE event_date >= '2023-02-01';但是，由于event_date是字符串类型，这个查询可能会进行字符串比较，性能不高。为了优化查询性能，我们可以创建一个Generated Column将字符串转换为日期类型，并对该列创建索引。
ALTER TABLE events ADD COLUMN date DATE GENERATED ALWAYS AS (STR_TO_DATE(event_date, '%Y-%m-%d')) VIRTUAL;
CREATE INDEX idx_date ON events (date);
SELECT * FROM events WHERE date >= '2023-02-01';通过创建一个Generated Column date，并将event_date转换为日期类型，我们可以对date列创建索引，从而加速查询。
示例3: 部分匹配查询
假设我们有一个存储用户信息的表，我们需要频繁按用户名的前缀查询用户。
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50)
);
INSERT INTO users (id, username) VALUES
(1, 'Alice123'),
(2, 'Bob456'),
(3, 'Alice789');如果我们需要查询所有以"Alice"开头的用户，我们可以使用以下查询：
SELECT * FROM users WHERE username LIKE 'Alice%';这个查询的性能可能不高，因为LIKE操作需要对每一行数据进行匹配。为了优化查询性能，我们可以创建一个Generated Column来存储用户名的前缀，并对该列创建索引。
ALTER TABLE users ADD COLUMN username_prefix VARCHAR(50) GENERATED ALWAYS AS (LEFT(username, 5)) STORED;
CREATE INDEX idx_username_prefix ON users (username_prefix);
SELECT * FROM users WHERE username_prefix = 'Alice';通过创建一个Generated Column username_prefix，并将用户名的前缀存储在其中，我们可以对username_prefix列创建索引，从而加速查询。 注意这里使用了STORED，因为虚拟列无法直接用于前缀匹配。
Virtual vs. Stored Generated Columns的选择
选择Virtual Generated Columns还是Stored Generated Columns，取决于具体的应用场景。
-
Virtual Generated Columns:
- 优点：不占用存储空间，节省磁盘空间。
- 缺点：每次查询都需要计算，会增加CPU开销。
- 适用场景：计算逻辑简单，不经常被查询的列。
-
Stored Generated Columns:
- 优点：查询时不需要计算，性能更高。
- 缺点：占用存储空间，会增加磁盘空间。
- 适用场景：计算逻辑复杂，经常被查询的列，或需要创建索引的列。
以下表格总结了Virtual和Stored Generated Columns的比较：
| 特性 | Virtual Generated Columns | Stored Generated Columns |
|---|---|---|
| 存储空间 | 不占用 | 占用 |
| 计算时机 | 查询时 | 数据写入时 |
| 查询性能 | 较低 | 较高 |
| 写入性能 | 较高 | 较低 |
| 适用场景 | 计算简单，不常查询 | 计算复杂，常查询 |
| 是否可建索引 | 可以 | 可以 |
注意事项和限制
- Generated Columns的表达式必须是确定性的，即对于相同的输入，必须产生相同的输出。
- Generated Columns不能引用其他的Generated Columns（MySQL 5.7及更早版本）。MySQL 8.0支持引用其他Generated Column，但是存在循环依赖检查。
- Generated Columns不能引用
AUTO_INCREMENT列。 - Generated Columns不能使用
RAND()、UUID()等不确定性函数。 - Generated Columns不能使用存储过程或函数。
- 修改Generated Columns的表达式需要较高的权限。
- 在涉及大数据量的表中使用Stored Generated Columns时，需要考虑额外的存储成本和写入性能影响。
- Generated Columns的表达式不能超过一定的长度限制。
最佳实践
- 在设计数据库时，仔细评估哪些列可以通过Generated Columns计算得到，避免冗余存储。
- 根据查询频率和计算复杂度选择Virtual或Stored Generated Columns。
- 对经常用于查询的Generated Columns创建索引，提高查询性能。
- 定期检查Generated Columns的表达式是否需要更新，以适应业务需求的变化。
- 在修改Generated Columns的表达式之前，进行充分的测试，确保不会影响现有数据和查询。
- 监控Generated Columns的使用情况，及时发现和解决性能问题。
总结：数据一致与性能优化的平衡
MySQL的Generated Columns为我们提供了一种强大的工具，它可以在数据冗余和查询性能之间找到平衡点。 通过合理使用Generated Columns，我们可以减少数据冗余，提高数据一致性，并优化查询性能。 在实际应用中，我们需要根据具体的业务场景和性能需求，选择合适的Generated Columns类型和索引策略，以达到最佳效果。