深入探索MySQL的虚拟列:发展、原理与应用

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 深入探索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的不断发展,我们可以期待虚拟列在未来版本中继续得到增强和优化,为开发者提供更多便利和功能。在设计和优化数据库时,不要忘记考虑使用虚拟列来提高性能和简化应用程序逻辑。


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
39
分享
相关文章
RDS用多了,你还知道MySQL主从复制底层原理和实现方案吗?
随着数据量增长和业务扩展,单个数据库难以满足需求,需调整为集群模式以实现负载均衡和读写分离。MySQL主从复制是常见的高可用架构,通过binlog日志同步数据,确保主从数据一致性。本文详细介绍MySQL主从复制原理及配置步骤,包括一主二从集群的搭建过程,帮助读者实现稳定可靠的数据库高可用架构。
20 9
RDS用多了,你还知道MySQL主从复制底层原理和实现方案吗?
MySQL底层概述—6.索引原理
本文详细回顾了:索引原理、二叉查找树、平衡二叉树(AVL树)、红黑树、B-Tree、B+Tree、Hash索引、聚簇索引与非聚簇索引。
MySQL底层概述—6.索引原理
MySQL原理简介—12.MySQL主从同步
本文介绍了四种为MySQL搭建主从复制架构的方法:异步复制、半同步复制、GTID复制和并行复制。异步复制通过配置主库和从库实现简单的主从架构,但存在数据丢失风险;半同步复制确保日志复制到从库后再提交事务,提高了数据安全性;GTID复制简化了配置过程,增强了复制的可靠性和管理性;并行复制通过多线程技术降低主从同步延迟,保证数据一致性。此外,还讨论了如何使用工具监控主从延迟及应对策略,如强制读主库以确保即时读取最新数据。
MySQL原理简介—12.MySQL主从同步
MySQL原理简介—11.优化案例介绍
本文介绍了四个SQL性能优化案例,涵盖不同场景下的问题分析与解决方案: 1. 禁止或改写SQL避免自动半连接优化。 2. 指定索引避免按聚簇索引全表扫描大表。 3. 按聚簇索引扫描小表减少回表次数。 4. 避免产生长事务长时间执行。
MySQL原理简介—10.SQL语句和执行计划
本文介绍了MySQL执行计划的相关概念及其优化方法。首先解释了什么是执行计划,它是SQL语句在查询时如何检索、筛选和排序数据的过程。接着详细描述了执行计划中常见的访问类型,如const、ref、range、index和all等,并分析了它们的性能特点。文中还探讨了多表关联查询的原理及优化策略,包括驱动表和被驱动表的选择。此外,文章讨论了全表扫描和索引的成本计算方法,以及MySQL如何通过成本估算选择最优执行计划。最后,介绍了explain命令的各个参数含义,帮助理解查询优化器的工作机制。通过这些内容,读者可以更好地理解和优化SQL查询性能。
MySQL原理简介—9.MySQL索引原理
本文详细介绍了MySQL索引的设计与使用原则,涵盖磁盘数据页的存储结构、页分裂机制、主键索引设计及查询过程、聚簇索引和二级索引的原理、B+树索引的维护、联合索引的使用规则、SQL排序和分组时如何利用索引、回表查询对性能的影响以及索引覆盖的概念。此外还讨论了索引设计的案例,包括如何处理where筛选和order by排序之间的冲突、低基数字段的处理方式、范围查询字段的位置安排,以及通过辅助索引来优化特定查询场景。总结了设计索引的原则,如尽量包含where、order by、group by中的字段,选择离散度高的字段作为索引,限制索引数量,并针对频繁查询的低基数字段进行特殊处理等。
MySQL原理简介—9.MySQL索引原理
MySQL原理简介—8.MySQL并发事务处理
这段内容深入探讨了SQL语句执行原理、事务并发问题、MySQL事务隔离级别及其实现机制、锁机制以及数据库性能优化等多个方面。
MySQL原理简介—7.redo日志的底层原理
本文介绍了MySQL中redo日志和undo日志的主要内容: 1. redo日志的意义:确保事务提交后数据不丢失,通过记录修改操作并在系统宕机后重做日志恢复数据。 2. redo日志文件构成:记录表空间号、数据页号、偏移量及修改内容。 3. redo日志写入机制:redo日志先写入Redo Log Buffer,再批量刷入磁盘文件,减少随机写以提高性能。 4. Redo Log Buffer解析:描述Redo Log Buffer的内存结构及刷盘时机,如事务提交、Buffer过半或后台线程定时刷新。 5. undo日志原理:用于事务回滚,记录插入、删除和更新前的数据状态,确保事务可完整回滚。
MySQL原理简介—6.简单的生产优化案例
本文介绍了数据库和存储系统的几个主题: 1. **MySQL日志的顺序写和数据文件的随机读指标**:解释了磁盘随机读和顺序写的原理及对数据库性能的影响。 2. **Linux存储系统软件层原理及IO调度优化原理**:解析了Linux存储系统的分层架构,包括VFS、Page Cache、IO调度等,并推荐使用deadline算法优化IO调度。 3. **数据库服务器使用的RAID存储架构**:介绍了RAID技术的基本概念及其如何通过多磁盘阵列提高存储容量和数据冗余性。 4. **数据库Too many connections故障定位**:分析了MySQL连接数限制问题的原因及解决方法。
MySQL进阶突击系列(08)年少不知BufferPool核心原理 | 大哥送来三条大金链子LRU、Flush、Free
本文深入探讨了MySQL中InnoDB存储引擎的buffer pool机制,包括其内存管理、数据页加载与淘汰策略。Buffer pool作为高并发读写的缓存池,默认大小为128MB,通过free链表、flush链表和LRU链表管理数据页的存取与淘汰。其中,改进型LRU链表采用冷热分离设计,确保预读机制不会影响缓存公平性。文章还介绍了缓存数据页的刷盘机制及参数配置,帮助读者理解buffer pool的运行原理,优化MySQL性能。