الدرس 26 · الوحدة 11: تطوير استعلامات محسّنة باستخدام البرمجة الإجرائية

العروض والعروض المادية

استعلام محفوظ باسم للتبسيط والأمان، أو نتيجة محفوظة للسرعة. ⏱ نحو 40 دقيقة.

بعد هذا الدرس ستستطيع أن:

تنشئ عرضًا لإخفاء التعقيد ولضبط الصلاحيات.
تحدّث جدولًا عبر عرض بسيط وتستخدم WITH CHECK OPTION.
تنشئ عرضًا ماديًّا وتحدّثه بـ REFRESH.
تختار بين العرض والعرض المادي وتستخدم CONCURRENTLY.

١العرض: استعلام محفوظ باسم

العرض View استعلام محفوظ في القاعدة تستخدمه كأنه جدول. لا يخزن بيانات؛ في كل مرة يُقرأ فيها يُنفَّذ استعلامه من جديد، فيعطي أحدث البيانات دائمًا:

query.sql

CREATE VIEW order_totals ASSELECT o.id, c.name AS customer, o.order_date, o.status,       sum(i.qty * i.unit_price) AS totalFROM orders oJOIN customers c   ON c.id = o.customer_idJOIN order_items i ON i.order_id = o.idGROUP BY o.id, c.name; SELECT customer, count(*) AS الطلبات, sum(total) AS المجموعFROM order_totals WHERE status <> 'cancelled'GROUP BY customer ORDER BY المجموع DESC LIMIT 3;

✓ أُنشئ العرض (View)

النتيجة · 3 صفوف

customer	الطلبات	المجموع
سارة	3	3805
خالد	2	3575
نورة	2	3530

من يستخدم العرض لا يحتاج أن يعرف الربط الثلاثي والتجميع. كتبناه مرة، ويستخدمه الجميع.

الفائدة	مثال
إخفاء التعقيد	فريق التقارير يقرأ order_totals لا الجداول الثلاثة
الأمان	امنح الدور SELECT على عرض بأعمدة محددة (بلا البريد مثلًا) لا على الجدول
ثبات الواجهة	غيّر الجداول خلف العرض، ويبقى العرض كما هو للتطبيقات

العرض البسيط (جدول واحد بلا تجميع) قابل للتحديث: INSERT و UPDATE عليه تصل إلى الجدول. و WITH CHECK OPTION يمنع إدخال صف لا يظهر في العرض:

query.sql

CREATE VIEW riyadh_customers ASSELECT id, name, city, joined FROM customers WHERE city = 'الرياض'WITH CHECK OPTION; UPDATE riyadh_customers SET name = 'نورة العتيبي' WHERE id = 3;SELECT name FROM customers WHERE id = 3;

✓ أُنشئ العرض (View)

✓ عُدِّل صف واحد

النتيجة · صف واحد

name
نورة العتيبي

٢العرض المادي: احسب مرة واقرأ كثيرًا

تقرير ثقيل على ملايين الصفوف يُطلب مئات المرات يوميًّا. العرض العادي يعيد الحساب كل مرة. أما العرض المادي Materialized View فيحفظ النتيجة نفسها كجدول، ويُحدَّث حين تطلب:

query.sql

CREATE MATERIALIZED VIEW city_revenue ASSELECT c.city, count(DISTINCT o.id) AS orders, sum(i.qty * i.unit_price) AS revenueFROM customers cJOIN orders o      ON o.customer_id = c.idJOIN order_items i ON i.order_id = o.idWHERE o.status IN ('paid', 'shipped')GROUP BY c.city; EXPLAIN SELECT * FROM city_revenue;

✓ أُنشئ العرض (View)

خطة التنفيذ (EXPLAIN)

Seq Scan on city_revenue (cost=0.00..18.10 rows=810 width=72)

الخطة قراءة بسيطة من 8 صفوف محفوظة، بدل ربط وتجميع 40 ألف بند في كل مرة.

الثمن: البيانات تصبح قديمة حتى تحدّثها بـ REFRESH:

query.sql

CREATE MATERIALIZED VIEW category_count ASSELECT category_id, count(*) AS n FROM products GROUP BY category_id; INSERT INTO products (name, category_id, price) VALUES ('منتج جديد', 1, 99);SELECT n AS قبل_التحديث FROM category_count WHERE category_id = 1; REFRESH MATERIALIZED VIEW category_count;SELECT n AS بعد_التحديث FROM category_count WHERE category_id = 1;

✓ أُنشئ العرض (View)

✓ أُضيف صف واحد

النتيجة · صف واحد

قبل_التحديث
3

✓ حُدّث العرض المادي

النتيجة · صف واحد

بعد_التحديث
4

	العرض VIEW	العرض المادي MATERIALIZED VIEW
يخزن	الاستعلام فقط	النتيجة
البيانات	حديثة دائمًا	حتى آخر REFRESH
القراءة	بكلفة الاستعلام كاملًا	سريعة، ويقبل الفهارس
المناسب	إخفاء التعقيد والأمان	التقارير الثقيلة المتكررة

٣التحديث دون إيقاف القراء

REFRESH العادي يقفل العرض أثناء التحديث فينتظر القرّاء. REFRESH … CONCURRENTLY يحدّثه والقراءة مستمرة، بشرط وجود فهرس فريد على العرض:

✗ خطأ

query.sql

CREATE MATERIALIZED VIEW category_count ASSELECT category_id, count(*) AS n FROM products GROUP BY category_id; REFRESH MATERIALIZED VIEW CONCURRENTLY category_count;

✓ أُنشئ العرض (View)

✗ خطأ (السطر 4): cannot refresh materialized view "public.category_count" concurrently

💡 تلميح: Create a unique index with no WHERE clause on one or more columns of the materialized view.

✓ صواب

query.sql

CREATE MATERIALIZED VIEW category_count ASSELECT category_id, count(*) AS n FROM products GROUP BY category_id;CREATE UNIQUE INDEX ON category_count (category_id); REFRESH MATERIALIZED VIEW CONCURRENTLY category_count;SELECT * FROM category_count ORDER BY category_id;

✓ أُنشئ العرض (View)

✓ أُنشئ الفهرس

✓ حُدّث العرض المادي

النتيجة · 5 صفوف

category_id	n
1	3
2	3
3	2
4	2
5	2

توقّع وتحقّق

أيهما يخزن النتيجة؟

لتحديث بيانات العرض المادي:

شرط REFRESH CONCURRENTLY:

WITH CHECK OPTION يمنع:

الخلاصة

العرض استعلام محفوظ، بياناته حديثة دائمًا، ولا يسرّع بذاته.
العرض المادي يحفظ النتيجة: قراءة سريعة، وبيانات حتى آخر REFRESH.
REFRESH … CONCURRENTLY يحتاج فهرسًا فريدًا ولا يوقف القراء.
WITH CHECK OPTION يحمي شرط العرض عند الكتابة عبره.

تحدٍّ برمجي: لوحة المبيعات اليومية

متقدم

لوحة الإدارة تعرض مبيعات كل يوم، وتُفتح مئات المرات. ابنِ لها عرضًا ماديًّا يُحدَّث دون إيقاف القراء.

المطلوب:

عرض مادي daily_sales بأعمدة day و orders (عدد الطلبات) و revenue، للطلبات غير الملغاة.
فهرس فريد على day.
يقبل REFRESH MATERIALIZED VIEW CONCURRENTLY.

👀 اعرض الناتج المطلوب

✓ التحقق الآلي من حلّك:

الكود يعمل بلا أخطاء
محتوى العرض صحيح
طلب جديد يظهر بعد REFRESH CONCURRENTLY
MATERIALIZED VIEW
فهرس فريد

💡 تلميح 1

ابدأ من order_totals في الدرس، واجمع حسب order_date.

💡 تلميح 2

ثم CREATE UNIQUE INDEX … ON daily_sales (day);

حاول بنفسك أولًا؛ للمسألة أكثر من حلّ صحيح.

solution.sql

CREATE MATERIALIZED VIEW daily_sales ASSELECT o.order_date AS day,       count(DISTINCT o.id) AS orders,       sum(i.qty * i.unit_price) AS revenueFROM orders o JOIN order_items i ON i.order_id = o.idWHERE o.status <> 'cancelled'GROUP BY o.order_date; CREATE UNIQUE INDEX ux_daily_sales_day ON daily_sales (day);

انتقل إلى تقييم الدرساختيار من متعدد، وصح وخطأ، وأكمل الفراغ، وتتبّع الناتج.ابدأ التقييم ←

📝 معلم؟ أرسل ملاحظة للمؤلف عن هذا الدرس