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 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 de 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 lorsque l'application interroge une boîte aux lettres électronique, par exemple. Chaque requête est limitée à une boîte aux lettres spécifique.

• Une requête non partitionnée est le meilleur choix, par exemple, 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

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

• Les recherches JSON, qui constituent un moyen efficace d'indexer et d'interroger les documents JSON et JSONB.
• Les recherches de sous-chaînes, qui sont un type de requête qui recherche une chaîne plus courte (la sous-chaîne) dans un corps de texte plus long.
• Combinaison de conditions sur n'importe quel sous-ensemble de données indexées, y compris les correspondances exactes et 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 capacités des index de recherche, supposons qu'il existe une table qui stocke des informations sur les albums de musique :

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

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

Spanner propose plusieurs fonctions de tokenisation qui créent des jetons. Pour modifier le tableau précédent 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 contenir le résultat de la tokenisation à partir de TOKENIZE_FULLTEXT. Pour cet exemple, nous allons créer la colonne AlbumTitle_Tokens.

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

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

CREATE SEARCH INDEX AlbumsIndex
  ON Albums(AlbumTitle_Tokens);

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 :

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

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

Cohérence des données

Lorsqu'un index est créé, Spanner utilise des processus automatisés pour remplir 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 se composent des éléments suivants :

• Table de base : table Spanner à indexer.
• 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).

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

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 :

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

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

Les index de recherche proposent les options suivantes :

• Partitions : groupe facultatif de colonnes qui divisent l'index de recherche. Interroger un index partitionné est souvent beaucoup plus efficace qu'interroger un index non partitionné. Pour en savoir plus, consultez Partitionner les index de recherche.
• Colonne 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 des 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 en termes de 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 fournies par l'utilisateur dans l'instruction CREATE SEARCH INDEX. Elle est représentée par un ou deux nombres entiers de 64 bits.

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

1. Jetons vers docids
2. ID de document 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. 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 docid, semblable aux index inversés dans la documentation sur la recherche d'informations. Spanner gère un index de jetons distinct pour chaque TOKENLIST de l'index de recherche. Logiquement, les index de jetons conservent des listes de docid pour chaque jeton dans chaque partition (appelées listes de postings dans la recherche d'informations). Les listes sont triées par jetons pour une récupération rapide, et l'ID de document est utilisé pour le tri dans les listes. Les index de jetons individuels sont un détail d'implémentation qui n'est pas exposé par les API Spanner.

Spanner accepte les quatre options suivantes pour docid.

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 une paire (partition,ordre de tri) déjà existante échouent.

Par exemple, examinez les données suivantes :

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

Partitionnement 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 fragmenté par document. Cette stratégie de partitionnement a des implications importantes en termes de 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 fractionnement, ce qui évite la surcharge de performances associée à une jointure distribuée.

Les index de recherche comportent deux modes de distribution :

• Segmentation uniforme (par défaut) : Dans le partitionnement uniforme, les données indexées de chaque ligne de table de base sont attribuées de manière aléatoire à une partition d'index.
• Segmentation par ordre de tri. Dans le partitionnement par ordre de tri, les données de chaque ligne de la table de base sont attribuées à une fraction 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 avec les valeurs d'ordre de tri les plus élevées apparaissent dans la première fraction d'index d'une partition, et le groupe de valeurs d'ordre de tri le plus élevé suivant apparaît dans la fraction suivante.

Ces modes de partitionnement présentent un compromis entre les risques de points chauds et le coût des requêtes :

• Les index de recherche partitionnés de manière uniforme sont recommandés lorsque les modèles de lecture ou d'écriture dans l'index de recherche peuvent entraîner des points chauds. Le partitionnement uniforme atténue les points chauds en répartissant uniformément la charge de lecture et d'écriture sur les divisions, mais cela peut augmenter l'utilisation des ressources lors de l'exécution des requêtes. Dans les index de recherche partitionnés de manière uniforme, les requêtes doivent lire toutes les divisions d'une partition en raison de la distribution aléatoire des données. Lorsque vous accédez à des index fragmentés de manière uniforme, Spanner lit tous les fractionnements en parallèle pour réduire la latence globale des requêtes.
• Les index de recherche partitionnés par ordre de tri sont préférables lorsque les modèles de lecture ou d'écriture sont peu susceptibles de provoquer des points chauds. Cette approche peut réduire le coût des requêtes dont le ORDER BY correspond au ORDER BY de l'index et spécifie un LIMIT relativement faible. Lors de l'exécution de telles requêtes, Spanner lit les premiers fractionnements d'une partition de manière incrémentielle. La requête peut se terminer sans lire tous les fractionnements lorsque LIMIT peut être satisfait tôt.

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

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

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 non spécifié, l'index de recherche est créé à l'aide du partitionnement uniforme.

Index de recherche entrelacés

Comme 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 un commit en deux phases.
• Les jointures inversées 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 fragmentés par ordre de tri peuvent être entrelacés.
2. Les index de recherche ne peuvent être entrelacés que dans les tables de premier niveau (et non dans les tables enfants).
3. Comme pour les tables et les index secondaires entrelacés, la clé de la table parente doit être un préfixe des colonnes PARTITION BY de l'index de recherche entrelacé.

Définir un index de recherche entrelacé

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

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

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 comme exemple le tableau suivant :

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

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 les 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. Pour répondre à cette exigence, vous avez deux possibilités :

   1. Déclarez la colonne d'ordre de tri en tant que 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'époque 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 les valeurs NULL

Les index de recherche peuvent utiliser la syntaxe WHERE column_name IS NOT NULL pour exclure des 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 n'est pas autorisé sur les colonnes de tableaux stockés.

Exemple

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

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 pourra pas utiliser l'index de recherche. Pour en savoir plus, consultez Exigences concernant les requêtes SQL.

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

• En savoir plus sur la tokenisation et les tokenizers Spanner
• En savoir plus sur les index numériques
• En savoir plus sur les index JSON
• En savoir plus sur le partitionnement des index