ماهو - مبادئ قواعد البيانات sql




كيف تعمل فهرسة قاعدة البيانات؟ (6)

وصف بسيط!

الفهرس ليس سوى بنية بيانات تخزن القيم لعمود معين في جدول. يتم إنشاء فهرس في عمود جدول.

مثال: لدينا جدول قاعدة بيانات يسمى User مع ثلاثة أعمدة - Name Age Address . افترض أن جدول User يحتوي على آلاف الصفوف.

الآن ، دعنا نقول أننا نريد تشغيل استعلام للعثور على جميع تفاصيل أي مستخدمين يدعى "جون". إذا قمنا بتشغيل الاستعلام التالي:

SELECT * FROM User 
WHERE Name = 'John'

سيتعين على برنامج قاعدة البيانات بشكل حرفي أن ينظر إلى كل صف في جدول User لمعرفة ما إذا كان Name هذا الصف هو "جون". هذا وسوف يستغرق وقتا طويلا.

هذا هو المكان الذي يساعدنا index فيه: يتم استخدام الفهرس لتسريع استعلامات البحث عن طريق خفض عدد السجلات / الصفوف في الجدول الذي يجب فحصه بشكل أساسي .

كيفية إنشاء فهرس:

CREATE INDEX name_index
ON User (Name)

يتكون index من قيم الأعمدة (على سبيل المثال: John) من جدول واحد ، ويتم تخزين هذه القيم في بنية بيانات .

حتى الآن ستستخدم قاعدة البيانات الفهرس للعثور على موظفين يدعى جون لأنه من المفترض أن يتم فرز الفهرس أبجديًا حسب اسم المستخدمين. ونظرًا لتصنيفها ، فهذا يعني أن البحث عن اسم أسرع كثيرًا لأن جميع الأسماء التي تبدأ بحرف "J" ستكون بجوار بعضها البعض في الفهرس!

نظرًا لأن الفهرسة مهمة للغاية حيث يزداد حجم مجموعة البيانات الخاصة بك ، هل يمكن لأي شخص أن يشرح كيف تعمل الفهرسة على مستوى قواعد البيانات؟

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


الآن ، دعنا نقول أننا نريد تشغيل استعلام للعثور على جميع تفاصيل أي موظف يدعى "Abc"؟

SELECT * FROM Employee 
WHERE Employee_Name = 'Abc'

ماذا سيحدث بدون فهرس؟

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

كيف يمكن أن يساعد فهرس قاعدة البيانات في الأداء؟

بيت القصيد من وجود فهرس هو تسريع استعلامات البحث عن طريق خفض عدد السجلات / الصفوف في الجدول الذي يحتاج إلى فحص. الفهرس هو بنية بيانات (غالبًا شجرة B) تخزن القيم لعمود معين في جدول.

كيف يعمل مؤشر B-tree؟

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

كيف يعمل فهرس جدول التجزئة؟

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

على سبيل المثال ، يمكن أن يستفيد الاستعلام الذي ناقشناه سابقًا من فهرس تجزئة تم إنشاؤه في العمود Employee_Name. الطريقة التي يعمل بها فهرس التجزئة هي أن قيمة العمود ستكون المفتاح في جدول التجزئة وأن القيمة الفعلية المعينة لهذا المفتاح ستكون مجرد مؤشر لبيانات الصف في الجدول. نظرًا لأن جدول التجزئة يمثل في الأساس صفيفًا ترابطيًا ، سيبدو الإدخال النموذجي مثل "Abc => 0x28939 ″ ، حيث 0x28939 هو مرجع إلى صف الجدول حيث يتم تخزين Abc في الذاكرة. من الواضح أن البحث عن قيمة مثل "Abc" في فهرس جدول التجزئة واستعادة مرجع للصف الموجود في الذاكرة هو أسرع بكثير من مسح الجدول للعثور على جميع الصفوف بقيمة "Abc" في العمود Employee_Name.

مساوئ مؤشر التجزئة

لا يتم فرز جداول التجزئة هياكل البيانات ، وهناك العديد من أنواع الاستعلامات التي لا يمكن أن تساعد فهارس التجزئة. على سبيل المثال ، افترض أنك تريد معرفة جميع الموظفين الذين تقل أعمارهم عن 40 عامًا. كيف يمكنك أن تفعل ذلك مع فهرس جدول التجزئة؟ حسنًا ، هذا غير ممكن لأن جدول التجزئة مفيد فقط للبحث عن أزواج القيمة الرئيسية - مما يعني الاستعلامات التي تحقق المساواة

ما هو بالضبط داخل فهرس قاعدة البيانات؟ لذلك ، أنت تعرف الآن أن فهرس قاعدة البيانات يتم إنشاؤه على عمود في جدول ، وأن الفهرس يخزن القيم في هذا العمود المحدد. ولكن ، من المهم أن نفهم أن فهرس قاعدة البيانات لا يخزن القيم في الأعمدة الأخرى في نفس الجدول. على سبيل المثال ، إذا أنشأنا فهرسًا في عمود Employee_Name ، فهذا يعني أن قيم العمود Employee_Age و Employee_Address لا يتم تخزينها أيضًا في الفهرس. إذا قمنا بتخزين جميع الأعمدة الأخرى في الفهرس ، فسيكون الأمر مثل إنشاء نسخة أخرى من الجدول بأكمله - والتي ستستهلك مساحة كبيرة جدًا وستكون غير فعالة للغاية.

كيف تعرف قاعدة البيانات متى تستخدم الفهرس؟ عند تشغيل استعلام مثل "SELECT * FROM Employee WHERE Employee_Name =" Abc "" ، ستتحقق قاعدة البيانات لمعرفة ما إذا كان هناك فهرس في العمود (الأعمدة) يتم الاستعلام عنه. على افتراض أن عمود Employee_Name يحتوي على فهرس تم إنشاؤه عليه ، فسوف يتعين على قاعدة البيانات أن تقرر ما إذا كان من المنطقي بالفعل استخدام الفهرس للعثور على القيم التي يتم البحث فيها - لأن هناك بعض السيناريوهات التي يكون فيها استخدام قاعدة البيانات أقل فعالية بالفعل ، وأكثر كفاءة فقط لمسح الجدول بأكمله.

ما هي تكلفة وجود فهرس قاعدة البيانات؟

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

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

أنظر أيضا

  1. ما الأعمدة التي تجعل الفهارس جيدة بشكل عام؟
  2. كيف تعمل فهارس قاعدة البيانات

المثال الكلاسيكي "الفهرس في الكتب"

النظر في "كتاب" من 1000 صفحة ، مقسوما على 100 قسم ، كل قسم مع صفحات X.

بسيط ، هاه؟

الآن ، بدون صفحة فهرس ، للعثور على قسم معين يبدأ بالحرف "S" ، لا يوجد لديك خيار آخر سوى البحث في الكتاب بأكمله. أي: 1000 صفحة

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

ولكن بعد ذلك ، بالإضافة إلى 1000 صفحة ، ستحتاج إلى 10 صفحات أخرى لعرض صفحة الفهرس ، حتى 1010 صفحة.

وبالتالي ، فإن الفهرس عبارة عن قسم منفصل يقوم بتخزين قيم العمود المفهرسة + المؤشر إلى الصف المفهرسة بترتيب فرز للبحث الفعال.

الأمور بسيطة في المدارس ، أليس كذلك؟ : P


في المرة الأولى التي قرأت فيها هذا كان مفيدًا جدًا لي. شكرا لك.

منذ ذلك الحين اكتسبت بعض المعرفة حول الجانب السلبي لإنشاء الفهارس: إذا كنت تكتب في جدول ( UPDATE أو INSERT ) مع فهرس واحد ، فأنت بالفعل لديك عمليتي كتابة في نظام الملفات. واحد لبيانات الجدول والآخر لبيانات الفهرس (واللجوء إليها (و- إذا تم تجميعها- اللجوء إلى بيانات الجدول)). إذا كان الجدول والفهرس موجودان على نفس القرص الثابت ، فإن هذا يكلف المزيد من الوقت. وبالتالي فإن الجدول الذي لا يحتوي على فهرس (كومة) ، سيسمح بعمليات كتابة أسرع. (إذا كان لديك فهرسان ، فسينتهي بك الأمر إلى ثلاث عمليات كتابة وما إلى ذلك)

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

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

في بعض السيناريوهات ، يكون الكومة أكثر فائدة من جدول به فهارس ،

على سبيل المثال: - إذا كان لديك الكثير من عمليات الكتابة المتنافسة ولكن يمكنك قراءة ليلة واحدة فقط خارج ساعات العمل للإبلاغ.

كما أن التمييز بين الفهارس المجمعة وغير المجمعة أمر مهم إلى حد ما.

ساعدني: - ماذا يعني مؤشر متفاوت المسافات وغير متفاوت؟


مجرد التفكير في فهرس قاعدة البيانات بمثابة فهرس للكتاب.

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

هناك خيار آخر وهو أنه يمكنك فقط الانتقال إلى قسم الفهرس في الكتاب ثم العثور على ما تبحث عنه باستخدام اسم الكيان الذي تبحث عنه (في هذه الحالة ، الرعاة الألمان) وكذلك النظر في رقم الصفحة إلى العثور بسرعة على ما تبحث عنه.

في قاعدة البيانات ، يشار إلى رقم الصفحة كمؤشر يقوم بتوجيه قاعدة البيانات إلى العنوان على القرص حيث يوجد الكيان. باستخدام نفس تشبيه German Shepherd ، قد يكون لدينا شيء مثل هذا ("German Shepherd" ، 0x77129) حيث 0x77129 هو العنوان الموجود على القرص حيث يتم تخزين بيانات الصف لـ German Shepherd.

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


لماذا هو مطلوب؟

عندما يتم تخزين البيانات على أجهزة التخزين المستندة إلى القرص ، يتم تخزينها ككتل من البيانات. يتم الوصول إلى هذه الكتل بالكامل ، مما يجعلها عملية الوصول إلى القرص الذري. يتم هيكلة كتل الأقراص بنفس طريقة القوائم المرتبطة ؛ كلاهما يحتوي على قسم للبيانات ، مؤشر إلى موقع العقدة التالية (أو الكتلة) ، وكلاهما لا يحتاجان إلى التخزين بشكل متواصل.

نظرًا لحقيقة أنه لا يمكن فرز عدد من السجلات إلا في حقل واحد ، يمكننا أن نقول أن البحث في حقل غير مصنّف يتطلب بحثًا خطيًا يتطلب وصول كتلة N/2 (في المتوسط) ، حيث N هو عدد الكتل التي يمتد الجدول. إذا كان هذا الحقل عبارة عن حقل غير مفتاح (على سبيل المثال لا يحتوي على مدخلات فريدة) ، فيجب البحث في مساحة الجدول بأكملها عند الوصول إلى N block.

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

ما هو الفهرسة؟

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

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

كيف يعمل؟

أولاً ، دعنا نسرد مخطط جدول قاعدة بيانات نموذجي ؛

Field name       Data type      Size on disk
id (Primary key) Unsigned INT   4 bytes
firstName        Char(50)       50 bytes
lastName         Char(50)       50 bytes
emailAddress     Char(100)      100 bytes

ملاحظة : تم استخدام char بدلاً من varchar للسماح بحجم دقيق على قيمة القرص. تحتوي قاعدة بيانات العينة هذه على خمسة ملايين صف وهي غير مرتبطة. سيتم الآن تحليل أداء العديد من الاستعلامات. هذه عبارة عن استعلام يستخدم المعرف (حقل مفتاح تم الفرز) والآخر يستخدم الاسم الأول (حقل غير مصنّف غير مفتاح).

مثال 1 - مرتبة مقابل الحقول غير المصنفة

بالنظر إلى نموذج قاعدة البيانات الخاص بنا الذي يساوي r = 5,000,000 سجلات ذات حجم ثابت مع إعطاء طول سجل R = 204 بايت ويتم تخزينها في جدول باستخدام محرك MyISAM الذي يستخدم حجم الكتلة الافتراضي B = 1,024 بايت. سيكون عامل حظر الجدول bfr = (B/R) = 1024/204 = 5 سجلات لكل كتلة قرص. إجمالي عدد الكتل المطلوبة لعقد الجدول هو N = (r/bfr) = 5000000/5 = 1,000,000 كتل.

يتطلب البحث الخطي في حقل المعرف معدل N/2 = 500,000 الوصول إلى الكتلة للعثور على قيمة ، بالنظر إلى أن حقل المعرف هو حقل رئيسي. ولكن نظرًا لأن حقل المعرف مرتب أيضًا ، فيمكن إجراء بحث ثنائي يتطلب متوسط log2 1000000 = 19.93 = 20 وصول كتلة. على الفور يمكننا أن نرى هذا هو تحسن كبير.

الآن لا يتم فرز حقل الاسم الأول أو حقل المفتاح ، لذا فإن البحث الثنائي أمر مستحيل ، ولا القيم فريدة ، وبالتالي سيتطلب الجدول البحث حتى النهاية للوصول إلى كتلة N = 1,000,000 دقيقة. هذا هو الموقف الذي تهدف الفهرسة إلى تصحيحه.

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

Field name       Data type      Size on disk
firstName        Char(50)       50 bytes
(record pointer) Special        4 bytes

ملاحظة : يبلغ طول المؤشرات في MySQL 2 أو 3 أو 4 أو 5 بايت حسب حجم الجدول.

مثال 2 - الفهرسة

بالنظر إلى نموذج قاعدة البيانات الخاص بنا الذي يساوي r = 5,000,000 سجلات يبلغ طول سجل الفهرس R = 54 بايت وباستخدام حجم الكتلة الافتراضي B = 1,024 بايت. سيكون عامل حظر الفهرس bfr = (B/R) = 1024/54 = 18 سجلًا لكل كتلة قرص. إجمالي عدد الكتل المطلوبة للاحتفاظ N = (r/bfr) = 5000000/18 = 277,778 هو N = (r/bfr) = 5000000/18 = 277,778 كتل.

الآن يمكن استخدام البحث باستخدام الحقل firstName الاستفادة من الفهرس لزيادة الأداء. هذا يسمح بالبحث الثنائي للفهرس بمتوسط log2 277778 = 18.08 = 19 كتلة الوصول. للعثور على عنوان السجل الفعلي ، والذي يتطلب وصول كتلة آخر للقراءة ، وبذلك يصل المجموع إلى 19 + 1 = 20 كتلة وصول ، بعيدة كل البعد عن 1،000،000 الوصول إلى كتلة المطلوبة للعثور على تطابق الاسم الأول في الجدول غير المفهرسة .

متى يجب استخدامها؟

نظرًا لأن إنشاء فهرس يتطلب مساحة إضافية على القرص (277،778 قالبًا إضافيًا من المثال أعلاه ، زيادة بنسبة 28٪ تقريبًا) ، وأن عددًا كبيرًا جدًا من المؤشرات يمكن أن يسبب مشاكل ناشئة عن حدود حجم أنظمة الملفات ، يجب استخدام التفكير الدقيق لتحديد الخيار الصحيح حقول للفهرسة.

نظرًا لأن المؤشرات تستخدم فقط لتسريع البحث عن حقل مطابقة داخل السجلات ، فمن المنطقي أن تكون حقول الفهرسة المستخدمة فقط للإخراج مجرد مضيعة لمساحة القرص ووقت المعالجة عند إجراء عملية إدراج أو حذف ، وبالتالي يجب اجتنابها. أيضًا نظرًا لطبيعة البحث الثنائي ، تعد أهمية البيانات أو تفردها مهمة. ستؤدي فهرسة أحد الحقول التي تحتوي على رقم أساسي 2 إلى تقسيم البيانات إلى نصفين ، بينما ترجع القيمة الأساسية 1000 إلى حوالي 1000 سجل. باستخدام مثل هذه العلاقة الأساسية المنخفضة ، يتم تقليل الفعالية إلى نوع خطي ، وسيتجنب مُحسِّن الاستعلام استخدام الفهرس إذا كانت النسبة الأساسية أقل من 30٪ من رقم السجل ، مما يجعل الفهرس مضيعة للفضاء بشكل فعال.







database-indexes