mysql 中是没有 oracle 的函数索引功能的，把 mysql 的 generated column 称为“函数索引”并不准确，但可以和函数索引达到同样的效果，也有人把这个特性称为“衍生列”。
generated column 的值是根据其定义的表达式所计算而来的，下面使用官方文档中的例子做个简单介绍。
有一张表存储直角三角形的三条边长，大家都知道，根据直角三角形的边长公式，斜边的长度可以通过另外两条边长计算得到，这样就可以在表中只存储两条直角边，而斜边通过 generated column 定义，创建这张表并插入一条数据：
create table triangle ( sidea double, sideb double, sidec double as (sqrt(sidea * sidea + sideb * sideb)) ); insert into triangle (sidea, sideb) values(1,1),(3,4),(6,8);
sidea 和 sideb 是两条直角边，sidec 是斜边，insert 时只需要插入两条直角边，也就是说 generated column 不能人为操作（插入、更新、删除），会自动根据其定义表达式计算得到。
查询这张表：
mysql> select * from triangle; +-------+-------+--------------------+ | sidea | sideb | sidec | +-------+-------+--------------------+ | 1 | 1 | 1.4142135623730951 | | 3 | 4 | 5 | | 6 | 8 | 10 | +-------+-------+--------------------+
generated column 的定义语法如下：
col_name data_type [generated always] as (expr) [virtual | stored] [not null | null] [unique [key]] [[primary] key] [comment 'string']
关键字“as”指明了这个字段是衍生的，是 generated column，as 后面就是用以计算的表达式。generated always 使定义更明确，可以省略。
virtual 和 stored 是 generated column 的两种类型，指明该字段的值如何存储：
如果不指明的话，mysql 会默认以 virtual 的形式实现，stored 需要更多的磁盘空间，性能也没有明显的优势，所以一般使用 virtual。
我要评论now()、current_user()、connection_id(）的定义会失败。auto_increment 不允许使用。通过慢查询日志找到一条慢sql，执行计划如下：
mysql> explain
select
c.id,
b.customer_status
from
t_core_customer c
inner join t_core_customer_bizinfo b on c.id = b.customer_id and b.biz_id = 'maintain'
where
replace ( replace ( c.customer_name, '（', '(' ), '）', ')' ) = '天津买斯扣科技有限公司';
+----+-------------+-------+------------+--------+----------------------------------+---------+---------+--------------------------------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
+----+-------------+-------+------------+--------+----------------------------------+---------+---------+--------------------------------+---------+----------+-------------+
| 1 | simple | b | null | all | idx_core_customer_bizinfo_cidbid | null | null | null | 1263918 | 10.00 | using where |
| 1 | simple | c | null | eq_ref | primary | primary | 110 | b.customer_id | 1 | 100.00 | using where |
+----+-------------+-------+------------+--------+----------------------------------+---------+---------+--------------------------------+---------+----------+-------------+
2 rows in set (0.05 sec)
客户表中有117万行数据，这条sql执行耗时4秒多，通过执行计划可以看到，客户表没有走索引而进行全表扫描，customer_name 字段的索引由于 replace 函数没有被利用到。
增加 generated column ：
alter table `t_core_customer`
add column `customer_name_replaced` varchar(200) as (replace(replace(customer_name, '（', '(' ), '）', ')' ));
创建索引：
alter table `t_core_customer` add index `customer_name_replaced`(`customer_name_replaced`) using btree;
优化后再看执行计划：
mysql> explain
select
c.id,
b.customer_status
from
t_core_customer c
inner join t_core_customer_bizinfo b on c.id = b.customer_id and b.biz_id = 'maintain'
where
replace ( replace ( c.customer_name, '（', '(' ), '）', ')' ) = '天津买斯扣科技有限公司';
+----+-------------+-------+------------+------+----------------------------------+----------------------------------+---------+-----------------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
+----+-------------+-------+------------+------+----------------------------------+----------------------------------+---------+-----------------------------+------+----------+-------+
| 1 | simple | c | null | ref | primary,customer_name_replaced | customer_name_replaced | 603 | const | 1 | 100.00 | null |
| 1 | simple | b | null | ref | idx_core_customer_bizinfo_cidbid | idx_core_customer_bizinfo_cidbid | 222 | c.id,const | 1 | 100.00 | null |
+----+-------------+-------+------------+------+----------------------------------+----------------------------------+---------+-----------------------------+------+----------+-------+
2 rows in set (0.40 sec)
执行计划正常，利用了索引，sql耗时到了10毫秒以内。
如对本文有疑问， 点击进行留言回复！！
网友评论