MySQL Generated Always As表达式索引的使用技巧

MySQL 5.7 引入了虚拟列功能，MySQL 8.0 进一步完善了函数索引的支持。利用 GENERATED ALWAYS AS 可以在不修改业务代码逻辑的前提下，将复杂的计算、函数调用或 JSON 字段提取逻辑固化为列，并直接对其建立索引。这能有效解决无法直接对函数表达式建立索引或查询性能低下的问题。

场景一：解决计算字段查询慢的问题

假设有一张订单表 orders，你需要频繁查询“商品单价乘以数量”大于特定值的订单。直接在 WHERE 子句中使用计算公式通常会导致索引失效，引发全表扫描。

1. 创建包含基础字段的测试表。

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    unit_price DECIMAL(10, 2),
    quantity INT,
    order_date DATE
);

2. 插入一些测试数据。

INSERT INTO orders (product_name, unit_price, quantity, order_date) VALUES
('Laptop', 1200.00, 2, '2023-10-01'),
('Mouse', 25.50, 10, '2023-10-02'),
('Keyboard', 45.00, 5, '2023-10-03');

3. 添加一个基于表达式的虚拟列 total_price。

使用 VIRTUAL 关键字（默认也是 VIRTUAL），表示该列不占用物理存储空间，而是在读取时动态计算。

ALTER TABLE orders
ADD COLUMN total_price DECIMAL(10, 2)
GENERATED ALWAYS AS (unit_price * quantity) VIRTUAL;

4. 建立针对该虚拟列的索引。

CREATE INDEX idx_total_price ON orders(total_price);

5. 执行查询验证索引生效。

在查询条件中直接引用新生成的列，MySQL 优化器将使用索引。

EXPLAIN SELECT * FROM orders WHERE total_price > 200;

在输出结果中，检查 type 列是否为 range，key 列是否为 idx_total_price，以此确认索引已生效。

场景二：JSON 字段的高效索引

在现代应用开发中，JSON 数据类型越来越常用。直接对 JSON 文本进行模糊查询效率极低。通过 GENERATED ALWAYS AS 提取 JSON 中的特定路径并建立索引，可以大幅提升性能。

1. 创建包含 JSON 列的表 users。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    info JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 插入包含嵌套结构的 JSON 数据。

INSERT INTO users (info) VALUES
('{"name": "Alice", "contact": {"email": "alice@example.com", "phone": "123456789"}}'),
('{"name": "Bob", "contact": {"email": "bob@example.com", "phone": "987654321"}}');

3. 添加虚拟列提取 JSON 中的 email 字段。

使用 ->> 操作符（MySQL 5.7.9+）提取 JSON 字段并转换为字符串。

ALTER TABLE users
ADD COLUMN email VARCHAR(255)
GENERATED ALWAYS AS (info->>"$.contact.email") VIRTUAL;
```

4.  **创建**唯一索引以防止重复邮箱。

```sql
CREATE UNIQUE INDEX idx_user_email ON users(email);
```

5.  **测试**基于邮箱的快速查询。

```sql
SELECT * FROM users WHERE email = 'alice@example.com';
```

数据库将直接利用 `idx_user_email` 索引查找数据，而无需解析每行的 JSON 文本。

---

### 核心参数：STORED 与 VIRTUAL 的选择

定义生成列时，必须权衡存储空间与查询性能。`STORED` 和 `VIRTUAL` 是两种主要的存储模式，它们的特性对比决定了具体的使用场景。

| 特性对比 | VIRTUAL (虚拟) | STORED (存储) |
| :--- | :--- | :--- |
| **物理存储** | 不占用磁盘空间，仅作为元数据存在 | 占用磁盘空间，数据实际写入磁盘 |
| **写入性能** | 高（不涉及计算数据的写入操作） | 较低（每次插入或更新都必须计算并存储值） |
| **读取性能** | 较低（每次读取都需要实时计算） | 高（直接读取已计算好的值） |
| **适用场景** | 计算逻辑简单、写入频繁但读取较少 | 计算代价大、读取频率极高 |

1.  **使用** `VIRTUAL` 当表达式计算非常简单（如加减乘除），且表写入压力巨大时。
2.  **使用** `STORED` 当计算逻辑复杂（如复杂的字符串函数调用），且该字段经常被用于查询或排序时。

---

### 进阶技巧：让优化器自动适配

在 MySQL 8.0.13 及以上版本中，MySQL 支持函数索引。这意味着你甚至不需要在 `SELECT` 或 `WHERE` 子句中显式引用生成列的名字，只要表达式一致，优化器就会自动匹配索引。

1.  **定义**一个存储式的生成列（示例为将邮箱转为小写）。

```sql
ALTER TABLE users
ADD COLUMN lower_email VARCHAR(255)
GENERATED ALWAYS AS (LOWER(info->>"$.contact.email")) STORED;

2. 创建索引。

CREATE INDEX idx_lower_email ON users(lower_email);

3. 执行使用了 LOWER() 函数的查询，无需修改查询去使用 lower_email 列名。

SELECT * FROM users WHERE LOWER(info->>"$.contact.email") = 'ALICE@EXAMPLE.COM';
```

MySQL 优化器会自动识别出 `LOWER(info->>"$.contact.email")` 与索引定义中的表达式完全一致，从而自动使用 `idx_lower_email` 索引。这一特性允许你在不修改旧代码复杂 SQL 的情况下，利用生成列技术实现底层性能优化。