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

ایندکس ها بر روی ستون های جدول و مشاهده ایجاد می شوند. ایندکس ها راهی برای جستجوی سریع داده ها بر اساس مقادیر موجود در آن ستون ها ارائه می دهند. به عنوان مثال، اگر یک شاخص روی یک کلید اصلی ایجاد کنید و سپس با استفاده از مقادیر کلید اولیه، ردیفی از داده ها را جستجو کنید، سپس SQL Serverابتدا مقدار شاخص را پیدا می کند و سپس از شاخص برای یافتن سریع کل ردیف داده ها استفاده می کند. بدون ایندکس، اسکن کامل تمام ردیف‌های جدول انجام می‌شود که می‌تواند تأثیر قابل توجهی در عملکرد داشته باشد.
شما می توانید در اکثر ستون های جدول یا نما یک شاخص ایجاد کنید. استثنا عمدتاً ستون هایی با انواع داده برای ذخیره اشیاء بزرگ است ( LOB)، مانند تصویر, متنیا varchar (حداکثر). همچنین می توانید بر روی ستون هایی که برای ذخیره داده ها در قالب طراحی شده اند، فهرست ایجاد کنید XML، اما ساختار این شاخص ها کمی متفاوت از شاخص های استاندارد است و بررسی آنها از حوصله این مقاله خارج است. همچنین، مقاله بحث نمی کند ستون فروشیشاخص ها در عوض، من بر روی شاخص هایی تمرکز می کنم که بیشتر در پایگاه های داده استفاده می شوند SQL Server.
یک شاخص شامل مجموعه ای از صفحات، گره های شاخص است که در یک ساختار درختی سازماندهی شده اند - درخت متعادل. این ساختار ماهیت سلسله مراتبی دارد و با یک گره ریشه در بالای سلسله مراتب و گره های برگ، برگ ها، در پایین شروع می شود، همانطور که در شکل نشان داده شده است:


وقتی یک ستون نمایه شده را پرس و جو می کنید، موتور پرس و جو از بالای گره ریشه شروع می شود و از طریق گره های میانی به سمت پایین حرکت می کند و هر لایه میانی حاوی اطلاعات دقیق تری در مورد داده ها است. موتور پرس و جو به حرکت در گره های شاخص ادامه می دهد تا زمانی که با برگ های شاخص به سطح پایینی برسد. به عنوان مثال، اگر به دنبال مقدار 123 در یک ستون ایندکس شده هستید، موتور پرس و جو ابتدا صفحه را در اولین سطح متوسط ​​در سطح ریشه تعیین می کند. در این حالت، صفحه اول به یک مقدار از 1 تا 100 و صفحه دوم از 101 تا 200 اشاره می کند، بنابراین موتور پرس و جو به صفحه دوم این سطح متوسط ​​دسترسی خواهد داشت. در ادامه خواهید دید که باید به صفحه سوم سطح متوسط ​​بعدی بروید. از اینجا، زیرسیستم پرس و جو مقدار خود شاخص را در سطح پایین تری می خواند. برگه‌های فهرست می‌توانند شامل خود داده‌های جدول یا صرفاً نشانگر ردیف‌هایی با داده‌های موجود در جدول باشند، بسته به نوع شاخص: شاخص خوشه‌ای یا شاخص غیرخوشه‌ای.

شاخص خوشه ای

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

شاخص غیر خوشه ای

بر خلاف یک شاخص خوشه‌ای، برگ‌های یک شاخص غیرخوشه‌ای فقط شامل آن ستون‌ها می‌شود ( کلید) که توسط آن این شاخص تعیین می شود و همچنین حاوی یک اشاره گر به ردیف هایی با داده های واقعی در جدول است. این بدان معناست که سیستم پرس و جو نیاز به یک عملیات اضافی برای مکان یابی و بازیابی داده های مورد نیاز دارد. محتوای نشانگر داده به نحوه ذخیره داده ها بستگی دارد: جدول خوشه ای یا پشته. اگر یک اشاره گر به یک جدول خوشه ای اشاره کند، به یک شاخص خوشه ای اشاره می کند که می تواند برای یافتن داده های واقعی استفاده شود. اگر اشاره گر به یک پشته اشاره کند، آنگاه به یک شناسه ردیف داده خاص اشاره می کند. نمایه های غیرخوشه ای را نمی توان مانند نمایه های خوشه ای مرتب کرد، اما می توانید بیش از یک نمایه غیرخوشه ای روی یک جدول یا نما تا 999 ایجاد کنید. شاخص ها می توانند عملکرد سیستم را بهبود یا کاهش دهند. علاوه بر امکان ایجاد چندین نمایه غیر خوشه ای، می توانید ستون های اضافی را نیز اضافه کنید ( ستون گنجانده شده است) در نمایه آن: برگه های ایندکس نه تنها ارزش خود ستون های نمایه شده، بلکه مقادیر این ستون های اضافی غیر نمایه شده را نیز ذخیره می کند. این رویکرد به شما این امکان را می دهد که برخی از محدودیت های اعمال شده بر روی شاخص را دور بزنید. به عنوان مثال، می توانید یک ستون غیرقابل نمایه سازی اضافه کنید یا از محدودیت طول شاخص (900 بایت در اکثر موارد) عبور کنید.

انواع شاخص ها

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

شاخص ترکیبی

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

شاخص منحصر به فرد

این شاخص تضمین می کند که هر مقدار در ستون نمایه شده منحصر به فرد است. اگر شاخص ترکیبی باشد، منحصر به فرد بودن برای همه ستون‌های ایندکس اعمال می‌شود، اما نه برای هر ستون جداگانه. به عنوان مثال، اگر یک شاخص منحصر به فرد روی ستون ها ایجاد کنید نامو نام خانوادگی، آن نام و نام خانوادگیباید منحصر به فرد باشد، اما تکرار در نام یا نام خانوادگی امکان پذیر است.
هنگامی که شما یک محدودیت ستونی را تعریف می کنید، یک شاخص منحصر به فرد ایجاد می شود: کلید اولیه یا محدودیت مقدار منحصر به فرد:

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

شاخص پوشش

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

طراحی شاخص ها

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

پایگاه داده

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

• برای جداولی که به طور مکرر به روز می شوند، تا حد امکان از نمایه های کمتری استفاده کنید.
• اگر جدول حاوی مقدار زیادی داده است، اما تغییرات جزئی هستند، از هر تعداد شاخص لازم برای بهبود عملکرد جستجوهای خود استفاده کنید. با این حال، قبل از استفاده از نمایه ها در جداول کوچک خوب فکر کنید، زیرا ... این امکان وجود دارد که استفاده از جستجوی فهرست ممکن است بیشتر از اسکن ساده همه ردیف ها طول بکشد.
• برای نمایه های خوشه ای، سعی کنید فیلدها را تا حد امکان کوتاه نگه دارید. بهترین رویکرد استفاده از یک شاخص خوشه‌بندی شده در ستون‌هایی است که مقادیر منحصر به فرد دارند و اجازه NULL را نمی‌دهند. به همین دلیل است که یک کلید اولیه اغلب به عنوان یک شاخص خوشه ای استفاده می شود.
• منحصر به فرد بودن مقادیر در یک ستون بر عملکرد شاخص تأثیر می گذارد. به طور کلی، هرچه تعداد تکرارهای بیشتری در یک ستون داشته باشید، عملکرد ایندکس بدتر است. از سوی دیگر، هرچه مقادیر منحصر به فرد بیشتری وجود داشته باشد، عملکرد شاخص بهتر است. در صورت امکان از یک شاخص منحصر به فرد استفاده کنید.
• برای یک شاخص ترکیبی، ترتیب ستون‌ها را در نمایه در نظر بگیرید. ستون هایی که در عبارات استفاده می شوند جایی که(مثلا، WHERE FirstName = "چارلی") باید اول در فهرست باشد. ستون های بعدی باید بر اساس منحصر به فرد بودن مقادیرشان فهرست شوند (ستون هایی با بیشترین تعداد مقادیر منحصر به فرد اول هستند).
• همچنین می‌توانید در ستون‌های محاسبه‌شده در صورتی که شرایط خاصی را برآورده می‌کنند، یک شاخص مشخص کنید. به عنوان مثال، عبارات مورد استفاده برای به دست آوردن مقدار یک ستون باید قطعی باشند (همیشه همان نتیجه را برای مجموعه معینی از پارامترهای ورودی برمی گرداند).

پرس و جوهای پایگاه داده

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

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

و اکنون، در واقع:

14 سوال در مورد ایندکس ها در SQL Server که از پرسیدن آنها خجالت می کشیدید

چرا یک جدول نمی تواند دو شاخص خوشه ای داشته باشد؟

پاسخ کوتاه می خواهید؟ یک شاخص خوشه ای یک جدول است. هنگامی که یک نمایه خوشه‌ای روی یک جدول ایجاد می‌کنید، موتور ذخیره‌سازی تمام ردیف‌های جدول را طبق تعریف شاخص به ترتیب صعودی یا نزولی مرتب می‌کند. یک نمایه خوشه ای موجودیت جداگانه ای مانند سایر نمایه ها نیست، بلکه مکانیزمی برای مرتب سازی داده ها در یک جدول و تسهیل دسترسی سریع به ردیف های داده است.
بیایید تصور کنیم که شما یک جدول حاوی تاریخچه معاملات فروش دارید. جدول فروش شامل اطلاعاتی مانند شناسه سفارش، موقعیت محصول در سفارش، شماره محصول، تعداد محصول، شماره و تاریخ سفارش و غیره است. شما یک نمایه خوشه ای روی ستون ها ایجاد می کنید شماره سفارشو شناسه خط، طبق شکل زیر به ترتیب صعودی مرتب شده اند T-SQLکد:
ایجاد INDEX خوشه ای منحصر به فرد ix_oriderid_lineid در dbo.Sales(OrderID, LineID);
هنگامی که این اسکریپت را اجرا می کنید، تمام ردیف های جدول ابتدا بر اساس ستون OrderID و سپس توسط LineID به صورت فیزیکی مرتب می شوند، اما خود داده ها در یک بلوک منطقی، جدول باقی می مانند. به همین دلیل نمی توانید دو شاخص خوشه ای ایجاد کنید. فقط یک جدول با یک داده می تواند وجود داشته باشد و آن جدول فقط یک بار می تواند به ترتیب خاصی مرتب شود.

اگر یک جدول خوشه ای مزایای زیادی را ارائه می دهد، پس چرا از یک پشته استفاده می کنیم؟

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

چگونه می توانم ضریب پر شدن شاخص پیش فرض را تغییر دهم؟

تغییر فاکتور پر شدن شاخص پیش فرض یک چیز است. درک نحوه عملکرد نسبت پیش فرض موضوع دیگری است. اما ابتدا چند قدم به عقب برگردید. ضریب پر شدن فهرست، مقدار فضای صفحه را برای ذخیره ایندکس در سطح پایین (سطح برگ) قبل از شروع پر کردن تعیین می کند. صفحه جدید. به عنوان مثال، اگر ضریب 90 تنظیم شود، پس از رشد شاخص، 90 درصد صفحه را اشغال می کند و سپس به صفحه بعدی می رود.
به طور پیش‌فرض، مقدار ضریب پر شدن شاخص در است SQL Server 0 است، که همان 100 است. در نتیجه، همه شاخص های جدید به طور خودکار این تنظیمات را به ارث می برند، مگر اینکه به طور خاص مقداری را در کد خود مشخص کنید که با مقدار استاندارد سیستم متفاوت باشد یا رفتار پیش فرض را تغییر دهید. شما می توانید استفاده کنید SQL Server Management Studioبرای تنظیم مقدار پیش فرض یا اجرای یک رویه ذخیره شده در سیستم sp_configure. به عنوان مثال مجموعه زیر T-SQLدستور مقدار ضریب را روی 90 تنظیم می کند (ابتدا باید به حالت تنظیمات پیشرفته بروید):
EXEC sp_configure "نمایش گزینه های پیشرفته"، 1; GO RECONFIGURE. GO EXEC sp_configure "fill factor", 90; GO RECONFIGURE. برو
پس از تغییر مقدار ضریب پر کردن شاخص، باید سرویس را مجددا راه اندازی کنید SQL Server. اکنون می توانید مقدار تنظیم شده را با اجرای sp_configure بدون آرگومان دوم مشخص شده بررسی کنید:
EXEC sp_configure "fill factor" GO
این دستور باید مقدار 90 را برگرداند. در نتیجه، همه ایندکس های ایجاد شده جدید از این مقدار استفاده خواهند کرد. شما می توانید این را با ایجاد یک شاخص و پرس و جو برای مقدار ضریب پر کردن آزمایش کنید:
از AdventureWorks2012 استفاده کنید. -- پایگاه داده شما GO CREATE NONCLUSTERED INDEX ix_people_lastname ON Person.Person(LastName); GO SELECT fill_factor FROM sys.indexes WHERE object_id = object_id("Person.Person") AND name="ix_people_lastname";
که در در این مثالما یک شاخص غیر خوشه ای روی جدول ایجاد کرده ایم شخصدر پایگاه داده AdventureWorks2012. پس از ایجاد ایندکس، می توانیم مقدار ضریب پر را از جداول سیستم sys.indexes دریافت کنیم. پرس و جو باید 90 برگرداند.
با این حال، بیایید تصور کنیم که ایندکس را حذف کرده و دوباره آن را ایجاد کرده ایم، اما اکنون یک مقدار ضریب پری خاص را مشخص کرده ایم:
CREATE NONCLUSTERED INDEX ix_people_lastname ON Person.Person(LastName) WITH (fillfactor=80); GO SELECT fill_factor FROM sys.indexes WHERE object_id = object_id("Person.Person") AND name="ix_people_lastname";
این بار دستورالعمل ها را اضافه کرده ایم باو گزینه عامل پرکنندهبرای عملیات ایجاد نمایه ما ایجاد شاخصو مقدار 80. Operator را مشخص کرد انتخاب کنیداکنون مقدار مربوطه را برمی گرداند.
تا اینجا، همه چیز کاملاً ساده بوده است. جایی که واقعاً می‌توانید در کل این فرآیند سوخته شوید، زمانی است که یک شاخص ایجاد می‌کنید که از یک مقدار ضریب پیش‌فرض استفاده می‌کند، با فرض اینکه آن مقدار را می‌دانید. به عنوان مثال، شخصی در حال دستکاری تنظیمات سرور است و آنقدر سرسخت است که ضریب پر شدن شاخص را روی 20 قرار می دهد. در همین حال، با فرض اینکه مقدار پیش فرض 0 باشد، به ایجاد ایندکس ادامه می دهید. متأسفانه، راهی برای فهمیدن پر شدن ندارید. فاکتور کنید تا زمانی که یک شاخص ایجاد نکنید و سپس مقدار را مانند نمونه هایمان بررسی کنید. در غیر این صورت، باید منتظر لحظه ای باشید که عملکرد پرس و جو آنقدر کاهش یابد که شروع به شک به چیزی کنید.
موضوع دیگری که باید از آن آگاه باشید، بازسازی ایندکس ها است. همانطور که با ایجاد یک شاخص، می توانید مقدار ضریب پر کردن شاخص را هنگام بازسازی آن مشخص کنید. با این حال، برخلاف دستور ایجاد فهرست، بازسازی از تنظیمات پیش‌فرض سرور استفاده نمی‌کند، علیرغم آنچه ممکن است به نظر برسد. حتی بیشتر، اگر به طور خاص مقدار ضریب پر کردن شاخص را مشخص نکنید، پس SQL Serverاز مقدار ضریبی که این شاخص قبل از تجدید ساختار با آن وجود داشته است استفاده خواهد کرد. برای مثال عملیات زیر ALTER INDEXایندکسی را که به تازگی ایجاد کرده ایم بازسازی می کند:
ALTER INDEX ix_people_lastname ON Person.Person REBUILD; GO SELECT fill_factor FROM sys.indexes WHERE object_id = object_id("Person.Person") AND name="ix_people_lastname";
هنگامی که مقدار ضریب پر را بررسی می کنیم، مقدار 80 را دریافت می کنیم، زیرا این همان چیزی است که در آخرین ایجاد ایندکس مشخص کردیم. مقدار پیش فرض نادیده گرفته می شود.
همانطور که می بینید، تغییر مقدار ضریب پر شدن شاخص چندان دشوار نیست. دانستن مقدار فعلی و درک زمانی که آن اعمال می شود بسیار دشوارتر است. اگر همیشه به طور خاص ضریب را هنگام ایجاد و بازسازی ایندکس ها مشخص کنید، همیشه نتیجه خاص را می دانید. مگر اینکه باید نگران این باشید که مطمئن شوید شخص دیگری دوباره تنظیمات سرور را خراب نمی کند و باعث می شود همه ایندکس ها با ضریب پر شدن شاخص بسیار کم بازسازی شوند.

آیا امکان ایجاد یک نمایه خوشه ای بر روی ستونی که دارای موارد تکراری است وجود دارد؟

بله و خیر. بله، می توانید یک نمایه خوشه ای روی یک ستون کلیدی ایجاد کنید که حاوی مقادیر تکراری است. خیر، مقدار یک ستون کلید نمی تواند در حالت غیر منحصر به فرد باقی بماند. بگذار توضیح بدهم. اگر یک نمایه خوشه‌ای غیر منحصربفرد روی یک ستون ایجاد کنید، موتور ذخیره‌سازی یک تک‌افزا به مقدار تکراری اضافه می‌کند تا از منحصربه‌فرد بودن اطمینان حاصل کند و بنابراین بتواند هر ردیف را در جدول خوشه‌بندی شده شناسایی کند.
برای مثال، ممکن است تصمیم بگیرید که یک شاخص خوشه‌بندی شده در ستونی حاوی داده‌های مشتری ایجاد کنید نام خانوادگیحفظ نام خانوادگی این ستون حاوی مقادیر Franklin، Hancock، Washington و Smith است. سپس مقادیر Adams، Hancock، Smith و Smith را دوباره وارد می کنید. اما مقدار ستون کلید باید منحصربه‌فرد باشد، بنابراین موتور ذخیره‌سازی مقدار تکراری‌ها را تغییر می‌دهد تا چیزی شبیه به این باشند: آدامز، فرانکلین، هنکوک، هنکوک1234، واشنگتن، اسمیت، اسمیت4567 و اسمیت5678.
در نگاه اول، این رویکرد خوب به نظر می رسد، اما یک مقدار صحیح، اندازه کلید را افزایش می دهد، که در صورت وجود تعداد زیادی از موارد تکراری، می تواند مشکل ساز شود و این مقادیر مبنای یک شاخص غیر خوشه ای یا یک شاخص خارجی می شوند. مرجع کلیدی به این دلایل، همیشه باید سعی کنید هر زمان که ممکن است شاخص های خوشه ای منحصر به فردی ایجاد کنید. اگر این امکان پذیر نیست، پس حداقلسعی کنید از ستون هایی با محتوای بسیار بالا از مقادیر منحصر به فرد استفاده کنید.

اگر یک نمایه خوشه ای ایجاد نشده باشد، جدول چگونه ذخیره می شود؟

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

چه رابطه ای بین محدودیت های منحصر به فرد ارزش و یک کلید اولیه با شاخص های جدول وجود دارد؟

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

چرا شاخص های خوشه ای و غیر خوشه ای در SQL Server B-tree نامیده می شوند؟

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


گره ریشه نقطه ورودی اصلی را برای پرس و جوهایی که سعی در بازیابی داده ها از طریق ایندکس دارند فراهم می کند. با شروع از این گره، زیرسیستم پرس و جو یک انتقال را آغاز می کند ساختار سلسله مراتبیبه گره برگ مناسب حاوی داده ها می رسد.
به عنوان مثال، تصور کنید که درخواستی برای انتخاب سطرهایی دریافت شده است که حاوی مقدار کلیدی 82 هستند. زیرسیستم پرس و جو از گره ریشه شروع به کار می کند، که به یک گره میانی مناسب اشاره دارد، در مورد ما 1-100. از گره میانی 1-100 انتقال به گره 51-100 و از آنجا به گره نهایی 76-100 وجود دارد. اگر این یک شاخص خوشه‌ای است، برگ گره حاوی داده‌های ردیف مرتبط با کلید برابر با 82 است. اگر این یک شاخص غیر خوشه‌ای است، آنگاه برگ شاخص حاوی یک اشاره‌گر به جدول خوشه‌بندی شده یا یک ردیف خاص در پشته

اگر باید از همه این گره‌های شاخص عبور کنید، چگونه یک شاخص می‌تواند عملکرد پرس و جو را بهبود بخشد؟

اولاً، شاخص‌ها همیشه عملکرد را بهبود نمی‌بخشند. ایندکس های بسیار نادرست ایجاد شده سیستم را به یک باتلاق تبدیل کرده و عملکرد پرس و جو را کاهش می دهد. این دقیق تر است که بگوییم اگر شاخص ها به دقت اعمال شوند، می توانند دستاوردهای عملکردی قابل توجهی را ایجاد کنند.
به یک کتاب بزرگ اختصاص داده شده به تنظیم عملکرد فکر کنید SQL Server(نسخه کاغذی، نه نسخه الکترونیکی). تصور کنید می خواهید اطلاعاتی در مورد پیکربندی Resource Governor پیدا کنید. می توانید انگشت خود را صفحه به صفحه در کل کتاب بکشید یا فهرست مطالب را باز کنید و با اطلاعاتی که به دنبال آن هستید شماره دقیق صفحه را بیابید (به شرطی که کتاب به درستی نمایه شده باشد و مطالب دارای نمایه های صحیح باشند). این مطمئناً باعث صرفه جویی قابل توجهی در زمان شما می شود، حتی اگر ابتدا باید به یک ساختار کاملاً متفاوت (فهرست) دسترسی داشته باشید تا اطلاعات مورد نیاز خود را از ساختار اصلی (کتاب) دریافت کنید.
مانند فهرست کتاب، نمایه در SQL Serverبه شما امکان می دهد به جای اسکن کامل تمام داده های موجود در یک جدول، پرس و جوهای دقیقی را روی داده های مورد نیاز خود اجرا کنید. برای جداول کوچک، اسکن کامل معمولاً مشکلی نیست، اما جداول بزرگ صفحات زیادی از داده ها را اشغال می کنند، که می تواند منجر به زمان اجرای پرس و جو قابل توجهی شود، مگر اینکه شاخصی وجود داشته باشد که به موتور پرس و جو اجازه دهد بلافاصله مکان صحیح داده ها را به دست آورد. تصور کنید در تقاطع جاده ای چند سطحی در مقابل یک کلان شهر بزرگ و بدون نقشه گم شوید و این ایده را دریافت خواهید کرد.

اگر ایندکس ها بسیار عالی هستند، چرا فقط در هر ستون یکی ایجاد نکنید؟

هیچ کار خیری نباید بدون مجازات بماند. حداقل در مورد شاخص ها اینطور است. البته تا زمانی که کوئری های واکشی اپراتور را اجرا کنید، ایندکس ها عالی کار می کنند انتخاب کنید، اما به محض شروع تماس های مکرر با اپراتورها درج کنید, به روز رسانیو حذف، بنابراین چشم انداز خیلی سریع تغییر می کند.
هنگامی که درخواست داده توسط اپراتور را آغاز می کنید انتخاب کنید، موتور پرس و جو شاخص را پیدا می کند، در ساختار درختی آن حرکت می کند و داده های مورد نظر را کشف می کند. چه چیزی می تواند ساده تر باشد؟ اما اگر شما یک عبارت تغییر را آغاز کنید، همه چیز تغییر می کند به روز رسانی. بله، برای بخش اول بیانیه، موتور پرس و جو می تواند دوباره از ایندکس برای تعیین موقعیت ردیف در حال تغییر استفاده کند - این خبر خوبی است. و اگر یک تغییر ساده در داده ها در یک ردیف وجود داشته باشد که بر تغییرات ستون های کلیدی تأثیری نداشته باشد، فرآیند تغییر کاملاً بدون دردسر خواهد بود. اما اگر این تغییر باعث شود صفحات حاوی داده‌ها تقسیم شوند، یا مقدار یک ستون کلیدی تغییر کند و به گره شاخص دیگری منتقل شود - این منجر به این می‌شود که نمایه احتمالاً به سازماندهی مجدد نیاز داشته باشد که بر همه شاخص‌ها و عملیات مرتبط تأثیر بگذارد. ، منجر به کاهش گسترده بهره وری می شود.
فرآیندهای مشابهی هنگام فراخوانی یک اپراتور رخ می دهد حذف. ایندکس می تواند به تعیین مکان داده های در حال حذف کمک کند، اما حذف خود داده ها ممکن است منجر به تغییر شکل صفحه شود. در مورد اپراتور درج کنید، دشمن اصلی همه شاخص ها: شما شروع به اضافه کردن حجم زیادی از داده ها می کنید که منجر به تغییر در شاخص ها و سازماندهی مجدد آنها می شود و همه متضرر می شوند.
بنابراین وقتی به این فکر می‌کنید که چه نوع ایندکس‌ها و چه تعداد ایندکس ایجاد کنید، انواع پرس‌و‌جوهای پایگاه داده خود را در نظر بگیرید. بیشتر به معنای بهتر نیست. قبل از افزودن یک نمایه جدید به جدول، هزینه نه تنها پرس و جوهای اساسی، بلکه میزان فضای دیسک مصرف شده، هزینه حفظ عملکرد و شاخص ها را نیز در نظر بگیرید، که می تواند منجر به یک اثر دومینو بر سایر عملیات شود. استراتژی طراحی شاخص شما یکی از مهم ترین جنبه های پیاده سازی شما است و باید ملاحظات زیادی را شامل شود، از اندازه شاخص، تعداد مقادیر منحصر به فرد گرفته تا نوع پرس و جوهایی که ایندکس پشتیبانی می کند.

آیا ایجاد یک شاخص خوشه ای روی یک ستون با کلید اصلی ضروری است؟

شما می توانید یک شاخص خوشه ای در هر ستونی که شرایط لازم را دارد ایجاد کنید. درست است که یک شاخص خوشه‌ای و یک محدودیت کلید اولیه برای یکدیگر ساخته شده‌اند و تطبیقی ​​هستند که در بهشت ​​ساخته شده‌اند، بنابراین این واقعیت را درک کنید که وقتی یک کلید اولیه ایجاد می‌کنید، در صورتی که یکی از آن‌ها وجود نداشته باشد، یک شاخص خوشه‌بندی شده به‌طور خودکار ایجاد می‌شود. قبلا ایجاد شده است. با این حال، ممکن است تصمیم بگیرید که یک شاخص خوشه ای در جای دیگر بهتر عمل کند و اغلب تصمیم شما موجه خواهد بود.
هدف اصلی یک نمایه خوشه ای این است که تمام ردیف های جدول را بر اساس ستون کلید مشخص شده در هنگام تعریف شاخص مرتب کند. این فراهم می کند جستجوی سریعو دسترسی آسانبه داده های جدول
کلید اصلی جدول می تواند انتخاب خوبی باشد زیرا بدون نیاز به افزودن داده های اضافی، هر ردیف در جداول را به طور منحصر به فرد شناسایی می کند. در بعضی موارد بهترین انتخابیک کلید اصلی جایگزین وجود خواهد داشت که نه تنها منحصر به فرد است، بلکه اندازه کوچکی نیز دارد و مقادیر آن به طور متوالی افزایش می یابد، که ایندکس های غیر خوشه ای بر اساس این مقدار را کارآمدتر می کند. بهینه‌ساز پرس و جو نیز این ترکیب از یک شاخص خوشه‌ای و یک کلید اولیه را دوست دارد، زیرا اتصال جداول سریع‌تر از پیوستن به روش دیگری است که از کلید اصلی و شاخص خوشه‌ای مرتبط با آن استفاده نمی‌کند. همانطور که گفتم این مسابقه ساخته شده در بهشت ​​است.
در نهایت، با این حال، شایان ذکر است که هنگام ایجاد یک نمایه خوشه‌ای، چندین جنبه وجود دارد که باید در نظر گرفته شود: چند شاخص غیر خوشه‌بندی شده بر اساس آن، هر چند وقت یک‌بار مقدار ستون شاخص کلید تغییر می‌کند و چقدر بزرگ است. هنگامی که مقادیر ستون های یک شاخص خوشه ای تغییر می کند یا شاخص آن طور که انتظار می رود عمل نمی کند، همه شاخص های دیگر روی جدول می توانند تحت تأثیر قرار گیرند. یک شاخص خوشه‌بندی شده باید بر پایه ثابت‌ترین ستونی باشد که مقادیر آن به ترتیب خاصی افزایش می‌یابد اما به‌صورت تصادفی تغییر نمی‌کند. ایندکس باید از پرس‌و‌جوها در برابر داده‌های پردسترسی جدول پشتیبانی کند، بنابراین کوئری‌ها از این واقعیت استفاده می‌کنند که داده‌ها در گره‌های ریشه، برگ‌های فهرست، مرتب شده و قابل دسترسی هستند. اگر کلید اصلی با این سناریو مطابقت دارد، از آن استفاده کنید. اگر نه، مجموعه دیگری از ستون ها را انتخاب کنید.

اگر یک نما را ایندکس کنید، آیا همچنان یک نما است؟

ارائه است میز مجازی، که داده ها را از یک یا چند جدول تولید می کند. اساساً، این یک پرس و جو با نام است که هنگام جستجو در آن نما، داده ها را از جداول زیرین بازیابی می کند. شما می توانید با ایجاد یک نمایه خوشه ای و نمایه های غیر خوشه ای در این نما، عملکرد پرس و جو را بهبود ببخشید، مشابه نحوه ایجاد نمایه ها در جدول، اما نکته اصلی این است که ابتدا یک نمایه خوشه ای ایجاد می کنید و سپس می توانید یک نمایه غیر خوشه ای ایجاد کنید.
هنگامی که یک نمای نمایه شده (نمای مادی) ایجاد می شود، آنگاه تعریف نمای خود یک موجودیت جداگانه باقی می ماند. به هر حال این فقط یک اپراتور سخت کد شده است انتخاب کنید، در پایگاه داده ذخیره می شود. اما شاخص داستان کاملاً متفاوتی است. هنگامی که یک نمایه خوشه‌ای یا غیرخوشه‌ای را در یک ارائه‌دهنده ایجاد می‌کنید، داده‌ها به صورت فیزیکی در دیسک ذخیره می‌شوند، درست مانند یک فهرست معمولی. علاوه بر این، هنگامی که داده ها در جداول زیربنایی تغییر می کنند، نمایه نما به طور خودکار تغییر می کند (این بدان معناست که ممکن است بخواهید از نمایه سازی نماهای جداول که مرتباً تغییر می کنند خودداری کنید). در هر صورت، نما یک نما باقی می ماند - نگاهی به جداول، اما دقیقاً در آن اجرا شده است این لحظه، با شاخص های مربوط به آن.
قبل از اینکه بتوانید یک نمایه در یک نما ایجاد کنید، باید چندین محدودیت داشته باشد. به عنوان مثال، یک view فقط می تواند به جداول پایه ارجاع دهد، اما به نماهای دیگر نمی تواند اشاره کند و آن جداول باید در همان پایگاه داده باشند. در واقع محدودیت‌های زیادی وجود دارد، بنابراین حتماً اسناد را بررسی کنید SQL Serverبرای تمام جزئیات کثیف

چرا به جای شاخص ترکیبی از شاخص پوششی استفاده می شود؟

اول، بیایید مطمئن شویم که تفاوت بین این دو را درک می کنیم. یک شاخص مرکب به سادگی یک شاخص منظم است که شامل بیش از یک ستون است. می توان از چندین ستون کلیدی برای اطمینان از منحصر به فرد بودن هر یک استفاده کرد ردیف های جدول، همچنین ممکن است کلید اولیه از چندین ستون تشکیل شده باشد تا از منحصر به فرد بودن آن اطمینان حاصل شود، یا اینکه در تلاش برای بهینه سازی اجرای پرس و جوهای مکرر فراخوانی شده در چندین ستون هستید. با این حال، به طور کلی، هر چه تعداد ستون‌های کلیدی یک شاخص بیشتر باشد، کارایی شاخص کمتری خواهد داشت، به این معنی که شاخص‌های ترکیبی باید با احتیاط مورد استفاده قرار گیرند.
همانطور که گفته شد، اگر تمام داده های مورد نیاز بلافاصله مانند خود ایندکس، فوراً روی برگه های ایندکس قرار گیرند، یک پرس و جو می تواند بسیار سودمند باشد. این یک مشکل برای یک شاخص خوشه ای نیست زیرا همه داده ها از قبل وجود دارد (به همین دلیل است که هنگام ایجاد یک شاخص خوشه ای، بسیار مهم است که با دقت فکر کنید). اما یک شاخص غیر خوشه‌ای روی برگ‌ها فقط شامل ستون‌های کلیدی است. برای دسترسی به تمام داده‌های دیگر، بهینه‌ساز پرس و جو به مراحل دیگری نیاز دارد که می‌تواند سربار قابل توجهی را به اجرای پرس و جوهای شما اضافه کند.
اینجاست که شاخص پوشش به کمک می آید. هنگامی که یک شاخص غیر خوشه ای تعریف می کنید، می توانید ستون های اضافی را برای ستون های کلیدی خود تعیین کنید. به عنوان مثال، فرض کنید برنامه شما اغلب داده های ستونی را درخواست می کند شماره سفارشو تاریخ سفارشدر جدول حراجی:
SELECT OrderID, OrderDate FROM Sales WHERE OrderID = 12345;
شما می توانید یک شاخص ترکیبی غیر خوشه ای در هر دو ستون ایجاد کنید، اما ستون OrderDate فقط سربار نگهداری شاخص را بدون اینکه به عنوان یک ستون کلیدی مفید عمل کند، اضافه می کند. بهترین تصمیمایجاد یک نمایه پوششی روی ستون کلید خواهد بود شماره سفارشو علاوه بر این شامل ستون تاریخ سفارش:
CREATE NONCLUSTERED INDEX ix_orderid در dbo.Sales(OrderID) شامل (OrderDate);
این کار از مضرات نمایه سازی ستون های اضافی جلوگیری می کند و در عین حال مزایای ذخیره سازی داده ها در برگه ها را هنگام اجرای پرس و جو حفظ می کند. ستون شامل بخشی از کلید نیست، اما داده ها در گره برگ، برگ شاخص ذخیره می شود. این می تواند عملکرد پرس و جو را بدون هیچ سربار اضافی بهبود بخشد. علاوه بر این، ستون های گنجانده شده در نمایه پوششی نسبت به ستون های کلیدی شاخص دارای محدودیت های کمتری هستند.

آیا تعداد موارد تکراری در یک ستون کلید مهم است؟

هنگامی که یک نمایه ایجاد می کنید، باید سعی کنید تعداد موارد تکراری در ستون های کلید خود را کاهش دهید. یا دقیق تر: سعی کنید میزان تکرار را تا حد امکان پایین نگه دارید.
اگر با یک نمایه ترکیبی کار می کنید، تکرار در کل ستون های کلیدی اعمال می شود. یک ستون می تواند مقادیر تکراری زیادی داشته باشد، اما باید حداقل تکرار در بین تمام ستون های فهرست وجود داشته باشد. به عنوان مثال، شما یک شاخص ترکیبی غیر خوشه ای روی ستون ها ایجاد می کنید نام کوچکو نام خانوادگی، می توانید مقادیر John Doe و مقادیر Doe زیادی داشته باشید، اما می خواهید تا حد امکان مقدار John Doe یا ترجیحاً فقط یک مقدار John Doe داشته باشید.
نسبت منحصربه‌فرد بودن مقادیر یک ستون کلید، انتخاب‌پذیری شاخص نامیده می‌شود. هرچه مقادیر منحصربفرد بیشتری وجود داشته باشد، گزینش پذیری بالاتر است: یک شاخص منحصر به فرد بیشترین گزینش پذیری ممکن را دارد. موتور پرس و جو واقعاً ستون‌هایی با مقادیر گزینش پذیری بالا را دوست دارد، به خصوص اگر آن ستون‌ها در بندهای WHERE پرس‌وجوهایی که اغلب اجرا می‌شوند گنجانده شوند. هرچه شاخص انتخابی تر باشد، موتور پرس و جو سریعتر می تواند اندازه مجموعه داده های حاصل را کاهش دهد. البته نکته منفی این است که ستون هایی با مقادیر نسبتاً کمی منحصر به فرد به ندرت کاندیدهای خوبی برای نمایه سازی خواهند بود.

آیا می توان یک شاخص غیر خوشه ای تنها بر روی یک زیر مجموعه خاص از داده های یک ستون کلید ایجاد کرد؟

به طور پیش‌فرض، یک شاخص غیرخوشه‌ای شامل یک ردیف برای هر ردیف در جدول است. البته در مورد یک شاخص خوشه‌ای می‌توانید با فرض اینکه چنین شاخصی یک جدول باشد، همین را بگویید. اما وقتی صحبت از یک شاخص غیر خوشه‌ای می‌شود، رابطه یک به یک مفهوم مهمی است زیرا با نسخه شروع می‌شود. SQL Server 2008، شما می توانید یک شاخص قابل فیلتر ایجاد کنید که ردیف های موجود در آن را محدود می کند. یک نمایه فیلتر شده می تواند عملکرد پرس و جو را بهبود بخشد زیرا ... از نظر اندازه کوچکتر است و حاوی آمار فیلتر شده و دقیق تر از همه موارد جدولی است - این منجر به ایجاد برنامه های اجرایی بهبود یافته می شود. یک شاخص فیلتر شده همچنین به فضای ذخیره سازی کمتر و هزینه های نگهداری کمتر نیاز دارد. ایندکس تنها زمانی به روز می شود که داده های مطابق با فیلتر تغییر کند.
علاوه بر این، ایجاد یک شاخص قابل فیلتر آسان است. در اپراتور ایجاد شاخصشما فقط باید نشان دهید جایی کهوضعیت فیلتر به عنوان مثال، همانطور که در کد نشان داده شده است، می توانید تمام ردیف های حاوی NULL را از فهرست فیلتر کنید:
ایجاد INDEX غیرکلاستری ix_trackingnumber در Sales.SalesOrderDetail(CarrierTrackingNumber) در جایی که CarrierTrackingNumber NULL نیست.
در واقع می‌توانیم هر داده‌ای را که در جستارهای حیاتی مهم نیست، فیلتر کنیم. اما مراقب باشید، زیرا ... SQL Serverمحدودیت‌های متعددی را برای نمایه‌های قابل فیلتر اعمال می‌کند، مانند عدم امکان ایجاد نمایه قابل فیلتر روی یک نما، بنابراین مستندات را با دقت بخوانید.
همچنین ممکن است با ایجاد یک نمای نمایه شده به نتایج مشابهی برسید. با این حال، یک شاخص فیلتر شده دارای چندین مزیت است، مانند توانایی کاهش هزینه های تعمیر و نگهداری و بهبود کیفیت برنامه های اجرایی شما. نمایه های فیلتر شده را می توان به صورت آنلاین نیز بازسازی کرد. این را با نمای نمایه شده امتحان کنید.

و باز هم کمی از مترجم

هدف از ظهور از این ترجمهدر صفحات Habrahabr این بود که از وبلاگ SimpleTalk به شما بگویم یا یادآوری کنم RedGate.
پست های سرگرم کننده و جالب زیادی منتشر می کند.
من به هیچ یک از محصولات شرکت وابسته نیستم RedGateو نه با فروش آنها.

همانطور که قول داده بودیم، کتاب هایی برای کسانی که می خواهند بیشتر بدانند
من سه کتاب بسیار خوب را از خودم توصیه می کنم (لینک ها به روشن شدننسخه های موجود در فروشگاه آمازون):

در اصل، می‌توانید فهرست‌های ساده را باز کنید و تگ‌ها را اضافه کنید

Microsoft SQL Server 2012 T-SQL Fundamentals (مرجع توسعه دهنده) نویسنده Itzik Ben-Gan تاریخ انتشار: ۲۴ تیر ۱۳۹۱ نویسنده، استاد کار خود، می دهد دانش عمومیدر مورد کار با پایگاه داده اگر همه چیز را فراموش کرده اید یا هرگز نمی دانید، قطعا ارزش خواندن دارد.

شاخص های ROWIDاشیاء پایگاه داده ای هستند که نمایشی از تمام مقادیر در یک ستون جدول و همچنین ROWIDهای تمام سطرهای جدول که حاوی مقادیر ستون هستند را ارائه می دهند.

ROWIDیک شبه ستون است که یک شناسه منحصر به فرد برای یک ردیف در یک جدول است و در واقع موقعیت فیزیکی دقیق آن ردیف خاص را توصیف می کند. بر اساس این اطلاعات اوراکلمتعاقباً می تواند داده های مرتبط با ردیف جدول را پیدا کند. هر بار که یک ردیف جابجا می شود، صادر می شود، وارد می شود، یا هر عملیات دیگری که مکان آن را تغییر می دهد، ROWIDخط چون موقعیت فیزیکی متفاوتی را اشغال می کند. برای ذخیره سازی داده ها ROWID 80 بیت (10 بایت) مورد نیاز است. شناسه ها ROWIDاز چهار جزء تشکیل شده است: شماره شی (32 بیت)، شماره فایل نسبی (10 بیت)، شماره بلوک (22 بیت) و شماره خط (16 بیت). این شناسه ها به صورت توالی 18 کاراکتری نمایش داده می شوند که مکان داده ها را در پایگاه داده نشان می دهد، با هر کاراکتر در قالب پایه 64، که شامل موارد زیر است. شخصیت های A-Z، a-z، 0-9، + و /. شش کاراکتر اول شماره شی داده، سه کاراکتر بعدی شماره فایل نسبی، شش کاراکتر بعدی شماره بلوک و سه کاراکتر آخر شماره خط هستند.

مثال:

انتخاب خانواده، ROWIDاز دانش آموز؛

FAM ROWID

——————————————

ایوانوف AAAA3kAAGAAAAGsAAA

PETROV AAAA3kAAGAAAAGsAAB

در پایگاه داده اوراکلایندکس ها برای اهداف مختلفی استفاده می شوند: برای اطمینان از منحصر به فرد بودن مقادیر در پایگاه داده، بهبود عملکرد جستجوی رکوردها در یک جدول و غیره. عملکرد با گنجاندن ارجاع به ستون یا ستون های نمایه شده در معیارهای جستجو بهبود می یابد. برای داده های جدول که در اوراکلایندکس ها را می توان در هر ستون جدول به جز ستون های LONG ایجاد کرد. شاخص‌ها بین برنامه‌های حساس به سرعت و برنامه‌های کاربردی با کارایی بالا، به ویژه هنگام کار با جداول بزرگ تفاوت قائل می‌شوند. با این حال، قبل از تصمیم گیری برای ایجاد یک شاخص، باید جوانب مثبت و منفی عملکرد سیستم را بسنجید. اگر به سادگی یک شاخص را وارد کنید و آن را فراموش کنید، عملکرد بهبود نخواهد یافت.

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

استفاده از نمایه‌ها برای جستجوی اطلاعات در جداول می‌تواند بهبود عملکرد قابل توجهی را نسبت به جداول اسکن که ستون‌های آنها نمایه نشده‌اند، ایجاد کند. با این حال، انتخاب شاخص مناسب اصلا آسان نیست. البته، ستونی که همه مقادیر آن منحصربه‌فرد هستند برای نمایه‌سازی با شاخص درخت B ترجیح داده می‌شود، اما ستونی که این الزامات را برآورده نمی‌کند کاندیدای خوبی است تا زمانی که حدود 10 درصد از ردیف‌های آن دارای مقادیر یکسان باشند. و نه بیشتر ستون‌های «سوئیچ» یا «پرچم»، به‌عنوان مثال، آن‌هایی که اطلاعات مربوط به جنسیت یک فرد را ذخیره می‌کنند، برای نمایه‌های درخت B مناسب نیستند. ستون‌هایی که برای ذخیره تعداد کمی «مقادیر قابل اعتماد» استفاده می‌شوند و همچنین آن‌هایی که ذخیره می‌کنند. مقادیر معینی نیز مناسب نیستند، سپس علائمی مانند «قابلیت اطمینان» یا «غیرقابل اعتماد»، «فعالیت» یا «عدم فعالیت»، «بله» یا «خیر» و غیره و غیره در نهایت، ایندکس ها با کلیدهای معکوس هستند. به عنوان یک قاعده در جایی که نصب و کار می کند استفاده می شود اوراکلسرور موازی و باید سطح موازی سازی در پایگاه داده را به حداکثر برسانید.

برای شروع، من به شما پیشنهاد می کنم بفهمید که چیست شاخص پوشش، من گزیده ای از مقاله ای در Habré می آورم:

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

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

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

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

ممکن است پاسخ سوال دیگری برای شما مفید باشد.

مثال بالا به جای نمایه پوششی از یک نمایه ترکیبی 3 فیلدی استفاده می کند، کد ایجاد نمایه پوششی به این صورت است:

ایجاد نمایه غیرکلاستری در . ( ASC) شامل (، ) WITH (PAD_INDEX = OFF، STATISTICS_NORECOMPUTE = OFF، SORT_IN_TEMPDB = OFF، IGNORE_DUP_KEY = OFF، DROP_EXISTING = OFF، ONLINE = OFF، ALLOW_ROWON_LOCK = ALLOW_ROWON_LOCKS)

برای پاسخ به سوال شما:

برای یک نمایه پوششی، ترتیب ستون ها در بخش INCLUDE مهم نیست, اما ترتیب ستون ها برای یک شاخص ترکیبی مهم است، زیرا داده‌های ستون در درخت شاخص به ترتیبی که ستون‌ها فهرست شده‌اند قرار می‌گیرند و بهینه‌ساز پرس و جو نمی‌تواند از یک فهرست ۲ ستونی برای جستجوی مقادیر تنها ۲ ستون استفاده کند. شما می توانید یک مثال واضح از اینکه ساختار شاخص 2 ستون (EMPLOYEE_ID، SUBSIDIARY_ID) چگونه به نظر می رسد را در شکل مشاهده کنید.

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

تنوع زیادی در این اپراتور وجود دارد، زیرا استاندارد نیست، زیرا استانداردها مسائل مربوط به عملکرد را بررسی نمی کنند.

2) ایجاد نمایه ها
ایجاد شاخص
بر()

3) تغییر و حذف نمایه ها
برای کنترل فعالیت شاخص از عملگر استفاده می شود:
ALTER INDEX
برای حذف یک شاخص، از عملگر استفاده کنید:
DROP INDEX

الف) قوانین انتخاب جدول
1. توصیه می شود جدول هایی را فهرست بندی کنید که در آنها بیش از 5٪ از ردیف ها انتخاب نشده اند.
2. جداولی که در عبارت WHERE عبارت SELECT دارای موارد تکراری نیستند باید ایندکس شوند.
3. ایندکس کردن جداول که اغلب به روز می شوند عملی نیست.
4. فهرست کردن جداول که بیش از 2 صفحه را اشغال نمی کنند نامناسب است (برای Oracle این کمتر از 300 ردیف است)، زیرا اسکن کامل آن بیشتر طول نمی کشد.

ب) قوانین انتخاب ستون
1. کلیدهای اصلی و خارجی - اغلب برای پیوستن به جداول، بازیابی داده ها و جستجو استفاده می شود. اینها همیشه شاخص های منحصر به فرد با حداکثر کاربرد هستند
2. هنگام استفاده از گزینه های یکپارچگی ارجاعی، همیشه به یک شاخص در FK نیاز دارید.
3. ستون هایی که داده ها اغلب بر اساس آنها مرتب و/یا گروه بندی می شوند.
4. ستون هایی که اغلب در عبارت WHERE یک عبارت SELECT جستجو می شوند.
5. نباید بر روی ستون های توصیفی طولانی ایندکس ایجاد کنید.

ج) اصول ایجاد نمایه های ترکیبی
1. شاخص های ترکیبی در صورتی خوب هستند که ستون های منفرد مقادیر منحصر به فرد کمی داشته باشند، اما شاخص ترکیبی منحصر به فرد بودن بیشتری را ارائه می دهد.
2. اگر تمام مقادیر انتخاب شده توسط عبارت SELECT متعلق به یک شاخص ترکیبی باشد، آنگاه مقادیر از نمایه انتخاب می شوند.
3. اگر عبارت WHERE از دو یا چند مقدار ترکیب شده با عملگر AND استفاده کند، باید یک شاخص ترکیبی ایجاد شود.

د) ایجاد توصیه نمی شود
ایجاد ایندکس بر روی ستون ها، از جمله ستون های ترکیبی، توصیه نمی شود که:
1. به ندرت برای جستجو، ادغام، و مرتب سازی نتایج پرس و جو استفاده می شود.
2. حاوی مقادیری که به طور مکرر تغییر می کنند، که نیاز دارد به روز رسانی های مکررایندکس عملکرد پایگاه داده را کند می کند.
3. حاوی تعداد کمی از مقادیر منحصر به فرد (کمتر از 10٪ m/f) یا تعداد غالب خطوط با یک یا دو مقدار (شهر محل اقامت تامین کننده مسکو است).
4. توابع یا یک عبارت در عبارت WHERE برای آنها اعمال می شود و ایندکس کار نمی کند.

ه) نباید فراموش کنیم
باید برای کاهش تعداد ایندکس ها تلاش کنید، زیرا تعداد زیادی از آنها سرعت به روز رسانی داده ها را کاهش می دهد. بنابراین، MS SQL Server توصیه می کند که بیش از 16 فهرست در هر جدول ایجاد نکنید.
به طور معمول، ایندکس ها برای اهداف پرس و جو و حفظ یکپارچگی ارجاعی ایجاد می شوند.
اگر از ایندکس برای پرس و جو استفاده نمی شود، باید حذف شود و یکپارچگی ارجاع باید با استفاده از محرک ها تضمین شود.