Effizienten Top-k-Abruf durchführen

Viele Anwendungen fragen eine Datenbank ab, um eine einzelne Seite in ihren Anwendungen zu füllen. In solchen Anwendungen sind nicht alle Übereinstimmungen erforderlich, sondern nur die k besten Übereinstimmungen basierend auf der Sortierreihenfolge des Index. Suchindexe können diese Art der Suche sehr effizient implementieren. Auf dieser Seite wird beschrieben, wie Sie einen Index mit Top-k-Abgleich erstellen und durchsuchen.

Suchindexe für die k besten Übereinstimmungen erstellen

Wenn Sie einen Suchindex für Top-k-Abgleich konfigurieren möchten, verwenden Sie ORDER BY, um den Suchindex nach einer bestimmten Spalte zu sortieren. Abfragen müssen eine ORDER BY-Anweisung enthalten, die genau der Sortierreihenfolge des Suchindex entspricht (einschließlich aufsteigender und absteigender Richtung), sowie eine LIMIT-Klausel, die angibt, dass die Abfrage nach dem Auffinden von k übereinstimmenden Zeilen beendet werden soll.

Sie können diese Klauseln auch verwenden, um die Paginierung zu implementieren. Weitere Informationen finden Sie unter Suchanfragen paginieren.

In einigen Anwendungsfällen kann es sinnvoll sein, mehrere Suchindexe zu verwalten, die nach verschiedenen Spalten sortiert sind. Wie bei der Partitionierung ist es ein Kompromiss zwischen Speicher- und Schreibkosten und der Abfragelatenz.

Betrachten Sie beispielsweise eine Tabelle mit dem folgenden Schema:

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

Suchindexe nach den k besten Übereinstimmungen abfragen

Wie bereits erwähnt, müssen Abfragen eine ORDER BY-Anweisung enthalten, die genau der Sortierreihenfolge des Suchindex entspricht (einschließlich aufsteigender und absteigender Richtung), sowie eine LIMIT-Klausel, die angibt, dass die Abfrage nach dem Auffinden von k übereinstimmenden Zeilen beendet werden soll.

In der folgenden Liste wird die Effizienz einiger häufiger Abfragen analysiert.

  • Diese Abfrage ist sehr effizient. Damit wird der Index AlbumsRecordTimestampIndex ausgewählt. Auch wenn es viele Alben mit dem Wort „happy“ gibt, werden bei der Abfrage nur wenige Zeilen durchsucht:

    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

  • Für dieselbe Abfrage, bei der die Sortierreihenfolge nach ReleaseTimestamp in absteigender Reihenfolge angefordert wird, wird der AlbumsReleaseTimestampIndex-Index verwendet. Sie ist genauso effizient:

    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

  • Bei einer Abfrage, in der die Sortierreihenfolge nach ListenTimestamp angefordert wird, wird keine Top-k-Abfrage effizient ausgeführt. Es müssen alle übereinstimmenden Alben abgerufen, nach ListenTimestamp, sortiert und die 10 besten zurückgegeben werden. Eine solche Abfrage verbraucht mehr Ressourcen, wenn es eine große Anzahl von Dokumenten gibt, die den Begriff „happy“ enthalten.

    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

  • Eine Abfrage wird auch nicht effizient ausgeführt, wenn die Ergebnisse mit der Spalte RecordTimestamp in aufsteigender Reihenfolge sortiert werden sollen. Es werden alle Zeilen mit dem Wort „happy“ gescannt, obwohl ein LIMIT vorhanden ist.

    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

Nächste Schritte