虚拟列 Generated (Virtual) Columns
对于它的用途,我们通过一个场景来说明
案例:
假设有一个表,其中包含一个 date 类型的列
SimpleDate
date
SimpleDate 是一个常用的查询字段,并需要对其执行日期函数,例如
SELECT … WHERE dayofweek(SimpleDate) = 3 …
此时的问题是 即使对 SimpleDate 建立索引,这个查询语句也无法使用,因为日期函数阻止了索引
为了提高查询效率,通常要进行额外的操作,例如新建一个字段 SimpleDate_dayofweek,存放 dayofweek(SimpleDate) 的计算结果,然后对这列创建索引
SimpleDate_dayofweek 的值需要程序写入,例如使用触发器,在 SimpleDate 有变动时更新
这样查询就可以改为
SELECT … WHERE SimpleDate_dayofweek = 3 …
这么做的好处是提高了查询性能,可以使用 SimpleDate_dayofweek 列的索引了,但又带来了其他麻烦,例如
(1)降低了数据写入性能
(2)增加冗余数据,占用了更多的存储空间
(3)增加代码维护成本
进一步修改
虚拟列 Generated Columns 就是用来解决这个问题的,可以增加一个可被索引的列,但实际上并不存在于数据表中
对于上面的例子,可以对 SimpleDate 创建一个虚拟列,然后对虚拟列创建索引,如
CREATE TABLE test
(
id
int(11) NOT NULL AUTO_INCREMENT,
SimpleDate
date DEFAULT NULL,
…
SimpleDate_dayofweek
tinyint(4) GENERATED ALWAYS AS (dayofweek(SimpleDate)) VIRTUAL,
PRIMARY KEY (id
),
KEY SimpleDate_dayofweek
(SimpleDate_dayofweek
),
) ENGINE=InnoDB
这样就建好了虚拟列,此列的值不用我们计算
SELECT … WHERE SimpleDate_dayofweek = 3 …
查询语句可以正常使用索引
通过虚拟列的方式,即满足了查询性能,也不会有之前那个解决方案的潜在麻烦
虚拟列不存储在数据行中,但虚拟列的元数据信息会存在于相关系统表中,对虚拟列的添加或者删除只会涉及这些系统表,不会导致数据表的重建,所以效率很高
需要注意,不能建立虚拟列和真实列的联合索引
也可参考:虚拟列的实现
转载:https://blog.csdn.net/wfg18801733667/article/details/52217634?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522160402557719724813255897%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=160402557719724813255897&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2allfirst_rank_v2~rank_v28-1-52217634.pc_first_rank_v2_rank_v28&utm_term=%E8%99%9A%E6%8B%9F%E5%88%97%E4%BD%9C%E7%94%A8%E6%98%AF%E4%BB%80%E4%B9%88&spm=1018.2118.3001.4449