Melakukan pengambilan top-k yang efisien

Banyak aplikasi membuat kueri database untuk mengisi satu halaman dalam aplikasi mereka. Dalam aplikasi tersebut, aplikasi tidak memerlukan semua kecocokan, tetapi hanya kecocokan top-k berdasarkan urutan pengurutan indeks. Indeks penelusuran dapat menerapkan jenis penelusuran ini dengan sangat efisien. Halaman ini menjelaskan cara membuat dan menelusuri indeks yang memiliki pencocokan top-k.

Membuat indeks penelusuran untuk kecocokan top-k

Untuk mengonfigurasi indeks penelusuran untuk pencocokan top-k, gunakan ORDER BY untuk mengurutkan indeks penelusuran menurut kolom tertentu. Kueri harus memiliki klausa ORDER BY yang sama persis dengan urutan pengurutan indeks penelusuran (termasuk arah menaik versus menurun) dan klausa LIMIT yang meminta kueri untuk berhenti setelah menemukan baris yang cocok dengan k.

Anda juga dapat menerapkan penomoran halaman menggunakan klausa ini. Untuk informasi selengkapnya, lihat Membuat penomoran halaman kueri penelusuran.

Untuk beberapa kasus penggunaan, sebaiknya pertahankan beberapa indeks penelusuran yang diurutkan menurut kolom yang berbeda. Seperti partisi, ini adalah kompromi antara biaya penyimpanan dan tulis versus latensi kueri.

Misalnya, pertimbangkan tabel yang menggunakan skema berikut:

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

Membuat kueri indeks penelusuran untuk kecocokan top-k

Seperti yang dinyatakan sebelumnya, kueri harus memiliki klausa ORDER BY yang sama persis dengan urutan pengurutan indeks penelusuran (termasuk arah menaik versus menurun) dan klausa LIMIT yang meminta kueri untuk berhenti setelah menemukan baris yang cocok dengan k.

Daftar berikut menganalisis efisiensi beberapa kueri umum.

  • Kueri ini sangat efisien. Fungsi ini memilih indeks AlbumsRecordTimestampIndex. Meskipun ada banyak album dengan kata "happy", kueri hanya memindai sejumlah kecil baris:

    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

  • Kueri yang sama, yang meminta urutan pengurutan menurut ReleaseTimestamp dalam urutan menurun, menggunakan indeks AlbumsReleaseTimestampIndex dan sama-sama efisien:

    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

  • Kueri yang meminta urutan pengurutan menurut ListenTimestamp tidak menjalankan kueri top-k secara efisien. Fungsi ini harus mengambil semua album yang cocok, mengurutkannya berdasarkan ListenTimestamp,, dan menampilkan 10 teratas. Kueri tersebut menggunakan lebih banyak resource jika ada banyak dokumen yang berisi istilah "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

  • Demikian pula, kueri tidak berjalan secara efisien jika meminta agar hasil diurutkan menggunakan kolom RecordTimestamp dalam urutan menaik. Fungsi ini memindai semua baris dengan kata "happy", meskipun memiliki 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

Langkah berikutnya