فهارس التصميم

مكتمل

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

فهارس متفاوت المسافات

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

إشعار

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

فهارس غير متفاوتة المسافات

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

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

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

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

يمكن تعريف الفهارس غير متفاوتة المسافات والمتفاوتة المسافات على أنها فريدة، ما يعني أنه لا يمكن أن يكون هناك تكرار للقيم الرئيسية. يتم إنشاء الفهارس الفريدة تلقائيا عند إنشاء مفتاح أساسي أو قيد UNIQUE على جدول.

يركز هذا القسم على فهارس b-tree في SQL Server، والمعروفة أيضا باسم فهارس مخزن الصفوف. تمثل الصورة التالية البنية العامة لشجرة b:

تسمى كل صفحة في فهرس b-tree عقدة فهرس، وتسمى العقدة العلوية من شجرة b العقدة الجذر. تسمى العقد السفلية في الفهرس العقد الطرفية، ومجموعة العقد الطرفية هي مستوى الكائن الطرفي.

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

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

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

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

CREATE TABLE [HumanResources].[Employee](
     [BusinessEntityID] [int] NOT NULL,
     [NationalIDNumber] [nvarchar](15) NOT NULL,
     [LoginID] [nvarchar](256) NOT NULL,
     [OrganizationNode] [hierarchyid] NULL,
     [OrganizationLevel] AS ([OrganizationNode].[GetLevel]()),
     [JobTitle] [nvarchar](50) NOT NULL,
     [BirthDate] [date] NOT NULL,
     [MaritalStatus] [nchar](1) NOT NULL,
     [Gender] [nchar](1) NOT NULL,
     [HireDate] [date] NOT NULL,
     [SalariedFlag] [bit] NOT NULL,
     [VacationHours] [smallint] NOT NULL,
     [SickLeaveHours] [smallint] NOT NULL,
     [CurrentFlag] [bit] NOT NULL,
     [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
     [ModifiedDate] [datetime] NOT NULL)

في هذا الجدول، يوجد عمود يسمى CurrentFlag، يشير إلى ما إذا كان الموظف يعمل حاليا. يستخدم bit هذا المثال نوع البيانات، الذي يمثل قيمتين: واحدة للعاملين حاليا و صفر للعاملين حاليا. يسمح إنشاء فهرس تمت تصفيته باستخدام WHERE CurrentFlag = 1 في CurrentFlag العمود بالاستعلامات الفعالة للموظفين الحاليين.

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

مؤشرات تخزين الأعمدة

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

عادة ما تستخدم فهارس تخزين الأعمدة غير متفاوتة المسافات في سيناريوهين. الأول هو عندما لا يكون نوع بيانات العمود مدعوما في فهرس تخزين الأعمدة (على سبيل المثال، XML وCLR sql_variant وntext والنص والصورة). نظرا لأن فهرس تخزين الأعمدة متفاوت المسافات يحتوي دائما على جميع أعمدة الجدول، فإن الفهرس غير متفاوتة المسافات هو الخيار الوحيد. يتضمن السيناريو الثاني فهارس تمت تصفيتها، وتستخدم في بنيات المعالجة التحليلية للمعاملات المختلطة (HTAP)، حيث يتم تحميل البيانات في الجدول أثناء تشغيل التقارير في وقت واحد. تسمح تصفية الفهرس (عادة في حقل تاريخ) بأداء فعال للإدراج والإبلاغ.

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

تستفيد هذه الفهارس أيضا من وضع تنفيذ الدفعة، ومعالجة مجموعات الصفوف (عادة حوالي 900) في كل مرة بدلا من واحد تلو الآخر. يقلل هذا الأسلوب من إرشادات وحدة المعالجة المركزية بشكل كبير.

SELECT SUM(Sales) FROM SalesAmount;

يمكن أن يوفر وضع الدفعة زيادة في الأداء على معالجة الصفوف التقليدية. في حين أن وضع الدفعة ل rowstore لا يحتوي على نفس مستوى أداء القراءة مثل فهرس تخزين الأعمدة، قد ترى الاستعلامات التحليلية ما يصل إلى تحسين أداء 5 أضعاف.

ميزة أخرى من فهارس تخزين الأعمدة لأحمال عمل مستودع البيانات هي مسار التحميل الأمثل لعمليات الإدراج المجمع من 102400 صف أو أكثر. بينما 102,400 هو الحد الأدنى للقيمة لتحميلها مباشرة في مخزن الأعمدة، يمكن أن تصل كل مجموعة من الصفوف، تسمى مجموعة صفوف، إلى ما يصل إلى 1,024,000 صف تقريبا. إن وجود مجموعات صفوف أقل، ولكن أكثر اكتمالا، يجعل استعلاماتك SELECT أكثر كفاءة لأنه يجب فحص عدد أقل من مجموعات الصفوف لاسترداد السجلات المطلوبة. تحدث هذه الأحمال في الذاكرة ويتم تحميلها مباشرة في الفهرس. بالنسبة لوحدات التخزين الأصغر، تتم كتابة البيانات إلى بنية b-tree تسمى مخزن دلتا ويتم تحميلها بشكل غير متزامن في الفهرس.

في هذا المثال، يتم تحميل نفس البيانات في جدولين، FactResellerSales_CCI_Demo و FactResellerSales_Page_Demo. يحتوي FactResellerSales_CCI_Demo على فهرس columnstore متفاوت المسافات ، ويحتوي FactResellerSales_Page_Demo على فهرس b-tree متفاوت المسافات مع عمودين ويتم ضغط الصفحة. كما ترى ، يقوم كل جدول بتحميل 1،024،000 صف من جدول FactResellerSalesXL_CCI . عندما SET STATISTICS TIME يكون ON، يقوم SQL Server بتعقب الوقت المنقضي لتنفيذ الاستعلام. استغرق تحميل البيانات في جدول تخزين الأعمدة حوالي 8 ثوان، حيث استغرق التحميل في الجدول المضغوط للصفحة حوالي 20 ثانية. في هذا المثال، يتم تحميل جميع الصفوف التي تدخل في فهرس تخزين الأعمدة في مجموعة صفوف واحدة.

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