如何利用mysql5.7提供的虛擬列來提高查詢效率

linyb極客之路發表於2022-07-13
MySql

前言

在我們日常開發過程中，有時候因為對索引列進行函式呼叫，導致索引失效。舉個例子，比如我們要按月查詢記錄，而當我們 表中只存時間，如果我們使用如下語句，其中create_time為索引列

select count(*) from user where MONTH(create_time) = 5

雖然可能查到正確的結果，但通過explain我們會發現沒走索引。因此我們為了能確保使用索引，我們可能會改成

select count(*) from user where create_time BETWEEN '2022-05-01' AND '2022-06-01';

或者乾脆在資料庫表中冗餘一個月份的列欄位，並對這個月份建立索引。如果我們使用的mysql是5.7版本，我們則可以使用mysql5.7版本提供的一個新特性--虛擬列來達到上述效果

虛擬列

在mysql5.7支援2種虛擬列virtual columns 和 stored columns 。兩者的區別是virtual 只是在讀行的時候計算結果，但在物理上是不儲存，因此不佔儲存空間，且僅在InnoDB引擎上建二級索引，而stored 則是當行資料進行插入或更新時計算並儲存的，是需要佔用物理空間的，支援在MyISAM和InnoDB引擎建立索引

mysql5.7 預設的虛擬列型別為virtual columns

1、建立虛擬列語法
ALTER TABLE 表名稱 add column 虛擬列名稱 虛擬列型別 [GENERATED ALWAYS] as (表示式) [VIRTUAL | STORED];
2、使用虛擬列注意事項

a、衍生列的定義可以修改，但virtual和stored之間不能相互轉換，必要時需要刪除重建

b、虛擬列欄位只讀，不支援 INSRET 和 UPDATE

c、只能引用本表的非 generated column 欄位，不可以引用其它表的欄位

d、使用的表示式和操作符必須是 Immutable 屬性，比如不能使用 CONNECTION_ID(), CURRENT_USER(), NOW()

e、可以將已存在的普通列轉化為stored型別的衍生列，但virtual型別不行；同樣的，可以將stored型別的衍生列轉化為普通列，但virtual型別的不行

f、虛擬列定義不允許使用自增 (AUTO_INCREMENT)，也不允許使用自增基列

g、虛擬列允許修改表示式，但不允許修改儲存方式（只能通過刪除重新建立來修改）

h、如果虛擬列用作索引，會有一個缺點值會儲存兩次。一次用作虛擬列的值，一次用作索引中的值

3、虛擬列的使用場景

a、虛擬列可以簡化和統一查詢，將複雜條件定義為生成的列，可以在查詢時直接使用虛擬列（代替檢視）

b、儲存虛擬列可以用作例項化快取，以用於動態計算成本高昂的複雜條件

c、虛擬列可以模擬功能索引，並且可以使用索引，這對與無法直接使用索引的列（JSON 列）非常有用。

示例

因為mysql5.7也支援json列，因此本示例就以json和虛擬列為例子演示一下示例

1、建立示例表
CREATE TABLE `t_user_json` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_info` json DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2、建立虛擬列

注： 虛擬列可以在建表語句時候，直接建立即可。本示例是為了突出虛擬列語法

ALTER TABLE t_user_json ADD COLUMN v_user_name VARCHAR(50) GENERATED ALWAYS AS (JSON_UNQUOTE(json_extract(user_info,'$.username')));

正常我們的json語句如下

{"age": 23, "email": "likairui@qq.com", "mobile": "89136682644", "fullname": "李凱瑞", "username": "likairui"}

我們通過JSON_UNQUOTE來去除雙引號，否則到時候生成的虛擬列v_user_name 的值會變成"likairui"，而實際我們需要的欄位值應該likairui

因為mysql5.7的json不是本文的重點，本文就不論述了，如果對mysql5.7 json語法函式感興趣的朋友可以檢視如下連結

https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

3、為虛擬列建立索引
ALTER TABLE t_user_json ADD INDEX idx_v_user_name(v_user_name);
4、檢視生成的表資料

5、檢視是否使用了索引
EXPLAIN  SELECT  id,user_info,create_time,v_user_name AS username,v_date_month AS MONTH  FROM t_user_json     WHERE (v_user_name = 'likairui')


注： 在mysql8.0版本可以使用EXPLAIN ANALYZE，他可以檢視sql的耗時情況

EXPLAIN ANALYZE SELECT  id,user_info,create_time,v_user_name AS username,v_date_month AS MONTH  FROM t_user_json     WHERE (v_user_name = 'cengwen')

6、程式碼層面的小細節

因為虛擬列是不能進行插入和更新的，因此使用orm框架的時候，要特別注意這點。比如使用mybatis-plus時，要記得在實體的虛擬列的對映欄位上加上如下註解

 @TableField(value = "v_user_name",insertStrategy = FieldStrategy.NEVER,updateStrategy = FieldStrategy.NEVER)
    private String username;

加上這個註解後，虛擬列欄位就不會進行更新或者插入

總結

本文基於mysql5.7大體介紹了一下虛擬列，如果是使用mysql8.0.13以上的版本，可以函式索引，他的實現方式本質也是基於虛擬列實現。所謂的函式索引就是在建立索引的時候，支援使用函式表示式。比如

ALTER TABLE user ADD INDEX((MONTH(create_time)));

通過函式索引也可以很方便提高我們的查詢效率。具體使用可以檢視如下連結

https://dev.mysql.com/doc/refman/8.0/en/create-index.html

demo連結

https://github.com/lyb-geek/springboot-learning/tree/master/springboot-mysql-virtual-column

相關文章