MySQL5.7 虚拟列实现表达式索引

MySQL自古以来就不提供函数索引这么复杂的功能。那怎么在MySQL里面实现这样的功能呢？ 我们先来看看函数索引的概念。函数索引，也可称为表达式索引，也就是基于字段以特定函数（表达式）建立索引来提升查询性能之需。函数索引的优势在于更加精确的获取所需要的数据。


MySQL 5.7提供了一个新的特性，虚拟列，可以很完美的解决这个问题。
在介绍虚拟列之前，我们来看看在MySQL里面普通索引的范例。
示例表结构：

[sql] view plain copy

1. CREATE TABLE t1 (id INT ,rank INT, log_time DATETIME, nickname VARCHAR(64)) ENGINE INNODB;  
2. ALTER TABLE t1 ADD PRIMARY KEY (id), ADD KEY idx_rank (rank),ADD KEY idx_log_time (log_time);  

示例表数据量,这里我增加了5000条记录：

[sql] view plain copy

1. mysql> select count(*) from t1;  
2. +----------+  
3. | count(*) |  
4. +----------+  
5. |     5000 |  
6. +----------+  
7. 1 row in set (0.00 sec)  

假设我们来检索2015年4月9号的数据。（结果是有两条记录，id 分别为95和3423。）

[sql] view plain copy

1. mysql> SELECT * FROM t1 WHERE DATE(log_time) = '2015-04-09'\G  
2. *************************** 1. row ***************************  
3.       id: 95  
4.     rank: 24  
5. log_time: 2015-04-09 05:53:13  
6. nickname: test  
7. *************************** 2. row ***************************  
8.       id: 3423  
9.     rank: 42  
10. log_time: 2015-04-09 02:55:38  
11. nickname: test  
12. 2 rows in set (0.01 sec)  

下来我们看看这条语句的查询计划。

[sql] view plain copy

1. mysql> explain SELECT * FROM t1 WHERE DATE(log_time) = '2015-04-09'\G  
2. *************************** 1. row ***************************  
3.            id: 1  
4.   select_type: SIMPLE  
5.         table: t1  
6.    partitions: NULL  
7.          type: ALL  
8. possible_keys: NULL  
9.           key: NULL  
10.       key_len: NULL  
11.           ref: NULL  
12.          rows: 5000  
13.      filtered: 100.00  
14.         Extra: Using where  
15. 1 row in set, 1 warning (0.00 sec)  

我们发现TYPE是ALL，扫描的函数是5000，也就是说这条语句进行了一个全表扫描。 虽然给字段log_time 加了索引，但是没有用到，那这个时候怎么办？
在MySQL里面一般这样修改：

[sql] view plain copy

1. mysql> SELECT * FROM t1 WHERE log_time >= '2015-04-09 00:00:00' AND log_time <='2015-04-10 00:00:00'\G  
2. *************************** 1. row ***************************  
3.       id: 3423  
4.     rank: 42  
5. log_time: 2015-04-09 02:55:38  
6. nickname: test  
7. *************************** 2. row ***************************  
8.       id: 95  
9.     rank: 24  
10. log_time: 2015-04-09 05:53:13  
11. nickname: test  
12. 2 rows in set (0.00 sec)  

通过查询结果，发现结果集一致，那再来看看查询计划

[sql] view plain copy

1. mysql> explain SELECT * FROM t1 WHERE log_time >= '2015-04-09 00:00:00' AND log_time <= '2015-04-10 00:00:00'\G  
2. *************************** 1. row ***************************  
3.            id: 1  
4.   select_type: SIMPLE  
5.         table: t1  
6.    partitions: NULL  
7.          type: range  
8. possible_keys: idx_log_time  
9.           key: idx_log_time  
10.       key_len: 6  
11.           ref: NULL  
12.          rows: 2  
13.      filtered: 100.00  
14.         Extra: Using index condition  
15. 1 row in set, 1 warning (0.00 sec)  

可以看到这条修改过的语句很好的利用到了idx_log_time这条索引。


那好，这个是之前在MySQL 5.6以及之前的旧版本解决方法，随着MySQL 5.7的发布，虚拟列的出现让这个问题更加简单。
 现在修改下之前的表结构：

[sql] view plain copy

1. ALTER TABLE t1 ADD COLUMN log_date  DATE AS (DATE(log_Time)) stored, ADD KEY idx_log_date (log_date);  

这样，增加了一新列，用来存放date(log_time)这个表达式，并且给他加了一列索引。


那么，之前的语句就变成如下：

[sql] view plain copy

1. mysql> SELECT * FROM t1 WHERE log_date = '2015-04-09'\G  
2. *************************** 1. row ***************************  
3.       id: 95  
4.     rank: 24  
5. log_time: 2015-04-09 05:53:13  
6. nickname: test  
7. log_date: 2015-04-09  
8. *************************** 2. row ***************************  
9.       id: 3423  
10.     rank: 42  
11. log_time: 2015-04-09 02:55:38  
12. nickname: test  
13. log_date: 2015-04-09  
14. 2 rows in set (0.00 sec)  

执行后结果集和之前的一致。


我们来看看查询计划，发现很好的利用了idx_log_date索引列。

[sql] view plain copy

1. mysql> explain  SELECT * FROM t1 WHERE log_date = '2015-04-09'\G  
2. *************************** 1. row ***************************  
3.            id: 1  
4.   select_type: SIMPLE  
5.         table: t1  
6.    partitions: NULL  
7.          type: ref  
8. possible_keys: idx_log_date  
9.           key: idx_log_date  
10.       key_len: 4  
11.           ref: const  
12.          rows: 2  
13.      filtered: 100.00  
14.         Extra: NULL  
15. 1 row in set, 1 warning (0.00 sec)  

通过以上介绍，我们看到虚拟列实现起来相对之前的方法来的容易的多。但是这里笔者还是得说上几句。
函数索引的用法以及SQL语句虽然写起来简单，但是在大部分场合下，只能说不得已而为之，是一种设计上的缺陷，后期增加了运维人员的运维难度以及繁琐度。这也就是为什么MySQL 直到5.7才推出了这项类似的功能的原因。