الأصدقاء ، في نهاية يناير ، سنبدأ دورة تدريبية جديدة تسمى "MS SQL Server Developer ". تحسبا لإطلاقه ، طلبنا من معلمة الدورة ، كريستينا كوشروفا ، إعداد مقال للمؤلف. ستكون هذه المقالة مفيدة لك إذا كان لديك جدول شائع جدًا على المنتج مع وصول 24/7 وفجأة تدرك أنك بحاجة ماسة إلى إضافة فهرس وعدم كسر أي شيء في هذه العملية.ماذا تفعل؟ طريقة إنشاء CREATE INDEX WITH (ONLINE = ON) التقليدية ليست مناسبة لك ، لأنه على سبيل المثال ، يتسبب في تعطل النظام ونوبة قلبية من DBA الخاص بك ، وجميع القمم تراقب عن كثب وقت استجابة النظام الخاص بك ، وإذا زاد ، فإنها تأتي إليك و DBA الخاص بك للحديث بخصوص الأرقام المبالغة في تقدير تعويضك عن العمل.
تم استخدام البرامج النصية والتقنيات الموصوفة على نظام مع حمولة من طلبات 400K في الدقيقة ، إصدارات SQL Server 2012 و 2016 (Enterprise).
هناك طريقتان مختلفتان جدًا لإنشاء فهرس ، يتم استخدامهما وفقًا لحجم الجدول.
القضية رقم 1. طاولة صغيرة ولكنها تحظى بشعبية كبيرة
جدول يحتوي على 50 ألف سجل (صغير) ، لكنه شائع جدًا (عدة آلاف من الزيارات في الدقيقة). تحتاج إلى فهرس جديد والحد الأدنى من الوقت الضائع والأقفال على الطاولة.
في التطبيق ، كل الوصول إلى قاعدة البيانات هو فقط من خلال الإجراءات.
في حالة حدوث خطأ ، سيقوم التطبيق بإعادة محاولة الوصول إلى الجدول.
ما هي مشكلة تطبيق هذا المؤشر ببساطة ، تسأل؟ مع الجملة WITH ONLINE = ON (نعم ، كنا محظوظين ، وكان هذا واحد Enterprise).
والحقيقة هي أنه مع هذا الوصول النشط ، يستغرق الأمر بعض الوقت للحصول على قفل (حتى الحد الأدنى المطلوب مع خيار on = on Online). في طور الانتظار ، يتم وضع قائمة انتظار للطلبات الجديدة ، تتراكم قائمة الانتظار ، وحدة المعالجة المركزية (CPU) في نمو ، و DBA تتعرق وتتدفق بعصبية تجاه المطورين ، بينما في وقت مراقبة التطبيق ، يبدأ وقت الاستجابة في الزيادة بسلاسة ، ولكن لا محالة. يهتم نائب رئيس مهندسي الهندسة بشغف كبير فيما إذا كان ، نظرًا لهذه الزيادة في وقت الاستجابة ، سيكون هناك نوع من تعطل النظام ، في نهاية العام ، سيُقدر توفر التطبيق بـ 5 تسع (99،999) ، ولكن أقل؟ ومن ثم فإن الشركة لديها عقود والتزامات وغرامات باهظة في حالة انخفاض توافرها ، وبالطبع ، لن ننسى خسائر السمعة.
ماذا فعلنا لتجنب هذا الوضع المؤسف؟
لا يزال النظام بحاجة إلى فهرس.
لقد أخذوا الحقوق من الجميع باستثناء الجلسة الحالية في هذا الجدول.
تطبيق الفهرس.
نعم ، يحتوي الحل على ناقص: كل من تحول إلى الطاولة في هذه الثواني سيتلقى وصول مرفوض. إذا كان التطبيق الخاص بك يعالج عادة مثل هذا الموقف ويكرر الاستعلام إلى قاعدة البيانات ، فعليك إلقاء نظرة فاحصة على هذا الخيار. في حالة مشروعنا ، عملت هذه الطريقة بشكل جيد. مرة أخرى ، يمكنك إزالة ONLINE = ON بأمان ، نظرًا لأننا نعلم أن الجلسة فقط ستتمكن من الوصول إلى الجدول أثناء إنشاء الفهرس.
رمز لتطبيق الفهرس:
REVOKE EXECUTE ON [dbo].[spUserLogin] TO [User1] REVOKE EXECUTE ON [dbo].[spUserLogin] TO [User2] REVOKE EXECUTE ON [dbo].[spUserCreate] TO [User1] REVOKE EXECUTE ON [dbo].[spUserCreate] TO [User2] CREATE NONCLUSTERED INDEX IX_Users_Email_Status ON [dbo].[Users] ([Email],[Status]); GRANT EXECUTE ON [dbo].[spUserCreate] TO [User1] GRANT EXECUTE ON [dbo].[spUserCreate] TO [User2] GRANT EXECUTE ON [dbo].[spUserLogin] TO [User1] GRANT EXECUTE ON [dbo].[spUserLogin] TO [User2]
جدول زمن الاستجابة ونسبة الأخطاء أثناء الاختبار تحت الحمل.
يمكن تطبيق هذه الطريقة إذا كان لديك ، كما في الحالة الموضحة ، جدول صغير ، وأنت تعلم أنه بدون تحميل ، سيتم إنشاء الفهرس في ثوان (أو في وقت مقبول بالنسبة لك). في الوقت نفسه ، كما ترون من الرسم البياني أعلاه ، لن يزداد وقت استجابة التطبيق ، على الرغم من أنه يمكن ملاحظة أن معدل الخطأ في ثوانٍ دون الوصول إلى الجدول كان أعلى.
القضية رقم 2. طاولة كبيرة
إذا كان لديك جدول كبير وتحتاج إلى تغيير الفهارس الموجودة عليه ، فغالبًا ما تكون الطريقة الأكثر مؤلمة للبيع هي إنشاء جدول بجانبه باستخدام الفهرس الصحيح ونقل البيانات تدريجياً إلى جدول جديد.
هناك طريقتان:
- إذا كان لديك إجراء خاص لتعديل جدول ، فيمكنك ببساطة تغيير رمز الإجراء بحيث يتم إدراج البيانات الجديدة فقط في الجدول الجديد ، والحذف من كليهما ، كما تم تطبيق التحديث على كليهما ، وتم إجراء التحديد من جدولين باستخدام UNION ALL.
- إذا كان لديك العديد من أجزاء التعليمات البرمجية المختلفة حيث يمكنك تغيير البيانات في الجدول ، فهناك حيلتان شائعتان: العرض باستخدام المشغلات أو إعادة كتابة جميع أجزاء التعليمات البرمجية لإدراج البيانات في جدول جديد ، وحذفها من كلا الجدولين وتحديثها. طريقة العرض مع المشغلات هي خيار عندما تقوم بإنشاء طريقة عرض مع جدولين وإعادة تسميته ، وإعادة تسمية الجدول الحالي إلى TableOld ، والعرض إلى جدول. ثم تحصل تلقائيًا على جميع مكالمات الجدول إلى العرض ، وهنا مع إعادة التسمية ، قد تكون هناك مشكلة أيضًا ، لأن SchemaLock مطلوبة ، لكن إعادة التسمية تمر بسرعة كبيرة.
إصدار أكثر تفصيلاً حول إعادة كتابة المكالمات إلى جدول جديد:
- لديك جدول Orders (الطلبيات) ، قم بإنشاء جدول Orders (جديد) جديد بنفس المخطط ، لكن باستخدام الفهرس المطلوب. في نفس الوقت ، إذا كنت تستخدم المسافة البادئة ، فأنت بحاجة إلى تعيين القيمة الأولى للهوية في الجدول الجديد لتكون مساوية للحد الأقصى للقيمة في الجدول القديم + خطوة التغيير أو الفجوة التي يمكنك تحملها للانحراف عن الحد الأقصى للقيمة في الطلبات.
- إنشاء OrdersView ، داخله تحديد من Orders UNION ALL OrdersNew
- قم بتغيير جميع الإجراءات / المكالمات لتحديد البيانات من العرض ، وأدخلها في OrdersNew ، وحذف وتعديل كلا الجدولين.
- قم بترحيل البيانات من الجدول القديم إلى الجدول الجديد ، على سبيل المثال ، مثل هذا:
DECLARE @rowcount INT, @batchsize INT = 4999; SET IDENTITY_INSERT dbo.OrdersNew ON; SET @rowcount = @batchsize; WHILE @rowcount = @batchsize BEGIN BEGIN TRY DELETE TOP (@batchsize) FROM dbo.Orders OUTPUT deleted.Id ,deleted.Column1 ,deleted.Column2 ,deleted.Column3 INTO dbo.OrdersNew (Id ,Column1 ,Column2 ,Column3); SET @rowcount = @@ROWCOUNT; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; THROW; END CATCH; END; SET IDENTITY_INSERT dbo.OrdersNew OFF;
- أعد جميع الإجراءات إلى الإصدار قبل الترحيل - مع جدول واحد. يمكن القيام بذلك من خلال تغيير أو من خلال إزالة وإنشاء الإجراءات (ثم لا تنسى الحقوق) ، ويمكنك إعادة تسمية الجدول الجديد إلى الطلبات ، وحذف الجدول الفارغ وطريقة العرض.
في الخطوة 2 ، كان من الممكن ، إذا سمح التحميل ، إعادة تسمية الجدول الرئيسي Orders -> OrdersOld و OrdersView -> Orders وطريقة العرض نفسها ل OrdersOld UNION ALL OrdersNew ، فلن تحتاج إلى تغيير جميع الأماكن التي يوجد فيها تحديد من الجدول.
عند نقل الكتل من جدول إلى آخر ، سيتم تجزئة البيانات.
إذا تم استخدام الجدول الجاري تغييره بنشاط للقراءة ، ولكن نادراً ما تتغير البيانات الموجودة فيه ، فيمكنك مرة أخرى استخدام المشغلات - كتابة نسخة من جميع التغييرات إلى الجدول الثالث - نقل البيانات من الجدول عبر bcp out و bcp في (أو الإدراج المجمع) إلى جدول جديد ، قم بإنشاء فهارس عليه بعد نقل البيانات ثم قم بتطبيق التغييرات من الجدول باستخدام سجل التغيير - وقم بالتبديل بين جدول وآخر - الجدول الحالي ، وإعادة تسميته إلى TableOld ، والجدول الجديد من TableNew إلى Table.
احتمال حدوث أخطاء في هذا الموقف أعلى قليلاً ، لذلك اختبر تطبيق التغييرات وحالات التبديل المختلفة في هذه الحالة.
الخيارات الموصوفة ليست هي الوحيدة. تم استخدامها من قبلي على قاعدة بيانات SQL Server محملة بشكل كبير ولم تسبب مشاكل أثناء التطبيق ، مما يسر فريق DBA لدينا. عادةً ما لا يكون هذا الارتداد ضروريًا لقواعد ذات وضع حمل أكثر هدوءًا ، عندما يمكنك تطبيق التغييرات بأمان في ساعات النشاط الأقل. يوجد مستخدمو المشروع الذي استخدم الأساليب الموصوفة في الولايات المتحدة الأمريكية وأوروبا ويستخدمون التطبيق بنشاط في أيام الأسبوع وعطلات نهاية الأسبوع ، ويتم استخدام الجداول التي تم تطبيق التغييرات عليها باستمرار في العمل. عادة ما يتم تعديل المزيد من الكائنات "الأكثر هدوءًا" عن طريق البرامج النصية التلقائية التي يتم إنشاؤها من خلال Redgate Toolkit بعد مراجعة النصوص من قبل المطور وواحد من DBAs.
جيد للجميع! شارك في التعليقات إذا كنت تستخدم أيًا من هذه الطرق أو تصف طريقتك! ندعوك أيضًا إلى درس مفتوح ويوم مفتوح من الدورة التدريبية الجديدة "MS SQL Server Developer"