الكتابة استجواب سريع MySQL الاستفسارات

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

 لماذا MySQL؟

اليوم هناك الكثير من الحديث عن البيانات الكبيرة والتقنيات الجديدة.  حلول NoSQL والحوسبة السحابية رائعة ، لكن الكثير من برامج الويب الشائعة (مثل WordPress ، phpBB ، Drupal ، VBulletin Forum ، إلخ) لا تزال تعمل على MySQL.  قد لا تكون عملية الترحيل إلى هذه الحلول الجديدة بسيطة مثل مجرد تحسين التكوين الذي لديك بالفعل في الإنتاج.  الى جانب ذلك ، فإن أداء MySQL جيد جدا ، وخاصة إصدار Percona . 

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

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

من هو هذا الدليل؟ 

مطوري الويب ، مهندسي قواعد البيانات / DBAs ومسؤولي النظام المألوفين في MySQL.  إذا لم تكن على دراية بـ MySQL كمستجدة ، فحينئذٍ لن يكون هذا الدليل منطقيًا على الأرجح ، ولكنني سأحاول إبقائه كمعلومات مفيدة للقادمين الجدد إلى MySQL. 

النسخ الاحتياطي أولا 

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

النسخ الاحتياطي MySQL سهل مع أداة سطر الأوامر mysqldump : 

1

$ mysqldump myTable > myTable-backup.sql

يمكنك معرفة المزيد عن mysqldump . 

ما الذي يجعل الاستعلام بطيء؟  

باختصار وبدون ترتيب للأهمية ، تلعب جميع العوامل التالية أهمية كبيرة في أداء الاستعلام والخادم: 

• فهرس الجدول 
•   حيث الجملة (واستخدام وظائف MySQL الداخلية مثل IF و DATE على سبيل المثال) 
• الفرز مع النظام عن طريق 
• تردد الطلبات المتزامنة 
• نوع محرك التخزين (InnoDB ، MyISAM ، الذاكرة ، Blackhole) 
• لا تستخدم طبعة بيركونا 
• متغيرات تكوين الخادم (ضبط my.cnf / my.ini) 
• مجموعات النتائج الكبيرة (> أكثر من 1000 صف) 
• اتصالات غير مستمرة 
• تقسيم / تكوين الكتلة 
• ضعف تصميم الجدول 

سوف نتناول جميع هذه المجالات في هذا الدليل.  أيضًا ، إذا لم تكن تستخدمه بالفعل ، فالرجاء تثبيت Percona ، وهو بديل لـ MySQL والذي سيؤدي إلى زيادة كبيرة في الأداء.  لرؤية معيار Percona مقابل MySQL ، انظر إلى هذه المقارنة . 

ما هي الفهارس؟ 

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

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

اتصالات غير متواصلة؟ 

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

يمكن لمستخدمي PHP قراءة المزيد في دليل PHP . 

تقليل تكرار الطلبات المتزامنة 

أسرع وأنجع وسيلة وجدت لإصلاح هذه هي عن طريق الاستفادة من وجود مخزن زوج قيمة المفتاح مثل أعطها أو رديس . 

مع Memcache يمكنك ببساطة تخزين محتويات الاستعلام الخاصة بك مع ما يلي ، على سبيل المثال: 

1

<?php

2

$cache = new Memcache;

3

$cache->connect('localhost',11211);

4

$cacheResult = $cache->get('key-name');

5

if($cacheResult){

6

    //.. no need to query

7

    $result = $cacheResult;

8

} else {

9

  //.. run your query

10

     $mysqli = mysqli('p:localhost','username','password','table'); //prepend p: to hostname for persistancy

11

     $sql = 'SELECT * FROM posts 

12

     		LEFT JOIN userInfo using (UID) WHERE posts.post_type = 'post' || posts.post_type = 'article' 

13

     ORDER BY column LIMIT 50';

14

     $result = $mysqli->query($sql);

15

     $memc->set('key-name', $result->fetch_array(), MEMCACHE_COMPRESSED,86400);

16

}

17

18

//Pass the $cacheResult to template

19

$template->assign('posts', $cacheResult);

20

21

?>

الآن سيتم تشغيل استعلام LEFT JOIN المثال مرة واحدة فقط كل 86،400 ثانية (24 ساعة) ، وبالتالي أخذ كمية كبيرة من التحميل بعيدًا عن خادم MySQL وتقليل الاتصالات المتزامنة. 

ملاحظة: Prepend p: لوسيطة المضيف في MySQLi للاتصالات المستمرة. 

تقاسم / المجموعات 

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

ضعيف تصميم الطاولة 

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

يُعد ضمان أن التصميم صحيحًا للاستخدام المطلوب أمرًا بالغ الأهمية في إنشاء تطبيقك. احتفظ ببيانات محددة منفصلة (مثل الفئات والمشاركات) وتأكد من سهولة ربط العلاقات بين عدة أطراف أو علاقات فردية بأرقام التعريف.  يعد استخدام مرفق FOREIGN KEY في MySQL مثاليًا لتزامن البيانات بين الجداول. 

عند إنشاء الجدول ، حاول تذكر ما يلي: 

• استخدم الحد الأدنى الذي تحتاجه لإنجاز المهمة ؛ تكون متفرقة وحتى النقطة. 
• لا تتوقع أن تقوم MySQL بمنطق عملك أو أن تكون برمجيًا — يجب أن يتم ذلك فعلاً قبل إدخال لغة البرمجة النصية.  على سبيل المثال ، إذا كنت بحاجة إلى تحديد عشوائي لقائمة ، فقم بإجراء عملية عشوائية لصفيف في PHP ، وليس في ORDER BY في MySQL. 
• استخدام فريد نوع مؤشر لمجموعات البيانات الفريدة والاستفادة ON DUPLICATE KEY UPDATE للحفاظ على التاريخ والوقت أو يونيكس الزمني المحدثة على سبيل المثال من آخر مرة تم فيها فحص الصف.
• استخدم نوع بيانات INT من أجل عدد صحيح من الأرقام.   إذا لم تحدد الطول ، فستقوم MySQL بحساب ما هو مطلوب بحد ذاته. 

أساسيات التحسين

للتحسين الفعال ، يجب أن ننظر إلى ثلاث مجموعات بيانات أساسية بخصوص تطبيقك: 

1. التحليل (بطيء تسجيل الاستعلام ، والتدقيق ، وتحليل الاستعلام وتصميم الجدول) 
2. متطلبات الأداء (عدد المستخدمين، ما هو الطلب)
3. قيود التكنولوجيا (سرعة الأجهزة ، تطلب الكثير من MySQL) 

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

1

mysql> EXPLAIN SELECT * FROM `wp_posts` WHERE `post_type`='post';

2

+----+-------------+----------+------+------------------+------------------+---------+-------+------+-------------+

3

| id | select_type | table    | type | possible_keys    | key              | key_len | ref   | rows | Extra       |

4

+----+-------------+----------+------+------------------+------------------+---------+-------+------+-------------+

5

|  1 | SIMPLE      | wp_posts | ref  | type_status_date | type_status_date | 82      | const |    2 | Using where |

6

+----+-------------+----------+------+------------------+------------------+---------+-------+------+-------------+

7

1 row in set (0.00 sec)

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

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

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

1

EXPLAIN SELECT main_text

2

FROM posts

3

WHERE user = 'myUsername' &&

4

status = '1' && (

5

    status_spam_user = 'no_spam'

6

    || (

7

        status_spam_user = 'neutral' &&

8

        status_spam_system = 'neutral'

9

    )

10

)

11

ORDER BY datum DESC

12

LIMIT 6430 , 10

يمكن الحصول على هذا النوع من الاستعلام إلى القرص بسبب الشرطي فيها، الذي يحدث إذا ما نظرنا شرح:

1

id  select_type     table       type    possible_keys                       key         key_len     ref     rows    Extra

2

1   SIMPLE          posts  ref     index_user,index_status    index_user  32          const   7800    Using where; Using filesort

لذا هذا الاستعلام إمكانية استخدام فهارس اثنين وحاليا هو ضرب القرص بسبب filesort استخدام في الإضافية.

ويعرف ما تقوم به باستخدام Filesort هنا من دليل ماي:

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

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

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

تلميحات الفهرس

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

لننظر إلى مفاتيح الجدول, استخدم الأمر "إظهار الفهرس".

يمكنك تحديد عدة تلميحات للمحسن استخدامه، على سبيل المثال:

1

SELECT * FROM table1 USE INDEX (col1_index,col2_index)

2

  WHERE col1=1 AND col2=2 AND col3=3;

3

 ```

4

5

Running an `EXPLAIN` will show you which index was used in the final outcome. So to fix the previous example we will add the `USE INDEX` as so:

6

7

```sql

8

EXPLAIN SELECT main_text

9

FROM posts USE INDEX (index_user)

10

WHERE user = 'myUsername' &&

11

status = '1' && (

12

    status_spam_user = 'no_spam'

13

    || (

14

        status_spam_user = 'neutral' &&

15

        status_spam_system = 'neutral'

16

    )

17

)

18

ORDER BY datum DESC

19

LIMIT 6430 , 10

الآن أن الخلية قد index_status من الجدول المراد استخدامه، يتم إصلاح الاستعلام.

1

id  select_type     table       type    possible_keys                       key         key_len     ref     rows    Extra

2

1   SIMPLE          posts  ref     index_user,index_status    index_user  32          const   7800    Using where

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

1

mysql> DESCRIBE City;

2

+------------+----------+------+-----+---------+----------------+

3

| Field      | Type     | Null | Key | Default | Extra          |

4

+------------+----------+------+-----+---------+----------------+

5

| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |

6

| Name       | char(35) | NO   |     |         |                |

7

| Country    | char(3)  | NO   | UNI |         |                |

8

| District   | char(20) | YES  | MUL |         |                |

9

| Population | int(11)  | NO   |     | 0       |                |

10

+------------+----------+------+-----+---------+----------------+

إضافة الفهارس

يمكنك إنشاء فهارس في الخلية مع بناء جملة CREATE INDEX. وهناك عدد قليل من النكهات من الفهرس. يستخدم النص الكامل لأغراض البحث عن نص كامل ومن ثم هناك نوع فريد من نوعه لضمان البيانات يتم الاحتفاظ بها فريدة من نوعها.

لإضافة فهرس إلى الجدول الخاص بك، استخدم بناء الجملة التالي على سبيل المثال:

1

mysql> CREATE INDEX idx_start_of_username ON `users` (username(10));

هذا سوف إنشاء فهرس على الجدول المستخدمين، والتي سوف تستخدم الحروف الأولى 10 العمود اسم المستخدم، ونوع بيانات varchar.

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

فهارس مركب

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

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

1

mysql> CREATE INDEX idx_composite ON users (username, active);

هنا يجري إنشاء هذا المفتاح في العمود اسم المستخدم من المثال السابق ونشطة، العمود نوع بيانات تعداد الذي يدل على ما إذا كان حساب المستخدم نشطاً أم لا. لذلك الآن عند الاستعلام عن البيانات الخاصة بـ WHERE يكون اسم المستخدم صحيحًا والحساب نشط = 1 ، يتم الآن تحسين مجموعة البيانات للتعامل مع هذا بشكل أفضل.  

ما مدى سرعة Your MySQL؟ 

تمكين التنميط لإلقاء نظرة فاحصة على استعلاماتك MySQL.  يمكن القيام بذلك في وقت التشغيل من خلال مجموعة التنميط = 1 ، ومن ثم تنفيذ الاستعلام الخاص بك والنظر في نتيجة لمحات التعريف . 

مع PDO هنا مقتطف من الكود الذي يفعل ذلك بالضبط: 

1

$db->query('set profiling=1'); 

2

$db->query('select headline, body, tags from posts');

3

$rs = $db->query('show profiles');

4

$db->query('set profiling=0'); // Disable profiling after the query has been run

5

6

$records = $rs->fetchAll(PDO::FETCH_ASSOC); // Get the results from profiling

7

8

$errmsg = $rs->errorInfo()[2]; //Catch any errors here

إذا كنت لا تستخدم PDO ، فيمكن القيام بذلك مع mysqli على النحو التالي: 

1

$db = new mysqli($host,$username,$password,$dbname);

2

3

$db->query('set profiling=1');

4

$db->query('select headline, body, tags from posts');

5

if ($result = $db->query("SHOW profiles", MYSQLI_USE_RESULT)) {

6

    while ($row = $result->fetch_row()) {

7

        var_dump($row);

8

    }

9

    $result->close();

10

}

11

12

if ($result = $db->query("show profile for query 1", MYSQLI_USE_RESULT)) {

13

    while ($row = $result->fetch_row()) {

14

        var_dump($row);

15

    }

16

    $result->close();

17

}

18

19

$db->query('set profiling=0');

سيؤدي هذا إلى إرجاع بيانات التنميط ، والتي ستشمل وقت التنفيذ في القيمة الثانية  للمصفوفة النقابية:

1

array(3) { 

2

	[0]=>  string(1) "1" 

3

    [1]=>  string(10) "0.00024300" 

4

    [2]=>  string(17) "select headline, body, tags from posts" 

5

    }

استغرق الاستعلام 0.00024300 ثانية لإكمال.   هذا سريع لدرجة لا تقلق   ولكن عندما ترتفع الأرقام ، يجب أن نلقي نظرة أعمق. 

كمثال عملي ، تعرّف على تطبيقك.  ضع علامة للتحقق من ثابتDEBUG في برنامج تشغيل قاعدة بيانات طبقة / إطار عمل قاعدة بيانات التطبيق ، ثم يمكنك بدء التدوين عن طريق تمكين حالة ملف التعريف وإخراج النتيجة باستخدامvar_dump / print_r .   الآن سوف تكون قادرا على تصفح صفحات موقعك وتوصيفها بكل سهولة! 

تدقيق بالكامل التطبيق الخاص بك 

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

لتمكين تسجيل الدخول في MySQL الإصدار 5.1.6 ، يمكنك استخدامlog_slow_queries العام ويمكنه تحديد ملف ذيبطء_السرعة_الرقم_العالمي .   يمكن القيام بذلك في موجه وقت التشغيل على النحو التالي: 

1

set global log_slow_queries = 1;

2

set global slow_query_log_file = /dev/slow_query.log;

يمكنك تعيين هذا باستمرار في /etc/my.cnf أو ملف تكوين my.ini لخادمك. 

1

log_slow_queries = 1; 

2

slow_query_log_file = /dev/slow_query.log;

بعد إجراء هذا التغيير ، يجب إعادة تشغيل خادم MySQL ، على سبيل المثال ، إعادة تشغيل خدمة mysql على أنظمة Linux. 

في أحدث الخلية 5.6.1، log_slow_queries تم إهمال وslow_query_log يستخدم بدلا من ذلك.  يسمح تمكين TABLE كنوع مخرجات بتجربة تصحيح ألطف بكثير ويمكن القيام به كما يلي في MySQL 5.6.1 والإصدارات الأحدث: 

1

log_output = TABLE;

2

log_queries_not_using_indexes = 1;

3

long_query_time = 1

long_query_time يحدد عدد الثواني التي يصنف فيها الاستعلام البطيء باسم.  الافتراضي هو 10 و الأدنى 0. يمكن أن يستغرق قيم ميلي ثانية بتحديد float؛ هنا لقد قمت بتعيينها إلى 1 ثانية.  لذلك سيتم تسجيل أي استعلام يستغرق أكثر من ثانية واحدة بتنسيق إخراجTABLE . 

وهذا تسجيل الدخول إلى mysql.slow_log و mysql.general_logالجداول داخل الخلية . 

لتعطيل التسجيل ، قم بتعيين log_output إلى NONE . 

log_queries_not_using_indexes هو منطقي مفيد ، عند تمكينه بالاقتران مع سجل الاستعلام البطيء ، يعني أنه يتم تسجيل الاستعلامات فقط التي من المتوقع استرداد كافة الصفوف. 

لا يعني هذا الخيار دائمًا أنه لا يتم استخدام أي فهرس.   على سبيل المثال ، عندما يستخدم الاستعلام فحص فهرس كامل ، فسيتم تسجيل ذلك لأن الفهرس لن يحد من عدد الصفوف. 

تسجيل الدخول في الإنتاج؟ 

إن عملية تمكين تسجيل الدخول إلى موقع إنتاج بحركة المرور ستتم دائمًا إلى حد كبير دائمًا لفترة قصيرة ، مع مراقبة الحمل لضمان عدم تأثيره على الخدمة.  إذا كنت تحت حمولة ثقيلة وتحتاج إلى إصلاح عاجل ، ابدأ بمعالجة المشكلة في الموجه باستخدام SHOW PROCESSLIST أو عبرجدول information_schema.PROCESSLIST مباشرة ، على سبيل المثال select * from information_schema.PROCESSLIST؛ . 

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

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

تسجيل و percona في pt-query-digest 

لدى Percona بعض الأدوات الرائعة المرفقة به ، و pt-query-digest عبارة عن أداة سطر أوامر لتحليل سجلات الاستعلام ، أو processlist أو tcpdumps. 

يمكنك استخدام pt-query-digest بالطرق التالية: 

تحليل ملف * .log (إخراج من تسجيل استعلام بطيء على سبيل المثال): 

1

$ pt-query-digest slow.log

الإبلاغ عن أبطأ الاستعلامات من host1 في الوقت الحقيقي (مفيد جدًا!): 

1

$ pt-query-digest --processlist h=host1

استخدم tcpdump للإبلاغ عن أبطأ الاستعلامات من بيانات بروتوكول MySQL: 

1

$ tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt

2

3

$ pt-query-digest --type tcpdump mysql.tcp.txt

وأخيرًا ، يمكننا حفظ بيانات طلب البحث البطيئة من مضيف إلى آخر للمراجعة لاحقًا.   هنا نحفظ ملخص الاستعلام لـ slow.log إلى host2: 

1

$ pt-query-digest --review h=host2 --no-report slow.log

 لمعرفة كيفية استخدام أداة Pt -query-digest بالكامل من Percona ، اقرأالصفحة اليدوية . 

الرسوم البيانية MySQL وأداء الخادم 

يعرض هذا الرسم البياني الخاص بعمليات InnoDB Row عمليات الصف التي أجراها InnoDB: التحديثات ، والقراءة ، والحذف ، والإدراج. 

هذا موضوع كبير بالفعل ، وسأكتفي بالاطلاع عليه بشكل كافٍ في هذا الدليل لنبدأ مع مراقبة MySQL.   من المهم ملاحظة بشكل عام أن مراقبة جميع خدمات موقع الويب الخاص بك هو أمر مثالي لمعرفة ما هي أدائك واستخداماتك. 

 لتحقيق ذلك ، أوصي بإعداد حل قائم على RRDTool مثل Cacti مع تهيئة MySQL.  الحصول على قالب ل Cacti من الرجال في Percona. 

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

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

بطيء الاستعلام سجل 

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

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

قبل البدء في استخدام استعلامات جديدة ، يجب عليك المقارنة مع أداة اختبار الحمل مثل ab.  عند تشغيل المعيار ، يجب أن تقوم بعرض قائمةSHOW PROCESSLIST ، وكذلك تمكين التسجيل وأن تكون مراقبًا باستخدام أدوات النظام مثل top و free و iostat .   هذه خطوة حاسمة قبل طرح أي استعلام جديد في الإنتاج الحي.   ولكنه ليس اختبارًا حمضيًا بنسبة 100٪ نظرًا لأن حركة المرور المباشرة يمكن أن تتصرف بشكل مختلف تمامًا عن معيار محسوب. 

لإجراء المقارنة مع علامة ab ، تأكد من تثبيت الحزمة ، على سبيل المثال: 

1

#centos users

2

$ sudo yum install ab

3

#debian / ubuntu users

4

$ sudo apt-get install ab

يمكنك الآن البدء باختبار تطبيقك ، على سبيل المثال: 

1

$ ab -k -c 350 -n 20000 my-domain.com/

و -k يعني المحافظة على الحياة الاتصال، و -c 350 هو عدد الاتصالات المتزامنة، أي عدد من الناس / العملاء التي من شأنها أن تصل إلى الموقع في وقت واحد.  وأخيرًا ، فإن -n 20000 هو عدد الطلبات التي سيتم إجراؤها على my-domain.com . 

لذلك ، من خلال تشغيل الأمر أعلاه ، سيتم الضغط على http://my-domain.com/   مع 350 اتصالًا متزامنًا حتى يتم تلبية 20000 طلب ، وسيتم ذلك باستخدام رأس الصفحة. 

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

قياس الساخنة مقابل البرد 

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

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

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

تحديد الاستعلامات البطيئة 

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

إذا لم تجد أي استعلامات بطيئة حتى الآن ، فحينئذٍ ربما تحقق من إعداداتك فيlong_query_time إذا كنت تستخدم طريقة تسجيل طلبات البحث.  وبخلاف ذلك ، بعد التحقق من جميع طلبات البحث الخاصة بك مع التنميط ( تعيين التنميط = 1 ) ، قم بعمل قائمة بالاستعلامات التي تستغرق وقتًا أطول من كسور المللي ثانية لإكمالها (0.000x ثانية) ودعنا نبدأ بها. 

المشاكل الشائعة 

فيما يلي ستة مشاكل شائعة أواجهها عند تحسين استعلامات MySQL: 

1. ترتيب باستخدام filesort. 

1

mysql> explain select * from products where products.price > 4 and products.stock > 0 order by name;

2

+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+

3

| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                       |

4

+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+

5

|  1 | SIMPLE      | products | ALL  | NULL          | NULL | NULL    | NULL | 1142 | Using where; Using filesort |

6

+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+

تجنب حدوث ملف على هذا مستحيل بسبب اسم ORDER BY .  بغض النظر عن التقليب الذي تستخدمه ، فإن أفضل ما ستحصل عليه هو استخدام أين ؛ باستخدام Filesort في العمود الخاص بك اضافية .   لتحسين هذا ، احفظ النتيجة في Memcache ، أو قم بتنفيذ الطلب في طبقة المنطق الخاصة بالتطبيق. 

2. استخدام ORDER BY على WHERE و LEFT JOIN 

ORDER BY له تأثير كبير على الاستعلامات.  على سبيل المثال ، ما يلي هوLEFT JOIN الأساسي لجدول جدول فئات المنتجات عن طريق معرف صحيح.   عندما تتم إزالة الطلب ، فذلك هو عملية الإيداع. 

1

mysql> explain select products.* from products use index (idx_price) left join categories using (catID) where products.price > 4 and catID = 4 ORDER BY stock ASC limit 10;

2

+----+-------------+------------+-------+---------------+---------+---------+-------+------+-----------------------------+

3

| id | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra                       |

4

+----+-------------+------------+-------+---------------+---------+---------+-------+------+-----------------------------+

5

|  1 | SIMPLE      | products   | ALL   | idx_price     | NULL    | NULL    | NULL  |  986 | Using where; Using filesort |

6

|  1 | SIMPLE      | categories | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index                 |

7

+----+-------------+------------+-------+---------------+---------+---------+-------+------+-----------------------------+

8

2 rows in set (0.00 sec)

9

10

mysql> explain select products.* from products use index (idx_price) left join categories using (catID) where products.price > 4 and catID = 4;

11

+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+

12

| id | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra       |

13

+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+

14

|  1 | SIMPLE      | products   | ALL   | idx_price     | NULL    | NULL    | NULL  |  986 | Using where |

15

|  1 | SIMPLE      | categories | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |

16

+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+

17

2 rows in set (0.00 sec)

عندما يمكن تجنب ذلك ، حاول عدم استخدام ORDER BY .   إذا كان لا بد من استخدامه على الإطلاق ، فقم بالطلب على مفتاح فهرس فقط. 

3. ترتيب على عمود مؤقت 

فقط لا تفعل ذلك.  إذا كنت بحاجة إلى تجميع النتائج الخاصة بك ، قم بذلك في منطق التطبيق الخاص بك ؛ لا تفعل التصفية أو الطلب على جدول مؤقت داخل MySQL.   سوف تكون مكثفة جدا للموارد. 

4. عدم استخدام فهرس FULLTEXT 

يعد استخدام استعلام LIKE أبطأ طريقة لإجراء مطابقة النص الكامل على بياناتك.   قم بتنفيذ البحث عن نص كامل وجني فوائد هذه الميزة الرائعة لـ MySQL على النحو التالي: 

1

mysql> SELECT * FROM articles

2

    -> WHERE MATCH (title,body) AGAINST ('database');

3

+----+-------------------+------------------------------------------+

4

| id | title             | body                                     |

5

+----+-------------------+------------------------------------------+

6

|  5 | MySQL vs. YourSQL | In the following database comparison ... |

7

|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |

8

+----+-------------------+------------------------------------------+

9

2 rows in set (0.00 sec)

5. اختيار عدد ضخم من الصفوف بلا داع 

يمكن أن يؤدي نسيان LIMIT على استعلام إلى تغيير وقت البحث بشكل ملحوظ على مجموعات بيانات كبيرة (أكثر من مليون صف). 

 6. الإفراط في الانضمام بدلاً من مجرد إنشاء جدول مركب أو عرض 

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

الاستنتاجات 

لقد ناقشنا أساسيات التحسين والأدوات المتوفرة لدينا لأداء العمل.  يجب أن نقوم بالتدوين باستخدام التنميط ، وأن نستخدم أداة pt-query-digest tool ونوضح أولاً لمعرفة ما يحدث بالفعل ، ومن ثم يمكننا تصميم أفضل. 

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