‫تاثیر Index گذاری بر سرعت جداول MS SQL

چند روزی را در حال بررسی سرعت Query بر روی جداول حجیم بودم. Query مورد نظر من یک sum ساده بر روی جدولی به اسم amort بود. این Query به صورت یک function پیاده سازی شده بود. البته آن Query نهایی که من زمان آن را بررسی کرده و نتایجش را در نظر داشتم این function را همراه با یک select ساده صدا می زد. خود این select آخر روی یک جدول با ۷۰۰۰ رکورد اجرا می‌شد. در نتیجه اگر فرض کنیم جدول amort دارای ۳۰۰ رکورد است پس ما عملا با انجام عملیات روی ۳۰۰ ضرب در ۷۰۰۰ یعنی ۲۱۰۰۰۰۰ رکورد طرف هستیم. تنها عاملی که آن را در آزمایشات مختلف تغییر می‌دادم تعداد رکوردهای جدول amort (که function مورد نظر من روی آن اجرا می‌شد) و ایندکس داشتن و نداشتن همان جدول amort بود. تمام آزمایشات بر روی یک کامپیوتر Pentium 4, 2.26 Ghz, 1GB RAM, Win Xp SP2 و Microsoft Sql Server 2008 انجام شده است.

نتایج بدون ایندکس واقعا فاجعه بار بودند. برای ۷۲۰۰ رکورد ۲۱۳ ثانیه طول کشید! این در حالی بود که هر سال همین تعداد رکورد به جدول مورد نظر من یعنی amort اضافه می‌شد. به عبارتی دیگر اجرای Query مورد نظر برای سال هشتم حدود نیم ساعت کشنده طول می‌کشد! با این که روی همه جداول دیتابیس بر روی ستون id ایندکس وجود داشت به علت کندی بیش از حد مجبور شدم باز هم به ایندکس‌ها، انواع آن و تاثیرشان بر کارایی فکر کنم. در MS SQL چهار نوع ایندکس وجود دارد که من فقط دو تای پر استفاده‌تر آن را مطالعه و بررسی کردم: Clustered Index و Non-Clustered Index. هر دوی آنها اطلاعات را به صورت sort نگهداری می‌کنند. بر اساس توضیحات اینجا، نوع Clustered اطلاعات را به صورت فیزیکی به حالت sort نگه می‌دارد و در نتیجه در هر جدولی فقط یک ایندکس از این نوع مجاز است در حالی که نوع Non-Clustered از یک ساختار اضافه برای نگهداری اطلاعات sort جدول استفاده می‌کند و می‌توان حدود اقلا ۲۵۰ ایندکس از این نوع در یک جدول داشت. در بعضی منابع خوانده بودم که performance ایجاد شده در هر دو نوع تقریبا یکسان است در حالی که در آزمایشات خودم فهمیدم سرعت Clustered حدود ۱۰ برابر Non-Clustered است. دقت کنید تعریف یک یا چند ستون به عنوان Primary Key باعث می‌شود آن ستون یا ستون‌ها خود به خود به یک Clustered Index تعریف شوند. در همه انواع ایندکس‌ها دو مفهوم ReOrganize و ReBuild وجود دارد که مفهوم آنها را خیلی نفهمیدم ولی متوجه شدم که برای دستیابی به حداکثر کارایی هر از چندگاهی باید این دو عملیات را بر روی جدول مورد نظر اجرا کرد.

نتایج آزمایشم را با هر دو نوع ایندکس تکرار کردم. پرس و جو آن قدر سریع اجرا می‌شد که خیلی سریع تعداد رکوردها را از ۷۲۰۰ به ۷۲۰ هزار، یک و نیم میلیون رکورد و نهایتا ۳ میلیون رکورد رساندم. نتایج کار اگر من مرتکب اشتباهی نشده باشم واقعا شگفت آور بود: زمان مورد نیاز برای اجرا در حالت استفاده از Clustered Index کمتر از یک ثانیه و در حالت استفاده از None Clustered Index حدود ۱۰ ثانیه بود! دقت کنید که ۳ میلیون رکورد در جدول amort و ۷ هزار رکورد در select استفاده کننده function من یعنی ۲۱ میلیارد حالت!!

پ. ن.: برای تولید ۳ میلیون رکورد از مقادیر اتفاقی int و تاریخ در یک حلقه while استفاده کردم. نحوه تولید «تاریخ» به صورت random در اینجا و اینجا بحث شده است.