mysql-字段-虚拟列（结合使用场景）

站长

2022年09月17日 10:12 · 阅读数 44

什么是虚拟列

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

mysql-字段-虚拟列（结合使用场景）

虚拟列不允许手动输入值

引用：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模式

为什么虚拟列会快

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

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

mysql-字段-虚拟列（结合使用场景）

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