Antipattern PostgreSQL: "Mesti ada satu sahaja!"

Dalam SQL, anda menerangkan "apa" yang anda ingin capai, bukan "bagaimana" ia harus dilaksanakan. Oleh itu, masalah membangunkan pertanyaan SQL dalam gaya "seperti yang didengar adalah bagaimana ia ditulis" mengambil tempatnya, bersama-sama dengan ciri pengiraan keadaan dalam SQL.

Hari ini, menggunakan contoh yang sangat mudah, mari kita lihat perkara ini boleh membawa kepada konteks penggunaan GROUP/DISTINCT и LIMIT dengan mereka.

Sekarang, jika anda menulis dalam permintaan "mula-mula sambungkan tanda-tanda ini, dan kemudian buang semua pendua, sepatutnya hanya tinggal satu salinan untuk setiap kunci" - ini betul-betul bagaimana ia akan berfungsi, walaupun sambungan tidak diperlukan sama sekali.

Dan kadangkala anda bernasib baik dan ia "hanya berfungsi", kadangkala ia mempunyai kesan yang tidak menyenangkan pada prestasi, dan kadangkala ia memberikan kesan yang benar-benar tidak dijangka dari sudut pandangan pembangun.

Nah, mungkin tidak begitu hebat, tetapi...

“Pasangan manis”: JOIN + DISTINCT

SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

Ia akan menjadi jelas apa yang mereka mahu pilih rekod X yang mana terdapat rekod dalam Y yang berkaitan dengan syarat yang dipenuhi. Menulis permintaan melalui JOIN — mendapat beberapa nilai pk beberapa kali (tepat berapa banyak entri yang sesuai muncul dalam Y). Bagaimana untuk membuang? Sudah tentu DISTINCT!

Amat "menggembirakan" apabila untuk setiap rekod X terdapat beberapa ratus rekod Y yang berkaitan, dan kemudian pendua itu dikeluarkan dengan berani...

Bagaimana untuk membetulkan? Sebagai permulaan, sedar bahawa masalah itu boleh diubah suai kepada "pilih rekod X yang mana dalam Y terdapat SEKURANG-KURANGNYA SATU yang dikaitkan dengan syarat yang dipenuhi" - lagipun, kami tidak memerlukan apa-apa daripada rekod Y itu sendiri.

Bersarang WUJUD

SELECT
  *
FROM
  X
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  );

Sesetengah versi PostgreSQL memahami bahawa dalam EXISTS sudah cukup untuk mencari entri pertama yang muncul, entri yang lebih lama tidak. Oleh itu saya lebih suka untuk sentiasa menunjukkan LIMIT 1 dalam EXISTS.

SERTAI LATERAL

SELECT
  X.*
FROM
  X
, LATERAL (
    SELECT
      Y.*
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  ) Y
WHERE
  Y IS DISTINCT FROM NULL;

Pilihan yang sama membenarkan, jika perlu, untuk segera mengembalikan beberapa data daripada rekod Y berkaitan yang ditemui. Pilihan yang sama dibincangkan dalam artikel "Antipattern PostgreSQL: rekod yang jarang berlaku akan mencapai pertengahan JOIN".

“Mengapa bayar lebih”: DISTINCT [ON] + LIMIT 1

Manfaat tambahan daripada transformasi pertanyaan tersebut ialah keupayaan untuk mengehadkan carian rekod dengan mudah jika hanya satu atau beberapa daripadanya diperlukan, seperti dalam kes berikut:

SELECT DISTINCT ON(X.pk)
  *
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Sekarang kita membaca permintaan itu dan cuba memahami apa yang dicadangkan untuk dilakukan oleh DBMS:

• menghubungkan tanda-tanda
• unik oleh X.pk
• daripada entri yang tinggal, pilih satu

Jadi apa yang anda dapat? "Hanya satu penyertaan" daripada yang unik - dan jika kita mengambil yang tidak unik ini, adakah hasilnya akan berubah entah bagaimana?.. “Dan jika tiada perbezaan, mengapa perlu membayar lebih?”

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда можно подсунуть подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Dan topik yang sama dengan GROUP BY + LIMIT 1.

“Saya cuma perlu bertanya”: GROUP + LIMIT tersirat

Perkara yang sama berlaku pada berbeza pemeriksaan bukan kekosongan tanda atau CTE semasa permintaan berlanjutan:

...
CASE
  WHEN (
    SELECT
      count(*)
    FROM
      X
    LIMIT 1
  ) = 0 THEN ...

Fungsi agregat (count/min/max/sum/...) berjaya dilaksanakan pada keseluruhan set, walaupun tanpa arahan yang jelas GROUP BY. Hanya dengan LIMIT mereka tidak begitu mesra.

Pemaju boleh berfikir "jika terdapat rekod di sana, maka saya memerlukan tidak lebih daripada LIMIT". Tetapi jangan buat begitu! Kerana untuk asasnya ialah:

• mengira apa yang mereka mahu mengikut semua rekod
• berikan seberapa banyak baris yang mereka minta

Bergantung pada keadaan sasaran, adalah wajar untuk membuat salah satu daripada penggantian berikut:

• (count + LIMIT 1) = 0 pada NOT EXISTS(LIMIT 1)
• (count + LIMIT 1) > 0 pada EXISTS(LIMIT 1)
• count >= N pada (SELECT count(*) FROM (... LIMIT N))

"Berapa banyak untuk digantung dalam gram": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Pembangun yang naif mungkin benar-benar percaya bahawa permintaan itu akan berhenti dilaksanakan. sebaik sahaja kami menemui $1 daripada nilai berbeza pertama yang ditemui.

Pada masa akan datang ini mungkin dan akan berfungsi terima kasih kepada nod baharu Imbas Langkau Indeks, pelaksanaannya sedang diusahakan, tetapi belum.

Buat masa ini dulu semua rekod akan diambil semula, adalah unik, dan hanya daripada mereka jumlah yang diminta akan dikembalikan. Amat menyedihkan jika kita mahukan sesuatu seperti itu $ 1 = 4, dan terdapat ratusan ribu rekod dalam jadual...

Agar tidak sedih dengan sia-sia, mari gunakan pertanyaan rekursif "DISTINCT adalah untuk orang miskin" dari PostgreSQL Wiki:

Sumber: www.habr.com