MySQL之虚拟列

从MySQL 5.7开始，支持两种Generated Column，即Virtual Generated Column和Stored Generated Column;

区别

1. Virtual Generated Column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；
2. Stored Generated Column持久化到磁盘上，而不是每次读取的时候计算所得

创建语法

CREATE TABLE t1 (c1 INT);

ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;

CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);

使用限制：

1、主键索引不能包含virtual generated column 如：

mysql> create table t(a int, b int , c int as (a / b), primary key(c));
ERROR 3106 (HY000): 'Defining a virtual generated column as primary key' is not supported for generated columns.

2、Virtual Generated Column不能作为外键
3、不能使用非确定函数，如：
mysql> alter table a ADD p3 DATE GENERATED ALWAYS AS (curtime()) virtual;
ERROR 3763 (HY000): Expression of generated column 'p3' contains a disallowed function: curtime.
4、无法删除源列，如：
mysql> alter table t100w drop k1;
ERROR 3108 (HY000): Column 'k1' has a generated column dependency.

5、非法数据，如：
mysql> create table t( x int, y int, z int as( x / 0));
Query OK, 0 rows affected (0.22 sec)
mysql> insert into t(x,y) values(1,1);
ERROR 1365 (22012): Division by 0

测试100w表并发50执行100次全表扫描DB性能表现

测试sql:
SELECT test_vv FROM t100w limit 10000; #虚拟列无索引
虚拟列函数：
(concat(k1,_utf8mb4'-',k2))

stored类型

• 执行耗时

  
  stored类型-耗时

• CPU负载

  
  stored类型-cpu负载

• qps

  
  stored类型-qps

virtual 类型

virtual 类型-耗时
virtual 类型-cpu负载
virtual 类型-qps

说明

• 以上测试表结果集较小，且虚拟列计算函数计算较为简单。可以大致参考比较出Virtual Generated Column会有一定的CPU开销（函数列需要计算结果集）。
• 对于不是很核心的业务，函数模型不复杂的，复杂低频业务建议使用Virtual Generated Column。
• 添加Stored Generated Column列不是online操作，代价较大且需要额外的存储成本。