MySQL 虛擬列和虛擬索引的實現(xiàn)

更新時間：2024年08月22日 10:00:15 作者：Q z1997

虛擬列是MySQL 5.7開始引入的新特性,本文主要介紹了MySQL 虛擬列和虛擬索引的實現(xiàn),文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧

是什么

Mysql 5.7 中推出了一個非常實用的功能虛擬列 Generated (Virtual) Columns

在MySQL 5.7中，支持兩種Generated Column，即Virtual Generated Column和Stored Generated Column，前者只將Generated Column保存在數(shù)據(jù)字典中（表的元數(shù)據(jù)），并不會將這一列數(shù)據(jù)持久化到磁盤上；后者會將Generated Column持久化到磁盤上，而不是每次讀取的時候計算所得。很明顯，后者存放了可以通過已有數(shù)據(jù)計算而得的數(shù)據(jù)，需要更多的磁盤空間，與Virtual Column相比并沒有優(yōu)勢，因此，MySQL 5.7中，不指定Generated Column的類型，默認是Virtual Column。

如果需要Stored Generated Golumn的話，可能在Virtual Generated Column上建立索引更加合適

綜上，一般情況下，都使用Virtual Generated Column，這也是MySQL默認的方式

能干嘛

能對 where 條件上使用函數(shù)等原因使索引失效如使用函數(shù) 就可以對這個字段和函數(shù)建立一個虛擬類和虛擬索引提高效率

怎么用

比如是 sys_user 表中 create_time

在這里插入圖片描述

他是一個datetime 類型的
假設我們有一個查詢

SELECT dayofweek(create_time) from  sys_user WHERE dayofweek(create_time) = 1

結(jié)果

在這里插入圖片描述

執(zhí)行計劃

在這里插入圖片描述

如果我們?yōu)閏reate_time 字段建立索引這個查詢語句仍然無法使用索引因為函數(shù) 阻止了索引 (這里就不實驗了啊有興趣可以自己試試肯定沒用)

建立一個虛擬列

在這里插入圖片描述

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

結(jié)果

在這里插入圖片描述