DanLevy.net

اختبار: Postgres المتعمق: الجزء 2

هل أنت SQL صلب؟ أم SQL لين؟

الجزء 2 من 2. العودة إلى الجزء 1

مرحبًا بك في الجزء 2 من اختبار Deep Postres Quiz الخاص بي!

يغطي هذا الاختبار مزيجًا من ميزات PostgreSQL المألوفة والأقل شهرة والمفاجآت: JOIN، NULL، SQL القياسي (ANSI)، المعاملات، التفاصيل الداخلية، الفهرسة، أخذ عينات البيانات… والمزيد!

حظ سعيد! 🍀

1. 

إحماء: JOINs

أي من التالي ليس ❌ تعبير JOIN صالحًا في PostgreSQL؟

كنت تعرف CROSS JOIN، أليس كذلك؟

هل جعلك JOIN ALL تفكر في عملية أخرى، UNION ALL؟

الإجابة هنا هي JOIN ALL ❌.

أنواع JOIN الصحيحة هي:

  • INNER JOIN (JOIN الافتراضي)
  • LEFT JOIN (أو LEFT OUTER JOIN)
  • RIGHT JOIN (أو RIGHT OUTER JOIN)
  • FULL JOIN (أو FULL OUTER JOIN)
  • CROSS JOIN (المنتج الديكارتي، ما لم يتم تصفيته لاحقًا بشرط WHERE)

2. 

أعمدة الهوية

ما هي الصيغة (معيار SQL) لعمود IDENTITY ذي الزيادة التلقائية في PostgreSQL (الإصدار 10+)؟

بينما لا يزال SERIAL شائع الاستخدام، فإن الطريقة وفق معيار SQL هي:

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • id INT IDENTITY(1,1) هي صيغة SQL Server.
  • id INT AUTO_INCREMENT هي صيغة MySQL.
  • id INTEGER AUTO_INCREMENT PRIMARY KEY هي صيغة MySQL.

وهذا يوفر عدة مزايا مقارنة بـ SERIAL:

  • متوافق مع معيار SQL
  • معالجة أفضل لملكية التسلسل
  • دلالات أوضح حول الإدراج اليدوي
  • يعمل بشكل أفضل مع النسخ المتماثل

SERIAL هو في الواقع مجرد اختصار لإنشاء تسلسل وتعيين قيمة افتراضية، مما قد يؤدي إلى مشاكل في التفريغ والاستعادة.

3. 

ANSI SQL

أي من هذه الميزات في PostgreSQL ليست ❌ جزءًا من معيار SQL ANSI؟

أمثلة على إضافات PostgreSQL إلى SQL ANSI:

  • عبارة RETURNING.
  • ILIKE للمطابقة غير الحساسة لحالة الأحرف.
  • أنواع المصفوفات والعمليات.
  • أنواع JSON/JSONB.

يتضمن معيار SQL ANSI الأصلي:

  • WITH (التعبيرات الجدولية الشائعة).
  • LIKE للمطابقة الأنماط.
  • وصلات LATERAL.
  • دوال النوافذ.

4. 

المعاملات

باستثناء “يعتمد على التأثيرات الجانبية” ماذا يحدث بعد حدوث خطأ؟

BEGIN;
UPDATE students
  SET status = 'alumni'
  WHERE grade = 12;
UPDATE students
  SET graduation_date = CURRENT_DATE
  WHERE grade = 12;
UPDATE students
  SET grade = 'GRADUATED'; -- 🚨 Error: type mismatch
COMMIT;

عند حدوث خطأ هنا:

  • يتم التراجع عن المعاملة بأكملها
  • لا يتم التزام أي تغييرات
  • يتم وضع علامة فشل على المعاملة

للالتزام الجزئي:

BEGIN;
UPDATE ...;
SAVEPOINT my_save;
UPDATE ...; -- Error
ROLLBACK TO my_save;
COMMIT;

5. 

داخلية SQL

أي شرط ON يمكن لـ PostgreSQL استخدامه في HASH JOIN؟

SELECT *
FROM students a
JOIN grades b
ON /* What goes here? ⁉ */;

يمكن استخدام hash join مع شرط المساواة (=) في جملة ON.

  • = هو شكل المسند الأساسي لـ hash joins
  • > و >= ليسا من مسندات hash join
  • HASH JOIN ليس بناء جملة صالحًا في PostgreSQL

قد يختار planner still loop متداخل أو merge join اعتمادًا على حجم الجدول والإحصائيات والفهارس وإعدادات الذاكرة.

6. 

الفهرسة

أي فهرس هو الأفضل لهذا الاستعلام؟

-- Query:
SELECT * FROM students
WHERE grade_level = 42
  AND last_name IN ('Levy', 'Smith');

الشيء المدهش هنا هو أنه يمكن استخدام فهرس متعدد الأعمدة حتى عندما لا يتطابق ترتيب الأعمدة في نص الاستعلام مع تعريف الفهرس.

ملاحظة: مع فهارس B-tree، العمود الرئيسي هو المهم. نظرًا لأن هذا الاستعلام يقوم بالتصفية حسب grade_level و last_name، فإن الفهرس الذي يبدأ بـ grade_level ثم last_name هو مناسب.

كلما تمكنت SQL من تحديد أقل عدد من الصفوف بشكل أسرع، كان الأداء أفضل.

على سبيل المثال، إذا كان لديك جدول task، وتعرف أن status أكثر انتقائية من created_at، فقد تختار فهرسًا يبدأ بـ status لاستعلام مثل status = 'done' AND created_at > '2024-01-01'. ترتيب الشروط في نص جملة WHERE ليس هو المهم؛ ترتيب أعمدة الفهرس وأشكال المسندات هي المهمة.

يمكن استخدام فهرس B+Tree من أجل:

  • العمود الرئيسي، أو بادئة يسرى من الأعمدة.
  • عوامل تشغيل مثل =، >، <، BETWEEN، و IN، اعتمادًا على شكل الاستعلام.
  • مطابقة البادئة، LIKE 'prefix%'.

بفضل u/mwdb2 للإشارة إلى خطأ سابق في هذا الشرح!

7. 

الاقتباس 1

أي من التالي صحيح لأسماء الأعمدة الحساسة لحالة الأحرف؟

CREATE TABLE grades (
  "Student Name" VARCHAR(100),
  'Student Grade' INTEGER
);

في PostgreSQL:

  • علامات الاقتباس المزدوجة (") للمُعرّفات (أسماء الجداول، أسماء الأعمدة)
  • علامات الاقتباس المفردة (') للنصوص الحرفية

أفضل الممارسات:

  • تجنب الأحرف المختلطة أو المسافات في الأسماء
  • استخدم snake_case للمُعرّفات
  • استخدم المُعرّفات المقتبسة فقط عند الضرورة القصوى

8. 

الاقتباس 2

كيف تحدد اسم عمود يحتوي على نقطة؟

CREATE TABLE teachers (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  first.name TEXT,
  -- How do you include the column: first.name?
  salary NUMERIC
);

يستخدم PostgreSQL علامات الاقتباس المزدوجة لاقتباس المعرفات:

  • علامات الاقتباس المزدوجة "..." للمعرفات (أسماء الأعمدة، أسماء الجداول)
  • علامات الاقتباس المفردة '...' للنصوص الحرفية
  • الصيغة first.name تُفسر على أنها table_name.column_name

بدون علامات الاقتباس، سيتم تفسير النقطة في اسم العمود كفاصل بين المخطط/الجدول! بالإضافة إلى ذلك، جميع المعرفات غير المقتبسة تُحوّل إلى أحرف صغيرة افتراضيًا.

لتقليل المفاجآت، من الأفضل استخدام snake_case وتجنب استخدام الأحرف الخاصة.

9. 

أخذ العينات من البيانات

ما هي الصيغة الصحيحة لأخذ عينة عشوائية بنسبة 10% من students؟

هذا السؤال صعب! يحتوي على حوالي 6 فخاخ، بعضها يشمل:

  1. وصف عالي المستوى + أجزاء من الصيغة. يختبر كيف تتكيف وتجمع المعلومات غير المكتملة.
  2. يتطلب معرفة مسبقة بصيغة TABLESAMPLE، حيث لا تستخدم أي من خيارات TABLESAMPLE الصيغة الصحيحة! (شرير! أعلم! استمر، أنا أثق بك!!! ❤️)
  3. ROW_NUMBER() OVER (ORDER BY RAND()) و ORDER BY RANDOM() > 10 هما مجرد تشويش. يبدوان صحيحين لكنهما ليسا كذلك. RAND() غير موجود. و RANDOM() > 10 هو تعبير منطقي سيكون دائمًا خطأ لأن RANDOM() دائمًا في النطاق 0.0-1.0. مشكلة أخرى، ORDER BY RANDOM() > 10 سيرتب أيضًا بناءً على قيمة منطقية، وليس رقمًا عشوائيًا. لكن > 10 بدا وكأنه يمكن أن يكون نسبة مئوية، أليس كذلك؟
  4. BERNOULLI صحيح، لكن SAMPLETABLE ليس كذلك.
  5. WHERE RANDOM() >= 0.1 هو فخ! RANDOM() تُرجع قيمة في النطاق 0.0 <= x < 1.0، لذا >= 0.1 سترجع حوالي 90% من الصفوف، وليس 10%.

بالنسبة لطريقة TABLESAMPLE، يدعم PostgreSQL طريقتين لأخذ العينات:

  • BERNOULLI: كل صف له احتمال متساوٍ
  • SYSTEM: أخذ عينات على مستوى الكتلة (أسرع لكن أقل عشوائية)

لمعرفة المزيد عن أخذ العينات، اطّلع على هذه المقالة من Render.

مثال:

-- Sample 10% of rows
SELECT * FROM students TABLESAMPLE BERNOULLI (10);


-- For repeatable results, use REPEATABLE:
SELECT * FROM students
TABLESAMPLE BERNOULLI (10) REPEATABLE (42);

أيضًا جدير بالذكر، TABLESAMPLE تم الإبلاغ أنه ليس عشوائيًا جدًا. إذا كنت بحاجة إلى عينة بحجم ثابت أكثر عشوائية، فكر في استخدام ORDER BY RANDOM() LIMIT 10. لكن كن على علم أن هذا يُرجع 10 صفوف، وليس 10%، ويمكن أن يكون بطيئًا على الجداول الكبيرة.

هناك أيضًا بعض الإضافات الخارجية التي توفر طرقًا أفضل لأخذ العينات العشوائية، لكن هذا خارج نطاق هذا السؤال! 😅

10. 

الفهرسة

ماذا تفعل هذه التعويذة؟

CREATE INDEX idx_active_students
ON students(last_name)
WHERE status = 'active';

تُسمى هذه الحيلة Partial Index.

وهي مفيدة لتقليل استخدام القرص وتسريع الاستعلامات عندما يتم الاستعلام عادةً عن مجموعة فرعية فقط من الصفوف.

سيستخدم Postgres الفهرس فقط عندما يكون الشرط status = 'active' موجودًا في الاستعلام.

تذكير: لا يدعم Postgres ‘تلميحات الاستعلام’ مثل أنظمة RDBMS الأخرى. سيقرر مخطط الاستعلام متى يستخدم أي فهرس مناسب.

11. 

سلوك NULL

كم عدد الصفوف التي سيعيدها هذا الاستعلام؟

SELECT COUNT(*)
FROM students
WHERE grade = NULL;

حسنًا، كان هذا صعبًا بعض الشيء. الفخ هو أن x = NULL لن تطابق أي صفوف أبدًا، وستعيد دائمًا صفًا واحدًا بعدد 0.

بما أن NULL قيمة غير معروفة، فلا يمكن مقارنتها بأي شيء، حتى بنفسها.

الطريقة الصحيحة للتحقق من قيم NULL هي باستخدام IS NULL.

مثال:

postgres=# SELECT count(*)
  FROM students
  WHERE age = null;
count
-------
    0
(1 row)


postgres=#
postgres=# SELECT count(*)
  FROM students
  WHERE age is null;
count
-------
    3
(1 row)

12. 

الأداء

ماذا يفعل EXPLAIN ANALYZE مع عبارات UPDATE؟

EXPLAIN ANALYZE
UPDATE students
SET grade = grade + 1
WHERE status = 'active';

EXPLAIN ANALYZE ينفذ الاستعلام فعليًا! كن حذرًا عند تحليل عملية كتابة، خاصة عبارات UPDATE وDELETE وINSERT. قد تضطر إلى تشغيل عمليات التنظيف بعد كل EXPLAIN ANALYZE للحصول على نتائج قابلة للتكرار.

أفضل الممارسات:

  • استخدم EXPLAIN (بدون ANALYZE) مع UPDATE/DELETE/INSERT
  • لف التعديلات في معاملات:
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

أحسنت! لقد تعمقت في عدة مجالات من PostgreSQL! 🐘

أتمنى أن تكون قد تعلمت شيئًا جديدًا، أو على الأقل حصلت على درجة تتفاخر بها! 🏆

هل تريد المزيد من الإثارة في الحياة؟ اطّلع على مجموعة الاختبارات للمتعة التي لا تنتهي*!

Loading comments...