好的，各位观众老爷，欢迎来到“MySQL 索引奇妙夜”！🌙 今天，咱们不聊风花雪月，就来聊聊 MySQL 5.7+ 里的两个小妖精：虚拟列（Virtual Columns）和存储列（Stored Columns），看看它们在索引的舞台上，是如何搔首弄姿，大放异彩的！
开场白：索引，数据库的“导航卫星”
话说这数据库啊，就像一个堆满书籍的大仓库。你想找本书，没有索引，就得一排排地翻，那效率，简直比蜗牛还慢！ 🐌 索引，就是咱们数据库的“导航卫星”，能帮你快速定位到想要的数据，提高查询效率，让你的数据库跑得飞快！🚀
第一幕：隆重登场！虚拟列（Virtual Columns）
虚拟列，顾名思义，它不是真实存在的列，而是通过表达式计算出来的。就像你化妆一样，原本平平无奇的脸蛋，经过一番修饰，瞬间变美了！ 💄
1. 什么是虚拟列？
虚拟列，也叫生成列（Generated Columns），它不会占用额外的存储空间，每次查询时，MySQL 都会根据定义的表达式实时计算。
2. 虚拟列的语法
创建表时，可以这样定义虚拟列：
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(255),
price DECIMAL(10, 2),
discount DECIMAL(3, 2),
final_price DECIMAL(10, 2) AS (price * (1 - discount)) VIRTUAL
);这里，final_price 就是一个虚拟列，它的值等于 price * (1 - discount)。注意最后的 VIRTUAL 关键字，它告诉 MySQL 这是一列虚拟列。
3. 虚拟列的优点
- 节省空间： 不占用额外的存储空间，就像你的影子一样，跟你形影不离，却不增加你的体重。
- 简化查询： 可以将复杂的计算逻辑封装在虚拟列中，简化查询语句，提高代码可读性。
- 实时更新： 当依赖的列发生变化时，虚拟列的值会自动更新，保持数据的一致性。
4. 虚拟列的缺点
- 计算开销： 每次查询都需要实时计算，会增加 CPU 的负担，尤其是在数据量大、计算复杂的场景下。
- 不适用于复杂计算： 对于过于复杂的计算，虚拟列可能会成为性能瓶颈。
5. 虚拟列与索引的爱恨情仇
虚拟列虽然好用，但它本身是不能直接创建索引的！ 💔 因为每次查询都需要实时计算，如果直接在虚拟列上创建索引，MySQL 就不知道该如何维护这个索引了。
但是！ 别灰心！ MySQL 5.7.6 之后，允许在虚拟列上创建索引啦！ 🎉 但是，这里有个前提：虚拟列必须是确定性的（Deterministic）。
什么是确定性的？
简单来说，就是对于相同的输入，虚拟列的计算结果必须始终相同。例如，price * (1 - discount) 就是一个确定性的表达式，而 RAND() 或 NOW() 这种每次都返回不同结果的函数，就不是确定性的。
如何在虚拟列上创建索引？
CREATE INDEX idx_final_price ON products (final_price);只要 final_price 的表达式是确定性的，就可以这样创建索引。
6. 虚拟列索引的适用场景
- 频繁查询的计算结果： 如果某个计算结果经常被用于查询，并且计算表达式是确定性的，就可以考虑在虚拟列上创建索引，提高查询效率。
- 简化复杂查询： 可以将复杂的计算逻辑封装在虚拟列中，然后在虚拟列上创建索引，简化查询语句，提高代码可读性。
第二幕：闪亮登场！存储列（Stored Columns）
存储列，就像你的照片一样，是真实存在的，需要占用额外的存储空间。但是，它也带来了更高的查询效率！ 🖼️
1. 什么是存储列？
存储列，也叫持久列（Persisted Columns），它会将计算结果存储在磁盘上，就像普通列一样。
2. 存储列的语法
创建表时，可以这样定义存储列：
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(255),
price DECIMAL(10, 2),
discount DECIMAL(3, 2),
final_price DECIMAL(10, 2) AS (price * (1 - discount)) STORED
);这里，final_price 就是一个存储列，它的值等于 price * (1 - discount)。注意最后的 STORED 关键字，它告诉 MySQL 这是一列存储列。
3. 存储列的优点
- 查询效率高： 因为计算结果已经存储在磁盘上，所以查询时不需要实时计算，直接读取即可，效率非常高。
- 适用于复杂计算： 对于复杂的计算，存储列可以避免每次查询都进行计算，提高性能。
4. 存储列的缺点
- 占用空间： 需要占用额外的存储空间，就像你的照片一样，越多越占地方。
- 更新开销： 当依赖的列发生变化时，存储列的值也需要更新，会增加写入操作的开销。
5. 存储列与索引的完美结合
存储列就像一位天生丽质的美女，天生就适合在索引的舞台上翩翩起舞！💃 你可以直接在存储列上创建索引，没有任何限制！
CREATE INDEX idx_final_price ON products (final_price);6. 存储列索引的适用场景
- 频繁查询且计算复杂的场景： 如果某个计算结果经常被用于查询，并且计算表达式比较复杂，就可以考虑使用存储列，并在其上创建索引，提高查询效率。
- 对写入性能要求不高的场景： 因为存储列的更新会增加写入操作的开销，所以适用于对写入性能要求不高的场景。
第三幕：虚拟列 vs 存储列，谁才是你的菜？
虚拟列和存储列，就像一对双胞胎姐妹，各有千秋，各有优点。那么，到底该选择哪个呢？ 🤔
| 特性 | 虚拟列 (VIRTUAL) | 存储列 (STORED) |
|---|---|---|
| 存储空间 | 不占用 | 占用 |
| 计算方式 | 实时计算 | 预先计算并存储 |
| 查询效率 | 较低 | 较高 |
| 更新开销 | 无 | 有 |
| 索引限制 | 必须是确定性的 | 无限制 |
| 适用场景 | 节省空间，计算简单 | 性能要求高，计算复杂 |
总结一下：
- 如果你追求极致的节省空间，并且计算表达式比较简单，那就选择虚拟列！
- 如果你追求更高的查询效率，并且不介意占用额外的存储空间，那就选择存储列！
- 如果你不确定，那就先用虚拟列试试，如果性能不满足要求，再考虑改成存储列！
案例分析：实战演练
假设我们有一个电商网站，需要根据商品的价格和折扣计算最终价格，并根据最终价格进行查询。
方案一：使用虚拟列
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(255),
price DECIMAL(10, 2),
discount DECIMAL(3, 2),
final_price DECIMAL(10, 2) AS (price * (1 - discount)) VIRTUAL
);
CREATE INDEX idx_final_price ON products (final_price);
SELECT * FROM products WHERE final_price BETWEEN 100 AND 200;优点： 节省空间，不需要额外的存储成本。
缺点： 查询效率相对较低，每次查询都需要实时计算 final_price。
方案二：使用存储列
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(255),
price DECIMAL(10, 2),
discount DECIMAL(3, 2),
final_price DECIMAL(10, 2) AS (price * (1 - discount)) STORED
);
CREATE INDEX idx_final_price ON products (final_price);
SELECT * FROM products WHERE final_price BETWEEN 100 AND 200;优点： 查询效率高，不需要实时计算 final_price。
缺点： 占用额外的存储空间，每次更新 price 或 discount 时，都需要更新 final_price。
最终选择：
如果我们的商品数量非常庞大，并且对存储空间的要求比较高，可以选择虚拟列。如果我们的商品数量不是特别大，并且对查询效率的要求比较高，可以选择存储列。
第四幕：注意事项，避坑指南
- 确定性表达式： 在虚拟列上创建索引时，一定要确保表达式是确定性的，否则 MySQL 会报错。
- 数据类型： 虚拟列和存储列的数据类型必须与表达式的计算结果类型一致。
- 性能测试： 在生产环境中使用虚拟列或存储列之前，一定要进行充分的性能测试，确保满足性能要求。
结尾：索引优化，永无止境
好了，各位观众老爷，今天的“MySQL 索引奇妙夜”就到这里了。希望通过今天的讲解，大家对 MySQL 5.7+ 的虚拟列和存储列有了更深入的了解。记住，索引优化是一个永无止境的过程，需要不断学习和实践，才能找到最适合自己的方案！ 💪
最后，祝大家都能写出高效的 SQL 语句，让你的数据库跑得飞快！ 💨
感谢大家的观看！ 💖