mysql-字段-虚拟列(结合使用场景)

金城宇

什么是虚拟列

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

image.png

虚拟列不允许手动输入值

引用:http://t.zoukankan.com/nkefww...
此文提到一个规范,虚拟列通过添加统一前缀v_ ,在团队写作时写入数据时避免sql出现错误,一同记录下。

使用场景

我用到的场景

环境:
mysql5.7
一张表中存在一个表有text字段存储的json数据,此表中多个类型数据进行混合,json数据key大多不一致但是存在相同的值。
比如:json 数据可能是 {"a":"1"} 也可能是 {"b":"2"} ...

问题:
当我读取json中的一个字段进行连接表的时候,耗时很慢,通过ID进行查询500条数据没有连接表的时候200毫秒,连接另一个表进行查询时30秒都没有返回数据。
连接像这样:from table a left join b on a.json ->> '$.id' = b.id

思考为什么会慢

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

怎么优化

我是通过创建虚拟列来实现的,采用VIRTUAL模式

为什么虚拟列会快

官方:虚拟生成的列可以用作简化和统一查询的一种方式。可以将复杂条件定义为生成的列,并从
对表进行多个查询,以确保所有查询都使用完全相同的条件。

猜测:而不管是使用哪种模式,都相当于表中的一个字段,连接表的时候这个字段已经有值了,相当于关联表的时候并没有做运算处理。

image.png

其他场景大多是对索引进行函数操作失效或者同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...

阅读 1.7k
7 声望
0 粉丝
0 条评论
7 声望
0 粉丝
宣传栏