Generierte Spalten erstellen und verwalten

Eine generierte Spalte ist eine Spalte, die immer aus anderen Spalten in einer Zeile berechnet wird. Diese Spalten können eine Abfrage vereinfachen, die Kosten für die Auswertung eines Ausdrucks zur Abfragezeit sparen und indiziert oder als Fremdschlüssel verwendet werden. Auf dieser Seite wird beschrieben, wie Sie diesen Spaltentyp in Ihrer Datenbank für GoogleSQL- und PostgreSQL-Dialektdatenbanken verwalten.

Einer neuen Tabelle eine generierte Spalte hinzufügen

Im folgenden CREATE TABLE-Snippet wird eine Tabelle zum Speichern von Informationen über Nutzer erstellt. Wir haben Spalten für FirstName und LastName und definieren eine generierte Spalte für FullName, die die Verkettung von FirstName und LastName darstellt. Der SQL-Code in Klammern wird als Generierungsausdruck bezeichnet.

Eine generierte Spalte kann als STORED markiert werden, um die Kosten für die Auswertung des Ausdrucks zur Abfragezeit zu sparen. Daher wird der Wert von FullName nur berechnet, wenn eine neue Zeile eingefügt wird oder wenn FirstName oder LastName für eine vorhandene Zeile aktualisiert wird. Der berechnete Wert wird zusammen mit anderen Spalten in der Tabelle gespeichert.

GoogleSQL 

CREATE TABLE Users (
Id STRING(20) NOT NULL,
FirstName STRING(50),
LastName STRING(50),
Age INT64 NOT NULL,
FullName STRING(100) AS (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)
);

Sie können eine nicht gespeicherte generierte Spalte erstellen, indem Sie das Attribut STORED in der DDL weglassen. Diese Art von generierter Spalte wird zur Abfragezeit ausgewertet und kann eine Abfrage vereinfachen. In PostgreSQL können Sie eine nicht gespeicherte generierte Spalte mit dem Attribut VIRTUAL erstellen.

GoogleSQL 

FullName STRING(MAX) AS (CONCAT(FirstName, " ", LastName))

PostgreSQL 

fullname text GENERATED ALWAYS AS (firstname || ' ' || lastname) VIRTUAL

  • expression kann ein beliebiger gültiger SQL-Ausdruck sein, der dem Datentyp der Spalte mit folgenden Einschränkungen zugewiesen werden kann.

    • Der Ausdruck kann nur auf Spalten in derselben Tabelle verweisen.

    • Der Ausdruck kann nur unveränderliche Funktionen verwenden. Eine unveränderliche Funktion gibt bei jedem Aufruf mit denselben Argumentwerten dieselben Ergebnisse zurück. Weitere Informationen finden Sie unter Funktionsvolatilität.

    • Der Ausdruck muss skalar sein, d. h., er gibt einen einzelnen Wert zurück.

    • Der Ausdruck darf keine Unterabfragen enthalten.

    • Sie können den Ausdruck einer STORED- oder indexierten generierten Spalte nicht ändern.

  • Bei GoogleSQL-Dialektdatenbanken muss eine nicht gespeicherte generierte Spalte vom Typ STRING oder BYTES eine Länge von MAX haben.

  • Bei PostgreSQL-Dialektdatenbanken muss eine nicht gespeicherte oder virtuelle generierte Spalte vom Typ VARCHAR eine Länge von MAX haben.

  • Das Attribut STORED nach dem Ausdruck speichert das Ergebnis des Ausdrucks zusammen mit anderen Spalten der Tabelle. Bei späteren Aktualisierungen einer der referenzierten Spalten wird der Ausdruck von Spanner noch einmal ausgewertet und gespeichert.

  • Generierte Spalten, die nicht STORED sind, können nicht als NOT NULL markiert werden.

  • Direkte Schreibvorgänge in generierte Spalten sind nicht zulässig.

  • Die Spaltenoption allow_commit_timestamp ist für generierte Spalten oder Spalten, auf die von generierten Spalten verwiesen wird, nicht zulässig.

  • Bei STORED- oder generierten Spalten, die indexiert sind, können Sie den Datentyp der Spalte oder von Spalten, auf die von der generierten Spalte verwiesen wird, nicht ändern.

  • Sie können eine Spalte, auf die von einer generierten Spalte verwiesen wird, nicht löschen.

  • Sie können eine generierte Spalte als Primärschlüssel mit den folgenden zusätzlichen Einschränkungen verwenden:

    • Der generierte Primärschlüssel kann nicht auf andere generierte Spalten verweisen.

    • Der generierte Primärschlüssel kann auf maximal eine Nicht-Schlüsselspalte verweisen.

    • Der generierte Primärschlüssel darf nicht von einer Nicht-Schlüsselspalte mit einer DEFAULT-Klausel abhängen.

  • Die folgenden Regeln gelten bei der Verwendung generierter Schlüsselspalten:

    • Read APIs: Sie müssen die Schlüsselspalten vollständig angeben, einschließlich der generierten Schlüsselspalten.
    • Mutation APIs: Bei INSERT, INSERT_OR_UPDATE und REPLACE können Sie in Spanner keine generierten Schlüsselspalten angeben. Bei UPDATE können Sie optional generierte Schlüsselspalten angeben. Bei DELETE, müssen Sie die Schlüsselspalten vollständig angeben, einschließlich der generierten Schlüssel.
    • DML: Sie können in INSERT- oder UPDATE-Anweisungen nicht explizit in generierte Schlüssel schreiben.
    • Abfrage: Im Allgemeinen empfehlen wir, die generierte Schlüsselspalte als Filter in Ihrer Abfrage zu verwenden. Wenn der Ausdruck für die generierte Schlüssel spalte nur eine Spalte als Referenz verwendet, kann die Abfrage optional eine Gleichheitsbedingung (=) oder eine INBedingung auf die referenzierte Spalte anwenden. Weitere Informationen und ein Beispiel finden Sie unter Eindeutigen Schlüssel aus einer Wertspalte erstellen.

Die erzeugte Spalte kann wie jede andere Spalte abgefragt werden, wie im folgenden Beispiel gezeigt.

GoogleSQL 

SELECT Id, FullName
FROM Users;

PostgreSQL 

SELECT id, fullname
FROM users;

Die Abfrage mit Fullname entspricht der Abfrage mit dem generierten Ausdruck. Daher kann eine generierte Spalte die Abfrage vereinfachen.

GoogleSQL 

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

PostgreSQL 

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

Index für eine generierte Spalte erstellen

Sie können eine generierte Spalte auch indexieren oder als Fremdschlüssel verwenden.

Um Suchvorgänge in unserer generierten Spalte FullName zu unterstützen, können wir einen sekundären Index erstellen, wie im folgenden Snippet gezeigt.

GoogleSQL 

CREATE INDEX UsersByFullName ON Users (FullName);

PostgreSQL 

CREATE INDEX UserByFullName ON users (fullname);

Index für einen skalaren Ausdruck erstellen

Sie können auch einen sekundären Index für einen skalaren Ausdruck erstellen, ohne eine entsprechende generierte Spalte in Ihrer Tabelle zu erstellen.

Anstatt der Tabelle eine generierte Spalte VenueCity hinzuzufügen und dann einen Index für die Spalte zu erstellen, können Sie einen Expression Index erstellen.

GoogleSQL 

CREATE TABLE Venues (
  Id INT64 NOT NULL,
  VenueData JSON
) PRIMARY KEY (Id);

CREATE INDEX VenuesByCity ON Venues((JSON_VALUE(VenueData.address.city)));

PostgreSQL 

CREATE TABLE Venues (
  Id BIGINT NOT NULL PRIMARY KEY,
  VenueData JSONB
);

CREATE INDEX VenuesByCity ON Venues((VenueData -> 'address' ->> 'city'));

Einer vorhandenen Tabelle eine generierte Spalte hinzufügen

Mit der folgenden ALTER TABLE-Anweisung können wir der Tabelle Users eine generierte Spalte hinzufügen, um die Initialen des Nutzers zu generieren und zu speichern.

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;

Wenn Sie einer vorhandenen Tabelle eine gespeicherte generierte Spalte hinzufügen, wird ein Vorgang mit langer Ausführungszeit zum Backfill der Spaltenwerte gestartet. Beim Backfill können die gespeicherten generierten Spalten weder gelesen noch abgefragt werden. Der Backfill-Status wird in der Tabelle INFORMATION_SCHEMA wiedergegeben.

Teilindex mithilfe einer generierten Spalte erstellen

Was wäre, wenn wir nur Nutzer abfragen möchten, die mindestens 18 Jahre alt sind? Ein vollständiger Scan der Tabelle wäre ineffizient, daher verwenden wir einen Teilindex.

  1. Mit der folgenden Anweisung können Sie eine weitere generierte Spalte hinzufügen, die das Alter des Nutzers zurückgibt, wenn das Alter über 18 Jahre liegt. Andernfalls wird NULL zurückgegeben.

    GoogleSQL 

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

    PostgreSQL 

    ALTER TABLE Users ADD COLUMN AgeAbove18 BIGINT
GENERATED ALWAYS AS (nullif( Age , least( 18, Age) )) VIRTUAL;

  2. Erstellen Sie einen Index für diese neue Spalte und deaktivieren Sie die Indexierung von NULL Werten mit dem NULL_FILTERED Keyword in GoogleSQL oder dem IS NOT NULL Prädikat in PostgreSQL. Dieser Teilindex ist kleiner und effizienter als ein normaler Index, da er alle Nutzer von 18 Jahren oder jünger ausschließt.

    GoogleSQL 

    CREATE NULL_FILTERED INDEX UsersAbove18ByAge
ON Users (AgeAbove18);

    PostgreSQL 

    CREATE INDEX UsersAbove18ByAge ON users (AgeAbove18)
WHERE AgeAbove18 IS NOT NULL;

  3. Führen Sie die folgende Abfrage aus, um Id und Age aller Nutzer über 18 abzurufen.

    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. Um beispielsweise nach einem anderen Alter zu filtern und alle Nutzer ab 21 Jahren abzurufen, verwenden Sie denselben Index und filtern Sie die generierte Spalte so:

    GoogleSQL 

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

    PostgreSQL 

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

    Eine indexierte generierte Spalte kann die Kosten für die Auswertung eines Ausdrucks zur Abfragezeit sparen und im Vergleich zu einer STORED-generierten Spalte vermeiden, dass die Werte zweimal gespeichert werden (in der Basistabelle und im Index).

Generierte Spalte entfernen

Mit der folgenden DDL-Anweisung wird eine generierte Spalte aus der Tabelle Users gelöscht:

GoogleSQL 

  ALTER TABLE Users DROP COLUMN Initials;

PostgreSQL 

  ALTER TABLE users DROP COLUMN initials;

Generierten Spaltenausdruck ändern

GoogleSQL 

ALTER TABLE Users ALTER COLUMN FullName STRING(100)
AS (ARRAY_TO_STRING(ARRAY_TO_STRING([LastName, FirstName ], " ")));

PostgreSQL 

ALTER TABLE users ADD COLUMN Initials VARCHAR(2)
GENERATED ALWAYS AS (lastname || ' ' || firstname) VIRTUAL;

Das Aktualisieren des Ausdrucks einer STORED-generierten Spalte oder einer indexierten nicht gespeicherten generierten Spalte ist nicht zulässig.

Primärschlüssel für eine generierte Spalte erstellen

In Spanner können Sie eine STORED-generierte Spalte im Primärschlüssel verwenden.

Das folgende Beispiel zeigt eine DDL-Anweisung, mit der die Tabelle UserInfoLog mit einer generierten Spalte ShardId erstellt wird. Der Wert der Spalte ShardId hängt von einer anderen Spalte ab. Er wird mit einer MOD-Funktion für die Spalte UserId abgeleitet. ShardId wird als Teil des Primärschlüssels deklariert.

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));

Normalerweise müssen Sie alle Schlüsselspalten angeben, um effizient auf eine bestimmte Zeile zuzugreifen. Im vorherigen Beispiel müssten Sie sowohl eine ShardId als auch eine UserId angeben. Spanner kann jedoch manchmal den Wert der generierten Primärschlüsselspalte ableiten, wenn sie von einer einzelnen anderen Spalte abhängt und der Wert der Spalte, von der sie abhängt, vollständig bestimmt ist. Dies gilt, wenn die von der generierten Primärschlüsselspalte referenzierte Spalte eine der folgenden Bedingungen erfüllt:

  • Sie ist gleich einem konstanten Wert oder einem gebundenen Parameter in der WHERE-Klausel oder
  • Ihr Wert wird durch einen IN-Operator in der WHERE-Klausel festgelegt.
  • Ihr Wert wird aus einer Equi-Join-Bedingung abgeleitet.

Beispiel für die folgende Abfrage:

GoogleSQL 

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

PostgreSQL 

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

Spanner kann den Wert von ShardId aus der angegebenen UserId ableiten. Die vorherige Abfrage entspricht nach der Abfrageoptimierung der folgenden Abfrage:

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;

Im nächsten Beispiel wird gezeigt, wie Sie die Tabelle Students erstellen und einen Ausdruck verwenden, der das Feld id der JSON-Spalte StudentInfo abruft und als Primärschlüssel verwendet:

GoogleSQL 

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

PostgreSQL 

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

Attribute einer generierten Spalte ansehen

Das INFORMATION_SCHEMA von Spanner enthält Informationen zu den generierten Spalten in Ihrer Datenbank. Im Folgenden finden Sie einige Beispiele für Fragen, die Sie durch Abfragen des Informationsschemas beantworten können.

Welche generierten Spalten werden in meiner Datenbank definiert?

GoogleSQL 

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

PostgreSQL 

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

IS_STORED ist entweder YES für gespeicherte generierte Spalten, NO für nicht gespeicherte generierte Spalten oder NULL für nicht generierte Spalten.

Wie ist der aktuelle Status der generierten Spalten in der Tabelle Users?

Wenn Sie einer vorhandenen Tabelle eine generierte Spalte hinzugefügt haben, können Sie SPANNER_STATE in einer Abfrage übergeben, um den aktuellen Status der Spalte zu ermitteln. SPANNER_STATE gibt die folgenden Werte zurück:

  • COMMITTED: Die Spalte ist vollständig verwendbar.
  • WRITE_ONLY: Backfill für diese Spalte wird durchgeführt. Es ist kein Lesevorgang erlaubt.

Verwenden Sie die folgende Abfrage, um den Status einer Spalte zu ermitteln:

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;

Hinweis: Auf eine nicht gespeicherte generierte Spalte kann nur über die SQL-Abfrage zugegriffen werden. Wenn sie jedoch indexiert ist, können Sie mit der Read API auf den Wert aus dem Index zugreifen.

Leistung

Eine STORED-generierte Spalte hat keine Auswirkungen auf die Leistung eines Lese- oder Abfragevorgangs. Nicht gespeicherte generierte Spalten, die in einer Abfrage verwendet werden, können jedoch die Leistung beeinträchtigen, da der Ausdruck der generierten Spalte ausgewertet werden muss.

Die Leistung von Schreibvorgängen (DML-Anweisungen und Mutationen) wird beeinträchtigt, wenn entweder eine STORED-generierte Spalte oder eine indexierte generierte Spalte verwendet wird. Der Mehraufwand entsteht durch die Auswertung des Ausdrucks der generierten Spalte, wenn der Schreibvorgang Spalten einfügt oder ändert, auf die im Ausdruck der generierten Spalte verwiesen wird. Da der Mehraufwand je nach Schreibarbeitslast für die Anwendung, dem Schemadesign und den Datensatzmerkmalen variiert, empfehlen wir, Ihre Anwendungen zu testen, bevor Sie eine generierte Spalte verwenden.

Nächste Schritte