MySQL高阶讲座之：`MySQL`的`Virtual Columns`：其计算开销与存储成本。

各位观众老爷，大家好！我是你们的老朋友，今天咱们来聊点MySQL里的小秘密，一个既实用又容易被忽略的家伙——Virtual Columns，也就是虚拟列。

想象一下，咱们平时写SQL，是不是经常要对某些字段做计算才能得到想要的结果？比如，你要算个商品的折扣价，或者把两个字段拼起来显示个全名啥的。每次都写一遍计算公式，是不是觉得有点烦？Virtual Columns就是来拯救你的！它允许你创建一个列，它的值不是直接存储的，而是根据其他列的值动态计算出来的。

今天，咱们就深入探讨Virtual Columns的方方面面，重点关注它的计算开销和存储成本，以及如何巧妙地使用它，让你的数据库跑得更快、更省空间。

一、Virtual Columns 是个什么鬼？

简单来说，Virtual Columns 就是一个虚拟的列，它不占用实际的存储空间，它的值是通过一个表达式计算出来的。这个表达式可以引用表中的其他列，甚至可以使用 MySQL 的内置函数。

Virtual Columns 分为两种类型：

• VIRTUAL (也叫Generated Always): 这种类型的 Virtual Columns 的值必须通过表达式计算出来，不能直接插入或更新数据。
• STORED: 这种类型的 Virtual Columns 的值也是通过表达式计算出来的，但是会将计算结果存储在磁盘上。

二、Virtual Columns 的语法

创建 Virtual Columns 的语法很简单，就是在 CREATE TABLE 语句中，像定义普通列一样定义它，但是要加上 GENERATED ALWAYS AS 或 STORED AS 关键字。

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  price DECIMAL(10, 2),
  discount DECIMAL(5, 2),
  discounted_price DECIMAL(10, 2) GENERATED ALWAYS AS (price * (1 - discount)) VIRTUAL,
  full_name VARCHAR(255) GENERATED ALWAYS AS (CONCAT(name, ' - ', price)) STORED
);

• discounted_price 是一个 VIRTUAL 类型的 Virtual Column，它的值是 price * (1 - discount) 计算出来的。每次查询的时候，MySQL 都会实时计算这个值。
• full_name 是一个 STORED 类型的 Virtual Column，它的值是 CONCAT(name, ' - ', price) 计算出来的。MySQL 会将这个计算结果存储在磁盘上，每次查询的时候，直接读取存储的值，不需要重新计算。

三、计算开销：VIRTUAL vs. STORED

这是大家最关心的问题。VIRTUAL 类型的 Virtual Columns 的计算开销在于每次查询都要实时计算，而 STORED 类型的 Virtual Columns 的计算开销在于每次插入或更新数据的时候都要计算并存储。

1. VIRTUAL Columns 的计算开销

VIRTUAL 类型的 Virtual Columns 的计算开销主要取决于表达式的复杂程度。如果表达式很简单，比如只是简单的加减乘除，那么计算开销可以忽略不计。但是如果表达式很复杂，比如使用了大量的函数或者涉及到多个表的连接，那么计算开销就会比较明显。

举个例子：

-- 假设我们有 orders 表，包含 order_id, customer_id, order_date, total_amount 等字段
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  total_amount DECIMAL(10, 2),
  --  计算年份
  order_year INT GENERATED ALWAYS AS (YEAR(order_date)) VIRTUAL
);

-- 查询每年订单总金额
SELECT order_year, SUM(total_amount)
FROM orders
GROUP BY order_year;

在这个例子中，每次查询 order_year 都要调用 YEAR() 函数来计算年份。如果 orders 表的数据量很大，那么这个计算开销就会比较明显。

2. STORED Columns 的计算开销

STORED 类型的 Virtual Columns 的计算开销主要在于每次插入或更新数据的时候都要计算并存储。这意味着每次写操作都会变慢。

举个例子：

--  假设我们有一个 employees 表，包含 first_name, last_name 等字段
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  full_name VARCHAR(255) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED
);

-- 插入一条数据
INSERT INTO employees (employee_id, first_name, last_name) VALUES (1, 'John', 'Doe');

在这个例子中，每次插入或更新 employees 表的数据，都要计算 full_name 并存储。如果 employees 表的数据量很大，并且插入或更新操作很频繁，那么这个计算开销就会比较明显。

如何选择 VIRTUAL 还是 STORED？

选择 VIRTUAL 还是 STORED，需要根据实际情况进行权衡。

• 如果计算表达式很简单，而且查询操作远多于写入操作，那么可以选择 VIRTUAL。 这样可以避免写入操作的开销，并且可以节省存储空间。
• 如果计算表达式很复杂，或者查询操作和写入操作都比较频繁，那么可以选择 STORED。 这样可以避免每次查询都进行复杂的计算，提高查询效率。
• 如果查询次数很少，但对读取性能要求极高，也可以考虑 STORED。 牺牲写入性能，换取读取性能。

总结一下，用表格更清晰：

特性	VIRTUAL	STORED
计算时机	查询时实时计算	写入时计算并存储
存储空间	不占用存储空间	占用存储空间
查询性能	慢（每次查询都要计算）	快（直接读取存储的值）
写入性能	快（不需要计算）	慢（需要计算并存储）
适用场景	计算简单，查询多于写入	计算复杂，查询和写入都频繁，或读取性能要求极高

四、存储成本：空间换时间？

STORED 类型的 Virtual Columns 会占用额外的存储空间，这是它的一个缺点。但是，存储空间越来越便宜，而 CPU 资源却越来越宝贵。在很多情况下，用存储空间换取 CPU 时间是值得的。

1. 存储空间占用

STORED 类型的 Virtual Columns 的存储空间占用取决于计算结果的数据类型和长度。例如，如果计算结果是 VARCHAR(255) 类型的字符串，那么每个 Virtual Column 就会占用最多 255 个字节的存储空间。

2. 存储成本的权衡

在考虑是否使用 STORED 类型的 Virtual Columns 时，需要权衡存储成本和计算成本。

• 如果存储空间很紧张，那么应该尽量避免使用 STORED 类型的 Virtual Columns。
• 如果计算成本很高，或者对查询性能要求很高，那么可以考虑使用 STORED 类型的 Virtual Columns。

五、Virtual Columns 的应用场景

Virtual Columns 有很多应用场景，可以简化 SQL 语句，提高查询效率，甚至可以用来创建索引。

1. 简化 SQL 语句

Virtual Columns 可以将复杂的计算逻辑封装起来，简化 SQL 语句。

举个例子：

--  假设我们有一个 products 表，包含 price 和 discount 字段
--  如果没有 Virtual Columns，我们需要这样计算折扣价：
SELECT price * (1 - discount) AS discounted_price
FROM products;

--  有了 Virtual Columns，我们可以这样：
CREATE TABLE products (
  id INT PRIMARY KEY,
  price DECIMAL(10, 2),
  discount DECIMAL(5, 2),
  discounted_price DECIMAL(10, 2) GENERATED ALWAYS AS (price * (1 - discount)) VIRTUAL
);

SELECT discounted_price
FROM products;

可以看到，使用 Virtual Columns 可以使 SQL 语句更简洁易懂。

2. 提高查询效率

STORED 类型的 Virtual Columns 可以提高查询效率，因为它避免了每次查询都进行复杂的计算。

举个例子：

--  假设我们有一个 orders 表，包含 order_date 字段
--  我们需要频繁地查询每个月的订单总金额
--  如果没有 Virtual Columns，我们需要这样：
SELECT MONTH(order_date), SUM(total_amount)
FROM orders
GROUP BY MONTH(order_date);

--  有了 Virtual Columns，我们可以这样：
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  order_date DATE,
  total_amount DECIMAL(10, 2),
  order_month INT GENERATED ALWAYS AS (MONTH(order_date)) STORED
);

SELECT order_month, SUM(total_amount)
FROM orders
GROUP BY order_month;

在这个例子中，使用 STORED 类型的 Virtual Column order_month 可以避免每次查询都调用 MONTH() 函数，从而提高查询效率。

3. 创建索引

Virtual Columns 可以用来创建索引，从而提高查询效率。

举个例子：

-- 假设我们有一个 users 表，包含 first_name 和 last_name 字段
--  我们需要根据 full_name 来查询用户
--  我们可以创建一个 Virtual Column 和一个索引：
CREATE TABLE users (
  user_id INT PRIMARY KEY,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  full_name VARCHAR(255) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED,
  INDEX idx_full_name (full_name)
);

--  现在我们可以这样查询：
SELECT *
FROM users
WHERE full_name = 'John Doe';

在这个例子中，我们创建了一个 STORED 类型的 Virtual Column full_name，并创建了一个索引 idx_full_name。这样，当我们根据 full_name 来查询用户时，MySQL 可以使用索引，从而提高查询效率。

注意： 只有 STORED 类型的 Virtual Columns 才能创建索引。VIRTUAL 类型的 Virtual Columns 不能创建索引。

六、Virtual Columns 的限制

Virtual Columns 也有一些限制：

• 不能直接插入或更新 VIRTUAL 类型的 Virtual Columns。 只能通过修改表达式中引用的列的值来间接更新 Virtual Columns 的值。
• 表达式不能包含子查询、存储过程或用户自定义函数。
• 表达式不能引用自身或其他的 Virtual Columns。 也就是说，Virtual Columns 不能依赖其他的 Virtual Columns。
• 在 BEFORE INSERT 或 BEFORE UPDATE 触发器中，不能修改 VIRTUAL 类型的 Virtual Columns。
• 某些数据类型可能不支持 Virtual Columns。 例如，TEXT 和 BLOB 类型可能不支持。需要查阅具体的 MySQL 版本文档。

七、总结与建议

Virtual Columns 是 MySQL 中一个非常实用的功能，可以简化 SQL 语句，提高查询效率，甚至可以用来创建索引。但是，Virtual Columns 也有一些限制，需要根据实际情况进行权衡。

我的建议是：

• 了解 Virtual Columns 的基本概念和语法。
• 根据实际情况选择 VIRTUAL 还是 STORED。
• 合理使用 Virtual Columns，避免过度使用。 不要为了使用 Virtual Columns 而使用 Virtual Columns。
• 注意 Virtual Columns 的限制。
• 在生产环境中使用 Virtual Columns 之前，进行充分的测试。

Virtual Columns就像一把双刃剑，用好了能让你事半功倍，用不好可能适得其反。希望今天的讲座能帮助大家更好地理解和使用 Virtual Columns，让你的数据库跑得更快、更省空间。

好了，今天的讲座就到这里。感谢大家的观看，咱们下期再见！如果觉得讲得还可以，点个赞再走呗！