MySQL虚拟列

当我们谈论数据库优化时，经常会遇到各种技术和策略。其中，MySQL的虚拟列（也被称为生成列或存储列）是一个引人注目的特性。它不仅可以帮助开发者提高查询效率，还能为数据表提供额外的计算功能，而无需真正改变表的结构。在这篇文章中，我们将深入探讨MySQL虚拟列的发展、原理以及应用。

一、虚拟列的发展

在早期的MySQL版本中，开发者通常需要为经常需要计算的字段创建额外的物理列，并在数据插入或更新时手动计算这些列的值。这种方法虽然可行，但它增加了数据冗余和应用程序的复杂性。

为了解决这个问题，MySQL 5.7版本引入了虚拟列（也称为生成列）的概念。虚拟列允许开发者在表中定义一个基于其他列的计算公式，而不需要实际存储这些计算的结果。当查询虚拟列时，MySQL会根据公式动态计算其值。

在后续的版本中，MySQL进一步增强了虚拟列的功能，允许开发者选择是否将虚拟列的结果实际存储在磁盘上（即存储列），以提高查询性能。

二、虚拟列的原理

虚拟列的工作原理相对简单。当你在表中定义一个虚拟列时，你需要为其提供一个表达式，该表达式基于表中的其他列。每当查询虚拟列时，MySQL都会根据该表达式动态计算其值。

虚拟列的一个重要特点是它们不占用实际的磁盘空间（除非你选择将它们定义为存储列）。这意味着你可以在不增加存储开销的情况下为表添加额外的计算功能。MySQL 5.7 并且支持两种类型的生成列：

2.1 虚拟生成列（Virtual Generated Column）

虚拟生成列的值是在查询时动态计算的，不会占用额外的磁盘空间来存储这些值。
它们的值是根据列定义中的表达式计算得出的，该表达式可以引用同一表中的其他列。
由于值是动态计算的，因此每次查询虚拟生成列时，MySQL 都会根据相应的表达式重新计算其值。
虚拟生成列可以用于 SELECT 查询的 WHERE 子句、ORDER BY 子句和 GROUP BY 子句等，以提供基于计算的查询条件或排序。

2.2 存储生成列（Stored Generated Column）

存储生成列的值是在数据插入或更新时计算的，并且计算结果会实际存储在磁盘上。
与虚拟生成列不同，存储生成列占用了额外的磁盘空间来存储它们的值。
由于值是预先计算并存储的，因此在查询存储生成列时，MySQL 可以直接读取存储的值，而不需要重新计算。
存储生成列可以用于创建索引，以进一步提高查询性能。索引可以基于存储生成列的值进行快速查找和排序。

三、虚拟列的用法

当你定义一个虚拟列时，你需要使用GENERATED ALWAYS AS语句来指定该列的值是如何从其他列计算得出的。基本的语法如下：

column_name data_type
 [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED]
  • column_name：虚拟列的名称。
  • data_type：虚拟列的数据类型，它必须与你所使用的表达式返回的数据类型兼容。
  • GENERATED ALWAYS：指示该列的值总是由给定的表达式生成。- ALWAYS关键字是可选的，因为默认情况下生成列就是ALWAYS生成的。
  • AS (expression)：指定如何计算虚拟列值的表达式。这个表达式可以引用表中的其他列。
  • VIRTUAL 或 STORED：指定生成列的类型。VIRTUAL表示该列的值在查询时动态计算，而STORED表示该列的值在数据插入或更新时计算并存储。如果你省略这部分，MySQL 5.7及更早版本会默认使用VIRTUAL，而在MySQL 8.0及更高版本中，你需要明确指定VIRTUAL或STORED。

我们创建一个表，其中包含一个JSON列和一个基于JSON列中某个值的虚拟列。然后，我们为这个虚拟列创建索引以提高查询性能。
首先，我们创建一个包含JSON列和虚拟列的表：

CREATE TABLE users (  
  id INT AUTO_INCREMENT PRIMARY KEY,  
  profile JSON,  
  full_name VARCHAR(255) GENERATED ALWAYS AS (  
    CONCAT(  
      JSON_UNQUOTE(JSON_EXTRACT(profile, '$.first_name')), ' ',  
      JSON_UNQUOTE(JSON_EXTRACT(profile, '$.last_name'))  
    )  
  ) VIRTUAL  
);

users 表有一个 profile 列，它的数据类型是 JSON。此外，我们还有一个 full_name 虚拟列，它是通过拼接 profile 列中 first_name 和 last_name 键对应的值生成的。我们使用 JSON_EXTRACT 函数从 profile 列中提取值，并使用 JSON_UNQUOTE 函数将提取出的JSON字符串转换为普通字符串。
接下来，我们为 full_name 虚拟列创建一个索引：

CREATE INDEX idx_full_name ON users(full_name);

-- 插入数据  
INSERT INTO users (profile) VALUES  
('{"first_name": "John", "last_name": "Doe"}'),  
('{"first_name": "Jane", "last_name": "Smith"}');  

-- 查询数据  
SELECT * FROM users WHERE full_name = 'John Doe';

由于我们已经为 full_name 列创建了索引，因此上述查询应该会更快，尤其是当表中有大量数据时。

请注意：由于 full_name 是一个虚拟列，你不能直接更新它的值。如果你需要改变 full_name 的值，你必须更新 profile 列中相应的 first_name 或 last_name 值。

四、虚拟列的使用条件和限制

4.1 使用条件

  • 确定性：生成列的表达式必须是确定性的。这意味着给定相同的输入，表达式必须总是产生相同的结果。例如，使用NOW()函数的表达式就不是确定性的，因为它返回当前的日期和时间。

  • 引用其他列：生成列的表达式可以引用表中的其他列，但这些列必须定义在生成列之前。

  • 数据类型兼容性：生成列的数据类型必须与表达式的结果兼容。例如，如果你将两个整数相加，生成列的数据类型应该是整数类型。

  • 索引限制：只有STORED生成列可以被索引。VIRTUAL生成列在MySQL 5.7及更早版本中不能被索引，但在MySQL 8.0及更高版本中，这个限制已经被放宽，允许对VIRTUAL生成列创建索引。

4.2 限制条件

  • 性能考虑：对于VIRTUAL生成列，每次查询时都需要计算表达式，这可能会影响性能，尤其是当表达式复杂或数据量大时。对于STORED生成列，虽然计算只在数据插入或更新时发生，但它们占用了额外的存储空间。

  • 存储引擎限制：并非所有的MySQL存储引擎都支持生成列。例如，在MySQL 5.7中，只有InnoDB、MyISAM和MEMORY存储引擎支持生成列。

  • 表达式限制：生成列的表达式有一些限制。例如，它们不能引用其他表中的列，不能包含子查询，不能引用非确定性的函数（如RAND()或NOW()），除非这些函数被用作常量值。

  • 修改限制：一旦创建了生成列，就不能直接修改它的值。因为它是根据其他列的值自动生成的。如果你需要改变生成列的值，你必须修改它所依赖的列的值。

  • 复制和二进制日志：如果你的MySQL服务器配置了复制或使用了二进制日志，那么对生成列的更改（通过修改它所依赖的列）也会被记录并复制到其他服务器上。

  • 备份和恢复：在备份和恢复数据库时，需要确保备份工具能够正确处理生成列。一些较旧的备份工具可能不支持生成列。

  • 升级考虑：如果你的数据库是从较旧的MySQL版本升级而来的，需要确保升级过程正确处理了生成列。在升级之前，最好先在测试环境中验证生成列的行为和性能。

五、虚拟列的应用场景

虚拟列在许多场景中都非常有用。以下是一些常见的应用示例：

  • 复杂计算与表达式：当查询中需要频繁进行复杂计算，而这些计算又是基于表中其他字段的时候，虚拟列特别有用。通过将这些计算定义为虚拟列，MySQL可以预先或在查询时计算这些值，从而避免在每次查询时都重复相同的计算。

  • 用作索引：虚拟列可以被索引，这对于提高查询性能非常关键。特别是在处理大型数据集时，如果查询条件涉及到计算密集型操作，将这些操作的结果存储为虚拟列并为其创建索引可以大大加快查询速度。

  • 数据转换与格式化：如果查询中经常需要将数据从一种格式转换为另一种格式（例如日期时间格式、货币格式等），将这些转换定义为虚拟列可以减少每次查询时的转换开销。

  • 统一查询逻辑：在复杂的应用程序中，可能会有多个查询需要执行相同的计算或转换。通过使用虚拟列，可以将这些计算或转换的逻辑封装在表结构中，使得查询更加简洁且易于维护。

  • 避免使用视图：在某些情况下，开发者可能会使用视图来封装复杂的查询逻辑。然而，视图在某些情况下可能不如虚拟列高效。虚拟列允许数据库在物理表层面进行优化，而视图则可能需要在每次查询时动态生成结果集。

  • 减少IO操作：当使用存储虚拟列时（即结果实际存储在磁盘上），由于数据已经预先计算并存储，因此可以减少查询时的IO操作，从而提高查询性能。

  • 与JSON等非标准字段的交互：对于存储了JSON或其他非标准格式数据的字段，直接在这些字段上进行查询可能会非常低效。通过将JSON字段中的值提取为虚拟列，并为其创建索引，可以显著提高对这些数据的查询效率。

总结

MySQL的虚拟列是一个强大而灵活的特性，它允许开发者在表中定义基于其他列的计算结果，而无需实际存储这些计算的值。通过使用虚拟列，你可以避免数据冗余，简化查询，优化索引，执行数据验证以及自动转换数据格式。

随着MySQL的不断发展，我们可以期待虚拟列在未来版本中继续得到增强和优化，为开发者提供更多便利和功能。在设计和优化数据库时，不要忘记考虑使用虚拟列来提高性能和简化应用程序逻辑。

原文链接：https://blog.csdn.net/qq_26664043/article/details/136047875

posted @ 2024-03-19 13:01  JaxYoun  阅读(977)  评论(0)  编辑  收藏  举报