MySQL自古以来就不提供函数索引这么复杂的功能。那怎么在MySQL里面实现这样的功能呢？ 我们先来看看函数索引的概念。函数索引，也可称为表达式索引，也就是基于字段以特定函数（表达式）建立索引来提升查询性能之需。函数索引的优势在于更加精确的获取所需要的数据。
MySQL 5.7提供了一个新的特性，虚拟列，可以很完美的解决这个问题。
在介绍虚拟列之前，我们来看看在MySQL里面普通索引的范例。
示例表结构：
|
1
2
|
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);
|
示例表数据量,这里我增加了5000条记录：
|
1
2
3
4
5
6
7
|
mysql>
select
count
(*)
from
t1;
+
----------+
|
count
(*) |
+
----------+
| 5000 |
+
----------+
1 row
in
set
(0.00 sec)
|
假设我们来检索2015年4月9号的数据。（结果是有两条记录，id 分别为95和3423。）
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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)
|
下来我们看看这条语句的查询计划。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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)
|
我们发现TYPE是ALL，扫描的函数是5000，也就是说这条语句进行了一个全表扫描。 虽然给字段log_time 加了索引，但是没有用到，那这个时候怎么办？
在MySQL里面一般这样修改：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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)
|
通过查询结果，发现结果集一致，那再来看看查询计划
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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)
|
可以看到这条修改过的语句很好的利用到了idx_log_time这条索引。
那好，这个是之前在MySQL 5.6以及之前的旧版本解决方法，随着MySQL 5.7的发布，虚拟列的出现让这个问题更加简单。
现在修改下之前的表结构：
|
1
|
ALTER
TABLE
t1
ADD
COLUMN
log_date
DATE
AS
(
DATE
(log_Time)) stored,
ADD
KEY
idx_log_date (log_date);
|
这样，增加了一新列，用来存放date(log_time)这个表达式，并且给他加了一列索引。
那么，之前的语句就变成如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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)
|
执行后结果集和之前的一致。
我们来看看查询计划，发现很好的利用了idx_log_date索引列。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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)
|
通过以上介绍，我们看到虚拟列实现起来相对之前的方法来的容易的多。但是这里笔者还是得说上几句。
函数索引的用法以及SQL语句虽然写起来简单，但是在大部分场合下，只能说不得已而为之，是一种设计上的缺陷，后期增加了运维人员的运维难度以及繁琐度。这也就是为什么MySQL 直到5.7才推出了这项类似的功能的原因。
