يمكن استخدام قواعد البيانات العلاقية Relational Databases للعمل مع بيانات من جميع الأحجام بما في ذلك قواعد البيانات الكبيرة التي تحتوي على ملايين الصفوف، وتوفّر لنا لغة الاستعلام البنيوية Structured Query Language -أو SQL اختصارًا- طريقة موجزة ومباشرة للعثور على صفوف معينة في جداول قاعدة البيانات وفق معايير محددة، ولكن مع تزايد الحجم سيصبح تحديد موقع صفوف معينة في قواعد البيانات أكثر صعوبة ويشبه البحث عن إبرة في كومة قش!
تصعّب قدرة قواعد البيانات على قبول مجموعة واسعة من شروط الاستعلام على محرّك قاعدة البيانات توقّع الاستعلامات الأكثر شيوعًا، إذ يجب أن يكون المحرّك مستعدًا لتحديد موقع الصفوف بكفاءة في جداول قاعدة البيانات بغض النظر عن حجمها، ولكن بطبيعة الحالة سيسوء أداء البحث مع زيادة حجم البيانات وسيصعب العثور على النتائج التي تتطابق مع الاستعلام بسرعة.
في هذه الحالة يمكن لمسؤول قواعد البيانات استخدام مفهوم الفهارس Indexes لمساعدة محرّك قاعدة البيانات على تسريع البحث وتحسين أدائه، حيث سنتعلم في هذا المقال مفهوم الفهارس وكيفية إنشائها للاستفادة منها في الاستعلام من قاعدة البيانات.
مستلزمات العمل
يجب أن يكون لدينا حاسوب يشغّل نظام إدارة قواعد البيانات العلاقية Relational Database Management System -أو RDBMS اختصارًا- مستند إلى لغة SQL. وقد اختبرنا التعليمات والأمثلة الواردة في هذا المقال باستخدام البيئة التالية:
- خادم عامل على توزيعة أوبنتو Ubuntu مع مستخدم ذي صلاحيات مسؤول مختلف عن المستخدم الجذر وجدار حماية مضبوط باستخدام أداة UFW كما هو موضح في دليل الإعداد الأولي للخادم مع الإصدار 20.04 من أوبنتو
- نظام MySQL مُثبَّت ومؤمَّن على الخادم كما هو موضح في مقال كيفية تثبيت MySQL على أوبنتو، وقد نفذنا خطوات هذا المقال باستخدام مستخدم MySQL مختلف عن المستخدم الجذر وفق الطريقة الموضحة في الخطوة التالية من المقال
-
معرفة أساسية بتنفيذ استعلامات
SELECTلاسترجاع البيانات من قاعدة البيانات كما هو موضّح في مقال الاستعلام عن السجلات من الجداول في SQL
ملاحظة: تجدر الإشارة إلى أنّ الكثير من أنظمة إدارة قواعد البيانات العلاقية RDBMS لها تقديماتها الفريدة من لغة SQL، إذ ستعمل الأوامر في هذا المقال بنجاح مع معظم هذه الأنظمة، ولكن الفهارس ليست جزءًا من صيغة SQL المعيارية، لذا قد تجد بعض الاختلافات في الصيغة أو الناتج عند اختبارها على أنظمة مختلفة عن MySQL.
سنحتاج أيضًا إلى قاعدة بيانات تحتوي على بعض الجداول المُحمَّلة ببيانات تجريبية نموذجية لنتمكن من التدرب على استخدام الفهارس، وسنشرح في القسم التالي تفاصيل حول الاتصال بخادم MySQL وإنشاء قاعدة بيانات تجريبية، والتي سنستخدمها في أمثلة هذا المقال.
الاتصال بخادم MySQL وإعداد قاعدة بيانات تجريبية نموذجية
سنتصل بخادم MySQL وسننشئ قاعدة بيانات تجريبية لاتباع الأمثلة الواردة في هذا المقال. إذا كان نظام قاعدة بيانات SQL الخاص بنا يعمل على خادم بعيد، نتصل بالخادم باستخدام بروتوكول SSH من جهازنا المحلي كما يلي:
$ ssh user@your_server_ip
ثم نفتح واجهة سطر أوامر خادم MySQL مع وضع اسم حساب مستخدم MySQL الخاص بنا مكان user:
$ mysql -u user -p
ننشئ قاعدة بيانات باسم indexes:
mysql> CREATE DATABASE indexes;
إذا أُنشئِت قاعدة البيانات بنجاح، فسيظهر الخرج التالي:
الخرج Query OK, 1 row affected (0.01 sec)
يمكن اختيار قاعدة البيانات indexes من خلال تنفيذ تعليمة USE التالية:
$ USE indexes;
وسيظهر الخرج التالي:
الخرج Database changed
اخترنا قاعدة البيانات، وسننشئ جدولًا تجريبيًا ضمنها، حيث سنستخدم في هذا المقال قاعدة بيانات افتراضية للموظفين لتخزين تفاصيل الموظفين الحاليين وأجهزة عملهم. سيحتوي الجدول employees على بيانات بسيطة حول الموظفين في قاعدة البيانات، والتي سنمثّلها باستخدام الأعمدة التالية:
-
employee_id: معرّف الموظف، نوع بياناتهint، وسيكون هذا العمود المفتاح الرئيسي Primary Key للجدول -
first_name: الاسم الأول لكل موظف، نوع بياناتهvarcharبحد أقصى 50 محرفًا -
last_name: لاسم الأخير لكل موظف، ونمثّله باستخدام نوع بياناتهvarcharبحد أقصى 50 محرفًا -
device_serial: الرقم التسلسلي لحاسوب الموظف، ونمثّله باستخدام نوع البياناتvarcharبحد أقصى 15 محرفًا -
salary: راتب كل موظف، ونمثّله باستخدام نوع البياناتintالذي يخزّن البيانات العددية
ننشئ هذا الجدول التجريبي باستخدام الأمر التالي:
mysql> CREATE TABLE employees ( mysql> employee_id int, mysql> first_name varchar(50), mysql> last_name varchar(50), mysql> device_serial varchar(15), mysql> salary int mysql> );
إذا كان الخرج كما يلي، فهذا يعني إنشاء الجدول بنجاح:
الخرج Query OK, 0 rows affected (0.00 sec)
نحمّل بعد ذلك الجدول employees ببعض البيانات التجريبية من خلال تشغيل عملية INSERT INTO التالية:
mysql> INSERT INTO employees VALUES mysql> (1, 'John', 'Smith', 'ABC123', 60000), mysql> (2, 'Jane', 'Doe', 'DEF456', 65000), mysql> (3, 'Bob', 'Johnson', 'GHI789', 70000), mysql> (4, 'Sally', 'Fields', 'JKL012', 75000), mysql> (5, 'Michael', 'Smith', 'MNO345', 80000), mysql> (6, 'Emily', 'Jones', 'PQR678', 85000), mysql> (7, 'David', 'Williams', 'STU901', 90000), mysql> (8, 'Sarah', 'Johnson', 'VWX234', 95000), mysql> (9, 'James', 'Brown', 'YZA567', 100000), mysql> (10, 'Emma', 'Miller', 'BCD890', 105000), mysql> (11, 'William', 'Davis', 'EFG123', 110000), mysql> (12, 'Olivia', 'Garcia', 'HIJ456', 115000), mysql> (13, 'Christopher', 'Rodriguez', 'KLM789', 120000), mysql> (14, 'Isabella', 'Wilson', 'NOP012', 125000), mysql> (15, 'Matthew', 'Martinez', 'QRS345', 130000), mysql> (16, 'Sophia', 'Anderson', 'TUV678', 135000), mysql> (17, 'Daniel', 'Smith', 'WXY901', 140000), mysql> (18, 'Mia', 'Thomas', 'ZAB234', 145000), mysql> (19, 'Joseph', 'Hernandez', 'CDE567', 150000), mysql> (20, 'Abigail', 'Smith', 'FGH890', 155000);
ستستجيب قاعدة البيانات برسالة النجاح التالية:
الخرج Query OK, 20 rows affected (0.010 sec) Records: 20 Duplicates: 0 Warnings: 0
ملاحظة: مجموعة البيانات هنا ليست كبيرة بما يكفي لتوضيح تأثير الفهارس على الأداء مباشرة، فالهدف منها هنا توضيح لكيفية استخدام الفهارس في MySQL لتقييد عدد الصفوف التي نجتازها لإجراء الاستعلامات والحصول على النتائج المطلوبة.
نحن الآن جاهزون لمتابعة هذا المقال والبدء باستخدام الفهارس في MySQL.
ما هي الفهارس Indexes
يجب أن تمر قاعدة البيانات على جميع الصفوف الموجودة في الجدول واحدًا تلو الآخر عند تنفيذ استعلام على قاعدة بيانات MySQL، فمثلًا قد نرغب في البحث عن الاسم الأخير للموظفين المتطابق مع الاسم Smith أو جميع الموظفين الذين يتقاضون راتبًا أعلى من 100000 دولار، حيث سيُفحَص كل صف في الجدول واحدًا تلو الآخر للتحقق مما إذا كان يتطابق مع الشرط.
إذا كان الصف متطابقًا مع الشرط، فسيُضاف إلى قائمة الصفوف المُعادة، وإن لم يكن كذلك، فسيمسح MySQL الصفوف اللاحقة حتى يستعرض الجدول بالكامل. هذه الطريقة للعثور على الصفوف المطابقة فعّالة، ولكنها قد تصبح بطيئة وتستهلك كثيرًا من الموارد عند زيادة حجم الجدول، لذا قد لا تكون مناسبة للجداول الكبيرة أو الاستعلامات التي تتطلب وصولًا متكررًا أو سريعًا إلى البيانات.
يمكن حل مشكلات الأداء المتعلقة بالجداول والاستعلامات الكبيرة باستخدام الفهارس، وهي هياكل بيانات فريدة تخزّن مجموعة فرعية مرتبة من البيانات فقط بحيث تكون منفصلة عن صفوف الجدول، وتسمح لمحرّك قاعدة البيانات بالعمل بسرعة وكفاءة أكبر عند البحث عن القيم أو الترتيب وفق حقل معين أو مجموعة من الحقول.
لنستخدم الآن الجدول employees، فأحد الاستعلامات النموذجية التي يمكن تنفيذها هو العثور على الموظفين باستخدام اسمهم الأخير. إن لم نستخدم الفهارس، فسيسترجع MySQL كل موظف من الجدول ويتحقق من تطابق الاسم الأخير مع الاستعلام، وإذا استخدمنا فهرسًا ما، فسيحتفظ بقائمة منفصلة من الأسماء الأخيرة، والتي تحتوي فقط على مؤشّرات إلى صفوف الموظفين المحدَّدين في الجدول الرئيسي، ثم سيستخدم هذا الفهرس لاسترجاع النتائج دون مسح الجدول بأكمله.
يمكن تشبيه الفهارس بدليل الهاتف، فإذا أردنا تحديد موقع شخص اسمه John Smith في هذا الدليل، ننتقل أولًا إلى الصفحة الصحيحة التي تسرد الأشخاص الذين تبدأ أسماؤهم بالحرف S، ثم نبحث في الصفحات عن الأشخاص الذين تبدأ أسماؤهم بالحرفين Sm، وبذلك يمكن استبعاد العديد من الإدخالات بسرعة، مع العلم أنها لا تتطابق مع الشخص الذي نبحث عنه. تعمل هذه العملية بنجاح لأن البيانات في دليل الهاتف مرتبة أبجديًا، وهو أمر نادر الحدوث مع البيانات المخزَّنة مباشرةً في قاعدة البيانات. يمثّل الفهرس في محرّك قاعدة البيانات غرضًا مشابهًا لدليل الهاتف، حيث يحتفظ بالمراجع المرتبة أبجديًا إلى البيانات، وبالتالي يساعد قاعدة البيانات في العثور على الصفوف المطلوبة بسرعة.
لاستخدام الفهارس فوائد متعددة، وأكثرها شيوعًا هو تسريع استعلامات WHERE الشرطية، وفرز البيانات باستخدام تعليمات ORDER BY بسرعة أكبر، وفرض أن تكون القيم فريدة، لكن من ناحية أخرى قد يؤدي استخدام الفهارس إلى تراجع أداء قاعدة البيانات في بعض الظروف، فهي مصممة الفهارس لتسريع استرجاع البيانات وتُنفَّذ باستخدام هياكل بيانات إضافية مخزَّنة مع بيانات الجدول، ويجب تحديث هذه الهياكل عند كل تغيير في قاعدة البيانات، مما قد يؤدي إلى إبطاء أداء استعلامات INSERT و UPDATE و DELETE. لكن إذا كان لدينا مجموعات بيانات كبيرة تتغير كثيرًا، فستتفوق الفوائد الناتجة عن السرعة المُحسَّنة لاستعلامات SELECT أحيانًا على الأداء الأبطأ الملحوظ للاستعلامات التي تكتب البيانات في قاعدة البيانات.
يُفضَّل إنشاء الفهارس عند وجود حاجة واضحة إليها فقط مثل الوقت الذي يبدأ فيه أداء التطبيق في الانخفاض. نحتاج لأن نضع في الاعتبار الاستعلامات التي تُنفَّذ بشكل متكرر وتستغرق وقتًا أطول عند اختيار الفهارس التي سننشئها، ونبني الفهارس بناءً على شروط الاستعلام التي ستستفيد منها أكثر من غيرها.
ملاحظة: نركز في هذا المقال على شرح فهارس قاعدة البيانات في MySQL وتوضيح تطبيقاتها الشائعة وأنواعها، حيث يدعم محرّك قاعدة البيانات عدة سيناريوهات أكثر تعقيدًا لاستخدام الفهارس لزيادة أداء قاعدة البيانات، لكن هذا خارج نطاق هذا المقال. ويمكن مطالعة توثيق MySQL الرسمي حول الفهارس للحصول على معلومات وافية عن مميزات فهارس قاعدة البيانات.
ستنشئ في الأقسام التالية فهارس من أنواع مختلفة لمجموعة من السيناريوهات، وسنتعلم كيفية التحقق من استخدام الفهارس في الاستعلام، وكيفية إزالة الفهارس عند الحاجة.
استخدام فهارس العمود الواحد Single-Column
فهرس العمود الواحد هو أحد أكثر أنواع الفهارس شيوعًا ووضوحًا، حيث يمكن استخدامه لتحسين أداء الاستعلام، ويساعد هذا النوع من الفهارس قاعدة البيانات على تسريع الاستعلامات التي ترشّح مجموعة البيانات بناءً على قيم عمود واحد. يمكن للفهارس التي أنشأناها على عمود واحد تسريع العديد من الاستعلامات الشرطية التي تستخدم المطابقات التامة باستخدام المعامل = والمقارنات باستخدام > أو <.
لا توجد فهارس في قاعدة البيانات التجريبية التي أنشأناها في خطوة سابقة. سنختبر أولًا كيفية تعامل قاعدة البيانات مع استعلامات SELECT للجدول employees عند استخدام التعليمة WHERE لطلب مجموعة فرعية من البيانات من الجدول فقط قبل إنشاء الفهرس.
لنفترض أننا نريد العثور على الموظفين الذين راتبهم يساوي 100000 دولار أمريكي تمامًا من خلال تنفيذ الاستعلام التالي:
mysql> SELECT * FROM employees WHERE salary = 100000;
تطلب التعليمة WHERE مطابقة تامة للموظفين الذين يتطابق راتبهم مع القيمة المطلوبة، وستستجيب قاعدة البيانات كما يلي:
الخرج +-------------+------------+-----------+---------------+--------+ | employee_id | first_name | last_name | device_serial | salary | +-------------+------------+-----------+---------------+--------+ | 9 | James | Brown | YZA567 | 100000 | +-------------+------------+-----------+---------------+--------+ 1 row in set (0.000 sec)
ملاحظة: استجابت قاعدة البيانات استجابةً آنية تقريبًا للاستعلام كما يظهر الخرج السابق، فلن يؤثر استخدام الفهارس بوضوح على أداء الاستعلام مع وجود عدد قليل من الصفوف في قاعدة البيانات، ولكن سنلاحظ تغييرات كبيرة في زمن تنفيذ الاستعلام في حال تنفيذه على مجموعات البيانات الكبيرة.
لا يمكن معرفة كيفية تعامل محرّك قاعدة البيانات مع مسألة العثور على الصفوف المطابقة في الجدول بالاعتماد على خرج الاستعلام فقط، ولكن يوفّر MySQL طريقة لمعرفة الطريقة التي ينفّذ بها المحرّك الاستعلام باستخدام التعليمة EXPLAIN، حيث يمكننا مثلًا الوصول إلى طريقة تنفيذ الاستعلام SELECT من خلال تنفيذ الأمر التالي:
mysql> EXPLAIN SELECT * FROM employees WHERE salary = 100000;
يخبر الأمر EXPLAIN نظام MySQL بتشغيل استعلام SELECT، ويعرض معلومات حول كيفية إجراء الاستعلام داخليًا إلى جانب إعادة النتائج، وستكون نتيجة التنفيذ مشابهة لما يلي:
الخرج +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 10.00 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
توضّح الأعمدة في جدول الخرج السابق العديد من جوانب تنفيذ الاستعلام، وقد يحتوي الخرج على أعمدة إضافية بناءً على إصدار MySQL، وفيما يلي أهم هذه المعلومات:
-
يسرد
possible_keysالفهارس التي اعتمدها MySQL للاستخدام، حيث لا يوجد فهارس في حالتناNULL -
يمثل
keyالفهرس الذي قرّر MySQL استخدامه عند تنفيذ الاستعلام، حيث لم نستخدم أي فهرس في مثالناNULL. -
يحدد
rowsعدد الصفوف التي يجب على MySQL تحليلها قبل إعادة النتائج، وتبلغ قيمته 20 في مثالنا وهو يمثّل عدد جميع الصفوف الممكنة في الجدول، مما يعني أنه يجب على MySQL مسح جميع الصفوف في الجدولemployeesللعثور على الصف الوحيد المُعاد -
يعرض
Extraمعلومات إضافية تصف خطة الاستعلام، حيث تعنيUsing whereفي مثالنا أن قاعدة البيانات رشّحت النتائج مباشرة من الجدول باستخدام التعليمةWHERE
تجدر الإشارة لأنه يجب على قاعدة البيانات مسح 20 صفًا لاسترجاع صف واحد في حالة عدم وجود فهرس، وإذا احتوى الجدول على ملايين الصفوف، فيجب على MySQL المرور عليها واحدًا تلو الآخر، مما يؤدي إلى ضعف أداء الاستعلام.
ملاحظة: تعرض إصدارات MySQL الأحدث العبارة 1 row in set, 1 warning في الخرج عند استخدام التعليمة EXPLAIN، بينما تعرض إصدارات MySQL الأقدم وقواعد البيانات المتوافقة مع MySQL العبارة 1 row in set، ولا يُعَد التحذير علامة على وجود مشكلة، حيث يستخدم MySQL آلية التحذيرات الخاصة به لتوفير مزيد من المعلومات الموسَّعة حول خطة الاستعلام. يُعَد هذا الاستخدام لهذه المعلومات الإضافية خارج نطاق هذا المقال، حيث يمكنك معرفة المزيد حول هذا السلوك في صفحة تنسيق خرج التعليمة EXPLAIN المُوسَّع في توثيق MySQL.
استخدم استعلام SELECT الذي نفّذته سابقًا شرط المساواة WHERE salary = 100000، ولكن لنتحقق مما إذا كانت قاعدة البيانات ستتصرف بطريقة مماثلة مع شرط المقارنة، ونجرب استرجاع الموظفين الذين راتبهم أقل من 70000:
mysql> SELECT * FROM employees WHERE salary < 70000;
أعادت قاعدة البيانات هذه المرة صفين John Smith و Jane Doe كما يلي:
الخرج +-------------+------------+-----------+---------------+--------+ | employee_id | first_name | last_name | device_serial | salary | +-------------+------------+-----------+---------------+--------+ | 1 | John | Smith | ABC123 | 60000 | | 2 | Jane | Doe | DEF456 | 65000 | +-------------+------------+-----------+---------------+--------+ 8 rows in set (0.000 sec)
ولكن إذا استخدمنا التعليمة EXPLAIN لفهم تنفيذ الاستعلام كما يلي:
mysql> EXPLAIN SELECT * FROM employees WHERE salary < 70000;
فسنلاحظ أن الجدول مطابق تقريبًا للاستعلام السابق:
الخرج +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 33.33 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
مسح MySQL جميع الصفوف 20 في الجدول للعثور على الصفوف التي طلبتها باستخدام تعليمة WHERE في الاستعلام كما هو الحال مع الاستعلام السابق. يُعَد عدد الصفوف المُعادة في الخرج السابق صغيرًا مقارنة بعدد جميع الصفوف في الجدول، ولكن يجب على محرّك قاعدة البيانات إنجاز الكثير من العمل للعثور عليها.
يمكن حل هذه المشكلة من خلال إنشاء فهرس للعمود salary، والذي سيخبر MySQL بالحفاظ على هيكل بيانات إضافي ومُحسَّن، وخاصةً لبيانات العمود salary من الجدول employees، لذا ننفّذ الاستعلام التالي:
mysql> CREATE INDEX salary ON employees(salary);
تتطلب صيغة التعليمة CREATE INDEX ما يلي:
-
اسم الفهرس وهو
salaryفي مثالنا، ويجب أن يكون اسم الفهرس فريدًا في الجدول الواحد ويمكن تكراره بجداول مختلفة في قاعدة البيانات -
اسم الجدول الذي أنشأنا الفهرس له، وهو
employeesفي مثالنا -
قائمة الأعمدة التي أنشأنا الفهرس لها، حيث استخدمنا في مثالنا عمودًا واحدًا بالاسم
salaryلبناء الفهرس
قد يظهر الخطأ التالي:
ERROR 1142 (42000): INDEX command denied to user 'user'@'host' for table 'employees'
عند تنفيذ الأمر CREATE INDEX بناءً على أذونات مستخدم MySQL، حيث يمكن منح أذونات INDEX للمستخدم من خلال تسجيل الدخول إلى MySQL كمستخدم جذر وتنفيذ الأوامر التالية مع تعديل اسم مستخدم MySQL والمضيف حسب الحاجة:
mysql> GRANT INDEX on *.* TO 'user'@'localhost'; mysql> FLUSH PRIVILEGES;
نسجّل الخروج كمستخدم جذر ونسجّل الدخول مرة أخرى كمستخدم عادي بعد تحديث أذونات المستخدم، ثم نعيد تشغيل التعليمة CREATE INDEX، ستؤكّد الآن قاعدة البيانات إنشاء الفهرس بنجاح كما يلي:
الخرج Query OK, 0 rows affected (0.024 sec) Records: 0 Duplicates: 0 Warnings: 0
نجرّب تكرار الاستعلامات السابقة للتحقق مما إذا كان هناك أي تغيير عند استخدام الفهرس، لذا نبدأ باسترجاع الموظف الذي يتقاضى راتبًا قدره 100000 بالضبط كما يلي:
mysql> SELECT * FROM employees WHERE salary = 100000;
وستبقى النتيجة نفسها مع إعادة الموظف James Brown فقط كما يلي:
الخرج +-------------+------------+-----------+---------------+--------+ | employee_id | first_name | last_name | device_serial | salary | +-------------+------------+-----------+---------------+--------+ | 9 | James | Brown | YZA567 | 100000 | +-------------+------------+-----------+---------------+--------+ 1 row in set (0.000 sec)
وإذا طلبنا من MySQL شرحَ كيفية تعامله مع الاستعلام، فسيعرض بعض الاختلافات عمّا سبق، لذا ننفّذ تعليمة EXPLAIN كما يلي:
mysql> EXPLAIN SELECT * FROM employees WHERE salary = 100000;
وسيكون الخرج هذه المرة كما يلي:
الخرج +----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | salary | salary | 5 | const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
يصرّح MySQL أنه قرّر استخدام المفتاح الذي اسمه salary من المفتاح الوحيد الموضح في العمود possible_keys، وهذا المفتاح هو الفهرس الذي أنشأناه. يعرض العمود rows الآن القيمة 1 بدلًا من 20، حيث تجنّبت قاعدة البيانات مسح جميع الصفوف في قاعدة البيانات ويمكنها إعادة الصف المطلوب مباشرة لأنها استخدمت الفهرس. لا يذكر العمود Extra الآن العبارة Using WHERE، لأن التكرار على الجدول الرئيسي والتحقق من أن كل صف يحقق شرط الاستعلام لم يكن ضروريًا لإجراء الاستعلام.
وكما ذكرنا سابقًا لن نلاحظ تأثير استخدام الفهرس جدًا مع مجموعة بيانات تجريبية صغيرة، ولكن تطلّب الأمر من قاعدة البيانات عملًا أقل بكثير لاسترجاع النتيجة وسيكون تأثير هذا التغيير كبيرًا على مجموعة بيانات أكبر.
نجرّب إعادة تشغيل الاستعلام الثاني واسترجاع الموظفين الذين راتبهم أقل من 70000 للتحقق من استخدام الفهرس، لذا نفّذ الاستعلام التالي:
mysql> SELECT * FROM employees WHERE salary < 70000;
نلاحظ إعادة بيانات John Smith و Jane Doe أيضًا كما يلي:
الخرج +-------------+------------+-----------+---------------+--------+ | employee_id | first_name | last_name | device_serial | salary | +-------------+------------+-----------+---------------+--------+ | 1 | John | Smith | ABC123 | 60000 | | 2 | Jane | Doe | DEF456 | 65000 | +-------------+------------+-----------+---------------+--------+ 8 rows in set (0.000 sec)
ولكن إذا استخدمنا تعليمة EXPLAIN كما يلي:
mysql> EXPLAIN SELECT * FROM employees WHERE salary < 70000;
فسيكون الجدول مختلفًا عن التنفيذ السابق للاستعلام نفسه كما يلي:
الخرج +----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | employees | NULL | range | salary | salary | 5 | NULL | 2 | 100.00 | Using index condition | +----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
يخبرنا العمود key أن MySQL استخدم الفهرس لإجراء الاستعلام، ويخبرنا العمود rows بتحليل صفين فقط لإعادة النتيجة. يحتوي العمود Extra الآن على العبارة Using index condition، مما يعني أن MySQL أجرى ترشيحًا باستخدام الفهرس في هذه الحالة ثم استخدم الجدول الأساسي فقط لاسترجاع الصفوف المطابقة فعليًا.
ملاحظة: قد يقرر MySQL عدم استخدام الفهرس في بعض الأحيان حتى في حالة وجود الفهرس وإمكانية استخدامه، فمثلًا إذا نفذنا الأمر التالي:
mysql> EXPLAIN SELECT * FROM employees WHERE salary < 140000;
فستكون خطة التنفيذ كما يلي:
الخرج +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | salary | NULL | NULL | NULL | 20 | 80.00 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
يعني وجود عمود key الفارغ الذي له القيمة NULL أن MySQL قرر عدم استخدام الفهرس، والذي يمكن تأكيده من خلال الصفوف العشرين الممسوحة بالرغم من إدراج الفهرس salary في العمود possible_keys. يحلل مخطِّط استعلام قاعدة البيانات كل استعلام مقابل للفهارس المحتملة لتحديد أسرع مسار للتنفيذ، فإذا كانت تكلفة الوصول إلى الفهرس أكبر من فائدة استخدامه مثل إعادة الاستعلام جزءًا كبيرًا من بيانات الجدول الأصلية، فيمكن لقاعدة البيانات أن تقرر أنه من الأسرع إجراء مسح كامل للجدول فعليًا.
توضّح التعليقات في العمود Extra مثل Using index condition أو Using where كيفية تنفيذ محرّك قاعدة البيانات للاستعلام بمزيد من التفصيل، فقد تختار قاعدة البيانات طريقة أخرى لتنفيذ الاستعلام وقد يكون لدينا خرج مع عدم وجود التعليق Using index condition أو أي تعليق آخر اعتمادًا على السياق. لا يعني ذلك عدم استخدام الفهرس استخدامًا صحيحًا، ولكنه يعني أن قاعدة البيانات قرّرت أن الطريقة الأخرى للوصول إلى الصفوف ستكون أفضل في الأداء.
أنشأنا واستخدمنا في هذا القسم فهارس مؤلفة من عمود واحد لتحسين أداء استعلامات SELECT التي تعتمد على الترشيح لعمود واحد، وسنتعرّف في القسم التالي على كيفية استخدام الفهارس لضمان أن تكون القيم فريدة في عمود معين.
استخدام الفهارس الفريدة لمنع تكرار البيانات
أحد الاستخدامات الشائعة للفهارس هو استرجاع البيانات بسرعة من خلال مساعدة محرّك قاعدة البيانات على إجراء عمل أقل لتحقيق النتيجة نفسها كما وضّحنا سابقًا، وهناك استخدام آخر وهو ضمان عدم تكرار البيانات في جزء الجدول الذي عرّفنا الفهرس له، وهذا ما يفعله الفهرس الفريد Unique Index.
إن تجنب القيم المكررة ضروري لضمان سلامة البيانات سواءً من وجهة نظر منطقية أو تقنية، فمثلًا لا ينبغي أن يكون هناك شخصان يستخدمان نفس رقم الضمان الاجتماعي، ولا ينبغي لنظام عبر الإنترنت أن يسمح لمستخدمين متعددين أن يسجّلوا باستخدام اسم المستخدم أو عنوان البريد الإلكتروني نفسه.
في حالة جدولنا employees لا ينبغي أن يحتوي حقل الرقم التسلسلي على قيمٍ مكررة وإذا كان الأمر كذلك، فهذا قد يتسبب في منح أكثر من موظف الحاسوب نفسه، ففي هذا الجدول يمكن بسهولة إدخال موظفين جدد مع أرقام تسلسلية مكررة. لنحاول إدخال موظف آخر مع رقم تسلسلي لجهاز قيد الاستخدام كما يلي:
mysql> INSERT INTO employees VALUES (21, 'Sammy', 'Smith', 'ABC123', 65000);
ستدرج قاعدة البيانات هذا الصف وتعلمنا بنجاح العملية كما يلي:
الخرج Query OK, 1 row affected (0.009 sec)
فإذا استعلمنا عن الموظفين باستخدام الحاسوب ذي الرقم التسلسلي ABCD123 كما يلي:
mysql> SELECT * FROM employees WHERE device_serial = 'ABC123';
فسنحصل على شخصين مختلفين كما توضح النتيجة التالية:
الخرج +-------------+------------+-----------+---------------+--------+ | employee_id | first_name | last_name | device_serial | salary | +-------------+------------+-----------+---------------+--------+ | 1 | John | Smith | ABC123 | 60000 | | 21 | Sammy | Smith | ABC123 | 65000 | +-------------+------------+-----------+---------------+--------+ 2 rows in set (0.000 sec)
هذا ليس سلوكًا متوقعًا لإبقاء قاعدة بيانات employees صالحة. لذا سنتراجع عن هذا التغيير من خلال حذف الصف الأخير الذي أنشأناه كما يلي:
mysql> DELETE FROM employees WHERE employee_id = 21;
يمكنك التأكد من ذلك من خلال إعادة تشغيل استعلام SELECT السابق كما يلي:
mysql> SELECT * FROM employees WHERE device_serial = 'ABC123';
وبالتالي أصبح الموظف John Smith المستخدم الوحيد للجهاز الذي رقمه التسلسلي ABC123 مرة أخرى:
الخرج +-------------+------------+-----------+---------------+--------+ | employee_id | first_name | last_name | device_serial | salary | +-------------+------------+-----------+---------------+--------+ | 1 | John | Smith | ABC123 | 60000 | +-------------+------------+-----------+---------------+--------+ 1 row in set (0.000 sec)
لننشئ الآن فهرسًا فريدًا للعمود device_serial لحماية قاعدة البيانات من مثل هذه الأخطاء من خلال تنفيذ التعليمة التالية:
mysql> CREATE UNIQUE INDEX device_serial ON employees(device_serial);
توجِّه إضافة الكلمة المفتاحية UNIQUE عند إنشاء الفهرس قاعدةَ البيانات للتأكد من عدم تكرار القيم في العمود device_serial، حيث تؤدي الفهارس الفريدة إلى التحقق من جميع الصفوف الجديدة المضافة إلى الجدول مقابل الفهرس لتحديد ما إذا كانت قيمة العمود تتوافق مع القيد أم لا.
وستؤكد قاعدة البيانات إنشاء الفهرس كما يلي:
الخرج Query OK, 0 rows affected (0.021 sec) Records: 0 Duplicates: 0 Warnings: 0
نتحقق الآن من إمكانية إضافة إدخال مكرر إلى الجدول من خلال تشغيل استعلام INSERT من جديد:
mysql> INSERT INTO employees VALUES (21, 'Sammy', 'Smith', 'ABC123', 65000);
ستظهر رسالة الخطأ التالية هذه المرة:
الخرج ERROR 1062 (23000): Duplicate entry 'ABC123' for key 'device_serial'
يمكن التحقق من عدم إضافة الصف الجديد إلى الجدول باستخدام استعلام SELECT مرة أخرى:
mysql> SELECT * FROM employees WHERE device_serial = 'ABC123';
وسيُعاد صف واحد فقط هذه المرة:
الخرج +-------------+------------+-----------+---------------+--------+ | employee_id | first_name | last_name | device_serial | salary | +-------------+------------+-----------+---------------+--------+ | 1 | John | Smith | ABC123 | 60000 | +-------------+------------+-----------+---------------+--------+ 1 row in set (0.000 sec)
تعمل الفهارس الفريدة على الحماية من الإدخالات المكررة، وهي أيضًا فهارس وظيفية بالكامل لتسريع الاستعلامات. ويستخدم محرّك قاعدة البيانات الفهارس الفريدة باستخدام الطريقة نفسها في الخطوة السابقة، حيث يمكننا التحقق من ذلك من خلال تنفيذ التعليمة التالية:
mysql> EXPLAIN SELECT * FROM employees WHERE device_serial = 'ABC123';
وستكون نتيجة التنفيذ مشابهة لما يلي:
الخرج +----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | const | device_serial | device_serial | 63 | const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
يظهَر الفهرس device_serial في العمودين possible_keys و key، مما يؤكّد استخدام الفهرس عند تنفيذ الاستعلام.
بهذا تعلمنا استخدام الفهارس الفريدة Unique Index للحماية من البيانات المكررة في قاعدة البيانات، وسنستخدم في القسم التالي الفهارس التي تمتد إلى أكثر من عمود واحد.
الخلاصة
تعلّمنا في هذا المقال ما هي الفهارس واستعرضنا أمثلة متعددة على فهارس العمود الواحد المستخدمة لتسريع استرجاع البيانات من خلال استعلامات SELECT الشرطية، أو للحفاظ على جعل بيانات العمود فريدة، وسنشرح في المقال التالي المزيد حول الفهارس ونوضح كيفية تعريف فهارس متعددة الأعمدة Indexes on Multiple Columns وحالات استخدامها، كما ننصح بالاطلاع على سلسلة تعلم SQL للمزيد حول التعامل مع لغة SQL.
ترجمة -وبتصرف- للمقال How To Use Indexes in MySQL لصاحبَيه Mateusz Papiernik و Rachel Lee.
أفضل التعليقات
لا توجد أية تعليقات بعد
انضم إلى النقاش
يمكنك أن تنشر الآن وتسجل لاحقًا. إذا كان لديك حساب، فسجل الدخول الآن لتنشر باسم حسابك.