الجداول المنظَّمة بالفهرس والفهارس العنقودية

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

بل إن بعض قواعد البيانات تستطيع فعلاً استخدام فهرس مخزناً رئيسياً للجدول. وتسمّي قاعدة بيانات Oracle هذا المفهوم الجداول المنظَّمة بالفهرس (index-organized tables — IOT)، بينما تستخدم قواعد بيانات أخرى مصطلح الفهرس العنقودي (clustered index). وفي هذا القسم نستخدم المصطلحين بحسب الحاجة، إما للتأكيد على خصائص الجدول وإما على خصائص الفهرس.

فالجدول المنظَّم بالفهرس إذن فهرس شجرة B بلا جدول كومة. وينتج عن ذلك فائدتان: (1) توفير المساحة اللازمة لبنية الكومة؛ (2) أن كل وصول إلى فهرس عنقودي يصبح تلقائياً مسحاً للفهرس فقط. والفائدتان تبدوان واعدتين، لكنهما بالكاد قابلة للتحقيق عملياً.

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

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

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

تعرض الأشكال التالية بحثاً في الفهرس للعثور على جميع المبيعات في 23 مايو 2012. وللمقارنة، سننظر أولاً إلى الشكل 5.2 الذي يوضح العملية عند استخدام جدول كومة. ويتضمن التنفيذ خطوتين: (1) INDEX RANGE SCAN، (2) TABLE ACCESS BY INDEX ROWID.

الشكل 5.2 الوصول عبر الفهرس إلى جدول كومة

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

لماذا لا تحتوي الفهارس الثانوية على ROWID

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

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

والوصول إلى فهرس ثانوي لا يعيد ROWID بل مفتاحاً منطقياً للبحث في الفهرس العنقودي. غير أن وصولاً واحداً لا يكفي للبحث في الفهرس العنقودي — فهو يتطلب اجتياز الشجرة كاملاً. ويعني ذلك أن الوصول إلى جدول عبر فهرس ثانوي يبحث في فهرسين: الفهرس الثانوي مرة واحدة (INDEX RANGE SCAN)، ثم الفهرس العنقودي مقابل كل صف يُعثر عليه في الفهرس الثانوي (INDEX UNIQUE SCAN).

الشكل 5.3 فهرس ثانوي على جدول منظَّم بالفهرس

يوضح الشكل 5.3 بوضوح أن شجرة B للفهرس العنقودي تقف بين الفهرس الثانوي وبيانات الجدول.

والوصول إلى جدول منظَّم بالفهرس عبر فهرس ثانوي غير فعّال أبداً، ويمكن منعه بالطريقة نفسها التي يُمنع بها الوصول إلى جدول الكومة: باستخدام مسح الفهرس فقط — ويوصف في هذه الحالة بأنه «مسح الفهرس الثانوي فقط». بل إن المكسب الأدائي لمسح الفهرس فقط أكبر؛ لأنه لا يمنع وصولاً واحداً فحسب، بل يمنع عملية INDEX UNIQUE SCAN كاملة.

مهم

الوصول إلى جدول منظَّم بالفهرس عبر فهرس ثانوي غير فعّال أبداً.

وبهذا المثال نرى أيضاً أن قواعد البيانات تستغل كل التكرارات المتاحة لها. وتذكّر أن الفهرس الثانوي يخزّن مفتاح العنقدة في كل مدخل من مدخلاته؛ ومن ثمّ يمكننا الاستعلام عن مفتاح العنقدة من فهرس ثانوي دون الوصول إلى الجدول المنظَّم بالفهرس:

SELECT sale_id
  FROM sales_iot
 WHERE sale_date = ?

-------------------------------------------------
| Id | Operation        | Name           | Cost |
-------------------------------------------------
|  0 | SELECT STATEMENT |                |    4 |
|* 1 |  INDEX RANGE SCAN| SALES_IOT_DATE |    4 |
-------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("SALE_DATE"=:DT)

الجدول SALES_IOT جدول منظَّم بالفهرس يستخدم SALE_ID مفتاحاً للعنقدة. ومع أن الفهرس SALE_IOT_DATE على العمود SALE_DATE وحده، فإنه يحتفظ بنسخة من مفتاح العنقدة SALE_ID فيستطيع تلبية الاستعلام باستخدام الفهرس الثانوي وحده.

وعند اختيار أعمدة أخرى، يجب على قاعدة البيانات تنفيذ INDEX UNIQUE SCAN على الفهرس العنقودي مقابل كل صف:

SELECT eur_value
  FROM sales_iot
 WHERE sale_date = ?

---------------------------------------------------
| Id  | Operation         | Name           | Cost |
---------------------------------------------------
|   0 | SELECT STATEMENT  |                |   13 |
|*  1 |  INDEX UNIQUE SCAN| SALES_IOT_PK   |   13 |
|*  2 |   INDEX RANGE SCAN| SALES_IOT_DATE |    4 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("SALE_DATE"=:DT)
   2 - access("SALE_DATE"=:DT)

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

مهم

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

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

ودعم قواعد البيانات للجداول المنظَّمة بالفهرس والفهارس العنقودية غير متسق أبداً. وتشرح النظرة العامة التالية أهم الخصائص.

Db2 (LUW) ليس في Db2 جداول منظَّمة بالفهرس، لكنه يستخدم مصطلح «الفهرس العنقودي» لميزة مختلفة؛ فهو يستخدم جدول كومة، لكنه يحاول insert الصفوف الجديدة في الكتلة نفسها التي توجد فيها صفوف مجاورة في الفهرس.

MySQL

يستخدم محرّك MyISAM جداول الكومة فقط، بينما يستخدم محرّك InnoDB الفهارس العنقودية دائماً. ويعني ذلك أنه ليس لديك خيار مباشر.

Oracle

تستخدم قاعدة بيانات Oracle جداول الكومة افتراضياً. ويمكن إنشاء جداول منظَّمة بالفهرس باستخدام جملة ORGANIZATION INDEX:

CREATE TABLE (
   id    NUMBER NOT NULL PRIMARY KEY,
   [...]
) ORGANIZATION INDEX

وتستخدم قاعدة بيانات Oracle المفتاح الأساسي دائماً مفتاحاً للعنقدة.

PostgreSQL

يستخدم PostgreSQL جداول الكومة فقط.

غير أنه يمكنك استخدام جملة CLUSTER لمواءمة محتويات جدول الكومة مع فهرس.

SQL Server

تستخدم SQL Server افتراضياً فهارس عنقودية (جداول منظَّمة بالفهرس) مستخدمةً المفتاح الأساسي مفتاحاً للعنقدة. ومع ذلك يمكنك استخدام أعمدة اعتباطية لمفتاح العنقدة — حتى أعمدة غير فريدة.

ولإنشاء جدول كومة يجب استخدام جملة NONCLUSTERED في تعريف المفتاح الأساسي:

CREATE TABLE (
   id    NUMBER NOT NULL,
   [...]
   CONSTRAINT pk PRIMARY KEY NONCLUSTERED (id)
)

ويؤدي إسقاط فهرس عنقودي إلى تحويل الجدول إلى جدول كومة.

وغالباً ما يسبّب السلوك الافتراضي في SQL Server مشكلات أداء عند استخدام فهارس ثانوية.