شاخص های فیلتر شده ایجاد کنید

در این مقاله نحوه ایجاد یک فهرست فیلتر شده با استفاده از استودیوی مدیریت سرور SQL (SSMS) یا Transact-SQL توضیح داده شده است. یک شاخص فیلتر شده یک شاخص غیر خوشه ای RowStore مبتنی بر دیسک بهینه شده است که مخصوصاً برای پوشش داده هایی که از زیر مجموعه ای از داده های تعریف شده انتخاب می کنند ، مناسب است. از یک محمول فیلتر برای نمایه بخشی از ردیف های جدول استفاده می کند. یک شاخص فیلتر شده به خوبی طراحی شده می تواند عملکرد پرس و جو را بهبود بخشد و هزینه های نگهداری و ذخیره سازی شاخص را در مقایسه با شاخص های کامل جدول کاهش دهد.

شاخص های فیلتر شده می توانند مزایای زیر را نسبت به شاخص های تمام جدول ارائه دهند:

عملکرد پرس و جو بهبود یافته و کیفیت برنامه.

یک شاخص فیلتر شده به خوبی طراحی شده ، عملکرد پرس و جو و کیفیت برنامه اجرای را بهبود می بخشد زیرا کوچکتر از یک شاخص بدون جدول کامل است و آماری را فیلتر کرده است. آمار فیلتر شده دقیق تر از آمار کامل جدول است زیرا فقط ردیف های موجود در شاخص فیلتر شده را پوشش می دهند.

کاهش هزینه های نگهداری شاخص.

یک شاخص فقط هنگامی حفظ می شود که بیانیه های زبان دستکاری داده (DML) بر داده های موجود در فهرست تأثیر بگذارد. یک شاخص فیلتر شده هزینه های نگهداری شاخص را در مقایسه با یک شاخص کامل بدون جدول کاهش می دهد زیرا کوچکتر است و فقط در صورت تغییر داده های موجود در شاخص حفظ می شود. می توان تعداد زیادی از شاخص های فیلتر شده را داشت ، به خصوص هنگامی که آنها حاوی داده هایی هستند که به ندرت تغییر می کنند. به طور مشابه ، اگر یک شاخص فیلتر شده فقط داده های اصلاح شده را شامل شود ، اندازه کوچکتر این شاخص هزینه به روزرسانی آمار را کاهش می دهد.

کاهش هزینه های ذخیره سازی شاخص.

ایجاد یک شاخص فیلتر شده می تواند در صورت لزوم یک شاخص کامل ، ذخیره دیسک را برای شاخص های غیر خوشه ای کاهش دهد. شما می توانید یک شاخص بدون میز کامل را با شاخص های فیلتر چندگانه جایگزین کنید بدون اینکه نیازهای ذخیره سازی را به میزان قابل توجهی افزایش دهید.

ملاحظات طراحی

هنگامی که یک ستون فقط چند مقدار مرتبط برای نمایش داده شد ، می توانید یک شاخص فیلتر شده را در زیر مجموعه مقادیر ایجاد کنید. شاخص حاصل کوچکتر خواهد بود و برای حفظ کمتر از یک شاخص بدون جدول کامل که در همان ستون های کلیدی تعریف شده است ، هزینه کمتری خواهد داشت.

به عنوان مثال ، یک شاخص فیلتر شده را در سناریوهای داده زیر در نظر بگیرید. در هر حالت ، بند شاخص فیلتر شده باید زیر مجموعه ای از بند یک پرس و جو برای بهره مندی از شاخص فیلتر باشد.

• هنگامی که مقادیر موجود در یک ستون عمدتا تهی هستند و پرس و جو فقط از مقادیر غیر تهی انتخاب می شود. می توانید یک شاخص فیلتر شده برای ردیف داده های غیر تهی ایجاد کنید.
• هنگامی که ردیف های یک جدول به عنوان پردازش شده توسط یک گردش کار مکرر یا فرآیند صف مشخص می شوند. با گذشت زمان ، بیشتر ردیف های جدول به صورت پردازش مشخص می شوند. یک شاخص فیلتر شده در ردیف هایی که هنوز پردازش نشده اند ، به نفع پرس و جو تکراری است که به دنبال ردیف هایی است که هنوز پردازش نشده اند.
• هنگامی که یک جدول دارای ردیف داده های ناهمگن است. می توانید برای یک یا چند دسته از داده ها یک شاخص فیلتر شده ایجاد کنید. این می تواند با محدود کردن تمرکز یک پرس و جو به یک منطقه خاص از جدول ، عملکرد نمایش داده شدگان را در این ردیف های داده بهبود بخشد. باز هم ، شاخص حاصل کوچکتر و هزینه کمتری نسبت به یک شاخص بدون طبقه کامل است.

محدودیت ها و محدودیت ها

شما نمی توانید یک فهرست فیلتر شده را در یک نمای ایجاد کنید. با این حال ، بهینه ساز پرس و جو می تواند از یک شاخص فیلتر شده تعریف شده در یک جدول که در یک نمای ذکر شده است بهره مند شود. بهینه ساز پرس و جو یک شاخص فیلتر شده را برای یک پرس و جو در نظر می گیرد که در صورت صحیح بودن نتایج پرس و جو از یک نمای انتخاب می کند.

هنگامی که ستون دسترسی به بیان فیلتر از نوع داده CLR است ، نمی توانید یک شاخص فیلتر شده را روی یک جدول ایجاد کنید.

شاخص های فیلتر شده مزایای زیر را نسبت به نماهای فهرست بندی شده دارند:

کاهش هزینه های نگهداری شاخص. به عنوان مثال ، پردازنده پرس و جو از منابع کمتری برای CPU برای به روزرسانی یک شاخص فیلتر شده نسبت به نمای ایندکس شده استفاده می کند.

کیفیت برنامه بهبود یافته. به عنوان مثال ، در هنگام تدوین پرس و جو ، بهینه ساز پرس و جو با استفاده از یک شاخص فیلتر شده در موقعیت های بیشتر از نمای ایندکس معادل در نظر می گیرد.

بازسازی شاخص آنلاین. شما می توانید شاخص های فیلتر شده را در حالی که برای نمایش داده شد ، بازسازی کنید. بازسازی های شاخص آنلاین برای نماهای فهرست بندی شده پشتیبانی نمی شوند. برای اطلاعات بیشتر ، به گزینه Rebuild برای Alter Index (Tranact-SQL) مراجعه کنید.

شاخص های غیر متحد. شاخص های فیلتر شده می توانند غیر منحصر به فرد باشند ، در حالی که نماهای ایندکس شده باید بی نظیر باشند.

شاخص های فیلتر شده در یک جدول تعریف شده و فقط از اپراتورهای مقایسه ساده پشتیبانی می کنند. اگر به یک عبارت فیلتر نیاز دارید که چندین جدول را ارجاع دهد یا منطق پیچیده ای داشته باشد ، باید نمای ایجاد کنید. شاخص های فیلتر شده مانند اپراتورها پشتیبانی نمی کنند.

اگر بیان شاخص فیلتر شده معادل محمول پرس و جو باشد ، یک ستون در بیان شاخص فیلتر شده نیازی به یک ستون یا ستون در تعریف شاخص فیلتر ندارد و پرس و جو ستون را در بیان شاخص فیلتر شده با آن باز نمی گرداندنتایج پرس و جو.

اگر در صورت استفاده از PENDICT پرس و جو از ستون در مقایسه ای که معادل بیان شاخص فیلتر نیست ، یک ستون در بیان شاخص فیلتر شده باید یک ستون یا ستون در تعریف شاخص فیلتر باشد.

اگر ستون در مجموعه نتیجه پرس و جو باشد ، یک ستون در بیان شاخص فیلتر شده باید یک کلید یا ستون در تعریف شاخص فیلتر باشد.

کلید شاخص خوشه ای جدول نیازی به یک ستون یا ستون موجود در تعریف شاخص فیلتر ندارد. کلید شاخص خوشه ای به طور خودکار در کلیه شاخص های غیر خوشه ای ، از جمله شاخص های فیلتر شده گنجانده شده است. در راهنمای معماری و طراحی شاخص بیشتر بیاموزید.

اگر اپراتور مقایسه مشخص شده در بیان شاخص فیلتر شده از شاخص فیلتر شده منجر به تبدیل داده های ضمنی یا صریح شود ، در صورت بروز تبدیل در سمت چپ یک اپراتور مقایسه ، خطایی رخ می دهد. یک راه حل نوشتن عبارت شاخص فیلتر شده با اپراتور تبدیل داده (بازیگران یا تبدیل) در سمت راست اپراتور مقایسه است.

گزینه های مجموعه مورد نیاز برای ایجاد شاخص فیلتر شده را در Syntax ایجاد شاخص (Transact-SQL) مرور کنید

فیلترها را نمی توان برای محدودیت های اصلی کلید یا منحصر به فرد اعمال کرد ، اما می تواند برای شاخص ها با خاصیت منحصر به فرد اعمال شود.

شما نمی توانید یک فهرست فیلتر شده را در یک ستون محاسبه شده ایجاد کنید.

مجوزها

نیاز به تغییر مجوز در جدول یا نمای دارد. کاربر باید عضو نقش سرور ثابت Sysadmin یا نقش های ثابت DB_DDLADMIN و DB_OWNER باشد. برای تغییر بیان شاخص فیلتر شده ، از ایجاد فهرست با drop_existing استفاده کنید.

با SSM یک شاخص فیلتر شده ایجاد کنید

در Object Explorer ، علامت Plus را برای گسترش پایگاه داده ای که شامل جدول است که می خواهید یک شاخص فیلتر شده ایجاد کنید ، انتخاب کنید.

برای گسترش پوشه جداول ، علامت Plus را انتخاب کنید.

برای گسترش جدول که می خواهید یک شاخص فیلتر شده ایجاد کنید ، علامت Plus را انتخاب کنید.

روی پوشه Indexes راست کلیک کنید ، به فهرست جدید اشاره کنید و فهرست غیر خوشه ای را انتخاب کنید. واد

در کادر گفتگوی جدید فهرست ، در صفحه عمومی ، نام فهرست جدید را در کادر نام فهرست وارد کنید.

در زیر ستون های کلید فهرست ، افزودن را انتخاب کنید. واد

در ستون های Select از کادر گفتگوی Table_Name ، کادر انتخاب یا کادر انتخاب ستون جدول یا ستون ها را انتخاب کنید تا به فهرست اضافه شوند.

خوب را انتخاب کنید.

در صفحه فیلتر ، تحت بیان فیلتر ، عبارت SQL را که برای ایجاد شاخص فیلتر شده استفاده می کنید ، وارد کنید.

خوب را انتخاب کنید.

یک شاخص فیلتر شده با tranact-sql ایجاد کنید

در این مثال از پایگاه داده AdventureWorks2019 استفاده شده است که برای بارگیری در پایگاه داده های نمونه AdventureWorks در دسترس است.

در Object Explorer ، به نمونه ای از موتور پایگاه داده وصل شوید.

در نوار استاندارد ، پرس و جو جدید را انتخاب کنید.

مثال زیر را در پنجره Query کپی و چسبانده و Execute را انتخاب کنید.

شاخص فیلتر شده FibillofMaterialSwitHendDate برای پرس و جو زیر معتبر است. می توانید برنامه اجرای پرس و جو را برای تعیین اینکه آیا بهینه ساز پرس و جو از شاخص فیلتر شده استفاده کرده است ، نمایش دهید.

مراحل بعدی

برای کسب اطلاعات بیشتر در مورد ایجاد فهرست ها و مفاهیم مرتبط ، به مقالات زیر مراجعه کنید: