نمایه سازی پایگاه داده ضعیف - یک قاتل عملکرد پرس و جو SQL - توصیه ها

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

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

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

با داشتن یک شاخص خوشه ای مناسب ، برای بازیابی سوابق درخواست شده توسط یک پرس و جو یا روش ذخیره شده ، خوانده شده کمتر مورد نیاز است. بنابراین ، کمتر دیسک I/O از پیش ساخته شده و عملیات سریعتر انجام می شود.

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

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

فهرست بندی ضعیف چیست؟

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

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

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

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

از کدام ستون برای ساخت یک فهرست استفاده می شود؟

هر دو شاخص خوشه ای و غیر خوشه ای از یک یا چند ستون جدول می توانند ساخته شوند.

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

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

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

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

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

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

با استفاده از ستونی که داده های بزرگی را ذخیره می کند ، مانند ستون های Blob (متن ، Nvarchar (حداکثر) ، تصویر و غیره) و ستون های GUID توصیه نمی شود. استفاده از مقادیر بزرگ برای مرتب سازی داده ها کارآمد نیست ، و در مورد ستون های GUID و تصویر معنی ندارد.

یک شاخص خوشه ای نباید بر روی ستونی که قبلاً در یک شاخص منحصر به فرد استفاده می شود ساخته شود.

در تست های زیر از جدول Person. Address در پایگاه داده AdventureWorks استفاده کردیم.

جدول تقریباً یکسان را ایجاد کردیم. ADDRess1 جدول. تفاوت این است که ستون AddressID یک شاخص غیر خوشه ای است و شاخص خوشه ای در ستون City غیر Unique ایجاد می شود. داده ها همانند شخصاً است.

) با (pad_index = خاموش ، statistics_noreCompute = خاموش ، sort_in_tempdb = خاموش ، drop_existing = خاموش ، آنلاین = خاموش ، any_row_locks = روشن ، ant_page_locks = روشن) در [اولیه]

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

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

همانطور که در خصوصیات اسکن شاخص خوشه ای نشان داده شده است ، هزینه های تخمین زده شده I/O ، اپراتور و زیر درخت تقریباً دو برابر بیشتر از نظر شخص است.

مضرات استفاده از شاخص ها

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

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

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

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

هنگامی که صحبت از اجرای یک عبارت SELECT می شود ، در هنگام مشخص شدن لیست ستون های برگشتی ، هزینه کمترین هزینه است و بیانیه در جدول اجرا می شود که در آن شاخص خوشه ای در ستون کلید اصلی ایجاد می شود. هزینه می تواند برای یک جدول با یک شاخص خوشه ای غیر بهینه (نشان داده شده در ستون 2) ، سپس در جداول با شاخص غیر خوشه ای یا به هیچ وجه.

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

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

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

Milena یک حرفه ای سرور SQL است که بیش از 20 سال تجربه در آن دارد. او با برنامه نویسی رایانه در دبیرستان شروع کرده و در دانشگاه ادامه دارد.

وی از سال 2005 با SQL Server همکاری کرده و با SQL 2000 از طریق SQL 2014 تجربه دارد.

مباحث مورد علاقه وی SQL Server بازیابی فاجعه SQL Server ، حسابرسی و نظارت بر عملکرد است.