Eseguire un recupero efficiente dei risultati migliori

Molte applicazioni eseguono query su un database per compilare una singola pagina nelle loro applicazioni. In questi casi, l'applicazione non ha bisogno di tutte le corrispondenze, ma solo delle corrispondenze migliori 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 in un indice con corrispondenza ai primi k elementi.

Creare indici di ricerca per le corrispondenze top-k

Per configurare un indice di ricerca per la corrispondenza dei primi k elementi, utilizza ORDER BY per ordinare l'indice di ricerca in base a una colonna specifica. Le query devono contenere una clausola ORDER BY che corrisponda esattamente all'ordine di ordinamento dell'indice di ricerca (inclusa la direzione crescente rispetto a quella decrescente) 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 ulteriori informazioni, consulta Eseguire la paginazione delle query di ricerca.

Per alcuni casi d'uso, potrebbe essere opportuno gestire più indici di ricerca ordinati per colonne diverse. Come il partizionamento, è un compromesso tra spazio di archiviazione e costo di scrittura rispetto alla 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

Esegui query sugli indici di ricerca per le corrispondenze top-k

Come affermato in precedenza, le query devono contenere una clausola ORDER BY che corrisponda esattamente all'ordinamento dell'indice di ricerca (inclusa la direzione crescente rispetto a quella 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. Viene selezionato l'AlbumsRecordTimestampIndex indice. Anche se ci sono molti album con la parola "felice", la query esamina solo un numero limitato 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 è ugualmente 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 in modo efficiente una query top-k. Deve recuperare tutti gli album corrispondenti, ordinarli in base a ListenTimestamp, e restituire i 10 migliori. 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

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