混合使用全文和非文字查詢

本頁說明如何執行混合全文和非文字資料的搜尋。

執行混合式全文和非文字搜尋

搜尋索引支援全文、完全相符、數值資料欄和 JSON/JSONB 資料欄。您可以在 WHERE 子句中結合文字和非文字條件，與多欄搜尋查詢類似。查詢最佳化工具會嘗試使用搜尋索引，最佳化非文字述詞。如果無法執行這項操作，Spanner 會評估符合搜尋索引的每個資料列條件。如果參照的資料欄未儲存在搜尋索引中，系統會從主資料表擷取。

請見如下範例：

CREATE TABLE Albums (
  AlbumId STRING(MAX) NOT NULL,
  Title STRING(MAX),
  Rating FLOAT64,
  Genres ARRAY<STRING(MAX)>,
  Likes INT64,
  Cover BYTES(MAX),
  Title_Tokens TOKENLIST AS (TOKENIZE_FULLTEXT(Title)) HIDDEN,
  Rating_Tokens TOKENLIST AS (TOKENIZE_NUMBER(Rating)) HIDDEN,
  Genres_Tokens TOKENLIST AS (TOKEN(Genres)) HIDDEN
) PRIMARY KEY(AlbumId);

CREATE SEARCH INDEX AlbumsIndex
ON Albums(Title_Tokens, Rating_Tokens, Genres_Tokens)
STORING (Likes);

CREATE TABLE albums (
  albumid character varying NOT NULL,
  title character varying,
  rating bigint,
  genres character varying NOT NULL,
  likes bigint,
  cover bytea,
  title_tokens spanner.tokenlist AS (spanner.tokenize_fulltext(title)) VIRTUAL HIDDEN,
  rating_tokens spanner.tokenlist AS (spanner.tokenize_number(rating)) VIRTUAL HIDDEN,
  genres_tokens spanner.tokenlist AS (spanner.token(genres)) VIRTUAL HIDDEN,
PRIMARY KEY(albumid));

CREATE SEARCH INDEX albumsindex
ON albums(title_tokens, rating_tokens, genres_tokens)
INCLUDE (likes);

對這個資料表執行查詢時，會出現下列行為：

• Rating 和 Genres 會納入搜尋索引。Spanner 會使用搜尋索引發布清單，加快條件的處理速度。ARRAY_INCLUDES_ANY 和 ARRAY_INCLUDES_ALL 是 GoogleSQL 函式，PostgreSQL 方言不支援這些函式。

  SELECT Album
  FROM Albums
  WHERE Rating > 4
    AND ARRAY_INCLUDES_ANY(Genres, ['jazz'])
  

• 查詢可以任意組合連詞、析取和否定，包括混合使用全文和非文字述詞。這項查詢完全由搜尋索引加速。

  SELECT Album
  FROM Albums
  WHERE (SEARCH(Title_Tokens, 'car')
        OR Rating > 4)
    AND NOT ARRAY_INCLUDES_ANY(Genres, ['jazz'])
  

• Likes 儲存在索引中，但結構定義不會要求 Spanner 為可能的值建立符記索引。因此，系統會加速處理 Title 的全文述詞和 Rating 的非文字述詞，但不會加速處理 Likes 的述詞。在 Spanner 中，這項查詢會擷取 Title 中含有「car」一詞且評分超過 4 的所有文件，然後篩除按讚數未達 1000 的文件。如果幾乎所有相簿的標題都含有「車」一詞，且幾乎所有相簿的評分都是 5 分，但只有少數相簿獲得 1000 個讚，這項查詢就會使用大量資源。在這種情況下，以類似 Rating 的方式為 Likes 建立索引，可節省資源。

  GoogleSQL

  SELECT Album
  FROM Albums
  WHERE SEARCH(Title_Tokens, 'car')
    AND Rating > 4
    AND Likes >= 1000
  

  PostgreSQL

  SELECT album
  FROM albums
  WHERE spanner.search(title_tokens, 'car')
    AND rating > 4
    AND likes >= 1000
  

• Cover 不會儲存在索引中。下列查詢會在 AlbumsIndex 和 Albums 之間執行反向聯結，擷取所有相符專輯的 Cover。

  GoogleSQL

  SELECT AlbumId, Cover
  FROM Albums
  WHERE SEARCH(Title_Tokens, 'car')
    AND Rating > 4
  

  PostgreSQL

  SELECT albumid, cover
  FROM albums
  WHERE spanner.search(title_tokens, 'car')
    AND rating > 4
  

後續步驟

• 瞭解全文搜尋查詢。
• 瞭解如何排序搜尋結果。
• 瞭解如何執行子字串搜尋。
• 瞭解如何將搜尋結果分頁。
• 瞭解如何搜尋多個資料欄。