当前位置：首页 > 数据库 > Mysql > 详细页面

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

时间：2020-03-09来源：电脑系统城作者：电脑系统城

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

Generated Column 是什么#

Generated Column 的值是根据其定义的表达式所计算而来的，下面使用官方文档中的例子做个简单介绍。

有一张表存储直角三角形的三条边长，大家都知道，根据直角三角形的边长公式，斜边的长度可以通过另外两条边长计算得到，这样就可以在表中只存储两条直角边，而斜边通过 Generated Column 定义，创建这张表并插入一条数据：

Copy

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 不能人为操作（插入、更新、删除），会自动根据其定义表达式计算得到。

查询这张表：

Copy

mysql> SELECT * FROM triangle; +-------+-------+--------------------+ | sidea | sideb | sidec | +-------+-------+--------------------+ | 1 | 1 | 1.4142135623730951 | | 3 | 4 | 5 | | 6 | 8 | 10 | +-------+-------+--------------------+

Generated Column 定义语法#

Generated Column 的定义语法如下：

Copy

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，执行计划如下：

Copy

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 ：

Copy

ALTER TABLE `t_core_customer` ADD COLUMN `customer_name_replaced` varchar(200) AS (REPLACE(REPLACE(customer_name, '（', '(' ), '）', ')' ));

创建索引：

Copy
ALTER TABLE `t_core_customer` ADD INDEX `customer_name_replaced`(`customer_name_replaced`) USING BTREE;

优化后再看执行计划：

Copy

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)