執行高效的 top-k 擷取作業

許多應用程式會查詢資料庫，以便在應用程式中填入單一網頁。在這種應用程式中，應用程式不需要所有相符項目，只需要根據索引排序順序取得前 k 個相符項目。搜尋索引可非常有效率地實作這類搜尋。本頁面說明如何建立及搜尋含有前 k 項相符項目的索引。

為前 k 個相符項目建立搜尋索引

如要設定搜尋索引以進行前 k 項比對，請使用 ORDER BY 依特定欄位排序搜尋索引。查詢必須包含 ORDER BY 子句，才能與搜尋索引排序順序 (包括遞增或遞減方向) 完全相符，並且包含 LIMIT 子句，要求查詢在找到 k 個相符資料列後停止。

您也可以使用這些子句實作分頁功能。詳情請參閱「分頁顯示搜尋查詢」。

在某些用途中，您可能需要依不同欄位排序，維護多個搜尋索引。就像分割一樣，您必須在儲存空間和寫入成本與查詢延遲之間取得平衡。

舉例來說，請考慮使用下列結構定義的資料表：

GoogleSQL 

CREATE TABLE Albums (
  AlbumId STRING(MAX) NOT NULL,
  RecordTimestamp INT64 NOT NULL,
  ReleaseTimestamp INT64 NOT NULL,
  ListenTimestamp INT64 NOT NULL,
  AlbumTitle STRING(MAX),
  AlbumTitle_Tokens TOKENLIST AS (TOKENIZE_FULLTEXT(AlbumTitle)) HIDDEN
) PRIMARY KEY(AlbumId);

CREATE SEARCH INDEX AlbumsRecordTimestampIndex
ON Albums(AlbumTitle_Tokens, SingerId_Tokens)
STORING (ListenTimestamp)
ORDER BY RecordTimestamp DESC

CREATE SEARCH INDEX AlbumsReleaseTimestampIndex
ON Albums(AlbumTitle_Tokens)
STORING (ListenTimestamp)
ORDER BY ReleaseTimestamp DESC

PostgreSQL

CREATE TABLE albums (
  albumid character varying NOT NULL,
  recordtimestamp bigint NOT NULL,
  releasetimestamp bigint NOT NULL,
  listentimestamp bigint NOT NULL,
  albumtitle character varying,
  albumtitle_tokens spanner.tokenlist
      GENERATED ALWAYS AS (spanner.tokenize_fulltext(albumtitle)) VIRTUAL HIDDEN,
PRIMARY KEY(albumid));

CREATE SEARCH INDEX albumsrecordtimestampindex
ON Albums(albumtitle_tokens, singerid_tokens)
INCLUDE (listentimestamp)
ORDER BY recordtimestamp DESC

CREATE SEARCH INDEX albumsreleasetimestampindex
ON Albums(albumtitle_tokens)
INCLUDE (listentimestamp)
ORDER BY releasetimestamp DESC

查詢搜尋索引，以取得前 k 項相符項目

如先前所述，查詢必須包含 ORDER BY 子句，才能與搜尋索引排序順序 (包括遞增或遞減方向) 完全相符，並且包含 LIMIT 子句，要求查詢在找到 k 個相符資料列後停止。

下表分析了一些常見查詢的效率。

  • 這項查詢非常有效率。它會選取 AlbumsRecordTimestampIndex 索引。即使有許多專輯含有「happy」一詞，查詢只會掃描少數資料列：

    GoogleSQL 

    SELECT AlbumId
FROM Albums
WHERE SEARCH(AlbumTitle_Tokens, 'happy')
ORDER BY RecordTimestamp DESC
LIMIT 10

    PostgreSQL

    SELECT albumid
FROM albums
WHERE spanner.search(albumtitle_tokens, 'happy')
ORDER BY recordtimestamp DESC
LIMIT 10

  • 同樣的查詢要求以遞減順序依 ReleaseTimestamp 排序，會使用 AlbumsReleaseTimestampIndex 索引，效率也一樣高：

    GoogleSQL 

    SELECT AlbumId
FROM Albums
WHERE SEARCH(AlbumTitle_Tokens, 'happy')
ORDER BY ReleaseTimestamp DESC
LIMIT 10

    PostgreSQL

    SELECT albumid
FROM albums
WHERE spanner.search(albumtitle_tokens, 'happy')
ORDER BY releasetimestamp DESC
LIMIT 10

  • 要求依 ListenTimestamp 排序的查詢不會以有效率的方式執行前 k 項查詢。必須擷取所有相符的專輯，並依 ListenTimestamp, 排序，然後傳回前 10 個。如果有大量文件包含「happy」一詞，這類查詢會使用更多資源。

    GoogleSQL 

    SELECT AlbumId
FROM Albums
WHERE SEARCH(AlbumTitle_Tokens, 'happy')
ORDER BY ListenTimestamp DESC
LIMIT 10

    PostgreSQL

    SELECT albumid
FROM albums
WHERE spanner.search(albumtitle_tokens, 'happy')
ORDER BY listentimestamp DESC
LIMIT 10

  • 同樣地，如果查詢要求以遞增順序使用 RecordTimestamp 欄排序結果，查詢執行效率就會降低。即使有 LIMIT，它也會掃描所有包含「happy」一詞的資料列。

    GoogleSQL 

    SELECT AlbumId
FROM Albums
WHERE SEARCH(AlbumTitle_Tokens, 'happy')
ORDER BY RecordTimestamp ASC
LIMIT 10

    PostgreSQL

    SELECT albumid
FROM albums
WHERE spanner.search(albumtitle_tokens, 'happy')
ORDER BY recordtimestamp ASC
LIMIT 10

後續步驟