Antipatterns PostgreSQL: "فقط باید یک مورد باقی بماند!"

در SQL، "آنچه" را که می خواهید به دست آورید، توصیف می کنید، نه "چگونه" باید اجرا شود. بنابراین، مشکل توسعه پرس و جوهای SQL به سبک "همانطور که شنیده می شود نحوه نگارش است" جای افتخار خود را به همراه دارد. ویژگی های محاسبه شرایط در SQL.

امروز، با استفاده از مثال‌های بسیار ساده، بیایید ببینیم که این امر در زمینه استفاده به چه چیزی منجر می‌شود GROUP/DISTINCT и LIMIT با آنها.

حالا اگر در درخواست نوشته بودید ابتدا این علائم را به هم وصل کنید و سپس همه موارد تکراری را بیرون بیاورید. فقط باید یکی باقی بماند برای هر کلید کپی کنید" - این دقیقاً چگونه کار خواهد کرد، حتی اگر اتصال اصلاً مورد نیاز نباشد.

و گاهی اوقات شما خوش شانس هستید و "فقط کار می کند" ، گاهی اوقات تأثیر ناخوشایندی بر عملکرد دارد و گاهی اوقات جلوه هایی می دهد که از نظر توسعه دهنده کاملاً غیرمنتظره هستند.

خب، شاید چندان دیدنی نباشد، اما...

"زوج شیرین": JOIN + DISTINCT

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

معلوم می شد که چه می خواهند رکوردهای X را انتخاب کنید که برای آنها رکوردهایی در Y وجود دارد که مربوط به شرط انجام شده است. درخواستی از طریق نوشت JOIN - چندین مقدار pk دریافت کرد (دقیقاً چند ورودی مناسب در Y ظاهر شد). چگونه حذف کنیم؟ قطعا DISTINCT!

زمانی که برای هر رکورد X چندین صد رکورد Y مرتبط وجود دارد و سپس تکرارها قهرمانانه حذف می شوند، "خوشحال کننده" است...

چگونه رفع کنیم؟ برای شروع، متوجه شوید که مشکل را می توان اصلاح کرد "رکوردهای X را انتخاب کنید که در Y حداقل یکی با شرط انجام شده مرتبط است" - پس از همه، ما به چیزی از خود رکورد Y نیاز نداریم.

تو در تو وجود دارد

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

برخی از نسخه‌های PostgreSQL می‌دانند که در EXISTS کافی است اولین ورودی را پیدا کنید، اما نسخه‌های قدیمی‌تر این کار را نمی‌کنند. بنابراین ترجیح می دهم همیشه نشان دهم LIMIT 1 در داخل EXISTS.

پیوستن جانبی

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;

همین گزینه اجازه می‌دهد، در صورت لزوم، فوراً برخی از داده‌ها را از رکورد Y مرتبط یافت شده بازگرداند. گزینه مشابهی در مقاله مورد بحث قرار گرفته است "PostgreSQL Antipatterns: یک رکورد نادر به وسط یک JOIN می رسد".

«چرا بیشتر بپردازید»: DISTINCT [روشن] + LIMIT 1

یکی از مزایای دیگر چنین تبدیل‌های پرس و جو این است که در صورت نیاز به یک یا چند مورد از آنها به راحتی جستجوی رکوردها را محدود می‌کند، مانند مورد زیر:

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

اکنون ما درخواست را می خوانیم و سعی می کنیم بفهمیم DBMS چه کاری را پیشنهاد می کند:

• اتصال علائم
• منحصر به فرد توسط X.pk
• از ورودی های باقی مانده، یکی را انتخاب کنید

پس چی گرفتی؟ "فقط یک ورودی" از موارد منحصر به فرد - و اگر این یکی از غیر منحصر به فردها را در نظر بگیریم، آیا نتیجه به نحوی تغییر می کند؟.. "و اگر تفاوتی وجود ندارد، چرا بیشتر پرداخت کنید؟"

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

و دقیقا همین موضوع با GROUP BY + LIMIT 1.

"فقط باید بپرسم": ضمنی GROUP + LIMIT

موارد مشابه در موارد مختلف رخ می دهد چک های غیر خالی با پیشرفت درخواست علامت یا CTE می دهد:

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

توابع مجموع (count/min/max/sum/...) با موفقیت در کل مجموعه حتی بدون دستورالعمل های صریح اجرا می شوند GROUP BY. فقط با LIMIT آنها خیلی دوستانه نیستند.

توسعه دهنده می تواند فکر کند "اگر سوابق وجود دارد، پس من به LIMIT بیشتر نیاز ندارم". اما این کار را نکن! زیرا برای پایه این است:

• بشمارند چه می خواهند طبق تمام سوابق
• هر چقدر که می خواهند خط بدهند

بسته به شرایط هدف، انجام یکی از جایگزین های زیر مناسب است:

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

"چقدر به گرم آویزان شود": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

یک توسعه دهنده ساده لوح ممکن است صادقانه معتقد باشد که اجرای درخواست متوقف می شود. به محض اینکه 1 دلار از اولین مقادیر متفاوتی را پیدا کردیم.

در آینده ممکن است به لطف یک گره جدید این کار انجام شود و کار خواهد کرد فهرست پرش اسکن، که اجرای آن در حال حاضر در حال انجام است، اما هنوز انجام نشده است.

فعلا اول تمام رکوردها بازیابی خواهند شد، منحصر به فرد هستند و فقط از آنها مبلغ درخواستی عودت داده می شود. به خصوص غم انگیز است اگر ما چیزی شبیه به آن را بخواهیم $ 1 = 4و صدها هزار رکورد در جدول وجود دارد...

برای اینکه بیهوده غمگین نباشیم، از یک پرس و جو بازگشتی استفاده کنیم "DISTINCT برای فقرا است" از PostgreSQL Wiki:

منبع: www.habr.com