تقدم هذه المقالة استراتيجيات MySQL لتحسين كفاءة عمليات تحميل البيانات. في كثير من الأحيان تكون مهتمًا بتحسين استعلامات SELECT لأنها أكثر الاستعلامات استخدامًا، ولا يكون تحديد كيفية تحسينها أمرًا سهلاً دائمًا. يعد تحميل البيانات إلى قاعدة بيانات أمرًا بسيطًا نسبيًا. في كثير من الأحيان تكون مهتمًا بتحسين استعلامات SELECT لأنها أكثر الاستعلامات استخدامًا، ولا يكون تحديد كيفية تحسينها أمرًا سهلاً دائمًا. يعد تحميل البيانات إلى قاعدة بيانات أمرًا بسيطًا نسبيًا. ومع ذلك، هناك استراتيجيات يمكن استخدامها لتحسين كفاءة عمليات تحميل البيانات، والمبادئ الأساسية لها هي كما يلي:
يكون التحميل المجمع أسرع من تحميل الصف الواحد لأن ذاكرة التخزين المؤقت للفهرس لا تحتاج إلى مسحها بعد تحميل كل سجل؛ ويمكن مسحها بعد تحميل مجموعة السجلات.
يعد تحميل جدول بدون فهرس أسرع من التحميل بعد الفهرس. إذا كانت هناك فهارس، فلا يجب إضافة السجلات إلى ملفات البيانات فحسب، بل يجب تعديل كل فهرس ليعكس إضافة السجلات الجديدة.
تعد عبارات SQL الأقصر أسرع من عبارات SQL الأطول لأنها تتضمن تحليلًا أقل من جانب الخادم ولأنها أسرع في الإرسال عبر الشبكة من العميل إلى الخادم. قد تبدو بعض هذه العوامل تافهة (خاصة العامل الأخير)، ولكن إذا كنت تقوم بتحميل كمية كبيرة من البيانات، فحتى العوامل الصغيرة يمكن أن تحدث فرقًا كبيرًا في النتائج. يمكننا استخدام المبادئ العامة المذكورة أعلاه لاستخلاص عدة استنتاجات عملية حول كيفية تحميل البيانات بشكل أسرع:
يعد تحميل البيانات (بجميع أشكاله) أكثر كفاءة من INSERT لأنه يقوم بتحميل الصفوف على دفعات. تكون عمليات تحديث الفهرس أقل، ويتعين على الخادم فقط تحليل وتفسير عبارة واحدة بدلاً من عدة عبارات.
يعد تحميل البيانات أكثر كفاءة من تحميل البيانات المحلية. باستخدام LOAD DATA، يجب أن يكون الملف موجودًا على الخادم ويجب أن يكون لديه أذونات FILE، ولكن يمكن للخادم قراءة الملف مباشرة من القرص. باستخدام LOAD DATA LOCAL، يقرأ العميل الملف ويرسله عبر الشبكة إلى الخادم، وهو أمر بطيء.
إذا كان يجب عليك استخدام INSERT، فيجب عليك استخدام نموذج يسمح بتحديد صفوف متعددة في عبارة واحدة، مثل:
كلما زاد عدد الصفوف التي يمكنك تحديدها في العبارة، كلما كان ذلك أفضل. يؤدي هذا إلى تقليل عدد العبارات المطلوبة وتقليل مقدار تحديثات الفهرس. إذا كنت تستخدم mysqldump لإنشاء ملف نسخة احتياطية لقاعدة البيانات، فيجب عليك استخدام خيار --extensive-insert بحيث يحتوي ملف التفريغ على عبارات INSERT متعددة الأسطر. يمكنك أيضًا استخدام --opt (التحسين)، الذي يمكّن خيار --extensive-insert. على العكس من ذلك، يجب تجنب استخدام خيار --complete-insert لـ mysqldump؛ يؤدي هذا الخيار إلى أن تكون عبارات INSERT ذات سطر واحد، وتستغرق وقتًا أطول في التنفيذ، وتتطلب تحليلًا أكثر من العبارات التي تم إنشاؤها بدون خيار --complete-insert.
استخدم بروتوكولات العميل/الخادم المضغوطة لتقليل حركة مرور بيانات الشبكة. بالنسبة لمعظم عملاء MySQL، يمكن تحديد ذلك باستخدام خيار سطر الأوامر --compress. يتم استخدامه بشكل عام فقط على الشبكات الأبطأ لأن الضغط يتطلب الكثير من وقت المعالج.
اسمح لـ MySQL بإدراج القيم الافتراضية، ولا تحدد الأعمدة في عبارة INSERT التي سيتم تعيين القيم الافتراضية لها بأي شكل من الأشكال. في المتوسط، يؤدي هذا إلى عبارات أقصر ويقلل عدد الأحرف المرسلة عبر الشبكة إلى الخادم. بالإضافة إلى ذلك، تتطلب البيانات التي تحتوي على قيم أقل تحليلًا وتحويلًا أقل بواسطة الخادم.
إذا تمت فهرسة الجدول، فيمكنك استخدام الإدخالات المجمعة (LOAD DATA أو عبارات INSERT متعددة الصفوف) لتقليل حمل الفهرس. وهذا يقلل من تأثير تحديثات الفهرس لأن الفهرس يحتاج فقط إلى التحديث عند معالجة كافة الصفوف، وليس بعد كل صف.
إذا كنت بحاجة إلى تحميل كمية كبيرة من البيانات في جدول جديد، فيجب عليك إنشاء الجدول وتحميله عندما لا يتم فهرسته، ثم إنشاء الفهرس بعد تحميل البيانات، وهذا أسرع. يعد إنشاء الفهرس مرة واحدة (بدلاً من تعديله مرة واحدة لكل صف) أسرع.
إذا قمت بإسقاط فهرس أو تعطيله قبل التحميل، فإن إعادة إنشاء الفهرس أو تمكينه بعد تحميل البيانات قد يؤدي إلى زيادة سرعة التحميل. إذا كنت تريد استخدام استراتيجية الحذف أو التعطيل لتحميل البيانات، فتأكد من إجراء بعض التجارب لمعرفة ما إذا كان الأمر يستحق ذلك (إذا كنت تقوم بتحميل كمية صغيرة من البيانات إلى جدول كبير، فقد تستغرق إعادة البناء والفهرسة وقتًا أطول من التحميل البيانات)).
يمكن استخدام DROP INDEX وCREATE INDEX لإسقاط الفهارس وإعادة بنائها. البديل هو تعطيل وتمكين الفهارس باستخدام myisamchk أو isamchk. يتطلب هذا حسابًا على مضيف خادم MySQL مع إمكانية الوصول للكتابة إلى ملفات الجدول. لتعطيل فهارس الجدول، أدخل دليل قاعدة البيانات المقابل وقم بتنفيذ أحد الأوامر التالية:
استخدم myisamchk لجداول MyISAM التي تحتوي على ملفات فهرس بامتداد .MYI، وisamchk لجداول ISAM التي تحتوي على ملفات فهرس بامتداد .ISM. بعد تحميل البيانات في الجدول، قم بتنشيط الفهرس كما يلي:
إذا قررت استخدام تعطيل الفهرس وتنشيطه، فيجب عليك استخدام بروتوكول قفل إصلاح الجدول الموضح في الفصل 13 لمنع الخادم من تغيير الأقفال في نفس الوقت (على الرغم من عدم إصلاح الجدول في هذا الوقت، إلا أنه يتم تعديله مثل الجدول) عملية الإصلاح، لذلك تحتاج إلى استخدام نفس بروتوكول القفل).
تنطبق مبادئ تحميل البيانات الموضحة أعلاه أيضًا على الاستعلامات الثابتة المتعلقة بالعملاء الذين يحتاجون إلى إجراء عمليات مختلفة. على سبيل المثال، تريد بشكل عام تجنب تشغيل استعلامات SELECT الطويلة على الجداول التي يتم تحديثها بشكل متكرر. يمكن أن تؤدي استعلامات SELECT طويلة الأمد إلى حدوث الكثير من التنافس وتقليل أداء الكاتب. أحد الحلول المحتملة هو تخزين السجلات في جدول مؤقت أولاً ثم إضافة السجلات بشكل دوري إلى الجدول الرئيسي إذا كانت عمليات الكتابة عبارة عن عمليات INSERT بشكل أساسي. وهذا ليس نهجا ممكنا إذا كان الوصول الفوري إلى السجلات الجديدة مطلوبا. ولكن يمكن استخدام هذه الطريقة طالما لم يتم الوصول إليها لفترة قصيرة من الزمن. هناك فائدتان لاستخدام الجداول المؤقتة. أولاً، فهو يقلل من التعارض مع عبارة استعلام SELECT في الجدول الرئيسي، وبالتالي يتم تنفيذه بشكل أسرع. ثانيًا، الوقت الإجمالي لتحميل السجلات من الجدول المؤقت إلى الجدول الرئيسي أقل من الوقت الإجمالي لتحميل السجلات بشكل منفصل؛ تحتاج ذاكرة التخزين المؤقت للفهرس المقابلة إلى التحديث فقط في نهاية كل تحميل دفعة، وليس بعد كل صف حمولة. أحد تطبيقات هذه الإستراتيجية هو الوصول إلى قاعدة بيانات MySQL من صفحة الويب الخاصة بخادم الويب. في هذا السيناريو، قد لا يكون هناك مستوى أعلى من السلطة يضمن الإدخال الفوري للسجل في الجدول الرئيسي.
إذا لم تكن البيانات هي بالضبط نوع السجل الفردي الذي سيتم إدراجه في حالة إيقاف تشغيل النظام، فهناك إستراتيجية أخرى لتقليل تحديثات الفهرس وهي استخدام خيار إنشاء الجدول DELAYED_KEY_WRITE لجداول MyISAM (والذي قد يكون ممكنًا إذا تم استخدام MySQL لـ بعض أعمال إدخال البيانات). يؤدي هذا الخيار إلى تحديث ذاكرة التخزين المؤقت للفهرس من حين لآخر فقط، وليس بعد كل عملية إدراج.
إذا كنت ترغب في الاستفادة من تأخر تحديث الفهرس على مستوى الخادم، فما عليك سوى تشغيل mysqld باستخدام خيار --delayed-key-write. في هذا السيناريو، يتم تأخير كتابة كتلة الفهرس حتى يجب مسح الكتلة لإفساح المجال لقيم الفهرس الأخرى، أو حتى يتم تنفيذ أمر جداول التدفق، أو حتى يتم إغلاق جدول الفهرس.
-