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

ينتشر استخدام MySQL وMariaDB كثيرا لإدارة قواعد البيانات العلاقيّة، وتستخدم الاثنتان استعلامات SQL لإدخال البيانات في القاعدة واستخراجها منها.

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

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

مبادئ عامّة في تهيئة الجداول Tables

تبدأ فعاليّة قاعدة البيانات بتصميم بنية جداول تحترم معايير مجرَّبة لتحسين الأداء. يعني هذا أنه يجب عليك التفكير في أفضل طريقة لتنظيم بياناتك قبل البدء في استخدام البرنامج.

في ما يلي بضعة أسئلة تساعدك في إيجاد طريقة مثلى لتهيئة مخطّط الجداول في قاعدة البيانات.

ما هو الاستخدام الأساسي للجدول؟

تُملي معرفةُ الكيفية التي ستُستغَل بها البيانات الموجودة في الجدول مستقبلا أفضلَ مقاربة لتخطيط الجداول في قاعدة البيانات.

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

تكون عمليّات التحديث عموما أسرع بكثير عند تنفيذها على جداول صغيرة، بينما يُفضَّّل تنفيذ تحليل البيانات المعقّدة على الجداول الكبيرة، بدلا من تجميع الكثير من الجداول الصغيرة، عبر تعليمات join مثلا.

ما هي أنواع البيانات المطلوبة؟

يُمكن أحيانا اقتصاد الكثير من الوقت على المدى الطويل إن استطعت تطبيق قيود سلفا على أحجام البيانات المطلوبة.

إن كانت لديك، على سبيل المثال، قيم محدودة لأحد الحقول النصيّة فيمكنك استخدام نوع البيانات enum لهذا الحقل بدلا من varchar. البيانات من النوع البيانات enum ذات حجم صغير وبالتالي يُنفَّذ الاستعلام عنها بسرعة كبيرة. يصلُح النوع enum - مثلا - لحقل يُخزّن أدوار المستخدِمين في منتدى: مدير Admin، مشرف Moderator، مستخدم فعّال Poweruser أو مستخدم عادي User.

ماهي الحقول التي ستستعلِم عنها؟

تساهم المعرفة القبْليّة للحقول التي ستبحث عن قيمها باستمرار في التحسين المعتبر لسرعة تنفيذ الاستعلامات، وذلك بفهرسة Indexing حقول البحث.

يُضاف الفهرس على حقل على النحو التالي عند إنشاء الجدول:

CREATE TABLE example_table (
    id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR(50),
    address VARCHAR(150),
    username VARCHAR(16),
    PRIMARY KEY (id),
    INDEX (username)
);

لاحظ التعليمة INDEX أعلاه.

يفيد الفهرس على الحقل username كثيرا إذا كنا نعرف أن زوّار موقعنا سيبحثون عن المستخدمين بأسماء حساباتهم. يُنشئ الاستعلام أعلاه جدول example_table يمكن عرض خاصيّاته كالتالي:

explain example_table;

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(50)  | YES  |     | NULL    |                |
| address  | varchar(150) | YES  |     | NULL    |                |
| username | varchar(16)  | YES  | MUL | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

يوجد لدينا - كما يظهر - فهرسان. الأول المفتاح الرئيس Primary key ويوجد على حقل المعرِّف id، أما الثاني فهو الذي عرّفناه ويوجد على الحقل username. يعني هذا أن البحث عن المستخدمين بأسماء حساباتهم سيكون أسرع كثيرا من البحث عنهم بأحد الحقول المتبقيّة.

من المهمّ جدا، من وجهة نظر التصميم البرمجي، التفكير في الحقول التي يجب أن تُفهرَس وفعل ذلك مع إنشاء الجدول؛ إلا أن بالإمكان ايضا إضافة فهارس إلى جداول موجودة سلفا على النحو التالي:

CREATE INDEX index_name ON table_name(column_name);

توجد طريقة أخرى للحصول على نفس النتيجة:

ALTER TABLE table_name ADD INDEX ( column_name );

استخدام الدالة explain لإيجاد حقول لفهرستها

إذا كان برنامجك يطلب تنفيذ استعلامات منتظمة يمكن التنبؤ بها فيجب عليك تحليل هذه الاستعلامات للتأكد من أن الاستعلامات تستخدم حقولا بها فهارس كلما كان ذلك ممكنا. تساعد الدالة explain في هذه المهمة.

سنستورد قاعدة البيانات التجريبية الموجودة في المرفق employees_db.zip لتطبيق بعض الأمثلة عليها. نزّل الملف المضغوط ثم نفّذ الأمرين التاليين لفكّ ضغطه والانتقال إلى المجلّد employees_db الناتج عن فك الضغط:

tar xjvf employees_db-full-1.0.6.tar.bz2
cd employees_db

ثم ننفّذ اﻷمر التالي لاستيراد القاعدة إلى MySQL (ينبغي أن يكون عميل MySQL - حزمة mysql-client - مثبتا لديك):

mysql -u root -p -t < employees.sql

ستُطلب منك كلمة سرّ خادوم MySQL.

نسجّل الدخول إلى خادوم MySQL:

mysql -u root -p

ننفّذ الأمر التالي داخل المحثّ Prompt الخاص بـMySQL لتحديد قاعدة البيانات التي استوردناها للتو:

use employees;

نحتاج أولا لإخبار MySQL ألا يستخدم تخبئته الداخليّة ليمكننا الحكم بدقّة على الوقت اللازم لتنفيذ هذه المهامّ.

SET GLOBAL query_cache_size = 0;
SHOW VARIABLES LIKE "query_cache_size";

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+
1 row in set (0.00 sec)

يمكننا الآن تشغيل استعلام بسيط على مجموعة كبيرة من البيانات:

SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;

+----------+
| count(*) |
+----------+
|   588322 |
+----------+
1 row in set (0.80 sec)

يطلُب الاستعلام أعلاه عدَّ الموظفين ذوي الدخل المحصور بين 60000 و70000. تُظهر النتيجة وجود 588322 موظف في هذا المجال.

نستفيد الآن من الدالة explain لرؤية كيف نُفِّذ الاستعلام السابق وذلك بإضافة الكلمة EXPLAIN أمام الاستعلام الذي طبّقناه للتو:

EXPLAIN SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;

+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | salaries | ALL  | NULL          | NULL | NULL    | NULL | 2844738 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

يظهر في نتيجة استخدام التعليمة EXPLAIN أن قيمة الحقل key هي NULL، ما يعني أنه لم يُستخدَم أي فهرس في الاستعلام السابق.

فلنضف فهرسا على الحقل salary، ثم لنعد نفس الاستعلام ولننظر إن كان ذلك يُسرّع من تنفيذه:

ALTER TABLE salaries ADD INDEX ( salary );
SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;

النتيجة:

+----------+
| count(*) |
+----------+
|   588322 |
+----------+
1 row in set (0.14 sec)

يمكن ملاحظة أن سرعة تنفيذ الاستعلام تحسّنت كثيرا من 0.80 ثانية قبل إضافة الفهرس إلى 0.14بعد إضافته.

قاعدة مهمّة أخرى عند استخدام الفهارس هي أنه ينبغي الانتباه إلى ربط الجداول (عبر التعليمة join مثلا)؛ إذ يجب أن تُنشَأ فهارس للحقول المستخدمة في الربط، كما يجب أن تشترك هذه الحقول في نوع البيانات.

على سبيل المثال، إن كان لديك جدول يحوي وصفات طعام (وليكن اسمه cheeses) وآخر لمكوّنات كلّ وصفة (وليكن اسمُه ingredients) فإن ربط الجدوليْن يمكن أن يكون حسب حقل من النوع INT (عدد طبيعي) يحوي معرّف المكوّن (وليكن اسمُه ingredient_id) موجود في الجدوليْن. يمكننا بعدها إنشاء فهرس على كل من الحقليْن ingredient_id وبالتالي تسريع استعلامات join كثيرا.

تحسين الاستعلامات لتسريع تنفيذها

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

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

SELECT * FROM SALARIES WHERE salary < 40000 LIMIT 1;

النتيجة:

+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10022 |  39935 | 2000-09-02 | 2001-09-02 |
+--------+--------+------------+------------+
1 row in set (0.00 sec)

يُنفَّّذ الاستعلام سريعا لأنه يكتفي بأول تسجيلة تحقّق الشرط المطلوب.

إن كان الاستعلام - مثلا - يستخدم عامل المقارنة OR وكان عنصرا المقارنة يختبران حقولا مختلفة فإن الاستعلام قد يأخذ وقتا أكثر من اللازم. على سبيل المثال، إن أردت البحث عن جميع الموظفين الذين تبدأ أسماءهم الشخصية والعائلية بـBre فستبحث عن قيم حقليْن مختلفيْن (first_name وlast_name). يكون الاستعلام باستخدام العامل OR على النحو التالي:

SELECT * FROM employees WHERE last_name like 'Bre%' OR first_name like 'Bre%';

إلا أن الاستعلام قد يكون (حسب طبيعة البيانات في الجدول) أسرع إن بحثتَ عن الأسماء الشخصيّة أولا في استعلام خاصّ ثم عن الأسماء العائلية ثانيا ودمجت الاثنين:

SELECT * FROM employees WHERE first_name like 'Bre%' UNION SELECT * FROM employees WHERE last_name like 'Bre%';

خاتمة

عرضنا في هذا المقال لبضعة حيّل تساعدك في البدء في تحسين الاستعلامات الموجهة لقاعدة بيانات MySQL (أو MariaDB). توجد الكثير من الحيّل الأخرى التي ستساعدك في الرفع من أداء قاعدة البيانات وبالتالي أداء تطبيقك أو موقعك.

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

ترجمة - بتصرف - للمقال How To Optimize Queries and Tables in MySQL and MariaDB on a VPS لصاحبه Justin Ellingwood.