MySQL 虚拟列和虚拟索引的实现_Mysql

是什么

mysql 5.7 中推出了一个非常实用的功能虚拟列 generated (virtual) columns

在mysql 5.7中，支持两种generated column，即virtual generated column和stored generated column，前者只将generated column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将generated column持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与virtual column相比并没有优势，因此，mysql 5.7中，不指定generated column的类型，默认是virtual column。

如果需要stored generated golumn的话，可能在virtual generated column上建立索引更加合适

综上，一般情况下，都使用virtual generated column，这也是mysql默认的方式

能干嘛

能对 where 条件上使用函数等原因使索引失效如使用函数就可以对这个字段和函数建立一个虚拟类和虚拟索引提高效率

怎么用

比如是 sys_user 表中 create_time

在这里插入图片描述

他是一个datetime 类型的
假设我们有一个查询

select dayofweek(create_time) from  sys_user where dayofweek(create_time) = 1

结果

在这里插入图片描述

执行计划

在这里插入图片描述

如果我们为create_time 字段建立索引这个查询语句仍然无法使用索引因为函数阻止了索引 (这里就不实验了啊有兴趣可以自己试试肯定没用)

建立一个虚拟列

在这里插入图片描述

alter table `qzboot`.`sys_user` 
add column `create_time_dayofweek` tinyint(4)  generated always as (dayofweek(create_time)) virtual ;

新建一个虚拟索引

在这里插入图片描述

alter table `qzboot`.`sys_user` 
add index `test`(`create_time_dayofweek`) using btree comment '虚拟索引';

select dayofweek(create_time) from  sys_user where dayofweek(create_time) = 1

#  sql 改造
select create_time_dayofweek from  sys_user where create_time_dayofweek = 1

结果

在这里插入图片描述