MySQL的`Generated Columns`：如何实现基于函数的索引？

MySQL Generated Columns 与函数索引：优化之路

各位，大家好！今天我们来深入探讨 MySQL 中 Generated Columns (生成列) 的高级应用，特别是如何利用它来实现基于函数的索引，从而大幅提升特定场景下的查询性能。

传统索引通常是基于列的原始值建立的，但有些查询需要基于列值的某种转换或计算结果进行过滤。例如，你可能需要根据字符串的大写形式、日期字段的年份，或者两个数字字段的乘积来查找数据。在没有 Generated Columns 的情况下，你只能在查询语句中使用函数，但这样会导致索引失效，从而进行全表扫描，效率极低。

Generated Columns 允许你创建一个新的列，其值由一个表达式计算而来，这个表达式可以包含内置函数、其他列，甚至是自定义函数（UDF）。更重要的是，你可以在这个生成列上创建索引，从而实现基于函数的索引。

什么是 Generated Columns？

Generated Columns 是 MySQL 5.7.6 引入的一个特性，它可以让你创建一个列，其值是根据一个表达式自动计算出来的。这意味着，每次插入或更新数据时，这个列的值都会自动更新。

Generated Columns 有两种类型：

• VIRTUAL: 虚拟生成列，只在查询时计算，不占用存储空间。
• STORED: 存储生成列，在数据写入时计算并存储，占用存储空间。

选择哪种类型取决于具体的应用场景。如果计算成本较低，且查询频率不高，可以选择 VIRTUAL 列。如果计算成本较高，或者查询频率很高，可以选择 STORED 列，以避免每次查询都重复计算。

如何创建 Generated Columns？

创建 Generated Columns 的语法如下：

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

• table_name: 要修改的表名。
• column_name: 生成列的名称。
• column_definition: 生成列的数据类型和约束。
• expression: 计算生成列值的表达式。
• VIRTUAL | STORED: 指定生成列的类型。默认为 VIRTUAL。

示例：

假设我们有一个 products 表，包含 product_name 和 price 列。我们想要创建一个生成列 upper_name，用于存储 product_name 的大写形式，并创建一个生成列 discounted_price，存储打8折后的价格。

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255),
    price DECIMAL(10, 2)
);

ALTER TABLE products
ADD COLUMN upper_name VARCHAR(255)
GENERATED ALWAYS AS (UPPER(product_name)) VIRTUAL;

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

INSERT INTO products (product_name, price) VALUES
('Laptop', 1200.00),
('Mouse', 25.00),
('Keyboard', 75.00);

SELECT * FROM products;

查询结果如下：

id	product_name	price	upper_name	discounted_price
1	Laptop	1200.00	LAPTOP	960.00
2	Mouse	25.00	MOUSE	20.00
3	Keyboard	75.00	KEYBOARD	60.00

可以看到，upper_name 列存储了 product_name 的大写形式，discounted_price 列存储了打8折后的价格。

基于函数的索引：解决索引失效的问题

现在，我们来解决索引失效的问题。假设我们需要根据 product_name 的大写形式进行查询。如果没有 Generated Columns，我们可能会这样写：

SELECT * FROM products WHERE UPPER(product_name) = 'LAPTOP';

虽然这条语句能够返回正确的结果，但是 MySQL 无法使用 product_name 列上的索引，因为查询条件中使用了 UPPER() 函数。这会导致全表扫描，效率非常低。

有了 Generated Columns，我们可以创建一个存储 product_name 大写形式的生成列，并在该列上创建索引。

-- 如果没有创建 upper_name 列，先创建
ALTER TABLE products
ADD COLUMN upper_name VARCHAR(255)
GENERATED ALWAYS AS (UPPER(product_name)) VIRTUAL; -- 或者 STORED，根据场景选择

-- 创建索引
CREATE INDEX idx_upper_name ON products (upper_name);

-- 查询语句
SELECT * FROM products WHERE upper_name = 'LAPTOP';

-- 使用 EXPLAIN 查看执行计划
EXPLAIN SELECT * FROM products WHERE upper_name = 'LAPTOP';

通过 EXPLAIN 命令，我们可以看到 MySQL 使用了 idx_upper_name 索引，从而避免了全表扫描，大大提高了查询效率。

更多示例：日期函数、数学函数、自定义函数

Generated Columns 可以与各种内置函数和自定义函数一起使用，从而实现更复杂的索引。

1. 日期函数：根据年份创建索引

假设我们有一个 orders 表，包含 order_date 列。我们想要根据订单年份进行查询。

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

ALTER TABLE orders
ADD COLUMN order_year INT
GENERATED ALWAYS AS (YEAR(order_date)) STORED;

CREATE INDEX idx_order_year ON orders (order_year);

-- 查询语句
SELECT * FROM orders WHERE order_year = 2023;

2. 数学函数：根据计算结果创建索引

假设我们有一个 items 表，包含 quantity 和 price 列。我们想要根据 quantity * price 的结果进行查询。

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

ALTER TABLE items
ADD COLUMN total_price DECIMAL(10, 2)
GENERATED ALWAYS AS (quantity * price) STORED;

CREATE INDEX idx_total_price ON items (total_price);

-- 查询语句
SELECT * FROM items WHERE total_price > 1000;

3. 自定义函数 (UDF)：实现更复杂的逻辑

如果你需要实现更复杂的逻辑，可以使用自定义函数 (UDF)。首先，你需要创建一个 UDF，然后才能在 Generated Columns 中使用它。

注意： UDF 的创建和使用涉及到 C/C++ 编程，以及 MySQL 的插件机制，比较复杂。这里只给出一个简单的示例，仅作演示用途。

假设我们有一个 UDF my_function，它可以将字符串转换为小写形式。

-- 假设 UDF 已经创建并注册
ALTER TABLE products
ADD COLUMN lower_name VARCHAR(255)
GENERATED ALWAYS AS (my_function(product_name)) STORED;

CREATE INDEX idx_lower_name ON products (lower_name);

-- 查询语句
SELECT * FROM products WHERE lower_name = 'laptop';

Generated Columns 的限制

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

• 表达式限制： Generated Columns 的表达式不能包含子查询、存储过程、用户变量、以及某些非确定性函数（如 RAND()、UUID()）。
• 循环依赖： Generated Columns 不能循环依赖，即一个生成列不能依赖于另一个生成列，而后者又依赖于前者。
• 性能影响： STORED 类型的 Generated Columns 会增加数据写入的开销，因为每次写入数据时都需要计算生成列的值。

选择 VIRTUAL 还是 STORED？

选择 VIRTUAL 还是 STORED 取决于具体的应用场景。

特性	VIRTUAL	STORED
存储空间	不占用	占用
计算时间	查询时计算	写入时计算
写入性能	较高	较低
读取性能	较低	较高
适用场景	计算成本低，查询频率不高	计算成本高，查询频率高

一般来说，如果计算成本较低，且查询频率不高，可以选择 VIRTUAL 列。如果计算成本较高，或者查询频率很高，可以选择 STORED 列。

最佳实践

• 谨慎选择生成列类型： 根据计算成本和查询频率选择 VIRTUAL 或 STORED 类型。
• 避免复杂的表达式： 尽量使用简单的表达式，以避免影响性能。
• 定期维护索引： 定期检查和维护索引，以确保其有效性。
• 监控性能： 监控数据库的性能，以便及时发现和解决问题。

示例：JSON 列与 Generated Columns 的结合

MySQL 5.7 之后支持 JSON 数据类型，Generated Columns 可以与 JSON 列结合使用，实现更灵活的数据处理和索引。

假设我们有一个 users 表，其中包含一个 profile JSON 列，用于存储用户的个人信息。我们想要根据用户的 age 进行查询。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    profile JSON
);

INSERT INTO users (profile) VALUES
('{"name": "Alice", "age": 30}'),
('{"name": "Bob", "age": 25}'),
('{"name": "Charlie", "age": 35}');

ALTER TABLE users
ADD COLUMN age INT
GENERATED ALWAYS AS (profile ->> '$.age') STORED;

CREATE INDEX idx_age ON users (age);

-- 查询语句
SELECT * FROM users WHERE age > 30;

在这个例子中，我们创建了一个 age 生成列，其值从 profile JSON 列中提取。然后，我们在 age 列上创建了索引，从而可以高效地根据用户的年龄进行查询。 注意，这里使用了 ->> 操作符，它会将 JSON 值转换为字符串。如果需要保持数值类型，可以使用 -> 操作符，但需要在创建索引时指定索引类型 (Functional Index)。

总结一下

Generated Columns 是 MySQL 中一个强大的特性，可以让你创建基于表达式的列，并在这些列上创建索引，从而大幅提升特定场景下的查询性能。通过合理地利用 Generated Columns，可以避免全表扫描，优化查询语句，提高数据库的整体性能。 需要注意的是，在使用 Generated Columns 时，需要仔细考虑其限制和性能影响，并根据具体的应用场景选择合适的类型和表达式。

持续学习，不断优化

数据库优化是一个持续学习和实践的过程。希望今天的分享能够帮助大家更好地理解和应用 MySQL 的 Generated Columns 特性，并在实际工作中不断优化数据库性能。