المفاتيح المُسلسلة

رغم أن قاعدة البيانات تُنشئ فهرس المفتاح الأساسي تلقائياً، لا يزال هناك مجال لتحسينات يدوية إذا كان المفتاح يتكوّن من عدة أعمدة. وفي هذه الحالة تُنشئ قاعدة البيانات فهرساً على جميع أعمدة المفتاح الأساسي—وهو ما يسمى فهرساً مُسلسلاً (concatenated index) (ويُعرف أيضاً بالفهرس متعدد الأعمدة أو المركّب أو المدمج). لاحظ أن ترتيب الأعمدة في الفهرس المُسلسل له تأثير كبير في قابليته للاستخدام، لذا يجب اختياره بعناية.

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

لذا يُعرَّف فهرس المفتاح الأساسي الجديد كما يلي:

CREATE UNIQUE INDEX employees_pk
    ON employees (employee_id, subsidiary_id)

يجب أن يأخذ الاستعلام عن موظف معين المفتاح الأساسي الكامل في الحسبان—أي يجب استخدام العمود SUBSIDIARY_ID أيضاً:

SELECT first_name, last_name
  FROM employees
 WHERE employee_id   = 123
   AND subsidiary_id = 30

كلما استخدم استعلام المفتاح الأساسي الكامل، يمكن لقاعدة البيانات استخدام INDEX UNIQUE SCAN—مهما كان عدد أعمدة الفهرس. لكن ماذا يحدث عند استخدام عمود واحد فقط من أعمدة المفتاح، مثلاً عند البحث عن جميع موظفي شركة فرعية؟

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = 20

تكشف خطة التنفيذ (execution plan) أن قاعدة البيانات لا تستخدم الفهرس. بل تنفّذ TABLE ACCESS FULL. ونتيجة لذلك تقرأ قاعدة البيانات الجدول بالكامل وتقيّم كل صف مقابل عبارة where. وينمو زمن التنفيذ مع حجم الجدول: فإذا تضاعف الجدول عشرة أضعاف، يستغرق TABLE ACCESS FULL عشرة أضعاف الوقت. وخطر هذه العملية أنها غالباً ما تكون سريعة بما يكفي في بيئة تطوير صغيرة، لكنها تسبب مشكلات أداء خطيرة في بيئة الإنتاج.

مسح كامل للجدول

قد تكون عملية TABLE ACCESS FULL، المعروفة أيضاً بـمسح كامل للجدول (full table scan)، أكثر العمليات كفاءة في بعض الحالات على أي حال، خصوصاً عند استرجاع جزء كبير من الجدول.

ويرجع ذلك جزئياً إلى العبء الإضافي للبحث بالفهرس نفسه، وهو ما لا يحدث في عملية TABLE ACCESS FULL. والسبب الأكبر أن البحث بالفهرس يقرأ كتلة تلو الأخرى؛ إذ لا تعرف قاعدة البيانات أي كتلة تقرأ تالياً حتى تتم معالجة الكتلة الحالية. أما FULL TABLE SCAN فيجب أن يحصل على الجدول كاملاً على أي حال، بحيث يمكن لقاعدة البيانات أن تقرأ أجزاءً أكبر في المرة الواحدة (قراءة متعددة الكتل multi block read). فرغم أن قاعدة البيانات تقرأ بيانات أكثر، فقد تحتاج إلى تنفيذ عمليات قراءة أقل.

لا تستخدم قاعدة البيانات الفهرس لأنها لا تستطيع استخدام أعمدة فردية من فهرس مُسلسل بشكل اعتباطي. وبنظرة أدق إلى بنية الفهرس يتّضح ذلك.

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

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

الفهرس المُسلسل ليس إلا فهرس شجرة B (B-tree index) كأي فهرس آخر يحفظ البيانات المفهرسة في قائمة مرتبة. وتراعي قاعدة البيانات كل عمود حسب موضعه في تعريف الفهرس لترتيب مدخلات الفهرس. فالعمود الأول هو معيار الترتيب الأساسي، والعمود الثاني يحدّد الترتيب فقط إذا تساوت قيمتا مدخلين في العمود الأول، وهكذا.

مهم

الفهرس المُسلسل هو فهرس واحد عبر أعمدة متعددة.

لذا فإن ترتيب فهرس بعمودين يشبه ترتيب دليل هاتف: يُرتَّب أولاً حسب اسم العائلة، ثم حسب الاسم الأول. ويعني ذلك أن فهرساً بعمودين لا يدعم البحث على العمود الثاني وحده؛ فذلك سيكون مثل البحث في دليل هاتف بالاسم الأول.

الشكل 2.1 الفهرس المُسلسل

يوضّح مقتطف الفهرس في الشكل 2.1 أن مدخلات الشركة الفرعية 20 ليست مخزّنة بجوار بعضها بعضاً. ويتّضح أيضاً أنه لا توجد مدخلات بقيمة SUBSIDIARY_ID = 20 في الشجرة، رغم وجودها في عقد الأوراق. لذا فالشجرة عديمة الفائدة لهذا الاستعلام.

نصيحة

يساعد تصوّر الفهرس في فهم الاستعلامات التي يدعمها. ويمكنك الاستعلام من قاعدة البيانات لاسترجاع المدخلات بترتيب الفهرس (صيغة SQL:2008، انظر صيغة استعلامات top-n للحلول الخاصة باستخدام LIMIT أو TOP أو ROWNUM):

SELECT <INDEX COLUMN LIST> 
  FROM <TABLE>  
 ORDER BY <INDEX COLUMN LIST>
 FETCH FIRST 100 ROWS ONLY

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

يمكننا بالطبع إضافة فهرس آخر على SUBSIDIARY_ID لتحسين سرعة الاستعلام. غير أن هناك حلاً أفضل—على الأقل إذا افترضنا أن البحث بـEMPLOYEE_ID وحده لا معنى له.

يمكننا الاستفادة من كون العمود الأول في الفهرس صالحاً دائماً للبحث. ومرة أخرى، الأمر كدليل هاتف: لا تحتاج إلى معرفة الاسم الأول للبحث باسم العائلة. والحيلة هي عكس ترتيب أعمدة الفهرس بحيث يصبح SUBSIDIARY_ID في الموضع الأول:

CREATE UNIQUE INDEX EMPLOYEES_PK 
    ON EMPLOYEES (SUBSIDIARY_ID, EMPLOYEE_ID)

لا يزال العمودان معاً فريدين، لذا يمكن للاستعلامات بالمفتاح الأساسي الكامل أن تستخدم INDEX UNIQUE SCAN، لكن تسلسل مدخلات الفهرس مختلف تماماً. فقد أصبح SUBSIDIARY_ID معيار الترتيب الأساسي. ويعني ذلك أن جميع مدخلات شركة فرعية تأتي متتالية في الفهرس، فيمكن لقاعدة البيانات استخدام شجرة B للعثور على موضعها.

مهم

أهم اعتبار عند تعريف فهرس مُسلسل هو كيفية اختيار ترتيب الأعمدة بحيث يمكن استخدامه بأكبر قدر ممكن.

تؤكد خطة التنفيذ أن قاعدة البيانات تستخدم الفهرس «المعكوس». فـSUBSIDIARY_ID وحده لم يعد فريداً، لذا يجب على قاعدة البيانات تتبّع عقد الأوراق للعثور على جميع المدخلات المطابقة: لذلك تستخدم عملية INDEX RANGE SCAN.

Db2 (LUW)

Explain Plan
-------------------------------------------------------------
ID | Operation               |                    Rows | Cost
 1 | RETURN                  |                         |  128
 2 |  FETCH EMPLOYEES        |  1195 of 1195 (100.00%) |  128
 3 |   RIDSCN                |  1195 of 1195 (100.00%) |   43
 4 |    SORT (UNIQUE)        |  1195 of 1195 (100.00%) |   43
 5 |     IXSCAN EMPLOYEES_PK | 1195 of 10000 ( 11.95%) |   43

Predicate Information
 2 - SARG (Q1.SUBSIDIARY_ID = +00002.)
 5 - START (Q1.SUBSIDIARY_ID = +00002.)
      STOP (Q1.SUBSIDIARY_ID = +00002.)

تبدو خطة التنفيذ هذه أكثر تعقيداً من خطة التنفيذ التي استخدمت الفهرس سابقاً. غير أن العمليات الأساسية لا تزال موجودة: IXSCAN التي تمثّل مسح نطاق الفهرس (index range scan)، وFETCH للوصول إلى الجدول. وبين هاتين العمليتين توجد عملية SORT وعملية RIDSCN غير متوقعتين: ترتّب عملية SORT المدخلات المجلوبة من الفهرس وفق موضع التخزين الفيزيائي للصفوف في جدول الكومة. ثم تجلب RIDSCAN مسبقاً جميع صفحات قاعدة البيانات المتأثرة (بدمج عدة كتل متجاورة في عملية إدخال/إخراج (I/O) واحدة).

MySQL

+----+-----------+------+---------+---------+------+-------+
| id | table     | type | key     | key_len | rows | Extra |
+----+-----------+------+---------+---------+------+-------+
|  1 | employees | ref  | PRIMARY | 5       |  123 |       |
+----+-----------+------+---------+---------+------+-------+

نمط الوصول ref في MySQL هو مقابل INDEX RANGE SCAN في قاعدة بيانات Oracle.

Oracle

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

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SUBSIDIARY_ID"=20)

PostgreSQL

                 QUERY PLAN
----------------------------------------------
 Bitmap Heap Scan on employees
 (cost=24.63..1529.17 rows=1080 width=13)
   Recheck Cond: (subsidiary_id = 2::numeric)
   -> Bitmap Index Scan on employees_pk
      (cost=0.00..24.36 rows=1080 width=0)
      Index Cond: (subsidiary_id = 2::numeric)

تستخدم قاعدة بيانات PostgreSQL عمليتين في هذه الحالة: Bitmap Index Scan ثم Bitmap Heap Scan. وهما تقابلان تقريباً INDEX RANGE SCAN وTABLE ACCESS BY INDEX ROWID في Oracle مع فرق مهم واحد: فهي تجلب أولاً جميع النتائج من الفهرس (Bitmap Index Scan)، ثم ترتّب الصفوف وفق موضع التخزين الفيزيائي للصفوف في جدول الكومة، ثم تجلب جميع الصفوف من الجدول (Bitmap Heap Scan). وتقلّل هذه الطريقة عدد عمليات الإدخال/الإخراج العشوائية على الجدول.

SQL Server

|--Nested Loops(Inner Join)
   |--Index Seek(OBJECT:employees_pk,
   |               SEEK:subsidiary_id=20
   |            ORDERED FORWARD)
   |--RID Lookup(OBJECT:employees,
                   SEEK:Bmk1000=Bmk1000
                 LOOKUP ORDERED FORWARD)

بشكل عام، يمكن لقاعدة البيانات استخدام فهرس مُسلسل عند البحث بالأعمدة البادئة (الأكثر يساراً). فالفهرس ذو الأعمدة الثلاثة يمكن استخدامه عند البحث بالعمود الأول، وعند البحث بالعمودين الأولين معاً، وعند البحث باستخدام جميع الأعمدة.

ورغم أن حل الفهرس المزدوج يحقق أداء select جيداً جداً أيضاً، فإن حل الفهرس الواحد أفضل. فهو لا يوفّر مساحة التخزين فحسب، بل يوفر أيضاً عبء الصيانة للفهرس الثاني. فكلما قلّ عدد فهارس الجدول، تحسّن أداء insert وdelete وupdate.

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

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

لتعريف فهرس أمثل، يجب أن تفهم أكثر من مجرد كيفية عمل الفهارس—يجب أن تعرف أيضاً كيف يستعلم التطبيق عن البيانات. وهذا يعني أن تعرف تركيبات الأعمدة التي تظهر في عبارة where.

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

والمكان الوحيد الذي تلتقي فيه المعرفة التقنية بقاعدة البيانات مع المعرفة الوظيفية بمجال العمل هو قسم التطوير. فالمطوّرون لديهم حسّ بالبيانات ويعرفون مسار الوصول، ويمكنهم الفهرسة على نحو سليم لتحقيق أفضل منفعة للتطبيق ككل دون جهد كبير.