PostgreSQL Antipatterns: "يجب أن يكون هناك واحد فقط!"

في 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 نفسه.

متداخلة EXISTS

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

تدرك بعض إصدارات PostgreSQL أنه يكفي في EXISTS العثور على السجل الأول الذي يتم عرضه ، في حين أن السجلات القديمة لا تفعل ذلك. لذلك ، أفضل الإشارة دائمًا LIMIT 1 داخل EXISTS.

انضمام 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;

يسمح نفس الخيار ، إذا لزم الأمر ، بإرجاع بعض البيانات على الفور من السجل Y المرتبط الذي تم العثور عليه في نفس الوقت. تمت مناقشة خيار مماثل في المقالة "PostgreSQL Antipatterns: سجل نادر سيصل إلى منتصف JOIN".

"لماذا تدفع أكثر": DISTINCT [ON] + 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.

"أنا فقط يجب أن أسأل": مجموعة ضمنية + حد

تحدث أشياء متشابهة بشكل مختلف شيكات عدم الإفراغ تسميات أو 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 for the Poor" من PostgreSQL Wiki:

المصدر: www.habr.com