آموزش مبانی index ها
در این درس از دوره “SQL Server Essential Training” به مبانی Indexes پرداخته می‌شود؛ به‌طوری که نقش حیاتی آن‌ها در بهبود Performance و سرعت اجرای Queryها در SQL Server مورد بررسی قرار می‌گیرد. محتوا شامل تعریف Indexes، بررسی تفاوت‌های Clustered و Non-Clustered Indexes، و نکات تخصصی در زمینه طراحی، مدیریت و نگهداری بهینه آن‌هاست؛ همچنین راهکارهایی برای مقابله با Fragmentation ارائه شده که از کاهش کارایی سیستم جلوگیری می‌کند. این آموزش تخصصی با ارائه مثال‌های عملی، متخصصان و کارشناسان را در درک عمیق مفاهیم و استراتژی‌های بهینه‌سازی پایگاه داده راهنمایی می‌کند.
در این جلسه، ما میخوایم که راجع به INDEX ها صحبت کنیم. ایندکس در پایگاه داده بسیار شبیه ایندکس در کتابخانه ها است. ایندکس ها کمک می کنند تا اطلاعات در پایگاه داده سریعتر مکان یابی بشن. شبیه عملکردی که در کتابخانه ها انجام می شه . اگه همه کتاب ها توی قفسه های کتابخونه براساس حروف الفبا مرتب سازی بشه، شما دیگه نیاز ندارین تا کل کتابخونه رو برای اینکه کتاب خاصی پیدا کنین، بگردین. در عوض با استفاده از حرف اول عنوان کتاب، بخش مربوط به اون حرف رو پیدا میکنین و بعد بین کتاب های اون بخش میگردین. در این صورت جستجو محدود میشه، و سرعت شما در پیدا کردن اطاعات مد نظرتون فوق العاده بالا میره.
یک index و یک database، یک کپی از یک زیرمجموعه از جداول داده ای رو حفظ میکنه، و در جایی که ما براش تعریف می کنیم ایندکس ها رو جداگونه ذخیره می کنه. بنابراین، یک index با یک جدول پیوند میخوره و index در واقع شامل بعضی از داده هایی هست که توی اون جدول وجود داره. اما همه داده هایی که در جدول مربوطه هست در ایندکس قرار نداره. در واقع نیاز نیست که همه اون داده ها رو به طور کامل داشته باشه و برخی از این داده ها رو به عنوان مشخصه ای از اون جدول داشته باشه، کافیه.
بعضی مواقع هم چون فقط یکسری از داده های یک جدول خیلی مورد استفاده مون هستن ما فقط اون داده های خاص رو ایندکس می کنیم. ایندکس ها، با یک یا بیشتر از یک ستون، مرتب شدن و این دو ویژگی با این واقعیت که ایندکس یک زیر مجموعه و یک مقدار کمتری از داده ها است، ترکیب شدن و در حقیقت صورت مرتب شده اون باعث search سریعتر هم میشه.
بدیهی است که جستجو در یک ساختار داده ی کوچیک، خیلی سریعتر از جستجو در یک ساختار داده بزرگ و همچنین زمانی که بعضی چیزها مرتب میشن، ما میتونیم خیلی سریعتر جستجو رو انجام بدیم. اگه ما بخایم یک مقدار خاصی که با حرف a شروع میشه، رو جستجو کنیم، دستگاه میفهمه که تنها باید قسمت اول از index رو جستجو کنه، همچنین اگه ایندکس بر اساس یک ستون، مرتب شده باشه، این ایتم ها در بالا قرار میگیرن. بد نیست بدونید که این حرکت نزولی index ها، همواره باید update بشه. معمولا در هر بار که یک جدول بروز میشه، هر بار، همه اون index ها باید update بشن و بنابراین به همین دلیل هست که update کردن داده یا قرار دادن داده جدید در database مون ممکنه بیشتر طول بکشه.
بنابراین، با index ها، که قائده کلی حرکت ما هست، باعث میشه که داده رو سریع تر جستجو کنیم. اما ممکنه که بروز رسانی یا قرار دادن داده های جدید رو همونطور که گفتم، کندتر کنه. Microsoft SQL Server 2016 چندین نوع متفاوت index داره.
Clustered، nonclustered، columnstore، spatial، XML و full-text.
که در ادامه ما در مورد هر کدوم به صورت مجزا صحبت میکنیم.
یک clustered index تعیین میکنه که چطور یک جدول، روی دیسک نوشته میشه. بنابراین اگه ما یک clustered index ایجاد کنیم و با یک ستون مطمئن اون رو مرتب کنیم، هنگامی که جدول درواقع در هارد دیسک نوشته شد، اون در همون جهتی که clustered indexهست، نوشته میشه و بنابراین، ما میتونیم برای هر جدول یک cluster index داشته باشیم. اگه ما برای هر جدول دو تا ایندکس داشته باشیم، ممکنه روی این مساله که اطلاعات چطور روی دیسک نوشته بشن، در واقع روی طریقه نوشته شدن اون ها روی هارد دیسک، تصادف یا برخورد پیش بیاد. بنابراین تنها یک clustered index برای هر جدول وجود داره و زمانی که شما یک primary key روی یک جدول ایجاد میکنین، یک clustered index به صورت اتوماتیک ایجاد میشه.
شما میتونین clustered index رو تغییر بدین، خیلی کم پیش میاد که این اتفاق دیده بشه، معمولا در هر صورت primary key تعریف میشه، ما بالطبع میخوایم clustered index هم در همون جهت تعریف بشه. بعد از اون nonclusted index هست که رایج ترین نوع ایندکس ها درsql server هست. ما میتونیم تا 999، nonclustered indexes برای هر جدول داشته باشیم و معمولا این در واقع بیشتر از اون چیزی هست که ما نیاز داریم. خیلی کم پیش میاد که بیشتر از 10 یا 20 index نیاز داشته باشیم. چون ما با یک بررسی کوچیک متوجه میشیم که مرتب شدن چه داده هایی می تونه سرعت ما رو در اجرای مثلا کوئری هامون بالا ببره و این بررسی بیشتر از تعریف چند ایندکس رو بهمون پیشنهاد نمیده.
مگه اینکه شما داده های خیلی تخصصی انبار کنین، که در این صورت 999 اندازه کافی ای خواهد بود. columnstore index، داده رو که معمولا در ستونها ذخیره میشن رو طوری تغییر میده که داده ها در ردیف ها ذخیره بشن، اون میتونه یک افزایش عملکرد و کارایی قابل توجهی رو برای انواع خیلی خاص از داده فراهم کنه. Spatial indexe با انواع مختلفی از داده ها استفاده میشه. انواع داده ای مثل، داده های هندسی و داده های جغرافیایی. که این داده ها در سیستم های GIS تخصصی استفاده میشن.
XML indexes، داده هایی هستن که به صورت عادی در فرمت XML ذخیره شدن و این ایندکس میاد و اون رو به فرمت جدولی تبدیلش می کنه تا SQL server خیلی سریع تر، بتونه در اون داده های تبدیل شده به فرمت جدولی، جستجو کنه. دو زیر مجموعه برای xml index وجود داره: primaryو secondary. شما در ابتدا باید یک primary ایجاد کنین و بعد از اون شما میتونین یکی یا بیشتر از یکی، secondaryایجاد کنین و در نهایت ایندکس full-text رو داریم؛ full-text به ما اجازه میده که کوئری های زبان انگلیسی رو در database مون اجرا کنیم.
بنابراین ما میتونیم کلماتی رو که صداشون مثل کلمات دیگه هست رو جستجو کنیم. یا ما میتونیم کلماتی رو که مترادف با کلمات دیگه هستن رو جستجو کنیم.