효율적인 Top-K 검색 수행

많은 애플리케이션에서 데이터베이스를 쿼리하여 애플리케이션의 단일 페이지를 채웁니다. 이러한 애플리케이션에서는 일치 항목 전부가 아닌 색인 정렬 순서를 기반으로 한 Top-K 일치 항목만 필요합니다. 검색 색인은 이러한 유형의 검색을 매우 효율적으로 구현할 수 있습니다. 이 페이지에서는 Top-K 일치 항목이 있는 색인을 만들고 검색하는 방법을 설명합니다.

Top-K 일치 항목의 검색 색인 만들기

Top-K 일치를 위해 검색 색인을 구성하려면 ORDER BY를 사용하여 특정 열을 기준으로 검색 색인을 정렬합니다. 쿼리에는 검색 색인 정렬 순서(오름차순 및 내림차순 방향 포함)와 정확하게 일치하는 ORDER BY 절과 Top-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

Top-K 일치 항목의 검색 색인 쿼리

앞에서 설명했듯이 쿼리에는 검색 색인 정렬 순서(오름차순 및 내림차순 방향 포함)와 정확하게 일치하는 ORDER BY 절과 Top-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 열을 사용하여 결과를 오름차순으로 정렬하도록 요청하는 쿼리도 효율적으로 실행되지 않습니다. 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

다음 단계