Primär- und Fremdschlüssel verwenden

Primär- und Fremdschlüssel sind Tabelleneinschränkungen, die bei der Abfrageoptimierung helfen können. In diesem Dokument wird beschrieben, wie Sie Einschränkungen erstellen, ansehen und verwalten und wie Sie sie zum Optimieren Ihrer Abfragen verwenden.

BigQuery unterstützt die folgenden Schlüsselbeschränkungen:

  • Primärschlüssel: Ein Primärschlüssel für eine Tabelle ist eine Kombination aus einer oder mehreren Spalten, die für jede Zeile eindeutig ist und nicht NULL.
  • Fremdschlüssel: Ein Fremdschlüssel für eine Tabelle ist eine Kombination aus einer oder mehreren Spalten, die in der Primärschlüsselspalte einer referenzierten Tabelle vorhanden ist oder NULL ist.

Primär- und Fremdschlüssel werden in der Regel verwendet, um die Datenintegrität sicherzustellen und die Abfrageoptimierung zu ermöglichen. In BigQuery werden keine Primär- und Fremdschlüsseleinschränkungen erzwungen. Wenn Sie Einschränkungen für Ihre Tabellen deklarieren, müssen Sie dafür sorgen, dass Ihre Daten diesen Einschränkungen entsprechen. BigQuery kann Tabelleneinschränkungen verwenden, um Ihre Abfragen zu optimieren.

Beschränkungen verwalten

Primärschlüssel- und Fremdschlüsselbeziehungen können über die folgenden DDL-Anweisungen erstellt und verwaltet werden:

Sie können Tabelleneinschränkungen auch über die BigQuery API verwalten, indem Sie das TableConstraints-Objekt aktualisieren.

Beschränkungen ansehen

Die folgenden Ansichten enthalten Informationen zu Ihren Tabelleneinschränkungen:

Abfragen optimieren

Wenn Sie Primär- und Fremdschlüssel für Ihre Tabellen erstellen und erzwingen, kann BigQuery diese Informationen verwenden, um bestimmte Join-Vorgänge in Abfragen zu eliminieren oder zu optimieren. Es ist zwar möglich, diese Optimierungen durch Umschreiben Ihrer Abfragen nachzubilden, aber das ist nicht immer praktikabel.

In einer Produktionsumgebung erstellen Sie möglicherweise Ansichten, in denen viele Fakten- und Dimensionstabellen verknüpft werden. Entwickler können die Ansichten abfragen, anstatt die zugrunde liegenden Tabellen abzufragen und die Joins jedes Mal manuell neu zu schreiben. Wenn Sie die richtigen Einschränkungen definieren, werden Join-Optimierungen automatisch für alle entsprechenden Abfragen durchgeführt.

In den Beispielen in den folgenden Abschnitten wird auf die Tabellen store_sales und customer mit Einschränkungen verwiesen:

CREATE TABLE mydataset.customer (customer_name STRING PRIMARY KEY NOT ENFORCED);

CREATE TABLE mydataset.store_sales (
    item STRING PRIMARY KEY NOT ENFORCED,
    sales_customer STRING REFERENCES mydataset.customer(customer_name) NOT ENFORCED,
    category STRING);

Inner Joins eliminieren

Betrachten Sie die folgende Abfrage, die eine INNER JOIN enthält:

SELECT ss.*
FROM mydataset.store_sales AS ss
    INNER JOIN mydataset.customer AS c
    ON ss.sales_customer = c.customer_name;

Die Spalte customer_name ist ein Primärschlüssel in der Tabelle customer. Daher hat jede Zeile aus der Tabelle store_sales entweder eine einzelne Übereinstimmung oder keine Übereinstimmung, wenn sales_customer gleich NULL ist. Da in der Abfrage nur Spalten aus der Tabelle store_sales ausgewählt werden, kann der Abfrageoptimierer den Join entfernen und die Abfrage so umschreiben:

SELECT *
FROM mydataset.store_sales
WHERE sales_customer IS NOT NULL;

Outer Joins entfernen

Wenn Sie ein LEFT OUTER JOIN entfernen möchten, müssen die Join-Schlüssel auf der rechten Seite eindeutig sein und es dürfen nur Spalten von der linken Seite ausgewählt sein. Betrachten Sie die folgende Abfrage:

SELECT ss.*
FROM mydataset.store_sales ss
    LEFT OUTER JOIN mydataset.customer c
    ON ss.category = c.customer_name;

In diesem Beispiel besteht keine Beziehung zwischen category und customer_name. Die ausgewählten Spalten stammen nur aus der Tabelle store_sales und der Join-Schlüssel customer_name ist ein Primärschlüssel in der Tabelle customer. Daher ist jeder Wert eindeutig. Das bedeutet, dass es für jede Zeile in der Tabelle store_sales genau eine (möglicherweise NULL) Übereinstimmung in der Tabelle customer gibt und die LEFT OUTER JOIN eliminiert werden kann:

SELECT ss.*
FROM mydataset.store_sales;

Joins neu anordnen

Wenn BigQuery einen Join nicht eliminieren kann, können Tabelleneinschränkungen verwendet werden, um Informationen zu Join-Kardinalitäten zu erhalten und die Reihenfolge zu optimieren, in der Joins ausgeführt werden.

Beschränkungen

Für Primär- und Fremdschlüssel gelten die folgenden Einschränkungen:

  • Schlüsselbeschränkungen werden in BigQuery nicht erzwungen. Sie sind dafür verantwortlich, die Einschränkungen jederzeit einzuhalten. Abfragen für Tabellen mit verletzten Einschränkungen können falsche Ergebnisse zurückgeben.
  • Primärschlüssel dürfen 16 Spalten nicht überschreiten.
  • Fremdschlüssel müssen Werte haben, die in der Spalte der referenzierten Tabelle vorhanden sind. Diese Werte können NULL sein.
  • Primärschlüssel und Fremdschlüssel müssen einen der folgenden Typen haben: BIGNUMERIC, BOOLEAN, DATE, DATETIME, INT64, NUMERIC, STRING oder TIMESTAMP.
  • Primärschlüssel und Fremdschlüssel können nur für Spalten auf oberster Ebene festgelegt werden.
  • Primärschlüssel können nicht benannt werden.
  • Tabellen mit Primärschlüsseleinschränkungen können nicht umbenannt werden.
  • Eine Tabelle kann bis zu 64 Fremdschlüssel haben.
  • Ein Fremdschlüssel kann nicht auf eine Spalte in derselben Tabelle verweisen.
  • Felder, die Teil von Primärschlüsseleinschränkungen oder Fremdschlüsseleinschränkungen sind, können nicht umbenannt werden oder ihren Typ ändern.
  • Wenn Sie eine Tabelle kopieren, klonen, wiederherstellen oder einen Snapshot machen, ohne die Option -a oder --append_table auswählen, werden die Einschränkungen der Quelltabelle kopiert und in die Zieltabelle überschrieben. Wenn Sie die Option -a oder --append_table verwenden, werden nur die Quelltabelleneinträge zur Zieltabelle ohne Tabelleneinschränkungen hinzugefügt.

Nächste Schritte