بهینه سازی ایندکس ها (Indexes) در SQL Server

در باره ی پاکسازی ایندکس های بلااستفاده و تکراری، فکر می کنم هر مدیر پایگاه داده ای حداقل یک بار چیزی خوانده است. همه می گویند ایندکس های اضافه گران اند، از حافظه می خورند و سیستم را در بخش نوشتن کند می کنند. حتی ابزارهای خوبی مثل sp_BlitzIndex درست شده اند که بدون دردسر کار را راحت می کنند.

پس تا اینجا، هیچ چیز جدیدی نبود. اما مشکل اینجاست: اکثر منابع، به جنبه ی کمی قضیه می پردازند و جنبه ی کیفی را نادید می گذارند. آن ها می گویند «از این کوئری استفاده کن تا ایندکس های بلااستفاده را پیدا کنی»؛ اما هیچ کس به شما نمی گوید «چرا شاید نباید برخی از آن ها را حذف کنی».

این دقیقاً همان چیزی است که می خواهم در باره ی آن صحبت کنیم: قبل از اینکه دست به حذف یا تغییر ایندکسی ببرید، یک لحظه مکث کنید و به چند نکته فکر کنید.

اعداد دروغ نمی گویند، اما ناقص اند

نگاه کردن به آمار بسیار وسوسه انگیز است. مثلاً در sys.dm_db_index_usage_stats می بینیم که تعداد بنویشت یک ایندکس از خواندهش بیشتر است و فوراً فکر می کنیم: «این ایندکس ضایع است».

اما این نگاه، خیلی سطحی است. بگذارید چند مثال بدهیم که چرا این نتیجه گیری همیشه درست نیست.

یک هفته فقط نشانه ی اینچقدر است

فرض کنید ایندکسی را در نظر بگیرید که در یک هفته اخیر هیچ خواندهی نداشته است. آیا معنی این است که بلااستفاده است؟

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

آیا مطمئنید همه ی ایندکس ها را دیده اید؟

یک نکته ناراحت کننده: ممکن است ایندکسی هنوز اصلاً در جدول آمار وجود نداشته باشد. این وضعیت به ویژه در ثانویه های Availability Group پیش می آید که الگوهای کاری کاملاً متفاوتی دارند.

پس همیشه از همه ی ایندکس ها شروع کنید، نه فقط آن هایی که در آمار دیده می شوند:

SELECT i.*
 FROM sys.indexes AS i
 LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s
 ON s.object_id = i.object_id AND s.index_id = i.index_id; 

ترتیب ستون ها مقدار دارد

دو ایندکس (CustomerID, OrderDate) و (OrderDate, CustomerID) را در نظر بگیرید. از نظر برخی اسکریپت ها، هر دو «یکسان» به شمار می روند. اما واقعیت این است که این دو کاملاً متفاوتند و ممکن است هر دو، هر کدام برای یک کوئری خاصی، لازم باشند.

فیلتر و INCLUDE را فراموش نکنید

حتی اگر ستون های کلیدی دو ایندکس عین باشند، وجود فیلترهای متفاوت می تواند آن ها را کاملاً متمایز کند. مثال روشن: در جدول Posts سایت Stack Overflow، ممکن است عمداً یک ایندکس برای سوال ها (PostTypeId = 1) و یکی دیگر برای جواب ها (PostTypeId = 2) داشته باشید. هر دو با ستون های کلیدی یکسان، اما با کار کاملاً متفاوت.

همینطور برای INCLUDE، ممکن است یک ایندکس عریض برای گزارش های سنگین و یکی دیگر باریک برای کارهای روزمره داشته باشید.

و یک هشدار: به sp_helpindex اعتماد نکنید. این ابزار برای امکانات جدید مثل فیلتر و ستون های INCLUDE به روز نشده است و می تواند دو ایندکس کاملاً متفاوت را به شما یکسان نشان دهد. به جای آن، از sp_BlitzIndex یا sp_SQLskills_helpindex استفاده کنید.

پاکسازی فقط حذف نیست؛ اصلاح هم است

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

ایندکسی که باید Unique باشد، ولی نیست

اگر ایندکسی به دلیل پیش بودن کلید اصلی، ذاتاً منحصر به فرد است، بهتر است آن را به صورت UNIQUE بسازیم. این تغییر لزوماً عملکرد را سریع تر نمی کند، اما هدف و محتوای ایندکس را برای سایر همکاران کاملاً روشن می سازد. حتی افرادیکه کوئری می نویسانند، دیگر نیازی به DISTINCT اضافه برای حذف تکراری ها ندارند. بگذارید متادیتا، خودش مستندسازی باشد.

ایندکسی که مرتب سازی را پیشتیبانی نمی کند

مثال کلاسیک: ایندکسی ساخته شده برای Join با جدول والد، اما برای ORDER BY درست نیست. مثلاً:

-- قبل
CREATE INDEX ix_orders_fk
 ON Orders (CustomerID)
 INCLUDE (OrderDate, Status);

-- بعد
CREATE INDEX ix_orders_fk
 ON Orders (CustomerID, OrderDate)
 INCLUDE (Status); 

در نسخه اول، کوئری که به ترتیب CustomerID, OrderDate نیاز دارد، باید خودش عملیات مرتب سازی انجام دهد. اما با جابجایی OrderDate به بخش Key، عملیات Sort حذف می شود — و حجم ایندکس اصلاً افزایش نمی یابد. بدون شک، یکی از بهترین اصلاحات، این است.

نام های ریشته ی قدیمی

برخی ایندکس ها در دوران تست ساخته شده اند و نام شان تغییر نکرده است. وسوسه دارید که نام شان را تصحیح کنید؟ احتیاط کنید. ممکن است آن نام، در کوئری ها، اسکریپت های Deployment یا روتین های Maintenance به صورت صریح ارجاع داده شده باشد. در این صورت، تغییر نام، همه چیز را به هم می ریزاند.

مهم ترین بخش: وقتی مطمئن نیستید، به دنبال زمینه بگردید

آمار فقط گذشته را نشان می دهد و حتی این را هم با دقت محدود. نمی تواند به شما بگوید که فردا چه کوئری نوشته خواهد شد. پس اگر دلیل ایجاد یک ایندکس را نمی دانید:

• از تیمی که روی آن ویژگی کار کرده است بپرسید.
• در سیستم کنترل نسخه و تیکتینگ به دنبال ارجاعات بگردید.
• ایمیل و Slack و هر ابزار دیگری را جستجو کنید. و واژه ها را گسترش دهید. مثلاً برای جدول CustomerOrders، از «slow» و «orders» شروع کنید؛ حتی شاید باید به «orders and index» نیز نگاه کنید.

و در پایان، مهم ترین توصیه: تغییرات را ابتدا در محیط Development یا Test آزمایش کنید. شبیه سازی کامل بار عملیاتی سخت است، اما باید ایده ای از سنگین ترین کوئری های سیستم خود داشته باشید.

«بگذار اجرا کنیم و ببینیم چی می شود» یک استراتژی است، ولی اگر می توانید، بهتر از آن آماده باشید.