الفهارس في جداول تجمع SQL المخصص في Azure Synapse Analytics
توصيات وأمثلة بشأن فهرسة الجداول في تجمع SQL المخصص في Azure Synapse Analytics.
أنواع الفهارس
تجمع SQL المخصص يوفر العديد من خيارات الفهرسة، بما في ذلك فهارس تخزين الأعمدة لبيانات مجمعة والفهارس لبيانات مجمعة والفهارس لبيانات منفصلة وخيار بدون فهرس يعرف أيضًا باسم كومة.
لإنشاء جدول بفهرس، راجع وثائق إنشاء جدول (تجمع SQL مخصص).
فهارس تخزين الأعمدة لبيانات مجمعة
بشكل افتراضي، تقوم تجمعات SQL المخصصة بإنشاء فهرس تخزين الأعمدة لبيانات مجمعة عند عدم تحديد خيارات الفهرس في جدول. توفر جداول تخزين الأعمدة لبيانات مجمعة أعلى مستوى لضغط البيانات، وأفضل أداء للاستعلام بشكل عام على حدٍّ سواء. ستكون جداول تخزين الأعمدة لبيانات مجمعة بصفة عامة أفضل من الفهرس لبيانات مجمعة، وجداول الكومة، وعادةً ما تكون أفضل خيار للجداول الكبيرة. لهذه الأسباب، تخزين الأعمدة لبيانات مجمعة هو أفضل مكان للبدء عندما تكون غير متأكد من كيفية فهرسة جدولك.
لإنشاء جدول تخزين الأعمدة لبيانات مجمعة، ما عليك سوى تحديد CLUSTERED COLUMNSTORE INDEX في عبارة WITH، أو عدم استخدام عبارة WITH:
CREATE TABLE myTable
(
id int NOT NULL,
lastName varchar(20),
zipCode varchar(6)
)
WITH ( CLUSTERED COLUMNSTORE INDEX );
هناك بعض السيناريوهات التي لا يكون تخزين الأعمدة لبيانات مجمعة فيها خيارًا جيدًا:
- لا تدعم جداول تخزين الأعمدة varchar(max) وnvarchar(max) وvarbinary(max). ضع في اعتبارك كومة الذاكرة المؤقتة أو فهرس نظام المجموعة بدلاً من ذلك.
- قد تكون جداول تخزين الأعمدة أقل كفاءة للبيانات العابرة. ضع في اعتبارك كومة الذاكرة المؤقتة وربما حتى الجداول المؤقتة.
- جداول صغيرة بها أقل من 60 مليون صف. ضع في اعتبارك جداول كومة الذاكرة المؤقتة.
جداول الكومة
عندما تضع البيانات مؤقتًا في تجمع SQL مخصص، قد تجد أن استخدام جدول الكومة يسرع من العملية الإجمالية. وهذا لأن التحميلات إلى جداول الكومة أسرع من التحميلات إلى جداول الفهرس، وفي بعض الحالات يمكن إجراء القراءة اللاحقة من ذاكرة التخزين المؤقت. إذا كنت تقوم بتحميل البيانات فقط لتنظيمها قبل تشغيل المزيد من التحويلات، فإن تحميل الجدول إلى جدول الكومة أسرع بكثير من تحميل البيانات إلى جدول تخزين الأعمدة لبيانات مجمعة. بالإضافة إلى ذلك، فإن تحميل البيانات إلى جدول مؤقت يكون أسرع من تحميل جدول إلى تخزين دائم. بعد تحميل البيانات، يمكنك إنشاء فهارس في الجدول للحصول على أداء استعلام أسرع.
يبدأ تحقيق الضغط الأمثل لجداول تخزين الأعمدة لبيانات مجمعة عند وجود أكثر من 60 مليون صف. بالنسبة لجداول البحث الصغيرة، أقل من 60 مليون صف، ضع بعين الاعتبار استخدام HEAP أو الفهرس لبيانات مجمعة للحصول على أداء استعلام أسرع.
لإنشاء جدول كومة، ليس عليك سوى تحديد HEAP في عبارة WITH:
CREATE TABLE myTable
(
id int NOT NULL,
lastName varchar(20),
zipCode varchar(6)
)
WITH ( HEAP );
إشعار
إذا كنت تقوم بإجراء INSERTUPDATEعمليات أو أو بشكل DELETE متكرر على جدول كومة الذاكرة المؤقتة، فمن المستحسن تضمين إعادة إنشاء الجدول في جدول الصيانة باستخدام ALTER TABLE الأمر . على سبيل المثال، ALTER TABLE [SchemaName].[TableName] REBUILD تساهم هذه الممارسة في تقليل التجزئة، ما يؤدي إلى تحسين الأداء أثناء عمليات القراءة.
الفهارس لبيانات مجمعة والفهارس لبيانات منفصلة
يكون استخدام الفهارس لبيانات مجمعة أفضل من جداول تخزين الأعمدة لبيانات مجمعة، وذلك عند الحاجة إلى استرداد صف واحد بسرعة. بالنسبة للاستعلامات التي يلزم فيها إجراء بحث عن صف واحد أو عدد قليل جدًا بأقصى سرعة، استخدم الفهرس لبيانات مجمعة أو الفهرس الثانوي لبيانات منفصلة. ما يعيب استخدام الفهرس لبيانات مجمعة هو أن فقط الاستعلامات التي تستخدم عامل تصفية انتقائي للغاية في عمود الفهرس لبيانات مجمعة هي التي تستفيد. يمكن إضافة فهرس لبيانات منفصلة إلى أعمدة أخرى لتحسين عامل التصفية على أعمدة أخرى. ولكن، كل فهرس تتم إضافته إلى جدول يضيف كلاً من المساحة ووقت المعالجة إلى الأحمال.
لإنشاء جدول فهرس لبيانات مجمعة، ليس عليك سوى تحديد CLUSTERED INDEX في عبارة WITH:
CREATE TABLE myTable
(
id int NOT NULL,
lastName varchar(20),
zipCode varchar(6)
)
WITH ( CLUSTERED INDEX (id) );
لإضافة فهرس لبيانات منفصلة على جدول، استخدم بناء الجملة التالي:
CREATE INDEX zipCodeIndex ON myTable (zipCode);
تحسين فهارس تخزين الأعمدة لبيانات مجمعة
جداول تخزين الأعمدة لبيانات مجمعة تنظم البيانات في شرائح. يُعد وجود جودة عالية للشرائح أمرًا بالغ الأهمية لتحقيق الأداء الأمثل للاستعلام في جدول تخزين الأعمدة. يمكن قياس جودة الشريحة بعدد الصفوف في مجموعة صفوف مضغوطة. تكون أفضل جودة للشرائح عندما يكون هناك ما لا يقل عن 100 ألف صف في كل مجموعة صفوف مضغوطة ويكون أداؤها أفضل عندما يقترب عدد الصفوف لكل مجموعة صفوف من 1,048,576 صفًا، وهو أكبر عدد من الصفوف التي يمكن أن تحتوي عليها مجموعة الصفوف.
طريقة العرض أدناه يمكن إنشاؤها واستخدامها على النظام لحساب متوسط الصفوف لكل مجموعة صفوف وتحديد أي فهارس تخزين الأعمدة لبيانات مجمعة دون المستوى الأمثل. العمود الأخير في طريقة العرض هذه ينشئ عبارة SQL التي يمكن استخدامها لإعادة إنشاء الفهارس.
CREATE VIEW dbo.vColumnstoreDensity
AS
SELECT
GETDATE() AS [execution_date]
, DB_Name() AS [database_name]
, s.name AS [schema_name]
, t.name AS [table_name]
, MAX(p.partition_number) AS [table_partition_count]
, SUM(rg.[total_rows]) AS [row_count_total]
, SUM(rg.[total_rows])/COUNT(DISTINCT rg.[distribution_id]) AS [row_count_per_distribution_MAX]
, CEILING((SUM(rg.[total_rows])*1.0/COUNT(DISTINCT rg.[distribution_id]))/1048576) AS [rowgroup_per_distribution_MAX]
, SUM(CASE WHEN rg.[State] = 0 THEN 1 ELSE 0 END) AS [INVISIBLE_rowgroup_count]
, SUM(CASE WHEN rg.[State] = 0 THEN rg.[total_rows] ELSE 0 END) AS [INVISIBLE_rowgroup_rows]
, MIN(CASE WHEN rg.[State] = 0 THEN rg.[total_rows] ELSE NULL END) AS [INVISIBLE_rowgroup_rows_MIN]
, MAX(CASE WHEN rg.[State] = 0 THEN rg.[total_rows] ELSE NULL END) AS [INVISIBLE_rowgroup_rows_MAX]
, AVG(CASE WHEN rg.[State] = 0 THEN rg.[total_rows] ELSE NULL END) AS [INVISIBLE_rowgroup_rows_AVG]
, SUM(CASE WHEN rg.[State] = 1 THEN 1 ELSE 0 END) AS [OPEN_rowgroup_count]
, SUM(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] ELSE 0 END) AS [OPEN_rowgroup_rows]
, MIN(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] ELSE NULL END) AS [OPEN_rowgroup_rows_MIN]
, MAX(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] ELSE NULL END) AS [OPEN_rowgroup_rows_MAX]
, AVG(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] ELSE NULL END) AS [OPEN_rowgroup_rows_AVG]
, SUM(CASE WHEN rg.[State] = 2 THEN 1 ELSE 0 END) AS [CLOSED_rowgroup_count]
, SUM(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] ELSE 0 END) AS [CLOSED_rowgroup_rows]
, MIN(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] ELSE NULL END) AS [CLOSED_rowgroup_rows_MIN]
, MAX(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] ELSE NULL END) AS [CLOSED_rowgroup_rows_MAX]
, AVG(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] ELSE NULL END) AS [CLOSED_rowgroup_rows_AVG]
, SUM(CASE WHEN rg.[State] = 3 THEN 1 ELSE 0 END) AS [COMPRESSED_rowgroup_count]
, SUM(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] ELSE 0 END) AS [COMPRESSED_rowgroup_rows]
, SUM(CASE WHEN rg.[State] = 3 THEN rg.[deleted_rows] ELSE 0 END) AS [COMPRESSED_rowgroup_rows_DELETED]
, MIN(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] ELSE NULL END) AS [COMPRESSED_rowgroup_rows_MIN]
, MAX(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] ELSE NULL END) AS [COMPRESSED_rowgroup_rows_MAX]
, AVG(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] ELSE NULL END) AS [COMPRESSED_rowgroup_rows_AVG]
, 'ALTER INDEX ALL ON ' + s.name + '.' + t.NAME + ' REBUILD;' AS [Rebuild_Index_SQL]
FROM sys.[dm_pdw_nodes_db_column_store_row_group_physical_stats] rg
JOIN sys.[pdw_nodes_tables] nt ON rg.[object_id] = nt.[object_id]
AND rg.[pdw_node_id] = nt.[pdw_node_id]
AND rg.[distribution_id] = nt.[distribution_id]
JOIN sys.[pdw_permanent_table_mappings] mp ON nt.[name] = mp.[physical_name]
JOIN sys.[tables] t ON mp.[object_id] = t.[object_id]
JOIN sys.[schemas] s ON t.[schema_id] = s.[schema_id]
JOIN sys.[partitions] p ON P.object_id = t.object_id
GROUP BY
s.[name]
, t.[name];
الآن وبعد إنشاء طريقة العرض، قم بتشغيل هذا الاستعلام لتحديد الجداول ذات مجموعات الصفوف التي تحتوي على أقل من 100 ألف صف. قد ترغب في زيادة حد 100 ألف إذا كنت تبحث عن جودة شريحة أكثر مثالية.
SELECT *
FROM [dbo].[vColumnstoreDensity]
WHERE COMPRESSED_rowgroup_rows_AVG < 100000
OR INVISIBLE_rowgroup_rows_AVG < 100000;
بمجرد تشغيل الاستعلام، يمكنك البدء في النظر إلى البيانات وتحليل النتائج. هذا الجدول يشرح ما يجب البحث عنه في تحليل مجموعة الصفوف.
| Column | كيفية استخدام هذه البيانات |
|---|---|
| [table_partition_count] | إذا كان الجدول مقسما، فقد تتوقع رؤية عدد أعلى من مجموعات الصفوف المفتوحة. يمكن أن يكون لكل قسم في التوزيع نظريا مجموعة صفوف مفتوحة مقترنة به. قم بمراعاة ذلك في تحليلك. يمكن تحسين جدول صغير تم تقسيمه عن طريق إزالة التقسيم تماما لأن هذا من شأنه تحسين الضغط. |
| [row_count_total] | إجمالي عدد الصفوف للجدول. على سبيل المثال، يمكنك استخدام هذه القيمة لحساب النسبة المئوية للصفوف في الحالة المضغوطة. |
| [row_count_per_distribution_MAX] | إذا تم توزيع كافة الصفوف بالتساوي، فإن هذه القيمة ستكون العدد المستهدف للصفوف لكل توزيع. قارن هذه القيمة مع compressed_rowgroup_count. |
| [COMPRESSED_rowgroup_rows] | إجمالي عدد الصفوف بتنسيق مخزن الأعمدة للجدول. |
| [COMPRESSED_rowgroup_rows_AVG] | إذا كان متوسط عدد الصفوف أقل بكثير من الحد الأقصى لعدد الصفوف لمجموعة صفوف، ففكر في استخدام CTAS أو ALTER INDEX REBUILD لإعادة ضغط البيانات |
| [COMPRESSED_rowgroup_count] | عدد مجموعات الصفوف بتنسيق مخزن الأعمدة. إذا كان هذا الرقم مرتفعًا جدًا فيما يتعلق بالجدول، فهو مؤشر على انخفاض كثافة تخزين الأعمدة. |
| [COMPRESSED_rowgroup_rows_DELETED] | يتم حذف الصفوف منطقيا بتنسيق مخزن الأعمدة. إذا كان الرقم مرتفعا بالنسبة لحجم الجدول، ففكر في إعادة إنشاء القسم أو إعادة إنشاء الفهرس لأن هذا يزيله فعليا. |
| [COMPRESSED_rowgroup_rows_MIN] | استخدم هذا مع العمودين AVG وMAX لفهم نطاق القيم لمجموعات الصفوف في مخزن الأعمدة الخاص بك. يشير العدد المنخفض فوق حد التحميل (102400 لكل توزيع محاذاة للقسم) إلى توفر تحسينات في تحميل البيانات |
| [COMPRESSED_rowgroup_rows_MAX] | كما هو موضح أعلاه |
| [OPEN_rowgroup_count] | مجموعات الصفوف المفتوحة عادية. يتوقع المرء بشكل معقول مجموعة صفوف OPEN واحدة لكل توزيع جدول (60). تشير الأرقام الزائدة إلى تحميل البيانات عبر الأقسام. تحقق مرة ثانية من استراتيجية التقسيم للتأكد من سلامتها |
| [OPEN_rowgroup_rows] | يمكن أن تحتوي كل مجموعة صفوف على 1048576 صفا كحد أقصى. استخدم هذه القيمة لمعرفة مدى امتلاء مجموعات الصفوف المفتوحة حاليا |
| [OPEN_rowgroup_rows_MIN] | تشير المجموعات المفتوحة إلى أن البيانات إما يتم تحميلها في الجدول أو أن التحميل السابق تم تمديده عبر الصفوف المتبقية في مجموعة الصفوف هذه. استخدم أعمدة MIN وMAX وAVG لمعرفة مقدار البيانات التي يتم جلوسها في مجموعات الصفوف المفتوحة. بالنسبة للجداول الصغيرة، يمكن أن تكون 100% من جميع البيانات! في هذه الحالة ALTER INDEX REBUILD لفرض البيانات على تخزين الأعمدة. |
| [OPEN_rowgroup_rows_MAX] | كما هو موضح أعلاه |
| [OPEN_rowgroup_rows_AVG] | كما هو موضح أعلاه |
| [CLOSED_rowgroup_rows] | انظر إلى صفوف مجموعة الصفوف المغلقة كتحقق من السلامة. |
| [CLOSED_rowgroup_count] | يجب أن يكون عدد مجموعات الصفوف المغلقة منخفضا إذا تم رؤية أي منها على الإطلاق. يمكن تحويل مجموعات الصفوف المغلقة إلى مجموعات صفوف مضغوطة باستخدام ALTER INDEX ... الأمر "REORGANIZE". ومع ذلك، فإن هذا عادةً ما يكون غير مطلوب. يتم تحويل المجموعات المغلقة تلقائيا إلى مجموعات صفوف تخزين الأعمدة بواسطة عملية "tuple mover" في الخلفية. |
| [CLOSED_rowgroup_rows_MIN] | يجب أن يكون لدى مجموعات الصفوف المغلقة معدل تعبئة مرتفع جدا. إذا كان معدل التعبئة لمجموعة صفوف مغلقة منخفضا، فيجب إجراء مزيد من التحليل لمتلقي الأعمدة. |
| [CLOSED_rowgroup_rows_MAX] | كما هو موضح أعلاه |
| [CLOSED_rowgroup_rows_AVG] | كما هو موضح أعلاه |
| [Rebuild_Index_SQL] | SQL لإعادة إنشاء فهرس تخزين الأعمدة لجدول |
تأثير صيانة الفهرس
يحتوي العمود Rebuild_Index_SQL في طريقة العرض vColumnstoreDensity على عبارة ALTER INDEX REBUILD والتي يمكن استخدامها لإعادة إنشاء الفهارس. عند إعادة إنشاء الفهارس، تأكد من تخصيص ذاكرة كافية للجلسة التي تعيد إنشاء الفهارس. للقيام بذلك، قم بزيادة فئة الموارد لمستخدم لديه أذونات لإعادة إنشاء الفهرس على هذا الجدول إلى الحد الأدنى الموصى به. على سبيل المثال، راجع إعادة إنشاء الفهارس لتحسين جودة الشريحة لاحقًا في هذه المقالة.
بالنسبة للجدول الذي يحتوي على فهرس تخزين الأعمدة لبيانات مجمعة مرتب، ALTER INDEX REBUILD سيعيد فرز البيانات باستخدام tempdb. قم بمراقبة tempdb أثناء عمليات إعادة البناء. إذا كنت بحاجة إلى مساحة tempdb إضافية، فقم بزيادة حجم تجمع قاعدة البيانات. قم بتغيير السعة مرة أخرى بمجرد اكتمال إعادة إنشاء الفهرس.
بالنسبة للجدول الذي يحتوي على فهرس تخزين الأعمدة لبيانات مجمعة مرتب، ALTER INDEX REORGANIZE لا يعيد فرز البيانات. لإعادة فرز البيانات، استخدم ALTER INDEX REBUILD.
لمزيد من المعلومات حول فهارس تخزين الأعمدة لبيانات مجمعة مرتبة، راجع ضبط الأداء مع فهرس تخزين الأعمدة لبيانات مجمعة مرتب.
أسباب ضعف جودة فهرس تخزين الأعمدة
إذا حددت جداول بشريحة ذات جودة ضعيفة، يجب عليك تحديد السبب الجذري. فيما يلي بعض الأسباب الشائعة الأخرى لجودة الشريحة الضعيفة:
- ضغط الذاكرة عند إنشاء الفهرس
- حجم عمليات DML كبير
- عمليات تحميل صغيرة أو ضعيفة
- عدد الأقسام كبير جدًا
يمكن أن تتسبب هذه العوامل في أن يحتوي فهرس تخزين الأعمدة على أقل بكثير من 1 مليون صف لكل مجموعة صفوف، وهو العدد الأمثل. يمكن أيضًا أن تتسبب في انتقال الصفوف إلى مجموعة صفوف دلتا بدلاً من مجموعة صفوف مضغوطة.
ضغط الذاكرة عند إنشاء الفهرس
عدد الصفوف لكل مجموعة صفوف مضغوطة يرتبط مباشرة بعرض الصف ومقدار الذاكرة المتوفرة لمعالجة مجموعة الصفوف. عند كتابة الصفوف إلى جداول تخزين الأعمدة تحت ضغط الذاكرة، قد تتأثر جودة شريحة تخزين الأعمدة. لذلك، فإن أفضل ممارسة هي منح الجلسة التي تكتب إلى جداول فهرس تخزين الأعمدة إمكانية الوصول إلى أكبر قدر ممكن من الذاكرة. نظرًا لوجود مفاضلة بين الذاكرة والتزامن، تعتمد الإرشادات المتعلقة بتخصيص الذاكرة الصحيحة على البيانات الموجودة في كل صف من الجدول، ووحدات مستودع البيانات المخصصة لنظامك، وعدد فتحات التزامن التي يمكنك منحها للجلسة التي تكتب البيانات إلى جدولك.
حجم عمليات DML كبير
يمكن أن يؤدي حجم عمليات DML الكبير التي تقوم بتحديث الصفوف وحذفها إلى عدم الكفاءة في تخزين الأعمدة. هذا صحيح بشكل خاص عند تعديل معظم الصفوف في مجموعة صفوف.
- يؤدي حذف صف من مجموعة صفوف مضغوطة إلى وضع علامة منطقية فقط على الصف على أنه محذوف. الصف يبقى في مجموعة الصفوف المضغوطة حتى إعادة إنشاء القسم أو الجدول.
- إدراج صف يؤدي إلى إضافة الصف إلى جدول تخزين صفوف داخلي يسمى مجموعة صفوف دلتا. الصف المدرج لا يتم تحويله إلى تخزين الأعمدة حتى تكون مجموعة صفوف دلتا ممتلئة ويتم وضع علامة عليها على أنها مغلقة. مجموعات الصفوف يتم إغلاقها بمجرد وصولها إلى الحد الأقصى للسعة البالغة 1,048,576 صفًا.
- تتم معالجة تحديث صف بتنسيق تخزين الأعمدة كحذف منطقي ثم كإدراج. قد يتم تخزين الصف المدرج في مخزن دلتا.
عمليات التحديث والإدراج المجمعة التي تتجاوز الحد المجمع البالغ 102,400 صف لكل توزيع محاذي للقسم تنتقل مباشرة إلى تنسيق تخزين الأعمدة. ومع ذلك، بافتراض أن التوزيع متساو، ستحتاج إلى تعديل أكثر من 6.144 مليون صف في عملية واحدة حتى يحدث ذلك. إذا كان عدد الصفوف لتوزيع معين محاذي للقسم أقل من 102,400 صف، تنتقل الصفوف إلى مخزن دلتا وتبقى هناك حتى يتم إدراج صفوف كافية أو تعديلها لإغلاق مجموعة الصفوف أو إعادة إنشاء الفهرس.
عمليات تحميل صغيرة أو ضعيفة
تُعرف أيضًا الأحمال الصغيرة التي تتدفق إلى تجمع SQL المخصص أحيانًا باسم الأحمال الضعيفة. وهي عادةً ما تمثل دفقًا ثابتًا تقريبًا من البيانات التي يستوعبها النظام. ومع ذلك، نظرًا لأن هذا الدفق قريب من الاستمرار، فإن حجم الصفوف ليس كبيرًا بشكل خاص. غالبًا ما تكون البيانات أقل بكثير من الحد المطلوب للتحميل المباشر إلى تنسيق تخزين الأعمدة.
في هذه الحالات، غالبًا ما يكون من الأفضل وضع البيانات أولاً في تخزين الكائن الثنائي كبير الحجم في Azure والسماح لها بالتراكم قبل التحميل. غالبًا ما تعرف هذه التقنية باسم الإرسال في دفعات صغيرة.
عدد الأقسام كبير جدًا
هناك شيء آخر يجب مراعاته وهو تأثير التقسيم على جداول تخزين الأعمدة لبيانات مجمعة. قبل التقسيم، يقسم تجمع SQL المخصص بياناتك بالفعل إلى 60 قاعدة بيانات. يؤدي التقسيم إلى تقسيم بياناتك بشكل أكبر. إذا قمت بتقسيم بياناتك، ففكر في أن كل قسم يحتاج إلى 1 مليون صف على الأقل للاستفادة من فهرس تخزين الأعمدة لبيانات مجمعة. إذا قمت بتقسيم الجدول إلى 100 قسم، فسيحتاج الجدول إلى 6 مليار صف على الأقل للاستفادة من فهرس تخزين الأعمدة لبيانات مجمعة (60 توزيعًا 100 قسم 1 مليون صف). إذا كان الجدول المكون من 100 قسم لا يحتوي على 6 مليار صف، فقم إما بتقليل عدد الأقسام أو التفكير في استخدام جدول الكومة بدلاً من ذلك.
بمجرد تحميل الجداول ببعض البيانات، اتبع الخطوات التالية لتحديد الجداول وإعادة إنشائها باستخدام فهارس تخزين الأعمدة لبيانات مجمعة دون المستوى الأمثل.
إعادة إنشاء الفهارس لتحسين جودة الشريحة
الخطوة 1: تحديد أو إنشاء مستخدم يستخدم فئة الموارد الصحيحة
إحدى الطرق السريعة لتحسين جودة الشريحة على الفور تتمثل في إعادة إنشاء الفهرس. تحتوي SQL الذي تم إرجاعها بواسطة طريقة العرض أعلاه على عبارة ALTER INDEX REBUILD، والتي يمكن استخدامها لإعادة إنشاء الفهارس. عند إعادة إنشاء الفهارس، تأكد من تخصيص ذاكرة كافية للجلسة التي تعيد إنشاء الفهارس. للقيام بذلك، قم بزيادة فئة الموارد لمستخدم لديه أذونات لإعادة إنشاء الفهرس على هذا الجدول إلى الحد الأدنى الموصى به.
فيما يلي مثال على كيفية تخصيص المزيد من الذاكرة للمستخدم عن طريق زيادة فئة الموارد الخاصة به. للعمل مع فئات الموارد، راجع فئات الموارد لإدارة حمل العمل.
EXEC sp_addrolemember 'xlargerc', 'LoadUser';
الخطوة 2: إعادة إنشاء فهارس تخزين الأعمدة لبيانات مجمعة باستخدام مستخدم فئة موارد أعلى
سجل الدخول كمستخدم من الخطوة 1 (LoadUser)، والتي تستخدم الآن فئة موارد أعلى، وقم بتنفيذ عبارات ALTER INDEX. تأكد من أن هذا المستخدم لديه إذن ALTER للجداول حيث تتم إعادة إنشاء الفهرس. هذه الأمثلة توضح كيفية إعادة إنشاء فهرس تخزين الأعمدة بأكمله أو كيفية إعادة إنشاء قسم واحد. في الجداول الكبيرة، من العملي أكثر إعادة إنشاء الفهارس بقسم واحد في كل مرة.
بدلاً من ذلك، وبدلاً من إعادة إنشاء الفهرس، يمكنك نسخ الجدول إلى جدول جديد باستخدام CTAS. أي طريقة هي الأفضل؟ بالنسبة إلى الكميات الكبيرة من البيانات، عادةً ما يكون CTAS أسرع من ALTER INDEX. بالنسبة إلى الأحجام الأصغر من البيانات، يكون استخدام ALTER INDEX أسهل، ولن يتطلب منك مبادلة الجدول.
-- Rebuild the entire clustered index
ALTER INDEX ALL ON [dbo].[DimProduct] REBUILD;
-- Rebuild a single partition
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5;
-- Rebuild a single partition with archival compression
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5 WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
-- Rebuild a single partition with columnstore compression
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5 WITH (DATA_COMPRESSION = COLUMNSTORE);
إعادة إنشاء فهرس في تجمع SQL المخصص تُعد عملية غير متصلة. لمزيد من المعلومات حول إعادة إنشاء الفهارس، راجع قسم ALTER INDEX REBUILD في إلغاء تجزئة فهارس تخزين الأعمدة وALTER INDEX.
الخطوة 3: التحقق من تحسين جودة شريحة تخزين الأعمدة لبيانات مجمعة
أعد تشغيل الاستعلام الذي حدد جدولاً بجودة شريحة ضعيفة وتحقق من تحسن جودة الشريحة. إذا لم تتحسن جودة الشريحة، فقد يكون عرض الصفوف في الجدول أكبر بكثير. ضع في اعتبارك استخدام فئة موارد أعلى أو DWU عند إعادة إنشاء الفهارس.
إعادة إنشاء الفهارس باستخدام CTAS وتبديل القسم
هذا المثال يستخدم عبارة CREATE TABLE AS SELECT (CTAS) وتبديل القسم لإعادة إنشاء قسم جدول.
-- Step 1: Select the partition of data and write it out to a new table using CTAS
CREATE TABLE [dbo].[FactInternetSales_20000101_20010101]
WITH ( DISTRIBUTION = HASH([ProductKey])
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101,20010101
)
)
)
AS
SELECT *
FROM [dbo].[FactInternetSales]
WHERE [OrderDateKey] >= 20000101
AND [OrderDateKey] < 20010101
;
-- Step 2: Switch IN the rebuilt data with TRUNCATE_TARGET option
ALTER TABLE [dbo].[FactInternetSales_20000101_20010101] SWITCH PARTITION 2 TO [dbo].[FactInternetSales] PARTITION 2 WITH (TRUNCATE_TARGET = ON);
لمزيد من المعلومات حول إعادة إنشاء الأقسام باستخدام CTAS، راجع استخدام الأقسام في تجمع SQL المخصص.
الخطوات التالية
لمزيد من المعلومات حول تطوير الجداول، راجع تطوير الجداول.
الملاحظات
قريبًا: خلال عام 2024، سنتخلص تدريجيًا من GitHub Issues بوصفها آلية إرسال ملاحظات للمحتوى ونستبدلها بنظام ملاحظات جديد. لمزيد من المعلومات، راجع https://aka.ms/ContentUserFeedback.
إرسال الملاحظات وعرضها المتعلقة بـ