MySQL中的函数索引（Generated Column）及一次SQL优化

mysql 中是没有 oracle 的函数索引功能的，把 mysql 的 generated column 称为“函数索引”并不准确，但可以和函数索引达到同样的效果，也有人把这个特性称为“衍生列”。

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 定义语法

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 的两种类型，指明该字段的值如何存储：

• virtual: virtual generated column 的值不会持久化到磁盘，只保存在数据字典中（表的元数据），每次读取时在 before 触发器后就会立即计算。
  
• stored：stored generated column 的值会持久化到磁盘上，而不是每次读取时计算。

如果不指明的话，mysql 会默认以 virtual 的形式实现，stored 需要更多的磁盘空间，性能也没有明显的优势，所以一般使用 virtual。

generated column 定义要点

• 一般情况下，generated column 可以使用内置函数及操作符定义。如果给定相同的数据，多次调用会产生相同的结果，这样的定义是明确被允许的。否则，定义会失败，例如使用 now()、current_user()、connection_id(）的定义会失败。
  
• 自定义的函数和存储过程，不允许使用。
  
• 变量，例如系统变量、自定义变量等不允许使用。
  
• 子查询不允许使用。
  
• generated column 的定义中可以依赖其他 generated column 字段，但所依赖的衍生字段必须定义在它的前面。如果只依赖非衍生字段，则定义顺序没有要求。
  
• 自增长 auto_increment 不允许使用。
  
• 自增长的列，不能用到 generated column 的定义中。
  
• 从 mysql 5.7.10 开始，如果表达式计算导致截断或给函数提供了不正确的输入，则create table语句将终止，并返回ddl操作。

一次sql优化

通过慢查询日志找到一条慢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毫秒以内。