加速模式比對運算式

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 加速下列查詢。查詢可以選擇提供 @{force_index = AlbumsIndex}，強制最佳化工具使用 AlbumsIndex。

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')

加速的必要條件

如要讓 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) 太短。

後續步驟