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

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

يبدو أن الاستنتاج المنطقي هو أننا يجب أن نحافظ على نسخ مرتبة - والتي سنسميها رسميًا فهارس SQL ، مسبوقة بـ IX_ - من جدول معين. ستكون خوارزميات الاسترجاع من المقالة الأولى قابلة للتطبيق ، ولن نحتاج إلى فرز الجدول قبل البدء.

الفهرس كنسخة مرتبة من الجدول

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

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

1. كيفية استرداد البيانات بشكل أكثر كفاءة عند استخدام فهارس منفصلة بدلاً من الجداول الأولية المصنفة
2. كيفية الحفاظ على الترتيب في كل فهرس وجدول عند تعديل البيانات

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

تمرين أولي: إلغاء الحجز

للتعرف على أداء فهرس SQL باستخدام إستراتيجية الجدول المصنف ، تتمثل مهمتك في حذف حجز لـ Client 12 ، اعتبارًا من 22 أغسطس 2020 ، في Hotel 4. ضع في اعتبارك أنه يتعين عليك إزالة صف من جميع نسخ الجدول والحفاظ على الترتيب الصحيح.

منجز؟ يجب أن يكون واضحًا أن فكرة الاحتفاظ بنسخ مرتبة متعددة من الجدول ليست جيدة كما تبدو. إذا كان لا يزال لديك أي شكوك ، فيمكنك أيضًا محاولة إعادة إدخال الحجز الذي حذفته للتو أو تغيير تاريخ حجز موجود.

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

فهارس SQL باستخدام عناوين الصفوف

يحتوي جدول البيانات هذا على فهارس تستخدم طريقة مختلفة. لا تزال صفوف الفهرس مصنفة وفقًا لمعايير محددة ، لكننا لا نحتفظ بجميع المعلومات الأخرى في صف الفهرس. بدلاً من ذلك ، نحتفظ فقط بـ "عنوان الصف" ، عنوان الصف في ورقة الحجوزات - يمثل الجدول نفسه - في العمود H.

تستخدم جميع تطبيقات RDBMS قدرة على مستوى نظام التشغيل للعثور بسرعة على الكتلة الموجودة على القرص باستخدام عنوان فعلي. تتكون عناوين الصف عادةً من عنوان كتلة بالإضافة إلى موضع الصف داخل الكتلة.

لنقم ببعض التمارين لمعرفة كيفية عمل تصميم الفهرس هذا.

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

كما في المقالة الأولى ، ستقوم بمحاكاة تنفيذ استعلام SQL التالي:

 SELECT * FROM Reservations WHERE ClientID = 12;

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

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

يتضمن الأسلوب الثاني قراءة البيانات من ورقة IX_ClientID ، ولأي عنصر يطابق المعايير ، والعثور على صف في جدول الحجز بناءً على قيمة rowAddress:

 Get first row from IX_ClientID where ClientID = 12 While IX_ClientID.ClientID = 12 Fetch Reservations.* where rowAddress = IX_ClientID.rowAddress Write down Reservations.* Get next row from IX_ClientID

هنا ، يتم تنفيذ تعبير Get first row from بواسطة حلقة مماثلة لتلك التي تم عرضها في المقالة السابقة:

 Repeat Fetch next row from IX_ClientID Until ClientID >= 12

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

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

يعتمد حجم القراءات على قيمة ClientID. لأكبر قيمة ، يجب عليك قراءة الفهرس بالكامل ، بينما بالنسبة لأدنى قيمة ، يكون في بداية الفهرس. متوسط ​​القيمة هو نصف عدد الصفوف.

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

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

تتمثل المهمة في حساب عدد مرات تسجيل الوصول في 16 أغسطس 2020 ، باستخدام تصميم الفهرس الجديد.

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

يتفوق نهج استخدام الفهرس المناسب للعد على فحص الجدول ، بغض النظر عن عدد الصفوف المعنية. والسبب هو أنه لا يتعين علينا الوصول إلى جدول الحجوزات على الإطلاق - لدينا جميع المعلومات التي نحتاجها في الفهرس نفسه:

 Count := 0 Get first row from IX_DateFrom where DateFrom >= '2020-08-16' While found and DateFrom < '2020-08-17' Count := Count + 1 Get next row from IX_DateFrom Write down Count

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

التمرين 3: التحقيق الجنائي (قائمة النزلاء المعطاة للفندق والنطاق الزمني)

دعنا نجهز قائمة بالضيوف الذين وصلوا إلى فندق 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;

يمكننا قراءة جميع الصفوف من جدول الحجوزات أو استخدام أحد الفهارس المتاحة. بعد القيام بنفس التمرين باستخدام جدول تم فرزه وفقًا لمعايير محددة ، اكتشفنا أن الفهرس IX_HotelID_DateFrom هو الأكثر كفاءة.

 Get first row from IX_HotelID_DateFrom where HotelID = 3 and DateFrom between '2020-08-13' and '2020-08-14' While found and DateFrom < '2020-08-15' and IX_HotelID_DateFrom.HotelID = 3 Fetch Reservations.* where rowAddress = IX_HotelID_DateFrom.rowAddress Write down Reservations.ClientID Get next row from IX_HotelID_DateFrom

هل يمكننا تصميم مؤشر أكثر كفاءة؟

نصل إلى الجدول نظرًا لقيمة ClientID ، وهي المعلومات الوحيدة التي نحتاجها لقائمة الضيوف الذين نبلغهم. إذا قمنا بتضمين هذه القيمة في فهرس SQL ، فلن نضطر إلى الوصول إلى الجدول على الإطلاق. حاول تحضير قائمة للقراءة فقط من هذا الفهرس ، IX_HotelID_DateFrom_ClientID :

 Get first row from IX_HotelID_DateFrom_ClientID where HotelID = 3 and DateFrom between '2020-08-13' and '2020-08-14' While found and HotelID = 3 and DateFrom < '2020-08-15' Write down ClientID Get next row from IX_HotelID_DateFrom_ClientID

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

التمرين 4: قائمة بأسماء الضيوف بدلاً من المعرفات

قد تكون قائمة معرفات الضيوف عديمة الفائدة لضابط شرطة يحقق في جريمة. نحتاج إلى تقديم الأسماء:

 SELECT c.ClientName FROM Reservations r JOIN Clients c ON r.ClientID = c.ClientID WHERE r.DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND r.HotelID = 3;

لتوفير قائمة ، إلى جانب البيانات من جدول Reservations ، نحتاج أيضًا إلى جدول Clients يحتوي على معلومات الضيف ، والتي يمكن العثور عليها في ورقة Google هذه.

هذا التمرين مشابه للتمرين السابق ، وكذلك النهج.

 Get first row from IX_HotelID_DateFrom_ClientID where HotelID = 3 and DateFrom between '2020-08-13' and '2020-08-14' While found and HotelID = 3 and DateFrom < '2020-08-15' Fetch Clients.* where ClientID = IX_HotelID_DateFrom_ClientID.ClientID Write down Clients.ClientName Get next row from IX_HotelID_DateFrom_ClientID

التعبير Fetch Clients.* where ClientID = IX_HotelID_DateFrom_ClientID.ClientID يمكن تنفيذه عن طريق فحص الجدول أو باستخدام فهرسنا. إذا استخدمنا مسحًا للجدول ، لكل ClientID من حلقة While ، فسنضطر إلى قراءة نصف الصفوف من جدول Clients في المتوسط:

 -- Get row from Clients using table scan Repeat Fetch next row from Clients Until ClientID = IX_HotelID_DateFrom_ClientID.ClientID or not found If found Write down ClientName

تطبيق الفهرس الذي درسناه حتى الآن - دعنا نطلق عليه تطبيق فهرس "ثابت" - لن يكون مفيدًا للغاية. سيتعين علينا قراءة نفس عدد الصفوف (على الرغم من وجود صفوف أصغر) من الفهرس ، ثم القفز إلى الصف في Clients باستخدام RowAddress :

 -- Get row from Clients using flat index Repeat Fetch next row from Clients_PK_Flat Until ClientID >= IX_HotelID_DateFrom_ClientID.ClientID If found Fetch Clients.* where rowAddress = Clients_PK_Flat.rowAddress Write down ClientName

ملاحظة: هنا ، يشير PK إلى "المفتاح الأساسي" ، وهو مصطلح سنستكشفه لاحقًا في السلسلة.

هل هناك طريقة لتحقيق ذلك دون الحاجة إلى قراءة الكثير من الصفوف؟ نعم - هذا هو بالضبط الغرض من فهارس B-tree.

فهارس شجرة متوازنة (ب شجرة)

دعنا نقسم الصفوف من Clients_PK_Flat إلى كتل من أربعة صفوف وننشئ قائمة تحتوي على قيمة IndexRowAddress ClientID . بنية بيانات فهرس قاعدة البيانات الناتجة — يمكنك العثور عليها في ورقة Clients_PK_2Levels. جرب كيف يساعدك الهيكل الجديد في العثور على عميل لديه ClientID 28. يجب أن تبدو الخوارزمية كما يلي:

 Fetch Level2.* Loop Leaf_address := Level3Address Exit when ClientID >= 28 Fetch next row from Level2 Fetch Level3.* where Level3Address = Leaf_address -- 3-21 Loop Client_address := RowAddress Exit when ClientID >= 28 Fetch next row from Level 3 Fetch Clients.* where rowAddress = Client_address -- 42 Write down Clients.*

ربما اكتشفت أنه يمكننا إضافة مستوى آخر. يتكون المستوى 1 من أربعة صفوف ، كما ترى في علامة التبويب IX_Clients_PK. للعثور على اسم الضيف مع معرف العميل 28 ، عليك قراءة ثلاث مجموعات (عقد) من البيانات - واحدة لكل مستوى - من بنية المفتاح الأساسي ، وأخيراً القفز إلى صف العملاء بالعنوان 42.

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

من الآن فصاعدًا ، سنعتبر أن كل فهرس له بنية B-tree ، على الرغم من أن جداول البيانات الخاصة بنا تحتوي فقط على إدخالات على مستوى الأوراق. أهم الحقائق التي يجب معرفتها عن B-Tree هي:

• هيكل مؤشر B-Tree هو المؤشر الأكثر استخدامًا من قبل جميع أنظمة RDBMS الرئيسية في السوق.
• يتم ترتيب جميع مستويات الشجرة المتوازنة حسب قيم الأعمدة الرئيسية.
• تتم قراءة البيانات من القرص في كتل.
• تحتوي عقدة B-tree واحدة على كتلة واحدة أو أكثر.
• العامل الأكثر أهمية الذي يؤثر على أداء الاستعلام هو عدد الكتل المقروءة من القرص.
• عدد العناصر في كل مستوى B-tree جديد ، بدءًا من الجذر ، وانتهاءً بمستوى الورقة ، يزداد أضعافاً مضاعفة.

التمرين 5: التحقيق الجنائي ، الجزء الثاني

الآن ، يبحث مفتش الشرطة عن قائمة بأسماء الضيوف المطابقين وتواريخ الوصول وأسماء الفنادق من جميع الفنادق في المدينة أ.

 SELECT h.HotelName, r.DateFrom as CheckInDate, c.ClientName FROM Reservations r JOIN Clients c ON r.ClientID = c.ClientID JOIN Hotels h ON r.HotelID = h.HotelID WHERE r.DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND h.City = 'A';

النهج 1

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

 For each row from IX_DateFrom_HotelID_ClientID where DateFrom between '2020-08-13' and '2020-08-14' For each row from Hotels where HotelID = IX_DateFrom_HotelID_ClientID.HotelID If Hotels.City = 'A' then Fetch Clients.* where ClientID = IX_HotelID_DateFrom_ClientID.ClientID Write down Hotels.HotelName, IX_HotelID_DateFrom_ClientID.DateFrom, Clients.ClientName

النهج 2

يمنحنا استخدام IX_HotelID_DateFrom_ClientID خطة تنفيذ أكثر كفاءة.

 For each row from Hotels where City = 'A' For each row from IX_HotelID_DateFrom_ClientID where HotelID = Hotels.HotelID and DateFrom between '2020-08-13' and '2020-08-14' Fetch Clients.* where ClientID = IX_HotelID_DateFrom_ClientID.ClientID Write down Hotels.HotelName, IX_HotelID_DateFrom_ClientID.DateFrom, Clients.ClientName

من جدول Hotels ، نجد جميع الفنادق من المدينة أ. بمعرفة معرف هذه الفنادق ، يمكننا قراءة العناصر التالية من فهرس IX_HotelID_DateFrom_ClientID . بهذه الطريقة ، بعد العثور على الصف الأول في مستوى أوراق الشجرة B لكل فندق وتاريخ ، لا نقرأ الحجوزات من الفنادق خارج المدينة A.

هنا ، يمكننا أن نرى أنه عندما يكون لدينا فهرس قاعدة بيانات مناسب لأهدافنا ، يمكن لضم إضافي أن يجعل الاستعلام أسرع.

ستتم معالجة بنية B-tree وكيفية تحديثها كلما تم إدراج صف أو تحديثه أو حذفه بمزيد من التفصيل عندما أشرح الدافع للتقسيم وتأثيره. النقطة المهمة هي أنه يمكننا اعتبار هذه العملية سريعة كلما استخدمنا فهرسًا.

استعلام الفهرس في SQL: التفاصيل تصنع الفارق

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

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

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