كيف يستخدم SQL Server عوامل تصفية الصور النقطية

تم إعداد ترجمة المقال استعدادًا لبدء دورة "مطور خادم MS SQL" .

---

هل يمكن للاستعلام الذي يتم تشغيله بالتوازي استخدام وحدة معالجة مركزية أقل وتشغيله بشكل أسرع من الاستعلام الذي يتم تشغيله بالتتابع؟



نعم! للتوضيح ، سأستخدم جدولين بنوع عمود واحد integer.





ملاحظة - نص TSQL في شكل نص موجود في نهاية المقالة.

توليد البيانات التجريبية

نقوم #BuildIntبإدخال 5000 عدد صحيح عشوائي في الجدول (بحيث يكون لديك نفس قيمي ، أستخدم RAND مع البذور وحلقة WHILE). أدخل 5.000.000 سجل في







الجدول #Probe.

خطة متسلسلة

لنكتب الآن استعلامًا لحساب عدد تطابقات القيم في هذه الجداول. نستخدم تلميح MAXDOP 1 للتأكد من أن الاستعلام لن يتم تنفيذه بالتوازي.



خطة التنفيذ والإحصائيات كما يلي:







يستغرق هذا الاستعلام 891 مللي ثانية ويستخدم 890 مللي ثانية من وحدة المعالجة المركزية.

خطة موازية

لنقم الآن بتشغيل نفس الاستعلام باستخدام MAXDOP 2.







يستغرق الاستعلام 221 مللي ثانية ويستخدم 436 مللي ثانية من وحدة المعالجة المركزية. انخفض وقت التنفيذ أربع مرات ، وانخفض استخدام وحدة المعالجة المركزية إلى النصف!

نقطية سحرية

السبب في أن تنفيذ الاستعلام المتوازي أكثر كفاءة هو عامل الصورة النقطية.



دعنا نلقي نظرة فاحصة على خطة التنفيذ الفعلية للاستعلام الموازي:







وقارنها بالخطة المتسلسلة:







مبدأ مشغل الصورة النقطية موثق جيدًا ، لذلك سأقدم هنا وصفًا موجزًا ​​مع روابط للوثائق في نهاية المقالة.

Hash Join

يتم تنفيذ Hash Join على خطوتين:

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

بطبيعة الحال ، نظرًا لاحتمال حدوث تصادمات تجزئة ، لا يزال من الضروري مقارنة القيم الحقيقية للمفاتيح.

ملاحظة المترجم: لمزيد من التفاصيل حول كيفية عمل ربط التجزئة ، راجع مقالة التصور والتعامل مع Hash Match Join

صورة نقطية في خطط متسلسلة

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



HASH JOIN في مرحلة إنشاء وإنشاء جدول تجزئة يعيّن بت واحد (أو أكثر) في الصورة النقطية. يمكنك بعد ذلك استخدام الصورة النقطية لمطابقة قيم التجزئة بكفاءة دون الحاجة إلى الوصول إلى جدول التجزئة.



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



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

صورة نقطية في خطط متوازية

في خطة متوازية ، يتم عرض الصورة النقطية على هيئة بيان نقطي منفصل.



عند الانتقال من مرحلة البناء إلى مرحلة التحقق ، يتم تمرير الصورة النقطية إلى مشغل HASH MATCH من جانب الجدول الثاني (المسبار). كحد أدنى ، يتم تمرير الصورة النقطية إلى جانب الفحص قبل JOIN ومشغل التبادل (Parallelism).



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



بالطبع ، لا توجد بيانات تبادل في الخطط المتسلسلة ، لذا فإن نقل الصورة النقطية خارج HASH JOIN لا يوفر أي ميزة إضافية على الصورة النقطية "المضمنة" داخل عبارة HASH MATCH.



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



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



أيضًا ، يحاول المُحسِّن عادةً وضع مرشحات بسيطة بالقرب من الأوراق قدر الإمكان: من الأفضل تصفية الصفوف في أقرب وقت ممكن. ومع ذلك ، يجب أن أذكر أنه تمت إضافة الصورة النقطية التي نتحدث عنها بعد اكتمال التحسين.



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

نقل مرشح الصورة النقطية

دعنا نعود إلى مفهوم نقل مرشح الصورة النقطية إلى جانب التحقيق في الاتصال.



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







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

الذهاب أعمق ...

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



سيظل المسند يظهر في عبارات Scan أو Seek كما في المثال أعلاه ، ولكن الآن سيتم تمييزه بسمة INROW ، مما يعني أنه تم نقل عامل التصفية إلى Storage Engine وتطبيقه على الصفوف أثناء قراءتها.



باستخدام هذا التحسين ، يتم تصفية الصفوف قبل أن يرى معالج الاستعلام الصف. يتم إرسال تلك السلاسل التي تطابق HASH MATCH JOIN فقط من Storage Engine.



تعتمد الشروط التي يتم بموجبها تطبيق هذا التحسين على إصدار SQL Server. على سبيل المثال ، في SQL Server 2005 ، بالإضافة إلى الشروط المحددة سابقًا ، يجب تعريف عمود التحقيق على أنه NOT NULL. تم تخفيف هذا القيد في SQL Server 2008.



قد تتساءل عن كيفية تأثير تحسينات INROW على الأداء. هل سيكون نقل المشغل بالقرب من البحث أو المسح قدر الإمكان بنفس كفاءة التصفية في محرك التخزين؟ سأجيب على هذا السؤال المثير للاهتمام في مقالات أخرى. وهنا سنلقي نظرة أيضًا على MERGE JOIN و NESTED LOOP JOIN.

خيارات JOIN الأخرى

يعد استخدام الحلقات المتداخلة بدون مؤشرات فكرة سيئة. يتعين علينا مسح أحد الجداول بالكامل لكل صف من الجدول الآخر - ما مجموعه 5 مليارات مقارنة. من المحتمل أن يستغرق هذا الطلب وقتًا طويلاً جدًا.

دمج الانضمام

يتطلب هذا النوع من الصلة المادية إدخالاً مُفرزًا ، لذلك يتسبب MERGE JOIN القسري في وجود نوع قبله. تبدو الخطة التسلسلية كما يلي:







يستخدم الاستعلام الآن 3105 مللي ثانية من وحدة المعالجة المركزية ، ويبلغ إجمالي وقت التنفيذ 5632 مللي ثانية .



ترجع الزيادة في وقت التنفيذ الكلي إلى حقيقة أن إحدى عمليات الفرز تستخدم tempdb (على الرغم من أن SQL Server به ذاكرة كافية للفرز).



يحدث تسرب إلى tempdb لأن خوارزمية منح الذاكرة الافتراضية لا تحفظ مسبقًا ذاكرة كافية. حتى ننتبه إلى ذلك ، من الواضح أن الطلب لن يكتمل في أقل من 3105 مللي ثانية.



دعنا نواصل فرض MERGE JOIN ، مع السماح بالتوازي (MAXDOP 2):







كما هو الحال في HASH JOIN الموازي الذي رأيناه سابقًا ، يوجد مرشح الصورة النقطية على الجانب الآخر من MERGE JOIN بالقرب من Table Scan ويتم تطبيقه باستخدام تحسين INROW.



مع 468 مللي ثانية من وحدة المعالجة المركزية والوقت المنقضي 240 مللي ثانية ، يكون MERGE JOIN مع أنواع إضافية تقريبًا بنفس سرعة HASH JOIN المتوازي ( 436 مللي ثانية / 221 مللي ثانية ).



لكن MERGE JOIN الموازي له عيب واحد: فهو يحتفظ بـ 330 كيلوبايت من الذاكرة بناءً على العدد المتوقع من الصفوف للفرز. نظرًا لاستخدام هذه الأنواع من الصور النقطية بعد تحسين التكلفة ، فلا يوجد تعديل على التقدير ، على الرغم من أن 2488 صفًا فقط تمر عبر الفرز السفلي.



يمكن أن تظهر عبارة Bitmap في خطة تحتوي على MERGE JOIN فقط مع عبارة حظر لاحقة (على سبيل المثال ، Sort). يجب أن يتلقى عامل الحظر جميع القيم المطلوبة كمدخلات قبل أن يقوم بإنشاء السطر الأول للإخراج. هذا يضمن أن الصورة النقطية ممتلئة تمامًا قبل قراءة الصفوف من جدول JOIN والتحقق منها مقابلها.



ليس من الضروري أن تكون عبارة الحظر على الجانب الآخر من MERGE JOIN ، ولكن من المهم في أي جانب يتم استخدام الصورة النقطية.

مع المؤشرات

في حالة توفر مؤشرات مناسبة ، يكون الوضع مختلفًا. يتم توزيع بياناتنا "العشوائية" بحيث #BuildIntيمكن إنشاء فهرس فريد على الجدول . #Probeويحتوي الجدول على نسخ مكررة ، لذلك عليك أن تتعامل مع فهرس غير فريد:







لن يؤثر هذا التغيير على HASH JOIN (سواء في التسلسلي أو المتوازي). لا يمكن لـ HASH JOIN استخدام الفهارس ، لذلك تظل الخطط والأداء كما هو.

دمج الانضمام

لم يعد MERGE JOIN بحاجة إلى إجراء عملية انضمام متعدد إلى متعدد ولم يعد يتطلب عامل فرز على الإدخال.

يعني عدم وجود عامل فرز مانع أنه لا يمكن استخدام الصورة النقطية.



نتيجة لذلك ، نرى خطة متسلسلة ، بغض النظر عن معلمة MAXDOP ، والأداء أسوأ من الخطة المتوازية قبل إضافة الفهارس: 702 مللي ثانية CPU و 704 مللي ثانية الوقت المنقضي:







ومع ذلك ، هناك تحسن ملحوظ على خطة MERGE JOIN المتسلسلة الأصلية ( 3105 مللي ثانية / 5632 مللي ثانية ). ويرجع ذلك إلى التخلص من الفرز وتحسين أداء انضمام واحد إلى متعدد.



حلقات ربط متداخلة

كما قد تتوقع ، تعمل NESTED LOOP بشكل أفضل. على غرار MERGE JOIN ، قرر المحسن عدم استخدام التزامن:







هذه هي الخطة الأكثر فعالية حتى الآن - فقط 16 مللي ثانية من وحدة المعالجة المركزية و 16 مللي ثانية تقضي الوقت.



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



على أداء الكمبيوتر المحمول الخاص بي من ذاكرة التخزين المؤقت الباردة NESTED LOOP استغرق 78 مللي ثانية من وحدة المعالجة المركزية و 2152 مللي ثانية ، الوقت المنقضي. في ظل نفس الظروف ، استخدم MERGE JOIN 686 مللي ثانية من وحدة المعالجة المركزية و 1471 مللي ثانية . HASH JOIN - 391 مللي ثانية من وحدة المعالجة المركزية و905 مللي ثانية .



تستفيد MERGE JOIN و HASH JOIN من إدخال / إخراج كبير ، وربما متسلسل باستخدام القراءة المسبقة.

مصادر إضافية

الانضمام إلى التجزئة المتوازية (Craig Freedman)

عوامل تصفية الصور النقطية لتنفيذ الاستعلام (فريق معالجة استعلام SQL Server)

الصور النقطية في Microsoft SQL Server 2000 (مقالة MSDN)

تفسير خطط التنفيذ التي تحتوي على عوامل تصفية الصور النقطية (وثائق SQL Server)

فهم صلات التجزئة (وثائق SQL Server)

اختبار كتابي

USE tempdb;
GO
CREATE TABLE #BuildInt
(
    col1    INTEGER NOT NULL
);
GO
CREATE TABLE #Probe
(
    col1    INTEGER NOT NULL
);
GO

-- Load 5,000 rows into the build table
SET NOCOUNT ON;
SET STATISTICS XML OFF;

DECLARE @I INTEGER = 1;

INSERT #BuildInt
    (col1) 
VALUES 
    (CONVERT(INTEGER, RAND(1) * 2147483647));

WHILE @I < 5000
BEGIN
    INSERT #BuildInt
        (col1)
    VALUES 
        (RAND() * 2147483647);
    SET @I += 1;
END;

-- Load 5,000,000 rows into the probe table
SET NOCOUNT ON;
SET STATISTICS XML OFF;

DECLARE @I INTEGER = 1;

INSERT #Probe
    (col1) 
VALUES 
    (CONVERT(INTEGER, RAND(2) * 2147483647));

BEGIN TRANSACTION;
WHILE @I < 5000000
BEGIN
    INSERT #Probe
        (col1) 
    VALUES 
        (CONVERT(INTEGER, RAND() * 2147483647));

    SET @I += 1;

    IF @I % 25 = 0
    BEGIN
        COMMIT TRANSACTION;
        BEGIN TRANSACTION;
    END;
END;

COMMIT TRANSACTION;
GO
-- Demos
SET STATISTICS XML OFF;
SET STATISTICS IO, TIME ON;

-- Serial
SELECT 
    COUNT_BIG(*) 
FROM #BuildInt AS bi 
JOIN #Probe AS p ON 
    p.col1 = bi.col1 
OPTION (MAXDOP 1);

-- Parallel
SELECT 
    COUNT_BIG(*) 
FROM #BuildInt AS bi 
JOIN #Probe AS p ON 
    p.col1 = bi.col1 
OPTION (MAXDOP 2);

SET STATISTICS IO, TIME OFF;

-- Indexes
CREATE UNIQUE CLUSTERED INDEX cuq ON #BuildInt (col1);
CREATE CLUSTERED INDEX cx ON #Probe (col1);

-- Vary the query hints to explore plan shapes

SELECT 
    COUNT_BIG(*) 
FROM #BuildInt AS bi 
JOIN #Probe AS p ON 
    p.col1 = bi.col1 
OPTION (MAXDOP 1, MERGE JOIN);
GO
DROP TABLE #BuildInt, #Probe;

اقرأ أكثر:

• أخطاء عند العمل مع التاريخ والوقت في SQL Server
• تعمل النافذة مع "نافذة" أو كيفية استخدام الإطار