How to improve query efficiency by using virtual columns provided by mysql5.7

Preface

In our daily development process , Sometimes because of function calls to index columns , Cause index to fail . for instance , For example, we need to query records by month , And when we Only time is saved in the table , If we use the following statement , among create_time Index column

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

Although the correct results may be found , But through explain We will find that the index did not go . Therefore, in order to ensure the use of indexes , We may change it to

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

Or simply redundant column fields of one month in the database table , And create an index for this month . If we use mysql yes 5.7 edition , We can use mysql5.7 Version provides a new feature -- Virtual column To achieve the above effect

Virtual column

stay mysql5.7 Support 2 Two virtual columns virtual columns and stored columns . The difference between the two virtual Just calculate the result when reading the line , But physically, there is no storage , Therefore, it does not occupy storage space , And only in InnoDB Build a secondary index on the engine , and stored It is calculated and stored when the row data is inserted or updated , It needs to occupy physical space , Support in MyISAM and InnoDB The engine creates indexes

mysql5.7 The default virtual column type is virtual columns

1、 Create virtual column syntax

ALTER TABLE  The name of the table  add column  Virtual column name   Virtual column type  [GENERATED ALWAYS] as ( expression ) [VIRTUAL | STORED];

2、 Considerations for using virtual columns

a、 The definition of derived columns can be modified , but virtual and stored Can't be converted to each other , Delete and rebuild if necessary

b、 Virtual column fields are read-only , I won't support it INSRET and UPDATE

c、 Only non generated column Field , Fields of other tables cannot be referenced

d、 The expressions and operators used must be Immutable attribute , For example, you can't use CONNECTION_ID(), CURRENT_USER(), NOW()

e、 You can convert existing ordinary columns into stored Derived column of type , but virtual No type ; alike , Can be stored The derived column of type is converted to ordinary column , but virtual Not of type

f、 Auto increment is not allowed for virtual column definitions (AUTO_INCREMENT), It is also not allowed to use self incrementing base columns

g、 Virtual columns allow you to modify expressions , However, it is not allowed to modify the storage method （ Can only be modified by deleting and recreating ）

h、 If the virtual column is used as an index , There will be a disadvantage value that will be stored twice . Once used as the value of the virtual column , Used once as a value in the index

3、 Usage scenarios of virtual columns

a、 Virtual columns can simplify and unify queries , Define complex conditions as generated columns , You can use virtual columns directly when querying （ Instead of view ）

b、 Storing virtual columns can be used as instantiation caches , For complex conditions with high cost of dynamic computing

c、 Virtual columns can simulate functional indexes , And you can use indexes , This pair is related to columns that cannot be indexed directly （JSON Column ） Very useful .

Example

because mysql5.7 Also support json Column , Therefore, this example takes json And virtual column as examples to demonstrate the example

1、 Create a sample table

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、 Create virtual columns

notes ： Virtual columns can be used when creating table statements , Create it directly . This example is to highlight the virtual column syntax

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

It's normal for us json The statement is as follows

{"age": 23, "email": "[email protected]", "mobile": "89136682644", "fullname": " Li Kairui ", "username": "likairui"}

We go through JSON_UNQUOTE To remove double quotes , Otherwise, the virtual column generated at that time v_user_name The value of will become "likairui", In fact, the field value we need should likairui

because mysql5.7 Of json Not the focus of this article , This article will not discuss , If the mysql5.7 json Grammar function interested friends can see the following links

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

3、 Create indexes for virtual columns

ALTER TABLE t_user_json ADD INDEX idx_v_user_name(v_user_name);

4、 View the generated table data

5、 See if the index is used

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')

notes ： stay mysql8.0 Version can be used EXPLAIN ANALYZE, He can check sql Time consuming

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、 Small details at the code level

Because virtual columns cannot be inserted and updated , Therefore use orm Frame time , Pay special attention to this . For example, use mybatis-plus when , Remember to add the following annotation to the mapping field of the virtual column of the entity

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

After adding this note , Virtual column fields will not be updated or inserted

summary

This article is based on mysql5.7 A general introduction to virtual columns , If using mysql8.0.13 Version above , You can use functional indexes , The essence of its implementation is also based on virtual columns . The so-called functional index is when creating an index , Support the use of function expressions . such as

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

It is also convenient to improve our query efficiency through functional index . See the following links for specific use

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

demo link

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