关于mysql:mysql字段虚拟列结合使用场景

什么是虚构列

创立字段时应用 [GENERATED ALWAYS] AS (expr) 通过表达式来生成的字段。虚构列依据是否进行物理存储分为两种类型（VIRTUAL）和（STORED）。STORED 模式在新增和批改时会进行批改和存储。VIRTUAL 模式在读取数据时进行计算（在 BEFORE 触发器后执行）。虚构列如不指定默认为 VIRTUAL 模式。

虚构列不容许手动输出值

援用：http://t.zoukankan.com/nkefww…
此文提到一个标准，虚构列通过增加对立前缀 v_ , 在团队写作时写入数据时防止 sql 呈现谬误，一起记录下。

应用场景

我用到的场景

环境：
mysql5.7
一张表中存在一个表有 text 字段存储的 json 数据，此表中多个类型数据进行混合，json 数据 key 大多不统一然而存在雷同的值。
比方：json 数据可能是 {“a”:”1″} 也可能是 {“b”:”2″} …

问题：
当我读取 json 中的一个字段进行连贯表的时候，耗时很慢，没有连贯表的时候 200 毫秒查问 500 条数据。
连贯像这样：from table a left join b on a.json ->> ‘$.id’ = b.id

思考为什么会慢

我连贯这个表的时候，须要将文本的字段转换成 json 对象并进行连贯，相当于连贯之前做了一次运算，并且 json 的字段不能够走索引。

怎么优化

我是通过创立虚构列来实现的，采纳 VIRTUAL 模式

为什么虚构列会快

官网：虚构生成的列能够用作简化和对立查问的一种形式。能够将简单条件定义为生成的列，并从
对表进行多个查问，以确保所有查问都应用完全相同的条件。

猜想：而不论是应用哪种模式，都相当于表中的一个字段，相当于把计算过程提前到读取数据，将读取出的值进行连贯表或者其余操作。

其余场景大多是对索引进行函数操作生效或者同 json 数据的操作

怎么创立虚构列

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

example：

create table test_virual
(
    id   int auto_increment primary key,
    name varchar(255),
    age  int
);

# 创立虚构列 将 name 和 age 字段进行拼接
alter table test_virual add column name_age varchar(300) GENERATED ALWAYS AS (concat(name,age)) virtual;

# 插入数据
insert into  test_virual(name, age) values ('tom',13),('rose',16),('jack',19);

# 查问数据
select * from test_virual;

+--+----+---+--------+
|id|name|age|name_age|
+--+----+---+--------+
|1 |tom |13 |tom13   |
|2 |rose|16 |rose16  |
|3 |jack|19 |jack19  |
+--+----+---+--------+

# 验证 virtual 是否反对索引
create index v_idx on test_virual(name_age);
explain select * from test_virual where name_age like 't%';

+--+-----------+-----------+----------+-----+-------------+-----+-------+----+----+--------+-----------+
|id|select_type|table      |partitions|type |possible_keys|key  |key_len|ref |rows|filtered|Extra      |
+--+-----------+-----------+----------+-----+-------------+-----+-------+----+----+--------+-----------+
|1 |SIMPLE     |test_virual|NULL      |range|v_idx        |v_idx|1203   |NULL|1   |100     |Using where|
+--+-----------+-----------+----------+-----+-------------+-----+-------+----+----+--------+-----------+


# 验证 virtual 是否反对联结索引
create index v_idx_combo on test_virual(name_age,age);
explain select * from test_virual where name_age like 't%' and age = 13;

+--+-----------+-----------+----------+-----+-------------+-----------+-------+----+----+--------+-----------+
|id|select_type|table      |partitions|type |possible_keys|key        |key_len|ref |rows|filtered|Extra      |
+--+-----------+-----------+----------+-----+-------------+-----------+-------+----+----+--------+-----------+
|1 |SIMPLE     |test_virual|NULL      |range|v_idx_combo  |v_idx_combo|1208   |NULL|1   |33.33   |Using where|
+--+-----------+-----------+----------+-----+-------------+-----------+-------+----+----+--------+-----------+

参考：
mysql 创立表和字段
https://dev.mysql.com/doc/ref…
mysql 创立辅助索引
https://dev.mysql.com/doc/ref…