تاريخ واحد MySQL الأمثل

سيكون حول التحسين في قاعدة بيانات MySQL.

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

1. اجمع خطابًا من html ، بيانات شخصية بديلة.
2. أضف رسالة تعرض البكسل ، واستبدل جميع الروابط في الرسالة برقمك الخاص - لتتبع النقرات.
3. تحقق قبل إرسال أن البريد الإلكتروني ليس في القائمة السوداء.
4. إرسال بريد إلكتروني إلى تجمع معين.

سأخبركم أكثر عن الفقرة الثانية:
يقوم Microservice mail-builder بإعداد خطاب لإرساله:

• يجد جميع الروابط في الرسالة.
• يتم إنشاء uuid فريد من 32 حرفًا لكل رابط ؛
• يستبدل الرابط الأصلي بواحد جديد ويحفظ البيانات في قاعدة البيانات.

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

المشكلة رقم 1

جيل من uuid فينا يعتمد على الطابع الزمني.

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

لقد حللنا هذه المشكلة باستخدام وحدة uuid في بيثون ، حيث لا يوجد اعتماد للوقت.
مثل هذا الشيء الضمني خفض سرعة الفهارس.

كيف يجري التخزين؟

كان هيكل الجدول على النحو التالي:

CREATE TABLE IF NOT EXISTS `Messages` ( `UUID` varchar(32) NOT NULL, `Message` json NOT NULL, `Inserted` DATE NOT NULL, PRIMARY KEY (`UUID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

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

كل شيء كان رائعا حتى نمت الجدول.

المشكلة رقم 2

إذا قرأت المزيد عن فهرس الكتلة ، فيمكنك معرفة هذا الفارق الدقيق:

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

وبالتالي ، مع زيادة الحمل ، زاد وقت الإدراج.

كان الحل هو استخدام هيكل طاولة مختلف.

 CREATE TABLE IF NOT EXISTS `Messages` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `UUID` varchar(32) NOT NULL, `Message` json NOT NULL, `Inserted` DATE NOT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `UUID` (`UUID`, `Inserted`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

نظرًا لأن المفتاح الأساسي الآن يزداد تلقائيًا ، ويقوم mysql بتخزين ذاكرة التخزين المؤقت الخاصة بمكان الإدراج الأخير ، والآن يحدث الإدراج دائمًا في النهاية ، على سبيل المثال ، تم تحسين Innodb لكتابة قيم متسلسلة متزايدة.

لقد وجدت تفاصيل هذا التحسين في شفرة مصدر postgres. Mysql تنفذ التحسين مماثلة جدا.
بالطبع ، اضطررت إلى إضافة مفتاح فريد بحيث لا توجد تعارضات ، لكننا قمنا بزيادة سرعة الإدراج.

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

لذلك ، قررنا تقسيم طاولتنا إلى أقسام.
يوم واحد - قسم واحد ، والقوائم القديمة تسقط تلقائيًا عندما يحين الوقت.

المشكلة رقم 3

لقد أتيحت لنا الفرصة لحذف البيانات القديمة ، لكننا لم نحصل على فرصة للاختيار من القسم المطلوب ، لأنه مع select`e نحدد uuid فقط ، mysql لا يعرف القسم الذي يجب أن نبحث عنه ويبحث فيه بالكامل.

وُلِد الحل من المشكلة رقم 1 - أضف طابعًا زمنيًا إلى معرف المستخدم المولد. هذه المرة فقط فعلنا بشكل مختلف قليلاً: لقد أدخلنا الطابع الزمني في مكان عشوائي على الخط ، وليس في البداية أو في النهاية ؛ قبل وبعد أن أضفوا رمز شرطة حتى يمكن الحصول عليها بتعبير منتظم.

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

أيضًا ، بفضل أشياء مثل ROW_FORMAT = مضغوط وتغيير الترميز إلى latin1 ، وفرنا مساحة أكبر على القرص الصلب.