Index secondaires

Dans une base de données Cloud Spanner, Cloud Spanner crée automatiquement un index pour la clé primaire de chaque table. Par exemple, vous n'avez rien à faire pour indexer la clé primaire de Singers, car elle est automatiquement indexée pour vous.

Vous pouvez également créer des index secondaires pour d'autres colonnes. L'ajout d'un index secondaire sur une colonne facilite la recherche des données dans cette colonne. Par exemple, si vous devez rechercher rapidement un ensemble de valeurs SingerId pour une plage donnée de valeurs LastName, créez un index secondaire sur LastName. Ainsi, Cloud Spanner n'a pas besoin d'analyser l'ensemble de la table.

Si la recherche de l'exemple ci-dessus est effectuée au cours d'une transaction en lecture/écriture, pour plus d'efficacité, elle évite également de verrouiller la table entière, ce qui permet des insertions et des mises à jour simultanées dans la table pour les lignes en dehors de la plage de recherche LastName.

Cloud Spanner stocke les données suivantes dans chaque index secondaire :

  • Toutes les colonnes clés de la table de base
  • Toutes les colonnes incluses dans l'index
  • Toutes les colonnes spécifiées dans la clause facultative STORING de la définition de l'index

Au fil du temps, Cloud Spanner analyse vos tables pour s'assurer que vos index secondaires sont utilisés pour les requêtes appropriées.

Ajouter un index secondaire

Le moment le plus efficace pour ajouter un index secondaire est lors de la création de la table. Pour créer une table et ses index en même temps, envoyez les instructions DDL pour la nouvelle table et les nouveaux index dans une seule requête à Cloud Spanner.

Dans Cloud Spanner, vous pouvez également ajouter un index secondaire à une table existante pendant que la base de données continue de diffuser du trafic. Comme toute autre modification de schéma dans Cloud Spanner, l'ajout d'un index à une base de données existante ne nécessite pas de mettre cette base de données hors connexion et n'entraîne pas le verrouillage de colonnes ou de tables entières.

Chaque fois qu'un nouvel index est ajouté à une table existante, Cloud Spanner remplit automatiquement ou renseigne l'index pour afficher une vue à jour des données indexées. Cloud Spanner gère ce processus de remplissage à votre place et utilise des ressources supplémentaires pendant le remplissage de l'index.

La création d'un index peut prendre de quelques minutes à plusieurs heures. La création d'index étant une mise à jour du schéma, elle est soumise aux mêmes contraintes de performances que toute autre mise à jour de schéma. Le temps nécessaire à la création d'un index secondaire dépend de plusieurs facteurs :

  • La taille de l'ensemble de données
  • Capacité de calcul de l'instance
  • La charge sur l'instance

Pour consulter la progression d'un processus de remplissage d'index, reportez-vous à la section Progression.

Notez que l'utilisation de la colonne commit timestamp en tant que première partie de l'index secondaire peut créer des hotspots et réduire les performances en écriture.

Utilisez l'instruction CREATE INDEX pour définir un index secondaire dans votre schéma. Voici quelques exemples :

Pour indexer tous les chanteurs (Singers) de la base de données par leur prénom et leur nom :

CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName)

Pour créer un index de tous les titres (Songs) de la base de données selon la valeur SongName :

CREATE INDEX SongsBySongName ON Songs(SongName)

Pour n'indexer que les titres d'un chanteur particulier, entrelacez l'index dans la table Singers à l'aide de la clause INTERLEAVE IN :

CREATE INDEX SongsBySingerSongName ON Songs(SingerId, SongName),
    INTERLEAVE IN Singers

Pour n'indexer que les titres d'un album particulier :

CREATE INDEX SongsBySingerAlbumSongName ON Songs(SingerId, AlbumId, SongName),
    INTERLEAVE IN Albums

Pour indexer par ordre décroissant de SongName :

CREATE INDEX SongsBySingerAlbumSongNameDesc ON Songs(SingerId, AlbumId, SongName DESC),
    INTERLEAVE IN Albums

Notez que l'annotation DESC ci-dessus ne s'applique qu'à SongName. Pour indexer par ordre décroissant d'autres clés d'index, annotez-les avec DESC : SingerId DESC, AlbumId DESC.

Notez également que PRIMARY_KEY est un mot réservé ne pouvant pas être utilisé comme nom d'index. Il s'agit du nom donné à l'index pseudo-index établi lors de la création d'une table avec la spécification PRIMARY KEY.

Pour plus de détails et de bonnes pratiques concernant le choix des index non entrelacés et des index entrelacés, consultez les sections Options d'index et Utiliser un index entrelacé sur une colonne dont la valeur augmente ou diminue de façon linéaire.

Afficher la progression du remplissage de l'index

Étapes à suivre pour afficher la progression du remplissage de l'index

  1. Un processus de remplissage d'index fait partie d'une opération de longue durée schema-update, car l'ajout d'un index secondaire nécessite une mise à jour du schéma. Vous pouvez afficher la progression du remplissage d'index à l'aide de l'ID d'opération. Si vous ne disposez pas de l'ID d'opération, recherchez-le à l'aide de la commande gcloud spanner operation list:

    gcloud spanner operations list --instance=INSTANCE --database=DATABASE

    Consignes d'utilisation :

    • Pour limiter la liste des opérations renvoyées par cette commande, spécifiez l'option --filter. Par exemple, utilisez le filtre suivant pour renvoyer les opérations de mise à jour du schéma.

      --filter="@TYPE:UpdateDatabaseDdlMetadata"

      Pour en savoir plus sur la syntaxe des filtres, consultez la page gcloud topic filters. Pour en savoir plus sur le filtrage des opérations de base de données, consultez le champ filter dans ListDatabaseOperationsRequest.

    Voici un exemple de résultat:

    OPERATION_ID     STATEMENTS                                                                                          DONE   @TYPE
_auto_op_123456  CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName)                                 False  UpdateDatabaseDdlMetadata
                 CREATE INDEX SongsBySingerAlbumSongName ON Songs(SingerId, AlbumId, SongName), INTERLEAVE IN Albums
_auto_op_234567                                                                                                      True   CreateDatabaseMetadata

  2. Pour suivre la progression d'un ou de plusieurs processus de remplissage d'index secondaires, utilisez gcloud spanner operation describe:

    gcloud spanner operations describe _auto_op_123456 \
    --instance=INSTANCE \
    --database=DATABASE

    Voici un exemple de résultat d'une opération de longue durée avec mise à jour du schéma qui contient deux processus de remplissage d'index:

    done: true
metadata:
  '@type': type.googleapis.com/google.spanner.admin.database.v1.UpdateDatabaseDdlMetadata
  commitTimestamps:
  - '2021-01-22T21:58:42.912540Z'
  database: projects/my-project/instances/test-instance/databases/example-db
  progress:
  - endTime: '2021-01-22T21:58:42.912540Z'
    progressPercent: 100
    startTime: '2021-01-22T21:58:11.053996Z'
  - progressPercent: 67
    startTime: '2021-01-22T21:58:11.053996Z'
  statements:
  - CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName)
  - CREATE INDEX SongsBySingerAlbumSongName ON Songs(SingerId, AlbumId, SongName), INTERLEAVE IN Albums
name: projects/my-project/instances/test-instance/databases/example-db/operations/_auto_op_123456
response:
  '@type': type.googleapis.com/google.protobuf.Empty

    La progression de chaque instruction de remplissage d'index est indiquée dans le champ progress. Chaque instruction du tableau d'instructions comporte un champ correspondant dans le tableau de progression. Les champs startTime et endTime sont renseignés s'ils sont disponibles. L'ordre des tableaux de progression correspond à l'ordre des tableaux d'instructions.

Scénarios lors de l'affichage de la progression du remplissage des index

Il existe différents scénarios auxquels un utilisateur peut être confronté lorsqu'il tente de vérifier la progression d'un remplissage d'index. Les instructions de création d'index nécessitant un remplissage d'index font partie d'opérations de mise à jour de schéma, et plusieurs instructions peuvent faire partie d'une opération de mise à jour de schéma.

Le premier scénario est le plus simple, c'est-à-dire lorsque la déclaration de création d'index est la première instruction de l'opération de mise à jour du schéma. Le champ startTime de l'instruction de création d'index sera renseigné avec l'heure de début de l'opération de mise à jour du schéma, car il s'agit de la première instruction. Il sera également le premier à être traité et exécuté. à l'ordre d'exécution. Le champ progressPercent de l'instruction de création d'index est renseigné lorsque la progression du remplissage de l'index est supérieure à 0%. Le champ endTime est renseigné une fois que l'instruction a été validée.

Le deuxième scénario intervient lorsque l'instruction de création d'index n'est pas la première instruction de l'opération de mise à jour du schéma. Aucun champ lié à l'instruction de création d'index n'est renseigné tant que les instructions précédentes n'ont pas été validées en raison de l'ordre d'exécution. Une fois la ou les instructions précédentes validées, le champ startTime du champ de progression de l'instruction de création d'index sera le premier attribut renseigné. Le champ progressPercent de l'instruction de création d'index est ensuite renseigné lorsque la progression du remplissage de l'index est supérieure à 0%. Le champ endTime est renseigné une fois que l'instruction a été validée.

Annuler la création de l'index

Vous pouvez utiliser le SDK Cloud pour annuler la création de l'index. Pour extraire la liste des opérations de mise à jour du schéma d'une base de données Cloud Spanner, utilisez la commande gcloud spanner operations list et ajoutez l'option --filter :

gcloud spanner operations list \
    --instance=INSTANCE \
    --database=DATABASE \
    --filter="@TYPE:UpdateDatabaseDdlMetadata"

Recherchez le OPERATION_ID pour l'opération que vous souhaitez annuler, puis utilisez la commande gcloud spanner operations cancel pour l'annuler :

gcloud spanner operations cancel OPERATION_ID \
    --instance=INSTANCE \
    --database=DATABASE

Afficher des index existants

Pour afficher des informations sur des index existants dans une base de données, vous pouvez utiliser Google Cloud Console ou l'outil de ligne de commande gcloud :

Console

  1. Accédez à la page Instances de Cloud Spanner dans Cloud Console.

    Accéder à la page Instances

  2. Cliquez sur le nom de l'instance que vous souhaitez afficher.

  3. Dans le volet de gauche, cliquez sur la base de données à afficher, puis sur la table que vous souhaitez consulter.

  4. Cliquez sur l'onglet Index. Cloud Console affiche une liste d'index.

  5. Facultatif : Pour obtenir des détails sur un index, par exemple les colonnes qu'il contient, cliquez sur le nom de l'index.

gcloud

Utilisez la commande gcloud spanner databases ddl describe :

    gcloud spanner databases ddl describe DATABASE \
        --instance=INSTANCE

L'outil gcloud affiche les instructions DDL (Data Definition Language) pour créer les tables et les index de la base de données. Les instructions CREATE INDEX décrivent les index existants. Exemple :

    --- |-
  CREATE TABLE Singers (
    SingerId INT64 NOT NULL,
    FirstName STRING(1024),
    LastName STRING(1024),
    SingerInfo BYTES(MAX),
  ) PRIMARY KEY(SingerId)
---
  CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName)

Requêtes avec un index spécifique

Les sections suivantes expliquent comment spécifier un index dans une instruction SQL et avec l'interface de lecture pour Cloud Spanner. Les exemples de ces sections supposent que vous avez ajouté une colonne MarketingBudget à la table Albums et que vous avez créé un index appelé AlbumsByAlbumTitle :

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

CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle);

Spécifier un index dans une instruction SQL

Lorsque vous utilisez SQL pour interroger une table Cloud Spanner, Cloud Spanner utilise automatiquement tous les index susceptibles d'améliorer l'efficacité de la requête. Par conséquent, il n'est pas nécessaire de spécifier un index pour les requêtes SQL. Toutefois, pour les requêtes critiques pour votre charge de travail, nous vous recommandons d'utiliser les instructions FORCE_INDEX dans vos instructions SQL pour obtenir des performances plus cohérentes.

Dans certains cas, Cloud Spanner peut choisir un index qui entraîne une augmentation de la latence des requêtes. Si vous avez suivi la procédure de dépannage des régressions de performances et vérifié qu'il est judicieux d'essayer un autre index pour la requête, vous pouvez spécifier l'index.

Pour spécifier un index dans une instruction SQL, utilisez FORCE_INDEX pour fournir une directive d'index. Les directives d'index utilisent la syntaxe suivante :

FROM MyTable@{FORCE_INDEX=MyTableIndex}

Vous pouvez également utiliser une directive d'index pour indiquer à Cloud Spanner d'analyser la table de base au lieu d'utiliser un index :

FROM MyTable@{FORCE_INDEX=_BASE_TABLE}

L'exemple suivant illustre une requête SQL qui spécifie un index :

SELECT AlbumId, AlbumTitle, MarketingBudget
    FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}
    WHERE AlbumTitle >= "Aardvark" AND AlbumTitle < "Goo";

Une directive d'index peut obliger le processeur de requêtes de Cloud Spanner à lire des colonnes supplémentaires requises par la requête, mais qui ne sont pas stockées dans l'index. Le processeur de requêtes récupère ces colonnes en joignant l'index et la table de base. Pour éviter cette jointure supplémentaire, utilisez une clause STORING pour stocker les colonnes supplémentaires dans l'index.

Par exemple, dans l'exemple ci-dessus, la colonne MarketingBudget n'est pas stockée dans l'index, mais la requête SQL sélectionne cette colonne. Par conséquent, Cloud Spanner doit rechercher la colonne MarketingBudget dans la table de base, puis la joindre aux données de l'index pour renvoyer les résultats de la requête.

Cloud Spanner génère une erreur si la directive d'index présente l'un des problèmes suivants :

Les exemples suivants montrent comment écrire et exécuter des requêtes qui récupèrent les valeurs de AlbumId, AlbumTitle et MarketingBudget à l'aide de l'index AlbumsByAlbumTitle :

C++

void QueryUsingIndex(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  spanner::SqlStatement select(
      "SELECT AlbumId, AlbumTitle, MarketingBudget"
      " FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}"
      " WHERE AlbumTitle >= @start_title AND AlbumTitle < @end_title",
      {{"start_title", spanner::Value("Aardvark")},
       {"end_title", spanner::Value("Goo")}});
  using RowType =
      std::tuple<std::int64_t, std::string, absl::optional<std::int64_t>>;
  auto rows = client.ExecuteQuery(std::move(select));
  for (auto const& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::runtime_error(row.status().message());
    std::cout << "AlbumId: " << std::get<0>(*row) << "\t";
    std::cout << "AlbumTitle: " << std::get<1>(*row) << "\t";
    auto marketing_budget = std::get<2>(*row);
    if (marketing_budget) {
      std::cout << "MarketingBudget: " << *marketing_budget << "\n";
    } else {
      std::cout << "MarketingBudget: NULL\n";
    }
  }
  std::cout << "Read completed for [spanner_query_data_with_index]\n";
}

C#

string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
    var cmd = connection.CreateSelectCommand(
        "SELECT AlbumId, AlbumTitle, MarketingBudget FROM Albums@ "
        + "{FORCE_INDEX=AlbumsByAlbumTitle} "
        + $"WHERE AlbumTitle >= @startTitle "
        + $"AND AlbumTitle < @endTitle",
        new SpannerParameterCollection {
            {"startTitle", SpannerDbType.String},
            {"endTitle", SpannerDbType.String} });
    cmd.Parameters["startTitle"].Value = startTitle;
    cmd.Parameters["endTitle"].Value = endTitle;
    using (var reader = await cmd.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            var marketingBudget = reader.IsDBNull(
                reader.GetOrdinal("MarketingBudget")) ?
                "" :
                reader.GetFieldValue<string>("MarketingBudget");
            Console.WriteLine("AlbumId : "
            + reader.GetFieldValue<string>("AlbumId")
            + " AlbumTitle : "
            + reader.GetFieldValue<string>("AlbumTitle")
            + " MarketingBudget : "
            + marketingBudget);
        }
    }
}

Go


import (
	"context"
	"fmt"
	"io"
	"strconv"

	"cloud.google.com/go/spanner"
	"google.golang.org/api/iterator"
)

func queryUsingIndex(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	stmt := spanner.Statement{
		SQL: `SELECT AlbumId, AlbumTitle, MarketingBudget
			FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}
			WHERE AlbumTitle >= @start_title AND AlbumTitle < @end_title`,
		Params: map[string]interface{}{
			"start_title": "Aardvark",
			"end_title":   "Goo",
		},
	}
	iter := client.Single().Query(ctx, stmt)
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		var albumID int64
		var marketingBudget spanner.NullInt64
		var albumTitle string
		if err := row.ColumnByName("AlbumId", &albumID); err != nil {
			return err
		}
		if err := row.ColumnByName("AlbumTitle", &albumTitle); err != nil {
			return err
		}
		if err := row.ColumnByName("MarketingBudget", &marketingBudget); err != nil {
			return err
		}
		budget := "NULL"
		if marketingBudget.Valid {
			budget = strconv.FormatInt(marketingBudget.Int64, 10)
		}
		fmt.Fprintf(w, "%d %s %s\n", albumID, albumTitle, budget)
	}
	return nil
}

Java

static void queryUsingIndex(DatabaseClient dbClient) {
  Statement statement =
      Statement
          // We use FORCE_INDEX hint to specify which index to use. For more details see
          // https://cloud.google.com/spanner/docs/query-syntax#from-clause
          .newBuilder(
              "SELECT AlbumId, AlbumTitle, MarketingBudget "
                  + "FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle} "
                  + "WHERE AlbumTitle >= @StartTitle AND AlbumTitle < @EndTitle")
          // We use @BoundParameters to help speed up frequently executed queries.
          //  For more details see https://cloud.google.com/spanner/docs/sql-best-practices
          .bind("StartTitle")
          .to("Aardvark")
          .bind("EndTitle")
          .to("Goo")
          .build();
  try (ResultSet resultSet = dbClient.singleUse().executeQuery(statement)) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %s %s\n",
          resultSet.getLong("AlbumId"),
          resultSet.getString("AlbumTitle"),
          resultSet.isNull("MarketingBudget") ? "NULL" : resultSet.getLong("MarketingBudget"));
    }
  }
}

Node.js

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// const startTitle = 'Ardvark';
// const endTitle = 'Goo';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const query = {
  sql: `SELECT AlbumId, AlbumTitle, MarketingBudget
        FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}
        WHERE AlbumTitle >= @startTitle AND AlbumTitle <= @endTitle`,
  params: {
    startTitle: startTitle,
    endTitle: endTitle,
  },
};

// Queries rows from the Albums table
try {
  const [rows] = await database.run(query);

  rows.forEach(row => {
    const json = row.toJSON();
    const marketingBudget = json.MarketingBudget
      ? json.MarketingBudget
      : null; // This value is nullable
    console.log(
      `AlbumId: ${json.AlbumId}, AlbumTitle: ${json.AlbumTitle}, MarketingBudget: ${marketingBudget}`
    );
  });
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  database.close();
}

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Queries sample data from the database using SQL and an index.
 *
 * The index must exist before running this sample. You can add the index
 * by running the `add_index` sample or by running this DDL statement against
 * your database:
 *
 *     CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)
 *
 * Example:
 * ```
 * query_data_with_index($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 * @param string $startTitle The start of the title index.
 * @param string $endTitle   The end of the title index.
 */
function query_data_with_index(
    $instanceId,
    $databaseId,
    $startTitle = 'Aardvark',
    $endTitle = 'Goo'
) {
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $parameters = [
        'startTitle' => $startTitle,
        'endTitle' => $endTitle
    ];

    $results = $database->execute(
        'SELECT AlbumId, AlbumTitle, MarketingBudget ' .
        'FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle} ' .
        'WHERE AlbumTitle >= @startTitle AND AlbumTitle < @endTitle',
        ['parameters' => $parameters]
    );

    foreach ($results as $row) {
        printf('AlbumId: %s, AlbumTitle: %s, MarketingBudget: %d' . PHP_EOL,
            $row['AlbumId'], $row['AlbumTitle'], $row['MarketingBudget']);
    }
}

Python

def query_data_with_index(
    instance_id, database_id, start_title="Aardvark", end_title="Goo"
):
    """Queries sample data from the database using SQL and an index.

    The index must exist before running this sample. You can add the index
    by running the `add_index` sample or by running this DDL statement against
    your database:

        CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)

    This sample also uses the `MarketingBudget` column. You can add the column
    by running the `add_column` sample or by running this DDL statement against
    your database:

        ALTER TABLE Albums ADD COLUMN MarketingBudget INT64

    """
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    params = {"start_title": start_title, "end_title": end_title}
    param_types = {
        "start_title": spanner.param_types.STRING,
        "end_title": spanner.param_types.STRING,
    }

    with database.snapshot() as snapshot:
        results = snapshot.execute_sql(
            "SELECT AlbumId, AlbumTitle, MarketingBudget "
            "FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle} "
            "WHERE AlbumTitle >= @start_title AND AlbumTitle < @end_title",
            params=params,
            param_types=param_types,
        )

        for row in results:
            print(u"AlbumId: {}, AlbumTitle: {}, " "MarketingBudget: {}".format(*row))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
# start_title = "An album title to start with such as 'Ardvark'"
# end_title   = "An album title to end with such as 'Goo'"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

sql_query = "SELECT AlbumId, AlbumTitle, MarketingBudget
             FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}
             WHERE AlbumTitle >= @start_title AND AlbumTitle < @end_title"

params      = { start_title: start_title, end_title: end_title }
param_types = { start_title: :STRING,     end_title: :STRING }

client.execute(sql_query, params: params, types: param_types).rows.each do |row|
  puts "#{row[:AlbumId]} #{row[:AlbumTitle]} #{row[:MarketingBudget]}"
end

Spécifier un index dans l'interface de lecture

Lorsque vous utilisez l'interface de lecture pour Cloud Spanner et que vous souhaitez que Cloud Spanner utilise un index, vous devez spécifier l'index. L'interface de lecture ne sélectionne pas automatiquement l'index.

En outre, votre index doit contenir toutes les données qui apparaissent dans les résultats de la requête, à l'exception des colonnes qui font partie de la clé primaire. Cette restriction existe, car l'interface de lecture ne prend pas en charge les jointures entre l'index et la table de base. Si vous devez inclure d'autres colonnes dans les résultats de la requête, vous disposez de plusieurs options :

  • Utilisez une clause STORING pour stocker les colonnes supplémentaires dans l'index.
  • Effectuez une requête sans inclure les colonnes supplémentaires, puis utilisez les clés primaires pour envoyer une autre requête qui lit les colonnes supplémentaires.

Cloud Spanner renvoie les valeurs de l'index par ordre de tri croissant par clé d'index. Pour récupérer les valeurs dans l'ordre décroissant, procédez comme suit :

  • Ajoutez à la clé d'index l'annotation DESC : Exemple :

    CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle DESC);

    L'annotation DESC s'applique à une seule clé d'index. Si l'index comprend plusieurs clés et si vous souhaitez que les résultats de la requête apparaissent dans l'ordre décroissant en fonction de toutes les clés, ajoutez une annotation DESC pour chaque clé.

  • Si la lecture spécifie une plage de clés, assurez-vous que la plage de clés est également dans l'ordre décroissant. En d'autres termes, la valeur de la clé de début doit être supérieure à la valeur de la clé de fin.

L'exemple suivant montre comment extraire les valeurs de AlbumId et AlbumTitle à l'aide de l'index AlbumsByAlbumTitle :

C++

void ReadDataWithIndex(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  spanner::ReadOptions read_options;
  read_options.index_name = "AlbumsByAlbumTitle";
  auto rows = client.Read("Albums", google::cloud::spanner::KeySet::All(),
                          {"AlbumId", "AlbumTitle"}, read_options);
  using RowType = std::tuple<std::int64_t, std::string>;
  for (auto const& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::runtime_error(row.status().message());
    std::cout << "AlbumId: " << std::get<0>(*row) << "\t";
    std::cout << "AlbumTitle: " << std::get<1>(*row) << "\n";
  }
  std::cout << "Read completed for [spanner_read_data_with_index]\n";
}

C#


using Google.Cloud.Spanner.Data;
using System.Collections.Generic;
using System.Threading.Tasks;

public class QueryDataWithIndexAsyncSample
{
    public class Album
    {
        public int AlbumId { get; set; }
        public string AlbumTitle { get; set; }
        public long MarketingBudget { get; set; }
    }

    public async Task<List<Album>> QueryDataWithIndexAsync(string projectId, string instanceId, string databaseId,
        string startTitle, string endTitle)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        using var connection = new SpannerConnection(connectionString);
        using var cmd = connection.CreateSelectCommand(
            "SELECT AlbumId, AlbumTitle, MarketingBudget FROM Albums@ "
            + "{FORCE_INDEX=AlbumsByAlbumTitle} "
            + $"WHERE AlbumTitle >= @startTitle "
            + $"AND AlbumTitle < @endTitle",
            new SpannerParameterCollection
            {
                { "startTitle", SpannerDbType.String, startTitle },
                { "endTitle", SpannerDbType.String, endTitle }
            });

        var albums = new List<Album>();
        using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            albums.Add(new Album
            {
                AlbumId = reader.GetFieldValue<int>("AlbumId"),
                AlbumTitle = reader.GetFieldValue<string>("AlbumTitle"),
                MarketingBudget = reader.IsDBNull(reader.GetOrdinal("MarketingBudget")) ? 0 : reader.GetFieldValue<long>("MarketingBudget")
            });
        }
        return albums;
    }
}

Go


import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
	"google.golang.org/api/iterator"
)

func readUsingIndex(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	iter := client.Single().ReadUsingIndex(ctx, "Albums", "AlbumsByAlbumTitle", spanner.AllKeys(),
		[]string{"AlbumId", "AlbumTitle"})
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var albumID int64
		var albumTitle string
		if err := row.Columns(&albumID, &albumTitle); err != nil {
			return err
		}
		fmt.Fprintf(w, "%d %s\n", albumID, albumTitle)
	}
}

Java

static void readUsingIndex(DatabaseClient dbClient) {
  try (ResultSet resultSet =
      dbClient
          .singleUse()
          .readUsingIndex(
              "Albums",
              "AlbumsByAlbumTitle",
              KeySet.all(),
              Arrays.asList("AlbumId", "AlbumTitle"))) {
    while (resultSet.next()) {
      System.out.printf("%d %s\n", resultSet.getLong(0), resultSet.getString(1));
    }
  }
}

Node.js

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const albumsTable = database.table('Albums');

const query = {
  columns: ['AlbumId', 'AlbumTitle'],
  keySet: {
    all: true,
  },
  index: 'AlbumsByAlbumTitle',
};

// Reads the Albums table using an index
try {
  const [rows] = await albumsTable.read(query);

  rows.forEach(row => {
    const json = row.toJSON();
    console.log(`AlbumId: ${json.AlbumId}, AlbumTitle: ${json.AlbumTitle}`);
  });
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  database.close();
}

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Reads sample data from the database using an index.
 *
 * The index must exist before running this sample. You can add the index
 * by running the `add_index` sample or by running this DDL statement against
 * your database:
 *
 *     CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)
 *
 * Example:
 * ```
 * read_data_with_index($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function read_data_with_index($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $keySet = $spanner->keySet(['all' => true]);
    $results = $database->read(
        'Albums',
        $keySet,
        ['AlbumId', 'AlbumTitle'],
        ['index' => 'AlbumsByAlbumTitle']
    );

    foreach ($results->rows() as $row) {
        printf('AlbumId: %s, AlbumTitle: %s' . PHP_EOL,
            $row['AlbumId'], $row['AlbumTitle']);
    }
}

Python

def read_data_with_index(instance_id, database_id):
    """Reads sample data from the database using an index.

    The index must exist before running this sample. You can add the index
    by running the `add_index` sample or by running this DDL statement against
    your database:

        CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)

    """
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    with database.snapshot() as snapshot:
        keyset = spanner.KeySet(all_=True)
        results = snapshot.read(
            table="Albums",
            columns=("AlbumId", "AlbumTitle"),
            keyset=keyset,
            index="AlbumsByAlbumTitle",
        )

        for row in results:
            print("AlbumId: {}, AlbumTitle: {}".format(*row))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

result = client.read "Albums", [:AlbumId, :AlbumTitle],
                     index: "AlbumsByAlbumTitle"

result.rows.each do |row|
  puts "#{row[:AlbumId]} #{row[:AlbumTitle]}"
end

Clause STORING

Si vous le souhaitez, vous pouvez utiliser la clause STORING pour stocker une copie d'une colonne dans l'index. Ce type d'index offre des avantages pour les requêtes et les appels en lecture utilisant l'index, au prix d'un stockage supplémentaire :

  • Les requêtes SQL qui utilisent l'index et sélectionnent les colonnes stockées dans la clause STORING ne nécessitent pas de jointure supplémentaire à la table de base.
  • Les appels de lecture utilisant l'index peuvent lire les colonnes stockées dans la clause STORING.

Supposons, par exemple, que vous ayez créé une autre version de AlbumsByAlbumTitle qui stocke une copie de la colonne MarketingBudget dans l'index (notez la clause STORING en gras) :

CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget);

Avec l'ancien index AlbumsByAlbumTitle, Cloud Spanner doit joindre l'index à la table de base, puis récupérer la colonne à partir de la table de base. Avec le nouvel index AlbumsByAlbumTitle2, Cloud Spanner lit la colonne directement à partir de l'index, ce qui est plus efficace.

Si vous utilisez l'interface de lecture au lieu de SQL, le nouvel index AlbumsByAlbumTitle2 vous permet également de lire directement la colonne MarketingBudget :

C++

void ReadDataWithStoringIndex(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  spanner::ReadOptions read_options;
  read_options.index_name = "AlbumsByAlbumTitle2";
  auto rows =
      client.Read("Albums", google::cloud::spanner::KeySet::All(),
                  {"AlbumId", "AlbumTitle", "MarketingBudget"}, read_options);
  using RowType =
      std::tuple<std::int64_t, std::string, absl::optional<std::int64_t>>;
  for (auto const& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::runtime_error(row.status().message());
    std::cout << "AlbumId: " << std::get<0>(*row) << "\t";
    std::cout << "AlbumTitle: " << std::get<1>(*row) << "\t";
    auto marketing_budget = std::get<2>(*row);
    if (marketing_budget) {
      std::cout << "MarketingBudget: " << *marketing_budget << "\n";
    } else {
      std::cout << "MarketingBudget: NULL\n";
    }
  }
  std::cout << "Read completed for [spanner_read_data_with_storing_index]\n";
}

C#


using Google.Cloud.Spanner.Data;
using System.Collections.Generic;
using System.Threading.Tasks;

public class QueryDataWithStoringIndexAsyncSample
{
    public class Album
    {
        public int AlbumId { get; set; }
        public string AlbumTitle { get; set; }
        public long? MarketingBudget { get; set; }
    }

    public async Task<List<Album>> QueryDataWithStoringIndexAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        using var connection = new SpannerConnection(connectionString);
        var cmd = connection.CreateSelectCommand(
            "SELECT AlbumId, AlbumTitle, MarketingBudget FROM Albums@ "
            + "{FORCE_INDEX=AlbumsByAlbumTitle2}");

        var albums = new List<Album>();
        using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            albums.Add(new Album
            {
                AlbumId = reader.GetFieldValue<int>("AlbumId"),
                AlbumTitle = reader.GetFieldValue<string>("AlbumTitle"),
                MarketingBudget = reader.IsDBNull(reader.GetOrdinal("MarketingBudget")) ? 0 : reader.GetFieldValue<long>("MarketingBudget")
            });
        }
        return albums;
    }
}

Go


import (
	"context"
	"fmt"
	"io"
	"strconv"

	"cloud.google.com/go/spanner"
	"google.golang.org/api/iterator"
)

func readStoringIndex(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	iter := client.Single().ReadUsingIndex(ctx, "Albums", "AlbumsByAlbumTitle2", spanner.AllKeys(),
		[]string{"AlbumId", "AlbumTitle", "MarketingBudget"})
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var albumID int64
		var marketingBudget spanner.NullInt64
		var albumTitle string
		if err := row.Columns(&albumID, &albumTitle, &marketingBudget); err != nil {
			return err
		}
		budget := "NULL"
		if marketingBudget.Valid {
			budget = strconv.FormatInt(marketingBudget.Int64, 10)
		}
		fmt.Fprintf(w, "%d %s %s\n", albumID, albumTitle, budget)
	}
}

Java

static void readStoringIndex(DatabaseClient dbClient) {
  // We can read MarketingBudget also from the index since it stores a copy of MarketingBudget.
  try (ResultSet resultSet =
      dbClient
          .singleUse()
          .readUsingIndex(
              "Albums",
              "AlbumsByAlbumTitle2",
              KeySet.all(),
              Arrays.asList("AlbumId", "AlbumTitle", "MarketingBudget"))) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %s %s\n",
          resultSet.getLong(0),
          resultSet.getString(1),
          resultSet.isNull("MarketingBudget") ? "NULL" : resultSet.getLong("MarketingBudget"));
    }
  }
}

Node.js

// "Storing" indexes store copies of the columns they index
// This speeds up queries, but takes more space compared to normal indexes
// See the link below for more information:
// https://cloud.google.com/spanner/docs/secondary-indexes#storing_clause

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const albumsTable = database.table('Albums');

const query = {
  columns: ['AlbumId', 'AlbumTitle', 'MarketingBudget'],
  keySet: {
    all: true,
  },
  index: 'AlbumsByAlbumTitle2',
};

// Reads the Albums table using a storing index
try {
  const [rows] = await albumsTable.read(query);

  rows.forEach(row => {
    const json = row.toJSON();
    let rowString = `AlbumId: ${json.AlbumId}`;
    rowString += `, AlbumTitle: ${json.AlbumTitle}`;
    if (json.MarketingBudget) {
      rowString += `, MarketingBudget: ${json.MarketingBudget}`;
    }
    console.log(rowString);
  });
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  database.close();
}

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Reads sample data from the database using an index with a storing
 * clause.
 *
 * The index must exist before running this sample. You can add the index
 * by running the `add_storing_index` sample or by running this DDL statement
 * against your database:
 *
 *     CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle)
 *     STORING (MarketingBudget)
 *
 * Example:
 * ```
 * read_data_with_storing_index($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function read_data_with_storing_index($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $keySet = $spanner->keySet(['all' => true]);
    $results = $database->read(
        'Albums',
        $keySet,
        ['AlbumId', 'AlbumTitle', 'MarketingBudget'],
        ['index' => 'AlbumsByAlbumTitle2']
    );

    foreach ($results->rows() as $row) {
        printf('AlbumId: %s, AlbumTitle: %s, MarketingBudget: %d' . PHP_EOL,
            $row['AlbumId'], $row['AlbumTitle'], $row['MarketingBudget']);
    }
}

Python

def read_data_with_storing_index(instance_id, database_id):
    """Reads sample data from the database using an index with a storing
    clause.

    The index must exist before running this sample. You can add the index
    by running the `add_soring_index` sample or by running this DDL statement
    against your database:

        CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle)
        STORING (MarketingBudget)

    """
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    with database.snapshot() as snapshot:
        keyset = spanner.KeySet(all_=True)
        results = snapshot.read(
            table="Albums",
            columns=("AlbumId", "AlbumTitle", "MarketingBudget"),
            keyset=keyset,
            index="AlbumsByAlbumTitle2",
        )

        for row in results:
            print(u"AlbumId: {}, AlbumTitle: {}, " "MarketingBudget: {}".format(*row))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

result = client.read "Albums", [:AlbumId, :AlbumTitle, :MarketingBudget],
                     index: "AlbumsByAlbumTitle2"

result.rows.each do |row|
  puts "#{row[:AlbumId]} #{row[:AlbumTitle]} #{row[:MarketingBudget]}"
end

Indexer des valeurs NULL

Par défaut, Cloud Spanner indexe les valeurs NULL. Par exemple, reprenez la définition de l'index SingersByFirstLastName dans la table Singers :

CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName);

Toutes les lignes de Singers sont indexées même si FirstName et/ou LastName sont NULL.

Un diagramme montre les lignes qui sont omises d&#39;un index filtré NULL.

Lorsque des valeurs NULL sont indexées, vous pouvez effectuer des requêtes SQL efficaces et effectuer des lectures sur des données incluant des valeurs NULL. Par exemple, utilisez cette instruction de requête SQL pour trouver tous les Singers avec un FirstName NULL :

SELECT s.SingerId, s.FirstName, s.LastName
    FROM Singers@{FORCE_INDEX=SingersByFirstLastName} AS s
    WHERE s.FirstName IS NULL;

Ordre de tri pour les valeurs NULL

Cloud Spanner trie NULL comme la plus petite valeur pour un type donné. Pour une colonne dans l'ordre croissant (ASC), les valeurs NULL sont triées en premier. Pour une colonne dans l'ordre décroissant (DESC), les valeurs NULL sont triées en dernier.

Désactiver l'indexation des valeurs NULL

Pour désactiver l'indexation des valeurs NULL, ajoutez le mot clé NULL_FILTERED à la définition de l'index. Les index NULL_FILTERED sont particulièrement utiles pour l'indexation des colonnes partiellement remplies, dont la plupart des lignes contiennent une valeur NULL. Dans ces cas, l'index NULL_FILTERED peut être considérablement plus petit et plus simple à maintenir qu'un index normal qui inclut des valeurs NULL.

Voici une autre définition de SingersByFirstLastName qui n'indexe pas les valeurs NULL :

CREATE NULL_FILTERED INDEX SingersByFirstLastNameNoNulls
    ON Singers(FirstName, LastName);

Le mot clé NULL_FILTERED s'applique à toutes les colonnes de la clé d'index. Vous ne pouvez pas spécifier le filtrage des valeurs NULL par colonne.

La création d'un index NULL_FILTERED empêche Cloud Spanner de l'utiliser pour certaines requêtes. Par exemple, Cloud Spanner n'utilise pas l'index pour cette requête, car il omet toutes les lignes Singers pour lesquelles LastName est NULL. Par conséquent, l'utilisation de l'index empêcherait la requête de renvoyer les lignes correctes :

FROM Singers@{FORCE_INDEX=SingersByFirstLastNameNoNulls}
    WHERE FirstName = "John";

Pour permettre à Cloud Spanner d'utiliser l'index, vous devez réécrire la requête afin d'exclure les lignes qui sont également exclues de l'index :

SELECT FirstName, LastName
    FROM Singers@{FORCE_INDEX=SingersByFirstLastNameNoNulls}
    WHERE FirstName = "John" AND LastName IS NOT NULL;

Index uniques

Les index peuvent être déclarés comme UNIQUE. Les index UNIQUE ajoutent une contrainte aux données indexées qui interdit les entrées en double pour une clé d'index donnée. Cloud Spanner applique cette contrainte au moment du commit de transaction. Plus précisément, toute transaction qui entraînerait l'existence de plusieurs entrées d'index pour une même clé verra son commit échouer.

Si une table contient des données violant la contrainte UNIQUE, la tentative de création d'un index UNIQUE échouera.

Remarque sur les index UNIQUE NULL_FILTERED

Un index UNIQUE NULL_FILTERED n'applique pas l'unicité des clés d'index lorsqu'au moins l'une des parties de la clé de l'index est NULL.

Par exemple, supposons que vous ayez créé la table et l'index suivants :

CREATE TABLE ExampleTable (
  Key1 INT64 NOT NULL,
  Key2 INT64,
  Key3 INT64,
  Col1 INT64,
) PRIMARY KEY (Key1, Key2, Key3);

CREATE UNIQUE NULL_FILTERED INDEX ExampleIndex ON ExampleTable (Key1, Key2, Col1);

Les deux lignes suivantes dans ExampleTable ont les mêmes valeurs pour les clés d'index secondaires Key1, Key2 et Col1 :

1, NULL, 1, 1
1, NULL, 2, 1

Étant donné que Key2 est NULL et que l'index est NULL_FILTERED, les lignes ne seront pas présentes dans l'index ExampleIndex. Comme elles ne sont pas insérées dans l'index, celui-ci ne les rejettera pas pour cause de violation de la contrainte d'unicité sur (Key1, Key2, Col1).

Si vous souhaitez que l'index applique la contrainte d'unicité pour les valeurs du tuple (Key1, Key2, Col1), vous devez annoter Key2 avec NOT NULL dans la définition de table ou créer un index sans NULL_FILTERED.

Supprimer un index

Utilisez l'instruction DROP INDEX pour supprimer un index secondaire de votre schéma.

Pour supprimer l'index nommé SingersByFirstLastName :

DROP INDEX SingersByFirstLastName;

Étape suivante