التنقّل عبر الصفحات في النتائج

بعد تنفيذ استعلام Top-N متدفق لاسترجاع الصفحة الأولى بكفاءة، ستحتاج غالباً إلى استعلام آخر لجلب الصفحات التالية. ويتمثل التحدي الناتج في أنه يجب تخطي صفوف الصفحات السابقة. وهناك طريقتان مختلفتان لمواجهة هذا التحدي: أولاً طريقة الإزاحة (offset method)، التي ترقّم الصفوف من البداية وتستخدم مرشّحاً على رقم الصف هذا لاستبعاد الصفوف السابقة للصفحة المطلوبة. والطريقة الثانية، التي أسميها طريقة البحث بالمفتاح (seek method)، تبحث عن المدخل الأخير من الصفحة السابقة وتجلب الصفوف التالية فقط.

تعرض الأمثلة التالية طريقة الإزاحة الأوسع استخداماً. وميزتها الأساسية أنها سهلة التعامل جداً — وبخاصة مع قواعد البيانات التي تملك كلمة مفتاحية مخصصة لها (offset). بل أُدرجت هذه الكلمة في معيار SQL كجزء من امتداد fetch first.Db2 (LUW)تدعم Db2 الكلمة offset منذ الإصدار 11.1. أما البديل المتوافق مع المعيار باستخدام دالة النافذة ROW_NUMBER() (انظر القسم التالي) فيعمل في الإصدارات الأقدم. وهناك طريقتان أخريان للحصول على وظيفة الإزاحة، وليس أي منهما مستحسنة: (1) استخدام db2set DB2_COMPATIBILITY_VECTOR=MYS لتمكين limit وoffset كما تدعمهما MySQL، غير أن ذلك لا يسمح بدمج fetch first مع offset؛ (2) استخدام db2set DB2_COMPATIBILITY_VECTOR=ORA للحصول على العمود الزائف ROWNUM في Oracle (انظر مثال Oracle).

MySQL

تقدّم MySQL وPostgreSQL جملة offset لاستبعاد العدد المحدد من الصفوف من بداية استعلام Top-N. وتُطبَّق جملة limit بعد ذلك.

SELECT *
  FROM sales
 ORDER BY sale_date DESC
 LIMIT 10 OFFSET 10

Oracle

تدعم قاعدة بيانات Oracle الكلمة offset منذ الإصدار 12c. وتوفّر الإصدارات الأقدم العمود الزائف ROWNUM الذي يرقّم صفوف مجموعة النتائج تلقائياً. غير أنه لا يمكن تطبيق مرشّح أكبر من أو يساوي (>=) على هذا العمود الزائف. ولجعل ذلك يعمل، يجب أولاً «تجسيد» أرقام الصفوف بإعادة تسمية العمود باسم مستعار.

SELECT *
  FROM ( SELECT tmp.*, rownum rn
           FROM ( SELECT *
                    FROM sales
                   ORDER BY sale_date DESC
                ) tmp
          WHERE rownum <= 20
       )
 WHERE rn > 10

لاحظ استخدام الاسم المستعار RN للحد الأدنى، واستخدام العمود الزائف ROWNUM نفسه للحد الأعلى (شكراً لـTom Kyte).

PostgreSQL

يعرّف امتداد fetch first جملة offset ... rows أيضاً. غير أن PostgreSQL لا يقبل سوى offset دون الكلمة rows. ولا تزال صيغة limit/offset المستخدمة سابقاً تعمل كما في مثال MySQL.

SELECT *
  FROM sales
 ORDER BY sale_date DESC
OFFSET 10
 FETCH NEXT 10 ROWS ONLY

SQL Server

ليس في SQL Server امتداد «offset» لجملته المملوكة top، لكنه أدخل امتداد fetch first مع SQL Server 2012. وجملة offset إلزامية رغم أن المعيار يعرّفها كإضافة اختيارية.

SELECT *
  FROM sales
 ORDER BY sale_date DESC
OFFSET 10 ROWS
 FETCH NEXT 10 ROWS ONLY

وإلى جانب البساطة، من مزايا هذه الطريقة أنك تحتاج فقط إلى إزاحة الصفوف لجلب صفحة اعتباطية. غير أن قاعدة البيانات يجب أن تعُدّ جميع الصفوف من البداية حتى تصل إلى الصفحة المطلوبة. ويُظهر الشكل 7.2 أن نطاق الفهرس الممسوح يكبر عند جلب صفحات أكثر.

الشكل 7.2 الوصول باستخدام طريقة الإزاحة

ولهذا عيبان: (1) تنزاح الصفحات عند إدراج مبيعات جديدة لأن الترقيم يجري دائماً من الصفر؛ (2) يزداد زمن الاستجابة عند التصفح إلى الخلف أكثر.

إن أعجبك هذا الموضوع، قد يعجبك أيضاً…

… أن تشترك في القوائم البريدية، وتحصل على ملصقات مجانية، وتشتري كتابي، أو تنضم إلى دورة تدريبية.

وتتجنّب طريقة البحث بالمفتاح المشكلتين معاً لأنها تستخدم قيم الصفحة السابقة فاصلاً. ويعني ذلك أنها تبحث عن القيم التي يجب أن تأتي بعد المدخل الأخير من الصفحة السابقة. ويمكن التعبير عن ذلك بجملة where بسيطة. وبعبارة معكوسة: طريقة البحث بالمفتاح لا تختار ببساطة القيم المعروضة أصلاً.

يعرض المثال التالي طريقة البحث بالمفتاح. ولأغراض العرض، سنبدأ بافتراض وجود عملية بيع واحدة في اليوم فقط، ما يجعل SALE_DATE مفتاحاً فريداً. ولاختيار المبيعات التي يجب أن تأتي بعد تاريخ معيّن، يجب أن تستخدم شرط أصغر من (``). وتُستخدم جملة fetch first فقط لقصر النتيجة على عشرة صفوف.

SELECT *
  FROM sales
 WHERE sale_date < ?
 ORDER BY sale_date DESC
 FETCH FIRST 10 ROWS ONLY

فبدلاً من رقم صف، تستخدم القيمة الأخيرة من الصفحة السابقة لتحديد الحد الأدنى. ولهذا فائدة هائلة في الأداء لأن قاعدة البيانات تستطيع استخدام الشرط SALE_DATE < ? للوصول إلى الفهرس؛ أي إن قاعدة البيانات تستطيع تخطي صفوف الصفحات السابقة فعلاً. وفوق ذلك، ستحصل أيضاً على نتائج مستقرة إذا أُدرجت صفوف جديدة.

ومع ذلك، لا تعمل هذه الطريقة إذا كانت هناك أكثر من عملية بيع في اليوم — كما في الشكل 7.2 — لأن استخدام آخر تاريخ من الصفحة الأولى («الأمس») يتخطى جميع نتائج الأمس، لا تلك المعروضة في الصفحة الأولى فقط. والمشكلة أن جملة order by لا تنشئ تسلسلاً حتمياً للصفوف، مع أنه شرط مسبق لاستخدام شرط نطاق بسيط لفواصل الصفحات.

وبدون جملة order by حتمية، لا تعيد قاعدة البيانات بحكم التعريف تسلسلاً حتمياً للصفوف. والسبب الوحيد الذي يجعلك عادةً تحصل على تسلسل صفوف متسق هو أن قاعدة البيانات عادةً تنفّذ الاستعلام بالطريقة نفسها. غير أن قاعدة البيانات قد تخلط فعلاً الصفوف التي لها SALE_DATE نفسها وتبقى مع ذلك محقّقة لجملة order by. وفي الإصدارات الحديثة قد يحدث حقاً أن تحصل على النتيجة بترتيب مختلف في كل مرة تنفّذ فيها الاستعلام، لا لأن قاعدة البيانات تخلط النتيجة عمداً بل لأنها قد تستخدم تنفيذ الاستعلام على نحو متوازٍ؛ ويعني ذلك أن خطة التنفيذ نفسها قد تعطي تسلسل صفوف مختلفاً لأن خيوط التنفيذ تنتهي بترتيب غير حتمي.

مهم

يتطلب ترقيم الصفحات ترتيب فرز حتمياً.

وحتى إذا كانت المواصفات الوظيفية تتطلب فقط الفرز «حسب التاريخ، الأحدث أولاً»، فيجب علينا نحن المطوّرين أن نضمن أن جملة order by تعطي تسلسل صفوف حتمياً. ولهذا الغرض، قد نحتاج إلى توسيع جملة order by بأعمدة اعتباطية فقط لضمان الحصول على تسلسل صفوف حتمي. وإذا كان للفهرس المستخدم في order by المتدفق أعمدة إضافية، فمن الجيد البدء بإضافتها إلى جملة order by حتى نواصل استخدام هذا الفهرس لـorder by المتدفق. وإذا لم يعطِ ذلك بعدُ ترتيب فرز حتمياً، فأضف أي عمود فريد ووسّع الفهرس وفقاً لذلك.

في المثال التالي، نوسّع جملة order by والفهرس بالمفتاح الأساسي SALE_ID للحصول على تسلسل صفوف حتمي. علاوة على ذلك، يجب أن نطبّق منطق «يأتي بعد» على العمودين معاً للحصول على النتيجة المطلوبة:

CREATE INDEX sl_dtid ON sales (sale_date, sale_id)

SELECT *
  FROM sales
 WHERE (sale_date, sale_id) < (?, ?)
 ORDER BY sale_date DESC, sale_id DESC
 FETCH FIRST 10 ROWS ONLY

تستخدم جملة where صيغة «قيم الصف» قليلة الشهرة (انظر المربع المعنون «قيم الصف في SQL»). وهي تدمج قيماً متعددة في وحدة منطقية واحدة قابلة للتطبيق على معاملات المقارنة العادية. وكما في القيم العددية، يقابل شرط أصغر من «يأتي بعد» عند الفرز بترتيب تنازلي. ويعني ذلك أن الاستعلام ينظر فقط في المبيعات التي تأتي بعد زوج SALE_DATE وSALE_ID المعطى.

قيم الصف في SQL

إلى جانب القيم العددية العادية، يعرّف معيار SQL أيضاً ما يسمى بواني قيم الصف (row value constructors). وهي «تحدد مجموعة مرتبة من القيم تُبنى في صف أو صف جزئي» [SQL:92، §7.1: ]. ومن الناحية النحوية، قيم الصف قوائم بين قوسين، وهذه الصيغة أشهر ما تكون في عبارة insert.

غير أن استخدام بواني قيم الصف في جملة where أقل شهرة لكنه سليم تماماً. بل يعرّف معيار SQL جميع معاملات المقارنة لبواني قيم الصف. وتعريف عمليات أصغر من مثلاً كما يلي:^[1]

X < Y صحيحة إذا وفقط إذا كانت Xi = Yi صحيحة لكل i < n وكانت Xn < Yn لبعض n.

حيث i وn يعكسان فهارس المواضع في القوائم. ويعني ذلك أن قيمة الصف X أصغر من Y إذا كانت أي قيمة Xn أصغر من Yn المقابلة وكانت جميع أزواج القيم السابقة متساوية (Xi = Yi لـi<n).

ويجعل هذا التعريف التعبير X < Y مرادفاً لـ«X يُرتَّب قبل Y»، وهو بالضبط المنطق الذي نحتاجه لطريقة البحث بالمفتاح.

ومع أن صيغة قيم الصف جزء من معيار SQL، فلا تدعمها سوى قواعد بيانات قليلة. ولا تدعم SQL Server 2017 قيم الصف إطلاقاً. وتدعم قاعدة بيانات Oracle قيم الصف من حيث المبدأ، لكنها لا تستطيع تطبيق معاملات النطاق عليها (ORA-01796). وتقيّم MySQL تعبيرات قيم الصف تقييماً صحيحاً لكنها لا تستطيع استخدامها كمُسند وصول أثناء الوصول إلى الفهرس. أما Db2 (في LUW فقط، منذ 10.1) وPostgreSQL (منذ 8.4) فلديهما دعم سليم لمُسندات قيم الصف ويستخدمانها للوصول إلى الفهرس إذا توفر فهرس مناسب.

ومع ذلك يمكن استخدام صيغة تقريبية من طريقة البحث بالمفتاح مع قواعد البيانات التي لا تدعم قيم الصف دعماً سليماً — حتى لو لم يكن التقريب بأناقة وكفاءة قيم الصف في PostgreSQL. ولهذا التقريب، يجب استخدام مقارنات «عادية» للتعبير عن المنطق المطلوب كما في مثال Oracle التالي:

SELECT *
  FROM ( SELECT *
           FROM sales
          WHERE sale_date <= ?
            AND NOT (sale_date = ? AND sale_id >= ?)
          ORDER BY sale_date DESC, sale_id DESC
       )
 WHERE rownum <= 10

تتألف جملة where من جزأين. ينظر الجزء الأول في SALE_DATE وحده ويستخدم شرط أصغر من أو يساوي (<=) — فهو يختار صفوفاً أكثر من اللازم. وهذا الجزء من جملة where بسيط بما يكفي لتستخدمه جميع قواعد البيانات للوصول إلى الفهرس. أما الجزء الثاني من جملة where فيزيل الصفوف الزائدة المعروضة أصلاً في الصفحة السابقة. ويشرح المربع المعنون «فهرسة المنطق المكافئ» سبب صياغة جملة where بهذه الطريقة.

فهرسة المنطق المكافئ

يمكن التعبير عن شرط منطقي دائماً بطرق مختلفة؛ فيمكنك مثلاً تنفيذ منطق التخطي المعروض أعلاه كما يلي:

WHERE (
         (sale_date < ?)
       OR
         (sale_date = ? AND sale_id < ?)
      )

لا يستخدم هذا البديل سوى شروط ضمّية، وهو على الأرجح أسهل فهماً — للبشر على الأقل. أما قواعد البيانات فوجهة نظرها مختلفة؛ فهي لا تدرك أن جملة where تختار جميع الصفوف بدءاً من زوج SALE_DATE/SALE_ID المعني — بشرط أن يكون SALE_DATE نفسه في الفرعين. وبدلاً من ذلك تستخدم قاعدة البيانات جملة where بأكملها كمُسند ترشيح. ويمكننا على الأقل توقع أن «يستخرج المُحسِّن الشرط SALE_DATE <= ?» من فرعي أو، لكن أياً من قواعد البيانات لا يقدم هذه الخدمة.

ومع ذلك يمكننا إضافة هذا الشرط المكرّر يدوياً — حتى لو لم يزد مقروئية:

WHERE sale_date <= ?
  AND (
         (sale_date < ?)
       OR
         (sale_date = ? AND sale_id < ?)
      )

ولحسن الحظ، تستطيع جميع قواعد البيانات استخدام هذا الجزء من جملة where كمُسند وصول. غير أن هذه الجملة أصعب في الاستيعاب من منطق التقريب المعروض أعلاه. علاوة على ذلك، يتجنب المنطق الأصلي خطر إزالة الجزء «غير الضروري» (المكرّر) من جملة where لاحقاً عن غير قصد.

وتُظهر خطة التنفيذ أن قاعدة البيانات تستخدم الجزء الأول من جملة where كمُسند وصول.

---------------------------------------------------------------
|Id | Operation                      | Name    |  Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT               |         |    10 |    4 |
|*1 |  COUNT STOPKEY                 |         |       |      |
| 2 |   VIEW                         |         |    10 |    4 |
| 3 |    TABLE ACCESS BY INDEX ROWID | SALES   | 50218 |    4 |
|*4 |     INDEX RANGE SCAN DESCENDING| SL_DTIT |     2 |    3 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   4 - access("SALE_DATE"<=:SALE_DATE)
       filter("SALE_DATE"<>:SALE_DATE
           OR "SALE_ID"<TO_NUMBER(:SALE_ID))

وتمكّن مُسندات الوصول على SALE_DATE قاعدة البيانات من تخطي الأيام التي عُرضت بالكامل في الصفحات السابقة. أما الجزء الثاني من جملة where فهو مُسند ترشيح فقط؛ ويعني ذلك أن قاعدة البيانات تفحص بضعة مدخلات من الصفحة السابقة مرة أخرى لكنها تسقطها فوراً. ويعرض الشكل 7.3 مسار الوصول المعني.

الشكل 7.3 الوصول باستخدام طريقة البحث بالمفتاح

يقارن الشكل 7.4 خصائص الأداء في طريقتي الإزاحة والبحث بالمفتاح. ودقة القياس غير كافية لرؤية الفرق في الجهة اليسرى من المخطط، لكن الفرق ظاهر بوضوح بدءاً من الصفحة 20 تقريباً.

الشكل 7.4 قابلية التوسع عند جلب الصفحة التاليةولطريقة البحث بالمفتاح عيوب أيضاً، وأهمها صعوبة التعامل معها؛ إذ لا يجب عليك صياغة جملة where بعناية فحسب، بل لا تستطيع أيضاً جلب صفحات اعتباطية. علاوة على ذلك، تحتاج إلى عكس جميع عمليات المقارنة والفرز لتغيير اتجاه التصفح. وهاتان الوظيفتان تحديداً — تخطي الصفحات والتصفح إلى الخلف — غير مطلوبتين عند استخدام آلية التمرير اللانهائي في واجهة المستخدم.

الشكل 7.5 مصفوفة قواعد البيانات/الميزات

---

1. ISO/IEC 9075-2:2023 §8.2 GR 1bi2 ↩︎