المنطق الذكي
من الميزات الأساسية لقواعد بيانات SQL (databases) دعمها للاستعلامات المخصّصة: فيمكن تنفيذ استعلامات جديدة في أي وقت. وهذا ممكن فقط لأن مُحسِّن الاستعلام (query optimizer) (مخطِّط الاستعلام) يعمل في زمن التشغيل؛ فهو يحلّل كل عبارة عند استلامها ويولّد خطة تنفيذ معقولة فوراً. ويمكن تقليل العبء الناتج عن التحسين في زمن التشغيل باستخدام معاملات الربط.
وخلاصة هذا التذكير أن قواعد البيانات مُحسَّنة لـSQL الديناميكي—فاستخدمه إذا احتجت إليه.
إن أعجبك هذا الموضوع، قد يعجبك أيضاً…
… أن تشترك في القوائم البريدية، وتحصل على ملصقات مجانية، وتشتري كتابي، أو تنضم إلى دورة تدريبية.
ومع ذلك هناك ممارسة شائعة تتجنّب SQL الديناميكي لصالح SQL الثابت—غالباً بسبب خرافة «SQL الديناميكي بطيء». وهذه الممارسة تضر أكثر مما تنفع إذا كانت قاعدة البيانات تستخدم ذاكرة مؤقتة مشتركة لخطط التنفيذ مثل Db2 (LUW) أو قاعدة بيانات Oracle أو SQL Server.
على سبيل التوضيح، تخيّل تطبيقاً يستعلم جدول EMPLOYEES. ويتيح التطبيق البحث بمعرّف الشركة الفرعية ومعرّف الموظف واسم العائلة (دون حساسية لحالة الأحرف) بأي تركيبة. ولا يزال ممكناً كتابة استعلام واحد يغطي جميع الحالات باستخدام منطق «ذكي».
SELECT first_name, last_name, subsidiary_id, employee_id
FROM employees
WHERE ( subsidiary_id = :sub_id OR :sub_id IS NULL )
AND ( employee_id = :emp_id OR :emp_id IS NULL )
AND ( UPPER(last_name) = :name OR :name IS NULL )
يستخدم الاستعلام متغيّرات ربط مسمّاة لقراءة أفضل. وجميع تعبيرات الترشيح الممكنة مكتوبة كتابة ثابتة في العبارة. وكلما لم تكن هناك حاجة إلى مرشّح، تستخدم NULL بدلاً من مصطلح بحث: فيعطّل الشرط عبر منطق OR.
إنها عبارة SQL معقولة تماماً. بل إن استخدام NULL يتوافق مع تعريفه وفق المنطق الثلاثي القيمي في SQL. ومع ذلك فهو أحد أسوأ الأنماط المضادة للأداء على الإطلاق.
لا تستطيع قاعدة البيانات تحسين خطة التنفيذ لمرشّح معين لأن أي مرشّح منها قد يُلغى في زمن التشغيل. فقاعدة البيانات تحتاج إلى الاستعداد لأسوأ الحالات—إذا عُطّلت جميع المرشّحات:
----------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 478 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 2 | 478 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((:NAME IS NULL OR UPPER("LAST_NAME")=:NAME)
AND (:EMP_ID IS NULL OR "EMPLOYEE_ID"=:EMP_ID)
AND (:SUB_ID IS NULL OR "SUBSIDIARY_ID"=:SUB_ID))
ونتيجة لذلك، تستخدم قاعدة البيانات مسحاً كاملاً للجدول حتى لو وُجد فهرس لكل عمود.
ليس أن قاعدة البيانات لا تستطيع حل المنطق «الذكي». إنها تنشئ خطة التنفيذ العامة بسبب استخدام معاملات الربط حتى يمكن تخزينها مؤقتاً وإعادة استخدامها بقيم أخرى لاحقاً. وإذا لم نستخدم معاملات الربط بل كتبنا القيم الفعلية في عبارة SQL، يختار المُحسِّن الفهرس المناسب للمرشّح النشط:
SELECT first_name, last_name, subsidiary_id, employee_id
FROM employees
WHERE( subsidiary_id = NULL OR NULL IS NULL )
AND( employee_id = NULL OR NULL IS NULL )
AND( UPPER(last_name) = 'WINAND' OR 'WINAND' IS NULL )
---------------------------------------------------------------
|Id | Operation | Name | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 |
|*2 | INDEX RANGE SCAN | EMP_UP_NAME | 1 | 1 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("LAST_NAME")='WINAND')
غير أن هذا ليس حلاً. إنه يثبت فقط أن قاعدة البيانات تستطيع حل هذه الشروط.
تحذير
استخدام القيم الحرفية يجعل تطبيقك عرضة لهجمات حقن SQL (SQL injection) وقد يسبب مشكلات أداء بسبب زيادة عبء التحسين.
الحل الواضح للاستعلامات الديناميكية هو SQL الديناميكي. ووفق مبدأ KISS، أخبر قاعدة البيانات بما تحتاجه الآن فقط—ولا شيء آخر.
SELECT first_name, last_name, subsidiary_id, employee_id
FROM employees
WHERE UPPER(last_name) = :name
لاحظ أن الاستعلام يستخدم معامل ربط.
نصيحة
استخدم SQL الديناميكي إذا احتجت إلى عبارات where ديناميكية.
ولا تزل تستخدم معاملات الربط عند توليد SQL الديناميكي—وإلا تحققت خرافة «SQL الديناميكي بطيء».
المشكلة الموصوفة في هذا القسم واسعة الانتشار. ولجميع قواعد البيانات التي تستخدم ذاكرة مؤقتة مشتركة لخطط التنفيذ ميزة للتعامل معها—غالباً ما تُدخل مشكلات وأخطاء جديدة.
Db2 (LUW) يستخدم Db2 ذاكرة مؤقتة مشتركة لخطط التنفيذ وهو معرّض تماماً للمشكلة الموصوفة في هذا القسم.
يتيح Db2 تحديد نهج إعادة التحسين باستخدام تلميح REOPT. والقيمة الافتراضية هي NONE التي تنتج خطة تنفيذ عامة وتعاني المشكلة الموصوفة أعلاه. وتطلب REOPT(ALWAYS) من المُحسِّن أن يطّلع دائماً على متغيّرات الربط الفعلية لإنشاء أفضل خطة لكل تنفيذ. وهذا فعلياً إيقاف للتخزين المؤقت لخطط التنفيذ لتلك العبارة.
والخيار الأخير هو REOPT(ONCE) الذي يطّلع على معاملات الربط للتنفيذ الأول فقط. ومشكلة هذا النهج سلوكه غير الحتمي: فالقيم من التنفيذ الأول تؤثر في جميع التنفيذات. وقد تتغير خطة التنفيذ كلما أُعيد تشغيل قاعدة البيانات، أو—بشكل أقل قابلية للتنبؤ—تنتهي صلاحية الخطة المخزّنة مؤقتاً فيعيد المُحسِّن إنشاءها بقيم مختلفة في المرة التالية التي تُنفَّذ فيها العبارة.
MySQL
لا يعاني MySQL من هذه المشكلة تحديداً لأنه لا يملك ذاكرة مؤقتة لخطط التنفيذ إطلاقاً. وتناقش طلب ميزة من 2009 أثر التخزين المؤقت لخطط التنفيذ. ويبدو أن مُحسِّن MySQL بسيط بما يكفي بحيث لا يستحق التخزين المؤقت لخطط التنفيذ.
Oracle
تستخدم قاعدة بيانات Oracle ذاكرة مؤقتة مشتركة لخطط التنفيذ («SQL area») وهي معرّضة تماماً للمشكلة الموصوفة في هذا القسم.
أدخلت Oracle ما يسمى التطلّع إلى معاملات الربط (bind peeking) مع الإصدار 9i. ويتيح التطلّع إلى معاملات الربط للمُحسِّن استخدام قيم الربط الفعلية من التنفيذ الأول عند إعداد خطة التنفيذ. ومشكلة هذا النهج سلوكه غير الحتمي: فالقيم من التنفيذ الأول تؤثر في جميع التنفيذات. وقد تتغير خطة التنفيذ كلما أُعيد تشغيل قاعدة البيانات، أو—بشكل أقل قابلية للتنبؤ—تنتهي صلاحية الخطة المخزّنة مؤقتاً فيعيد المُحسِّن إنشاءها بقيم مختلفة في المرة التالية التي تُنفَّذ فيها العبارة.
أدخل الإصدار 11g مشاركة المؤشرات التكيّفية (adaptive cursor sharing) لتحسين الوضع أكثر. وتتيح هذه الميزة لقاعدة البيانات تخزين عدة خطط تنفيذ مؤقتاً لعبارة SQL نفسها. علاوة على ذلك، يطّلع المُحسِّن على معاملات الربط ويخزّن انتقائيتها المقدّرة مع خطة التنفيذ. وعند الوصول إلى الذاكرة المؤقتة لاحقاً، يجب أن تقع انتقائية قيم الربط الحالية ضمن نطاقات انتقائية خطة تنفيذ مخزّنة لتُعاد استخدامها. وإلا ينشئ المُحسِّن خطة تنفيذ جديدة ويقارنها بخطط التنفيذ المخزّنة مسبقاً لهذا الاستعلام. وإذا وُجدت خطة تنفيذ كهذه بالفعل، تستبدلها قاعدة البيانات بخطة تنفيذ جديدة تغطي أيضاً تقديرات انتقائية قيم الربط الحالية. وإن لم توجد، تخزّن نسخة خطة تنفيذ جديدة لهذا الاستعلام—مع تقديرات الانتقائية بالطبع.
PostgreSQL
تعمل ذاكرة PostgreSQL المؤقتة لخطط الاستعلام للعبارات المفتوحة فقط—أي ما دمت تُبقي PreparedStatement مفتوحاً. ولا تحدث المشكلة الموصوفة أعلاه إلا عند إعادة استخدام مقبض عبارة. لاحظ أن مشغّل JDBC في PostgreSQL يفعّل الذاكرة المؤقتة بعد التنفيذ الخامس فقط. انظر أيضاً: التخطيط بقيم الربط الفعلية.
SQL Server
يستخدم SQL Server ما يسمى استنشاق المعاملات (parameter sniffing). ويتيح استنشاق المعاملات للمُحسِّن استخدام قيم الربط الفعلية من التنفيذ الأول أثناء التحليل. ومشكلة هذا النهج سلوكه غير الحتمي: فالقيم من التنفيذ الأول تؤثر في جميع التنفيذات. وقد تتغير خطة التنفيذ كلما أُعيد تشغيل قاعدة البيانات، أو—بشكل أقل قابلية للتنبؤ—تنتهي صلاحية الخطة المخزّنة مؤقتاً فيعيد المُحسِّن إنشاءها بقيم مختلفة في المرة التالية التي تُنفَّذ فيها العبارة.
يوفر SQL Server تلميحات استعلام لمزيد من التحكم في استنشاق المعاملات وإعادة الترجمة. ويتجاوز تلميح الاستعلام RECOMPILE ذاكرة الخطط المؤقتة لعبارة مختارة. وتتيح OPTIMIZE FOR تحديد قيم معاملات فعلية تُستخدم للتحسين فقط. وأخيراً، يمكنك تقديم خطة تنفيذ كاملة بتلميح USE PLAN.
غير أن هذه الميزات قطعت شوطاً طويلاً إذ وُجدت عدة أخطاء وسلوكيات مفاجئة في حالات خاصة. ووصفها يتجاوز نطاق هذا الكتاب بكثير، لكن لحسن الحظ يحتفظ Erland Sommarskog بجميع المعلومات ذات الصلة حتى SQL Server 2022.
ورغم أن الطرق الاستكشافية يمكن أن تحسّن مشكلة «المنطق الذكي» إلى حد ما، فقد بُنيت في الواقع للتعامل مع مشكلات معامل الربط فيما يتصل بالمدرجات التكرارية للأعمدة وتعبيرات LIKE.
وأكثر الطرق موثوقية للوصول إلى أفضل خطة تنفيذ هي تجنّب المرشّحات غير الضرورية في عبارة SQL.