在 Spanner 資料庫中,Spanner 會自動為每個資料表的主鍵建立索引。舉例來說,您無需另行為 Singers
的主鍵建立索引,系統會自動為您產生索引資料。
您也可以為其他資料欄建立次要索引。對資料欄新增次要索引,即可更有效率地查詢該資料欄的資料。舉例來說,如果需要依據專輯名稱快速查詢專輯,您應該在 AlbumTitle
上建立次要索引,這樣 Spanner 就不必掃描整個資料表。
如果上一個範例中的查閱作業是在讀取/寫入交易中完成,更有效率的查閱作業也會避免對整個資料表保留鎖定,因此可同時插入及更新資料表,但僅限於 AlbumTitle
查閱範圍以外的資料列。
除了查詢作業之外,次要索引也能協助 Spanner 更有效率地執行掃描,進而啟用索引掃描,而非掃描整個資料表。
Spanner 會在每個次要索引中儲存下列資料:
- 主資料表的所有索引鍵資料欄
- 索引中包含的所有資料欄
- 索引定義中透過選用的
STORING
子句 (GoogleSQL 方言資料庫) 或INCLUDE
子句 (PostgreSQL 方言資料庫) 指定的所有資料欄。
隨著時間推移,Spanner 會分析資料表,確保次要索引用於適當的查詢。
新增次要索引
新增次要索引的最佳時機是建立資料表時。如要同時建立資料表和索引,請在單一要求中,將新資料表和新索引的 DDL 陳述式傳送至 Spanner。
在 Spanner 中,您也可以在資料庫持續處理流量時,在現有資料表中新增次要索引。就和 Spanner 中的任何結構定義變更一樣,現有資料庫的新增索引作業不需要讓資料庫離線,也不會鎖定整個資料表或資料欄。
無論何時將新索引新增至現有資料表,Spanner 都會自動回填 (填入) 索引,以反映索引資料的最新狀態。Spanner 會為您處理這項補充程序,並使用低優先順序的節點資源在背景執行程序。索引回填速度會根據索引建立期間節點資源的變化進行調整,且回填作業不會大幅影響資料庫效能。
建立索引所需要的時間從幾分鐘到數小時不等,由於建立索引屬於結構定義更新,因此與其他結構定義更新一樣,會受到效能限制。建立次要索引所需的時間取決於多項因素:
- 資料集大小
- 執行個體的運算能力
- 執行個體的負載
如要查看索引補充作業的進度,請參閱進度部分。
請注意,如果使用修訂時間戳記欄做為次要索引的第一部分,可能會產生熱點並降低寫入效能。
您可以使用 CREATE INDEX
陳述式在結構定義中定義次要索引,例如:
根據姓氏和名字為資料庫中的所有 Singers
建立索引:
GoogleSQL
CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName);
PostgreSQL
CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName);
根據 SongName
的值為資料庫中的所有 Songs
建立索引:
GoogleSQL
CREATE INDEX SongsBySongName ON Songs(SongName);
PostgreSQL
CREATE INDEX SongsBySongName ON Songs(SongName);
如要只為特定歌手的歌曲建立索引,請使用 INTERLEAVE IN
子句,在 Singers
資料表中交錯索引:
GoogleSQL
CREATE INDEX SongsBySingerSongName ON Songs(SingerId, SongName),
INTERLEAVE IN Singers;
PostgreSQL
CREATE INDEX SongsBySingerSongName ON Songs(SingerId, SongName)
INTERLEAVE IN Singers;
只為特定專輯的歌曲建立索引:
GoogleSQL
CREATE INDEX SongsBySingerAlbumSongName ON Songs(SingerId, AlbumId, SongName),
INTERLEAVE IN Albums;
PostgreSQL
CREATE INDEX SongsBySingerAlbumSongName ON Songs(SingerId, AlbumId, SongName)
INTERLEAVE IN Albums;
依照遞減排序為 SongName
建立索引:
GoogleSQL
CREATE INDEX SongsBySingerAlbumSongNameDesc ON Songs(SingerId, AlbumId, SongName DESC),
INTERLEAVE IN Albums;
PostgreSQL
CREATE INDEX SongsBySingerAlbumSongNameDesc ON Songs(SingerId, AlbumId, SongName DESC)
INTERLEAVE IN Albums;
請注意,先前的 DESC
註解僅適用於 SongName
。如要依照遞減排序為其他索引鍵建立索引,請同樣使用 DESC
為這類索引鍵加上註解,例如:SingerId DESC, AlbumId DESC
。
另請注意,PRIMARY_KEY
是保留字,不能做為索引名稱。這是指建立具有 PRIMARY KEY 規格的資料表時,系統建立的虛擬索引名稱
如要進一步瞭解如何選擇非交錯索引和交錯索引,以及相關最佳做法,請參閱「索引選項」和「在包含單調遞增或遞減值的資料欄中建立交錯索引」。
索引和交錯
Spanner 索引可以與其他資料表交錯,以便將索引資料列與其他資料表的資料列共置。與 Spanner 資料表交錯類似,索引父項的主鍵欄必須是索引欄的前置字串,且類型和排序順序必須相符。與交錯式資料表不同,您不必比對資料欄名稱。交錯索引的每個資料列都會與相關聯的父項資料列一起儲存。
舉例來說,請參考下列結構定義:
CREATE TABLE Singers (
SingerId INT64 NOT NULL,
FirstName STRING(1024),
LastName STRING(1024),
SingerInfo PROTO<Singer>(MAX)
) PRIMARY KEY (SingerId), DIRECTORY;
CREATE TABLE Albums (
SingerId INT64 NOT NULL,
AlbumId INT64 NOT NULL,
AlbumTitle STRING(MAX),
PublisherId INT64 NOT NULL
) PRIMARY KEY (SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE;
CREATE TABLE Songs (
SingerId INT64 NOT NULL,
AlbumId INT64 NOT NULL,
TrackId INT64 NOT NULL,
PublisherId INT64 NOT NULL,
SongName STRING(MAX)
) PRIMARY KEY (SingerId, AlbumId, TrackId),
INTERLEAVE IN PARENT Albums ON DELETE CASCADE;
CREATE TABLE Publishers (
Id INT64 NOT NULL,
PublisherName STRING(MAX)
) PRIMARY KEY (Id);
如要根據姓氏和名字為資料庫中的所有 Singers
建立索引,請建立索引。以下說明如何定義索引 SingersByFirstLastName
:
CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName);
如要在 (SingerId, AlbumId, SongName)
上建立 Songs
的索引,可以執行下列操作:
CREATE INDEX SongsBySingerAlbumSongName
ON Songs(SingerId, AlbumId, SongName);
或者,您也可以建立與 Songs
的祖先交錯的索引,例如:
CREATE INDEX SongsBySingerAlbumSongName
ON Songs(SingerId, AlbumId, SongName),
INTERLEAVE IN Albums;
此外,您也可以在與 Songs
無關的資料表 (例如 Publishers
) 中,建立 Songs
的交錯索引 (PublisherId, SingerId, AlbumId, SongName)
。請注意,Publishers
資料表的主鍵 (id
) 並非下列範例中已建立索引的資料欄前置字串。由於 Publishers.Id
和 Songs.PublisherId
共用相同的型別、排序順序和可為空值性,因此仍可使用。
CREATE INDEX SongsByPublisherSingerAlbumSongName
ON Songs(PublisherId, SingerId, AlbumId, SongName),
INTERLEAVE IN Publishers;
查看索引回填進度
控制台
在 Spanner 導覽選單中,按一下「Operations」(作業) 分頁標籤。「Operations」(作業) 頁面會顯示正在執行的作業清單。
在清單中找出回填作業。如果作業仍在執行中,「結束時間」欄中的進度指標會顯示作業完成的百分比,如下圖所示:
gcloud
使用 gcloud spanner operations describe
檢查作業進度。
取得作業 ID:
gcloud spanner operations list --instance=INSTANCE-NAME \ --database=DATABASE-NAME --type=DATABASE_UPDATE_DDL
更改下列內容:
- 將 INSTANCE-NAME 替換為 Spanner 執行個體名稱。
- 將 DATABASE-NAME 替換為資料庫名稱。
使用須知:
如要限制清單,請指定
--filter
標記。例如:--filter="metadata.name:example-db"
只會列出特定資料庫的作業。--filter="error:*"
只會列出失敗的備份作業。
如需篩選器語法的相關資訊,請參閱「gcloud 主題篩選器」。如要瞭解如何篩選備份作業,請參閱 ListBackupOperationsRequest 中的
filter
欄位。--type
旗標不區分大小寫。
輸出看起來類似以下內容:
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
執行
gcloud spanner operations describe
:gcloud spanner operations describe \ --instance=INSTANCE-NAME \ --database=DATABASE-NAME \ projects/PROJECT-NAME/instances/INSTANCE-NAME/databases/DATABASE-NAME/operations/OPERATION_ID
更改下列內容:
- INSTANCE-NAME:Spanner 執行個體名稱。
- DATABASE-NAME:Spanner 資料庫名稱。
- PROJECT-NAME:專案名稱。
- OPERATION-ID:您要檢查的作業 ID。
輸出內容中的
progress
區段會顯示作業完成的百分比。輸出結果看起來與下列內容相似:done: true ... 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' ...
REST v1
取得作業 ID:
gcloud spanner operations list --instance=INSTANCE-NAME
--database=DATABASE-NAME --type=DATABASE_UPDATE_DDL
更改下列內容:
- 將 INSTANCE-NAME 替換為 Spanner 執行個體名稱。
- 將 DATABASE-NAME 替換為資料庫名稱。
使用任何要求資料之前,請先替換以下項目:
- PROJECT-ID:專案 ID。
- INSTANCE-ID:執行個體 ID。
- DATABASE-ID:資料庫 ID。
- OPERATION-ID:作業 ID。
HTTP 方法和網址:
GET https://spanner.googleapis.com/v1/projects/PROJECT-ID/instances/INSTANCE-ID/databases/DATABASE-ID/operations/OPERATION-ID
如要傳送要求,請展開以下其中一個選項:
您應該會收到如下的 JSON 回應:
{ ... "progress": [ { "progressPercent": 100, "startTime": "2023-05-27T00:52:27.366688Z", "endTime": "2023-05-27T00:52:30.184845Z" }, { "progressPercent": 100, "startTime": "2023-05-27T00:52:30.184845Z", "endTime": "2023-05-27T00:52:40.750959Z" } ], ... "done": true, "response": { "@type": "type.googleapis.com/google.protobuf.Empty" } }
如果是 gcloud
和 REST,您可以在 progress
部分查看每項索引回填陳述式的進度。陳述式陣列中的每個陳述式,在進度陣列中都有對應的欄位。這個進度陣列順序對應於陳述式陣列的順序。一旦可用,系統就會相應填入 startTime
、progressPercent
和 endTime
欄位。請注意,輸出內容不會顯示回填進度完成的預估時間。
如果作業時間過長,可以取消作業。詳情請參閱取消建立索引。
查看索引回填進度時的可能情況
嘗試檢查索引回填進度時,可能會遇到不同情況。需要索引回填的索引建立陳述式屬於結構定義更新作業,而結構定義更新作業可能包含多個陳述式。
第一種情境最簡單,也就是索引建立陳述式是結構定義更新作業中的第一個陳述式。由於索引建立陳述式是第一個陳述式,因此會因執行順序而率先處理及執行。索引建立陳述式的 startTime
欄位會立即填入結構定義更新作業的開始時間。接著,當索引回填進度超過 0% 時,系統會填入索引建立陳述式的 progressPercent
欄位。最後,陳述式提交後,系統會填入 endTime
欄位。
第二種情況是索引建立陳述式並非結構定義更新作業中的第一個陳述式。由於執行順序,與索引建立陳述式相關的欄位不會填入任何內容,直到先前的陳述式已提交為止。與先前的情境類似,一旦先前的陳述式已提交,索引建立陳述式的 startTime
欄位會先填入,接著是 progressPercent
欄位。最後,陳述式完成提交後,系統會填入 endTime
欄位。
取消建立索引
您可以使用 Google Cloud CLI 取消建立索引。如要擷取 Spanner 資料庫的結構定義更新作業清單,請使用 gcloud spanner operations list
指令,並加入 --filter
選項:
gcloud spanner operations list \
--instance=INSTANCE \
--database=DATABASE \
--filter="@TYPE:UpdateDatabaseDdlMetadata"
找出要取消作業的 OPERATION_ID
,然後使用 gcloud spanner operations cancel
指令取消作業:
gcloud spanner operations cancel OPERATION_ID \
--instance=INSTANCE \
--database=DATABASE
查看現有索引
如要查看資料庫中現有索引的相關資訊,可以使用Google Cloud 控制台或 Google Cloud CLI:
控制台
前往 Google Cloud 控制台的 Spanner「Instances」(執行個體) 頁面。
按一下要查看的執行個體名稱。
在左側窗格中,按一下要查看的資料庫,然後按一下要查看的資料表。
按一下「索引」分頁標籤。 Google Cloud 控制台會顯示索引清單。
選用:如要取得索引的詳細資料 (例如索引包含的資料欄),請按一下索引名稱。
gcloud
使用 gcloud spanner databases ddl describe
指令:
gcloud spanner databases ddl describe DATABASE \
--instance=INSTANCE
gcloud CLI 會列印資料定義語言 (DDL) 陳述式,以建立資料庫的資料表和索引。CREATE
INDEX
陳述式會說明現有索引。例如:
--- |-
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)
使用特定索引查詢
以下各節說明如何在 SQL 陳述式和 Spanner 的讀取介面中指定索引。這些章節中的範例假設您已將 MarketingBudget
資料欄新增至 Albums
資料表,並建立名為 AlbumsByAlbumTitle
的索引:
GoogleSQL
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);
PostgreSQL
CREATE TABLE Albums (
SingerId BIGINT NOT NULL,
AlbumId BIGINT NOT NULL,
AlbumTitle VARCHAR,
MarketingBudget BIGINT,
PRIMARY KEY (SingerId, AlbumId)
) INTERLEAVE IN PARENT Singers ON DELETE CASCADE;
CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle);
在 SQL 陳述式中指定索引
使用 SQL 查詢 Spanner 資料表時,Spanner 會自動使用可能有助提高查詢效率的任何索引。因此,您不需要為 SQL 查詢指定索引。不過,對於工作負載的關鍵查詢,Google 建議您在 SQL 陳述式中使用 FORCE_INDEX
指令,以獲得更一致的效能。
在少數情況下,Spanner 可能會選擇造成查詢延遲增加的索引。如果您按照效能迴歸問題適用的疑難排解步驟操作,並確認要嘗試使用不同索引來執行查詢的話,可以在查詢中指定索引。
如要在 SQL 陳述式中指定索引,請使用 FORCE_INDEX
提示提供索引指令。索引指令使用下列語法:
GoogleSQL
FROM MyTable@{FORCE_INDEX=MyTableIndex}
PostgreSQL
FROM MyTable /*@ FORCE_INDEX = MyTableIndex */
您也可以使用索引指令,告知 Spanner 掃描主資料表,而非使用索引:
GoogleSQL
FROM MyTable@{FORCE_INDEX=_BASE_TABLE}
PostgreSQL
FROM MyTable /*@ FORCE_INDEX = _BASE_TABLE */
您可以使用索引指令,告知 Spanner 掃描具名結構定義資料表中的索引:
GoogleSQL
FROM MyNamedSchema.MyTable@{FORCE_INDEX="MyNamedSchema.MyTableIndex"}
PostgreSQL
FROM MyTable /*@ FORCE_INDEX = MyTableIndex */
以下範例顯示指定索引的 SQL 查詢:
GoogleSQL
SELECT AlbumId, AlbumTitle, MarketingBudget
FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}
WHERE AlbumTitle >= "Aardvark" AND AlbumTitle < "Goo";
PostgreSQL
SELECT AlbumId, AlbumTitle, MarketingBudget
FROM Albums /*@ FORCE_INDEX = AlbumsByAlbumTitle */
WHERE AlbumTitle >= 'Aardvark' AND AlbumTitle < 'Goo';
索引指令可能會強制 Spanner 的查詢處理器讀取查詢所需但未儲存在索引中的額外資料欄。查詢處理器會透過索引和主資料表之間的聯結擷取這類資料欄。如要避免這項額外的彙整作業,請使用 STORING
子句 (GoogleSQL 方言資料庫) 或 INCLUDE
子句 (PostgreSQL 方言資料庫),將額外資料欄儲存在索引中。
在前一個範例中,MarketingBudget
欄並未儲存在索引中,但 SQL 查詢會選取這個資料欄。因此,Spanner 必須在主資料表中查詢 MarketingBudget
資料欄,然後將該資料欄與索引資料彙整,據此傳回查詢結果。
如果索引指令有下列任何問題,Spanner 就會發出錯誤:
- 索引不存在。
- 索引屬於其他主資料表。
- 查詢缺少
NULL_FILTERED
索引必要的NULL
篩選運算式。
以下範例說明如何撰寫及執行查詢,以透過 AlbumsByAlbumTitle
索引擷取 AlbumId
、AlbumTitle
和 MarketingBudget
的值:
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
在讀取介面中指定索引
使用 Spanner 的讀取介面時,如要讓 Spanner 使用索引,必須指定索引。讀取介面不會自動選取索引。
此外,索引必須包含查詢結果中顯示的所有資料,主鍵所屬的資料欄除外。這是因為讀取介面不支援索引和主資料表之間的聯結。如要在查詢結果中納入其他資料欄,可以採取以下幾種做法:
- 使用
STORING
或INCLUDE
子句,將其他資料欄儲存在索引中。 - 查詢時不加入其他資料欄,然後使用主鍵傳送另一個查詢,讀取其他資料欄。
Spanner 會根據索引鍵,以遞增排序傳回索引中的值。如要以遞減順序擷取值,請完成下列步驟:
為索引鍵加上
DESC
註解。例如:CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle DESC);
DESC
註解僅適用於單一索引鍵。如果索引包含多個鍵,且您希望查詢結果依所有鍵遞減排序,請為每個鍵加上DESC
註解。如果讀取作業指定了鍵範圍,請確保鍵範圍也採用遞減順序。換句話說,起始鍵的值必須大於結尾鍵的值。
以下範例說明如何使用索引 AlbumsByAlbumTitle
擷取 AlbumId
和 AlbumTitle
的值:
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
為僅限索引的掃描作業建立索引
您可以選擇使用 STORING
子句 (適用於 GoogleSQL 方言資料庫) 或 INCLUDE
子句 (適用於 PostgreSQL 方言資料庫),將資料欄副本儲存在索引中。這類索引可為查詢和讀取呼叫提供優勢,但會佔用額外儲存空間:
- 如果 SQL 查詢使用索引並選取儲存在
STORING
或INCLUDE
子句中的資料欄,就不需額外與主資料表進行彙整。 - 如果
read()
呼叫使用索引,可讀取STORING
/INCLUDE
子句儲存的資料欄。
舉例來說,假如您為儲存索引中 MarketingBudget
欄複本的 AlbumsByAlbumTitle
建立替代版本 (請留意以粗體顯示的 STORING
或 INCLUDE
子句):
GoogleSQL
CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget);
PostgreSQL
CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) INCLUDE (MarketingBudget);
使用舊的 AlbumsByAlbumTitle
索引時,Spanner 必須將索引與主資料表彙整,然後從主資料表擷取資料欄。使用新的 AlbumsByAlbumTitle2
索引時,Spanner 會直接從索引讀取資料欄,效率更高。
如果您使用讀取介面而非 SQL,新的 AlbumsByAlbumTitle2
索引也能讓您直接讀取 MarketingBudget
資料欄:
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
變更索引
您可以使用 ALTER INDEX
陳述式,在現有索引中新增其他資料欄或捨棄資料欄。建立索引時,這項操作可以更新 STORING
子句 (GoogleSQL 方言資料庫) 或 INCLUDE
子句 (PostgreSQL 方言資料庫) 定義的資料欄清單。您無法使用這項陳述式,在索引鍵中新增或捨棄資料欄。舉例來說,您可以使用 ALTER INDEX
將資料欄新增至 AlbumsByAlbumTitle
,而不必建立新的索引 AlbumsByAlbumTitle2
,如下例所示:
GoogleSQL
ALTER INDEX AlbumsByAlbumTitle ADD STORED COLUMN MarketingBudget
PostgreSQL
ALTER INDEX AlbumsByAlbumTitle ADD INCLUDE COLUMN MarketingBudget
將新資料欄新增至現有索引時,Spanner 會使用背景回填程序。回填作業進行期間,索引中的資料欄無法讀取,因此您可能無法獲得預期的效能提升。您可以使用 gcloud spanner operations
指令列出長時間執行的作業,並查看其狀態。詳情請參閱說明作業。
您也可以使用取消作業來取消執行中的作業。
回填完成後,Spanner 會將資料欄新增至索引。隨著索引變大,使用索引的查詢可能會變慢。
以下範例說明如何從索引中捨棄資料欄:
GoogleSQL
ALTER INDEX AlbumsByAlbumTitle DROP STORED COLUMN MarketingBudget
PostgreSQL
ALTER INDEX AlbumsByAlbumTitle DROP INCLUDE COLUMN MarketingBudget
NULL 值索引
根據預設,Spanner 會為 NULL
值建立索引。舉例來說,如要回呼 Singers
資料表 的 SingersByFirstLastName
索引定義:
CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName);
即使 FirstName
和/或 LastName
的值為 NULL
,系統仍會為 Singers
的所有資料列建立索引。
建立 NULL
值的索引後,您就可以有效執行 SQL 查詢和讀取作業,處理包含 NULL
值的資料。例如,使用以下 SQL 查詢陳述式,可以找出所有 Singers
值為 NULL
的 FirstName
:
GoogleSQL
SELECT s.SingerId, s.FirstName, s.LastName
FROM Singers@{FORCE_INDEX=SingersByFirstLastName} AS s
WHERE s.FirstName IS NULL;
PostgreSQL
SELECT s.SingerId, s.FirstName, s.LastName
FROM Singers /* @ FORCE_INDEX = SingersByFirstLastName */ AS s
WHERE s.FirstName IS NULL;
NULL 值的排序順序
Spanner 會將 NULL
視為任何指定類型的最小值。如果資料欄採用遞增 (ASC
) 順序,NULL
值會顯示在最前面。如果資料欄採用遞減 (DESC
) 順序,NULL
值會顯示在最後面。
停用 NULL 值索引功能
GoogleSQL
如要停止為 null 值建立索引,請在索引定義中加入 NULL_FILTERED
這個關鍵字。在為稀疏欄 (大部分資料列皆包含 NULL
值的欄) 建立索引時,NULL_FILTERED
索引功能相當實用。比起建立包含 NULL
值的一般索引,這種做法可以大幅降低維護 NULL_FILTERED
索引所需的大小和費用。
以下是不為 NULL
值建立索引的 SingersByFirstLastName
替代定義:
CREATE NULL_FILTERED INDEX SingersByFirstLastNameNoNulls
ON Singers(FirstName, LastName);
NULL_FILTERED
關鍵字會套用至所有索引鍵欄。您無法針對個別資料欄指定 NULL
篩選條件。
PostgreSQL
如要篩除一或多個已建立索引的資料欄中含有空值的資料列,請使用 WHERE COLUMN IS NOT NULL
述詞。在為稀疏欄 (大部分資料列皆包含 NULL
值的欄) 建立索引時,Null 篩選索引功能相當實用。比起建立包含 NULL
值的一般索引,這種做法可以大幅降低維護空值篩選索引所需的大小和費用。
以下是不為 NULL
值建立索引的 SingersByFirstLastName
替代定義:
CREATE INDEX SingersByFirstLastNameNoNulls
ON Singers(FirstName, LastName)
WHERE FirstName IS NOT NULL
AND LastName IS NOT NULL;
篩除 NULL
值可防止 Spanner 將其用於某些查詢。舉例來說,Spanner 不會使用這個查詢的索引,因為索引會省略任何 Singers
為 NULL
的 LastName
列;因此,使用索引會導致查詢無法傳回正確的列:
GoogleSQL
FROM Singers@{FORCE_INDEX=SingersByFirstLastNameNoNulls}
WHERE FirstName = "John";
PostgreSQL
FROM Singers /*@ FORCE_INDEX = SingersByFirstLastNameNoNulls */
WHERE FirstName = 'John';
如要讓 Spanner 使用索引,您必須改寫查詢,排除索引中排除的資料列:
GoogleSQL
SELECT FirstName, LastName
FROM Singers@{FORCE_INDEX=SingersByFirstLastNameNoNulls}
WHERE FirstName = 'John' AND LastName IS NOT NULL;
PostgreSQL
SELECT FirstName, LastName
FROM Singers /*@ FORCE_INDEX = SingersByFirstLastNameNoNulls */
WHERE FirstName = 'John' AND LastName IS NOT NULL;
索引 proto 欄位
只要索引的欄位使用原始或 ENUM
資料型別,即可使用產生的資料欄,為儲存在 PROTO
資料欄的通訊協定緩衝區欄位建立索引。
如果您在通訊協定訊息欄位中定義索引,就無法從 Proto 架構修改或移除該欄位。詳情請參閱含有 proto 欄位索引的結構定義更新。
以下是 Singers
資料表範例,其中包含 SingerInfo
proto 訊息資料欄。如要在 PROTO
的 nationality
欄位上定義索引,您需要建立儲存的產生資料欄:
GoogleSQL
CREATE PROTO BUNDLE (googlesql.example.SingerInfo, googlesql.example.SingerInfo.Residence);
CREATE TABLE Singers (
SingerId INT64 NOT NULL,
...
SingerInfo googlesql.example.SingerInfo,
SingerNationality STRING(MAX) AS (SingerInfo.nationality) STORED
) PRIMARY KEY (SingerId);
這個 API 的 googlesql.example.SingerInfo
proto 類型定義如下:
GoogleSQL
package googlesql.example;
message SingerInfo {
optional string nationality = 1;
repeated Residence residence = 2;
message Residence {
required int64 start_year = 1;
optional int64 end_year = 2;
optional string city = 3;
optional string country = 4;
}
}
然後在 proto 的 nationality
欄位上定義索引:
GoogleSQL
CREATE INDEX SingersByNationality ON Singers(SingerNationality);
下列 SQL 查詢會使用先前的索引讀取資料:
GoogleSQL
SELECT s.SingerId, s.FirstName
FROM Singers AS s
WHERE s.SingerNationality = "English";
注意:
- 使用索引指令存取通訊協定緩衝區資料欄欄位的索引。
- 您無法在重複的通訊協定緩衝區欄位上建立索引。
更新含有 Proto 欄位索引的結構定義
如果您在通訊協定訊息欄位中定義索引,就無法從 Proto 架構修改或移除該欄位。這是因為定義索引後,每次更新結構定義時都會執行型別檢查。Spanner 會擷取路徑中所有欄位的型別資訊,這些欄位會用於索引定義。
不重複的查詢
索引可宣告為 UNIQUE
。UNIQUE
索引會對要建立索引的資料加上一項限制,禁止指定索引鍵的項目重複。Spanner 會在交易修訂時間強制執行上述限制。具體而言,任何會導致單一索引鍵出現多個索引項目的交易都將無法修訂。
如果資料表中的第一個物件包含非 UNIQUE
資料,將無法建立 UNIQUE
索引。
UNIQUE NULL_FILTERED 索引注意事項
如果 UNIQUE NULL_FILTERED
索引中有任何一個鍵包含 NULL,系統不會強制執行索引鍵唯一性要求。
舉例來說,假設您已建立以下資料表和索引:
GoogleSQL
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);
PostgreSQL
CREATE TABLE ExampleTable (
Key1 BIGINT NOT NULL,
Key2 BIGINT,
Key3 BIGINT,
Col1 BIGINT,
PRIMARY KEY (Key1, Key2, Key3)
);
CREATE UNIQUE INDEX ExampleIndex ON ExampleTable (Key1, Key2, Col1)
WHERE Key1 IS NOT NULL
AND Key2 IS NOT NULL
AND Col1 IS NOT NULL;
在 ExampleTable
中,以下兩個資料列的次要索引鍵 Key1
、Key2
和 Col1
為相同的值:
1, NULL, 1, 1
1, NULL, 2, 1
由於 Key2
為 NULL
,且索引已篩除空值,這些資料列不會顯示在 ExampleIndex
索引中。因為這些資料列未插入索引,索引不會因 (Key1, Key2,
Col1)
違反唯一性而予以拒絕。
如果您希望索引對這個組合 (Key1
、Key2
和 Col1
) 的值強制執行唯一性,則您必須在資料表定義中為 Key2
加上 NOT NULL
註解,或是建立不篩除空值的索引。
捨棄索引
使用 DROP INDEX
陳述式從結構定義中捨棄次要索引。
如要捨棄名為 SingersByFirstLastName
的索引,請執行下列操作:
DROP INDEX SingersByFirstLastName;
建立索引,加快掃描速度
當 Spanner 需要執行資料表掃描 (而非索引查詢),從一或多個資料欄擷取值時,如果這些資料欄有索引,且順序符合查詢指定,您就能更快取得結果。如果您經常執行需要掃描的查詢,建議建立次要索引,提高掃描效率。
具體來說,如果需要 Spanner 經常以反向順序掃描資料表的主鍵或其他索引,則可透過次要索引明確指定所選順序,提高效率。
舉例來說,即使 Spanner 需要掃描 Songs
才能找出 SongId
的最低值,下列查詢一律會快速傳回結果:
SELECT SongId FROM Songs LIMIT 1;
SongId
是資料表的主鍵,會以遞增順序儲存 (與所有主鍵相同)。Spanner 可以掃描該鍵的索引,並快速找到第一個結果。
不過,如果沒有次要索引的協助,下列查詢就無法快速傳回結果,尤其是當 Songs
包含大量資料時:
SELECT SongId FROM Songs ORDER BY SongId DESC LIMIT 1;
即使 SongId
是資料表的主鍵,Spanner 也無法在不掃描整個資料表的情況下,擷取資料欄的最高值。
新增下列索引可讓這項查詢更快傳回結果:
CREATE INDEX SongIdDesc On Songs(SongId DESC);
有了這個索引,Spanner 就能使用它,更快傳回第二個查詢的結果。
後續步驟
- 瞭解 Spanner 適用的 SQL 最佳做法。
- 瞭解 Spanner 的查詢執行計畫。
- 瞭解如何排解 SQL 查詢效能降低的問題。