加速模式比對運算式

Spanner 搜尋索引可加快模式比對運算式 (例如 LIKE、STARTS_WITH、ENDS_WITH) 和規則運算式比對述詞 REGEXP_CONTAINS 的速度。本頁說明如何使用 TOKENIZE_NGRAMS 建立及設定搜尋索引，加快模式比對述詞的速度。

設定 n 元語法 TOKENLIST，加快模式比對速度

如要啟用模式比對運算式加速功能，請使用 TOKENIZE_NGRAMS 將小寫的 STRING 資料欄權杖化，然後使用 GoogleSQL 中的 STORING 子句或 PostgreSQL 中的 INCLUDE 子句儲存 STRING 資料欄。

GoogleSQL 

CREATE TABLE Albums (
AlbumId INT64 NOT NULL,
AlbumTitle STRING(MAX),
AlbumTitle_Ngram_Tokens TOKENLIST AS (
  TOKENIZE_NGRAMS(LOWER(AlbumTitle), ngram_size_min=>3, ngram_size_max=>4)) HIDDEN,
) PRIMARY KEY(AlbumId);

CREATE SEARCH INDEX AlbumsIndex
ON Albums(AlbumTitle_Ngram_Tokens) STORING (AlbumTitle);

PostgreSQL

CREATE TABLE albums (
albumid bigint NOT NULL,
album_title varchar,
album_title_ngrams_tokens spanner.tokenlist GENERATED ALWAYS AS (
  spanner.tokenize_ngrams(
    lower(album_title),
    ngram_size_min => 3,
    ngram_size_max => 4
  )
) VIRTUAL HIDDEN,
PRIMARY KEY(albumid));

CREATE SEARCH INDEX albumsidx ON
albums(album_title_ngrams_tokens) INCLUDE (album_title);

自動加速查詢 (使用模式比對述詞)

查詢最佳化工具可能會選擇使用 AlbumsIndex 和 AlbumTitle_Ngram_Tokens 加速下列查詢。

GoogleSQL

在 GoogleSQL 中，我們會加速 LIKE、STARTS_WITH、ENDS_WITH 和 REGEXP_CONTAINS。

  • LIKE 述詞：

    SELECT AlbumId
FROM Albums @{FORCE_INDEX=AlbumsIndex}
WHERE AlbumTitle LIKE "%999%";

  • STARTS_WITH 述詞：

    SELECT AlbumId
FROM Albums @{FORCE_INDEX=AlbumsIndex}
WHERE STARTS_WITH(AlbumTitle, "apple")

  • ENDS_WITH 述詞：

    SELECT AlbumId
FROM Albums @{FORCE_INDEX=AlbumsIndex}
WHERE ENDS_WITH(AlbumTitle, "apple")

  • REGEXP_CONTAINS 述詞：

    SELECT AlbumId
FROM Albums @{FORCE_INDEX=AlbumsIndex}
WHERE REGEXP_CONTAINS(AlbumTitle, r"(good|great)[ ]+morning")

PostgreSQL

在 PostgreSQL 中，我們加速了 LIKE 和 STARTS_WITH。

  • LIKE 述詞：

    SELECT albumid
FROM albums /*@ FORCE_INDEX = albumsidx */
WHERE album_title like '%999%';

  • STARTS_WITH 述詞：

    SELECT albumid
FROM albums /*@ FORCE_INDEX = albumsidx */
WHERE starts_with(album_title, 'apple')

查詢最佳化工具會根據模式比對述詞，使用不同的預設選擇性估計值。因此，最佳化工具可能會選取完整資料表掃描或具有剩餘篩選器的次要索引掃描，而非搜尋索引。

如果查詢速度異常緩慢，請檢查執行計畫。如果最佳化工具未選取搜尋索引，請嘗試在 GoogleSQL 中使用 @{FORCE_INDEX = AlbumsIndex}，或在 PostgreSQL 中使用 /*@ FORCE_INDEX = albumsidx */。

加速的必要條件

如要讓 Spanner 啟用這項加速功能，必須符合下列規則：

  • 索引必須使用 GoogleSQL 中的 STORING 子句，或 PostgreSQL 中的 INCLUDE 子句，儲存 STRING 欄。這可避免在事後篩選期間，對基礎資料表進行成本高昂的反向聯結，這對於搜尋過度擷取文件時的效能至關重要。
  • STRING 資料欄必須使用 TOKENIZE_NGRAMS 進行權杖化。
  • 代碼化必須套用至 LOWER(column_name)，而非 column_name。
  • LIKE 模式、STARTS_WITH 前置字元、ENDS_WITH 後置字元或REGEXP_CONTAINS 規則運算式必須指定為常數常值。為避免模式過短而加速，系統不支援查詢參數。
  • LIKE 模式、STARTS_WITH 前置字串、ENDS_WITH 後置字串或REGEXP_CONTAINS規則運算式必須包含足夠的文字，才能至少產生一個 n 元語法。舉例來說，r".*" 不符合資格，因為沒有要比對的字元序列。同樣地，如果將 N-gram 最小大小設為 3，則 LIKE 述詞 "%ab%" 不符合資格，因為 "ab" (大小為 2) 太短。

後續步驟