ایندکس در SQL SEVER چیست؟ - مقدمه

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

در مباحث performance tuning خواهیم دید که ایندکس ها قلب و روح بهینه سازی محسوب می شوند. شما به ندرت، کتاب، آموزش و یا دوره مرتبط با sql server را پیدا می کنید که فاقد بحث ایندکس در sql server باشد. در واقع پس از فراگیری مباحث مقدماتی و تئوریک در sql server، از زمان شروع به انجام پروژه و توسعه برنامه، با ایندکس در sql server سر و کار پیدا خواهید کرد تا فرآیند پشتیبانی و زمانی که برنامه در حال سرویس دهی باشد یعنی از زمانی که جداول را ایجاد می کنید یا نیاز به استفاده از یک تکنولوژی(partitioning- full text search) دارید تا زمان رفع مشکل کندی سرعت و ... همه و همه مستلزم آشنایی عمیق با ایندکس در sql server است. درادامه به برخی از مهمترین کاربردهای ایندکس در sql server اشاره کرده ایم:

     1. افزایش سرعت جستجو در اطلاعات جدول

     2. بررسی سریع عدم وجود مقدار تکراری درPK / UK  در عملیات درج

     3. افزایش کارایی عملیات جوین ( حذف Lookup - حافظه مورد نیاز  - Hash Joinکاهش Memory contention )

     4. افزایش سرعت عملیات گروهبندی (Group By )، مرتب سازی (Sort)، توابع ستونی

     5. افزایش کارایی عملیات حذف و بروزرسانی (Foreign Key)

     6. افزایش سرعت جستجوی مقدار خاص و یا رنجی از رکوردها (Clustered)

     7. کاهش مدت زمان Locking  جدول در عملیات (همزمانی - CheckDB)

     و ...

---

 ایندکس در SQL SEVER چیست؟ - اهمیت

در شکل زیر یک فلوچارت نمونه از مراحل گام به گام بررسی و رفع مشکلات کارایی کوئری در sql server نمایش داده شده است:

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

تصویر زیر نتایج یک نظر سنجی در مورد مهمترین عامل/عوامل مشترک در مشکلات کارایی SQLSERVER است:

در این نظر سنجی که تعداد 334 نفر از متخصصین و کاربران حوزه sql server به آن پاسخ داده اند، طراحی نادرست کوئری و ایندکس گذاری ضعیف از مهمترین عوامل مشترک در مشکلات کارایی محسوب شده اند و تجارب عملی ما نیز این مورد را تأیید می کند. پس ما به عنوان یک برنامه نویس و طراح دیتابیس، با ایندکس در sql server از زمان ایجاد جداول در دیتابیس و رفع مشکلات کارایی تا زمان پشتیبانی و حیات یک نرم افزار سرو کار خواهیم داشت.

---

 ایندکس در SQL SEVER چیست؟ - مفهوم

 برای آشنایی با مفهوم و کاربرد ایندکس در sql server از دو مثال کتاب و دفتر تلفن استفاده می کنیم. فرض کنید شما یک کتاب با عنوان "ایندکس در SQL SEVER چیست" دارید خوب به طور معمول در ابتدای هر کتاب، فهرست مطالب آن قرار دارد به این صورت که به ازای هر مطلب، شماره صفحه آن مشخص شده است. اگر مثلاً شما قصد مطالعه در مورد مفهوم ایندکس در sql server را داشته باشید، در ابتدا سراغ فهرست مطالب از کتاب می روید و عنوانِ مفهوم ایندکس در sql server را در بین عناوین فهرست جستجو می کنید و نهایتاً شماره صفحه مربوط به این مطلب را مشاهده و به آن صفحه مراجعه می کنید:

پس با توجه به شکل بالا مراحل یافتن مطلب مورد نظر در یک کتاب شامل دو مرحله جستجو در فهرست کتاب و مراجعه به صفحه مربوط به آن مطلب می باشد.

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

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

     1. فهرست کتاب در محلی جدا از مطالب اصلی آن قرار گرفته است(در ابتدا یا انتهای کتاب)

     2. یک کتاب می تواند چندین فهرست داشته باشد(بر اساس مطالب - تصاویر و هر فهرست دلخواه دیگر )

و اما در مورد دفتر تلفن:

     1. فهرست در دفتر تلفن در کنار مطالب(افراد و مشخصات تماس آن ها) قرار گرفته است.

     2. تنها می توانیم یک فهرست داشته باشیم! در واقع فهرست ما همان حروف الفبای نشانه گذاری شده هر صفحه هستند. 

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

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

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

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

---

 ایندکس در SQL SEVER چیست؟ - انواع

به طور کلی ایندکس در sql server به پنچ دسته کلی تقسیم می شود:

     1. Clustered Index

     2. Non Clustered Index

     3. XML Index

     4. Spatial Index

     5. Column Stored Index

از بین پنج نوع ایندکس نام برده شده در بالا، دو مورد اول تقریباً در تمام دیتابیس ها(OLTP) دیده می شود و در ادامه نیز قصد داریم به معرفی این دو نوع با استفاده از مطالب دکر شده در قسمت قبل بپردازیم. در واقع می توانیم ایندکس نان کلاستر(Non Clustered) در sql server را معادل با فهرست در کتاب در نظر بگیریم. دستور ایجاد یک ایندکس نان کلاستر به ازای یک فیلد جدول در sql server بصورت زیر است:

CREATE NONCLUSTERED INDEX IX_Family  ON dbo.Employee (Family)

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

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

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

نکته: مزیت ایندکس(فهرست کتاب) در کم حجم بودن نسبت به کل اطلاعات(جدول/کتاب) و نیز مرتب(sort) بودن اطلاعات است که سرعت جستجو را به میزان زیادی افزایش می دهد.

ایدکس کلاستر در sql server معادل با دفتر تلفن در دنیای واقعی است. گفتیم که در دفتر تلفن برای علامتگذاری صفحات از حروف الفبا استفاده می کنیم. در ایندکس کلاستر نیز از Row Offset Array(یک اشاره گر دو بایتی) جهت تعیین ترتیب رکوردها استفاده می شود. دستور ایجاد یک ایندکس نان کلاستر به ازای یک فیلد جدول در sql server بصورت زیر است:

CREATE CLUSTERED INDEX IX_Family  ON dbo.Employee (Family)

با اجرای دستور بالا بر خلاف ایندکس نان کلاستر، بدون اینکه اطلاعاتی به فضای جداگانه ای منتقل شود، در ساختار جدول اصلی تغییراتی ایجاد می شود(فعلاً به جزئیات نمی پردازیم) که یکی از آین تغییرات اضافه شدن Row Offset Array یا Slot Array به جدول، جهت تعیین ترتیب رکوردها بر اساس فیلد Family و افزایش سرعت جستجو است.

با توجه به شکل بالا کاملاً مشخص است که به ازای یک جدول، تنها و تنها یک ایندکس کلاستر(Clustered Index) بیشتر نمی توانیم داشته باشیم. زیرا در واقع ایندکس کلاستر و جدول اصلی جدا از همدیگر نیستند(دقیقاً مشابه با دفتر تلفن). برای جستجو در ایندکس کلاستر نیز با مراجعه به Row Offset Array با توجه به مرتب(sort) بودن آن بر اساس فیلد Family به راحتی اطلاعات مورد نظر را در اختیار داریم. دقت کنید در این حالت، کلیه اطلاعات در اختیار ما قرار دارد(مانند دفتر تلفن) و بر خلاف ایندکس نان کلاستر(فهرست کتاب) نیازی به مراجعه کردن به محلی دیگر جهت دستیابی به اطلاعات تکمیلی نیست.

نکته: بررسی دقیق معماری ایندکس کلاستر و نان کلاستر نیازمند آشنایی با ساختار b-tree است که در آموزشی جداگانه به آن پرداخته خواهد شد.

---

 ایندکس در SQL SEVER چیست؟ - جمع بندی

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

در آموزش های آتی(در مجموعه چیستان و سایر مجموعه ها) با معماری ایندکس کلاستر و نان کلاستر، چگونگی تأثیر ایندکس در تسریع انواع عملیات، نگهداری از ایندکس ها، مهمترین عوامل کاهش کارایی ایندکس ها، مراحل گام به گام ایندکس گذاری از ابتدای ایجاد جداول، مناسب ترین فیلدها جهت ایندکس گذاری و ... آشنا خواهیم شد و ابزارها و اسکریپت هایی در این مورد ارائه خواهیم کرد.

• قبلی
• بعدی