微信搜索“coder-home”或扫一扫下面的二维码，关注公众号，
第一时间了解更多干货分享，还有各类视频教程资源。扫描它，带走我
背景
环境准备
解决方式
冗余字段
函数索引
总结
背景
今天有同事咨询了一个SQL优化的问题。他有一个SQL语句执行时间比较长，是在前端页面中展现一个查询页面的SQL。这个页面中有几个列支持排序，并且有一个列并不是真实存在数据库表中的一个字段，而是通过表中的两个字段计算得到的，并且这个计算得到的列也需要支持排序的功能。
针对这样的一个需求，我想到Oracle数据库中函数索引，不知道MySQL是否支持函数索引，于是查了一下，果然也有这样的函数索引。但是，在老版本的MySQL中是不支持的，比如5.6版本中就不支持这个功能。
下面让我们来看一下，函数索引到底是一个什么东东。
环境准备
现在有这样一个表tab
，它有3个列，分别是列a、b、c
，他们都是int
类型的字段，建表语句如下所示：
drop table if exists tab;
create table tab(
a int,
b int,
c int
);
现在需要在页面展示4个字段，分别是列a、列b、列c、列a+b的和，并且这四个展示的列都需要有排序的功能。如下所示：
| a | b | c | a+b |
|---|---|---|---|
| 1 | 2 | 3 | 3 |
| ... | ... | ... | ... |
| x | y | z | x+y |
解决方式
先说一下同事遇到的这个问题，针对他的这个需求，目前我想到的有两个方式：
建立冗余字段
函数索引
冗余字段
所谓的建立冗余字段，就是在原先的表中，增加一个列，这个列就用来存储通过其他两个列计算得到的值，直接在插入数据的时候，就计算好这个列的值，然后直接维护到这个列中，这样在查询的时候，就不需要再次计算这两个列的值了，同时，如果查询效率的问题，可以在这个列上面增加索引。
针对我们上面准备的环境，表tab
的结构需要改为如下的结构，增加一个列d
，同时在插入数据的时候，需要在维护d
列的值，这个列的值再插入的时候就计算好，由a+b
得到的结果作为d
列的值插入到表tab
中。列d
是一个真实存在的物理字段。
drop table if exists tab;
create table tab(
a int,
b int,
c int,
d int
);
/*插入数据，此时手动的维护了列d的值。*/
insert into tab(a, b, c, d) values(1,1,1,2);
insert into tab(a, b, c, d) values(2,2,2,4);
insert into tab(a, b, c, d) values(3,3,3,6);
如果我们的数据量比较大，需要提高查询效率，我们还可以基于这个列d
增加索引，如下就是增加索引的SQL语句。
alter table tab add index idx_tab_d(d) using btree;
函数索引
这种实现方式，是我们具体要探讨的实现方式。我们的目的是为了在页面展示一个列，但是这个列的值通过其他两个或多个列的值计算得到的。并且想对这个列增加上索引，以便于提高查询的效率，这样的一个诉求，我们是没有办法直接在这个计算得到的列上面增加索引的，因为这个列是不存在的。
MySQL中提供了一种叫做Generated Column
的概念。它可以分为两种类型。
Virtual：虚拟列，这个列是虚拟的，它不占用任何存储空间，它的值，是在读取表中的数据行的时候，根据定义的计算公式，动态的计算出来的。
Stored：存储列，这个列是真实的存在磁盘上的，它会占用存储空间，它的值，是在插入数据的时候根据公式计算出来的，所以在读取的时候，不需要再次计算这个列的值，直接从磁盘上读取出来返回给客户端。
以上两种类型的Generated Column
的列，他们在向表中插入数据的时候，都不需要手动的维护这个列的值，只要你维护了这个列公式中用到的列的值之后，这个列的值，就会根据计算好的公式，自行的计算这个列的值到底是多少。一个是在数据行真正被读取到的时候，才去执行公式计算得到值，一个是在插入数据行的时候，就计算出了具体的值，然后存储在磁盘中。
根据上面准备的环境，此时的表tab
，需要改为如下结构：
drop table if exists tab;
create table tab(
a int,
b int,
c int,
d int as (a + b)
);
插入数据的时候，使用下面的SQL，从SQL语句中可以看出列d
的值并没有在插入的时候维护，但是当我们查询表中的数据的时候，列d
的值是可以查询出来的。
insert into tab(a, b, c) values(1,1,1);
insert into tab(a, b, c) values(2,2,2);
insert into tab(a, b, c) values(3,3,3);
实验过程如下所示：
在上面的创建表的语句中，我们没有指定列d
的类型到底是virtual还是stored，但是它的类型是virtual类型，也就是说当我们定义一个Generated Column
列的时候，如果不显示的指定是stored类型的，那么它默认就是virtual类型。
如果我们要显示的声明一个stored类型的列，请查看如下的SQL语句：
drop table if exists tab;
create table tab(
a int,
b int,
c int,
d int as (a + b) stored
);
insert into tab(a, b, c) values(1,1,1);
insert into tab(a, b, c) values(2,2,2);
insert into tab(a, b, c) values(3,3,3);
实验过程如下所示：
对于上面，我们创建的列d
，为了提高查询的效率，我们也可以在这个列上面创建索引，此时的索引就类似于Oracle
中的函数索引，因为这个列d
的值是通过其他列计算而来，这个计算的公式是一个函数表达式，上面的例子中，我们使用的是加法，它支持各种函数表达式，像MySQL中sum、avg、concat、max、min
等都支持。可以根据自己的业务需求自行更换。然后我们在基于这个列，创建了一个索引。创建索引的语句如下：
mysql> alter table tab add index idx_tab_d(d) using btree;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
总结
以上就是MySQL中函数索引的使用示例。对于Virtual Generated Column，MySQL只是将这一列的元信息保存在数据字典中，并不会将这一列数据持久化到磁盘上，因此，在MySQL的Virtual Generated Column上建立索引和Oracle的函数索引类似，并不需要更多的代价，只是使用方式有点不一样而已。
微信搜索“coder-home”或扫一扫下面的二维码，关注公众号，
第一时间了解更多干货分享，还有各类视频教程资源。扫描它，带走我
