فهرسة مرشّحات `LIKE`

كثيراً ما يسبّب المعامل LIKE في SQL سلوك أداء غير متوقع، لأن بعض قيم البحث تمنع الاستخدام الفعّال للفهرس؛ أي إن هناك قيم بحث يمكن فهرستها فهرسة جيدة جداً، وأخرى لا يمكن. وموضع المحارف البديلة (wildcards) هو ما يصنع الفرق كله.

يستخدم المثال التالي المحرف البديل % في وسط قيمة البحث:

SELECT first_name, last_name, date_of_birth
  FROM employees
 WHERE UPPER(last_name) LIKE 'WIN%D'

Db2 (LUW)

Explain Plan
----------------------------------------------------
ID | Operation         |                 Rows | Cost
 1 | RETURN            |                      |   13
 2 |  FETCH EMPLOYEES  |     1 of 1 (100.00%) |   13
 3 |   IXSCAN EMP_NAME | 1 of 10000 (   .01%) |    6

Predicate Information
 3 - START ('WIN....................................
      STOP (Q1.LAST_NAME <= 'WIN....................
      SARG (Q1.LAST_NAME LIKE 'WIN%D')

في هذا المثال، غُيِّر الاستعلام ليصبح WHERE last_name LIKE 'WIN%D' (بلا UPPER). ويبدو أن Db2 (LUW) 10.5 لا تستطيع استخدام مُسند وصول من LIKE على فهرس قائم على الدوال (وهي تنفّذ مسح فهرس كاملاً في أفضل الأحوال).

وخلافاً لذلك، تتألق Db2 هنا: فهي تُظهر بوضوح شرطَي START وSTOP، وهما الجزء الواقع قبل المحرف البديل الأول، لكنها تُظهر أيضاً أن النمط الكامل يُطبَّق كمُسند ترشيح.

MySQL

+----+-----------+-------+----------+---------+------+-------------+
| id | table     | type  | key      | key_len | rows | Extra       |
+----+-----------+-------+----------+---------+------+-------------+
|  1 | employees | range | emp_name | 767     |    2 | Using where |
+----+-----------+-------+----------+---------+------+-------------+

Oracle

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

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(UPPER("LAST_NAME") LIKE 'WIN%D')
       filter(UPPER("LAST_NAME") LIKE 'WIN%D')

PostgreSQL

                       QUERY PLAN
----------------------------------------------------------
Index Scan using emp_up_name on employees
   (cost=0.01..8.29 rows=1 width=17)
   Index Cond: (upper((last_name)::text) ~>=~ 'WIN'::text)
           AND (upper((last_name)::text) ~<~  'WIO'::text)
       Filter: (upper((last_name)::text) ~~ 'WIN%D'::text)

لا يمكن لمرشّحات LIKE استخدام الأحرف الواقعة قبل المحرف البديل الأول إلا أثناء اجتياز الشجرة. أما الأحرف المتبقية فهي مجرد مُسندات ترشيح لا تضيّق نطاق الفهرس الممسوح. ولذلك يمكن أن يحتوي تعبير LIKE واحد على نوعَي مُسندات: (1) الجزء الواقع قبل المحرف البديل الأول كمُسند وصول؛ (2) والأحرف الأخرى كمُسند ترشيح.

تنبيه

يعمل المعامل LIKE على أساس محرف بمحرف، بينما يمكن للترتيبات المحرّفية أن تعالج محارف متعددة كعنصر فرز واحد. ولذلك تمنع بعض الترتيبات المحرّفية استخدام الفهارس مع LIKE. اقرأ فهرسة «LIKE» في PostgreSQL وOracle لـLaurenz Albe لمزيد من التفاصيل.

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

الشكل 2.4 عمليات بحث LIKE متنوعة

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

مهم

لا يعمل إلا الجزء الواقع قبل المحرف البديل الأول كمُسند وصول.

والأحرف المتبقية لا تضيّق نطاق الفهرس الممسوح؛ وإنما تُستبعد المدخلات غير المطابقة من النتيجة فقط.

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

نصيحة

تجنّب تعبيرات LIKE ذات المحارف البديلة في البداية (مثل '%TERM').

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

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

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

تفترض معظم قواعد البيانات عند تحسين شرط LIKE بوسيط ربط أنه لا يوجد محرف بديل في البداية، لكن هذا الافتراض خاطئ إذا كان تعبير LIKE مستخدماً للبحث النصي الكامل. ولا توجد، للأسف، طريقة مباشرة لوسم شرط LIKE بأنه بحث نصي كامل. ويعرض المربع «وسم تعبيرات LIKE للبحث النصي الكامل» محاولة لا تنجح. وتحديد قيمة البحث دون وسيط ربط هو الحل الأوضح، لكنه يزيد كلفة التحسين ويفتح ثغرة حقن SQL (SQL injection). وهناك حل فعّال وآمن وقابل للنقل في الوقت نفسه: تشويش شرط LIKE عمداً. ويشرح «دمج الأعمدة» ذلك بالتفصيل.

وسم تعبيرات LIKE للبحث النصي الكامل

عند استخدام المعامل LIKE للبحث النصي الكامل، يمكننا فصل المحارف البديلة عن قيمة البحث:

WHERE text_column LIKE '%' || ? || '%'

أما في قاعدة بيانات PostgreSQL فالمشكلة مختلفة، لأن PostgreSQL تفترض وجود محرف بديل في البداية عند استخدام وسائط ربط في تعبير LIKE؛ وهي ببساطة لا تستخدم فهرساً في تلك الحالة. والطريقة الوحيدة للحصول على وصول عبر فهرس لتعبير LIKE هي جعل قيمة البحث الفعلية مرئية للمُحسِّن. وإذا لم تستخدم وسيط ربط بل وضعت قيمة البحث مباشرة في عبارة SQL، فيجب عليك اتخاذ احتياطات أخرى ضد هجمات حقن SQL!

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

Db2 (LUW)

تدعم Db2 الكلمة المفتاحية contains. انظر «دوال البحث في Db2 Text Search».

MySQL

تقدّم MySQL الكلمتين المفتاحيتين match وagainst للبحث النصي الكامل. وبدءاً من MySQL 5.6، يمكنك إنشاء فهارس نصية كاملة لجداول InnoDB أيضاً، بينما كان ذلك ممكناً سابقاً مع جداول MyISAM فقط. انظر «دوال البحث النصي الكامل» في وثائق MySQL.

Oracle

تقدّم قاعدة بيانات Oracle الكلمة المفتاحية contains. انظر «دليل مطوّر تطبيقات Oracle Text».

PostgreSQL

تقدّم PostgreSQL المعامل @@ لتنفيذ عمليات البحث النصي الكامل. انظر «البحث النصي الكامل» في وثائق PostgreSQL.

وهناك خيار آخر هو استخدام امتداد WildSpeed لتحسين تعبيرات LIKE مباشرة. ويخزّن الامتداد النص بجميع دوراته الممكنة بحيث يقع كل محرف في البداية مرة واحدة. ويعني ذلك أن النص المفهرس لا يُخزَّن مرة واحدة بل بعدد محارف السلسلة، ومن ثمّ يحتاج مساحة كبيرة.

SQL Server

تقدّم SQL Server الكلمة المفتاحية contains. انظر «البحث النصي الكامل» في وثائق SQL Server.

فكّر في الأمر

كيف يمكنك فهرسة بحث LIKE له محرف بديل واحد فقط في بداية قيمة البحث ('%TERM')؟