Eseguire un recupero efficiente dei risultati migliori

Molte applicazioni eseguono query su un database per popolare una singola pagina delle loro applicazioni. In queste applicazioni, non sono necessarie tutte le corrispondenze, ma solo le prime k in base all'ordine di ordinamento dell'indice. Gli indici di ricerca possono implementare questo tipo di ricerca in modo molto efficiente. Questa pagina descrive come creare e cercare un indice con corrispondenza top-k.

Crea indici di ricerca per le prime k corrispondenze

Per configurare un indice di ricerca per la corrispondenza top-k, utilizza ORDER BY per ordinare l'indice di ricerca in base a una colonna specifica. Le query devono avere una clausola ORDER BY che corrisponda esattamente all'ordinamento dell'indice di ricerca (inclusa la direzione crescente o descrescente) e una clausola LIMIT che richieda l'interruzione della query dopo aver trovato k righe corrispondenti.

Puoi anche implementare la paginazione utilizzando queste clausole. Per saperne di più, consulta la sezione Impaginare le query di ricerca.

Per alcuni casi d'uso, potrebbe essere utile mantenere più indici di ricerca ordinati in base a colonne diverse. Come il partizionamento, è un compromesso tra costi di archiviazione e scrittura e latenza delle query.

Ad esempio, considera una tabella che utilizza lo schema seguente:

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

Eseguire query sugli indici di ricerca per le prime k corrispondenze

Come indicato in precedenza, le query devono avere una clausola ORDER BY che corrisponda esattamente all'ordinamento dell'indice della Ricerca (inclusa la direzione crescente o decrescente) e una clausola LIMIT che richieda l'interruzione della query dopo aver trovato k righe corrispondenti.

Il seguente elenco analizza l'efficienza di alcune query comuni.

  • Questa query è molto efficiente. Seleziona l'indice AlbumsRecordTimestampIndex. Anche se ci sono molti album con la parola "happy", la query analizza solo un numero ridotto di righe:

    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

  • La stessa query, che richiede l'ordinamento per ReleaseTimestamp in ordine decrescente, utilizza l'indice AlbumsReleaseTimestampIndex ed è altrettanto efficiente:

    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

  • Una query che richiede l'ordinamento per ListenTimestamp non esegue una query top-k in modo efficiente. Deve recuperare tutti gli album corrispondenti, ordinarli per ListenTimestamp, e restituire i primi 10. Una query di questo tipo utilizza più risorse se esiste un numero elevato di documenti che contengono il termine "felice".

    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

  • Allo stesso modo, una query non viene eseguita in modo efficiente se richiede che i risultati vengano ordinati utilizzando la colonna RecordTimestamp in ordine crescente. Scansiona tutte le righe con la parola "happy", nonostante la presenza di un 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

Passaggi successivi