شرح فهارس SQL ، نقطة. 1

عند الاستخدام الصحيح ، يمكن أن يكون فهرس قاعدة بيانات SQL فعالاً لدرجة أنه قد يبدو سحريًا. لكن سلسلة التدريبات التالية ستظهر أنه في الأسفل ، فإن منطق معظم فهارس SQL - والتعامل معها بشكل صحيح - واضح تمامًا.

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

ليس عليك معرفة الكثير عن الفهارس لتتمكن من متابعة شرح فهارس SQL . هناك شرطان مسبقان فقط:

• معرفة أساسية بـ SQL
• معرفة أساسية بأي لغة برمجة

الموضوعات الرئيسية التي سيتم شرحها في فهارس SQL هي:

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

هذا ليس مجرد برنامج تعليمي لفهرس SQL - إنه غوص عميق في فهم الآليات الأساسية للفهارس.

سنكتشف كيف يستخدم نظام RDBMS الفهارس من خلال القيام بتمارين وتحليل طرق حل المشكلات لدينا. تتكون مواد التمرين الخاصة بنا من أوراق Google للقراءة فقط. للقيام بتمرين ، يمكنك نسخ جدول بيانات Google ( ملف ← عمل نسخة ) أو نسخ محتوياته إلى جدول بيانات Google الخاص بك.

في كل تمرين ، سنعرض استعلام SQL الذي يستخدم بنية Oracle. بالنسبة للتواريخ ، سنستخدم تنسيق ISO 8601 ، YYYY-MM-DD .

التمرين 1: جميع حجوزات العميل

المهمة الأولى - لا تفعل ذلك الآن - هي العثور على جميع الصفوف من جدول بيانات الحجز لعميل معين لنظام حجز الفنادق ، ونسخها في جدول البيانات الخاص بك ، ومحاكاة تنفيذ الاستعلام التالي:

 SELECT * FROM Reservations WHERE ClientID = 12;

لكننا نريد اتباع طريقة معينة.

المقاربة 1: لا فرز ، لا تصفية

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

يوضح هذا الرمز الكاذب الخوارزمية لإنجاز المهمة دون الفرز:

 For each row from Reservations If Reservations.ClientID = 12 then fetch Reservations.*

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

الأسلوب 2: الفرز فقط

للمحاولة الثانية ، قم بفرز الورقة وفقًا لقيمة عمود ClientID . لا تستخدم المرشحات. سجل الوقت وقارنه بالوقت المستغرق لإكمال المهمة دون فرز البيانات.

بعد الفرز ، يبدو الأسلوب كالتالي:

 For each row from Reservations If ClientID = 12 then fetch Reservations.* Else if ClientID > 12 exit

هذه المرة ، كان علينا التحقق من 780 صفًا "فقط". إذا تمكنا بطريقة ما من القفز إلى الصف الأول ، فسيستغرق الأمر وقتًا أقل.

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

التمرين 2: عدد الحجوزات التي تبدأ في تاريخ معين

المهمة الآن هي حساب عدد مرات الوصول في 16 أغسطس 2020:

 SELECT COUNT (*) FROM Reservations WHERE DateFrom = TO_DATE('2020-08-16', 'YYYY-MM-DD')

استخدم جدول البيانات من التمرين 1. قم بقياس ومقارنة الوقت المستغرق في إكمال المهمة مع الفرز وبدونه. العدد الصحيح هو 91.

بالنسبة للنهج بدون الفرز ، فإن الخوارزمية هي في الأساس نفس الخوارزمية الموجودة في التمرين 1.

يشبه أسلوب الفرز أيضًا أسلوب التمرين السابق. سنقسم الحلقة إلى قسمين:

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 16th of August 2020. Repeat Read next row Until DateFrom = '2020-08-16' -- Calculate the count While DateFrom = '2020-08-16' Increase the count Read the next row

التمرين 3: التحقيق الجنائي

مفتش الشرطة يطلب الإطلاع على قائمة الضيوف الذين وصلوا إلى الفندق يومي 13 و 14 أغسطس 2020.

 SELECT ClientID FROM Reservations WHERE DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND HotelID = 3;

الأسلوب 1: الترتيب حسب التاريخ فقط

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

من فضلك ، حاول وتسجيل الوقت ، وعدد الصفوف التي كان عليك قراءتها ، وعدد العناصر في القائمة.

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 13th of August 2020. Repeat Read next row Until DateFrom >= '2020-08-13' -- Prepare the list While DateFrom < '2020-08-15' If HotelID = 3 then write down the ClientID Read the next row

باستخدام هذا النهج ، كان علينا قراءة 511 صفًا لتجميع قائمة تضم 46 ضيفًا. إذا تمكنا من الانزلاق بدقة ، فلن نضطر في الواقع إلى إجراء 324 قراءة من دورة التكرار فقط لتحديد موقع الوصول الأول في 13 أغسطس. ومع ذلك ، لا يزال يتعين علينا قراءة أكثر من 100 صف للتحقق مما إذا كان الضيف قد وصل إلى الفندق HotelID تعريف الفندق 3 .

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

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

الطريقة 2: مرتبة حسب الفندق ثم التاريخ

لفرز الصفوف وفقًا لمعرّف DateFrom HotelID يمكننا تحديد جميع الأعمدة ، ثم استخدام خيار قائمة أوراق Google Data → Sort range .

 -- Assumption: Sorted according to HotelID and DateFrom -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Find the first arrival at the hotel on 13th of August While HotelID = 3 and DateFrom < '2020-08-13' Read the next row -- Prepare the list While HotelID = 3 and DateFrom < '2020-08-15' Write down the ClientID Read the next row

كان علينا تخطي أول 338 وصولًا قبل تحديد موقع أول وصول إلى فندقنا. بعد ذلك ، ذهبنا أكثر من 103 من الوافدين في وقت سابق لتحديد موقع الأول في 13 أغسطس. أخيرًا ، قمنا بنسخ 46 قيمة متتالية من ClientID . لقد ساعدنا ذلك في الخطوة الثالثة ، تمكنا من نسخ مجموعة من المعرفات المتتالية. من المؤسف أننا لم نتمكن من القفز بطريقة ما إلى الصف الأول من تلك الكتلة.

المقاربة 3: مرتبة حسب الفندق فقط

جرب الآن نفس التمرين باستخدام جدول البيانات الذي تم طلبه بواسطة HotelID فقط.

الخوارزمية المطبقة على الجدول الذي تم طلبه بواسطة HotelID فقط أقل كفاءة مما كانت عليه عندما نقوم بالفرز حسب HotelID و DateFrom (بهذا الترتيب):

 -- Assumption: Sorted according to HotelID -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Prepare the list While HotelID = 3 If DateFrom between '2020-08-13' and '2020-08-14' Write down the ClientID Read the next row

في هذه الحالة ، يتعين علينا قراءة جميع الوافدين البالغ عددهم 166 إلى الفندق HotelID تعريف الفندق 3 ، ولكل منهم ، للتحقق مما إذا كان DateFrom ينتمي إلى الفترة الزمنية المطلوبة.

المقاربة 4: مرتبة حسب التاريخ ، ثم الفندق

هل يهم حقًا ما إذا كنا نفرز أولاً حسب HotelID ثم DateFrom أو العكس؟ دعنا نكتشف ما يلي: حاول الفرز أولاً حسب DateFrom ، ثم حسب HotelID .

 -- Assumption: Sorted according to DateFrom and HotelID -- Find the first arrival on 13th of August While DateFrom < '2020-08-13' Read the next row --Find the first arrival at the Hotel While HotelID < 3 and DateFrom < '2020-08-15' Read the next row Repeat If HotelID = 3 Write down the ClientID Read the next row Until DateFrom > '2020-08-14' or (DateFrom = '2020-08-14' and HotelID > 3)

حددنا الصف الأول بالتاريخ المناسب ، ثم اقرأ المزيد حتى حددنا موقع الوصول الأول إلى الفندق. بعد ذلك ، بالنسبة لعدد من الصفوف ، تم استيفاء الشرطين ، التاريخ الصحيح والفندق المناسب. ومع ذلك ، بعد الوصول إلى الفندق 3 ، وصلنا إلى الفنادق 4 و 5 وما إلى ذلك في نفس التاريخ. بعدهم ، كان علينا قراءة الصفوف مرة أخرى في اليوم التالي للفنادق 1 و 2 ، حتى تمكنا من قراءة حالات الوصول المتتالية إلى الفندق الذي نهتم به.

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

يحتوي النهج 4 على بيانات مطابقة تمامًا في كتلتين ، ولكن النهج 2 هو الوحيد الذي تكون فيه البيانات المستهدفة كلها في كتلة واحدة متتالية.

بالأرقام ، من الواضح أن للمقترب 2 أكثر المزايا في هذه الحالة.

وأوضح فهارس SQL: الاستنتاجات وماذا بعد

يؤدي القيام بهذه التمارين إلى توضيح النقاط التالية:

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

الآن ، تبدو النسخة المصنفة من الجدول مثل فهرس قاعدة البيانات تقريبًا. يغطي المقال التالي في شرح فهارس SQL تطبيق فهرس بدائي. شكرا للقراءة!