المكتبة العربية لعلوم الحاسوب
استخدم الفهرس يا لوقا! › الاستخدام المقصود لمُحدِّدات مرشّح الفهرس 40 / 139 السابق التالي

مُسندات ترشيح الفهرس المستخدمة عمداً

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

عبارات شرط Where التي لا تصلح لتكون مُسند وصول مرشّح جيد لهذه التقنية:

SELECT first_name, last_name, subsidiary_id, phone_number
  FROM employees
 WHERE subsidiary_id = ?
   AND UPPER(last_name) LIKE '%INA%'

تذكّر أن تعبيرات LIKE ذات المحارف البديلة في البداية لا يمكنها استخدام شجرة الفهرس. ويعني ذلك أن فهرسة LAST_NAME لا تضيّق نطاق الفهرس الممسوح، سواء فهرست LAST_NAME أو UPPER(last_name). لذلك لا تُعدّ هذه الحالة مرشّحاً جيداً للفهرسة.

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

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

لكن الشرط على SUBSIDIARY_ID مناسب جداً للفهرسة. بل لا نحتاج حتى إلى إضافة فهرس جديد، لأن SUBSIDIARY_ID هو بالفعل العمود الأول في فهرس المفتاح الأساسي.

Db2 (LUW)

Explain Plan
------------------------------------------------------------
ID | Operation               |                   Rows | Cost
 1 | RETURN                  |                        |   40
 2 |  FETCH EMPLOYEES        |    33 of 333 (  9.91%) |   40
 3 |   RIDSCN                |   333 of 333 (100.00%) |   12
 4 |    SORT (UNIQUE)        |   333 of 333 (100.00%) |   12
 5 |     IXSCAN EMPLOYEES_PK | 333 of 10000 (  3.33%) |   12

Predicate Information
 2 - SARG (Q1.SUBSIDIARY_ID = ?)
     SARG ( UPPER(Q1.LAST_NAME) LIKE '%INA%')
 5 - START (Q1.SUBSIDIARY_ID = ?)
      STOP (Q1.SUBSIDIARY_ID = ?)

Oracle

---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |   17 |  230 |
|*1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |   17 |  230 |
|*2 |   INDEX RANGE SCAN          | EMPLOYEEs_PK|  333 |    2 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(UPPER("LAST_NAME") LIKE '%INA%')
   2 - access("SUBSIDIARY_ID"=TO_NUMBER(:A))

في خطة التنفيذ أعلاه، ترتفع قيمة التكلفة مئة مرة من INDEX RANGE SCAN إلى عملية TABLE ACCESS BY INDEX ROWID التالية لها. وبعبارة أخرى: الوصول إلى الجدول هو ما يسبّب معظم العمل. وهو في الواقع نمط شائع وليس مشكلة بحد ذاته، لكنه المساهم الأكبر في زمن التنفيذ الكلي لهذا الاستعلام.

ولا يُعدّ الوصول إلى الجدول عنق زجاجة بالضرورة إذا كانت الصفوف التي يُوصَل إليها مخزنة في كتلة جدول واحدة، لأن قاعدة البيانات تستطيع جلب جميع الصفوف بعملية قراءة واحدة. أما إذا كانت الصفوف نفسها موزعة على كتل مختلفة كثيرة، فقد يصبح الوصول إلى الجدول مشكلة أداء خطيرة، لأن قاعدة البيانات يجب أن تجلب كتلاً كثيرة لاسترجاع جميع الصفوف. ويعني ذلك أن الأداء يعتمد على التوزيع الفيزيائي للصفوف التي يُوصَل إليها، أي أنه يعتمد على عنقدة الصفوف (clustering of rows).

ملاحظة

العلاقة بين ترتيب الفهرس وترتيب الجدول معيار للأداء، وهو ما يسمى عامل العنقدة في الفهرس.

بل من الممكن فعلاً تحسين أداء الاستعلام بإعادة ترتيب صفوف الجدول بحيث توافق ترتيب الفهرس. لكن هذه الطريقة نادراً ما تكون قابلة للتطبيق، لأنك تستطيع تخزين صفوف الجدول في تسلسل واحد فقط. ويعني ذلك أنك لا تستطيع تحسين الجدول إلا لفهرس واحد. وحتى إذا استطعت اختيار فهرس واحد تحب تحسين الجدول له، فستبقى المهمة صعبة لأن معظم قواعد البيانات لا تقدم سوى أدوات بدائية لهذه المهمة. وما يسمى تسلسل الصفوف (row sequencing) في نهاية المطاف نهج غير عملي.

عامل العنقدة في الفهرس

عامل العنقدة في الفهرس مقياس غير مباشر لاحتمال أن يشير مدخلان متتاليان في الفهرس إلى كتلة الجدول نفسها. ويأخذ المُحسِّن هذا الاحتمال في الحسبان عند حساب قيمة تكلفة عملية TABLE ACCESS BY INDEX ROWID.

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

ولتطبيق هذا المفهوم على الاستعلام أعلاه، يجب أن نوسّع الفهرس ليغطي جميع الأعمدة الواردة في شرط where، حتى لو لم تضيّق نطاق الفهرس الممسوح:

CREATE INDEX empsubupnam ON employees
       (subsidiary_id, UPPER(last_name))

العمود SUBSIDIARY_ID هو أول عمود في الفهرس، لذا يمكن استخدامه كمُسند وصول. أما التعبير UPPER(last_name) فيغطي مرشّح LIKE بوصفه مُسند ترشيح في الفهرس. وتوفّر فهرسة التمثيل بأحرف كبيرة بعض دورات المعالج أثناء التنفيذ، لكن فهرساً مباشراً على LAST_NAME سيعمل أيضاً. وستجد المزيد عن ذلك في القسم التالي.

Db2 (LUW)

Explain Plan
--------------------------------------------------------
ID | Operation             |                 Rows | Cost
 1 | RETURN                |                      |   15
 2 |  FETCH EMPLOYEES      |               0 of 0 |   15
 3 |   IXSCAN EMPSUBUPNAM2 | 0 of 10000 (   .00%) |   15

Predicate Information
 3 - START (Q1.SUBSIDIARY_ID = ?)
      STOP (Q1.SUBSIDIARY_ID = ?)
      SARG (Q1.LAST_NAME LIKE '%INA%')

للحصول على خطة التنفيذ المطلوبة، اضطررت إلى إزالة UPPER من الفهرس ومن شرط where. فبدءاً من Db2 (LUW) 10.5، تُعدّ الفهرسة القائمة على الدوال ميزة حديثة نسبياً، ويبدو أن المُحسِّن لم يستوعبها تماماً بعد. علاوة على ذلك، فإن استخدام ترتيبات محرّفية (collations) مناسبة للحصول على سلوك غير حسّاس لحالة الأحرف هو الخيار الأفضل في Db2 على أي حال.

Oracle

--------------------------------------------------------------
|Id | Operation                   | Name       | Rows | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT            |            |   17 |   20 |
| 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |   17 |   20 |
|*2 |   INDEX RANGE SCAN          | EMPSUBUPNAM|   17 |    3 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SUBSIDIARY_ID"=TO_NUMBER(:A))
       filter(UPPER("LAST_NAME") LIKE '%INA%')

تُظهر خطة التنفيذ الجديدة العمليات نفسها تماماً كما في السابق، لكن قيمة التكلفة انخفضت انخفاضاً كبيراً رغم ذلك. وفي معلومات المُسندات نرى أن مرشّح LIKE يُطبَّق بالفعل أثناء INDEX RANGE SCAN؛ إذ تُستبعَد فوراً الصفوف التي لا تستوفي مرشّح LIKE. ولم يعد للوصول إلى الجدول أي مُسندات ترشيح، أي أنه لا يحمّل صفوفاً لا تستوفي شرط where.

الفرق بين خطتي التنفيذ ظاهر بوضوح في عمود «Rows». فحسب تقدير المُحسِّن، يطابق الاستعلام في النهاية 17 سجلاً، لكن مسح الفهرس في خطة التنفيذ الأولى يعيد 333 صفاً. ثم يجب على قاعدة البيانات أن تحمّل هذه الصفوف الـ333 من الجدول لتطبيق مرشّح LIKE الذي يقلّص النتيجة إلى 17 صفاً. أما في خطة التنفيذ الثانية، فلا يعيد الوصول إلى الفهرس تلك الصفوف أصلاً، لذا لا تحتاج قاعدة البيانات إلى تنفيذ عملية TABLE ACCESS BY INDEX ROWID سوى 17 مرة.

ويلزمك أيضاً أن تلاحظ أن قيمة تكلفة عملية INDEX RANGE SCAN ارتفعت من اثنين إلى ثلاثة لأن العمود الإضافي يكبّر الفهرس. وبالنظر إلى المكسب في الأداء، فهو تنازل مقبول.

تحذير

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

يوضّح الرسم المتحرك التالي الفرق بين خطتي التنفيذ:

الشكل 5.1 مُسندات ترشيح الفهرس المستخدمة عمداً

يبدو هذا المثال البسيط مؤكداً للحكمة الشائعة القائلة بفهرسة كل عمود في شرط where. لكن هذه «الحكمة» تتجاهل أهمية ترتيب الأعمدة، وهو ما يحدد الشروط التي يمكن استخدامها كمُسندات وصول، وبالتالي له أثر هائل في الأداء. لذلك لا ينبغي أبداً ترك قرار ترتيب الأعمدة للصدفة.

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

نصيحة

السابق تجميع البيانات التالي مسح الفهرس فقط: تجنّب الوصول إلى الجدول