"Original" MySQL5.7 virtual columns implement an expression index

MySQL has not provided the function index so complex function since ancient times. So how does this function in MySQL? Let's take a look at the concept of function indexes first. A function index, also known as an expression index, is the need to index a specific function (expression) based on a field to improve query performance. The advantage of a function index is that it obtains the required data more precisely.

MySQL 5.7 provides a new feature for virtual columns that can perfectly solve this problem.

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

Sample Table structure:

CREATE TABLE T1 (id INT, rank int, log_time DATETIME, nickname VARCHAR) ENGINE INNODB; ALTER TABLE T1 Add PRIMARY key (ID), add key Idx_rank (rank), add key Idx_log_time (Log_time);

Sample table data volume, here I added 5,000 records:

Mysql> Select COUNT (*) from t1;+----------+|     COUNT (*) |+----------+| |+----------+1 Row in Set (0.00 sec)

Let's say we retrieve the data for April 9, 2015. (The result is two records with IDs 95 and 3423, respectively.) ）

Mysql> SELECT * from T1 WHERE DATE (log_time) = ' 2015-04-09 ' \g*************************** 1. Row *************************** id:95 rank:24log_time:2015-04-09 05:53:13nickname:test************************ 2. Row *************************** id:3423 rank:42log_time:2015-04-09 02:55:38nickname:test2 rows in set (0.01 se C

Let's look at the query plan for this statement.

Mysql> explain SELECT * from T1 WHERE DATE (log_time) = ' 2015-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 Where1 Row in set, 1 Warning (0.00 sec)

We found that type is all, the function scanned is 5000, which means that the statement has a full table scan. Although the field log_time indexed, but not used, then what to do?

In MySQL it is generally modified as follows:

Mysql> SELECT * from t1 WHERE log_time >= ' 2015-04-09 00:00:00 ' and Log_time <= ' 2015-04-10 00:00:00 ' \g*********** 1. Row *************************** id:3423 rank:42log_time:2015-04-09 02:55:38nickname:test********************** 2. Row *************************** id:95 rank:24log_time:2015-04-09 05:53:13nickname:test2 rows in Set (0.00 sec)

Find the result set consistent through the query results, then take a look at the query plan

mysql> explain select * from t1 where log_time >=  ' 2015-04-09 00:00:00 '  AND log_time <=  ' 2015-04-10 00:00:00 ' \g*****************  1. row ***************************            id: 1  select_type: SIMPLE         table: t1   partitions: NULL          type: rangepossible_keys: idx_log_time           key: idx_log_time      key_len: 6           ref: null         rows: 2      filtered: 100.00        extra:  using index condition1 row in set, 1 warning  (0.00&NBSP;SEC) 

You can see that this modified statement makes good use of the Idx_log_time index.

Well, this is the previous version of the workaround in MySQL 5.6 and earlier, and with the release of MySQL 5.7, the advent of virtual columns makes the problem even easier.

Now modify the previous table structure:

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 hold the expression for date (Log_time), and a column index is added to it.

Then, the previous statement becomes the following:

Mysql> SELECT * from t1 WHERE log_date = ' 2015-04-09 ' \g*************************** 1. Row *************************** id:95 rank:24log_time:2015-04-09 05:53:13nickname:testlog_date:2015-04-09**** 2. Row *************************** id:3423 rank:42log_time:2015-04-09 02:55:38nickname:testlog_date:2015-04-092 Rows in Set (0.00 sec)

The result set after execution is consistent with the previous.

Let's take a look at the query plan and find a good use of the Idx_log_date index column.

Mysql> explain SELECT * from t1 WHERE log_date = ' 2015-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:1 00.00 extra:null1 Row in set, 1 Warning (0.00 sec)

With the above introduction, we see that the virtual column is much easier to implement than the previous method. But here I still have to say a few words.

The use of function indexes and the SQL statements are simple to write, but in most cases, only the last resort is a design flaw, which increases the operational difficulty and complexity of operation and maintenance personnel later. That's why MySQL didn't launch this similar feature until 5.7.

This article is from "God, we don't see!" "Blog, be sure to keep this provenance http://yueliangdao0608.blog.51cto.com/397025/1712423

"Original" MySQL5.7 virtual columns implement an expression index