検索インデックスで複数の列を検索する

検索インデックスでは、複数のトークン化された列をインデックスに登録できるため、これらの列に対するクエリを効率化できます。このページでは、全文検索の一種である複数の列で検索を実行する方法について説明します。

検索インデックスの構造により、クエリは分散結合が不要となり、クエリのパフォーマンスが予測可能になります。ベーステーブルの 1 行に対応するすべてのトークンが同じスプリット上に配置されているため、分散結合が回避されます。

たとえば、次のスキーマについて考えてみましょう。

GoogleSQL

CREATE TABLE Albums (
  AlbumId STRING(MAX) NOT NULL,
  Title STRING(MAX),
  Studio STRING(MAX),
  Title_Tokens TOKENLIST AS (TOKENIZE_FULLTEXT(Title)) HIDDEN,
  Studio_Tokens TOKENLIST AS (TOKENIZE_FULLTEXT(Studio)) HIDDEN
) PRIMARY KEY(AlbumId);

CREATE SEARCH INDEX AlbumsIndex ON Albums(Title_Tokens, Studio_Tokens);

PostgreSQL

CREATE TABLE albums (
  albumid character varying NOT NULL,
  title character varying,
  studio character varying,
  title_tokens spanner.tokenlist
      GENERATED ALWAYS AS (TOKENIZE_FULLTEXT(title)) VIRTUAL HIDDEN,
  studio_tokens spanner.tokenlist
      GENERATED ALWAYS AS (TOKENIZE_FULLTEXT(studio)) VIRTUAL HIDDEN,
) PRIMARY KEY(albumid);

CREATE SEARCH INDEX albumsindex ON albums(title_tokens, studio_tokens);

クエリで Title_Tokens と Studio_Tokens の 2 つのフィールドを検索できるようになりました。

GoogleSQL

SELECT AlbumId
FROM Albums
WHERE SEARCH(Title_Tokens, "fifth symphony")
  AND SEARCH(Studio_Tokens, "Blue Note Studio")

PostgreSQL

SELECT albumid
FROM albums
WHERE spanner.search(title_tokens, 'fifth symphony')
  AND spanner.search(studio_tokens, 'Blue Note Studio')

Spanner は、WHERE 句の結合演算子、論理和演算子、否定演算子で複数列の検索クエリをサポートしています。検索インデックスでは、次のタイプのクエリをすべて使用できます。

  • 連結: Title に「car」という用語が含まれ、Studio に「sun」という用語が含まれるドキュメントを検索します。

    GoogleSQL

    SELECT AlbumId
FROM Albums
WHERE SEARCH(Title_Tokens, 'car') AND SEARCH(Studio_Tokens, 'sun')

    PostgreSQL

    SELECT albumid
FROM albums
WHERE spanner.search(title_tokens, 'car') AND spanner.search(studio_tokens, 'sun')

  • 分離: Title に「car」という用語が含まれるか、Studio に「sun」という用語が含まれるドキュメントを検索します。

    GoogleSQL

    SELECT AlbumId
FROM Albums
WHERE SEARCH(Title_Tokens, 'car') OR SEARCH(Studio_Tokens, 'sun')

    PostgreSQL

    SELECT albumid
FROM albums
WHERE spanner.search(title_tokens, 'car') OR spanner.search(studio_tokens, 'sun')

  • 否定: Title に「car」という用語が含まれていないすべてのドキュメントを検索します。

    GoogleSQL

    SELECT AlbumId
FROM Albums
WHERE NOT SEARCH(Title_Tokens, 'car')

    PostgreSQL

    SELECT albumid
FROM albums
WHERE NOT spanner.search(title_tokens, 'car')

    rquery 言語では、次のような検索を行うことができます。

    GoogleSQL

    SELECT AlbumId
FROM Albums
WHERE SEARCH(Title_Tokens, '-car')

    PostgreSQL

    SELECT albumid
FROM albums
WHERE spanner.search(title_tokens, '-car')

    どちらの形式でも、Title が NULL であるドキュメントがフィルタされます。トークン化関数と検索関数は、NULL 入力で NULL を返すように定義されています。SQL では、NOT NULL は NULL として定義されます。

また、同じ TOKENLIST 列を複数回参照することもできます。

GoogleSQL

SELECT AlbumId
FROM Albums
WHERE (SEARCH(Title_Tokens, 'car') OR SEARCH(Studio_Tokens, 'sun'))
  AND (SEARCH(Title_Tokens, 'guy') OR SEARCH(Studio_Tokens, electric))

PostgreSQL

SELECT albumid
FROM albums
WHERE (spanner.search(title_tokens, 'car') OR spanner.search(studio_tokens, 'sun'))
  AND (spanner.search(title_tokens, 'guy') OR spanner.search(studio_tokens, 'electric'))

同じ列内の複数のキーワードを検索するには、rquery 言語または SQL を使用します。パラメータ化されたクエリに対して効率的なクエリキャッシュが使用されるため、rquery を使用することをおすすめします。クエリ キャッシュのヒット率が高いことを除けば、rquery 言語と SQL 言語が持つレイテンシとパフォーマンスは同じです。

GoogleSQL

SELECT AlbumId
FROM Albums
WHERE SEARCH(Title_Tokens, 'car OR guy')

SELECT AlbumId
FROM Albums
WHERE SEARCH(Title_Tokens, 'car') OR SEARCH(Title_Tokens, 'guy')

PostgreSQL

SELECT albumid
FROM albums
WHERE spanner.search(title_tokens, 'car OR guy')

SELECT albumid
FROM albums
WHERE spanner.search(title_tokens, 'car') OR spanner.search(title_tokens, 'guy')

検索インデックスで高速化されたテキスト以外の条件を、全文検索関数を含む同じクエリで使用することもできます。

次のステップ