In What Type Of Cloud Computing Does The Customer Have The Highest Level Of Control?

In a Cloud Spanner database, Deject Spanner automatically creates an index for each table'south main key. For example, you don't need to do anything to index the primary key of Singers, because it's automatically indexed for you.

You can also create secondary indexes for other columns. Adding a secondary index on a cavalcade makes it more efficient to look up data in that column. For example, if yous demand to quickly await up a set of SingerId values for a given range of LastName values, you should create a secondary index on LastName, then Cloud Spanner does not need to scan the entire tabular array.

If the lookup in the example above is done within a read-write transaction, the more than efficient lookup besides avoids holding locks on the entire tabular array, which allows concurrent inserts and updates to the table for rows outside of the LastName lookup range.

In addition to the benefits they bring to lookups, secondary indexes tin can also help Cloud Spanner execute scans more efficiently, enabling alphabetize scans rather than full table scans.

Cloud Spanner stores the following information in each secondary index:

All central columns from the base of operations table
All columns that are included in the alphabetize
All columns specified in the optional STORING clause of the index definition

Over time, Cloud Spanner analyzes your tables to ensure that your secondary indexes are used for the appropriate queries.

Adding a secondary index

The nearly efficient fourth dimension to add together a secondary alphabetize is when you create the table. To create a table and its indexes at the same time, ship the DDL statements for the new table and the new indexes in a single request to Cloud Spanner.

In Cloud Spanner, you lot can also add a new secondary index to an existing table while the database continues to serve traffic. Like any other schema changes in Deject Spanner, adding an index to an existing database does non require taking the database offline and does not lock unabridged columns or tables.

Whenever a new index is added to an existing table, Cloud Spanner automatically backfills, or populates, the index to reflect an upward-to-date view of the information being indexed. Cloud Spanner manages this backfill process for you, and it uses additional resources during the alphabetize backfill.

Index creation can take from several minutes to many hours. Considering index creation is a schema update, it is spring by the same performance constraints every bit any other schema update. The time needed to create a secondary index depends on several factors:

The size of the data set
The compute capacity of the instance
The load on the instance

To view the progress made for an index backfill process, refer to the progress section.

Be aware that using the commit timestamp column as the first part of the secondary index can create hotspots and reduce write performance.

Employ the CREATE INDEX statement to define a secondary index in your schema. Here are some examples:

To index all Singers in the database past their first and last proper noun:

          CREATE Alphabetize SingersByFirstLastName ON Singers(FirstName, LastName)

To create an index of all Songs in the database past the value of SongName:

          CREATE Index SongsBySongName ON Songs(SongName)

To index merely the songs for a particular singer, utilize the INTERLEAVE IN clause to interleave the index in the table Singers:

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

To index only the songs on a particular album:

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

To alphabetize past descending order of SongName:

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

Notation that the DESC notation to a higher place applies but to SongName. To index by descending club of other index keys, comment them with DESC as well: SingerId DESC, AlbumId DESC.

Also note that PRIMARY_KEY is a reserved word and cannot be used as the name of an index. It is the name given to the pseudo-index that is created when a table with Primary KEY specification is created

For more than details and best practices for choosing not-interleaved indexes and interleaved indexes, encounter Alphabetize options and Utilise an interleaved index on a column whose value monotonically increases or decreases.

Viewing index backfill progress

Steps to view index backfill progress

An index backfill process is part of a schema-update long-running performance since the addition of a secondary index requires a schema update. You can view the progress of the index backfill using the functioning id. If you don't have the operation id, find it past using gcloud spanner operations list:

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

Usage notes:

To limit the listing of operations returned by this command, specify the --filter flag. For example, use the following filter to render schema-update operations.
```
                  --filter="@TYPE:UpdateDatabaseDdlMetadata"                                  
```
For information on filter syntax, see gcloud topic filters. For information on filtering database operations, see the filter field in ListDatabaseOperationsRequest.

Hither is an example of the output:

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

To rail the progress of one or multiple secondary index backfill processes, use gcloud spanner operations describe:

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

Hither is an example of the output of a schema-update long-running operation that contains two alphabetize backfill processes:

              done: truthful metadata:   '@type': blazon.googleapis.com/google.spanner.admin.database.v1.UpdateDatabaseDdlMetadata   commitTimestamps:   - '2021-01-22T21:58:42.912540Z'   database: projects/my-projection/instances/test-example/databases/example-db   progress:   - endTime: '2021-01-22T21:58:42.912540Z'     progressPercent: 100     startTime: '2021-01-22T21:58:eleven.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-case/databases/example-db/operations/_auto_op_123456 response:   '@blazon': type.googleapis.com/google.protobuf.Empty

The progress of each alphabetize backfill statement can be found in the progress field. For each argument in the statement array, there is a corresponding field in the progress array. And the startTime and endTime fields are populated when bachelor. The progress array order corresponds to the social club of the statements array.

Scenarios when viewing index backfill progress

There are unlike scenarios that a user could face up when trying to cheque the progress of an index backfill. Alphabetize creation statements that require an alphabetize backfill are part of schema update operations, and there can be several statements that are part of a schema update operation.

The offset scenario is the simplest, which is when the index cosmos statement is the first statement in the schema update operation. The startTime field for the index creation statement will be populated with the start time of the schema update performance since it is the outset statement, and will be the first to exist processed and executed due to the club of execution. The alphabetize cosmos argument'southward progressPercent field will be populated when the progress of the alphabetize backfill is above 0%. The endTime field is populated one time the statement has been committed.

The second scenario is when the index creation statement is not the commencement statement in the schema update operation. No fields related to the index creation statement will be populated until the previous argument(southward) have been committed due to the order of execution. One time the previous statement(s) take been committed, the startTime of the alphabetize creation statement'southward progress field will exist the outset attribute populated. Then the index creation statement's progressPercent field will be populated when the progress of the index backfill is in a higher place 0%. The endTime field is populated once the statement has been committed.

Canceling index cosmos

You can use the Google Cloud CLI to abolish index cosmos. To think a list of schema-update operations for a Deject Spanner database, utilize the gcloud spanner operations list command, and include the --filter option:

          gcloud spanner operations listing \     --example=INSTANCE            \     --database=DATABASE            \     --filter="@TYPE:UpdateDatabaseDdlMetadata"

Find the OPERATION_ID for the functioning you desire to cancel, so use the gcloud spanner operations cancel control to cancel it:

          gcloud spanner operations abolish            OPERATION_ID            \     --case=INSTANCE            \     --database=DATABASE

Viewing existing indexes

To view data about existing indexes in a database, yous can apply the Google Cloud Console or the Google Cloud CLI:

Console

Become to the Deject Spanner Instances page in the Cloud Panel.

Go to the Instances page
Click the name of the case you lot desire to view.
In the left pane, click the database you desire to view, then click the table you desire to view.
Click the Indexes tab. The Cloud Console shows a list of indexes.
Optional: To get details about an index, such as the columns that it includes, click the name of the index.

gcloud

Use the gcloud spanner databases ddl depict command:

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

The gcloud CLI prints the Data Definition Language (DDL) statements to create the database'due south tables and indexes. The CREATE INDEX statements describe the existing indexes. For case:

                              --- |-   CREATE Table Singers (     SingerId INT64 Non NULL,     FirstName STRING(1024),     LastName STRING(1024),     SingerInfo BYTES(MAX),   ) Primary Key(SingerId) ---                CREATE Index SingersByFirstLastName ON Singers(FirstName, LastName)

Querying with a specific index

The post-obit sections explain how to specify an index in a SQL statement and with the read interface for Cloud Spanner. The examples in these sections assume that yous added a MarketingBudget column to the Albums table and created an index called AlbumsByAlbumTitle:

          CREATE TABLE Albums (   SingerId         INT64 NOT Cypher,   AlbumId          INT64 Not Aught,   AlbumTitle       Cord(MAX),            MarketingBudget  INT64, ) PRIMARY KEY (SingerId, AlbumId),   INTERLEAVE IN PARENT Singers ON DELETE Pour;            CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle);

Specifying an alphabetize in a SQL argument

When you lot utilize SQL to query a Cloud Spanner table, Cloud Spanner automatically uses any indexes that are probable to brand the query more efficient. As a effect, y'all don't demand to specify an index for SQL queries. Nonetheless, for queries that are critical for your workload, Google advises you lot to apply FORCE_INDEX directives in your SQL statements for more consistent functioning.

In a few cases, Cloud Spanner might choose an alphabetize that causes query latency to increase. If you've followed the troubleshooting steps for performance regressions and confirmed that it makes sense to endeavour a dissimilar alphabetize for the query, you lot can specify the index as part of your query.

To specify an index in a SQL statement, apply FORCE_INDEX to provide an index directive. Alphabetize directives utilise the following syntax:

          FROM            MyTable            @{FORCE_INDEX=MyTableIndex}

You can besides use an index directive to tell Deject Spanner to browse the base of operations table instead of using an alphabetize:

          FROM            MyTable            @{FORCE_INDEX=_BASE_TABLE}

The following example shows a SQL query that specifies an alphabetize:

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

An index directive might strength Cloud Spanner's query processor to read additional columns that are required by the query but not stored in the index. The query processor retrieves these columns past joining the index and the base table. To avoid this extra join, utilize a STORING clause to store the boosted columns in the index.

For instance, in the instance shown in a higher place, the MarketingBudget column is not stored in the index, just the SQL query selects this cavalcade. As a result, Deject Spanner must wait up the MarketingBudget column in the base of operations table, and then join information technology with information from the index, to return the query results.

Cloud Spanner raises an fault if the index directive has any of the post-obit issues:

The alphabetize does not be.
The index is on a different base of operations table.
The query is missing a required Nada filtering expression for a NULL_FILTERED index.

The following examples show how to write and execute queries that fetch the values of AlbumId, AlbumTitle, and MarketingBudget using the index AlbumsByAlbumTitle:

C++

C#

Go

Java

Node.js

PHP

Python

Ruby

Specifying an index in the read interface

When you use the read interface to Cloud Spanner, and you want Cloud Spanner to use an index, you must specify the alphabetize. The read interface does not select the alphabetize automatically.

In addition, your alphabetize must contain all of the data that appears in the query results, excluding columns that are function of the primary key. This brake exists because the read interface does not back up joins between the index and the base of operations table. If you lot need to include other columns in the query results, you take a few options:

Use a STORING clause to store the additional columns in the index.
Query without including the additional columns, and then use the primary keys to send some other query that reads the boosted columns.

Cloud Spanner returns values from the index in ascending sort club by index key. To call up values in descending club, complete these steps:

Annotate the index key with DESC. For example:
```
              CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle                DESC);                          
```
The DESC annotation applies to a unmarried index key. If the index includes more one fundamental, and you want the query results to appear in descending order based on all keys, include a DESC notation for each central.
If the read specifies a key range, ensure that the key range is likewise in descending order. In other words, the value of the beginning fundamental must exist greater than the value of the end key.

The following example shows how to retrieve the values of AlbumId and AlbumTitle using the alphabetize AlbumsByAlbumTitle:

C++

C#

Go

Coffee

Node.js

PHP

Python

Blood-red

STORING clause

Optionally, you tin apply the STORING clause to store a copy of a column in the index. This type of index provides advantages for queries and read calls using the index, at the price of using actress storage:

SQL queries that use the index and select columns stored in the STORING clause exercise not require an actress join to the base of operations table.
Read calls that use the index can read columns stored in the STORING clause.

For example, suppose you created an alternate version of AlbumsByAlbumTitle that stores a copy of the MarketingBudget column in the alphabetize (note the STORING clause in bold):

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

With the quondam AlbumsByAlbumTitle alphabetize, Cloud Spanner must bring together the alphabetize with the base table, then call up the cavalcade from the base table. With the new AlbumsByAlbumTitle2 index, Deject Spanner reads the column directly from the index, which is more efficient.

If you use the read interface instead of SQL, the new AlbumsByAlbumTitle2 index also lets you read the MarketingBudget column direct:

C++

C#

Go

Java

Node.js

PHP

Python

Cerise

Indexing of Nada values

By default, Deject Spanner indexes NULL values. For example, recall the definition of the index SingersByFirstLastName on the tabular array Singers:

          CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName);

All rows of Singers are indexed even if either FirstName or LastName, or both, are Nix.

A diagram shows rows that are omitted from a NULL filtered index.

When NULL values are indexed, y'all tin perform efficient SQL queries and reads over data that includes NULL values. For instance, use this SQL query argument to find all Singers with a NULL FirstName:

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

Sort guild for Cypher values

Cloud Spanner sorts NULL as the smallest value for whatsoever given type. For a column in ascending (ASC) order, NULL values sort first. For a column in descending (DESC) order, NULL values sort last.

Disabling indexing of NULL values

To disable the indexing of nulls, add together the NULL_FILTERED keyword to the index definition. NULL_FILTERED indexes are specially useful for indexing sparse columns, where most rows comprise a Null value. In these cases, the NULL_FILTERED alphabetize can be considerably smaller and more efficient to maintain than a normal alphabetize that includes NULL values.

Here's an alternating definition of SingersByFirstLastName that does non index NULL values:

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

The NULL_FILTERED keyword applies to all index key columns. You cannot specify Nada filtering on a per-column basis.

Making an index NULL_FILTERED prevents Cloud Spanner from using it for some queries. For example, Deject Spanner does not use the index for this query, because the index omits whatsoever Singers rows for which LastName is Zippo; every bit a upshot, using the index would prevent the query from returning the correct rows:

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

To enable Cloud Spanner to use the index, you lot must rewrite the query so it excludes the rows that are also excluded from the index:

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

Unique indexes

Indexes tin exist declared UNIQUE. UNIQUE indexes add a constraint to the data being indexed that prohibits duplicate entries for a given index central. This constraint is enforced by Deject Spanner at transaction commit time. Specifically, whatsoever transaction that would cause multiple alphabetize entries for the same key to exist volition fail to commit.

If a table contains non-UNIQUE data in information technology to brainstorm with, attempting to create a UNIQUE index on it will fail.

A note virtually UNIQUE NULL_FILTERED indexes

A UNIQUE NULL_FILTERED alphabetize does non enforce alphabetize key uniqueness when at least 1 of the index's key parts is Null.

For example, suppose that you created the following tabular array and index:

          CREATE Tabular array ExampleTable (   Key1 INT64 NOT NULL,   Key2 INT64,   Key3 INT64,   Col1 INT64, ) Master Key (Key1, Key2, Key3);  CREATE UNIQUE NULL_FILTERED Alphabetize ExampleIndex ON ExampleTable (Key1, Key2, Col1);

The post-obit ii rows in ExampleTable accept the aforementioned values for the secondary alphabetize keys Key1, Key2 and Col1:

          ane, Zippo, i, i 1, NULL, two, 1

Because Key2 is NULL and the index is NULL_FILTERED, the rows will not be present in the index ExampleIndex. Because they are not inserted into the alphabetize, the index will not reject them for violating uniqueness on (Key1, Key2, Col1).

If you want the index to enforce the uniqueness of values of the tuple (Key1, Key2, Col1), then you must comment Key2 with NOT Aught in the table definition or create the index without NULL_FILTERED.

Dropping an index

Use the Driblet Alphabetize statement to drib a secondary index from your schema.

To driblet the index named SingersByFirstLastName:

          DROP INDEX SingersByFirstLastName;

Indexing for faster scanning

When Cloud Spanner needs to perform a table scan (rather than an indexed lookup) to fetch values from one or more than columns, you tin receive faster results if an index exists for those columns, and in the order specified by the query. If you lot ofttimes perform queries that require scans, consider creating secondary indexes to help these scans happen more efficiently.

In particular, if you need Deject Spanner to frequently scan a table's primary key or other index in contrary order, so y'all can increase its efficiency through a secondary index that makes the desired gild explicit.

For instance, the query below always returns a fast event, even though Cloud Spanner needs to scan Songs to observe the everyman value of SongId:

          SELECT SongId FROM Songs LIMIT 1;

SongId is the tabular array'southward primary key, stored (as with all primary keys) in ascending order. Cloud Spanner can browse that key's index and detect the first effect quickly.

Yet, without the help of a secondary index, the following query would non return every bit apace, especially if Songs holds a lot of data:

          SELECT SongId FROM Songs Social club BY SongId DESC LIMIT 1;

Even though SongId is the table's primary key, Cloud Spanner has no fashion to fetch the cavalcade's highest value without resorting to a total table scan.

Adding the following index would permit this query to return more quickly:

          CREATE INDEX SongIdDesc On Songs(SongId DESC);

With this index in identify, Cloud Spanner would use it to return a result for the second query much more quickly.

What's side by side

Learn about SQL best practices for Cloud Spanner.
Understand query execution plans for Cloud Spanner.
Observe out how to troubleshoot performance regressions in SQL queries.