مسح الفهرس فقط: تجنّب الوصول إلى الجدول
يُعدّ مسح الفهرس فقط (index-only scan) واحداً من أقوى أساليب الضبط على الإطلاق؛ فهو لا يتجنّب الوصول إلى الجدول لتقييم شرط where فحسب، بل يتجنّب الوصول إلى الجدول كلياً إذا استطاعت قاعدة البيانات العثور على الأعمدة المختارة في الفهرس نفسه.
ولتغطية استعلام كامل، يجب أن يحتوي الفهرس على جميع الأعمدة الواردة في عبارة SQL، وبخاصة أيضاً الأعمدة الموجودة في جملة select كما يوضح المثال التالي:
CREATE INDEX sales_sub_eur
ON sales
( subsidiary_id, eur_value )
SELECT SUM(eur_value)
FROM sales
WHERE subsidiary_id = ?
بالطبع تأتي فهرسة جملة where في المقدمة على غيرها من الجمل. ولذلك يقع العمود SUBSIDIARY_ID في الموضع الأول ليُعدّ مُسند وصول.
إن أعجبك هذا الموضوع، قد يعجبك أيضاً…
… أن تشترك في القوائم البريدية، وتحصل على ملصقات مجانية، وتشتري كتابي، أو تنضم إلى دورة تدريبية.
تُظهر خطة التنفيذ مسح الفهرس دون وصول لاحق إلى الجدول (TABLE ACCESS BY INDEX ROWID).
Db2 (LUW)
Explain Plan
---------------------------------------------------------------
ID | Operation | Rows | Cost
1 | RETURN | | 21
2 | GRPBY (COMPLETE) | 1 of 34804 ( .00%) | 21
3 | IXSCAN SALES_SUB_EUR | 34804 of 1009326 ( 3.45%) | 19
Predicate Information
3 - START (Q1.SUBSIDIARY_ID = ?)
STOP (Q1.SUBSIDIARY_ID = ?)
Oracle
----------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 104 |
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | INDEX RANGE SCAN| SALES_SUB_EUR | 40388 | 104 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SUBSIDIARY_ID"=TO_NUMBER(:A))
يغطي الفهرس الاستعلام بأكمله، ولذلك يسمى أيضاً فهرساً مُغطّياً (covering index).
ملاحظة
إذا منع الفهرس الوصول إلى الجدول، سُمّي أيضاً فهرساً مُغطّياً.
غير أن المصطلح مضلّل لأنه يوحي بأنه خاصية من خصائص الفهرس، بينما تشير عبارة «مسح الفهرس فقط» على نحو صحيح إلى أنه عملية من عمليات خطة التنفيذ.
يحتوي الفهرس على نسخة من العمود EUR_VALUE، لذا تستطيع قاعدة البيانات استخدام القيمة المخزنة في الفهرس. ولا يلزم الوصول إلى الجدول لأن الفهرس يملك كل المعلومات اللازمة لتلبية الاستعلام.
ويمكن لمسح الفهرس فقط أن يحسّن الأداء تحسّناً هائلاً. انظر فقط إلى تقدير عدد الصفوف في خطة التنفيذ: يتوقع المُحسِّن تجميع أكثر من 40,000 صف. ويعني ذلك أن مسح الفهرس فقط يمنع 40,000 عملية جلب من الجدول — إذا كان كل صف في كتلة جدول مختلفة. أما إذا كان للفهرس عامل عنقدة جيد، أي إذا كانت الصفوف المعنية مجمّعة جيداً في بضع كتل من الجدول، فقد تكون الميزة أقل بكثير.
وإلى جانب عامل العنقدة، يحدّ عدد الصفوف المختارة من المكسب المحتمل في الأداء لمسح الفهرس فقط. فإذا اخترت صفاً واحداً مثلاً، فلن توفّر سوى وصول واحد إلى الجدول. وبالنظر إلى أن اجتياز الشجرة يحتاج إلى جلب بضع كتل أيضاً، فقد يصبح الوصول الموفَّر إلى الجدول مهملاً.
مهم
تعتمد الميزة الأدائية لمسح الفهرس فقط على عدد الصفوف التي يُوصَل إليها وعلى عامل العنقدة في الفهرس.
ومسح الفهرس فقط استراتيجية فهرسة عدوانية. فلا تصمّم فهرساً لأجل مسح الفهرس فقط بمجرد الشك، لأنه يستهلك الذاكرة بلا داع ويزيد جهد الصيانة اللازم لعبارات update. انظر الفصل 8، «تعديل البيانات». عملياً، ينبغي أن تفهرس أولاً دون النظر إلى جملة select، وألا توسّع الفهرس إلا عند الحاجة.
وقد يسبّب مسح الفهرس فقط مفاجآت غير سارة أيضاً، كما لو قصرنا الاستعلام على المبيعات الحديثة:
SELECT SUM(eur_value)
FROM sales
WHERE subsidiary_id = ?
AND sale_date > ?
من دون النظر إلى خطة التنفيذ، قد يتوقع المرء أن يعمل الاستعلام أسرع لأنه يختار صفوفاً أقل. غير أن جملة where تشير إلى عمود غير موجود في الفهرس، لذا يجب على قاعدة البيانات أن تصل إلى الجدول لتحميل هذا العمود.
Db2 (LUW)
Explain Plan
-------------------------------------------------------------------
ID | Operation | Rows | Cost
1 | RETURN | | 13547
2 | GRPBY (COMPLETE) | 1 of 1223 ( .08%) | 13547
3 | FETCH SALES | 1223 of 34804 ( 3.51%) | 13547
4 | RIDSCN | 34804 of 34804 (100.00%) | 32
5 | SORT (UNIQUE) | 34804 of 34804 (100.00%) | 32
6 | IXSCAN SALES_SUB_EUR | 34804 of 1009326 ( 3.45%) | 19
Predicate Information
3 - SARG (? < Q1.SALE_DATE)
SARG (Q1.SUBSIDIARY_ID = ?)
6 - START (Q1.SUBSIDIARY_ID = ?)
STOP (Q1.SUBSIDIARY_ID = ?)
Oracle
--------------------------------------------------------------
|Id | Operation | Name | Rows |Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 371 |
| 1 | SORT AGGREGATE | | 1 | |
|*2 | TABLE ACCESS BY INDEX ROWID| SALES | 2019 | 371 |
|*3 | INDEX RANGE SCAN | SALES_DATE| 10541 | 30 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SUBSIDIARY_ID"=TO_NUMBER(:A))
3 - access("SALE_DATE">:B)
يزيد الوصول إلى الجدول من زمن الاستجابة رغم أن الاستعلام يختار صفوفاً أقل. والعامل ذو الصلة ليس عدد الصفوف التي يعيدها الاستعلام، بل عدد الصفوف التي يجب على قاعدة البيانات فحصها للعثور عليها.
تحذير
قد يؤدي توسيع جملة where إلى سلوك أداء «غير منطقي». افحص خطة التنفيذ قبل توسيع الاستعلامات.
وإذا لم يعد بالإمكان استخدام الفهرس لمسح الفهرس فقط، اختار المُحسِّن ثاني أفضل خطة تنفيذ. ويعني ذلك أن المُحسِّن قد يختار خطة تنفيذ مختلفة تماماً أو، كما في الحالة أعلاه، خطة مشابهة بفهرس آخر. وهو هنا يستخدم فهرساً على SALE_DATE، وهو من بقايا الفصل السابق.
ومن منظور المُحسِّن، لهذا الفهرس ميزتان على SALES_SUB_EUR: إذ يعتقد المُحسِّن أن المرشّح على SALE_DATE أكثر انتقائية من المرشّح على SUBSIDIARY_ID، ويمكنك رؤية ذلك في عمود «Rows» في خطتي التنفيذ الأخيرتين (نحو 10,000 مقابل 40,000). غير أن هذه التقديرات اعتباطية محضة لأن الاستعلام يستخدم وسائط ربط؛ فقد يختار شرط SALE_DATE الجدول بأكمله مثلاً عند تمرير تاريخ أول عملية بيع.
والميزة الثانية لفهرس SALES_DATE أنه يمتلك عامل عنقدة أفضل. وهذا سبب وجيه، لأن جدول SALES لا ينمو إلا زمنياً؛ فالصفوف الجديدة تُضاف دائماً إلى نهاية الجدول ما لم تُحذف صفوف. ولذلك يوافق ترتيب الجدول ترتيب الفهرس لأن كليهما مرتّب زمنياً إجمالاً، فيكون للفهرس عامل عنقدة جيد.
وعند استخدام فهرس بعامل عنقدة جيد، تُخزَّن صفوف الجدول المختارة قريبة بعضها من بعض، فلا تحتاج قاعدة البيانات إلا إلى قراءة بضع كتل جدول للحصول على جميع الصفوف. وبهذا الفهرس، قد يكون الاستعلام سريعاً بما يكفي دون مسح الفهرس فقط. وفي هذه الحالة ينبغي إزالة الأعمدة غير اللازمة من الفهرس الآخر.
ملاحظة
بعض الفهارس لها عامل عنقدة جيد تلقائياً، فيكون المكسب الأدائي لمسح الفهرس فقط ضئيلاً.
وفي هذا المثال تحديداً، وقعت مصادفة سعيدة: فالمرشّح الجديد على SALE_DATE لم يمنع مسح الفهرس فقط فحسب، بل فتح في الوقت نفسه مسار وصول جديداً. ولذلك استطاع المُحسِّن أن يحدّ من أثر هذا التغيير في الأداء. غير أنه من الممكن أيضاً منع مسح الفهرس فقط بإضافة أعمدة إلى جمل أخرى. لكن إضافة عمود إلى جملة select لا يمكن أن تفتح مسار وصول جديداً قط، وهو ما قد يحدّ من أثر فقدان مسح الفهرس فقط.
نصيحة
حافظ على عمليات مسح الفهرس فقط لديك.
أضف تعليقات تذكّرك بمسح الفهرس فقط وتشير إلى هذه الصفحة ليتمكن أي شخص من القراءة عنه.
ويمكن أن تسبّب الفهارس القائمة على الدوال مفاجآت غير سارة أيضاً فيما يتصل بمسح الفهرس فقط؛ فالفهرس على UPPER(last_name) لا يمكن استخدامه لمسح الفهرس فقط عند اختيار العمود LAST_NAME. وكان ينبغي في القسم السابق أن نفهرس العمود LAST_NAME نفسه لدعم مرشّح LIKE وإتاحة استخدامه في مسح الفهرس فقط عند اختيار العمود LAST_NAME.
نصيحة
احرص دائماً على فهرسة البيانات الأصلية، فهي غالباً أنفع معلومات يمكنك وضعها في فهرس.
تجنّب الفهرسة القائمة على الدوال للتعبيرات التي لا تصلح لتكون مُسندات وصول.
تُعدّ الاستعلامات التجميعية مثل الاستعلام أعلاه مرشّحة جيدة لمسح الفهرس فقط؛ فهي تستعلم عن صفوف كثيرة لكن عن أعمدة قليلة فقط، ما يجعل فهرساً نحيفاً كافياً لدعم مسح الفهرس فقط. وكلما زاد عدد الأعمدة التي تستعلم عنها، زاد عدد الأعمدة التي يجب إضافتها إلى الفهرس لدعم مسح الفهرس فقط. ولهذا ينبغي لك كمطوّر أن تختار الأعمدة التي تحتاج إليها فعلاً فقط.
نصيحة
تجنّب select * واجلب الأعمدة التي تحتاج إليها فقط.
وبصرف النظر عن أن فهرسة صفوف كثيرة تستهلك مساحة كبيرة، يمكنك أيضاً أن تصل إلى حدود قاعدة بياناتك؛ فمعظم قواعد البيانات تفرض حدوداً صارمة نوعاً ما على عدد الأعمدة في الفهرس وعلى الحجم الكلي لمدخل الفهرس. ويعني ذلك أنك لا تستطيع فهرسة عدد اعتباطي من الأعمدة ولا أعمدة طويلة اعتباطياً. وتسرد النظرة العامة التالية أهم القيود. ومع ذلك، توجد فهارس تغطي جدولاً بأكمله كما سنرى في القسم التالي.
INCLUDE: أعمدة غير مفتاحية
تدعم SQL Server وPostgreSQL 11+ ما يسمى الأعمدة غير المفتاحية في فهارس شجرة B. وهذه الأعمدة — بخلاف الأعمدة المفتاحية التي ناقشناها حتى الآن — تُخزَّن في العقد الورقية فقط، ولذلك لا يمكن استخدامها كمُسندات وصول.
وتُحدَّد الأعمدة غير المفتاحية في جملة include:
CREATE INDEX empsubupnam
ON employees
(subsidiary_id, last_name)
INCLUDE(phone_number, first_name)
Db2 (LUW) تحدّ من الفهرس بـ64 عموداً وبحد أقصى لطول المفتاح يساوي 25% من حجم الصفحة.
وتدعم Db2 أيضاً جملة INCLUDE لإضافة أعمدة غير مفتاحية إلى الفهارس الفريدة. ويتيح لك ذلك توسيع فهرس فريد بأعمدة إضافية ليُستخدم في مسح الفهرس فقط دون تغيير دلالات الفرادة.
MySQL
مع InnoDB، تحدّ MySQL الطول الكلي للمفتاح (جميع الأعمدة) بـ3072 بايت. كما أن طول كل عمود محدود بـ767 بايت إذا لم تُمكَّن innodb_large_prefix أو استُخدمت صيغ صفوف غير DYNAMIC أو COMPRESSED. وكان ذلك هو السلوك الافتراضي حتى MySQL 5.6 ضمناً. أما فهارس MyISAM فمحدودة بـ16 عموداً وبحد أقصى لطول المفتاح يساوي 1000 بايت.
ولـMySQL ميزة فريدة تسمى «فهرسة البادئة» (prefix indexing) وتسمى أحياناً أيضاً «الفهرسة الجزئية». وهي تعني فهرسة الأحرف القليلة الأولى من العمود فقط، فلا صلة لها إذن بالفهارس الجزئية الموصوفة في الفصل 2. وإذا فهرست عموداً يتجاوز الطول المسموح للعمود (767 أو 1000 أو 3072 بايت كما وصفنا أعلاه)، فقد تقتطع MySQL العمود وفقاً لذلك — تبعاً لـنمط SQL وصيغة الصف. وفي هذه الحالة تنجح عبارة create index مع التحذير «Specified key was too long; max key length is … bytes». ويعني ذلك أن الفهرس لم يعد يحتفظ بنسخة كاملة من هذا العمود، فاختيار العمود يمنع مسح الفهرس فقط (على غرار الفهارس القائمة على الدوال).
ويمكنك استخدام فهرسة البادئة في MySQL صراحةً لتجنّب تجاوز حد الطول الكلي للمفتاح إذا ظهرت لك رسالة الخطأ «Specified key was too long; max key length is … bytes». والمثال التالي يفهرس الأحرف العشرة الأولى من العمود LAST_NAME فقط.
CREATE INDEX .. ON employees (last_name(10))
Oracle
يعتمد الحد الأقصى لطول مفتاح الفهرس على حجم الكتلة ومعاملات تخزين الفهرس (75% من حجم كتلة قاعدة البيانات ناقص بعض الكلفة الإضافية). وفهرس شجرة B محدود بـ32 عموداً.
وعند استخدام Oracle 11g بجميع الإعدادات الافتراضية (كتل بحجم 8k)، يكون الحد الأقصى لطول مفتاح الفهرس 6398 بايت. ويؤدي تجاوز هذا الحد إلى رسالة الخطأ «ORA-01450: maximum key length (6398) exceeded.»
PostgreSQL
تدعم قاعدة بيانات PostgreSQL مسح الفهرس فقط منذ الإصدار 9.2.
ويُحدّ طول مدخلات شجرة B بـ2713 بايت (قيمة مثبتة في الشيفرة، نحو BLCKSZ/3). ولا تظهر رسالة الخطأ المقابلة «index row size ... exceeds btree maximum, 2713» إلا عند تنفيذ عملية insert أو update تتجاوز الحد. ويمكن أن تحتوي فهارس شجرة B ما يصل إلى 32 عموداً.
SQL Server
تدعم SQL Server منذ الإصدار 2016 ما يصل إلى 32 عموداً مفتاحياً بحجم أقصى 1700 بايت (900 بايت للفهارس العنقودية).[1] ولا تُحسب الأعمدة غير المفتاحية ضمن هذا الحد.
فكّر في الأمر
الاستعلامات التي لا تختار أي أعمدة من الجدول تُنفَّذ غالباً بمسح الفهرس فقط.
هل يمكنك التفكير في مثال ذي معنى؟
قبل SQL Server 2016: 16 عمودًا و900 بايت. ↩︎