关于mysql:如何利用mysql57提供的虚拟列来提高查询效率

前言

在咱们日常开发过程中，有时候因为对索引列进行函数调用，导致索引生效。举个例子，比方咱们要按月查问记录，而当咱们 表中只存工夫，如果咱们应用如下语句，其中 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