时间:2020-03-09来源:电脑系统城作者:电脑系统城
MySQL 中是没有 Oracle 的函数索引功能的,把 MySQL 的 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 的定义语法如下:
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 的两种类型,指明该字段的值如何存储:
如果不指明的话,MySQL 会默认以 VIRTUAL 的形式实现,STORED 需要更多的磁盘空间,性能也没有明显的优势,所以一般使用 VIRTUAL。
NOW()
、CURRENT_USER()
、CONNECTION_ID()
的定义会失败。AUTO_INCREMENT
不允许使用。通过慢查询日志找到一条慢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)
执行计划正常,利用了索引,SQL耗时到了10毫秒以内。
2023-10-30
windows上的mysql服务突然消失提示10061 Unkonwn error问题及解决方案2023-10-30
MySQL非常重要的日志bin log详解2023-10-30
详解MySQL事务日志redo log一、单表查询 1、排序 2、聚合函数 3、分组 4、limit 二、SQL约束 1、主键约束 2、非空约束 3、唯一约束 4、外键约束 5、默认值 三、多表查询 1、内连接 1)隐式内连接: 2)显式内连接: 2、外连接 1)左外连接 2)右外连接 四...
2023-10-30
Mysql删除表重复数据 表里存在唯一主键 没有主键时删除重复数据 Mysql删除表中重复数据并保留一条 准备一张表 用的是mysql8 大家自行更改 创建表并添加四条相同的数据...
2023-10-30