Rechercher dans les index

Cette page explique comment ajouter et utiliser des index de recherche. La recherche en texte intégral est exécutée sur les entrées de l'index de recherche.

Utiliser les index de recherche

Vous pouvez créer un index de recherche sur toutes les colonnes que vous souhaitez rendre disponibles pour les recherches en texte intégral. Pour créer un index de recherche, utilisez l'instruction LDD CREATE SEARCH INDEX. Pour mettre à jour un index, utilisez l'instruction LDD ALTER SEARCH INDEX. Spanner crée et gère automatiquement l'index de recherche, y compris en ajoutant et en mettant à jour les données dans l'index de recherche dès qu'elles sont modifiées dans la base de données.

Partitions d'index de recherche

Un index de recherche peut être partitionné ou non partitionné, selon le type de requêtes que vous souhaitez accélérer.

  • Un index partitionné est le meilleur choix, par exemple, lorsque l'application interroge une boîte aux lettres. Chaque requête est limitée à une boîte aux lettres spécifique.

  • Une requête non partitionnée est par exemple le meilleur choix lorsqu'une requête porte sur toutes les catégories de produits d'un catalogue de produits.

Cas d'utilisation de l'index de recherche

Outre la recherche en texte intégral, les index de recherche Spanner sont compatibles avec les éléments suivants :

  • Recherches JSON, qui constituent un moyen efficace d'indexer et d'interroger des documents JSON et JSONB.
  • Recherches de sous-chaînes, qui est un type de requête qui recherche une chaîne plus courte (la sous-chaîne) dans un corps de texte plus grand.
  • Combinaison de conditions sur n'importe quel sous-ensemble de données indexées, y compris les correspondances exactes et les données numériques, en une seule analyse d'index.

Pour en savoir plus sur les cas d'utilisation, consultez Index de recherche et index secondaires.

Exemple d'index de recherche

Pour illustrer les fonctionnalités des index de recherche, supposons qu'il existe une table qui stocke des informations sur les albums de musique :

GoogleSQL 

CREATE TABLE Albums (
  AlbumId STRING(MAX) NOT NULL,
  AlbumTitle STRING(MAX)
) PRIMARY KEY(AlbumId);

PostgreSQL 

CREATE TABLE albums (
  albumid character varying NOT NULL,
  albumtitle character varying,
PRIMARY KEY(albumid));

Spanner dispose de plusieurs fonctions de tokenisation qui créent des jetons. Pour modifier la table précédente afin de permettre aux utilisateurs d'effectuer une recherche en texte intégral pour trouver des titres d'albums, utilisez la fonction TOKENIZE_FULLTEXT pour créer des jetons à partir des titres d'albums. Créez ensuite une colonne qui utilise le type de données TOKENLIST pour stocker le résultat de la tokenisation de TOKENIZE_FULLTEXT. Dans cet exemple, nous créons la colonne AlbumTitle_Tokens.

GoogleSQL 

ALTER TABLE Albums
  ADD COLUMN AlbumTitle_Tokens TOKENLIST
  AS (TOKENIZE_FULLTEXT(AlbumTitle)) HIDDEN;

PostgreSQL 

ALTER TABLE albums
  ADD COLUMN albumtitle_tokens spanner.tokenlist
    GENERATED ALWAYS AS (spanner.tokenize_fulltext(albumtitle)) VIRTUAL HIDDEN;

L'exemple suivant utilise le LDD CREATE SEARCH INDEX pour créer un index de recherche (AlbumsIndex) sur les jetons AlbumTitle (AlbumTitle_Tokens) :

GoogleSQL 

CREATE SEARCH INDEX AlbumsIndex
  ON Albums(AlbumTitle_Tokens);

PostgreSQL

Cet exemple utilise CREATE SEARCH INDEX.

CREATE SEARCH INDEX albumsindex ON albums(albumtitle_tokens);

Après avoir ajouté l'index de recherche, utilisez des requêtes SQL pour trouver les albums correspondant aux critères de recherche. Exemple :

GoogleSQL 

SELECT AlbumId
FROM Albums
WHERE SEARCH(AlbumTitle_Tokens, "fifth symphony")

PostgreSQL 

SELECT albumid
FROM albums
WHERE spanner.search(albumtitle_tokens, 'fifth symphony')

Créer et interroger un index de recherche pour un schéma nommé

Vous pouvez créer un index de recherche sur un schéma nommé, comme illustré dans les exemples suivants.

GoogleSQL 

CREATE SCHEMA AlbumSchema;
CREATE TABLE AlbumSchema.Albums (
  AlbumId STRING(MAX) NOT NULL,
  AlbumTitle STRING(MAX),
  AlbumTitle_Tokens TOKENLIST AS (TOKENIZE_FULLTEXT(AlbumTitle)) STORED HIDDEN,
) PRIMARY KEY(AlbumId);

CREATE SEARCH INDEX AlbumSchema.AlbumsIndex ON AlbumSchema.Albums(AlbumTitle_Tokens);

PostgreSQL

Dans PostgreSQL, vous ne pouvez pas utiliser de noms complets pour créer des index sur des schémas nommés.

CREATE SCHEMA AlbumSchema;
CREATE TABLE AlbumSchema.albums (
  albumid character varying NOT NULL,
  albumtitle character varying,
  albumtitle_tokens spanner.tokenlist GENERATED ALWAYS AS (spanner.tokenize_fulltext(albumtitle)) STORED HIDDEN,
  PRIMARY KEY(albumid)
);
CREATE SEARCH INDEX albumsindex ON AlbumSchema.albums(albumtitle_tokens);

Les exemples suivants montrent comment interroger l'index de recherche sur le schéma nommé :

GoogleSQL 

SELECT AlbumId
FROM AlbumSchema.Albums
WHERE SEARCH(AlbumTitle_Tokens, "fifth symphony")

L'exemple suivant utilise un indice de requête pour indiquer à l'optimiseur de requêtes d'utiliser l'index nommé AlbumsIndex lors de l'exécution de la requête.

SELECT AlbumId
FROM AlbumSchema.Albums@{FORCE_INDEX="AlbumSchema.AlbumsIndex"}
WHERE SEARCH(AlbumTitle_Tokens, "fifth symphony")

PostgreSQL

Dans PostgreSQL, vous ne pouvez pas utiliser de noms complets pour créer des index sur des schémas nommés.

SELECT albumid
FROM AlbumSchema.albums
WHERE spanner.search(albumtitle_tokens, 'fifth symphony')

L'exemple suivant utilise un indice de requête pour indiquer à l'optimiseur de requêtes d'utiliser l'index nommé albumsindex lors de l'exécution de la requête.

SELECT albumid
FROM AlbumSchema.albums /*@ FORCE_INDEX = albumsindex */
WHERE spanner.search(albumtitle_tokens, 'fifth symphony')

Cohérence des données

Lorsqu'un index est créé, Spanner utilise des processus automatisés pour renseigner les données afin d'assurer la cohérence. Lorsque les écritures sont validées, les index sont mis à jour dans la même transaction. Spanner effectue automatiquement des vérifications de cohérence des données.

Définitions de schémas d'index de recherche

Les index de recherche sont définis sur une ou plusieurs colonnes TOKENLIST d'une table. Les index de recherche comportent les composants suivants :

  • Table de base : table Spanner qui doit être indexée.
  • Colonne TOKENLIST : ensemble de colonnes qui définissent les jetons à indexer. L'ordre de ces colonnes n'a pas d'importance.

Par exemple, dans l'instruction suivante, la table de base est "Albums". Les colonnes TOKENLIST sont créées sur AlbumTitle (AlbumTitle_Tokens) et Rating (Rating_Tokens).

GoogleSQL 

CREATE TABLE Albums (
  AlbumId STRING(MAX) NOT NULL,
  SingerId INT64 NOT NULL,
  ReleaseTimestamp INT64 NOT NULL,
  AlbumTitle STRING(MAX),
  Rating FLOAT64,
  AlbumTitle_Tokens TOKENLIST AS (TOKENIZE_FULLTEXT(AlbumTitle)) HIDDEN,
  Rating_Tokens TOKENLIST AS (TOKENIZE_NUMBER(Rating)) HIDDEN
) PRIMARY KEY(AlbumId);

PostgreSQL 

CREATE TABLE albums (
  albumid character varying NOT NULL,
  singerid bigint NOT NULL,
  releasetimestamp bigint NOT NULL,
  albumtitle character varying,
  rating double precision,
  albumtitle_tokens spanner.tokenlist GENERATED ALWAYS AS (spanner.tokenize_fulltext(albumtitle)) VIRTUAL HIDDEN,
  rating_tokens spanner.tokenlist GENERATED ALWAYS AS (spanner.tokenize_fulltext(rating)) VIRTUAL HIDDEN,
PRIMARY KEY(AlbumId));

Utilisez l'instruction CREATE SEARCH INDEX suivante pour créer un index de recherche à l'aide des jetons pour AlbumTitle et Rating :

GoogleSQL 

CREATE SEARCH INDEX AlbumsIndex
ON Albums(AlbumTitle_Tokens, Rating_Tokens)
PARTITION BY SingerId
ORDER BY ReleaseTimestamp DESC

PostgreSQL 

CREATE SEARCH INDEX albumsindex
ON albums(albumtitle_tokens, rating_tokens)
PARTITION BY singerid
ORDER BY releasetimestamp DESC

Les index de recherche disposent des options suivantes :

  • Partitions : groupe facultatif de colonnes qui divisent l'index de recherche. L'interrogation d'un index partitionné est souvent beaucoup plus efficace que l'interrogation d'un index non partitionné. Pour en savoir plus, consultez Partitionner des index de recherche.
  • Colonne d'ordre de tri : colonne INT64 facultative qui établit l'ordre de récupération à partir de l'index de recherche. Pour en savoir plus, consultez Ordre de tri de l'index de recherche.
  • Entrelacement : comme pour les index secondaires, vous pouvez entrelacer les index de recherche. Les index de recherche entrelacés utilisent moins de ressources pour écrire et joindre la table de base. Pour en savoir plus, consultez Index de recherche entrelacés.
  • Clause d'options : liste de paires clé/valeur qui remplace les paramètres par défaut de l'index de recherche.

Mise en page interne des index de recherche

Un élément important de la représentation interne des index de recherche est un docid, qui sert de représentation efficace du stockage de la clé primaire de la table de base, qui peut être arbitrairement longue. C'est également ce qui crée l'ordre de la mise en page des données internes en fonction des colonnes ORDER BY de l'instruction CREATE SEARCH INDEX fournies par l'utilisateur. Il est représenté par un ou deux entiers de 64 bits.

Les index de recherche sont implémentés en interne sous la forme d'un mappage à deux niveaux :

  1. Jetons vers ID de document
  2. Docids vers les clés primaires de la table de base

Ce schéma permet de réaliser d'importantes économies de stockage, car Spanner n'a pas besoin de stocker la clé primaire complète de la table de base pour chaque paire <token, document>.

Il existe deux types d'index physiques qui implémentent les deux niveaux de mappage :

  1. Un index secondaire qui mappe les clés de partition et un docid à la clé primaire de la table de base. Dans l'exemple de la section précédente, cela mappe {SingerId, ReleaseTimestamp, uid} sur {AlbumId}. L'index secondaire stocke également toutes les colonnes spécifiées dans la clause STORING de CREATE SEARCH INDEX.
  2. Un index de jetons qui mappe les jetons aux ID de document, semblable aux index inversés dans la documentation sur la récupération d'informations. Spanner gère un index de jetons distinct pour chaque TOKENLIST de l'index de recherche. Logiquement, les index de jetons gèrent des listes de docid pour chaque jeton au sein de chaque partition (appelées listes de publications dans la récupération d'informations). Les listes sont classées par jetons pour une récupération rapide, et au sein des listes, docid est utilisé pour le classement. Les index de jetons individuels sont un détail d'implémentation qui n'est pas exposé via les API Spanner.

Spanner accepte les options suivantes pour docid.

index de recherche ID du document Comportement
La clause ORDER BY est omise pour l'index de recherche. {uid} Spanner ajoute une valeur unique (UID) masquée pour identifier chaque ligne.
ORDER BY column {column, uid} Spanner ajoute la colonne UID pour départager les lignes ayant les mêmes valeurs column dans une partition.

Remarques concernant l'utilisation :

  • La colonne UID interne n'est pas exposée via l'API Spanner.
  • Dans les index où l'UID n'est pas ajouté, les transactions qui ajoutent une ligne avec un ordre de tri (partition,ordre de tri) déjà existant échouent.

Prenons l'exemple des données suivantes :

ID de l'album SingerId ReleaseTimestamp SongTitle
a1 1 997 De belles journées
a2 1 743 Beaux yeux

En supposant que la colonne de tri préalable est dans l'ordre croissant, le contenu de l'index de jeton partitionné par SingerId partitionne le contenu de l'index de jeton de la manière suivante :

SingerId _token ReleaseTimestamp uid
1 magnifique 743 uid1
1 magnifique 997 uid2
1 jours 743 uid1
1 yeux 997 uid2

Segmentation de l'index de recherche

Lorsque Spanner divise une table, il distribue les données de l'index de recherche de sorte que tous les jetons d'une ligne de table de base particulière se trouvent dans la même division. En d'autres termes, l'index de recherche est segmenté par document. Cette stratégie de segmentation a des conséquences importantes sur les performances :

  1. Le nombre de serveurs avec lesquels chaque transaction communique reste constant, quel que soit le nombre de jetons ou de colonnes TOKENLIST indexées.
  2. Les requêtes de recherche impliquant plusieurs expressions conditionnelles sont exécutées indépendamment sur chaque division, ce qui évite la surcharge de performances associée à une jointure distribuée.

Les index de recherche ont deux modes de distribution :

  • Segmentation uniforme (par défaut). Dans la segmentation uniforme, les données indexées pour chaque ligne de table de base sont attribuées de manière aléatoire à une division d'index d'une partition.
  • Segmentation par ordre de tri. Dans le cas du partitionnement par ordre de tri, les données de chaque ligne de table de base sont attribuées à une division d'index d'une partition en fonction des colonnes ORDER BY (c'est-à-dire les colonnes de pré-tri). Par exemple, dans le cas d'un ordre de tri décroissant, toutes les lignes ayant les valeurs d'ordre de tri les plus élevées apparaissent sur la première division d'index d'une partition, et le groupe de valeurs d'ordre de tri suivant le plus élevé sur la division suivante.

Ces modes de segmentation s'accompagnent d'un compromis entre les risques de hotspots et le coût des requêtes :

  • Les index de recherche partitionnés uniformes sont recommandés lorsque les modèles de lecture ou d'écriture dans l'index de recherche peuvent entraîner des points d'accès. Le partitionnement uniforme limite les hotspots en répartissant uniformément la charge de lecture et d'écriture entre les divisions, mais cela peut augmenter l'utilisation des ressources lors de l'exécution des requêtes. Dans les index de recherche segmentés uniformément, les requêtes doivent lire tous les fractionnements d'une partition, en raison de la distribution aléatoire des données. Lors de l'accès à des index segmentés de manière uniforme, Spanner lit toutes les divisions en parallèle pour réduire la latence globale des requêtes.
  • Les index de recherche segmentés par ordre de tri sont préférables lorsque les modèles de lecture ou d'écriture ne sont pas susceptibles de provoquer des hotspots. Cette approche peut réduire le coût des requêtes dont le ORDER BY correspond au ORDER BY de l'index et qui spécifient une valeur LIMIT relativement faible. Lors de l'exécution de telles requêtes, Spanner lit de manière incrémentielle à partir des premières divisions d'une partition, et la requête peut se terminer sans lire toutes les divisions lorsque LIMIT peut être satisfait rapidement.

  • Le mode de segmentation d'un index de recherche est configuré à l'aide de la clause OPTIONS.

GoogleSQL 

CREATE SEARCH INDEX AlbumsIndex
ON Albums(AlbumTitle_Tokens, Rating_Tokens)
PARTITION BY SingerId
ORDER BY ReleaseTimestamp DESC
OPTIONS (sort_order_sharding = true);

PostgreSQL

Le mode de segmentation d'un index de recherche est configuré à l'aide de la clause WITH.

CREATE SEARCH INDEX albumsindex
ON albums(albumtitle_tokens, rating_tokens)
PARTITION BY singerid
ORDER BY releasetimestamp DESC
WITH (sort_order_sharding = true);

Lorsque sort_order_sharding=false est défini ou n'est pas spécifié, l'index de recherche est créé à l'aide d'un partitionnement uniforme.

Index de recherche entrelacés

Comme pour les index secondaires, vous pouvez entrelacer des index de recherche dans une table parente de la table de base. La principale raison d'utiliser des index de recherche entrelacés est de colocaliser les données de la table de base avec les données d'index pour les petites partitions. Cette colocation opportuniste présente les avantages suivants :

  • Les écritures n'ont pas besoin d'effectuer une validation en deux phases.
  • Les jointures inverses de l'index de recherche avec la table de base ne sont pas distribuées.

Les index de recherche entrelacés sont soumis aux restrictions suivantes :

  1. Seuls les index segmentés par ordre de tri peuvent être entrelacés.
  2. Les index de recherche ne peuvent être entrelacés que dans des tables de premier niveau (pas dans des tables enfants).
  3. Comme pour les tables entrelacées et les index secondaires, faites de la clé de la table parente un préfixe des colonnes PARTITION BY dans l'index de recherche entrelacé.

Définir un index de recherche entrelacé

L'exemple suivant montre comment définir un index de recherche entrelacé :

GoogleSQL 

CREATE TABLE Singers (
  SingerId INT64 NOT NULL
) PRIMARY KEY(SingerId);

CREATE TABLE Albums (
  SingerId INT64 NOT NULL,
  AlbumId STRING(MAX) NOT NULL,
  AlbumTitle STRING(MAX),
  AlbumTitle_Tokens TOKENLIST AS (TOKENIZE_FULLTEXT(AlbumTitle)) HIDDEN
) PRIMARY KEY(SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

CREATE SEARCH INDEX AlbumsIndex
ON Albums(AlbumTitle_Tokens)
PARTITION BY SingerId,
INTERLEAVE IN Singers
OPTIONS (sort_order_sharding = true);

PostgreSQL 

CREATE TABLE singers(
  singerid bigint NOT NULL
PRIMARY KEY(singerid));

CREATE TABLE albums(
  singerid bigint NOT NULL,
  albumid character varying NOT NULL,
  albumtitle character varying,
  albumtitle_tokens spanner.tokenlist
  GENERATED ALWAYS
AS (
  spanner.tokenize_fulltext(albumtitle)
) VIRTUAL HIDDEN,
  PRIMARY KEY(singerid, albumid)),
INTERLEAVE IN PARENT singers ON DELETE CASCADE;

CREATE
  SEARCH INDEX albumsindex
ON
  albums(albumtitle_tokens)
  PARTITION BY singerid INTERLEAVE IN singers WITH(sort_order_sharding = true);

Ordre de tri de l'index de recherche

Les exigences relatives à la définition de l'ordre de tri de l'index de recherche sont différentes de celles des index secondaires.

Prenons l'exemple de la table suivante :

GoogleSQL 

CREATE TABLE Albums (
  AlbumId STRING(MAX) NOT NULL,
  ReleaseTimestamp INT64 NOT NULL,
  AlbumName STRING(MAX),
  AlbumName_Token TOKENLIST AS (TOKEN(AlbumName)) HIDDEN
) PRIMARY KEY(AlbumId);

PostgreSQL 

CREATE TABLE albums (
  albumid character varying NOT NULL,
  releasetimestamp bigint NOT NULL,
  albumname character varying,
  albumname_token spanner.tokenlist
      GENERATED ALWAYS AS(spanner.token(albumname)) VIRTUAL HIDDEN,
PRIMARY KEY(albumid));

L'application peut définir un index secondaire pour rechercher des informations à l'aide de AlbumName trié par ReleaseTimestamp :

CREATE INDEX AlbumsSecondaryIndex ON Albums(AlbumName, ReleaseTimestamp DESC);

L'index de recherche équivalent se présente comme suit (il utilise la tokenisation par correspondance exacte, car les index secondaires ne sont pas compatibles avec les recherches en texte intégral) :

CREATE SEARCH INDEX AlbumsSearchIndex
ON Albums(AlbumName_Token)
ORDER BY ReleaseTimestamp DESC;

L'ordre de tri de l'index de recherche doit respecter les exigences suivantes :

  1. N'utilisez que des colonnes INT64 pour l'ordre de tri d'un index de recherche. Les colonnes de taille arbitraire utilisent trop de ressources dans l'index de recherche, car Spanner doit stocker un docid à côté de chaque jeton. Plus précisément, la colonne d'ordre de tri ne peut pas utiliser le type TIMESTAMP, car TIMESTAMP utilise une précision en nanosecondes qui ne tient pas dans un entier de 64 bits.

  2. Les colonnes d'ordre de tri ne doivent pas être NULL. Il existe deux façons de répondre à cette exigence :

    1. Déclarez la colonne d'ordre de tri comme NOT NULL.
    2. Configurez l'index pour exclure les valeurs NULL.

Un code temporel est souvent utilisé pour déterminer l'ordre de tri. Une pratique courante consiste à utiliser des microsecondes depuis l'epoch Unix pour ces codes temporels.

Les applications récupèrent généralement les données les plus récentes en premier à l'aide d'un index de recherche trié par ordre décroissant.

Index de recherche filtrés par NULL

Les index de recherche peuvent utiliser la syntaxe WHERE column_name IS NOT NULL pour exclure les lignes de la table de base. Le filtrage NULL peut s'appliquer aux clés de partitionnement, aux colonnes d'ordre de tri et aux colonnes stockées. Le filtrage NULL sur les colonnes de tableaux stockés n'est pas autorisé.

Exemple

GoogleSQL 

CREATE SEARCH INDEX AlbumsIndex
ON Albums(AlbumTitle_Tokens)
STORING (Genre)
WHERE Genre IS NOT NULL

PostgreSQL 

CREATE SEARCH INDEX albumsindex
ON albums(albumtitle_tokens)
INCLUDE (genre)
WHERE genre IS NOT NULL

La requête doit spécifier la condition de filtrage NULL (Genre IS NOT NULL pour cet exemple) dans la clause WHERE. Sinon, l'optimiseur de requête ne peut pas utiliser l'index de recherche. Pour en savoir plus, consultez Exigences concernant les requêtes SQL.

L'utilisation du filtre WHERE <column> IS NOT NULL dans la définition de l'index garantit que seules les valeurs non nulles sont indexées. Cela permet à l'optimiseur de requêtes d'utiliser efficacement l'index pour les requêtes pertinentes, même si la condition IS NOT NULL ne figure pas explicitement dans la clause WHERE de la requête.

Utilisez le filtrage NULL sur une colonne générée pour exclure des lignes en fonction de critères arbitraires. Pour en savoir plus, consultez Créer un index partiel à l'aide d'une colonne générée.

Étapes suivantes