MySQL5.7 implement expression indexes for virtual Columns

MySQL5.7 implement expression indexes for virtual Columns

MySQL has not provided such complex functions as function indexing since ancient times. So how can this function be implemented in MySQL? Let's take a look at the concept of function indexing. Function indexes, also known as expression indexes, are required to create indexes based on fields using specific functions (expressions) to improve query performance. The advantage of function indexing is to obtain the required data more accurately.

MySQL 5.7 provides a new feature, virtual columns, which can perfectly solve this problem.

Before introducing virtual columns, let's look at the examples of common indexes in MySQL.

Example Table Structure:

Create table t1 (id INT, rank INT, log_time DATETIME, nickname VARCHAR (64) engine innodb;
Alter table t1 add primary key (id), add key idx_rank (rank), add key idx_log_time (log_time );

In the sample table, I have added 5000 records:

Mysql> select count (*) from t1;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 5000 |
+ ---------- +
1 row in set (0.00 sec)

Let's assume that we are going to retrieve data from April 9, 2015. (The result is two records with IDs 95 and 3423, respectively .)

Mysql> SELECT * FROM t1 where date (log_time) = '2017-04-09 '\ G
* *************************** 1. row ***************************
Id: 95
Rank: 24
Log_time: 05:53:13
Nickname: test
* *************************** 2. row ***************************
Id: 3423
Rank: 42
Log_time: 02:55:38
Nickname: test
2 rows in set (0.01 sec)

Let's take a look at the query plan of this statement.

Mysql> explain SELECT * FROM t1 where date (log_time) = '2017-04-09 '\ G
* *************************** 1. row ***************************
Id: 1
Select_type: SIMPLE
Table: t1
Partitions: NULL
Type: ALL
Possible_keys: NULL
Key: NULL
Key_len: NULL
Ref: NULL
Rows: 5000
Filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)

We found that the TYPE is ALL, and the scan function is 5000. That is to say, this statement performs a full table scan. Although the log_time field is indexed but not used, what should I do at this time?

In MySQL, it is generally modified as follows:

Mysql> SELECT * FROM t1 WHERE log_time> = '2017-04-09 00:00:00 'AND log_time <= '2017-04-10 00:00:00' \ G
* *************************** 1. row ***************************
Id: 3423
Rank: 42
Log_time: 02:55:38
Nickname: test
* *************************** 2. row ***************************
Id: 95
Rank: 24
Log_time: 05:53:13
Nickname: test
2 rows in set (0.00 sec)

The query results show that the result set is consistent. Let's look at the query plan.

Mysql> explain SELECT * FROM t1 WHERE log_time> = '2017-04-09 00:00:00 'AND log_time <= '2017-04-10 00:00:00' \ G
* *************************** 1. row ***************************
Id: 1
Select_type: SIMPLE
Table: t1
Partitions: NULL
Type: range
Possible_keys: idx_log_time
Key: idx_log_time
Key_len: 6
Ref: NULL
Rows: 2
Filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

We can see that this modified statement makes good use of the index idx_log_time.

 

Well, this is the solution in MySQL 5.6 and earlier versions. With the release of MySQL 5.7, the emergence of virtual columns makes this problem easier.

Modify the table structure as follows:

Alter table t1 add column log_date date as (DATE (log_Time) stored, add key idx_log_date (log_date );

In this way, a new column is added to store the expression date (log_time) and a column index is added to it.

Then, the previous statement becomes as follows:

Mysql> SELECT * FROM t1 WHERE log_date = '2017-04-09 '\ G
* *************************** 1. row ***************************
Id: 95
Rank: 24
Log_time: 05:53:13
Nickname: test
Log_date: 2015-04-09
* *************************** 2. row ***************************
Id: 3423
Rank: 42
Log_time: 02:55:38
Nickname: test
Log_date: 2015-04-09
2 rows in set (0.00 sec)

The result set after execution is consistent with the previous one.

 

Let's look at the query plan and find that the idx_log_date index column is well used.

Mysql> explain SELECT * FROM t1 WHERE log_date = '2017-04-09 '\ G
* *************************** 1. row ***************************
Id: 1
Select_type: SIMPLE
Table: t1
Partitions: NULL
Type: ref
Possible_keys: idx_log_date
Key: idx_log_date
Key_len: 4
Ref: const
Rows: 2
Filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)

Through the above introduction, we can see that virtual columns are much easier to implement than the previous method. But here I still have to say a few words.

Although the usage of function indexes and SQL statements are simple to write, in most cases, it is a design defect that can only be said to have to do so, it increases the difficulty and complexity of O & M personnel. That's why MySQL didn't launch this similar feature until 5.7.

This article permanently updates the link address: