mysql虚拟列Generated Column

目录​​​​​​​

1、Generated Column简介

生成的列定义具有以下语法：

2、实践

2.1 存储格式为json字段增加索引

2.2 手机号后四位

3、虚拟列索引介绍

3.1 虚拟列索引的限制

3.1.1 Virtal Generated Column

4、阿里云数据库环境是否支持

下期扩展：

1、MySQL InnoDB Cluster

2、MySQL NDB Cluster

参考文档

---

1、Generated Column简介

MySQL 5.7引入Generated Column(生成列、虚拟列、虚拟生成列、索引函数)：根据列定义中包含的表达式计算得出

生成列包含下面两种类型：

Virtual Generated Column（虚拟）：当从表中读取记录时，将动态计算该列。保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上。（MySQL 5.7中默认是Virtual Generated Column）

Stored Generated Column（存储）：当向表中写入新记录时，将计算该列并将其存储为常规列。

virtual生成列比stored生成列更有用，因为一个虚拟的列不占用任何存储空间。你可以使用触发器模拟stored生成列的行为。

用法举例

drop TABLE triangle;
CREATE TABLE triangle
(
    sidea DOUBLE,
    sideb DOUBLE,
    sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);
select * from triangle;
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);

sidec边为虚拟列。插入c的值为动态计算如下图：

一般情况下，都使用Virtual Generated Column，这也是MySQL默认的方式，如果使用Stored Generated Column，前面的建表语句将会是下面这样，即多了一个STORED关键字.

CREATE TABLE `triangle`
(
    `sidea` double DEFAULT NULL,
    `sideb` double DEFAULT NULL,
    `sidec` double GENERATED ALWAYS AS (SQRT(sidea * sidea + sideb * sideb)) STORED
);

生成的列定义具有以下语法：

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

AS (expr) 指示生成列并定义用于计算列值的表达式。AS 可以在前面加上GENERATED ALWAYS以使生成的列的性质更加明确（区分其他列）。

VIRTUALor关键字指示如何存储列值， 这STORED 对列的使用有影响：

• VIRTUAL: 不存储列值，而是在读取行时，在任何 BEFORE触发器之后立即评估。虚拟列不占用存储空间、InnoDB支持虚拟列的二级索引。（默认）
• STORED：在插入或更新行时评估和存储列值。存储列确实需要存储空间并且可以被索引。

允许在同一个表中混合VIRTUAL列 STORED。

生成列的规则和限制：

• 允许使用文字、确定性内置函数和运算符。如果给定表中的相同数据，则函数是确定性的，多次调用产生相同的结果，独立于连接的用户。非确定性且不符合此定义的函数示例：CONNECTION_ID(), CURRENT_USER(), NOW().
• 不允许存储函数和可加载函数。
• 不允许使用存储过程和函数参数。
• 不允许使用变量（系统变量、用户定义变量和存储的程序局部变量）。
• 不允许子查询。
• 生成的列定义可以引用其他生成的列，但只能引用表定义中较早出现的列。生成的列定义可以引用表中的任何基本（非生成）列，无论其定义发生得早还是晚。
• 该AUTO_INCREMENT属性不能在生成的列定义中使用。
• AUTO_INCREMENT列不能用作生成的列定义中的基列 。
• 从 MySQL 5.7.10 开始，如果表达式求值导致截断或向函数提供不正确的输入，则 CREATE TABLE语句以错误终止并且 DDL 操作被拒绝。

生成的列作用：

• 虚拟生成的列可用作简化和统一查询的一种方式。一个复杂的条件可以定义为一个生成的列，并从对表的多个查询中引用，以确保它们都使用完全相同的条件。
• 存储的生成列可以用作复杂条件的物化缓存，这些条件在运行时计算成本很高。
• 生成列可以模拟函数索引：使用生成列定义函数表达式并对其进行索引。这对于处理无法直接索引的类型的列（例如 JSON列）很有用；有关详细示例， 请参阅 索引生成的列以提供 JSON 列索引。对于存储生成的列，这种方法的缺点是值被存储了两次；一次作为生成列的值，一次在索引中。
• 如果生成的列被索引，优化器会识别与列定义匹配的查询表达式，并在查询执行期间适当地使用列中的索引，即使查询不直接按名称引用列。有关详细信息，请参阅 第 8.3.10 节，“优化器使用生成的列索引”。

2、实践

2.1 存储格式为json字段增加索引

之前日志存储都是采用json，因此本次使用的存储json字段相关表

json解析的方法：

-> MySQL 5.7.9 及更高版本支持 该 运算符。->> 从 MySQL 5.7.13 开始支持 该 运算符。

请参阅 -> and ->> 运算符以及 JSON_EXTRACT()and JSON_UNQUOTE()函数的说明

-- json_extract和->>的区别 ，json_extract解析出的带双引号 两种解析中文和数字貌似都需要带双引号
select json_extract(params,'$."联系方式"') as tel from execute_log;
select json_extract(params,'$.name') from execute_log;

SELECT params->>'$."联系方式"' AS tel from execute_log;
-- 英文不用双引号
SELECT params->>'$.name' AS tel from execute_log;

虚拟生成列新增：

-- 删除虚拟列
ALTER TABLE execute_log DROP COLUMN `mobile`;
-- 添加联系方式的虚拟列
alter table execute_log add mobile varchar(20) generated always as (params->>'$."联系方式"') stored after params;
-- 新增索引
alter table execute_log add index idx_mobile(mobile);

执行查询后：

当然在实际使用过程中，索引都是带companyId的

-- 新增公司和手机号索引
alter table execute_log add index idx_company_mobile(company_id,mobile);

2.2 手机号后四位

-- 添加联系方式后4位的虚拟列
alter table test_table add right4Mobile varchar(20) generated always as (RIGHT (mobile,4)) stored after mobile ;
-- 删除虚拟列
ALTER TABLE test_table DROP COLUMN `right4Mobile`;
-- 添加联合索引
alter table test_table add index idx_company_right4Mobile(company_id,right4Mobile);

性能对比 数据集：1004177(百万)

是否添加虚拟列	执行sql	耗时
否	select * from test_table where company_id = 6 and mobile like '%1800';	109 rows retrieved starting from 1 in 5 s 83 ms (execution: 2 s 911 ms, fetching: 2 s 172 ms)
是	select * from test_table where company_id = 6 and right4Mobile = '1800';	109 rows retrieved starting from 1 in 160 ms (execution: 72 ms, fetching: 88 ms)

添加虚拟列过程记录备份：

demo> alter table test_table add right4Mobile varchar(10) generated always as (RIGHT (mobile,4)) after mobile [2022-01-19 20:22:07] 
completed in 3 s 101 ms 
demo> alter table test_table add index idx_company_right4Mobile(company_id,right4Mobile) [2022-01-19 20:22:26] 
completed in 6 s 91 ms 

---

3、虚拟列索引介绍

InnoDB支持虚拟生成列的二级索引。不支持其他索引类型。在虚拟列上定义的二级索引有时称为“虚拟索引”。

二级索引可以在一个或多个虚拟列或虚拟列和常规列的组合或存储的生成列上创建。包含虚拟列的二级索引可以定义为UNIQUE.

在虚拟生成列上创建二级索引时，生成的列值会在索引的记录中具体化。如果索引是 覆盖索引（包括查询检索到的所有列），则从索引结构中的物化值中检索生成的列值，而不是“即时”计算。

When a secondary index is created on a virtual generated column, generated column values are materialized in the records of the index. If the index is a covering index (one that includes all the columns retrieved by a query), generated column values are retrieved from materialized values in the index structure instead of computed “on the fly”.

covering index（不回表）

An index that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, saving disk I/O. InnoDB can apply this optimization technique to more indexes than MyISAM can, because InnoDB secondary indexes also include the primary key columns. InnoDB cannot apply this technique for queries against tables modified by a transaction, until that transaction ends.

Any column index or composite index could act as a covering index, given the right query. Design your indexes and queries to take advantage of this optimization technique wherever possible.

See Also column index, composite index, index, primary key, secondary index.

INSERT由于在和 UPDATE操作 期间实现二级索引记录中的虚拟列值时执行的计算，在虚拟列上使用二级索引时需要考虑额外的写入成本。即使有额外的写入成本，虚拟列上的二级索引也可能比生成的存储列更可取，后者在聚集索引中具体化，从而导致需要更多磁盘空间和内存的更大表。如果未在虚拟列上定义二级索引，则读取会产生额外成本，因为每次检查列的行时都必须计算虚拟列值。

索引虚拟列的值是 MVCC 记录的，以避免在回滚或清除操作期间对生成的列值进行不必要的重新计算。记录值的数据长度受索引键的限制，对于和行格式为 767 字节，对于 和 COMPACT行REDUNDANT格式为 3072 字节。 DYNAMICCOMPRESSED

在虚拟列上添加或删除二级索引是就地操作。（ Adding or dropping a secondary index on a virtual column is an in-place operation.）

在 5.7.16 之前，外键约束不能引用在虚拟生成列上定义的二级索引。

在 MySQL 5.7.13 和更早版本中，InnoDB不允许在索引生成的虚拟列的基列上定义具有级联引用操作的外键约束。MySQL 5.7.14 中取消了此限制。

3.1 虚拟列索引的限制

3.1.1 Virtal Generated Column

• 聚集索引不能包含Virtual generated column

create table t1(a int, b int , c int GENERATED ALWAYS AS (a / b), primary key(c))

[HY000][3106] 'Defining a virtual generated column as primary key' is not supported for generated columns.

-- STORED 可以
create table t1(a int, b int , c int GENERATED ALWAYS AS (a / b) STORED, primary key(c))

completed in 168 ms

• Virtual Generated Column不能作为外键（在 5.7.16 之前，外键约束不能引用在虚拟生成列上定义的二级索引。在 MySQL 5.7.13 和更早版本中，InnoDB不允许在索引生成的虚拟列的基列上定义具有级联引用操作的外键约束。MySQL 5.7.14 中取消了此限制。）

创建generated column(包括virtual generated column 和stored generated column)时不能使用非确定性的（不可重复的）函数，如下curtime()

create table t1(a int, b int , c int GENERATED ALWAYS AS (a / b) STORED, primary key(c));

[HY000][3763] Expression of generated column 'p3' contains a disallowed function: curtime.

ALTER TABLE `t1` ADD p3 DATE GENERATED ALWAYS AS (curtime()) stored; 

[HY000][3763] Expression of generated column 'p3' contains a disallowed function: curtime.

• 不能在Virtual Generated Column上创建全文索引和空间索引(后面版本有望解决)

4、阿里云数据库环境是否支持

下期扩展：

1、MySQL InnoDB Cluster

2、MySQL NDB Cluster

参考文档

1、MySQL :: MySQL 5.7 Reference Manual :: 13.1.18.7 CREATE TABLE and Generated Columns

2、RDS MySQL AliSQL内核小版本发布记录_云数据库 RDS(RDS)-阿里云帮助中心

文章写于2022年01月19日 语雀