MySQL学习篇:虚拟列(MySQL8)

前言：本次使用虚拟列是因为一个给玩家发送生日礼包的业务需要优化，而player表中的birthday字段为date类型，存储的是yyyy-MM-dd，需要在SQL中使用函数MONTH(birthday)和DAY(birthday)来提取生日的月和日，而使用函数的效率是极低的，需要计算全表的birthday列，而且还会索引失效，会造成严重的性能下降，所以这里引入了虚拟列来存储玩家生日的字符串"MMdd"。

Step1：原SQL分析。

直接上SQL：

SELECT DISTINCT
    player.id,
    player.player_id,
    player.vip_level,
    player.platform_id,
    player.child_game_id,
    player.channel_id,
    player.server_id,
    player.role_id,
    player.role_name
FROM player player
     INNER JOIN dictionary_entity_relation rel ON rel.entity_id = player.id
WHERE MONTH(player.birthday) = #{month}
  AND DAY(player.birthday) = #{day}
  AND player.game_id = #{gameId}

可以看到原SQL中，使用了函数去计算并匹配传入的当日的月和日。

从这里可以看出有以下问题：

1.每一行的数据都会进行函数的计算然后去匹配，所以这里的效率是极低的。

2.还会导致索引失效的问题。

Step2：虚拟列替代函数计算。

在这里，我们可以使用Navicat工具去新增虚拟列。

在虚拟那个勾选框勾选后，该列即是虚拟列。另外，再介绍一下下面的各个特殊属性选项。

一、虚拟类型：分为以下两种

1. STORED：实际存储的列 (占用物理空间)，在 保存/修改 的时候，根据 表达式 及时生成数据，能提升性能。

2. VIRTUAL：真正虚拟的列 (不占用物理空间)，在查询时，根据 表达式 实时生成，占用性能。

二、表达式：虚拟列数据的生成规则 (可以用各种函数，如我这里用到的date_format函数)

三、永远生成：对于MySQL来说，这个选项勾不勾选都没太大的区别，都不会影响虚拟列数据的生成规则和时机。 

---

DDL语句如下：

ALTER TABLE xxx ADD COLUMN `virtual_birthday` varchar(8) GENERATED ALWAYS AS (date_format(`birthday`,'%m%d')) STORED NOT NULL COMMENT '生日虚拟列(格式:MMdd)'

Step3：保存并查看生成的内容。

查询一下birthday和虚拟列virtual_birthday，看看字段情况。

可以看到这里成功根据表达式生成了对应的虚拟列内容。

随着对birthday的修改，virtual_birthday也会随之改变内容。

Step4：更新SQL语句。

根据新字段，我们可以修改我们的SQL语句了。

SELECT DISTINCT
	player.id,
	player.player_id,
	player.vip_level,
	player.platform_id,
	player.child_game_id,
	player.channel_id,
	player.server_id,
	player.role_id,
	player.role_name 
FROM
	player player
	INNER JOIN dictionary_entity_relation rel ON rel.entity_id = player.id 
WHERE
	virtual_birthday = #{birthdayStr}
	AND player.game_id = #{gameId}

可以看到我们第一个条件从两个SQL函数缩短为一个字段的匹配，比起使用函数的方法，可以提高我们的查询性能。