نمایه چیست؟

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

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

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

نکته : مواقعی وجود دارد که پویش جدول گزینه بهتری نسبت یک نمایه است . به عنوان مثال اگر SQL SERVER نیازمند پردازش میزان متعارفی از ردیف های موجود در جدول باشد که گاهی اوقات حدود 10 یا درصد بیشتر از داده ها تخمین زده می شود ، ممکن است پویش جدول را بهتر از استفاده از نمایه ارزیابی کنید . همه اینها برای این است که بگوییم پویش جدول همیشه چیز بدی نیست اما در جداول بزرگ ، پردازش طول خواهد کشید .

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

انواع مختلفی از نمایه ها وجود دارد که می توانید در یک جدول ایجاد کنید .یک نمایه می تواند روی یک ستون ایجاد شود . که نمایه ساده (simple Index) نامیده می شود و و یا روی بیش از یک ستون ساخته شود و نمایه ترکیبی (compond index) خوانده شود . شرایط ستون یا ستون هایی که انتخاب می کنید و داده هایی که در این ستون ها قرار خواهند گرفت ، تعیین کننده نوع نمایه مورد استفاده است .SQL SERVER سه نوع نمایه دارد که عبارتند از خوشه ای (clustered) ، غیر خوشه ای (Non clustered) و نمایه های اولیه و ثانویه XML .

تعیین آنچه یک نمایه خوب می سازد .

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

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

در بخش های زیر برخی از عواملی را که تعیین می کند که آیا یک نمایه خوب است یا خیر بررسی خواهیم نمود .

•  استفاده از ستون های با نگه داشت کم (low -Mintenance)
•  استفاده از کلید های اولیه و خارجی
• توانایی یافتن رکورد های خاص
•  استفاده از نمایه های پوششی
•  جستجوی برای محدوده ای از اطلاعات
•  مرتب نگه داشتن داده ها

استفاده از ستون هایی با نگه داشت کم (low –Mintenance)

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

در مثال کاربردی که مشغول ساخت آن هستیم ، نیاز داریم که هر ماه موجودی حساب مشتری را با هر سودی که بدست آمده ویا مبلغی که واریز شده بروزرسانی کنیم ، با این وجود یک کار شبانه داریم که هدف آن یافتن مشتریانی است که بین $10000 و $50000 موجودی دارند تا بانک بتواند نرخ بالاتری از سپرده را نزد بانک مرکزی در مورد آن مبالغ داشته باشد .

مبلغ حسابی بانکی یک مشتری مرتباً به روز رسانی می شود ولی یک نمایه در این نوع ستون می تواند باعث تسریع برنامه بررسی شبانه سپرده ها شود . پیش از ایجاد نمایه در این مثال ، لازم است تعیین کنیم که آیا کاهش جزیی کارایی در بروزرسانی موجودی در نتیجه بهبود کارایی برنامه بررسی سپرده ها قابل توجیه است یا خیر .

استفاده از کلید های اولیه وخارجی

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

SQL SERVER به صورت خودکار نمایه هایی را روی کلید های خارجی شما ایجاد نمی کند ، هر چند آنجا که هنگام اتصال به جدول والد ، مقادیر ستون کلید خارجی باید توسط SQL SERVER تعیین شوند ، تقریباً همیشه پیشنهاد می شود که یک نمایه روی ستون های کلید خارجی ایجادگردد.

توانایی یافتن رکود های خاص

کاندید های مطلوب برای نمایه ها ، ستون هایی هستند که به SQL SERVER امکان می دهند به سرعت ردیف های مناسب را تشخیص دهد . عبارت WHERE ستون های مشخصی از جدول را تعیین نموده و به این ترتیب تعداد ردیف های برگشتی از پرس و جو را محدود می کند . ستون های استفاده شده در بیشتر عبارت WHERE پرس و جو ها انتخاب های بسیار خوبی برای نمایه ها هستند . بنابراین به عنوان مثال اگر بخواهید سفارش یک مشتری را برای یک شماره سفارش خاص بیابید ، یک نمایه بر پایه customer id,order number عالی خواهد بود چون همه اطلاعات مورد نیاز جهت مکان یابی ردیف در خواستی در جدول ، در نمایه وجود خواهد داشت .

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

استفاده از نمایه های پوششی

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

به عنوان مثال جدول share Detail.shares را در پایگاه داده Apress Financial در نظر بگیرید . فرض کنید می خواهید توضیحات ، بهای فعلی و شناسه آن سهام را بیابید . اگر یک نمایه روی ستون shareld وجود داشت .

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

SQL SERVER قادر بود جزییات بها وتوضیحات را با عمل نمایه بازیابی کند . در این حالت نیازی به رفتن به محدوده داده ها نبود این نمایه پوششی (overed Index) نامیده می شود . چون نمایه همه ستون های موجود در جدول را برای باز یابی داده ها پوشش می دهد .

جستجو برای محدوده ای از اطلاعات

یک نمایه به همان اندازه که می تواند برای یافتن رکورد مفید باشد . برای جستجوی مجموعه ای از رکورد ها هم مفید است . به عنوان مثال فرض کنید می خواهید لیستی از شهر های ایالت فلورایدا را که نامشان بین peteresburg,orlando است به ترتیب به حروف الفبا بیابید . می توانید نمایه را روی نام شهر قرار دهید و SQL SERVER به مکان نمایه orlando رفته از آنجا هر بار یک ردیف نمایه را به ترتیب بخواند تا زمانی که به گزینه بعد از st.petersburg رسیده و متوقف شود. چون SQL SERVER می داند که یک نمایه روی این ستون تعریف شده و داده ها بر حسب نام شهر ذخیره شده اند لذا ساختن نمایه روی ستون نام شهر کاملاً مطلوب است .

باید توجه داشت نمایه های SQL SERVER هنگامی که سعی دارید به دنبال کارکتر های بدون بدنه یک متن بگردید ، مفید نیستند . به عنوان مثال فرض کنید که می خواهید همه نویسندگان موجود در پایگاه داده یک ناشر را که نام خانوادگی آن ها شامل حروف «ab» باشد بیابید . این نوع پرس و جو ابزار مناسبی برای تعیین محل و اتمام جستجو جهت دستیابی به مقادیر مناسب نیست . تنها روشی که SQL SERVER می تواند تعیین کند که کدام ردیف ها برای این پرس و جو معتبر هستند . بررسی همه ردیف های موجود در جدول است . بسته به میزان داده های موجود در جدول این پردازش می تواند کند باشد . اگر نیاز به جستجوی متنی با استفاده ازwild card ها دارید ، باید به قابلیت جستجوی تمام متن SQL SERVER نگاهی بیندازید ،چرا که برای چنین پرس و جو هایی کارایی بهتری برای ارایه می کند .

مرتب نگه داشتن داده ها

یک نمایه خوشه ای در حقیقت داده های موجود در یک جدول را به ترتیبی خاص نگهداری می کند . هنگامی که یک یا چند ستون را به عنوان یک نمایه خوشه ای تعریف کنید ، با درج یک رکورد SQL SERVER آن رکورد را در مکان فیزیکی قرار خواهد داد تا رکورد در ترتیب صعودی یا نزولی صحیحی که مطابق با ترتیب تعریف شده در نمایه باشد قرار دهد . برای توضیح بیشتر ، اگر شما یک نمایه خوشه ای روی شماره های مشتری داشته باشید و داده ها در حال حاضر دارای شماره های مشتری : 5و2و7و4و6و10باشند، آن گاه SQL SERVER به صورت فیزیکی داده ها را به ترتیب :2و4و5و7و10 ذخیره می نماید .

اگر پس از آن پردازشی با شماره مشتری 9افزوده شود ، به صورت فیزیکی بین 7و10 قرار خواهد گرفت و بدان معنی است که رکورد با شماره مشتری 10 باید به صورت فیزیکی جابه جا شود . بنابراین اگر یک نمایه خوشه ای روی یک ستون ویا مجموعه ای از ستون ها تعریف کرده باشید که درج داده ها باعث از نو مرتب شدن نمایه گردد، این مسأله دقیقاً بر کارایی درج تأثیر خواهد گذاشت . SQL SERVER روشی را برای کاهش تأثیر مرتب کردن مجدد ارایه می کند و آن امکان دادن به یک عامل پر کننده (Fill Factor) هنگام ایجاد نمایه است . این گزینه به شما امکان می دهد تعیین کنید چه مقداراز یک برگ نمایه بیش از ایجاد یک برگ جدید ، برخواهد شد ، یک برگ نمایه را به صورت کارت نمایه در کمد بایگانی در نظر بگیرید .

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