zkazemi
دوشنبه 24 اسفند 1388, 15:15 عصر
منظور از INDEX ها یعنی آرایش رکورد ها در درون حافظه به صورت یک B-Tree یا همان بالانس Tree می باشد. از ایندکس ها جهت مرتب سازی و سرعت بخشیدن به عملیات جستجو استفاده می شود.شکل زیر یه درخت بالانس رو نشان میده . همانطور که ملاحظه می کنید در درخت بالانس تعادل وجود داره و نیمی از داده در سمت راست و نیمی دیگر در سمت چپ به طور متوازن قرار گرفته اند.
هرچقدر داده های موجود در یک جدول مرتب تر باشند به این ترتیب سرعت عملیات جستجو نیز افزایش پیدا میکنه.بنابراین طراح پایگاه داده باید بروی ستون هایی که قرار است در filtering شرکت داشته باشند ایندکس را به طور مناسب انتخاب کند. در واقع هر ایندکس از یک کلید و یک آدرس تشکیل شده است. درست مثل ایندکس موجود در انتهای کتاب ها . در واقع ایندکس ها جهت انجام دسترسی های مستقیم و تصادفی بوجود آمده اند. و خیلی قبل تر از sql در انواع سیستم های مختلف به کاربرده می شده اند.
ایندکس ها در sql به دو دسته Cluster و NonCluster تقسیم می شوند.هر جدول حداکثر می تواند یک ایندکس از نوع خوشه ای داشته باشد. اما به هر تعداد دلخواه می تواند شامل ایندکس غیر خوشه ای باشد. ایندکس خوشه ای به طور خودکار توسط خود sql رو ستون Primary key ساخته می شود.و غیر قابل تغییر است .برای مشاهده ایندکس ها می توانید به پوشه ی Indexes از جدول مورد نظر در ساختار object explorer مراجعه کنید. در این پوشه اسامی همه ایندکس های ایجاد شده بروی آن جدول بخصوص قرار دارد.
قبل از اینکه نحوه کار ایندکس ها در حالت خوشه ای و غیر خوشه ای را شرح دهم لازم می بینم که کمی در مورد واحد های ذخیره سازی در sql صحبت کنم. کوچکترین واحد ذخیره سازی در sql یک page یا صفحه است. هر page ظرفیتی معادل 8کیلوبایت را دارد.هر page از سه قسمت header ، data،Row Address تشکیل شده است. در header اطلاعاتی راجع به شماره صفحه ، نوع صفحه و ... نوشته شده است . در قسمت data سطرها قرار می گیرند.فضای page برای ذخیره کردن هر نوعی که از 8کلیلو بایت تجاوز نکند قابل استفاده است. پس برای ذخیره انواعی مانند text ها ویا داده های Binary یا image که حجیم هستند از ساختار های بزرگتری استفاده می شود.
هر سطر می تواند حداکثر 8 کلیو بایت باشد. این بدین معنی است که یک سطر نمی تواند در بیش از یک صفحه توزیع شود و باید به طور کامل داخل یک صفحه باشد.آدرس شروع هر سطر در داخل pageدر قسمت row address ذخیره می شود. در واقع توسط این آدرس سطر ها قابل یافتن هستند.
انواع صفحه های شامل 1- داده ای 2- ایندکسی 3-مربوط به داده های حجیم مثل text,binary,image,… می باشد.
اما باید به خاطر داشته باشیم که sql از واحد بزرگتری به نام extent برای ذخیره سازی رکوردها استفاده می کند.هر extent از 8 عدد page تشکیل شده است.یعنی ظرفیت هر extent معادل 64 کیلوبایت می باشد.در صورتی که جدول تنها از یک extent یا به عبارتی از 8 عدد page استفاده کند درواقع از یک extent به نام extent uniform استفاده می کند.
ایندکس ها در صفحاتی از نوع صفحات ایندکسی قرار می گیرند. هر index از سه ساختار 1- سطح ریشه 2- سطح واسط یا non leaf level و 3- leaf level تشکیل شده است.
در صورتی که در جدول خود هیچ گونه ستون primary key ای نداشته باشیم در این حالت تمام داده به ترتیب زمانی خود در جدول ثبت شده و در داخل page ها قرار میگیرند. بدیهی است که در این حالت واکشی یک رکورد دلخواه زمان زیادی را نیاز دارد.
اما در صورتی که ستون primary key داشته باشیم در این حالت یک ایندکس از نوع خوشه ای داریم . در ساختار این نوع ایندکس ابتدا سطح ریشه قرار گرفته است . از این ریشه جهت دسته بندی داده ها استفاده می شود.
سپس در سطح بعدی که سطح واسط نامیده می شود از هر رکورد به سمت صفحات سطح leaf level اشاره گر هایی وجود دارد . در ساختار ایندکس خوشه ای داده های واقعی در سطح leaf level قرار دارند. مطابق شکل
http://barnamenevis.org/forum/attachment.php?attachmentid=45669&stc=1&d=1072957807
هنگام واکشی یک رکورد با شماره مثلا 158 ابتدا از ریشه شروع به حرکت می کنیم . با توجه به این که در ریشه دسته های مختلفی قرار گرفته اند دسته ای را که شماره رکورد مور نظر ما در آن وجود دارد را انتخاب کرده و اشاره گر آن را دنبال می کنیم تا به صفحات موجود در سطح non leaf level برسیم. در آنجا شماره رکورد مورد نظر را یافته و اشاره گر آن را تا رسید صفحه موجود در سطح leaf level دنبال می کنیم. و در نهایت به داده های آن رکورد در آن صفحه می رسیم.
در ساختار ایندکس خوشه ای هنگام درج در صورتی که صفحه موردنظر که باید عمل درج درآن انجام پذیرد پر باشد آنگاه نحوه درج رکورد جدید به این صورت خواهد بود: ابتدا محل درج رکورد موردنظر در داخل آن page مشخص می شود. سپس تمام رکورد های زیر آن به صفحه ی جدید منتقل می شود. در نهایت رکورد مورد نظر در صفحه اولیه درج می گردد.
http://barnamenevis.org/forum/attachment.php?attachmentid=45671&stc=1&d=1072957807
این درخت در عمل درج به صورت بالانس است زیرا داده ها در صورت پر بودن صفحه به سمت پایین حرکت می کنند. ودرخت همچنان متعادل باقی می ماند. اما درعمل حذف بالانس نیست. یعنی با حذف یک رکورد داده ها به سمت بالا حرکت نمی کنند.
اما در ساختار ایندکس های غیر خوشه ای یک تفاوت مهم وجود دارد و آن این است که در سطح leaf level دیگر داده های واقعی و فیزیکی وجود ندارد بلکه اشاره گر هایی به سمت داده های واقعی وجود دارد. به عبارت دیگر می توان گفت در ایندکس غیر خوشه ای خود داده های جدول ذخیره نمی شود در عوض اشاره گر هایی به داده های جدول به عنوان بخشی از کلید های ایندکس ذخیره می شوند . از این رو ایندکس های غیر خوشه ای بسیاری را می توان بر روی کی جدول ایجاد نمود. مطابق شکل زیر
http://barnamenevis.org/forum/attachment.php?attachmentid=45668&stc=1&d=1072957807
زمانیکه ایندکسی را روی جدولی قرار می دهید نتیجه را به صورت صوری مشاهده نمی کنید اما اجرای کوئری ها می توان دیگر اسم آن ستون را در مقابل در دستور order by ذکر نکرد . چون مقادیر آن ستون با توجه به استفاده از یک ایندکس غیر خوشه ای مرتب می باشد.
مثال زیر نحوه تعریف یک ایندکس را نشان می دهد.
create nonclustered |clustered
index testindex on
dbo.Products ( ProductName asc,UnitPrice asc )
with ( drop_existing =on )
از ایندکس های می توان جهت منحصر به فرد کردن مقادیر یک ستون دلخواه نیز استفاده کرد. در این حالت باید از گزینه Unique پس از Create استفاده کنید.در مثال بالا testindex نام ایندکس و dbo.Products نام جدولی است که می خواهیم روی آن ایندکس ایجاد کنیم. Option نوشته شده درمقابل with بعنی drop_existing در صورتی که ایندکس قبلا وجود داشته باشد آن را حذف کرده و مجددا این ایندکس را می سازد.
همانطور که قبلا اشاره شد درخت بالانس در عمل حذف بالانس نیست . ودر سیستم های پویا یعنی سیستم هایی که عملیات درج و حذف درآنها زیاد است پس از مدتی امکان مشاهده پدیده تفرق در ایندکس ها بوجود می آید. به همین دلیل هر چند وقت یکبار باید ایندکس ها را مجددا سازماندهی نمود . جهت از بین بردن پدیده تفرق کافی است از دستور
DBCC DBREINDEX (Products,testindex ,100)
استفاده کنید. دراین دستور پارامتراول اسم جدول ، پارامتر دوم نام ایندکس که این قسمت اختیاری است و اگر نوشته نشود تمام ایندکس های آن جدول سازماندهی مجدد می شوند. و پارامتر سوم fill factor یا درصد پرشدگی صفحه را مشخص می کند که حداکثر می تواند 100 باشد. اگر این پارامتر روی 100 تنظیم شود یعنی از تمام ظرفیت صفحه جهت ذخیره استفاده شود.البته در صورت تمایل می توان این عدد را کمتر نیز وارد کرد که به این ترتیب قسمتی از انتهای صفحات خالی خواهد بود تا درصورت لزوم جهت درج رکورد های جدید مورد استفاده قرار بگیرد.
به بیان دیگر ، Fillfactor میزان پر بودن هر page را در سطح برگ ایندکس مشخص می کند . چنانچه مقدار Fillfactor در دستور Create Index مشخص نباشد مقدار پیش فرض آن صفر است یعنی کل Page پر شود. یعنی در این حالت صفحات پر هستند اما صفحات میانی و ریشه حداقل فضایی به اندازه 2 سطر اضافی را دارا هستند . اگر fillfactor مساوی 100 باشد کلیه صفحات برگ 100% پر هستند و فضای اضافی برای صفحات دیگر وجود ندارد در این حالت نیز صفحات میانی و ریشه فضای لازم برای 2 رکورد را دارند.اگر یک Cluster INDEX با FillFactor مساوی 50 ایجاد کنید هر Page تا 50% ظرفیت تکمیل می شود. بکارگیری گزینه Pad_Index به همراه Fill factor باعث می شود تاثیر این گزینه به سایر صفحات دیکر انتقال یابد . علت استفاده از این دو گزینه این است که اگر Page پر شود SQL Server بخوهد رکوردی را به آن اضافه کند Page را Split می کند. یعنی Page جدیدی را ایجاد و 50% از رکورد ها را به آن انتقال داده و رکورد جدید را در محل مناسب جا می دهد . وقوق مکرر این امر هزینه ساز است استفاده از FillFactor امکان اضافه کردن رکورد جدید را بدون Split کردن Page فراهم می کند.
Create NONClustered INDEX Idxgrad2
ON Register(grad)
With FillFactor =60 ,Pad_Index
نکته : برای مشاهده زمان اجرای دستورات با وجود ایندکس می توان از منوی Query گزینه Include Actual Execution Plan را اجرا کرد.
هرچقدر داده های موجود در یک جدول مرتب تر باشند به این ترتیب سرعت عملیات جستجو نیز افزایش پیدا میکنه.بنابراین طراح پایگاه داده باید بروی ستون هایی که قرار است در filtering شرکت داشته باشند ایندکس را به طور مناسب انتخاب کند. در واقع هر ایندکس از یک کلید و یک آدرس تشکیل شده است. درست مثل ایندکس موجود در انتهای کتاب ها . در واقع ایندکس ها جهت انجام دسترسی های مستقیم و تصادفی بوجود آمده اند. و خیلی قبل تر از sql در انواع سیستم های مختلف به کاربرده می شده اند.
ایندکس ها در sql به دو دسته Cluster و NonCluster تقسیم می شوند.هر جدول حداکثر می تواند یک ایندکس از نوع خوشه ای داشته باشد. اما به هر تعداد دلخواه می تواند شامل ایندکس غیر خوشه ای باشد. ایندکس خوشه ای به طور خودکار توسط خود sql رو ستون Primary key ساخته می شود.و غیر قابل تغییر است .برای مشاهده ایندکس ها می توانید به پوشه ی Indexes از جدول مورد نظر در ساختار object explorer مراجعه کنید. در این پوشه اسامی همه ایندکس های ایجاد شده بروی آن جدول بخصوص قرار دارد.
قبل از اینکه نحوه کار ایندکس ها در حالت خوشه ای و غیر خوشه ای را شرح دهم لازم می بینم که کمی در مورد واحد های ذخیره سازی در sql صحبت کنم. کوچکترین واحد ذخیره سازی در sql یک page یا صفحه است. هر page ظرفیتی معادل 8کیلوبایت را دارد.هر page از سه قسمت header ، data،Row Address تشکیل شده است. در header اطلاعاتی راجع به شماره صفحه ، نوع صفحه و ... نوشته شده است . در قسمت data سطرها قرار می گیرند.فضای page برای ذخیره کردن هر نوعی که از 8کلیلو بایت تجاوز نکند قابل استفاده است. پس برای ذخیره انواعی مانند text ها ویا داده های Binary یا image که حجیم هستند از ساختار های بزرگتری استفاده می شود.
هر سطر می تواند حداکثر 8 کلیو بایت باشد. این بدین معنی است که یک سطر نمی تواند در بیش از یک صفحه توزیع شود و باید به طور کامل داخل یک صفحه باشد.آدرس شروع هر سطر در داخل pageدر قسمت row address ذخیره می شود. در واقع توسط این آدرس سطر ها قابل یافتن هستند.
انواع صفحه های شامل 1- داده ای 2- ایندکسی 3-مربوط به داده های حجیم مثل text,binary,image,… می باشد.
اما باید به خاطر داشته باشیم که sql از واحد بزرگتری به نام extent برای ذخیره سازی رکوردها استفاده می کند.هر extent از 8 عدد page تشکیل شده است.یعنی ظرفیت هر extent معادل 64 کیلوبایت می باشد.در صورتی که جدول تنها از یک extent یا به عبارتی از 8 عدد page استفاده کند درواقع از یک extent به نام extent uniform استفاده می کند.
ایندکس ها در صفحاتی از نوع صفحات ایندکسی قرار می گیرند. هر index از سه ساختار 1- سطح ریشه 2- سطح واسط یا non leaf level و 3- leaf level تشکیل شده است.
در صورتی که در جدول خود هیچ گونه ستون primary key ای نداشته باشیم در این حالت تمام داده به ترتیب زمانی خود در جدول ثبت شده و در داخل page ها قرار میگیرند. بدیهی است که در این حالت واکشی یک رکورد دلخواه زمان زیادی را نیاز دارد.
اما در صورتی که ستون primary key داشته باشیم در این حالت یک ایندکس از نوع خوشه ای داریم . در ساختار این نوع ایندکس ابتدا سطح ریشه قرار گرفته است . از این ریشه جهت دسته بندی داده ها استفاده می شود.
سپس در سطح بعدی که سطح واسط نامیده می شود از هر رکورد به سمت صفحات سطح leaf level اشاره گر هایی وجود دارد . در ساختار ایندکس خوشه ای داده های واقعی در سطح leaf level قرار دارند. مطابق شکل
http://barnamenevis.org/forum/attachment.php?attachmentid=45669&stc=1&d=1072957807
هنگام واکشی یک رکورد با شماره مثلا 158 ابتدا از ریشه شروع به حرکت می کنیم . با توجه به این که در ریشه دسته های مختلفی قرار گرفته اند دسته ای را که شماره رکورد مور نظر ما در آن وجود دارد را انتخاب کرده و اشاره گر آن را دنبال می کنیم تا به صفحات موجود در سطح non leaf level برسیم. در آنجا شماره رکورد مورد نظر را یافته و اشاره گر آن را تا رسید صفحه موجود در سطح leaf level دنبال می کنیم. و در نهایت به داده های آن رکورد در آن صفحه می رسیم.
در ساختار ایندکس خوشه ای هنگام درج در صورتی که صفحه موردنظر که باید عمل درج درآن انجام پذیرد پر باشد آنگاه نحوه درج رکورد جدید به این صورت خواهد بود: ابتدا محل درج رکورد موردنظر در داخل آن page مشخص می شود. سپس تمام رکورد های زیر آن به صفحه ی جدید منتقل می شود. در نهایت رکورد مورد نظر در صفحه اولیه درج می گردد.
http://barnamenevis.org/forum/attachment.php?attachmentid=45671&stc=1&d=1072957807
این درخت در عمل درج به صورت بالانس است زیرا داده ها در صورت پر بودن صفحه به سمت پایین حرکت می کنند. ودرخت همچنان متعادل باقی می ماند. اما درعمل حذف بالانس نیست. یعنی با حذف یک رکورد داده ها به سمت بالا حرکت نمی کنند.
اما در ساختار ایندکس های غیر خوشه ای یک تفاوت مهم وجود دارد و آن این است که در سطح leaf level دیگر داده های واقعی و فیزیکی وجود ندارد بلکه اشاره گر هایی به سمت داده های واقعی وجود دارد. به عبارت دیگر می توان گفت در ایندکس غیر خوشه ای خود داده های جدول ذخیره نمی شود در عوض اشاره گر هایی به داده های جدول به عنوان بخشی از کلید های ایندکس ذخیره می شوند . از این رو ایندکس های غیر خوشه ای بسیاری را می توان بر روی کی جدول ایجاد نمود. مطابق شکل زیر
http://barnamenevis.org/forum/attachment.php?attachmentid=45668&stc=1&d=1072957807
زمانیکه ایندکسی را روی جدولی قرار می دهید نتیجه را به صورت صوری مشاهده نمی کنید اما اجرای کوئری ها می توان دیگر اسم آن ستون را در مقابل در دستور order by ذکر نکرد . چون مقادیر آن ستون با توجه به استفاده از یک ایندکس غیر خوشه ای مرتب می باشد.
مثال زیر نحوه تعریف یک ایندکس را نشان می دهد.
create nonclustered |clustered
index testindex on
dbo.Products ( ProductName asc,UnitPrice asc )
with ( drop_existing =on )
از ایندکس های می توان جهت منحصر به فرد کردن مقادیر یک ستون دلخواه نیز استفاده کرد. در این حالت باید از گزینه Unique پس از Create استفاده کنید.در مثال بالا testindex نام ایندکس و dbo.Products نام جدولی است که می خواهیم روی آن ایندکس ایجاد کنیم. Option نوشته شده درمقابل with بعنی drop_existing در صورتی که ایندکس قبلا وجود داشته باشد آن را حذف کرده و مجددا این ایندکس را می سازد.
همانطور که قبلا اشاره شد درخت بالانس در عمل حذف بالانس نیست . ودر سیستم های پویا یعنی سیستم هایی که عملیات درج و حذف درآنها زیاد است پس از مدتی امکان مشاهده پدیده تفرق در ایندکس ها بوجود می آید. به همین دلیل هر چند وقت یکبار باید ایندکس ها را مجددا سازماندهی نمود . جهت از بین بردن پدیده تفرق کافی است از دستور
DBCC DBREINDEX (Products,testindex ,100)
استفاده کنید. دراین دستور پارامتراول اسم جدول ، پارامتر دوم نام ایندکس که این قسمت اختیاری است و اگر نوشته نشود تمام ایندکس های آن جدول سازماندهی مجدد می شوند. و پارامتر سوم fill factor یا درصد پرشدگی صفحه را مشخص می کند که حداکثر می تواند 100 باشد. اگر این پارامتر روی 100 تنظیم شود یعنی از تمام ظرفیت صفحه جهت ذخیره استفاده شود.البته در صورت تمایل می توان این عدد را کمتر نیز وارد کرد که به این ترتیب قسمتی از انتهای صفحات خالی خواهد بود تا درصورت لزوم جهت درج رکورد های جدید مورد استفاده قرار بگیرد.
به بیان دیگر ، Fillfactor میزان پر بودن هر page را در سطح برگ ایندکس مشخص می کند . چنانچه مقدار Fillfactor در دستور Create Index مشخص نباشد مقدار پیش فرض آن صفر است یعنی کل Page پر شود. یعنی در این حالت صفحات پر هستند اما صفحات میانی و ریشه حداقل فضایی به اندازه 2 سطر اضافی را دارا هستند . اگر fillfactor مساوی 100 باشد کلیه صفحات برگ 100% پر هستند و فضای اضافی برای صفحات دیگر وجود ندارد در این حالت نیز صفحات میانی و ریشه فضای لازم برای 2 رکورد را دارند.اگر یک Cluster INDEX با FillFactor مساوی 50 ایجاد کنید هر Page تا 50% ظرفیت تکمیل می شود. بکارگیری گزینه Pad_Index به همراه Fill factor باعث می شود تاثیر این گزینه به سایر صفحات دیکر انتقال یابد . علت استفاده از این دو گزینه این است که اگر Page پر شود SQL Server بخوهد رکوردی را به آن اضافه کند Page را Split می کند. یعنی Page جدیدی را ایجاد و 50% از رکورد ها را به آن انتقال داده و رکورد جدید را در محل مناسب جا می دهد . وقوق مکرر این امر هزینه ساز است استفاده از FillFactor امکان اضافه کردن رکورد جدید را بدون Split کردن Page فراهم می کند.
Create NONClustered INDEX Idxgrad2
ON Register(grad)
With FillFactor =60 ,Pad_Index
نکته : برای مشاهده زمان اجرای دستورات با وجود ایندکس می توان از منوی Query گزینه Include Actual Execution Plan را اجرا کرد.