الفهارس البطيئة، الجزء الثاني

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

حسّن فهرس EMPLOYEES_PK المعتمد أداء جميع الاستعلامات التي تبحث بالشركة الفرعية وحدها. لكنه صالح لجميع الاستعلامات التي تبحث بـSUBSIDIARY_ID—بغض النظر عن وجود أي معايير بحث إضافية. ويعني ذلك أن الفهرس يصبح صالحاً لاستعلامات كانت تستخدم فهرساً آخر مع جزء آخر من عبارة where. وفي هذه الحالة، إذا توفرت مسارات وصول متعددة، فمهمة المُحسِّن (optimizer) اختيار الأفضل.

مُحسِّن الاستعلام

مُحسِّن الاستعلام (query optimizer)، أو مخطِّط الاستعلام (query planner)، هو مكوّن قاعدة البيانات (database) الذي يحوّل عبارة SQL إلى خطة تنفيذ. وتسمى هذه العملية أيضاً الترجمة (compiling) أو التحليل (parsing). وهناك نوعان متميزان من المُحسِّنات.

المُحسِّنات القائمة على التكلفة (cost-based optimizers — CBO) تولّد صوراً كثيرة لخطة التنفيذ وتحسب قيمة تكلفة (cost) لكل خطة. ويستند حساب التكلفة إلى العمليات المستخدمة وأعداد الصفوف المقدّرة. وفي النهاية تكون قيمة التكلفة هي المرجع لاختيار «أفضل» خطة تنفيذ.

المُحسِّنات القائمة على القواعد (rule-based optimizers — RBO) تولّد خطة التنفيذ باستخدام مجموعة قواعد ثابتة. والمُحسِّنات القائمة على القواعد أقل مرونة ونادراً ما تُستخدم اليوم.

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

SELECT first_name, last_name, subsidiary_id, phone_number
  FROM employees
 WHERE last_name  = 'WINAND'
   AND subsidiary_id = 30

وخطة التنفيذ هي:

مثال 2.1 خطة تنفيذ مع فهرس المفتاح الأساسي المُنقّح

---------------------------------------------------------------
|Id |Operation                   | Name         | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT            |              |    1 |   30 |
|*1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |    1 |   30 |
|*2 |  INDEX RANGE SCAN          | EMPLOYEES_PK |   40 |    2 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  1 - filter("LAST_NAME"='WINAND')
  2 - access("SUBSIDIARY_ID"=30)

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

ولمزيد من التحليل، سيكون من الجيد مقارنة خطة التنفيذ قبل التغيير وبعده. وللحصول على خطة التنفيذ الأصلية، يمكننا ببساطة نشر تعريف الفهرس القديم مرة أخرى، غير أن معظم قواعد البيانات تقدّم طريقة أبسط لمنع استخدام فهرس في استعلام معين. ويستخدم المثال التالي تلميح مُحسِّن (optimizer hint) في Oracle لهذا الغرض.

SELECT /*+ NO_INDEX(EMPLOYEES EMPLOYEES_PK) */ 
       first_name, last_name, subsidiary_id, phone_number
  FROM employees
 WHERE last_name  = 'WINAND'
   AND subsidiary_id = 30

خطة التنفيذ التي كانت مستخدمة على الأرجح قبل تغيير الفهرس لم تكن تستخدم فهرساً على الإطلاق:

----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |    1 |  477 |
|* 1 |  TABLE ACCESS FULL| EMPLOYEES |    1 |  477 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("LAST_NAME"='WINAND' AND "SUBSIDIARY_ID"=30)

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

في مثل هذه الحالات، من الأفضل المرور على كل خطوة من خطوات خطة التنفيذ الإشكالية. والخطوة الأولى هي INDEX RANGE SCAN على فهرس EMPLOYEES_PK. وهذا الفهرس لا يغطي العمود LAST_NAME—فـINDEX RANGE SCAN لا يمكنه مراعاة مرشّح SUBSIDIARY_ID وحده؛ وتُظهر قاعدة بيانات Oracle ذلك في منطقة «معلومات المُسندات» (Predicate Information)—المدخل «2» من خطة التنفيذ. وهناك يمكنك رؤية الشروط المطبَّقة على كل عملية.

نصيحة

يشرح الملحق أ، «خطط التنفيذ» كيفية العثور على «معلومات المُسندات» في قواعد بيانات أخرى.

يطبّق INDEX RANGE SCAN ذو معرّف العملية 2 (المثال 2.1) مرشّح SUBSIDIARY_ID=30 فقط. ويعني ذلك أنه يجتاز شجرة الفهرس للعثور على أول مدخل للشركة الفرعية 30. ثم يتبّع سلسلة عقد الأوراق للعثور على جميع المدخلات الأخرى لتلك الشركة الفرعية. ونتيجة INDEX RANGE SCAN قائمة من ROWID تحقق شرط SUBSIDIARY_ID: فبحسب حجم الشركة الفرعية قد يكون عددها قليلاً أو قد يبلغ مئات كثيرة.

والخطوة التالية هي عملية TABLE ACCESS BY INDEX ROWID. وهي تستخدم ROWID من الخطوة السابقة لجلب الصفوف—جميع الأعمدة—من الجدول. وبمجرد توفر العمود LAST_NAME، يمكن لقاعدة البيانات تقييم الجزء المتبقي من عبارة where. ويعني ذلك أن قاعدة البيانات يجب أن تجلب جميع صفوف SUBSIDIARY_ID=30 قبل أن تتمكن من تطبيق مرشّح LAST_NAME.

ولا يعتمد زمن استجابة العبارة على حجم مجموعة النتائج بل على عدد الموظفين في تلك الشركة الفرعية. فإذا كان للشركة الفرعية عدد قليل من الأعضاء، كان أداء INDEX RANGE SCAN أفضل. ومع ذلك قد يكون TABLE ACCESS FULL أسرع لشركة فرعية ضخمة لأنه يستطيع قراءة أجزاء كبيرة من الجدول دفعة واحدة (انظر «مسح كامل للجدول»).

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

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

الاستعلام بطيء لأن البحث بالفهرس يعيد ROWID كثيرة—واحداً لكل موظف من الشركة الأصلية—ويجب على قاعدة البيانات جلبها فرادى. إنها التركيبة المثالية للمكوّنين اللذين يجعلان الفهرس بطيئاً: تقرأ قاعدة البيانات نطاق فهرس واسعاً ويجب أن تجلب صفوفاً كثيرة فرادى.

ويعتمد اختيار أفضل خطة تنفيذ على توزيع بيانات الجدول أيضاً، لذا يستخدم المُحسِّن إحصاءات (statistics) عن محتويات قاعدة البيانات. في مثالنا، يُستخدم مدرج تكراري (histogram) يضم توزيع الموظفين على الشركات الفرعية. وهذا يتيح للمُحسِّن تقدير عدد الصفوف المعادة من البحث بالفهرس—وتُستخدم النتيجة لحساب التكلفة.

الإحصاءات

يستخدم المُحسِّن القائم على التكلفة إحصاءات عن الجداول والأعمدة والفهارس. وتُجمع معظم الإحصاءات على مستوى العمود: عدد القيم المتمايزة، وأصغر القيم وأكبرها (نطاق البيانات)، وعدد مرات ظهور NULL، والمدرج التكراري للعمود (توزيع البيانات). وأهم قيمة إحصائية للجدول هي حجمه (بالصفوف والكتل).

وأهم إحصاءات الفهرس هي عمق الشجرة، وعدد عقد الأوراق، وعدد المفاتيح المتمايزة، وعامل التجميع (clustering factor) (انظر الفصل 5، «تجميع البيانات: القوة الثانية للفهرسة»).

ويستخدم المُحسِّن هذه القيم لتقدير انتقائية (selectivity) مُسندات عبارة where.

وإذا لم تتوفر إحصاءات—مثلاً لأنها حُذفت—استخدم المُحسِّن قيماً افتراضية. وتشير الإحصاءات الافتراضية في قاعدة بيانات Oracle إلى فهرس صغير بانتقائية متوسطة. وتؤدي إلى تقدير أن INDEX RANGE SCAN سيعيد 40 صفاً. وتُظهر خطة التنفيذ هذا التقدير في عمود Rows (مرة أخرى، انظر المثال 2.1). ومن الواضح أن هذا تقليل جسيم، إذ يعمل 1000 موظف في هذه الشركة الفرعية.

وإذا قدّمنا إحصاءات صحيحة، أدّى المُحسِّن عمله على نحو أفضل. وتُظهر خطة التنفيذ التالية التقدير الجديد: 1000 صف لـINDEX RANGE SCAN. وبالتالي حسب قيمة تكلفة أعلى للوصول اللاحق إلى الجدول.

---------------------------------------------------------------
|Id |Operation                   | Name         | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT            |              |    1 |  680 |
|*1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |    1 |  680 |
|*2 |  INDEX RANGE SCAN          | EMPLOYEES_PK | 1000 |    4 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  1 - filter("LAST_NAME"='WINAND')
  2 - access("SUBSIDIARY_ID"=30)

قيمة التكلفة 680 أعلى حتى من قيمة تكلفة خطة التنفيذ التي تستخدم FULL TABLE SCAN (477). لذا سيفضّل المُحسِّن تلقائياً FULL TABLE SCAN.إن أعجبك هذا الموضوع، قد يعجبك أيضاً…… أن تشترك في القوائم البريدية، وتحصل على ملصقات مجانية، وتشتري كتابي، أو تنضم إلى دورة تدريبية.

وهذا المثال لفهرس بطيء لا ينبغي أن يخفي حقيقة أن الفهرسة السليمة هي الحل الأفضل. فالبحث باسم العائلة مدعوم على أفضل وجه بفهرس على LAST_NAME بالطبع:

CREATE INDEX emp_name ON employees (last_name)

وباستخدام الفهرس الجديد، يحسب المُحسِّن قيمة تكلفة مقدارها 3:

مثال 2.2 خطة تنفيذ مع فهرس مخصّص

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

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("SUBSIDIARY_ID"=30)
   2 - access("LAST_NAME"='WINAND')

يقدّم الوصول إلى الفهرس—بحسب تقدير المُحسِّن—صفاً واحداً فقط. لذا يجب على قاعدة البيانات جلب ذلك الصف وحده من الجدول: وهذا أسرع بالتأكيد من FULL TABLE SCAN. فالفهرس المعرَّف تعريفاً سليماً لا يزال أفضل من المسح الكامل الأصلي للجدول.

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