أشارك ما أعرفه عن البرمجة، وتجارب أخرى.

تحسين اﻷداء بإستخدام فهارس قواعد البيانات

تم نشرها بتاريخ 2020-02-25 في شروح ، برمجيات

ربما تكون قد تعملت أو سمعت من قبل عن فهارس قواعد البيانات (Database Indexes)  فقد تكون قد درستها خلال مواد الخاصة بقواعد البيانات أو طبقتها خلال تعلمك لأحد الدورات التعليمية لأحد لغات البرمجة. فأنا تعلمتها بدايةً خلال دراستي الجامعية بمادة تصميم قواعد البيانات وقمت بتطبيقها ولكن لم أفهم ما أهميتها وما الجدوى منها، كل ما تعلمته أنها تقوم بفهرسة الجدول قواعد البيانات! كان هذا الوصف عام لأفهم من خلاله متى يجب علي استخدامهم ولماذا يجب علي ذلك؟ 


وخلال عملي شاهدت دورة تدريبية لقواعد البيانات راجعت بها ماتعلمته من قبل، لأنني أعمل بأسلوب ORM) Object-relational mapping) مما جعلني أنسى طريقة كتابة اﻹستعلامات (Queries) فكانت هذه مراجعةً لي، وخلال هذه السلسلة التدريبية كانت أحد الحلقات تتحدث عن فهارس قواعد البيانات، وعن أهميتها للزيادة من أداء الجمل الاستعلامية وكانت مفيدة جداً أنصح بمشاهدتها


فبعد مشاهدتي لها علمت مدى أهميتها، ولكني ظللت أعتبرها خطوة إضافية لتحسين اﻷداء حتى بدأت بالعمل على أحد المشاريع الذي كان به كمية علاقات كبيرة بين الجداول وعلمت أنه يجب علي التركيز على اﻷداء، فبعد  انتهائي من العمل قمت بعمل إختبار عن طريق تعبئة قاعدة البيانات بحوالي 2500 سجل للجدول الرئيسي و200 سجل للجداول ذات علاقة به وبهذا عند طلب API لعرض كل هذه السجلات الرئيسية يتطلب تضمين حتى علاقاتها فكان الوقت الذي تطلبه هذا طلب 8 ثواني (صدقوني كانت طويلة) وعندها بدأت باﻹصلاح للتحسين من هذا اﻷداء وبدأت أولا  بإنشاء فهارس على الجداول الناتجة من علاقات Many to Many لأنها اﻷعقد وبعد اﻹنتهاء والتجربة نقص زمن الطلب API إلي 0.67 ثانية، فالنتيجة كانت تحسن كبير في اﻷداء وهذا يدل على أهمية إضافة الفهارس.


واﻷن بعد معرفتها لأهميتها وكمية التحسين الذي تقوم بها نريد أولاً فهم كيفية عملها، ثم سأقوم بعمل مثال مشابه للذي ذكرته لنستعرض ونفهم تفاصيله.


إذا ماهي الفهارس وكيفية عملها؟

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


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



للبدء بالتجربة سنفترض إننا نقوم ببناء تطبيق لإدارة الطلاب (Student) موادهم الدراسية (Subject) فكل طلاب يمكنه دراسة أكثر من مادة وكل مادة دراسية يمكن أن تدرس لأكثر من طالب إذا فإن العلاقة هي Many to Many.


وبهذا سيكون جدول الطلاب كالتالي، قمت بإضافة البيانات الأساسية فقط كتوضيح


public function up()
   {
       Schema::create('students', function (Blueprint $table) {
           $table->bigIncrements('id');
           $table->string('name');
           $table->date('birthday');
           $table->timestamps();
       });
   }



جدول المواد الدراسية


public function up()
   {
       Schema::create('subjects', function (Blueprint $table) {
           $table->bigIncrements('id');
           $table->string('name');
           $table->timestamps();
       });
   }

وهكذا سيكون الجدول الناشئ من علاقة Many to Many، سنقوم أولا بتجربة بدون إضافة الفهرس لنلاحظ اﻷداء.


public function up()
   {
       Schema::create('student_subject', function (Blueprint $table) {
           $table->increments('id');
           $table->unsignedBigInteger('student_id');
           $table->unsignedBigInteger('subject_id');
           $table->timestamps();
       });
   }



ونقوم بإضافة العلاقة للمادة الدراسية، حيث أن كل مادة دراسية يمكن أن تنتمي لأكثر من طالب


class Subject extends Model
{
   public function students()
   {
       return $this->belongsToMany(Student::class);
   }
}



ونقوم بإضافة التالي بملف DatabaseSeeder.php لإضافة عدد كبير من الطلاب والمواد الدراسية، حيث يكون لكل مادة دراسية 600 طالب يدرسها.


public function run()	
   {
       factory('App\Student', 800)->create();
 
       factory('App\Subject', 200)->create()->each(function ($subject) {
           $subject->students()->attach(Student::inRandomOrder()->take(600)->pluck('id'));
       });
   }



وأخيراً نقوم بإضافة route التالي بملف routes/api.php والغرض منه عرض قائمة بالمواد الدراسية اللاتي يرتبطن بأسماء طلبة يحتوي اسمهم على حرف (a)


Route::get('subjects', function () {
   $subjects = Subject::whereHas('students', function (Builder $query) {
       $query->where('name', 'like', '%a%');
   })
       ->get();
   return  SubjectResource::collection($subjects);
});



وعند تجربته ستظهر النتيجة كالتالي:


نلاحظ أنه استغرق 2 ثواني، والذي يعتبر غير مقبول مقارنة بحجم ونوع البيانات البسيط الذي نقوم بعرضه فماذا لو كان الحجم أكبر وكانت هناك علاقات أكثر عددا وأكثر تعقيداً فبالتأكيد سيستغرق الوقت أكثر، والآن لنجرب إضافة فهرس index على جدول student_subject و لنقارن اﻷداء


public function up()
    {
        Schema::create('student_subject', function (Blueprint $table) {
            $table->increments('id');
            $table->unsignedBigInteger('student_id');
            $table->unsignedBigInteger('subject_id');
            $table->timestamps();

            $table->index(['student_id', 'subject_id']);
        });
    }





فنلاحظ الآن أنه استغرق فقط 0.052 ثانية، وأيضا التغيير في اﻷداء ملحوظ جداً.

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


Photo by Denny Müller on Unsplash


خولة الشح

تمت كتابتها بواسطة خولة الشح