执行高效的 Top-K 检索

许多应用都会查询数据库，以填充应用中的单个页面。在此类应用中，应用不需要所有匹配项，只需要根据索引排列顺序得出的前 k 个匹配项。搜索索引可以非常高效地实现此类型的搜索。本页面介绍了如何创建和搜索具有 Top-K 匹配的索引。

为前 k 个匹配项创建搜索索引

如需为 Top-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 子句，以及一个用于在找到 k 个匹配行后请求查询停止的 LIMIT 子句。

以下列表分析了一些常见查询的效率。

  • 此查询非常高效。它会选择 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 列按升序对结果进行排序，则查询无法高效运行。它会扫描包含“happy”一词的所有行，即使有 LIMIT 也是如此。

    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

后续步骤