当前位置：首页 > article >正文

MySQL5.7-虚拟列

article 2024/11/13 13:07:57

虚拟列的定义和作用

虚拟列和普通列的区别

虚拟列的类型

生成列

语法

存储列

语法

实战：使用MySQL虚拟列解决实际问题

场景描述

表结构

插入数据

查询订单总金额

性能优化

虚拟列使用限制

虚拟列的定义和作用

MySQL虚拟列是一种特殊的列，是mysql-5.7版本引入的一个新特性，它并不存储数据，而是在查询时动态生成数据。这种列的值通常是由其他列的值通过某种表达式计算得出的。虚拟列的主要作用是提高查询效率和数据处理的便利性。它可以使我们在不增加实际存储开销的情况下，对数据进行更高效的查询和管理。

虚拟列和普通列的区别

虚拟列与普通列在使用上有几个主要的区别：

存储方式：普通列的值在插入或更新时会被实际存储在数据库中，而虚拟列的值并不会被存储，而是在查询时动态计算生成。
更新方式：普通列的值可以直接通过UPDATE语句进行修改，而虚拟列的值则不能直接修改，它的值是由定义它的表达式决定的。
索引应用：虚拟列可以被索引，这使得它在某些情况下可以提高查询性能。虽然虚拟列的值在查询时才生成，但是如果对虚拟列创建了索引，那么索引的值会被存储，从而提高查询效率。

虚拟列的类型

在MySQL中，虚拟列主要分为两种类型：生成列和存储列。

STORED：实际存储的列 (占用物理空间)，在保存/修改的时候，根据表达式及时生成数据，能提升性能。
VIRTUAL：真正虚拟的列 (不占用物理空间)，在查询时，根据表达式实时生成，占用性能。

生成列

生成列是一种特殊的虚拟列，它的值是由其他列的值通过一个表达式生成的。生成列的值不会被实际存储，而是在查询时动态计算生成。生成列可以是基于一个或多个列的任何MySQL合法的表达式

语法

ALTER TABLE users
ADD COLUMN full_name VARCHAR(255) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL;

存储列

存储列是生成列的一种特殊形式。与生成列不同，存储列的值在插入或更新数据时会被计算并实际存储在数据库中。这意味着存储列的值不需要在查询时动态计算。

虽然存储列需要占用额外的存储空间，但是它可以提高查询速度，因为它的值在查询时已经被计算并存储好了。存储列特别适用于那些计算成本高，但查询频繁的场景。

需要注意的是，虽然存储列的值被存储在数据库中，但是它的值不能直接被修改，它的值仍然是由定义它的表达式决定的。

语法

ALTER TABLE users
ADD COLUMN full_name VARCHAR(255) AS (CONCAT(first_name, ' ', last_name)) STORED;

实战：使用MySQL虚拟列解决实际问题

场景描述

假设你是一家电子商务公司的数据库管理员，公司有一个 orders 表，记录了客户的订单信息。表结构如下：

order_id：订单的唯一标识。
product_id：产品的唯一标识。
quantity：购买的产品数量。
unit_price：产品的单价。
order_date：订单日期。

现在，公司需要快速生成每个订单的总金额报告，这是一个经常需要执行的查询。为了提高查询效率，你决定使用虚拟列来存储每个订单的总金额。

表结构

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * unit_price) VIRTUAL
) ENGINE=InnoDB;

在这个表结构中，total_amount 是一个虚拟列，它的值是 quantity 和 unit_price 相乘的结果。

插入数据

INSERT INTO orders (product_id, quantity, unit_price, order_date)
VALUES
(101, 2, 29.99, '2024-01-01'),
(102, 1, 49.99, '2024-01-02'),
(103, 3, 19.99, '2024-01-03');

查询订单总金额

在没有虚拟列的情况下，你需要执行一个包含计算的查询来获取每个订单的总金额：

SELECT order_id, quantity, unit_price, quantity * unit_price AS total_amount
FROM orders;

但是，通过使用虚拟列，你可以简化查询，并可能提高性能：

SELECT order_id, total_amount
FROM orders;

性能优化

由于 total_amount 是一个虚拟列，MySQL 会在查询时动态计算其值。如果你发现这个计算成为了性能瓶颈，你可以将虚拟列改为存储列（必须先删除后创建）：

ALTER TABLE orders DROP COLUMN total_amount;
ALTER TABLE orders ADD COLUMN total_amount DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED;

这样，MySQL 会在插入或更新行时计算 total_amount 并将其存储在表中。由于值已经预先计算并存储，查询时可以直接读取，而不需要每次都进行计算。

虚拟列使用限制

虚拟列在查询时动态计算，不占用磁盘空间，因此不会增加存储负担。以下是虚拟列的一些限制和注意事项：

只读属性：虚拟列是只读的，你不能直接插入或更新虚拟列的值。如果需要修改虚拟列的值，必须修改其依赖的基础列的值。
表达式限制：虚拟列的表达式必须使用确定性的函数和操作符。不能使用非确定性的函数，如 NOW()、RAND() 等，也不能使用子查询或存储过程。
索引限制：在 MySQL 5.7 及更早版本中，虚拟列不能被索引。但从 MySQL 8.0 开始，你可以在虚拟列上创建索引，这有助于提高查询性能。
存储引擎限制：虚拟列仅在某些存储引擎中受支持。例如，在 MySQL 5.7 中，只有 InnoDB、MyISAM 和 MEMORY 存储引擎支持虚拟列。
修改限制：虚拟列的定义可以修改，但是虚拟列和存储列之间不能相互转换。如果需要更改列的存储方式，必须先删除列，然后重新添加。
性能考虑：虽然虚拟列不占用存储空间，但每次查询时都需要计算其值，这可能会影响性能。特别是当表达式复杂或数据量大时，这种影响更为明显。
数据类型兼容性：虚拟列的数据类型必须与表达式的结果兼容。例如，如果你将两个整数相加，虚拟列的数据类型应该是整数类型。
复制和二进制日志：在使用复制或二进制日志时，对虚拟列的更改（通过修改其依赖的基础列）也会被记录并复制到其他服务器上。
备份和恢复：在备份和恢复数据库时，需要确保备份工具能够正确处理虚拟列。
升级考虑：如果你的数据库是从较旧的 MySQL 版本升级而来的，需要确保升级过程正确处理了虚拟列。