أفضل الممارسات لاستكشاف أخطاء قاعدة بيانات Azure ل MySQL وإصلاحها - الخادم المرن

ينطبق على:قاعدة بيانات Azure لـ MySQL - خادم فردي قاعدة بيانات Azure لـ MySQL - خادم مرن

هام

قاعدة بيانات Azure لخادم MySQL الفردي على مسار الإيقاف. نوصي بشدة بالترقية إلى قاعدة بيانات Azure لخادم MySQL المرن. لمزيد من المعلومات حول الترحيل إلى خادم Azure Database for MySQL المرن، راجع ما الذي يحدث لقاعدة بيانات Azure لخادم MySQL الفردي؟

استخدم الأقسام التالية للحفاظ على تشغيل قواعد بيانات خادم Azure Database for MySQL المرن بسلاسة واستخدام هذه المعلومات كمبادىء إرشادية لضمان تصميم المخططات على النحو الأمثل وتوفير أفضل أداء لتطبيقاتك.

التحقق من عدد الفهارس

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

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

إشعار

تذكر مراجعة الفهارس بشكل دوري وإجراء أي تحديثات ضرورية بناءً على أي تعديلات على بيانات الجدول.

SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name;

(أو)

use information_schema; select tables.table_name, statistics.index_name, statistics.cardinality, tables.table_rows from tables join statistics on (statistics.table_name = tables.table_name and statistics.table_schema = '<YOUR DATABASE NAME HERE>' and ((tables.table_rows / statistics.cardinality) > 1000));

سرد الفهارس الأكثر ازدحاما على الخادم

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

SELECT 
object_schema AS table_schema, 
object_name AS table_name, 
index_name, count_star AS all_accesses, 
count_read, 
count_write, 
Concat(Truncate(count_read / count_star * 100, 0), ':', 
Truncate(count_write / count_star * 100, 0)) AS read_write_ratio, 
 count_fetch AS rows_selected , 
 count_insert AS rows_inserted, 
 count_update AS rows_updated, 
 count_delete AS rows_deleted, 
 Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency , 
 Concat(Round(sum_timer_fetch / 1000000000000, 2), ' s') AS select_latency, 
 Concat(Round(sum_timer_insert / 1000000000000, 2), ' s') AS insert_latency, 
Concat(Round(sum_timer_update / 1000000000000, 2), ' s') AS update_latency, 
 Concat(Round(sum_timer_delete / 1000000000000, 2), ' s') AS  delete_latency 
FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE index_name IS NOT NULL AND count_star > 0 
ORDER BY sum_timer_wait DESC 

مراجعة تصميم المفتاح الأساسي

يستخدم خادم Azure Database for MySQL المرن محرك تخزين InnoDB لجميع الجداول غير النظامية. باستخدام InnoDB، يتم تخزين البيانات داخل فهرس مجموعة باستخدام بنية B-Tree. يتم تنظيم الجدول فعليًا بناءً على قيم المفتاح الأساسي، ما يعني أن الصفوف مخزنة في ترتيب المفتاح الأساسي.

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

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

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

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

إشعار

لمزيد من المعلومات حول أنواع البيانات وقيمها القصوى، في الدليل المرجعي MySQL، راجع أنواع البيانات.

استخدام فهارس التغطية

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

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

ضع في اعتبارك، على سبيل المثال، جدولًا تستخدمه لمحاولة العثور على جميع الموظفين الذين انضموا إلى الشركة بعد 1 يناير 2000.

mysql> show create table employee\G
*************************** 1. row ***************************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `empid` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(10) DEFAULT NULL,
  `lname` varchar(10) DEFAULT NULL,
  `joindate` datetime DEFAULT NULL,
  `department` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`empid`)
  ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)`

`mysql> select empid, fname, lname from employee where joindate > '2000-01-01';

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

mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

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

mysql> CREATE INDEX cvg_idx_ex ON employee (joindate, empid, fname, lname);

الآن، إذا قمت بتشغيل خطة EXPLAIN على نفس الاستعلام، فستظهر قيمة "استخدام الفهرس" في حقل "إضافي"، مما يعني أن InnoDB ينفذ الاستعلام باستخدام الفهرس الذي أنشأناه سابقًا، مما يؤكد ذلك على أنه فهرس تغطية.

mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: range
possible_keys: cvg_idx_ex
          key: cvg_idx_ex
      key_len: 6
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)

إشعار

من المهم اختيار الترتيب الصحيح للأعمدة في فهرس التغطية لخدمة الاستعلام بشكل صحيح. تتمثل القاعدة العامة في اختيار الأعمدة للتصفية أولاً (عبارة WHERE)، ثم الفرز/التجميع (ORDER BY وGROUP BY) وأخيرا إسقاط البيانات (SELECT).

من المثال السابق، رأينا أن وجود فهرس تغطية للاستعلام يوفر مسارات استرداد سجلات أكثر كفاءة ويحسن الأداء في بيئة قاعدة بيانات متزامنة للغاية.

الخطوات التالية

للعثور على إجابات نظير لأسئلتك الأكثر أهمية أو لنشر أسئلة أو الإجابة عنها، تفضل بزيارة Stack Overflow.