الاستعلام عن صفوف Top-N
استعلامات Top-N هي استعلامات تقصر النتيجة على عدد محدد من الصفوف، وهي غالباً استعلامات عن أحدث المدخلات أو «أفضلها» في مجموعة نتائج. وللتنفيذ بكفاءة، يجب أن يتم الترتيب بـorder by متدفق.
أبسط طريقة لجلب الصفوف الأولى فقط من استعلام هي جلب الصفوف المطلوبة ثم إغلاق العبارة. لكن المُحسِّن للأسف لا يستطيع التنبؤ بذلك عند تحضير خطة التنفيذ؛ فليختار أفضل خطة تنفيذ، يجب أن يعرف ما إذا كان التطبيق سيجلب جميع الصفوف في النهاية. وفي تلك الحالة، قد يكون مسح كامل للجدول بعملية فرز صريحة هو الأفضل أداءً، مع أن order by المتدفق قد يكون أفضل عند جلب عشرة صفوف فقط — حتى لو وجب على قاعدة البيانات جلب كل صف على حدة. ويعني ذلك أن المُحسِّن يجب أن يعرف أنك ستوقف العبارة قبل جلب جميع الصفوف ليختار أفضل خطة تنفيذ.
نصيحة
أبلغ قاعدة البيانات كلما لم تكن بحاجة إلى جميع الصفوف.
استبعد معيار SQL هذا الشرط زمناً طويلاً. وقد أُدخل الامتداد المقابل (fetch first) أخيراً مع SQL:2008، وهو متاح حالياً في IBM Db2 وPostgreSQL وSQL Server 2012 وOracle 12c. ويعود ذلك من جهة إلى أن الميزة امتداد غير جوهري، ومن جهة أخرى إلى أن كل قاعدة بيانات كانت تقدم حلها الخاص والمملوك لها منذ سنوات طويلة.
تعرض الأمثلة التالية استخدام هذه الامتدادات المعروفة عبر الاستعلام عن أحدث عشر عمليات بيع. والأساس واحد دائماً: جلب جميع المبيعات بدءاً من الأحدث. أما صيغة Top-N المعنية فتُوقف التنفيذ بعد جلب عشرة صفوف فقط.
Db2 (LUW)تدعم Db2 صيغة fetch first القياسية منذ الإصدار 9 على الأقل (في LUW وzOS).
SELECT *
FROM sales
ORDER BY sale_date DESC
FETCH FIRST 10 ROWS ONLY
الكلمة المفتاحية limit المملوكة مدعومة منذ Db2 (LUW) 9.7 (وتتطلب db2set DB2_COMPATIBILITY_VECTOR=MYS).
MySQL
تستخدم MySQL وPostgreSQL جملة limit لتقييد عدد الصفوف المجلوبة.
SELECT *
FROM sales
ORDER BY sale_date DESC
LIMIT 10
Oracle
أدخلت قاعدة بيانات Oracle امتداد fetch first مع الإصدار 12c. وفي الإصدارات الأقدم يجب استخدام العمود الزائف ROWNUM الذي يرقّم صفوف مجموعة النتائج تلقائياً. ولاستخدام هذا العمود في مرشّح، علينا تغليف الاستعلام:
SELECT *
FROM (
SELECT *
FROM sales
ORDER BY sale_date DESC
)
WHERE rownum <= 10
PostgreSQL
يدعم PostgreSQL امتداد fetch first منذ الإصدار 8.4. ولا تزال جملة limit المستخدمة سابقاً تعمل كما في مثال MySQL.
SELECT *
FROM sales
ORDER BY sale_date DESC
FETCH FIRST 10 ROWS ONLY
SQL Server
تقدّم SQL Server جملة top لتقييد عدد الصفوف المجلوبة.
SELECT TOP 10 *
FROM sales
ORDER BY sale_date DESC
بدءاً من الإصدار 2012، تدعم SQL Server امتداد fetch first أيضاً.
جميع استعلامات SQL المعروضة أعلاه خاصة لأن قواعد البيانات تتعرف عليها كاستعلامات Top-N.
مهم
لا تستطيع قاعدة البيانات تحسين استعلام للحصول على نتيجة جزئية إلا إذا علمت ذلك منذ البداية.
وإذا كان المُحسِّن على علم بأننا نحتاج عشرة صفوف فقط، فسيفضّل استخدام order by متدفق إذا أمكن:
Db2 (LUW)
Explain Plan
-----------------------------------------------------------------
ID | Operation | Rows | Cost
1 | RETURN | | 24
2 | FETCH SALES | 10 of 1009326 | 458452
3 | IXSCAN (REVERSE) SALES_DT_PR | 1009326 of 1009326 | 2624
Predicate Information
لا يظهر سلوك Top-N مباشرةً في خطة تنفيذ Db2 إلا إذا لزمت عملية SORT (وعندها يشير عرض last_explained إليه بين قوسين: SORT (TOP-N)، انظر المثال التالي).
وفي هذا المثال تحديداً، قد يُشتبه في أن هذا لا بد أن يكون استعلام Top-N بسبب الانخفاض المفاجئ في تقدير عدد الصفوف الذي لا تفسّره أي مُسندات ترشيح (فقسم Predicate Information فارغ).
Oracle
-------------------------------------------------------------
| Operation | Name | Rows | Cost |
-------------------------------------------------------------
| SELECT STATEMENT | | 10 | 9 |
| COUNT STOPKEY | | | |
| VIEW | | 10 | 9 |
| TABLE ACCESS BY INDEX ROWID| SALES | 1004K| 9 |
| INDEX FULL SCAN DESCENDING| SALES_DT_PR | 10 | 3 |
-------------------------------------------------------------
تشير خطة تنفيذ Oracle إلى الإيقاف المخطَّط له بعملية COUNT STOPKEY، أي إن قاعدة البيانات تعرفت على صيغة Top-N.
نصيحة
الملحق أ، «خطط التنفيذ»، يلخّص العمليات المقابلة لـDb2 (LUW) وMySQL وOracle وPostgreSQL وSQL Server.
مهم
لا يحتاج استعلام Top-N المتدفق إلى قراءة مجموعة النتائج كلها وفرزها.
وإذا لم يوجد فهرس مناسب على SALE_DATE من أجل order by متدفق، وجب على قاعدة البيانات قراءة الجدول بأكمله وفرزه. ولا يُسلَّم الصف الأول إلا بعد قراءة الصف الأخير من الجدول.
Db2 (LUW)
Explain Plan
-----------------------------------------------------------
ID | Operation | Rows | Cost
1 | RETURN | | 59835
2 | TBSCAN | 10 of 10 (100.00%) | 59835
3 | SORT (TOP-N) | 10 of 1009326 ( .00%) | 59835
4 | TBSCAN SALES | 1009326 of 1009326 (100.00%) | 59739
Predicate Information
Oracle
--------------------------------------------------
| Operation | Name | Rows | Cost |
--------------------------------------------------
| SELECT STATEMENT | | 10 | 59558 |
| COUNT STOPKEY | | | |
| VIEW | | 1004K| 59558 |
| SORT ORDER BY STOPKEY| | 1004K| 59558 |
| TABLE ACCESS FULL | SALES | 1004K| 9246 |
--------------------------------------------------
لا تحتوي خطة التنفيذ هذه على order by متدفق، وهي بطيئة تقريباً بقدر إيقاف التنفيذ من جهة العميل. ومع ذلك يبقى استخدام صيغة Top-N أفضل لأن قاعدة البيانات لا تحتاج إلى تجسيد النتيجة الكاملة بل أحدث عشرة صفوف فقط، وهو ما يتطلب ذاكرة أقل كثيراً. وتشير خطة تنفيذ Oracle إلى هذا التحسين بالمُعدِّل STOPKEY على عملية SORT ORDER BY.
إن أعجبك هذا الموضوع، قد يعجبك أيضاً…
… أن تشترك في القوائم البريدية، وتحصل على ملصقات مجانية، وتشتري كتابي، أو تنضم إلى دورة تدريبية.
تشمل مزايا استعلام Top-N المتدفق مكاسب أداء فورية فحسب، بل تحسين قابلية التوسع أيضاً. فبدون التنفيذ المتدفق، ينمو زمن استجابة استعلام Top-N هذا مع حجم الجدول؛ أما زمن الاستجابة مع التنفيذ المتدفق فلا ينمو إلا مع عدد الصفوف المختارة. وبعبارة أخرى، يبقى زمن استجابة استعلام Top-N المتدفق ثابتاً دائماً تقريباً بصرف النظر عن حجم الجدول، ولا يصبح الاستعلام أبطأ قليلاً إلا عندما يزداد عمق شجرة B.
يعرض الشكل 7.1 قابلية التوسع لكلا الصيغتين مع نمو حجم البيانات. ويظهر بوضوح النمو الخطي لزمن الاستجابة في التنفيذ بلا order by متدفق، بينما يبقى زمن الاستجابة في التنفيذ المتدفق ثابتاً.
الشكل 7.1 قابلية التوسع في استعلامات Top-N
ومع أن زمن استجابة استعلام Top-N المتدفق لا يعتمد على حجم الجدول، فإنه لا يزال ينمو مع عدد الصفوف المختارة؛ إذ يتضاعف زمن الاستجابة عند اختيار ضعف عدد الصفوف. وهذا مهم بوجه خاص في استعلامات «ترقيم الصفحات» التي تحمّل نتائج إضافية، لأن هذه الاستعلامات تبدأ غالباً من أول مدخل مجدداً؛ فتقرأ الصفوف المعروضة أصلاً في الصفحة السابقة وتستبعدها قبل أن تصل أخيراً إلى نتائج الصفحة الثانية. ومع ذلك، يوجد حل لهذه المشكلة أيضاً كما سنرى في القسم التالي.