شرحنا في المقال السابق مفهوم الفهارس في قاعدة البيانات، ووضحنا أنواعًا مختلفة من الفهارس على قاعدة البيانات، وكانت جميع هذه الفهارس معرًفة باستخدام اسم عمود واحد single column، حيث يتعلق هذا الفهرس بقيم هذا العمود المختار، ولكن تدعم معظم أنظمة قواعد البيانات الفهارس التي تمتد لأكثر من عمود واحد multiple columns، وهذا ما سنوضّحه في هذا المقال، بالإضافة توضيح كيفية سرد وإزالة الفهارس الموجودة مسبقًا.
استخدام الفهارس مع أعمدة متعددة
توفر الفهارس متعددة الأعمدة طريقةً لتخزين قيم أعمدة متعددة في فهرس واحد، مما يسمح لمحرّك قاعدة البيانات بتنفيذ الاستعلامات بسرعة وكفاءة أكبر باستخدام مجموعة الأعمدة مع بعضها البعض. فالاستعلامات المستخدَمة بصورة متكررة والتي يجب تحسينها للحصول على أداء أفضل تستخدم شروطًا متعددة في تعليمة الترشيح WHERE في أغلب الأحيان، ومن الأمثلة على هذا النوع من الاستعلامات استعلام يطلب من قاعدة البيانات أن تعثر على شخص معين من خلال اسمه الأول والأخير كما يلي:
mysql> SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';
قد تكون الفكرة الأولى لتحسين هذا الاستعلام باستخدام الفهارس هي إنشاء فهرسين، أحدهما في العمود last_name والآخر في العمود first_name، ولكنه ليس الخيار الأفضل لهذه الحالة، حيث فإذا أنشأنا فهرسين منفصلين بهذه الطريقة، فسيعرف MySQL كيفية العثور على جميع الموظفين الذين يحملون اسم Smith مثلًا، وسيعرف أيضًا كيفية العثور على جميع الموظفين الذين يحملون اسم John، ولكنه لن يعرف كيفية العثور على الموظفين الذين يحملون الاسم John Smith.
لنوضّح مشكلة وجود فهرسين فرديين من خلال تخيل وجود دليلي هاتف منفصلين، أحدهما مرتب حسب الاسم الأخير والآخر حسب الاسم الأول، ويشبه هذان الدليلان الفهارس التي أنشأناها في المقال السابق في العمودين last_name و first_name على التوالي. يمكنك التعامل مع مشكلة العثور على الاسم John Smith كمستخدمٍ لدليل الهاتف باستخدام ثلاث طرق ممكنة هي:
الطريقة الأولى هي استخدم دليل الهاتف المرتب حسب الاسم الأخير للعثور على جميع الأشخاص الذين يحملون الاسم Smith، وتجاهل دليل الهاتف الثاني، ثم يمكن المرور يدويًا على جميع الأشخاص الذين يحملون اسم Smith واحدًا تلو الآخر حتى نجد الاسم John Smith.
الطريقة الثانية هي تطبيق الطريقة المعاكسة من خلال استخدام دليل الهاتف المرتب حسب الاسم الأول للعثور على جميع الأشخاص الذين يحملون اسم John، وتجاهل دليل الهاتف الثاني، ثم المرور يدويًا على جميع الأشخاص الذين يحملون اسم John واحدًا تلو الآخر حتى نجد الاسم John Smith.
الطريقة الأخيرة هي محاولة استخدام دليلي الهاتف معًا من خلال البحث عن جميع الأشخاص الذين يحملون اسم John وعن جميع الأشخاص الذين يحملون اسم Smith بطريقة منفصلة، وكتابة النتائج المؤقتة، ثم نحاول يدويًا إيجاد تقاطع هاتين المجموعتين الفرعيتين من البيانات بحثًا عن الأشخاص الموجودين في القائمتين الفرديتين.
لا تُعَد أي طريقة من الطرق السابقة مثالية، ويوفر MySQL أيضًا خيارات مماثلة عند التعامل مع العديد من الفهارس المنفصلة والاستعلامات التي تطلب أكثر من شرط ترشيح واحد.
توجد طريقة أخرى أيضًا تتمثّل باستخدام الفهارس التي تأخذ عدة أعمدة بدلًا من عمود واحد، حيث يمكنك تخيل ذلك كدليل هاتف موضوع ضمن دليل هاتف آخر، إذ سنبحث أولًا عن الاسم الأخير Smith، مما يوجّهنا إلى الدليل الثاني لجميع الأشخاص الذين يحملون اسم Smith بحيث تكون الأسماء مرتبة أبجديًا حسب الاسم الأول، ويمكننا استخدام هذا الدليل للعثور على الاسم John بسرعة.
إنشاء فهرس متعدد الأعمدة
يمكن إنشاء فهرس متعدد الأعمدة في MySQL للأسماء الأخيرة والأسماء الأولى في الجدول employees من خلال تنفيذ التعليمة التالية:
mysql> CREATE INDEX names ON employees(last_name, first_name);
تختلف التعليمة CREATE INDEX في هذه الحالة بعض الشيء، حيث ستحتوي على عمودين هما: last_name ثم first_name بين قوسين بعد اسم الجدول employees، مما يؤدي إلى إنشاء فهرس متعدد الأعمدة مع هذين العمودين، ويُعَد ترتيب الأعمدة في تعريف الفهرس مهمًا.
تعرض قاعدة البيانات الرسالة التالية التي تؤكّد إنشاء الفهرس بنجاح:
الخرج Query OK, 0 rows affected (0.024 sec) Records: 0 Duplicates: 0 Warnings: 0
نستخدم الآن استعلام SELECT للعثور على الصفوف التي يتطابق فيها الاسم الأول مع John والاسم الأخير مع Smith كما يلي:
mysql> SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';
وتكون النتيجة صفًا واحدًا يحتوي على موظف اسمه John Smith:
الخرج +-------------+------------+-----------+---------------+--------+ | employee_id | first_name | last_name | device_serial | salary | +-------------+------------+-----------+---------------+--------+ | 1 | John | Smith | ABC123 | 60000 | +-------------+------------+-----------+---------------+--------+ 1 row in set (0.000 sec)
نستخدم الآن استعلام مع أمر EXPLAIN التالي للتحقق من استخدام الفهرس:
mysql> EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';
وستكون طريقة التنفيذ مشابهة لما يلي:
الخرج +----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | names | names | 406 | const,const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
في هذه الحالة استخدمت قاعدة البيانات الفهرس names، ومسحت صفًا واحدًا، لذا لم تمر على الجدول أكثر مما تحتاج إليه. يحتوي العمود Extra على العبارة Using index condition التي تعني أن MySQL يمكنه إكمال الترشيح باستخدام الفهرس فقط، حيث يوفّر الترشيح -وفقًا للأسماء الأولى والأخيرة باستخدام الفهرس متعدد الأعمدة الذي يمتد بين هذين العمودين لقاعدة البيانات- طريقةً مباشرة وسريعة للعثور على النتائج المطلوبة.
لنشاهد الآن ما سيحدث إذا حاولنا العثور على جميع الموظفين الذين يحملون اسم Smith دون الترشيح وفقًا للاسم الأول مع تعريف الفهرس في العمودين، ولنشغّل الاستعلام المعدَّل التالي:
mysql> SELECT * FROM employees WHERE last_name = 'Smith';
وستظهر النتائج التالية:
الخرج +-------------+------------+-----------+---------------+--------+ | employee_id | first_name | last_name | device_serial | salary | +-------------+------------+-----------+---------------+--------+ | 20 | Abigail | Smith | FGH890 | 155000 | | 17 | Daniel | Smith | WXY901 | 140000 | | 1 | John | Smith | ABC123 | 60000 | | 5 | Michael | Smith | MNO345 | 80000 | +-------------+------------+-----------+---------------+--------+ 4 rows in set (0.000 sec)
نلاحظ وجود أربع موظفين يحملون الاسم الأخير Smith.
ننتقل الآن إلى طريقة تنفيذ الاستعلام باستخدام التعليمة التالية:
mysql> EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';
وستكون طريقة التنفيذ مشابهة لما يلي:
الخرج +----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | names | names | 203 | const | 4 | 100.00 | NULL | +----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec)
نلاحظ إعادة 4 صفوف هذه المرة، حيث يوجد أكثر من موظف يحمل هذا الاسم الأخير، ولكن يوضّح جدول طريقة التنفيذ أن قاعدة البيانات استخدمت الفهرس متعدد الأعمدة names لإجراء هذا الاستعلام، ومسحت 4 صفوف فقط، وهو العدد الدقيق المُعاد.
مرّرنا في الاستعلامات السابقة العمود المُستخدَم لترشيح النتائج last_name أولًا في تعليمة CREATE INDEX، وسنرشّح الآن الجدول employees وفق العمود first_name، وهو العمود الثاني في قائمة الأعمدة لهذا الفهرس متعدد الأعمدة، لذا ننفّذ الآن الاستعلام التالي:
mysql> SELECT * FROM employees WHERE first_name = 'John';
وسيظهر الخرج التالي:
الخرج +-------------+------------+-----------+---------------+--------+ | 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 first_name = 'John';
وسيظهر الخرج التالي:
الخرج +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 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)
تحتوي النتائج المُعادة على موظف واحد دون استخدام أي فهرس هذه المرة، ومسحَت قاعدة البيانات الجدول بالكامل كما يوضح التعليق Using where في العمود Extra، بالإضافة إلى 20 صفًا ممسوحًا.
لم تستخدم قاعدة البيانات الفهرس في هذه الحالة بسبب ترتيب الأعمدة المُمرَّرة إلى التعليمة CREATE INDEX عند إنشاء الفهرس لأول مرة: last_name, first_name، إذ لا يمكن لقاعدة البيانات استخدام الفهرس إلا إذا استخدم الاستعلام العمود الأول أو العمودين الأول والثاني، ولا يمكنها دعم الاستعلامات مع الفهرس عند عدم استخدام العمود الأول من تعريف الفهرس.
إذا أنشأنا فهرسًا لأعمدة متعددة، فيمكن لقاعدة البيانات استخدام هذا الفهرس لتسريع الاستعلامات التي تتضمن جميع الأعمدة المفهرسَة أو ذات البادئة المتزايدة اليسارية لجميع الأعمدة المفهرسَة، فمثلًا يمكن استخدام فهرس متعدد الأعمدة يتضمن الأعمدة a و b و c لتسريع الاستعلامات التي تتضمن جميع الأعمدة الثلاثة، والاستعلامات التي تتضمن العمودين الأولين فقط، أو حتى الاستعلامات التي تتضمن العمود الأول فقط، ولكن لن يساعد الفهرس في الاستعلامات التي تتضمن العمود الأخير فقط c أو العمودين الأخيرين b و c.
يمكن استخدام فهرس واحد متعدد الأعمدة لتسريع الاستعلامات المختلفة للجدول نفسه من خلال اختيار الأعمدة المُضمَّنة في الفهرس بعناية وترتيبها، فمثلًا إذا افترضنا أن نبحث عن الموظفين باستخدام الاسم الأول والأخير أو الاسم الأخير فقط، فسيضمن الترتيب المُقدَّم للأعمدة في الفهرس names أن الفهرس سيسرّع جميع الاستعلامات ذات الصلة.
استخدمنا في هذا القسم فهرسًا متعدد الأعمدة وتعلّمنا ترتيب الأعمدة عند تحديد مثل هذا الفهرس، وسنتعلّم في الفقرات التالية كيفية إدارة الفهارس الموجودة مسبقًا.
سرد وإزالة الفهارس الموجودة مسبقًا
أنشأنا في الأقسام السابقة فهارس جديدة، بما أن الفهارس لها أسماء وتُعرَّف لجداول معينة، فيمكننا أيضًا سردها ومعالجتها عند الحاجة، حيث يمكن سرد جميع الفهارس التي أنشأناها سابقًا للجدول employees من خلال تنفيذ التعليمة التالية:
mysql> SHOW INDEXES FROM employees;
وسيكون الخرج مشابهًا لما يلي:
الخرج +-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | employees | 0 | device_serial | 1 | device_serial | A | 20 | NULL | NULL | YES | BTREE | | | YES | NULL | | employees | 1 | salary | 1 | salary | A | 20 | NULL | NULL | YES | BTREE | | | YES | NULL | | employees | 1 | names | 1 | last_name | A | 16 | NULL | NULL | YES | BTREE | | | YES | NULL | | employees | 1 | names | 2 | first_name | A | 20 | NULL | NULL | YES | BTREE | | | YES | NULL | +-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 4 rows in set (0.01 sec)
قد يختلف الخرج بعض الشيء اعتمادًا على إصدار MySQL الخاص بنا، ولكنه سيتضمن جميع الفهارس مع أسمائها والأعمدة المستخدمة لتعريف الفهرس والمعلومات التي تجعله فريدًا وتفاصيل أخرى لتعريف الفهرس.
يمكن حذف الفهارس الموجودة مسبقًا من خلال استخدام تعليمة SQL التالية:
DROP INDEX
فإن لم نعد نرغب في فرض جعل العمود device_serial فريدًا، فلن تكون هناك حاجة إلى الفهرس device_serial بعد الآن، وسننفّذ الأمر التالي لحذفه:
mysql> DROP INDEX device_serial ON employees;
device_serial هو اسم الفهرس و employees هو الجدول الذي عرّفنا الفهرس له، وستؤكد قاعدة البيانات حذف الفهرس كما يلي:
الخرج Query OK, 0 rows affected (0.018 sec) Records: 0 Duplicates: 0 Warnings: 0
قد تتغير أنماط الاستعلامات النموذجية بمرور الوقت، وقد تظهر أنواع استعلامات جديدة في بعض الأحيان، لذا قد نحتاج إلى إعادة تقييم الفهارس التي نستخدمها أو إنشاء فهارس جديدة أو حذف الفهارس غير المستخدمة لتجنب تناقص أداء قاعدة البيانات من خلال تحديثها باستمرار.
يمكننا إدارة الفهارس في قاعدة بيانات موجودة مسبقًا باستخدام أوامر CREATE INDEX و DROP INDEX من خلال اتباع أفضل الممارسات لإنشاء الفهارس عندما تصبح ضرورية ومفيدة.
الخلاصة
تعلّمنا في هذا المقال كيف يمكن تعريف فهارس متعددة الأعمدة وكيف يمكن للفهارس أن تؤثر على الاستعلامات عند استخدام أكثر من عمود واحد في شرط الترشيح وكيفية سرد وإزالة الفهارس الموجودة مسبقًا، وقد ركزنا على أمثلة بسيطة توضح أساسيات استخدام الفهارس فقط، ولكن يمكننا دعم الاستعلامات الأكثر تعقيدًا من خلال الفهارس عند فهم كيفية اختيار MySQL للفهارس المُستخدَمة ومتى يستخدمها، لذا يمكن الرجوع لتوثيق MySQL للفهارس لمزيد من المعلومات.
ترجمة -وبتصرف- للجزء الثاني من مقال How To Use Indexes in MySQL لصاحبيه Mateusz Papiernik و Rachel Lee.
أفضل التعليقات
لا توجد أية تعليقات بعد
انضم إلى النقاش
يمكنك أن تنشر الآن وتسجل لاحقًا. إذا كان لديك حساب، فسجل الدخول الآن لتنشر باسم حسابك.