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

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

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

搜尋索引支援全文、完全比對、數值欄和 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 個讚的文件。如果幾乎所有專輯的標題都含有「car」一詞，且幾乎所有專輯的評分都為 5 顆星，但只有少數專輯有 1, 000 個讚，則這項查詢會使用大量資源。在這種情況下，將 Likes 與 Rating 類似地編入索引可節省資源。

  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
  

後續步驟

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