بعض الممارسات لتحسين قاعدة بيانات MySQL على جهاز عالمي واحد

يوجد الكثير يمكن قوله عن تحسين قاعدة البيانات، يمكن تقسيمه بناءً على كمية البيانات الداعمة إلى مرحلتين: قاعدة البيانات المركزية ومشاركة الجداول، حيث يمكن أن يدعم الأول ما يصل إلى 5 مليون أو 10 جيجابايت من البيانات، وعندما يتجاوز هذا الرقم يجب النظر في مشاركة الجداول. بالإضافة إلى ذلك، غالبًا ما تبدأ الشركات الكبيرة في المقابلات بأسئلة حول قاعدة البيانات المركزية، وتتدرج إلى مشاركة الجداول، حيث ت穿插 العديد من الأسئلة المتعلقة بتحسين قاعدة البيانات. يهدف هذا المقال إلى وصف بعض الممارسات في تحسين قاعدة البيانات المركزية، القائمة على MySQL، ويُطلب منكم تصحيح أي شيء غير معقول.

1、تحسين بنية الجداول

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

1.1、النظام الحالي للوحدات

عادةً نحاول اختيار UTF-8، لأن استخدام GBK يقلل من مساحة التخزين عند حفظ البيانات، ولكن UTF-8 يتوافق مع لغات مختلفة، وبالتالي لا نحتاج للتضحية بتلك مساحة التخزين من أجل توسيع المرونة. في الواقع، إذا كان من الضروري تحويل GBK إلى UTF-8 في وقت لاحق، فإن التكلفة المرتفعة كبيرة، ويجب إجراء تحويل البيانات، ويمكن استخدام توسيع القرص لت扩يع مساحة التخزين.

1.2、主键

在使用mysql的innodb时，innodb的底层存储模型是B+树，它使用主键作为聚簇索引，使用插入的数据作为叶子节点，通过主键可以很快找到叶子节点，从而快速获取记录。因此在设计表的时候需要增加一个主键，而且最好要自增。因为自增主键可以让插入的数据按主键顺序插入到底层的B+树的叶子节点中，由于是按序的，这种插入几乎不需要去移动已有的其它数据，所以插入效率很高。如果主键不是自增的，那么每次主键的值近似随机，这时候就有可能需要移动大量数据来保证B+树的特性，增加了不必要的开销。

1.3、字段

1.3.1、建立索引的字段必须加上not null约束，并且设置default值

1.3.2、不建议使用float、double来存小数，防止精度损失，建议使用decimal

1.3.3、不建议使用Text/blob来保存大量数据，因为对大文本的读写会造成比较大的I/O开销，同时占用mysql的缓存，高并发下会极大的降低数据库的吞吐量，建议将大文本数据保存在专门的文件存储系统中，mysql中只保存这个文件的访问地址，比如博客文章可以保存在文件中，mysql中只保存文件的相对地址。

1.3.4、varchar类型长度建议不要超过8K。

1.3.5、时间类型建议使用Datetime，不要使用timestamp，虽然Datetime占用8个字节，而timestamp只占用4个字节，但是后者要保证非空，而且后者是对时区敏感的。

1.3.6、建议表中增加gmt_create和gmt_modified两个字段，用来记录数据创建的修改时间。这两个字段建立的原因是方便查问题。

1.4、创建索引

1.4.1、这个阶段由于对业务并不了解，所以尽量不要盲目加索引，只为一些一定会用到索引的字段加普通索引。

1.4.2、创建innodb单列索引的长度不要超过767bytes，如果超过会用前255bytes作为前缀索引

1.4.3、创建innodb组合索引的各列索引长度不要超过767bytes，总共不要超过3072bytes

2、优化SQL

一般来说SQL就那么几种：基本的增删改查，分页查询，范围查询，模糊搜索，多表连接

2.1、查询基本

عادةً يجب أن يمر البحث عبر المؤشر، إذا لم يكن هناك مؤشر، يُنصح بتعديل البحث وإضافة الحقل الذي يحتوي على المؤشر، وإذا لم يكن من الممكن استخدام هذا الحقل بسبب سيناريو العمل، يجب النظر في كمية الاستدعاءات للبحث، إذا كانت كبيرة، مثل 10W+ استدعاءات يوميًا، فإنه يجب إنشاء مؤشر جديد، وإذا كانت صغيرة، مثل 100+ استدعاءات يوميًا، يمكن النظر في الحفاظ على النسخة الأصلية. بالإضافة إلى ذلك، يُنصح بتجنب استخدام select *، وإضافة الحقول المطلوبة فقط في جملة SQL، ودون الحقول غير الضرورية، لأن ذلك يستهلك موارد I/O وذاكرة الوصول العشوائي.

2.2، التصفح الفعال

limit m,n في الواقع يعني أولاً تنفيذ limit m+n، ثم أخذ n سطرًا من السطر m، لذا عندما يتم التصفح باستخدام limit، يزيد m مع زيادة التصفح، مما يؤدي إلى انخفاض الأداء. على سبيل المثال

select * from A limit 100000,10،أداء هذه الجملة SQL سيكون ضعيفًا، يُنصح بتحويلها إلى النسخة التالية:

selec id,name,age from A where id >=(select id from A limit 100000,1) limit 10

2.3، البحث بنطاق

البحث بنطاق يشمل between، أكبر من، أصغر من، وin. هناك تحديد لحجم الشروط في استعلام in في mysql، إذا كان عدد الشروط صغيرًا يمكنه المرور عبر المؤشر، ولكن إذا كان كبيرًا، فإنه يصبح مسحًا كاملاً للجدول. بينما لا تمر هذه البحث بين بين، أكبر من، أصغر من، عبر المؤشر، لذا يُنصح بوضعها بعد الشروط التي يمكنها المرور عبر المؤشر.

2.4، البحث المضارب like

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

2.5، ربط جداول متعددة

الاستعلامات الفرعية والانضمام يمكنهما تنفيذ سحب البيانات بين جداول متعددة، ولكن أداء الاستعلام الفرعي ضعيف، يُنصح بتحويل الاستعلام الفرعي إلى انضمام. بالنسبة لـ mysql الانضمام، يستخدم خوارزمية Nested Loop Join، أي من خلال نتائج مجموعة الاستعلام السابقة للبحث في الجدول التالي، مثل إذا كانت نتائج مجموعة الاستعلام السابقة 100 سطر، وكان الجدول التالي يحتوي على 100000 سطر، فإنه سيكون من الضروري البحث في مجموعة بيانات تتكون من 100 * 100000 سطر للحصول على مجموعة النتائج النهائية. لذلك، يُنصح باستخدام جداول الصغيرة النطاق مع الجداول الكبيرة في عملية الانضمام، وإنشاء مؤشرات على أماكن الانضمام، وإذا لم يتمكن من إنشاء المؤشر، فإنه يجب إعداد حجم كبير لـ join buffer size. إذا لم تنجح هذه التقنيات في حل مشكلة انخفاض أداء الانضمام، فإنه يُفضل عدم استخدام الانضمام على الإطلاق، وإنقسام استعلام الانضمام إلى استعلامين بسيطين. بالإضافة إلى ذلك، يُنصح بتجنب ربط أكثر من ثلاثة جداول، لأن أداء أكثر من ثلاثة جداول عادةً سيكون ضعيفًا، ويُنصح بفصل الجملة SQL.

3، تحسين حوض اتصالات قاعدة البيانات

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

3.1، initialSize

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

3.2، minIdle

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

3.3، maxIdle

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

3.4، maxActive

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

3.5، maxWait

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

3.6، minEvictableIdleTimeMillis

فترة الزمنية التي يتم فيها الحفاظ على الاتصال الفارغ دون إزالته، بناءً على القيمة الافتراضية 30 دقيقة.

3.7، validationQuery

استخدام جملة SQL لتحقق من صحة الاتصال، عادةً ما تكون جملة بسيطة، يُنصح بتعيينها.

3.8، testOnBorrow

التحقق من الاتصال عند طلب الاتصال، لا يُنصح بتمكينه، حيث يؤثر بشكل كبير على الأداء.

3.9، testOnReturn

التحقق من الاتصال عند استعادته، لا يُنصح بتمكينه، حيث يؤثر بشكل كبير على الأداء.

3.10، testWhileIdle

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

3.11، numTestsPerEvictionRun

تمثل عدد الفحوصات التي يتم إجراؤها في كل مرة، يُنصح بتعيين نفس القيمة لـ maxActive، حيث يمكن فحص جميع الاتصالات في كل مرة.

3.12، تسخين خزان الاتصالات

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

4، تحسين الفهرس

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

4.1، فهرس رئيسي

إنشاء فهرس على الشروط التالية في where، يمكن إنشاء فهرس عادي لسطر واحد، والفهرس المركب للعديد من الأعمدة. يجب الانتباه لأصل الأحرف الأكثر أهمية في الفهرس المركب.

4.2، فهرس ثانوي

إذا كان هناك مجال يستخدم في order by أو group by، فيمكن النظر في إنشاء فهرس في هذا المجال، حيث يمكن لتسلسل الفهرس الطبيعي تجنب ترتيب order by و group by، مما يزيد من الأداء.

4.3، فهرس ثلاثي

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

4.4. اختيار الفهرسة

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

5. أرشيف البيانات التاريخية

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

هذا هو جمع المواد حول تحسين قاعدة بيانات MySQL المحلية، وسنواصل إضافة المزيد من المواد ذات الصلة، شكراً لكم على دعم هذا الموقع!

البيان: محتوى هذا المقال تم جمعه من الإنترنت، ويتمتع صاحب الحقوق بالملكية، ويتم جمع المحتوى من قبل المستخدمين عبر الإنترنت الذين يقدمون المساهمات بشكل تلقائي، ويستخدم هذا الموقع ليس لديه حقوق الملكية، ولا يتم تعديل المحتوى بشكل يدوي، ولا يتحمل هذا الموقع أي مسؤولية قانونية ذات صلة. إذا كنت قد وجدت محتوى يشتبه في انتهاك حقوق النسخ، فنرجو منك إرسال بريد إلكتروني إلى: notice#oldtoolbag.com (عند إرسال البريد الإلكتروني، يرجى استبدال # بـ @) لإبلاغنا، وتقديم الدليل على ذلك، وإذا تم التحقق من ذلك، فإن هذا الموقع سيقوم بإزالة المحتوى المشبوه فوراً.