| |
MySQL does not provide a functional index since ancient times such complex functions. That how to achieve this function in MySQL inside? Let's look at the concept of a function of the index. Functional index, also known as an expression index, which is the field index to a particular function (expression) based on the need to improve the performance of the query. Advantages functional index is more accurate to obtain the required data.
MySQL 5.7 offers a new feature, a virtual column, can be the perfect solution to this problem.
Before introducing virtual column, we look at the example in which the general index of 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);
Sample data table, where I added the 5000 record:
mysql> select count (*) from t1;
+ ---------- +
| Count (*) |
+ ---------- +
| 5000 |
+ ---------- +
1 row in set (0.00 sec)
Suppose we retrieve April 9, 2015 data. (As a result there are two records, id were 95 and 3423.)
mysql> SELECT * FROM t1 WHERE DATE (log_time) = '2015-04-09' \ G
*************************** 1. row ******************** *******
id: 95
rank: 24
log_time: 2015-04-09 05:53:13
nickname: test
*************************** 2. row ******************** *******
id: 3423
rank: 42
log_time: 2015-04-09 02:55:38
nickname: test
2 rows in set (0.01 sec)
Next we look at the query plan of 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 where
1 row in set, 1 warning (0.00 sec)
We found TYPE is ALL, the scan function 5000, that this statement was a full table scan. Although the field log_time added to the index, but not used, then how to do this time?
In general MySQL inside this Review:
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: 42
log_time: 2015-04-09 02:55:38
nickname: test
*************************** 2. row ******************** *******
id: 95
rank: 24
log_time: 2015-04-09 05:53:13
nickname: test
2 rows in set (0.00 sec)
Query results, findings consistent set, that 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: 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)
You can see this modified statement to idx_log_time good use of this index.
Well, this is before the older MySQL 5.6 and previous versions of the solution, along with the release of MySQL 5.7, the emergence of virtual column should make this easier.
Now change before the next table structure:
ALTER TABLE t1 ADD COLUMN log_date DATE AS (DATE (log_Time)) stored, ADD KEY idx_log_date (log_date);
Thus, adding a new column to date (log_time) This expression is stored, and gave him an extra one index.
So, before the statement becomes as follows:
mysql> SELECT * FROM t1 WHERE log_date = '2015-04-09' \ G
*************************** 1. row ******************** *******
id: 95
rank: 24
log_time: 2015-04-09 05:53:13
nickname: test
log_date: 2015-04-09
*************************** 2. row ******************** *******
id: 3423
rank: 42
log_time: 2015-04-09 02:55:38
nickname: test
log_date: 2015-04-09
2 rows in set (0.00 sec)
Results of previous sets and consistent execution.
Let's look at the query plan, we found good use 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: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
Through the above description, we see the virtual columns to implement relative to the previous method of easier. But here I'd have to say a few words.
Function index usage, and to write SQL statements, although simple, but in most cases, can only be a last resort, one design flaw is on the increase in the latter part of the operation and maintenance of complicated and difficult operation and maintenance personnel. This is why MySQL 5.7 was introduced until the reasons for this similar feature. |
|
|
|