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

foreword

In our daily development process, sometimes the index is invalid due to the function call to the index column. For example, if we want to query records by month, and when we only store time in the table, if we use the following statement, where create_time is the index column

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

Although it is possible to find the correct result, but through explain we will find that the index is not taken. Therefore, in order to ensure that the index is used, we may change to

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

Or simply redundant column fields of a month in the database table, and create an index for this month. If we are using mysql version 5.7, we can use a new feature provided by mysql5.7 version - virtual column to achieve the above effect

virtual column

In mysql5.7, two types of virtual columns are supported: virtual columns and stored columns. The difference between the two is that virtual only calculates the results when reading rows, but does not physically store them, so it does not occupy storage space, and only builds secondary indexes on the InnoDB engine, while stored is when row data is inserted or updated. Calculated and stored requires physical space, and supports index creation in MyISAM and InnoDB engines

The default virtual column type of mysql5.7 is virtual columns

1. Create virtual column syntax

 ALTER TABLE 表名称 add column 虚拟列名称 虚拟列类型 [GENERATED ALWAYS] as (表达式) [VIRTUAL | STORED];

2. Notes on using virtual columns

a. The definition of the derived column can be modified, but the virtual and stored cannot be converted to each other. If necessary, you need to delete and rebuild

b. Virtual column fields are read-only and do not support INSRET and UPDATE

c. Only non-generated column fields of this table can be referenced, and fields of other tables cannot be referenced

d. The expressions and operators used must be Immutable attributes, such as CONNECTION_ID(), CURRENT_USER(), NOW() cannot be used

e. The existing ordinary columns can be converted into the derived columns of the stored type, but the virtual type cannot; similarly, the derived columns of the stored type can be converted into ordinary columns, but the virtual type cannot

f. The virtual column definition does not allow the use of auto-increment (AUTO_INCREMENT), nor the use of auto-increment base columns

g. The virtual column allows to modify the expression, but does not allow to modify the storage method (it can only be modified by deleting and re-creating)

h. If the virtual column is used as an index, there is a disadvantage that the value is stored twice. once as the value of the virtual column and once as the value in the index

3. Use scenarios of virtual columns

a. Virtual columns can simplify and unify queries, define complex conditions as generated columns, and use virtual columns directly (instead of views) when querying

b. Storage virtual columns can be used as instantiated caches for complex conditions where dynamic computation is expensive

c. Virtual columns can simulate functional indexes and can use indexes, which is very useful for columns (JSON columns) that cannot directly use indexes.

Example

Because mysql5.7 also supports json columns, this example uses json and virtual columns 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 a virtual column

Note: Virtual columns can be created directly when creating a table statement. This example is to highlight 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')));

Normally our json statement is as follows

 {"age": 23, "email": "[e-mail]", "mobile": "89136682644", "fullname": "李凯瑞", "username": "likairui"}

We remove the double quotes through JSON_UNQUOTE, otherwise the value of the generated virtual column v_user_name will become "likairui", and the actual field value we need should be likairui

Because the json of mysql5.7 is not the focus of this article, this article will not discuss it. If you are interested in mysql5.7 json syntax functions, you can check the following link

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

3. Create an index for the virtual column

 ALTER TABLE t_user_json ADD INDEX idx_v_user_name(v_user_name);

4. View the generated table data

5. Check whether 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')

Note: EXPLAIN ANALYZE can be used in mysql8.0 version, he can view the time-consuming of 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. Small details at the code level

Because virtual columns cannot be inserted and updated, special attention should be paid to this when using the ORM framework. For example, when using mybatis-plus, 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 annotation, the virtual column field will not be updated or inserted

Summarize

This article generally introduces virtual columns based on mysql5.7. If you use a version above mysql8.0.13, you can use functional indexes. The essence of his implementation is also based on virtual columns. The so-called functional index supports the use of function expressions when creating an index. for example

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

It is also very convenient to improve our query efficiency through functional indexes. For specific use, please refer to the following link

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