创建和管理生成的列

生成的列始终是由某行中的其他列计算得出的列。这些列可简化查询、查询时评估表达式的费用,并可编织索引或作为外键。在本文中,我们将介绍如何在数据库中管理此列类型。

将生成的列添加到新表

在下面的 CREATE TABLE 代码段中,我们创建了一个用于存储用户相关信息的表。我们有 FirstName 列和 LastName 列,还为 FullName 定义了生成的列,该列是 FirstNameLastName 的串联

GoogleSQL

CREATE TABLE Users (
Id STRING(20) NOT NULL,
FirstName STRING(50),
LastName STRING(50),
Age INT64 NOT NULL,
FullName STRING(100) AS (ARRAY_TO_STRING([FirstName, LastName], " ")) STORED,
) PRIMARY KEY (Id);

PostgreSQL

CREATE TABLE users (
id VARCHAR(20) NOT NULL,
firstname VARCHAR(50),
lastname VARCHAR(50),
age BIGINT NOT NULL,
fullname VARCHAR(100) GENERATED ALWAYS AS (firstname || ' ' || lastname) STORED,
PRIMARY KEY(id)
);

在插入新行或为现有行更新 FirstName 和/或 LastName 时,计算 FullName 的值。计算值与表中的其他列一起存储。英文括号中的 SQL 称为 generation expression

  • expression 可以是可分配给列数据类型的任何有效 SQL 表达式,并具有以下限制。

  • 表达式后面的 STORED 属性会存储表达式的结果以及表的其他列。对引用的任何列的后续更新会导致 Spanner 重新评估并存储表达式。

  • 除非使用 STORED 属性,否则 Spanner 不允许生成的列。

  • 不允许直接写入生成的列。

  • 生成的列或生成了列引用的任何列不允许使用列选项 allow_commit_timestamp

  • 您无法更改生成的列或所引用的任何列的数据类型。

  • 您无法删除生成的列引用的列。

  • 您可以将生成的列用作主键,并遵守以下额外限制:

    • 生成的主键无法引用其他生成的列。

    • 生成的主键最多只能引用一个非键列。

    • 生成的主键不能依赖于具有 DEFAULT 子句的非键列。

  • 使用生成的键列时,需遵循以下规则:

    • 读取 API:您必须完整指定键列,包括生成的键列。
    • 变更 API:对于 INSERTINSERT_OR_UPDATEREPLACE,Spanner 不允许您指定生成的键列。对于 UPDATE,您可以选择指定生成的键列。对于 DELETE,您需要完整指定包含生成的键的键列。
    • DML:无法在 INSERTUPDATE 语句中显式写入生成的键。
    • 查询:通常,我们建议您在查询中使用生成的键列作为过滤条件。(可选)如果生成的键列的表达式仅使用一列作为引用,则查询可以将等式 (=) 或 IN 条件应用于引用的列。如需了解详情并查看示例,请参阅创建派生自值列的唯一键

生成的列可以像任何其他列一样进行查询,如以下示例所示。

GoogleSQL

SELECT Id, FullName
FROM Users;

PostgreSQL

SELECT id, fullname
FROM users;

这相当于以下语句,它不使用存储的已生成列。

GoogleSQL

SELECT Id, ARRAY_TO_STRING([FirstName, LastName], " ") as FullName
FROM Users;

PostgreSQL

SELECT id, firstname || ' ' || lastname as fullname
FROM users;

除了简化查询并节省查询时对表达式求值的费用外,生成的列还可以编入索引或用作外键。

根据生成的列创建索引

为了帮助查询我们 FullName 生成的列,我们可以创建二级索引,如以下代码段所示。

GoogleSQL

CREATE INDEX UsersByFullName ON Users (FullName);

PostgreSQL

CREATE INDEX UserByFullName ON users (fullname);

将生成的列添加到现有表

使用以下 ALTER TABLE 语句,将生成的列添加到 Users 表,以生成并存储用户的姓名首字母。

GoogleSQL

ALTER TABLE Users ADD COLUMN Initials STRING(2)
AS (ARRAY_TO_STRING([SUBSTR(FirstName, 0, 1), SUBSTR(LastName, 0, 1)], "")) STORED;

PostgreSQL

ALTER TABLE users ADD COLUMN Initials VARCHAR(2)
GENERATED ALWAYS AS (SUBSTR(FirstName, 0, 1) || SUBSTR(LastName, 0, 1)) STORED;

将存储的已生成列添加到现有表是一项长时间运行的操作,用于回填列值。在回填期间,无法读取或查询存储的生成列。回填状态会反映在 INFORMATION_SCHEMA 中。

使用生成的列创建部分索引

如果我们只想查询 18 岁以上的用户,该怎么办?对表进行完整扫描的效率很低,因此我们使用部分索引。

  1. 使用以下语句添加另一个生成的列,如果用户已年满 18 周岁,则该列返回用户的年龄,否则返回 NULL

    GoogleSQL

    ALTER TABLE Users ADD COLUMN AgeAbove18 INT64
    AS (IF(Age > 18, Age, NULL)) STORED;
    

    PostgreSQL

    ALTER TABLE Users ADD COLUMN AgeAbove18 BIGINT
    GENERATED ALWAYS AS (nullif( Age , least( 18, Age) )) STORED;
    
  2. 为此新列创建索引,并使用 GoogleSQL 中的 NULL_FILTERED 关键字或 PostgreSQL 中的 IS NOT NULL 谓词禁止将 NULL 值编入索引。这种部分索引比普通索引更小,效率更高,因为它排除了不满 18 周岁的所有人。

    GoogleSQL

    CREATE NULL_FILTERED INDEX UsersAbove18ByAge
    ON Users (AgeAbove18);
    

    PostgreSQL

    CREATE INDEX UsersAbove18ByAge ON users (AgeAbove18)
    WHERE AgeAbove18 IS NOT NULL;
    
  3. 如需检索所有 18 岁以上的用户的 IdAge,请运行以下查询。

    GoogleSQL

    SELECT Id, Age
    FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
    WHERE AgeAbove18 IS NOT NULL;
    

    PostgreSQL

    SELECT Id, Age
    FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */
    WHERE AgeAbove18 IS NOT NULL;
    
  4. 如需按其他年龄进行过滤,例如检索所有年满 21 周岁的用户,请使用相同的索引并对生成的列进行过滤,如下所示:

    GoogleSQL

    SELECT Id, Age
    FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
    WHERE AgeAbove18 > 21;
    

    PostgreSQL

    SELECT Id, Age
    FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */
    WHERE AgeAbove18 > 21;
    

移除生成的列

以下 DDL 语句会从 Users 表中删除生成列。

ALTER TABLE Users
DROP COLUMN Initials;

修改生成的列表达式

不允许修改生成列的表达式。相反,您需要删除现有列,然后使用新表达式新建一个生成的列。

在生成的列上创建主键

在 Spanner 中,您可以在主键中使用生成的列。

以下示例展示了一个 DDL 语句,该语句使用生成的 ShardId 列创建 UserInfoLog 表。ShardId 列的值取决于另一列。它是通过对 UserId 列使用 MOD 函数得出的。ShardId 声明为主键的一部分。

GoogleSQL

CREATE TABLE UserInfoLog (
  ShardId INT64 NOT NULL
  AS (MOD(UserId, 2048)) STORED,
  UserId INT64 NOT NULL,
  FullName STRING(1024) NOT NULL,
) PRIMARY KEY (ShardId, UserId);

PostgreSQL

CREATE TABLE UserInfoLog (
  ShardId BIGINT GENERATED ALWAYS
  AS (MOD(UserId, '2048'::BIGINT)) STORED NOT NULL,
  UserId BIGINT NOT NULL,
  FullName VARCHAR(1024) NOT NULL,
  PRIMARY KEY(ShardId, UserId));

通常,为了高效地访问特定行,您需要指定所有键列。在前面的示例中,这意味着同时提供 ShardIdUserId。但是,如果所生成主键列依赖于某个其他列,并且所依赖的列的值已完全确定,则 Spanner 有时可以推断出其值。如果生成的主键列引用的列满足以下条件之一,则结果为 true:

  • 它等于 WHERE 子句中的常量值或绑定参数,或者
  • 它通过 WHERE 子句中的 IN 运算符设置值
  • 它从等联接条件中获取值

例如,对于以下查询:

GoogleSQL

SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;

PostgreSQL

SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;

Spanner 可以根据提供的 UserId 推断 ShardId 的值。上述查询等效于查询优化后的以下查询:

GoogleSQL

SELECT * FROM UserInfoLog
AS T WHERE T.ShardId = MOD(1, 2048)
AND T.UserId=1;

PostgreSQL

SELECT * FROM UserInfoLog
AS T WHERE T.ShardId = MOD(1, 2048)
AND T.UserId=1;

以下示例展示了如何创建 Students 表,以及如何使用一个表达式来检索 StudentInfo JSON 列的 id 字段并将其用作主键:

GoogleSQL

CREATE TABLE Students (
  StudentId INT64 NOT NULL
  AS (CAST(JSON_VALUE(StudentInfo, "$.id") AS INT64)) STORED,
  StudentInfo JSON NOT NULL,
) PRIMARY KEY (StudentId);

PostgreSQL

CREATE TABLE Students (
  StudentId BIGINT GENERATED ALWAYS
  AS (((StudentInfo ->> 'id'::TEXT))::BIGINT) STORED NOT NULL,
  StudentInfo JSONB NOT NULL,
  PRIMARY KEY(StudentId));

查看所生成列的属性

Spanner 的 INFORMATION_SCHEMA 包含有关在数据库中生成的列的信息。以下是一些您在查询信息架构时可以回答的问题示例。

我的数据库中定义了哪些生成的列?

SELECT c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;

Users 中生成的列的当前状态如何?

如果您已将生成的列添加到现有表,则可能需要在查询中传递 SPANNER_STATE 以了解该列的当前状态。SPANNER_STATE 会返回以下值:

  • COMMITTED:该列完全可用。
  • WRITE_ONLY:正在回填列。不允许读取。

使用以下查询查找列的状态:

GoogleSQL

SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME="Users" AND c.GENERATION_EXPRESSION IS NOT NULL;

PostgreSQL

SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME='users' AND c.GENERATION_EXPRESSION IS NOT NULL;

注意:生成的列不会影响读取或查询操作的性能。但是,如果写入操作修改了生成的列表达式中引用的任何列,则对所生成列的列表达式求值会产生开销,因此可能会影响写入操作(“DML”语句和“Mutations”)的性能。由于开销取决于应用的写入工作负载、架构设计和数据集特征,因此我们建议您在使用生成的列之前先对您的应用进行基准测试。

后续步骤