Click on the blue letters above to follow us
preface
In our day-to-day development process, sometimes an index fails because of a function call to an indexed column. For example, let’s say we want to query records by month, and when we only have time left in the table, if we use the following statement, where create_time is the index column
Although it is possible to find the correct result, we can find that the index is not taken by explaining. So we might change it to make sure we use indexes
Or simply redundant a column field for one month in a database table and create an index on that month. If we are using mysql version 5.7, we can use a new feature provided by the mysql version 5.7 – virtual columns to achieve the above effect
Virtual columns
In mysql 5.7 supports 2 kinds of virtual columns and stored columns. The difference between the two is that virtual only calculates the result when reading rows, but is not physically stored, so it does not occupy storage space, and only builds secondary indexes on the InnoDB engine, while stored is calculated and stored when row data is inserted or updated, which requires physical space and supports indexing in MyISAM and InnoDB engines
mysql5.7 The default virtual column type is virtual columns
Create a virtual column syntax
Considerations for using virtual columns
a. The definition of the derived column can be modified, but the virtual and stored cannot be converted to each other, and if necessary, it needs to be deleted and rebuilt
b. Virtual column fields are read-only, INSRET and UPDATE are not supported
c. You can only refer to the non-generated column field of this table, and you cannot refer to the fields of other tables
The expressions and operators used must be Immutable properties, for example, you cannot use CONNECTION_ID(), CURRENT_USER(), NOW()
e, you can convert existing ordinary columns into derived columns of stored type, but virtual types do not; Similarly, a derived column of type stored can be converted to a normal column, but not of the virtual type
f. Increment (AUTO_INCREMENT) is not allowed in virtual column definitions, nor incremented base columns
g. Virtual columns allow modification of expressions, but do not allow modification of storage methods (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 will be stored twice. Once as the value of the virtual column and once as the value in the index
Scenarios for using virtual columns
a. Virtual columns can simplify and unify the query, define complex conditions as generated columns, and directly use virtual columns (instead of views) when querying.
b. Storage virtual columns can be used as instantiation caches for dynamic computationally expensive complex conditions
c. Virtual columns can simulate functional indexes and can use indexes, which is useful for columns that cannot directly use indexes (JSON columns).
example
Because mysql 5.7 also supports json columns, this example demonstrates an example with json and virtual columns
Create a sample table
Create a virtual column
Note: Virtual columns can be created directly when the table is created. This example is to highlight the virtual column syntax
Normal our json statement is as follows
We remove the double quotes by 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 mysql 5.7 json is not the focus of this article, this article will not discuss, if you are interested in mysql5.7 json syntax function friends can view the following link
https://dev.mysql.com/doc/refman/5.7/en/json-functions.html
Create an index for the virtual column
View the resulting table data
See if an index is used
Note: In mysql 8.0 you can use EXPLAIN ANALYZE, he can see the time consumption of SQL
Small details at the code level
Because virtual columns cannot be inserted and updated, special attention should be paid to when using the orm framework. For example, when using mybatis-plus, remember to add the following annotation to the mapped field of the virtual column of the entity
With this annotation, the virtual column fields are not updated or inserted
summary
This article is based on mysql 5.7 a general introduction to the virtual column, if you use mysql 8.0.13 or above version, you can function index, his implementation is essentially based on the virtual column implementation. The so-called function index is to support the use of function expressions when creating an index. Like what
It is also convenient to improve our query efficiency through function indexing. For specific use, you can view 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