دليل SQL: كيفية كتابة الاستعلامات بشكل أفضل (الجزء 2)

تابع المقال دليل SQL: كيفية كتابة الاستعلامات بشكل أفضل (الجزء 1)

من الطلب إلى خطط التنفيذ

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

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

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

تحسين الاستعلام

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

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

• تحتوي بعض الحزم على أدوات تنشئ تمثيلًا رسوميًا لخطة الاستعلام. النظر في المثال التالي:
  
  
  
• ستوفر الأدوات الأخرى وصفًا نصيًا لخطة الاستعلام. أحد الأمثلة هو عبارة EXPLAIN PLAN في Oracle ، لكن اسم التعليمة يعتمد على قواعد بيانات DBMS التي تعمل معها. في مكان آخر ، يمكنك العثور على EXPLAIN (MySQL أو PostgreSQL) أو EXPLAIN QUERY PLAN (SQLite).

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

في باقي هذا القسم ، ستتعلم المزيد عن EXPLAIN و ANALYZE ، وكذلك كيفية استخدامها للحصول على مزيد من المعلومات حول خطة الاستعلام وأدائها المحتمل. للقيام بذلك ، ابدأ ببعض الأمثلة التي ستعمل بها مع جدولين: one_million ونصف half_million .

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

 EXPLAIN SELECT * FROM one_million; QUERY PLAN ____________________________________________________ Seq Scan on one_million (cost=0.00..18584.82 rows=1025082 width=36) (1 row) 

في هذه الحالة ، سترى أن تكلفة الطلب هي 0.00..18584.82 ، وعدد الصفوف هو 1025082 . عرض عدد الأعمدة 36 .

بالإضافة إلى ذلك ، يمكنك تحديث الإحصاءات باستخدام ANALYZE .

 ANALYZE one_million; EXPLAIN SELECT * FROM one_million; QUERY PLAN ____________________________________________________ Seq Scan on one_million (cost=0.00..18334.00 rows=1000000 width=37) (1 row) 

إلى جانب EXPLAIN و ANALYZE ، يمكنك أيضًا الحصول على وقت التشغيل الفعلي باستخدام EXPLAIN ANALYZE :

 EXPLAIN ANALYZE SELECT * FROM one_million; QUERY PLAN ___________________________________________________________ Seq Scan on one_million (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.015..1207.019 rows=1000000 loops=1) Total runtime: 2320.146 ms (2 rows) 

عيوب استخدام EXPLAIN ANALYZE هي أن الاستعلام قد تم تنفيذه بالفعل ، لذا كن حذرًا في هذا!

حتى الآن ، كل الخوارزميات التي رأيتها هي Seq Scan ( Seq Scan المتسلسل) أو Full Table Scan: هذا مسح يتم إجراؤه في قاعدة بيانات حيث يتم قراءة كل صف من الجدول الممسوح ضوئيًا بالترتيب التسلسلي ويتم التحقق من الأعمدة الموجودة الامتثال للشرط أم لا. فيما يتعلق بالأداء ، فإن عمليات المسح المتسلسل ليست بالتأكيد أفضل خطة تنفيذ لأنك لا تزال تقوم بإجراء مسح كامل للجدول. ومع ذلك ، هذا ليس سيئًا للغاية عندما لا يتلاءم الجدول مع الذاكرة: القراءات المتسلسلة سريعة جدًا حتى على الأقراص البطيئة.

سوف تتعلم المزيد عن هذا لاحقًا عندما نتحدث عن مسح الفهرس.

ومع ذلك ، هناك خوارزميات أخرى. خذ ، على سبيل المثال ، خطة الاستعلام هذه للاتصال:

 EXPLAIN ANALYZE SELECT * FROM one_million JOIN half_million ON (one_million.counter=half_million.counter); QUERY PLAN _________________________________________________________________ Hash Join (cost=15417.00..68831.00 rows=500000 width=42) (actual time=1241.471..5912.553 rows=500000 loops=1) Hash Cond: (one_million.counter = half_million.counter) -> Seq Scan on one_million (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.007..1254.027 rows=1000000 loops=1) -> Hash (cost=7213.00..7213.00 rows=500000 width=5) (actual time=1241.251..1241.251 rows=500000 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 770kB -> Seq Scan on half_million (cost=0.00..7213.00 rows=500000 width=5) (actual time=0.008..601.128 rows=500000 loops=1) Total runtime: 6468.337 ms 

ترى أن مُحسِّن الاستعلام اختار Hash Join here! تذكر هذه العملية ، حيث ستحتاج إليها لتقييم التعقيد الزمني لطلبك. في الوقت الحالي ، لاحظ أنه لا يوجد فهرس في half_million.counter ، نضيفه في المثال التالي:

 CREATE INDEX ON half_million(counter); EXPLAIN ANALYZE SELECT * FROM one_million JOIN half_million ON (one_million.counter=half_million.counter); QUERY PLAN ________________________________________________________________ Merge Join (cost=4.12..37650.65 rows=500000 width=42) (actual time=0.033..3272.940 rows=500000 loops=1) Merge Cond: (one_million.counter = half_million.counter) -> Index Scan using one_million_counter_idx on one_million (cost=0.00..32129.34 rows=1000000 width=37) (actual time=0.011..694.466 rows=500001 loops=1) -> Index Scan using half_million_counter_idx on half_million (cost=0.00..14120.29 rows=500000 width=5) (actual time=0.010..683.674 rows=500000 loops=1) Total runtime: 3833.310 ms (5 rows) 

ترى أنه من خلال إنشاء الفهرس ، قرر مُحسِّن الاستعلام الآن استخدام Merge join عند مسح Index Scan .

لاحظ الفرق بين عمليات مسح الفهرس وعمليات مسح الجدول بالكامل أو عمليات الفحص المتسلسلة: الأولى ، التي تسمى أيضًا "عمليات مسح الجدول" ، تقوم بمسح البيانات أو صفحات الفهرس للعثور على السجلات المقابلة ، بينما تقوم المجموعة الثانية بمسح كل صف من الجدول.

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

نصيحة: إذا كنت ترغب في معرفة المزيد حول EXPLAIN أو تفكر في الأمثلة بمزيد من التفصيل ، فكر في قراءة Guillaume Lelarge ’s Understanding Explain .

تعقيد الوقت و كبير يا

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

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

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

في لغة قاعدة البيانات ، يحدد التعقيد المدة التي تستغرقها لإكمال استعلام مع زيادة حجم جداول البيانات وبالتالي تنمو قاعدة البيانات.

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

تقدير التعقيد الزمني لخطة الاستعلام الخاصة بك

كما رأينا سابقًا ، تحدد خطة التنفيذ ، من بين أشياء أخرى ، الخوارزمية المستخدمة في كل عملية ، والتي تتيح لك التعبير المنطقي عن كل وقت لتنفيذ الاستعلام كدالة بحجم الجدول المضمن في خطة الاستعلام ، والتي تسمى وظيفة التعقيد. بمعنى آخر ، يمكنك استخدام رمز O الكبير وخطة التنفيذ لتقييم تعقيد الاستعلام والأداء.

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

تلميح: الفهارس جزء من هذه القصة!

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

يا (1): وقت ثابت

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

هذا النوع من الاستعلامات ليس شائعًا حقًا ، ولكن هذا مثال على ذلك:

 SELECT TOP 1 t.* FROM t 

التعقيد الزمني ثابت ، حيث يتم تحديد صف تعسفي واحد من الجدول. لذلك ، يجب ألا يعتمد طول الوقت على حجم الجدول.

الخطي الوقت: يا (ن)

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

مثال على ذلك هو الاستعلام الذي يحتوي على WHERE لعمود غير مفهر: ستكون هناك حاجة إلى مسح كامل للجدول أو Seq Scan ، مما سيؤدي إلى تعقيد الوقت O (n). هذا يعني أنه يجب قراءة كل سطر للعثور على السطر ذي المعرف المطلوب (ID). ليس لديك أي قيود على الإطلاق ، لذلك تحتاج إلى حساب كل سطر ، حتى إذا كان السطر الأول يطابق الشرط.

ضع في اعتبارك أيضًا مثال الاستعلام التالي ، والذي سيكون له تعقيد O (n) إذا لم يكن هناك فهرس في حقل i_id :

 SELECT i_id FROM item; 

• تعني السابقة أيضًا استعلامات أخرى ، مثل الاستعلامات لحساب عدد الصفوف COUNT (*) FROM TABLE; سيكون هناك تعقيد زمني O (n) ، نظرًا لأن مسح جدول كامل سيكون مطلوبًا لأن إجمالي عدد الصفوف لم يتم حفظه للجدول. خلاف ذلك ، فإن تعقيد الوقت تكون مماثلة ل O (1) .
  

يرتبط وقت التشغيل الخطي ارتباطًا وثيقًا بوقت تشغيل الخطط التي لها وصلات الجدول. فيما يلي بعض الأمثلة:

• يحتوي رابط التجزئة على التعقيد المتوقع لـ O (M + N) ، وخوارزمية ربط التجزئة الكلاسيكية للانضمام داخليًا إلى جدولين تعد أولاً جدول التجزئة الخاص بالجدول الأصغر. تتكون إدخالات جدول التجزئة من سمة اتصال وسلسلة. يتم الوصول إلى جدول التجزئة من خلال تطبيق وظيفة التجزئة على سمة الاتصال. بمجرد إنشاء جدول التجزئة ، يتم فحص جدول كبير ، ويتم العثور على الصفوف المقابلة من الجدول الأصغر من خلال البحث في جدول التجزئة.
• عادةً ما يكون لدمج الوصلات معقد O (M + N) ، لكن ذلك يعتمد بشكل كبير على مؤشرات عمود الوصلة ، وإذا لم يكن هناك فهرس ، ما إذا كان يتم فرز الصفوف وفقًا للمفاتيح المستخدمة في الصلة:
  • إذا تم تصنيف كلا الجدولين وفقًا للمفاتيح المستخدمة في الصلة ، فسيكون للاستعلام تعقيد زمني لـ O (M + N).
  • إذا كان كلا الجدولين يحتوي على فهرس للأعمدة المرتبطة ، فإن الفهرس يدعم بالفعل هذه الأعمدة بالترتيب والفرز غير مطلوب. ستكون الصعوبة O (M + N).
  • إذا لم يكن أي من الجداول يحتوي على فهرس بالأعمدة المتصلة ، فيجب أولاً فرز كلا الجدولين ، بحيث يبدو التعقيد مثل O (سجل M + N سجل N).
  • إذا كان أحد الجداول فقط به فهرس على الأعمدة المتصلة ، فيجب فقط فرز الجدول الذي لا يحتوي على فهرس قبل حدوث خطوة الصلة ، بحيث يبدو التعقيد مثل O (سجل M + N N).
• بالنسبة للوصلات المتداخلة ، يكون التعقيد عادةً O (MN). يكون هذا الربط فعالًا عندما يكون أحد الجداول أو كلاهما صغيرًا للغاية (على سبيل المثال ، أقل من 10 سجلات) ، وهو موقف شائع جدًا عند تقييم الاستعلامات ، حيث تتم كتابة بعض الاستعلامات الفرعية لإرجاع صف واحد فقط.

تذكر: صلة متداخلة هي صلة تقارن كل سجل في جدول واحد بكل سجل في آخر.

الوقت اللوغاريتمي: O (log (n))

يقال إن الخوارزمية تعمل في وقت لوغاريتمي إذا كان وقت تنفيذها متناسباً مع لوغاريتم حجم الإدخال ؛ بالنسبة للاستعلامات ، هذا يعني أنه سيتم تنفيذها إذا كان وقت التنفيذ يتناسب مع لوغاريتم حجم قاعدة البيانات.

هذا التعقيد الزمني لوغاريتمي صالح لخطط الاستعلام حيث يتم Index Scan أو فهرس متفاوت المسافات. فهرس متفاوت المسافات هو فهرس حيث يحتوي مستوى الفهرس النهائي على صفوف الجدول الفعلية. يشبه فهرس متفاوت المسافات أي فهرس آخر: يتم تعريفه في عمود واحد أو أكثر. أنها تشكل مفتاح الفهرس. مفتاح المجموعات هو أعمدة المفاتيح في فهرس متفاوت المسافات. يعد مسح فهرس متفاوت المسافات هو عملية قراءة قواعد بيانات قواعد البيانات لديك للصف أو الصفوف من أعلى إلى أسفل في فهرس متفاوت المسافات.

خذ بعين الاعتبار مثال الاستعلام التالي ، حيث يوجد فهرس لـ i_id والذي ينتج عنه عادةً تعقيد O (log (n)):

 SELECT i_stock FROM item WHERE i_id = N; 

لاحظ أنه بدون فهرس ، سيكون التعقيد الزمني هو O (n).

الوقت التربيعي: O (n ^ 2)

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

مثال محتمل لاستعلام تعقيد الوقت التربيعي هو ما يلي:

 SELECT * FROM item, author WHERE item.i_a_id=author.a_id 

قد يكون الحد الأدنى من التعقيد O (n log (n)) ، ولكن قد يكون الحد الأقصى للتعقيد O (n ^ 2) استنادًا إلى معلومات الفهرس الخاصة بسمات الاتصال.

للتلخيص ، يمكنك أيضًا إلقاء نظرة على ورقة الغش التالية لتقييم أداء الاستعلام بناءً على تعقيد الوقت وفعاليتها:

ضبط SQL

بالنظر إلى خطة تنفيذ الاستعلام والتعقيد الزمني ، يمكنك تخصيص استعلام SQL بشكل أكبر. يمكنك البدء بالتركيز على النقاط التالية:

• استبدال عمليات مسح الجدول الكامل غير الضرورية بمسح الفهرس ؛
• تأكد من تطبيق ترتيب الصلة الأمثل.
• تأكد من استخدام الفهارس على النحو الأمثل. و
• يتم استخدام التخزين المؤقت لمسح النص الكامل للجداول الصغيرة (التخزين المؤقت للجدول الصغير بمسح الجدول الكامل.).

مزيد من استخدام مزود

تهانينا! لقد وصلنا إلى نهاية هذه المقالة ، التي أعطيت للتو نظرة بسيطة على أداء استعلامات SQL. آمل أن يكون لديك المزيد من المعلومات حول مضادات الضبط ومحسن الاستعلام والأدوات التي يمكنك استخدامها لتحليل وتقييم وتفسير مدى تعقيد خطة الاستعلام الخاصة بك. ومع ذلك ، لا يزال لديك الكثير لتكتشفه! إذا كنت تريد معرفة المزيد ، اقرأ كتاب "أنظمة إدارة قواعد البيانات" بقلم ر. راماكريشنان وجيه غيرك.

أخيرًا ، لا أريد حرمانك من StackOverflow منك في هذا الاقتباس:

بلدي antipattern المفضلة لديك لا تحقق طلباتك.

ومع ذلك ، فإنه ينطبق عندما:

• يوفر الاستعلام الخاص بك أكثر من جدول.
• تعتقد أن لديك التصميم الأمثل للطلب ، لكن لا تحاول التحقق من افتراضاتك.
• أنت تقبل طلب العمل الأول ، ولا تعرف مدى قربه من المستوى الأمثل.