MySQL中的虚拟列（Virtual Columns）：基于函数计算的索引（Functional Index）如何平衡存储与计算成本？ - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL 虚拟列与函数索引：存储与计算的权衡

大家好，今天我们来深入探讨 MySQL 中虚拟列（Virtual Columns）与基于函数计算的索引（Functional Index）这一主题。这个话题的核心在于如何在存储空间和计算资源之间找到一个最佳平衡点，以优化查询性能。

一、虚拟列的概念与类型

虚拟列，顾名思义，并非实际存储在磁盘上的列，而是基于其他列或表达式计算而来的。MySQL 5.7.6 版本开始引入了虚拟列，极大地增强了数据库的灵活性。

虚拟列分为两种类型：

VIRTUAL/GENERATED ALWAYS AS (expression): 每次读取时计算。不占用存储空间，但每次访问都需要进行计算。
STORED/GENERATED ALWAYS AS (expression) STORED: 在数据插入或更新时计算并存储。占用额外的存储空间，但读取速度更快，因为它无需实时计算。

语法示例：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2),
    full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
    bonus DECIMAL(10, 2) AS (salary * 0.10) STORED
);

在这个例子中，full_name 是一个虚拟列，每次查询时计算 first_name 和 last_name 的拼接结果。bonus 是一个存储列，会在数据插入或更新时计算并存储，占用了额外的存储空间。

二、函数索引（Functional Index）

函数索引是指基于函数或表达式创建的索引。它允许我们对计算结果进行索引，而不是直接对原始列进行索引。这在某些情况下可以显著提高查询性能，尤其是在我们需要对列进行转换或计算后再进行过滤时。

语法示例：

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    description TEXT
);

-- 创建基于 LOWER(name) 的索引
CREATE INDEX idx_lower_name ON products ((LOWER(name)));

-- 创建基于 SUBSTRING(description, 1, 100) 的索引
CREATE INDEX idx_description_prefix ON products ((SUBSTRING(description, 1, 100)));

在这个例子中，idx_lower_name 是一个基于 LOWER(name) 函数的索引，它将对产品名称的小写形式进行索引。idx_description_prefix 是一个基于 SUBSTRING(description, 1, 100) 函数的索引，它将对产品描述的前 100 个字符进行索引。

三、存储与计算的权衡

虚拟列和函数索引的核心挑战在于如何在存储空间和计算资源之间找到平衡。

特性	VIRTUAL 列	STORED 列	函数索引
存储空间	不占用	占用	占用，与普通索引类似
计算成本	每次读取都需要计算	只在写入时计算	查询时可能需要计算，取决于查询语句是否能完全利用索引
写入性能	较高	较低，需要计算并存储	较高，除非索引表达式非常复杂
读取性能	较低，需要实时计算	较高，无需实时计算	较高，如果查询能有效利用索引
使用场景	不经常读取，或者计算成本较低的情况	经常读取，或者计算成本较高的情况	需要对计算结果进行过滤或排序的情况
维护成本	较低	较高，需要考虑数据一致性	较高，需要考虑表达式的更新和维护
适用性	可以基于多个列进行计算，灵活性高	可以基于多个列进行计算，灵活性高	主要用于优化特定查询，适用性相对有限

存储成本： STORED 虚拟列和函数索引都会占用额外的存储空间。STORED 虚拟列会存储计算结果，而函数索引会存储索引键。我们需要根据数据量和存储成本来评估是否值得为此付出代价。
计算成本： VIRTUAL 虚拟列每次读取时都需要计算，这会增加 CPU 的负载。函数索引虽然可以加速查询，但如果查询无法有效利用索引，仍然需要进行全表扫描和计算。
写入性能： STORED 虚拟列在数据插入或更新时需要计算并存储，这会降低写入性能。函数索引在数据修改时也需要更新索引，也会对写入性能产生影响。
读取性能： STORED 虚拟列和函数索引可以显著提高读取性能，因为它们可以避免实时计算。VIRTUAL 虚拟列则会降低读取性能，因为它需要每次读取都进行计算。

四、选择策略

选择使用 VIRTUAL 列、STORED 列还是函数索引，需要综合考虑以下因素：

查询频率： 如果某个计算结果经常被查询，那么使用 STORED 列或函数索引可以提高读取性能。
计算复杂度： 如果计算非常复杂，那么使用 STORED 列可以避免重复计算，降低 CPU 负载。
数据量： 如果数据量很大，那么存储成本可能会成为一个重要的考虑因素。
写入频率： 如果写入频率很高，那么使用 STORED 列可能会降低写入性能。
查询模式： 函数索引只对特定的查询模式有效。我们需要仔细分析查询模式，确定是否可以使用函数索引来优化查询。

五、案例分析

接下来，我们通过几个案例来演示如何选择合适的方案。

案例 1：用户登录日志分析

假设我们有一个用户登录日志表 login_logs，包含以下字段：

id (INT, PRIMARY KEY)
user_id (INT)
login_time (DATETIME)
ip_address (VARCHAR(50))

我们需要分析每个用户的登录次数，并按月份进行分组。

方案 1：使用 VIRTUAL 列

ALTER TABLE login_logs ADD COLUMN login_month INT AS (MONTH(login_time)) VIRTUAL;

SELECT user_id, login_month, COUNT(*) AS login_count
FROM login_logs
GROUP BY user_id, login_month;

这个方案的优点是不占用额外的存储空间，但每次查询都需要计算 login_month。如果查询频率很高，可能会影响性能。

方案 2：使用 STORED 列

ALTER TABLE login_logs ADD COLUMN login_month INT AS (MONTH(login_time)) STORED;

SELECT user_id, login_month, COUNT(*) AS login_count
FROM login_logs
GROUP BY user_id, login_month;

这个方案的优点是读取性能高，因为 login_month 已经预先计算并存储。缺点是占用额外的存储空间，并且在数据插入或更新时需要进行计算。

方案 3：使用函数索引

CREATE INDEX idx_login_month ON login_logs ((MONTH(login_time)));

SELECT user_id, MONTH(login_time) AS login_month, COUNT(*) AS login_count
FROM login_logs
GROUP BY user_id, MONTH(login_time);

这个方案的优点是可以利用索引加速查询，避免全表扫描。缺点是只对特定的查询模式有效，并且需要考虑索引的维护成本。

选择：

如果查询频率很高，并且数据量不是很大，那么使用 STORED 列 是一个不错的选择。
如果查询频率不是很高，或者数据量很大，那么使用 函数索引 可以节省存储空间，并且仍然可以提高查询性能。
如果查询频率很低，或者计算成本很低，那么使用 VIRTUAL 列 也是一个可行的选择。

案例 2：产品搜索

假设我们有一个产品表 products，包含以下字段：

id (INT, PRIMARY KEY)
name (VARCHAR(255))
description (TEXT)

我们需要支持用户搜索产品，并且忽略大小写。

方案 1：使用 VIRTUAL 列

ALTER TABLE products ADD COLUMN lower_name VARCHAR(255) AS (LOWER(name)) VIRTUAL;

SELECT *
FROM products
WHERE lower_name LIKE '%keyword%';

这个方案的优点是不占用额外的存储空间，但每次查询都需要计算 lower_name。由于 LIKE 操作无法有效利用索引，因此查询性能可能较差。

方案 2：使用函数索引

CREATE INDEX idx_lower_name ON products ((LOWER(name)));

SELECT *
FROM products
WHERE LOWER(name) LIKE '%keyword%';

这个方案的优点是可以利用索引加速查询，避免全表扫描。但需要注意的是，前缀模糊查询（LIKE 'keyword%'）可以有效利用索引，但后缀模糊查询（LIKE '%keyword'）或中缀模糊查询（LIKE '%keyword%'）无法有效利用索引。

选择：

如果需要支持忽略大小写的搜索，并且主要是前缀模糊查询，那么使用 函数索引 是一个不错的选择。
如果需要支持后缀或中缀模糊查询，那么可能需要考虑其他方案，例如使用全文索引。

六、代码示例：性能测试

为了更直观地了解不同方案的性能差异，我们可以进行一些简单的性能测试。

-- 创建测试表
CREATE TABLE test_table (
    id INT PRIMARY KEY,
    data VARCHAR(255)
);

-- 插入 100 万条数据
INSERT INTO test_table (id, data)
SELECT i, REPEAT('a', 200)
FROM (SELECT a.N + b.N * 10 + c.N * 100 + d.N * 1000 + e.N * 10000 + f.N * 100000 AS i
      FROM (SELECT 1 AS N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0) AS a
           ,(SELECT 1 AS N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0) AS b
           ,(SELECT 1 AS N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0) AS c
           ,(SELECT 1 AS N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0) AS d
           ,(SELECT 1 AS N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0) AS e
           ,(SELECT 1 AS N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0) AS f
     ) AS numbers
WHERE i <= 1000000;

-- 测试 VIRTUAL 列
ALTER TABLE test_table ADD COLUMN data_length INT AS (LENGTH(data)) VIRTUAL;

-- 测试 STORED 列
ALTER TABLE test_table ADD COLUMN data_length_stored INT AS (LENGTH(data)) STORED;

-- 测试函数索引
CREATE INDEX idx_data_length ON test_table ((LENGTH(data)));

-- 查询 VIRTUAL 列
SELECT * FROM test_table WHERE data_length > 190;

-- 查询 STORED 列
SELECT * FROM test_table WHERE data_length_stored > 190;

-- 查询函数索引
SELECT * FROM test_table WHERE LENGTH(data) > 190;

-- 记录每个查询的执行时间，并进行比较

通过运行这些测试，我们可以观察到不同方案在不同场景下的性能差异。请注意，实际性能会受到硬件配置、MySQL 版本、数据分布等多种因素的影响，因此需要在实际环境中进行测试和评估。

七、注意事项

表达式的复杂性： 虚拟列和函数索引的表达式不宜过于复杂，否则会影响性能。
数据类型： 虚拟列的数据类型需要与表达式的结果类型一致。
索引维护： 函数索引需要定期维护，以确保索引的有效性。
版本兼容性： 虚拟列和函数索引是 MySQL 5.7.6 版本之后引入的特性，需要确保 MySQL 版本符合要求。
权限： 创建和修改虚拟列和函数索引需要相应的权限。

八、选择合适的方案并持续优化

虚拟列和函数索引是强大的工具，可以帮助我们优化查询性能，但需要谨慎使用。我们需要仔细分析业务需求，选择合适的方案，并持续进行优化，以达到最佳的存储和计算平衡。

九、存储、计算与查询性能的平衡点

总而言之，在MySQL中，虚拟列和函数索引的使用需要在存储成本、计算成本和查询性能之间找到一个合适的平衡点。根据实际的应用场景和数据特点，选择最合适的方案，才能有效地提升数据库的性能和效率。

MySQL 虚拟列与函数索引：存储与计算的权衡

发表回复 取消回复

发表回复取消回复