ProHoster > Blog > Pentadbiran > Antipattern PostgreSQL: Menavigasi Pendaftaran

Antipattern PostgreSQL: Menavigasi Pendaftaran

Hari ini tidak akan ada kes yang rumit dan algoritma yang canggih dalam SQL. Segala-galanya akan menjadi sangat mudah, pada tahap Kapten Obvious - mari lakukannya melihat daftar acara disusun mengikut masa.

Iaitu, terdapat tanda dalam pangkalan data events, dan dia mempunyai ladang ts - tepat pada masa kami mahu memaparkan rekod ini dengan teratur:

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

Sudah jelas bahawa kami tidak akan mempunyai sedozen rekod di sana, jadi kami memerlukan beberapa bentuk navigasi halaman.

#0. "Saya ahli pogrom ibu saya"

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

Ia hampir bukan jenaka - ia jarang berlaku, tetapi ditemui di alam liar. Kadangkala, selepas bekerja dengan ORM, sukar untuk beralih kepada kerja "mengarahkan" dengan SQL.

Tetapi mari kita beralih kepada masalah yang lebih biasa dan kurang jelas.

#1. OFFSET

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - записСй Π½Π° страницС, $1 - Π½Π°Ρ‡Π°Π»ΠΎ страницы

Dari mana datangnya nombor 26? Ini ialah anggaran bilangan penyertaan untuk mengisi satu skrin. Lebih tepat lagi, 25 rekod dipaparkan, ditambah 1, menandakan bahawa terdapat sekurang-kurangnya sesuatu yang lain lagi dalam sampel dan masuk akal untuk meneruskan.

Sudah tentu, nilai ini tidak boleh "dijahit" ke dalam badan permintaan, tetapi melalui parameter. Tetapi dalam kes ini, penjadual PostgreSQL tidak akan dapat bergantung pada pengetahuan bahawa terdapat sedikit rekod - dan dengan mudah akan memilih pelan yang tidak berkesan.

Dan semasa dalam antara muka aplikasi, melihat pendaftaran dilaksanakan sebagai bertukar antara "halaman" visual, tiada siapa yang perasan apa-apa yang mencurigakan untuk masa yang lama. Tepat sehingga saat apabila, dalam perjuangan untuk kemudahan, UI/UX memutuskan untuk membuat semula antara muka kepada "tatal tidak berkesudahan" - iaitu, semua entri pendaftaran dilukis dalam satu senarai yang pengguna boleh tatal ke atas dan ke bawah.

Oleh itu, semasa ujian seterusnya, anda ditangkap pertindihan rekod dalam pendaftaran. Mengapa, kerana jadual mempunyai indeks biasa (ts), pertanyaan anda bergantung pada?

Tepat kerana anda tidak mengambil kira itu ts bukan kunci unik dalam jadual ini. Sebenarnya, dan nilainya tidak unik, seperti mana-mana "masa" dalam keadaan sebenar - oleh itu, rekod yang sama dalam dua pertanyaan bersebelahan dengan mudah "melompat" dari halaman ke halaman disebabkan oleh susunan akhir yang berbeza dalam rangka mengisih nilai kunci yang sama.

Malah, terdapat juga masalah kedua yang tersembunyi di sini, yang jauh lebih sukar untuk diperhatikan - beberapa entri tidak akan ditunjukkan sama sekali! Lagipun, rekod "pendua" mengambil tempat orang lain. Penjelasan terperinci dengan gambar yang cantik boleh didapati baca di sini.

Memperluas indeks

Pembangun yang licik memahami bahawa kunci indeks perlu dibuat unik, dan cara paling mudah ialah mengembangkannya dengan medan yang jelas unik, yang sesuai untuk PK:

CREATE UNIQUE INDEX ON events(ts DESC, id DESC);

Dan permintaan itu bermutasi:

SELECT
  ...
ORDER BY
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

#2. Tukar kepada "kursor"

Beberapa ketika kemudian, DBA datang kepada anda dan "berbesar hati" atas permintaan anda mereka memuatkan pelayan seperti neraka dengan peraturan OFFSET mereka, dan secara umum, sudah tiba masanya untuk beralih kepada navigasi daripada nilai terakhir yang ditunjukkan. Pertanyaan anda bermutasi lagi:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- послСдниС ΠΏΠΎΠ»ΡƒΡ‡Π΅Π½Π½Ρ‹Π΅ Π½Π° ΠΏΡ€Π΅Π΄Ρ‹Π΄ΡƒΡ‰Π΅ΠΌ шагС значСния
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Anda menarik nafas lega sehingga ia datang...

#3. Membersihkan indeks

Kerana suatu hari DBA anda membaca artikel tentang mencari indeks yang tidak berkesan dan menyedari bahawa Cap masa "bukan yang terkini" tidak bagus. Dan saya datang kepada anda sekali lagi - kini dengan pemikiran bahawa indeks itu masih harus kembali ke (ts DESC).

Tetapi apa yang perlu dilakukan dengan masalah awal "melompat" rekod antara halaman?.. Dan semuanya mudah - anda perlu memilih blok dengan bilangan rekod yang tidak tetap!

Secara umum, siapa yang melarang kita membaca bukan "tepat 26", tetapi "tidak kurang daripada 26"? Sebagai contoh, supaya di blok seterusnya ada rekod dengan makna yang jelas berbeza ts - maka tidak akan ada masalah dengan rekod "melompat" antara blok!

Berikut ialah cara untuk mencapai ini:

SELECT
  ...
WHERE
  ts < $1 AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

Apa yang berlaku di sini?

  1. Kami langkah 25 merekodkan "turun" dan mendapatkan nilai "sempadan". ts.
  2. Jika tiada apa-apa di sana, maka gantikan nilai NULL dengan -infinity.
  3. Kami menolak keseluruhan segmen nilai antara nilai yang diterima ts dan parameter $1 diluluskan daripada antara muka (nilai "terakhir" yang diberikan sebelumnya).
  4. Jika blok dikembalikan dengan kurang daripada 26 rekod, ia adalah yang terakhir.

Atau gambar yang sama:
Antipattern PostgreSQL: Menavigasi Pendaftaran

Kerana sekarang kita ada sampel tidak mempunyai apa-apa "permulaan" khusus, maka tiada apa yang menghalang kami daripada "mengembangkan" permintaan ini ke arah yang bertentangan dan melaksanakan pemuatan dinamik blok data daripada "titik rujukan" dalam kedua-dua arah - ke bawah dan ke atas.

Nota:

  1. Ya, dalam kes ini kita mengakses indeks dua kali, tetapi semuanya "semata-mata mengikut indeks". Oleh itu, subkueri hanya akan menghasilkan kepada satu Imbasan Indeks Sahaja tambahan.
  2. Agak jelas bahawa teknik ini hanya boleh digunakan apabila anda mempunyai nilai ts boleh menyeberang hanya secara kebetulan, dan tidak ramai daripada mereka. Jika kes biasa anda ialah "sejuta rekod pada 00:00:00.000", anda tidak sepatutnya melakukan ini. Maksud saya, anda tidak sepatutnya membenarkan kes sedemikian berlaku. Tetapi jika ini berlaku, gunakan pilihan dengan indeks lanjutan.

Sumber: www.habr.com

Tambah komen