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

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

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

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

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

在某些情況下，維護依不同資料欄排序的多個搜尋索引可能很有意義。與分割類似，這是在儲存空間和寫入費用與查詢延遲之間取得平衡。

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

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 排序的查詢無法有效執行 top-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

後續步驟