mysql假造列Generated Column

海哥 · 4 天前

目录

1、Generated Column简介
天生的列定义具有以下语法：
2、实践
2.1 存储格式为json字段增长索引
2.2 手机号后四位
3、假造列索引介绍
3.1 假造列索引的限制
3.1.1 Virtal Generated Column
4、阿里云数据库环境是否支持
下期扩展：
1、MySQL InnoDB Cluster
2、MySQL NDB Cluster
参考文档

1、Generated Column简介

MySQL 5.7引入Generated Column(天生列、假造列、假造天生列、索引函数)：根据列定义中包罗的表达式计算得出
天生列包罗下面两种类型：
Virtual Generated Column（假造）：当从表中读取记录时，将动态计算该列。生存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上。（MySQL 5.7中默认是Virtual Generated Column）
Stored Generated Column（存储）：当向表中写入新记录时，将计算该列并将其存储为通例列。
virtual天生列比stored天生列更有用，因为一个假造的列不占用任何存储空间。你可以利用触发器模拟stored天生列的行为。

用法举例

drop TABLE triangle;
CREATE TABLE triangle
(
sidea DOUBLE,
sideb DOUBLE,
sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);
select * from triangle;
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);

复制代码

sidec边为假造列。插入c的值为动态计算如下图：

一般环境下，都利用Virtual Generated Column，这也是MySQL默认的方式，假如利用Stored Generated Column，前面的建表语句将会是下面如许，即多了一个STORED关键字.

CREATE TABLE `triangle`
(
`sidea` double DEFAULT NULL,
`sideb` double DEFAULT NULL,
`sidec` double GENERATED ALWAYS AS (SQRT(sidea * sidea + sideb * sideb)) STORED
);

复制代码

天生的列定义具有以下语法：

col_name data_type [GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']

复制代码

AS (expr) 指示天生列并定义用于计算列值的表达式。AS 可以在前面加上GENERATED ALWAYS以使天生的列的性子更加明白（区分其他列）。
VIRTUALor关键字指示如何存储列值，这STORED 对列的利用有影响：

VIRTUAL: 不存储列值，而是在读取行时，在任何 BEFORE触发器之后立即评估。假造列不占用存储空间、InnoDB支持假造列的二级索引。（默认）
STORED：在插入或更新行时评估和存储列值。存储列确实必要存储空间并且可以被索引。

答应在同一个表中肴杂VIRTUAL列 STORED。

天生列的规则和限制：

答应利用笔墨、确定性内置函数和运算符。假如给定表中的相同数据，则函数是确定性的，多次调用产生相同的效果，独立于毗连的用户。非确定性且不符合此定义的函数示例：CONNECTION_ID(), CURRENT_USER(), NOW().
不答应存储函数和可加载函数。
不答应利用存储过程和函数参数。
不答应利用变量（系统变量、用户定义变量和存储的程序局部变量）。
不答应子查询。
天生的列定义可以引用其他天生的列，但只能引用表定义中较早出现的列。天生的列定义可以引用表中的任何根本（非天生）列，无论其定义发生得早还是晚。
该AUTO_INCREMENT属性不能在天生的列定义中利用。
AUTO_INCREMENT列不能用作天生的列定义中的基列。
从 MySQL 5.7.10 开始，假如表达式求值导致截断或向函数提供不正确的输入，则 CREATE TABLE语句以错误停止并且 DDL 操作被拒绝。

天生的列作用：

假造天生的列可用作简化和统一查询的一种方式。一个复杂的条件可以定义为一个天生的列，并从对表的多个查询中引用，以确保它们都利用完全相同的条件。
存储的天生列可以用作复杂条件的物化缓存，这些条件在运行时计算本钱很高。
天生列可以模拟函数索引：利用天生列定义函数表达式并对其进行索引。这对于处理惩罚无法直接索引的类型的列（比方 JSON列）很有用；有关具体示例，请参阅索引天生的列以提供 JSON 列索引。对于存储天生的列，这种方法的缺点是值被存储了两次；一次作为天生列的值，一次在索引中。
假如天生的列被索引，优化器会识别与列定义匹配的查询表达式，并在查询执行期间恰当地利用列中的索引，即使查询不直接按名称引用列。有关具体信息，请参阅第 8.3.10 节，“优化器利用天生的列索引”。

2、实践

2.1 存储格式为json字段增长索引

之前日记存储都是采用json，因此本次利用的存储json字段相干表
json剖析的方法：
-> MySQL 5.7.9 及更高版本支持该运算符。->> 从 MySQL 5.7.13 开始支持该运算符。

请参阅 -> and ->> 运算符以及 JSON_EXTRACT()and JSON_UNQUOTE()函数的说明

-- json_extract和->>的区别，json_extract解析出的带双引号两种解析中文和数字貌似都需要带双引号
select json_extract(params,'$."联系方式"') as tel from execute_log;
select json_extract(params,'$.name') from execute_log;
SELECT params->>'$."联系方式"' AS tel from execute_log;
-- 英文不用双引号
SELECT params->>'$.name' AS tel from execute_log;

复制代码

假造天生列新增：

-- 删除虚拟列
ALTER TABLE execute_log DROP COLUMN `mobile`;
-- 添加联系方式的虚拟列
alter table execute_log add mobile varchar(20) generated always as (params->>'$."联系方式"') stored after params;
-- 新增索引
alter table execute_log add index idx_mobile(mobile);

复制代码

执行查询后：

固然在实际利用过程中，索引都是带companyId的

-- 新增公司和手机号索引
alter table execute_log add index idx_company_mobile(company_id,mobile);

复制代码

2.2 手机号后四位

-- 添加联系方式后4位的虚拟列
alter table test_table add right4Mobile varchar(20) generated always as (RIGHT (mobile,4)) stored after mobile ;
-- 删除虚拟列
ALTER TABLE test_table DROP COLUMN `right4Mobile`;
-- 添加联合索引
alter table test_table add index idx_company_right4Mobile(company_id,right4Mobile);

复制代码

性能对比 数据集：1004177(百万)

是否添加假造列	执行sql	耗时
否	select * from test_table where company_id = 6 and mobile like '%1800';	109 rows retrieved starting from 1 in 5 s 83 ms (execution: 2 s 911 ms, fetching: 2 s 172 ms)
是	select * from test_table where company_id = 6 and right4Mobile = '1800';	109 rows retrieved starting from 1 in 160 ms (execution: 72 ms, fetching: 88 ms)

添加假造列过程记录备份：

demo> alter table test_table add right4Mobile varchar(10) generated always as (RIGHT (mobile,4)) after mobile [2022-01-19 20:22:07]
completed in 3 s 101 ms
demo> alter table test_table add index idx_company_right4Mobile(company_id,right4Mobile) [2022-01-19 20:22:26]
completed in 6 s 91 ms

复制代码

3、假造列索引介绍

InnoDB支持假造天生列的二级索引。不支持其他索引类型。在假造列上定义的二级索引偶然称为“假造索引”。
  二级索引可以在一个或多个假造列或假造列和通例列的组合或存储的天生列上创建。包罗假造列的二级索引可以定义为UNIQUE.
  在假造天生列上创建二级索引时，天生的列值会在索引的记录中具体化。假如索引是覆盖索引（包括查询检索到的全部列），则从索引结构中的物化值中检索天生的列值，而不是“即时”计算。
When a secondary index is created on a virtual generated column, generated column values are materialized in the records of the index. If the index is a covering index (one that includes all the columns retrieved by a query), generated column values are retrieved from materialized values in the index structure instead of computed “on the fly”.

  covering index（不回表）
  An index that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, saving disk I/O. InnoDB can apply this optimization technique to more indexes than MyISAM can, because InnoDB secondary indexes also include the primary key columns. InnoDB cannot apply this technique for queries against tables modified by a transaction, until that transaction ends.
  Any column index or composite index could act as a covering index, given the right query. Design your indexes and queries to take advantage of this optimization technique wherever possible.
  See Also column index, composite index, index, primary key, secondary index.
  INSERT由于在和 UPDATE操作期间实现二级索引记录中的假造列值时执行的计算，在假造列上利用二级索引时必要思量额外的写入本钱。即使有额外的写入本钱，假造列上的二级索引也可能比天生的存储列更可取，后者在聚集索引中具体化，从而导致必要更多磁盘空间和内存的更大表。假如未在假造列上定义二级索引，则读取会产生额外本钱，因为每次检查列的行时都必须计算假造列值。
索引假造列的值是 MVCC 记录的，以避免在回滚或清除操作期间对天生的列值进行不必要的重新计算。记录值的数据长度受索引键的限制，对于和行格式为 767 字节，对于和 COMPACT行REDUNDANT格式为 3072 字节。 DYNAMICCOMPRESSED
在假造列上添加或删除二级索引是就地操作。（ Adding or dropping a secondary index on a virtual column is an in-place operation.）
在 5.7.16 之前，外键束缚不能引用在假造天生列上定义的二级索引。
在 MySQL 5.7.13 和更早版本中，InnoDB不答应在索引天生的假造列的基列上定义具有级联引用操作的外键束缚。MySQL 5.7.14 中取消了此限制。

3.1 假造列索引的限制

3.1.1 Virtal Generated Column

聚集索引不能包罗Virtual generated column

create table t1(a int, b int , c int GENERATED ALWAYS AS (a / b), primary key(c))
[HY000][3106] 'Defining a virtual generated column as primary key' is not supported for generated columns.
-- STORED 可以
create table t1(a int, b int , c int GENERATED ALWAYS AS (a / b) STORED, primary key(c))
completed in 168 ms

复制代码

Virtual Generated Column不能作为外键（在 5.7.16 之前，外键束缚不能引用在假造天生列上定义的二级索引。在 MySQL 5.7.13 和更早版本中，InnoDB不答应在索引天生的假造列的基列上定义具有级联引用操作的外键束缚。MySQL 5.7.14 中取消了此限制。）

创建generated column(包括virtual generated column 和stored generated column)时不能利用非确定性的（不可重复的）函数，如下curtime()

create table t1(a int, b int , c int GENERATED ALWAYS AS (a / b) STORED, primary key(c));
[HY000][3763] Expression of generated column 'p3' contains a disallowed function: curtime.
ALTER TABLE `t1` ADD p3 DATE GENERATED ALWAYS AS (curtime()) stored;
[HY000][3763] Expression of generated column 'p3' contains a disallowed function: curtime.

复制代码