التحسين باستخدام الفهارس

مكتمل

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

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

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

أنواع الفهارس المختلفة تخدم أغراضا مختلفة.

استخدام فهارس الصفوف

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

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

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

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

-- Create clustered index on primary key (defines physical row order)
CREATE CLUSTERED INDEX IX_Product_ProductID 
ON Product(ProductID);

-- Create non-clustered index on frequently searched column
CREATE NONCLUSTERED INDEX IX_Product_Category 
ON Product(Category) 
INCLUDE (ProductName, Price);

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

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

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

فهم فهارس columnstore

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

فهم بنية columnstore

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

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

الجدول التالي يصف التوصية بمؤشرات columnstore:

السيناريو	Recommendation	السبب
جداول حقائق مستودع البيانات	استخدم columnstore	الجداول التي تحتوي على ملايين+ صفوف تستخدم للتحليلات تستفيد من التخزين العمودي والضغط
قواعد بيانات التقارير	استخدم columnstore	أحمال العمل الثقيلة بالقراءة مع الاستعلامات المجمعة تعمل بشكل أسرع مع الوصول الموجه للعمود
البيانات التاريخية	استخدم columnstore	البيانات المؤرشفة التي نادرا ما تحدثها ولكن تحللها بشكل متكرر تحقق نسب ضغط عالية
جداول صغيرة (<مليون صف)	تجنب عمود التخزين	النفقات العامة تفوق الفوائد؛ تحتاج مجموعات الصفوف إلى صفوف كافية للضغط الفعال
التحديثات/الحذف عالية التردد	تجنب عمود التخزين	تقوم التعديلات بوضع علامة على الصفوف كمحذوفة بدلا من التحديث في مكانها، مما يسبب التجزئة
البحث في صف واحد	تجنب عمود التخزين	فهارس Rowstore أسرع لاسترجاع السجلات الفردية

استخدم مؤشر مخزن الأعمدة المجمع (CCI)

مؤشر مخزن الأعمدة المجمع (CCI) هو نوع من فهرس متاجر الأعمدة يصبح الهيكل الأساسي للتخزين للجدول بأكمله، ليحل محل أي فهرس مخازن صفوف مجمع موجود. على عكس فهرس عمود غير مجمع (NCCI)، الذي ينشئ نسخة عمودية ثانوية بجانب جدول الصفوف، يخزن CCI جميع بيانات الجدول حصريا بصيغة عمودية.

هذا يعني أن الطاولة لا تحتوي على تخزين تقليدي قائم على الصفوف — حيث يقوم المحرك بضغط وتخزين كل عمود بشكل منفصل. كل من CCI وNCCI يستخدمان نفس تحسينات الضغط العمودي ومعالجة الدفعات (Batch Pressure Optimization)، لكن يستخدمان CCI عندما يكون التحليل هو العبء الأساسي ولا تحتاج إلى أنماط وصول المعاملات على مستوى الصف. على النقيض من ذلك، يتيح لك NCCI الحفاظ على فهارس متاجر الصفوف للاستعلامات المعاملية مع توفير هيكل عمودي للاستعلامات التحليلية على نفس الجدول.

يمكنك إنشاء فهرس مخزن الأعمدة المجمع باستخدام الجملة CREATE CLUSTERED COLUMNSTORE INDEX . إليك مثال:

-- Create clustered columnstore index (replaces clustered rowstore)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesHistory
ON SalesHistory;

-- Rebuild to improve compression
ALTER INDEX CCI_SalesHistory ON SalesHistory REBUILD;

استخدم مؤشر العمود غير المجمع (NCCI)

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

يمكنك إنشاء فهرس columnstore غير مجمع باستخدام الجملة CREATE NONCLUSTERED COLUMNSTORE INDEX . إليك مثال:

-- Create non-clustered columnstore for analytics
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Product_Analytics
ON Product(Price, StockQuantity, Category, ProductName);

مراقبة فهارس columnstore

يمكنك مراقبة صحة وأداء فهارس العمود الخاصة بك من خلال الاستعلام عن عرض الإدارة الديناميكي sys.dm_db_column_store_row_group_physical_stats .

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

-- Check columnstore health
SELECT 
    object_name(object_id) AS TableName,
    state_desc,
    total_rows,
    deleted_rows,
    size_in_bytes / 1024 / 1024 AS SizeMB
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('SalesHistory');

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