ضبط أداء قاعدة بيانات SQL للمطورين

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

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

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

في هذا المقال ، أود أن أنجز شيئين:

1. تزويد المطورين ببعض تقنيات ضبط أداء SQL من جانب المطور.
2. اشرح كيف يمكن للمطورين و DBAs العمل معًا بشكل فعال.

ضبط أداء SQL (في Codebase): الفهارس

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

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

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

الهدف الأساسي هو فهرسة أعمدة البحث والترتيب الرئيسية.

لاحظ أنه إذا كانت الجداول الخاصة بك تعمل باستمرار بواسطة INSERT و UPDATE و DELETE ، فيجب أن تكون حذرًا عند الفهرسة - فقد ينتهي بك الأمر إلى انخفاض الأداء حيث تحتاج جميع الفهارس إلى تعديل بعد هذه العمليات.

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

ضبط SQL: خطط التنفيذ في SQL Server

بالمناسبة: يمكن أن تكون أداة خطة التنفيذ في SQL Server مفيدة في إنشاء الفهارس.

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

لاسترداد خطة التنفيذ (في SQL Server Management Studio) ، ما عليك سوى النقر فوق "تضمين خطة التنفيذ الفعلية" (CTRL + M) قبل تشغيل الاستعلام.

بعد ذلك ، ستظهر علامة تبويب ثالثة باسم "خطة التنفيذ". قد ترى فهرسًا مفقودًا تم اكتشافه. لإنشائه ، ما عليك سوى النقر بزر الماوس الأيمن في خطة التنفيذ واختيار "تفاصيل الفهرس المفقودة ...". إنها بهذه السهولة!

( انقر للتكبير )

ضبط SQL: تجنب حلقات الترميز

تخيل سيناريو فيه 1000 استعلام يطرق قاعدة البيانات الخاصة بك في تسلسل. شيء مثل:

 for (int i = 0; i < 1000; i++) { SqlCommand cmd = new SqlCommand("INSERT INTO TBL (A,B,C) VALUES..."); cmd.ExecuteNonQuery(); }

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

 INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) -- SQL SERVER 2008 INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 -- SQL SERVER 2005 UPDATE TableName SET A = CASE B WHEN 1 THEN 'NEW VALUE' WHEN 2 THEN 'NEW VALUE 2' WHEN 3 THEN 'NEW VALUE 3' END WHERE B in (1,2,3)

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

 UPDATE TableName SET A = @VALUE WHERE B = 'YOUR CONDITION' AND A <> @VALUE -- VALIDATION

ضبط SQL: تجنب استعلامات SQL الفرعية المرتبطة

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

فيما يلي مثال على طلب بحث فرعي مرتبط:

 SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c

على وجه الخصوص ، تكمن المشكلة في أن الاستعلام الداخلي ( SELECT CompanyName… ) يتم تشغيله لكل صف يتم إرجاعه بواسطة الاستعلام الخارجي ( SELECT c.Name… ). ولكن لماذا مراجعة Company مرارًا وتكرارًا لكل صف تتم معالجته بواسطة الاستعلام الخارجي؟

تتمثل إحدى تقنيات ضبط أداء SQL الأكثر كفاءة في إعادة تشكيل الاستعلام الفرعي المرتبط كصلة:

 SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID

في هذه الحالة ، ننتقل إلى جدول Company مرة واحدة فقط ، في البداية ، وننضم إليه مع جدول Customer . من ذلك الحين فصاعدًا ، يمكننا تحديد القيم التي نحتاجها ( co.CompanyName ) بكفاءة أكبر.

ضبط SQL: حدد باعتدال

إحدى نصائح تحسين SQL المفضلة لدي هي تجنب SELECT * ! بدلاً من ذلك ، يجب عليك تضمين الأعمدة المحددة التي تحتاجها بشكل فردي. مرة أخرى ، يبدو هذا بسيطًا ، لكني أرى هذا الخطأ في كل مكان. ضع في اعتبارك جدولاً يحتوي على مئات الأعمدة وملايين الصفوف - إذا كان التطبيق الخاص بك يحتاج فقط إلى بضعة أعمدة ، فلا معنى للاستعلام عن جميع البيانات. إنه إهدار هائل للموارد. ( لمزيد من القضايا ، انظر هنا. )

علي سبيل المثال:

 SELECT * FROM Employees

ضد.

 SELECT FirstName, City, Country FROM Employees

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

 INSERT INTO Employees SELECT * FROM OldEmployees Msg 213, Level 16, State 1, Line 1 Insert Error: Column name or number of supplied values does not match table definition.

لتجنب هذا النوع من الخطأ من SQL Server ، يجب التصريح عن كل عمود على حدة:

 INSERT INTO Employees (FirstName, City, Country) SELECT Name, CityName, CountryName FROM OldEmployees

لاحظ ، مع ذلك ، أن هناك بعض المواقف التي يكون فيها استخدام SELECT * مناسبًا. على سبيل المثال ، مع الجداول المؤقتة - التي تقودنا إلى موضوعنا التالي.

ضبط SQL: الاستخدام الحكيم للجداول المؤقتة (#Temp)

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

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

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

القرار ليس دائمًا مباشرًا ، ولكن هذا المثال سيمنحك فكرة عن المواقف التي قد ترغب في استخدام الجداول المؤقتة فيها:

تخيل جدول عميل يحتوي على ملايين السجلات. يجب عليك الانضمام إلى منطقة معينة. يمكنك تحقيق ذلك باستخدام SELECT INTO ثم الانضمام إلى الجدول المؤقت:

 SELECT * INTO #Temp FROM Customer WHERE RegionID = 5 SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

( ملاحظة: يتجنب بعض مطوري SQL أيضًا استخدام SELECT INTO لإنشاء جداول مؤقتة ، قائلين إن هذا الأمر يغلق قاعدة بيانات tempdb ، ويمنع المستخدمين الآخرين من إنشاء جداول مؤقتة. لحسن الحظ ، تم إصلاح هذا في 7.0 وما بعده .)

كبديل للجداول المؤقتة ، يمكنك التفكير في استخدام استعلام فرعي كجدول:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

لكن انتظر! هناك مشكلة في هذا الاستعلام الثاني. كما هو موضح أعلاه ، يجب علينا فقط تضمين الأعمدة التي نحتاجها في طلب البحث الفرعي (على سبيل المثال ، عدم استخدام SELECT * ). مع الأخذ في الاعتبار:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

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

أخيرًا ، عند الانتهاء من الجدول المؤقت الخاص بك ، قم بحذفه لمسح موارد tempdb ، بدلاً من مجرد الانتظار حتى يتم حذفه تلقائيًا (كما سيكون عند إنهاء اتصالك بقاعدة البيانات):

 DROP TABLE #temp

ضبط SQL: "هل سجلي موجود؟"

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

 IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0 PRINT 'YES'

ضد.

 IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') PRINT 'YES'

ضبط أداء SQL باستخدام SQL Server 2016

كما يعلم مسؤولو قواعد البيانات الذين يعملون مع SQL Server 2016 على الأرجح ، فإن الإصدار يمثل تحولًا مهمًا في الإعدادات الافتراضية وإدارة التوافق. كإصدار رئيسي ، يأتي بالطبع مع تحسينات استعلام جديدة ، ولكن التحكم في ما إذا كان يتم استخدامها الآن مبسط عبر sys.databases.compatibility_level .

ضبط أداء SQL (في المكتب)

غالبًا ما يتعارض مسؤولو قاعدة بيانات SQL (DBA) والمطورون حول المشكلات المتعلقة بالبيانات وغير المتعلقة بالبيانات. مستمدًا من تجربتي ، إليك بعض النصائح (لكلا الطرفين) حول كيفية التعايش والعمل معًا بشكل فعال.

سقسقة

تحسين قواعد البيانات للمطورين:

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

2. حتى لو كنت مصمم بيانات نينجا SQL ، اطلب من مسؤول قواعد البيانات مساعدتك في الرسم التخطيطي العلائقي. لديهم الكثير لمشاركته وعرضه.

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

4. لا تطلب من SQL DBAs إجراء تغييرات على البيانات في بيئة الإنتاج. إذا كنت تريد الوصول إلى قاعدة بيانات الإنتاج ، فيجب أن تكون مسؤولاً عن جميع التغييرات الخاصة بك.

تحسين قاعدة البيانات لـ SQL Server DBAs:

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

2. مساعدة المطورين في بيئة اختبار / ضمان الجودة. اجعل من السهل محاكاة خادم إنتاج باختبارات بسيطة على بيانات العالم الحقيقي. سيكون هذا بمثابة توفير كبير للوقت للآخرين وكذلك لك.

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

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