Gestion des valeurs par défaut des clés primaires

Cette page explique les stratégies à utiliser pour générer des valeurs de clé primaire dans votre table à l'aide d'expressions de valeur par défaut. Les informations de cette page s'appliquent aux bases de données utilisant les dialectes GoogleSQL et PostgreSQL. Ces stratégies présentent les avantages suivants :

  • Réduction du nombre de hotspots
  • Simplification des migrations depuis d'autres bases de données
  • Encapsulation de la logique de clé dans la base de données afin que vous n'ayez pas à vous soucier de sa gestion dans votre application
  • Dans la plupart des cas, remplacement de la nécessité de créer et de gérer vos propres séquences

Méthodes de génération automatique de clés primaires

Pour générer automatiquement des valeurs de clé primaire, vous pouvez utiliser les stratégies suivantes dans une colonne contenant des expressions DEFAULT :

  • Une fonction UUID qui génère des valeurs UUID version 4
  • Des colonnes IDENTITY qui génèrent automatiquement des valeurs entières pour les colonnes clés et non clés
  • SERIAL dans PostgreSQL et AUTO_INCREMENT dans GoogleSQL, qui sont des alias de langage de définition de données (LDD) pour les colonnes IDENTITY
  • Un objet de schéma, SEQUENCE, qui comporte une option bit_reversed_positive SEQUENCE est disponible pour GoogleSQL et PostgreSQL.
  • Une colonne rowid générée automatiquement pour les tables qui ne comportent pas de clés primaires définies par l'utilisateur

Identifiant unique universel (UUID)

Spanner peut générer automatiquement un UUID version 4 à utiliser comme clé primaire. Les UUID sont adaptés aux nouvelles applications et aux tables comportant de nombreuses lignes. Ils sont répartis de manière à peu près uniforme dans l'espace de clés, ce qui évite les hotspots à grande échelle. La génération d'UUID peut créer un grand nombre de valeurs (2122), et chaque valeur est effectivement unique. Par exemple, vous auriez besoin de 2,71 × 1018 valeurs pour une probabilité de collision de 50 %, soit 1 milliard par seconde pendant 86 ans. Cette large plage garantit des valeurs uniques lorsque vous utilisez des UUID dans de grandes tables. Les UUID sont uniques, que vous les génériez dans la base de données ou dans le client. Nous vous recommandons d'utiliser des UUID lorsque cela est possible. Vous pouvez mélanger en toute sécurité les UUID générés par le client et ceux générés par Spanner dans la même table si les UUID générés par le client sont sérialisés en minuscules, conformément à la RFC 4122.

Pour une colonne nécessitant des valeurs par défaut, vous pouvez utiliser la NEW_UUID fonction GoogleSQL ou la gen_random_uuid() fonction PostgreSQL pour les générer. L'exemple suivant montre comment créer une table dans laquelle la colonne de clé FanId comporte un UUID généré dans la colonne de valeur comme valeur par défaut.

GoogleSQL 

CREATE TABLE Fans (
  FanId UUID DEFAULT (NEW_UUID()),
  Name STRING(MAX),
) PRIMARY KEY (FanId);

PostgreSQL 

CREATE TABLE Fans (
  FanId uuid DEFAULT gen_random_uuid(),
  Name text,
  PRIMARY KEY (FanId)
);

GoogleSQL 

INSERT INTO Fans (Name) VALUES ('Melissa Garcia')
THEN RETURN FanId;

PostgreSQL 

INSERT INTO fans (name) VALUES ('Melissa Garcia')
RETURNING fanid;

Cette instruction renvoie un résultat semblable à celui-ci :

FanId
6af91072-f009-4c15-8c42-ebe38ae83751

Pour en savoir plus sur les fonctions UUID générées, consultez la GoogleSQL ou PostgreSQL.

Colonnes IDENTITY

Avec les colonnes IDENTITY, vous pouvez générer automatiquement des valeurs entières pour les colonnes clés et non clés. Les colonnes IDENTITY ne nécessitent pas que vous gériez manuellement une séquence sous-jacente ni que vous gériez la relation entre la colonne et la séquence sous-jacente. Lorsque vous supprimez une colonne d'identité générée automatiquement, Spanner supprime également automatiquement la séquence sous-jacente.

Vous pouvez utiliser des colonnes IDENTITY en fournissant une valeur entière de départ lors de la génération de la séquence ou en laissant Spanner générer la séquence d'entiers pour vous. Pour fournir une valeur entière de départ, vous devez utiliser l'option START COUNTER WITH et une valeur de départ INT64 positive. Spanner utilise cette valeur pour définir la valeur suivante de son compteur de séquence interne généré automatiquement et inverse les bits de la valeur avant de l'insérer dans cette colonne.

Dans Spanner, les colonnes IDENTITY sont compatibles avec GoogleSQL et PostgreSQL.

GoogleSQL

L'exemple suivant montre comment utiliser des colonnes IDENTITY pour créer une colonne de clé primaire entière générée automatiquement pour SingerId lors de la création d'une nouvelle table à l'aide de la commande CREATE TABLE :

CREATE TABLE Singers (
  SingerId INT64 GENERATED BY DEFAULT AS IDENTITY (BIT_REVERSED_POSITIVE),
  Name STRING(MAX),
  Rank INT64
) PRIMARY KEY (SingerId);

Vous pouvez également spécifier le début du compteur pour la colonne à l'aide de l'option START COUNTER WITH. Dans l'exemple suivant, une colonne d'entiers générée automatiquement est créée pour SingerId avec des valeurs positives inversées par bit et un compteur interne qui commence à 1 000.

CREATE TABLE Singers (
  SingerId INT64 GENERATED BY DEFAULT AS IDENTITY (BIT_REVERSED_POSITIVE START COUNTER WITH 1000),
  Name STRING(MAX),
  Rank INT64
) PRIMARY KEY (SingerId);

PostgreSQL

L'exemple suivant montre comment utiliser des colonnes IDENTITY pour créer une colonne d'entiers générée automatiquement pour SingerId lors de la création d'une nouvelle table à l'aide de la commande CREATE TABLE :

CREATE TABLE Singers (
  SingerId bigint GENERATED BY DEFAULT AS IDENTITY (BIT_REVERSED_POSITIVE),
  Name text,
  PRIMARY KEY (SingerId)
);

Vous pouvez également spécifier le début du compteur pour la colonne à l'aide de l'START COUNTER WITH option. Dans l'exemple suivant, une colonne d'entiers générée automatiquement est créée pour SingerId, qui génère des valeurs positives inversées par bit et le compteur interne, avant l'inversion des bits,commence à 1 000.

CREATE TABLE Singers (
  SingerId bigint GENERATED BY DEFAULT AS IDENTITY (BIT_REVERSED_POSITIVE START COUNTER WITH 1000),
  Name text,
  PRIMARY KEY (SingerId)
);

SERIAL et AUTO_INCREMENT

Spanner est compatible avec SERIAL dans PostgreSQL et AUTO_INCREMENT dans GoogleSQL, qui sont des alias LDD pour les IDENTITY colonnes et sont utilisés pour créer des colonnes d'entiers uniques. Vous devez d'abord définir l'option default_sequence_kind de la base de données avant d'utiliser SERIAL ou AUTO_INCREMENT. Vous pouvez utiliser l'instruction SQL suivante pour définir l'option default_sequence_kind de la base de données :

GoogleSQL 

ALTER DATABASE db SET OPTIONS (default_sequence_kind = 'bit_reversed_positive');

CREATE TABLE Singers (
  id INT64 AUTO_INCREMENT PRIMARY KEY,
  name STRING(MAX),
);

PostgreSQL 

ALTER DATABASE db SET spanner.default_sequence_kind = 'bit_reversed_positive';

CREATE TABLE Singers (
  id serial PRIMARY KEY,
  name text
);

Étant donné que SERIAL et AUTO_INCREMENT sont mappés sur des colonnes IDENTITY, ils ne s'affichent pas lorsque vous sérialisez votre schéma. Pour ce schéma, la sortie de GetDatabaseDDL serait la suivante :

GoogleSQL 

ALTER DATABASE db SET OPTIONS (default_sequence_kind = 'bit_reversed_positive');

CREATE TABLE Singers (
  id INT64 GENERATED BY DEFAULT AS IDENTITY,
  name STRING(MAX)
) PRIMARY KEY (id);

PostgreSQL 

ALTER DATABASE db SET spanner.default_sequence_kind = 'bit_reversed_positive';

CREATE TABLE Singers (
  id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  name character varying,
  PRIMARY KEY(id)
);

Séquence inversée par bit

Une séquence inversée par bit est un objet de schéma qui produit une séquence d'entiers et inverse leurs bits. Cet objet utilise l'inversion de bits sur un compteur Spanner interne privé pour garantir l'unicité. Les valeurs inversées par bit qui en résultent permettent d'éviter les hotspots à grande échelle lorsqu'elles sont utilisées dans une clé primaire.

Dans Spanner, vous utilisez des instructions LDD SEQUENCE avec l' bit_reversed_positive attribut pour créer, modifier ou supprimer une séquence qui produit des valeurs positives inversées par bit (GoogleSQL ou PostgreSQL).

Chaque séquence gère un ensemble de compteurs internes et les utilise pour générer une valeur. Le compteur de séquence fournit l'entrée à l'algorithme d'inversion de bits.

Lorsque vous définissez une colonne avec une expression DEFAULT qui utilise la fonction GoogleSQL GET_NEXT_SEQUENCE_VALUE ou la fonction PostgreSQL nextval comme valeur par défaut, Spanner appelle automatiquement la fonction et place les valeurs de sortie inversées par bit dans la colonne. Les séquences inversées par bit sont particulièrement utiles pour les clés primaires, car les valeurs inversées par bit sont réparties uniformément dans l'espace de clés afin de ne pas provoquer de hotspots.

L'exemple suivant montre comment créer une séquence inversée par bit et une table dans laquelle sa colonne de clé utilise la séquence comme valeur par défaut :

GoogleSQL 

CREATE SEQUENCE SingerIdSequence OPTIONS (
  sequence_kind="bit_reversed_positive"
);

CREATE TABLE Singers (
  SingerId INT64 DEFAULT (GET_NEXT_SEQUENCE_VALUE(SEQUENCE SingerIdSequence)),
  Name STRING(MAX),
  Rank INT64
) PRIMARY KEY (SingerId);

PostgreSQL 

CREATE SEQUENCE SingerIdSequence bit_reversed_positive;

CREATE TABLE Singers (
  SingerId bigint DEFAULT nextval('SingerIdSequence'),
  Name text,
  PRIMARY KEY (SingerId)
);

Vous pouvez ensuite utiliser l'instruction SQL suivante pour insérer et renvoyer la valeur de la clé primaire :

GoogleSQL 

INSERT INTO Singers (Name) VALUES ('Example Singer')
THEN RETURN SingerId;

PostgreSQL 

INSERT INTO Singers (name) VALUES ('Example Singer')
RETURNING SingerId;

Cette instruction renvoie un résultat semblable à celui-ci :

SingerId
3458764513820540928

Créer une table sans définir de clé primaire

Si vous ne fournissez aucune clé primaire lors de la création d'une table, Spanner crée une colonne masquée nommée rowid. Cette colonne sert de clé primaire. Par défaut, la rowid colonne est une IDENTITY colonne qui utilise des valeurs INT64 et est soutenue par une séquence inversée par bit pour générer automatiquement des clés.

Vous pouvez créer une table sans définir de clés primaires de la manière suivante :

GoogleSQL 

CREATE TABLE Singers (
  Name STRING(MAX),
  Rank INT64
);

PostgreSQL 

CREATE TABLE Singers (
  Name text,
  Rank bigint
);

Le schéma obtenu est le suivant :

GoogleSQL 

CREATE TABLE Singers (
  Name STRING(MAX),
  Rank INT64,
  rowid INT64 NOT NULL GENERATED BY DEFAULT AS IDENTITY
    (BIT_REVERSED_POSITIVE) HIDDEN
) PRIMARY KEY (rowid);

PostgreSQL 

CREATE TABLE Singers (
  Name text,
  Rank bigint,
  rowid bigint GENERATED BY DEFAULT AS IDENTITY
    (BIT_REVERSED_POSITIVE) NOT NULL HIDDEN,
  PRIMARY KEY (rowid)
);

Vous ne pouvez pas ajouter de clé primaire à une table que vous avez créée précédemment sans définir de clé primaire.

Étant donné que Spanner n'autorise pas deux colonnes portant le même nom dans une table, vous ne pouvez pas définir une nouvelle colonne non clé nommée rowid dans une table que vous avez créée précédemment sans définir de clés primaires. La requête suivante renvoie une erreur :

GoogleSQL 

CREATE TABLE Singers (
  rowid INT64,
  Name STRING(MAX),
);

PostgreSQL 

CREATE TABLE Singers (
  rowid bigint,
  Name text
);

Spanner définit la colonne rowid sur HIDDEN. Cela signifie que la colonne n'apparaît pas dans une requête SELECT * :

GoogleSQL 

SELECT * FROM Singers;

/*-------+------+
 | Name  | Rank |
 +-------+------+
 | Alice | 1    |
 | David | 2    |
 +-------+------*/

PostgreSQL 

SELECT * FROM Singers;

/*-------+------+
 | Name  | Rank |
 +-------+------+
 | Alice | 1    |
 | David | 2    |
 +-------+------*/

Toutefois, vous pouvez interroger explicitement la colonne rowid masquée :

GoogleSQL 

SELECT rowid FROM Singers;

/*---------------------+
 | rowid               |
 +---------------------+
 | 3458764513820540928 |
 +---------------------*/

PostgreSQL 

SELECT rowid FROM Singers;

/*---------------------+
 | rowid               |
 +---------------------+
 | 3458764513820540928 |
 +---------------------*/

Bien que la colonne rowid n'apparaisse pas dans une requête SELECT *, elle est visible dans les tables INFORMATION_SCHEMA.rowid Par exemple, vous pouvez exécuter la requête suivante :

GoogleSQL 

SELECT column_name
FROM information_schema.key_column_usage
WHERE constraint_name LIKE 'PK_%' AND table_name = 'Singers';

/*-------------+
 | column_name |
 +-------------+
 | rowid       |
 +-------------*/

PostgreSQL 

SELECT column_name
FROM information_schema.key_column_usage
WHERE constraint_name LIKE 'PK_%' AND table_name = 'singers';

 /*-------------+
 | column_name |
 +-------------+
 | "rowid"     |
 +-------------*/

Scénarios d'utilisation d'UUID et de séquences comme valeurs par défaut pour les clés primaires

Les scénarios pour les UUID et les séquences incluent les éléments suivants :

  • Nouvelles applications
  • Migrations

Les sections suivantes décrivent chaque scénario.

Nouvelles applications

Pour les nouvelles applications, Spanner recommande d'utiliser un identifiant unique universel (UUID) pour les clés primaires. Pour en savoir plus, consultez la page Utiliser un identifiant unique universel (UUID).

Si votre application existante nécessite des clés INT64 dans GoogleSQL, ou bigint clés dans PostgreSQL, Spanner propose l' objet de schéma de séquence positive inversée par bit (PostgreSQL ou GoogleSQL).

Migrations

Pour migrer des tables vers Spanner, vous disposez des options suivantes :

  • Si vous utilisez des UUID dans votre base de données source, dans Spanner, vous pouvez utiliser une colonne de type UUID et la fonction UUID générée ( GoogleSQL ou PostgreSQL) comme valeur par défaut.
  • Si vous utilisez une clé primaire entière et que votre application n'a besoin que de l'unicité de la clé, vous pouvez utiliser une colonne de clé dans INT64 et une séquence positive inversée par bit pour la valeur par défaut de la clé primaire. Consultez Migrer des colonnes de clés inversées par bit.
  • Si vous utilisez une clé monotone, telle que le type PostgreSQL SERIAL ou l'attribut MySQL AUTO_INCREMENT, et que vous avez besoin de nouvelles clés monotones sur Spanner, vous pouvez utiliser une clé composite, car Spanner ne permet pas de générer des valeurs monotones. Pour en savoir plus, consultez Inverser l'ordre des clés et Hacher la clé unique et répartir les écritures sur des segments logiques.
  • Si votre application inverse manuellement les bits de votre clé INT64 dans GoogleSQL ou de votre clé bigint dans PostgreSQL, vous pouvez utiliser une séquence positive inversée par bit (GoogleSQL ou PostgreSQL) pour générer de nouvelles valeurs de clé. Pour en savoir plus, consultez Migrer des colonnes de clés inversées par bit.

Étape suivante