دليل خطة SQL Server وغيرها من أفضل الممارسات

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



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



تلميحات تلميحات إلى مُحسّن الاستعلام ، يمكن العثور على قائمة كاملة على MSDN . بعضها تلميحات حقًا (على سبيل المثال ، يمكنك تحديد OPTION (MAXDOP 4)) بحيث يمكن تنفيذ الاستعلام بأقصى درجة من التوازي = 4 ، ولكن لا يوجد ضمان بأن SQL Server سينشئ خطة متوازية مع هذا التلميح على الإطلاق.



الجزء الآخر هو دليل مباشر للعمل. على سبيل المثال ، إذا كتبت OPTION (HASH JOIN) ، فسيقوم SQL Server بإنشاء خطة بدون LOOPS LOOPS و MERGE JOINs. وهل تعرف ماذا سيحدث إذا اتضح أنه من المستحيل بناء خطة بنقاط التجزئة فقط؟ سيقول المحسن ذلك - لا يمكنني بناء خطة ولن يتم تنفيذ الاستعلام.



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



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



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



الآن شرح صغير لماذا دخلت في هذا.



لدي طاولة عريضة مع مجموعة من حقول nvarchar بأحجام مختلفة - من 10 إلى الحد الأقصى. وهناك مجموعة من الاستعلامات لهذا الجدول ، والتي تبحث CHARINDEX عن حدوث سلاسل فرعية في واحد أو أكثر من هذه الأعمدة. على سبيل المثال ، هناك طلب يبدو كالتالي:

SELECT *
FROM table
WHERE CHARINDEX(N' ', column)>1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET x ROWS FETCH NEXT y ROWS ONLY

يحتوي الجدول على فهرس مجمع على معرف وفهرس غير فريد غير مجمع على عمود. كما تفهم بنفسك ، لا يوجد أي معنى من هذا كله ، حيث أنه حيث نستخدم CHARINDEX ، وهو بالتأكيد ليس SARGable. لتجنب المشاكل المحتملة مع SB ، سأقوم بمحاكاة هذا الموقف في قاعدة البيانات المفتوحة StackOverflow2013 ، والتي يمكن العثور عليها هنا .



خذ بعين الاعتبار جدول dbo.Posts ، الذي يحتوي فقط على فهرس مجمع حسب المعرف واستعلام مثل هذا:

SELECT *
FROM dbo.Posts
WHERE CHARINDEX (N'Data', Title) > 1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY

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

CREATE INDEX ix_Title ON dbo.Posts (Title);

نتيجة لذلك ، بالطبع ، نحصل على خطة تنفيذ منطقية تمامًا ، والتي تتكون من مسح المؤشر العنقودي في الاتجاه المعاكس:











ومن المسلم به أن أداءها جيد جدًا:

جدول "المشاركات". عدد عمليات المسح 1 ، القراءة المنطقية 516 ، القراءة الفعلية 0 ، قراءة القراءة المسبقة 0 ، قراءة منطقية lob 0 ، قراءة lob المادية 0 ، قراءة قراءة lob 0.

أوقات تنفيذ SQL Server:

وقت CPU = 16 مللي ثانية

ولكن ماذا يحدث إذا بحثنا عن شيء أكثر ندرة بدلاً من الكلمة الشائعة "البيانات"؟ على سبيل المثال ، N'Aptana '(لا فكرة عن ماهيتها). الخطة ، بالطبع ، ستبقى كما هي ، ولكن إحصائيات التنفيذ ، مهم ، ستتغير إلى حد ما:

جدول "المشاركات". عدد المسح 1 ، قراءة منطقية 253191 ، قراءة فعلية 113 ، قراءة قراءة 224602 ، قراءة منطقية لوب 0 ، قراءة لوب فعلية 0 ، قراءة قراءة لوب 0.

أوقات تنفيذ SQL Server:

وقت CPU = 2563 مللي ثانية

وهذا أمر منطقي أيضًا - فالكلمة أقل شيوعًا ويتعين على SQL Server مسح المزيد من البيانات للعثور على 25 صفًا بها. ولكن بطريقة ما ليس الأمر رائعًا ، أليس كذلك؟

وكنت أقوم بإنشاء فهرس غير مجمع. ربما سيكون من الأفضل إذا كان SQL Server يستخدمه؟ هو نفسه لن يستخدمه ، لذلك أضيف تلميحًا:

SELECT *
FROM dbo.Posts
WHERE CHARINDEX (N'Aptana', Title) > 1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY
OPTION (TABLE HINT (dbo.Posts, INDEX(ix_Title)));

وشيء ما حزين إلى حد ما. إحصائيات التنفيذ:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Posts'. Scan count 5, logical reads 109312, physical reads 5, read-ahead reads 104946, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 35031 ms

والخطة:







الآن خطة التنفيذ متوازية ولها نوعان ، كلاهما مع انسكابات في tempdb. بالمناسبة ، انتبه إلى الفرز الأول ، الذي يتم بعد فحص فهرس غير متفاوت ، قبل Key Lookup - هذا هو تحسين خاص لـ SQL Server يحاول تقليل عدد عمليات الإدخال / الإخراج العشوائية - يتم إجراء عمليات بحث المفتاح بترتيب تصاعدي لمفتاح الفهرس المجمع. يمكنك قراءة المزيد عن هذا هنا .



الفرز الثاني مطلوب لتحديد 25 سطرًا في معرّف الترتيب التنازلي. بالمناسبة ، يمكن أن يخمن SQL Server أنه سيتعين عليه الفرز حسب المعرف مرة أخرى ، فقط بترتيب تنازلي وإجراء عمليات بحث رئيسية في الاتجاه "المعاكس" ، والفرز بترتيب تنازلي ، وليس تصاعدي ، لمفتاح الفهرس المتجمع في البداية.



أنا لا أقدم إحصائيات حول تنفيذ استعلام مع تلميح على فهرس غير مجمع مع بحث بواسطة الإدخال "البيانات". على محرك الأقراص الثابتة شبه الميت في جهاز كمبيوتر محمول ، استغرق الأمر أكثر من 16 دقيقة ولم أفكر في التقاط لقطة شاشة. عُذْرًا ، لَا أُرِيدُ الْمُحَاوَلَةْ لَدَيْنَا الْمُوَقِّتْ.

لكن ماذا عن الطلب؟ هل الفهرس المتجمع هو الحلم النهائي ، ولا يمكنك فعل أي شيء بشكل أسرع؟



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

CREATE INDEX ix_Id_Title ON dbo.Posts (Id DESC, Title);

الآن نستخدم التلميح لإخبار SQL Server باستخدامه:

SELECT *
FROM dbo.Posts
WHERE CHARINDEX (N'Aptana', Title) > 1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY
OPTION (TABLE HINT (dbo.Posts, INDEX(ix_Id_Title)));

لقد عملت بشكل جيد:

جدول "المشاركات". عدد عمليات المسح 1 ، القراءة المنطقية 6259 ، القراءة الفعلية 0 ، قراءة القراءة السابقة 7816 ، قراءة منطقية lob 0 ، قراءة lob المادية 0 ، قراءة قراءة lob 0.

أوقات تنفيذ SQL Server:

وقت CPU = 1734 مللي ثانية

إن مكاسب وقت المعالج ليست كبيرة ، ولكن عليك أن تقرأ أقل بكثير - ليس سيئًا. ماذا عن "البيانات" المتكررة؟

جدول "المشاركات". عدد عمليات المسح 1 ، القراءة المنطقية 208 ، القراءة الفعلية 0 ، قراءة القراءة المسبقة 0 ، قراءة منطقية lob 0 ، قراءة lob المادية 0 ، قراءة قراءة lob 0.

أوقات تنفيذ SQL Server:

وقت CPU = 0 مللي ثانية

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



يتم استخدام الإجراء sp_create_plan_guide ( MSDN ) المخزن لإنشاء دليل خطة .



دعونا نفكر في ذلك بالتفصيل:

sp_create_plan_guide [ @name = ] N'plan_guide_name'  
    , [ @stmt = ] N'statement_text'  
    , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'  
    , [ @module_or_batch = ]  
      {   
        N'[ schema_name. ] object_name'  
        | N'batch_text'  
        | NULL  
      }  
    , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }   
    , [ @hints = ] { 
                 N'OPTION ( query_hint [ ,...n ] )'   
                 | N'XML_showplan'  
                 | NULL 
      }  

اسم - اسم دليل خطة واضح وفريد

STMT- هذا هو الطلب الذي تحتاج إلى إضافة التلميح إليه. من المهم أن تعرف هنا أنه يجب كتابة هذا الطلب تمامًا مثل الطلب الذي يأتي من التطبيق. الفضاء الغريب؟ لن يتم استخدام دليل الخطة. فاصل أسطر خاطئ؟ لن يتم استخدام دليل الخطة. لتسهيل الأمور على نفسك ، هناك "اختراق الحياة" الذي سأعود إليه بعد ذلك بقليل (والذي وجدته هنا ).



اكتب - يشير إلى المكان المحدد في الطلب STMT... إذا كان جزءًا من إجراء مخزن ، فيجب أن يكون الكائن. إذا كان هذا جزءًا من مجموعة من عدة طلبات ، أو طلب مخصص ، أو دفعة من طلب واحد ، يجب أن يكون هناك SQL. إذا تمت الإشارة إلى TEMPLATE هنا ، فهذه قصة منفصلة حول معلمات الاستعلام ، والتي يمكنك قراءتها على MSDN .



module_or_batch يعتمد علىاكتب. اذا كاناكتب= "كائن" ، يجب أن يكون هذا اسم الإجراء المخزن. اذا كاناكتب= "BATCH" - يجب أن يكون هناك نص للدُفعة بأكملها ، محددًا كلمة بكلمة مع ما يأتي من التطبيقات. الفضاء الغريب؟ حسنا ، أنت تعرف بالفعل. إذا كانت فارغة ، فإننا نعتبر أن هذه دفعة من طلب واحد وتتطابق مع ما هو محدد فيSTMT مع كل القيود.



المعلمات- يجب إدراج جميع المعلمات التي تم تمريرها إلى الطلب مع أنواع البيانات هنا.



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



لذلك ، نقوم بإنشاء دليل خطة للاستعلام:

DECLARE @sql nvarchar(max) = N'SELECT *
FROM dbo.Posts
WHERE CHARINDEX (N''Data'', Title) > 1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY';

exec sp_create_plan_guide @name = N'PG_dboPosts_Index_Id_Title'  
    ,  @stmt =  @sql
    ,  @type = N'SQL'
    ,  @module_or_batch =  NULL          
    ,  @params = NULL
    ,  @hints =  N'OPTION (TABLE HINT (dbo.Posts, INDEX (ix_Id_Title)))'

ونحاول تنفيذ الطلب:

SELECT *
FROM dbo.Posts
WHERE CHARINDEX (N'Data', Title) > 1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY

نجاح باهر ، لقد نجح الأمر:







في خصائص عبارة SELECT الأخيرة ، نرى:







عظيم ، تم تطبيق giude الخطة. ماذا لو بحثت عن Aptana الآن؟ وكل شيء سيكون سيئًا - سنعود مرة أخرى إلى مسح الفهرس العنقودي مع كل العواقب. لماذا ا؟ ولأن دليل الخطة يتم تطبيقه على استعلام محدد ، يتطابق نصه مع واحد إلى واحد مع الآخر المنفذ.



لحسن الحظ ، تأتي معظم الطلبات على نظامي في معلمات. لم أعمل مع الاستعلامات غير المعلمة وآمل ألا أضطر إلى ذلك. بالنسبة لهم ، يمكنك استخدام القوالب (انظر قليلاً أعلى حول TEMPLATE) ، يمكنك تمكين PARAMETERIZATION القسري في قاعدة البيانات ( لا تفعل ذلك دون فهم ما تفعله !!! ) ، وربما بعد ذلك ، ستتمكن من ربط دليل الخطة. لكني لم أجربها حقًا.



في حالتي ، يتم تنفيذ الطلب على النحو التالي:

exec sp_executesql  
N'SELECT *
FROM dbo.Posts
WHERE CHARINDEX (@p0, Title) > 1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY;'
, N'@p0 nvarchar(250), @p1 int, @p2 int'
, @p0 = N'Aptana', @p1 = 0, @p2 = 25;

لذلك ، أقوم بإنشاء دليل خطة مناظر:

DECLARE @sql nvarchar(max) = N'SELECT *
FROM dbo.Posts
WHERE CHARINDEX (@p0, Title) > 1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY;';

exec sp_create_plan_guide @name = N'PG_paramters_dboPosts_Index_Id_Title'  
    ,  @stmt =  @sql
    ,  @type = N'SQL'
    ,  @module_or_batch =  NULL          
    ,  @params = N'@p0 nvarchar(250), @p1 int, @p2 int'
    ,  @hints =  N'OPTION (TABLE HINT (dbo.Posts, INDEX (ix_Id_Title)))'

ويا للأسف ، كل شيء يعمل على النحو المطلوب:











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



يمكن استخدام طلب لهذا ، على سبيل المثال ، مثل:

SELECT qs.plan_handle, st.text, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) qp

يمكننا الآن استخدام الإجراء sp_create_plan_guide_from_handle المخزن لإنشاء دليل خطة من خطة موجودة.



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



محاولة:

exec sp_create_plan_guide_from_handle N'PG_dboPosts_from_handle'  
    , 0x0600050018263314F048E3652102000001000000000000000000000000000000000000000000000000000000
    , NULL;

والآن في SSMS ، نلقي نظرة على ما لدينا في قابلية البرمجة -> أدلة الخطة:







الآن تم "تثبيت" خطة التنفيذ الحالية لطلبنا باستخدام دليل الخطة "PG_dboPosts_from_handle" ، ولكن أفضل شيء الآن ، مثل أي كائن تقريبًا في SSMS ، يمكننا كتابة وإعادة إنشاء الطريقة التي نحتاجها.



RMB ، Script -> Drop AND Create ونحصل على نص برمجي جاهز نحتاج فيه إلى استبدال قيمة المعلمةhints بالقيمة التي نحتاجها ، ونتيجة لذلك نحصل على:

USE [StackOverflow2013]
GO

/****** Object:  PlanGuide PG_dboPosts_from_handle    Script Date: 05.07.2020 16:25:04 ******/
EXEC sp_control_plan_guide @operation = N'DROP', @name = N'[PG_dboPosts_from_handle]'
GO
/****** Object:  PlanGuide PG_dboPosts_from_handle    Script Date: 05.07.2020 16:25:04 ******/
EXEC sp_create_plan_guide @name = N'[PG_dboPosts_from_handle]', @stmt = N'SELECT *
FROM dbo.Posts
WHERE CHARINDEX (@p0, Title) > 1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY', @type = N'SQL', @module_or_batch = N'SELECT *
FROM dbo.Posts
WHERE CHARINDEX (@p0, Title) > 1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY;', 
@params = N'@p0 nvarchar(250), @p1 int, @p2 int', 
@hints = N'OPTION (TABLE HINT (dbo.Posts, INDEX (ix_Id_Title)))'
GO

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







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



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

رسالة 10502 ، المستوى 16 ، الحالة 1 ، السطر 1

لا يمكن إنشاء دليل الخطة "PG_dboPosts_from_handle2" لأن العبارة المحددة بواسطةSTMTوmodule_or_batch ، أو بواسطةplan_handle وstatement_start_offset ، تتطابق مع دليل الخطة الحالي "PG_dboPosts_from_handle" في قاعدة البيانات. أفلت دليل الخطة الحالي قبل إنشاء دليل الخطة الجديد.

آخر شيء أود أن أذكره هو إجراء sp_control_plan_guide المخزن .



بمساعدتها ، يمكنك حذف وتعطيل وتمكين أدلة الخطة - كلاهما في وقت واحد ، مع الإشارة إلى الاسم ، وجميع الأدلة (لست متأكدًا - كل شيء على الإطلاق. أو كل شيء في سياق قاعدة البيانات التي يتم فيها تنفيذ الإجراء) - يتم استخدام القيم لهذا @ معلمة التشغيل - DROP ALL و DISABLE ALL و ENABLE ALL. يتم إعطاء مثال على استخدام HP لخطة معينة أعلاه فقط - يتم حذف دليل خطة معين بالاسم المحدد.



هل كان من الممكن الاستغناء عن التلميحات ودليل الخطة؟



بشكل عام ، إذا بدا لك أن مُحسِّن طلب البحث غبي ويقوم بنوع من الألعاب ، وأنت تعرف أفضل طريقة ، مع احتمال بنسبة 99٪ أنك تقوم بنوع من الألعاب (كما في حالتي). ومع ذلك ، في حالة عدم القدرة على التأثير مباشرة على نص الطلب ، يمكن أن يكون دليل الخطة الذي يسمح لك بإضافة تلميح إلى الطلب منقذًا. افترض أن لدينا القدرة على إعادة كتابة نص الطلب كما نحتاج إليه - هل يمكن أن يغير هذا شيئًا؟ أكيد! حتى بدون استخدام كلمة "غريبة" في شكل بحث عن النص الكامل ، الذي يجب استخدامه في الواقع هنا. على سبيل المثال ، يحتوي هذا الاستعلام على خطة عادية تمامًا (للاستعلام) وإحصاءات تنفيذ:

;WITH c AS (
    SELECT p2.id
    FROM dbo.Posts p2
    WHERE CHARINDEX (N'Aptana', Title) > 1
    ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
    OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY
)
SELECT p.* 
FROM dbo.Posts p
JOIN c ON p.id = c.id;

جدول "المشاركات". عدد عمليات المسح 1 ، القراءة المنطقية 6250 ، القراءة الفعلية 0 ، قراءة القراءة المسبقة 0 ، قراءة منطقية lob 0 ، قراءة lob المادية 0 ، قراءة قراءة lob 0.

أوقات تنفيذ SQL Server:

وقت CPU = 1500 مللي ثانية

يعثر SQL Server أولاً على 25 معرّفًا مطلوبًا بواسطة الفهرس "ix_Id_Title" ملتوي ، وعندها فقط يبحث في الفهرس المجمع باستخدام المعرفات المحددة - أفضل من الدليل! ولكن ماذا يحدث إذا قمنا بتنفيذ استعلام حول "البيانات" وعرضنا 25 سطرًا ، بدءًا من السطر 20000:

;WITH c AS (
    SELECT p2.id
    FROM dbo.Posts p2 
    WHERE CHARINDEX (N'Data', Title) > 1
    ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
    OFFSET 20000 ROWS FETCH NEXT 25 ROWS ONLY
)
SELECT p.* 
FROM dbo.Posts p
JOIN c ON p.id = c.id;

جدول "المشاركات". عدد المسح 1 ، القراءة المنطقية 5914 ، القراءة الفعلية 0 ، قراءة القراءة المسبقة 0 ، قراءة منطقية lob 11 ، قراءة lob المادية 0 ، قراءة قراءة lob 0.

أوقات تنفيذ SQL Server:

وقت CPU = 1453 مللي ثانية

exec sp_executesql  
N'SELECT *
FROM dbo.Posts
WHERE CHARINDEX (@p0, Title) > 1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY;'
, N'@p0 nvarchar(250), @p1 int, @p2 int'
, @p0 = N'Data', @p1 = 20000, @p2 = 25;

Table 'Posts'. Scan count 1, logical reads 87174, physical reads 0, read-ahead reads 0, lob logical reads 11, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 1437 ms

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



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



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