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

1. ایندکس کلاستر(clustered index)
2. ایندکس نان کلاستر(nonclustered index)

 یک ایندکس از نوع کلاستر، جدول مورد نظر را ذخیره و سازمان دهی می کند. 

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

هر دوی این ایندکس ها اختیاری هستند، اما در افزایش کارایی کوئری ها نقش به سزایی دارند.  برای اینکه بفهمیم ایندکس ها چگونه کار می کنند، دو مثال می زنیم: 

فرض کنید که یک دفترچه ی تلفن داریم. این دفترچه را می توان یک ایندکس از نوع کلاستر در نظر گرفت. 

هر یک از سطرهای این دفترچه تلفن، را می توان به یک ردیف در یک جدول دیتابیس تشبیه کرد. هر جدول تنها می تواند یک کلاستر ایندکس، داشته باشد. 

دلیل این موضوع این است که یک ایندکس کلاستر، درواقع همان جدول اصلی است که بر اساس یک کلید خوشه بندی(کلاستر) سازمان دهی شده است. 

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

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

 کلید اصلی(primary key) این دفرچه تلفن، شماره تلفن های آن است. معمولا از کلید اصلی، به منظور کلید خوشه بندی(clustering key) استفاده می شود اما در مثال ما صحت ندارد. 

در یک دفترچه ی تلفن، کلید کلاستر(کلید خوشه بندی) یا cluster key درواقع ترکیبی از نام و نام خانوادگی است. 

اگر شما نام و نام خانوادگی شخصی را بدانید، آنگاه  در یک دفترچه ی تلفن چگونه شماره تلفن او را پیدا می کنید؟ راه حل بسیار ساده است؛ ابتدا به سراغ فهرست الفبایی این دفترچه می روید و حرف اول نام آن شخص را در دفترچه تلفن باز می کنید. و سپس به دنبال نام دوست خود می گردید. حالا باید به دنبال نام دوست خود بگردید، سپس شماره تلفن دوست شما در کنار نام او قرار دارد. این فرآیند کمی طولانی است بنابراین آن را کامل توضیح نمی دهیم. 

تعریف: به جستجوی یک شماره در دفترچه ی تلفن، با استفاده از نام و نام خانوادگی، ایندکس کلاستر(clustered index) گفته می شود. 

تعریف: به ایندکس(فهرست) که در اول یک کتاب قرار دارد، نمونه ای ایندکس نان کلاستر(nonclustered index) گفته می شود. 

یک ایندکس نان کلاستر(nonclustered index) حاوی فهرست ستون ها  به همراه یک اشاره گر است که به سطر حقیقی مورد نظر اشاره می کند. بعنوان مثال در یک دفترچه ی تلفن، فهرست اول این دفترچه، یک ایندکس نان کلاستر است که به شماره ی صفحات اشاره می کند. 

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

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

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

 با اینکه شما تنها می توانید یک ایندکس کلاستر  در هر جدول داشته باشید، اما شما می توانید بیش از 999 ایندکس نان-کلاستر، در یک جدول داشته باشید. نکته ی مهمی که باید به خاطر داشته باشید، این است که، با اینکه ایندکس ها می توانند کارایی کوئری ها را بهبود ببخشند، اما این ایندکس ها از فضای دیسک استفاده می کنند و برای نگهداری آنها باید منابع مورد نیازشان را تامین کنیم. 

در صورتی که یک جدول دارای چهار ایندکس نان کلاستر باشد، آنگاه هرگاه یک عمل write کردن در جدول انجام دهیم، نیاز داریم تا برای به روز نگهداشتن ایندکس ها، چهار عمل write دیگر نیز انجام دهیم. 

تعداد ایندکس های مجاز در یک جدول، با انتشار نسخه ی sql server 2008 افزایش پیدا کرد تا بتوانیم از دو ویژگی جدید زیر بهره بریم:

•  ستون های اسپارس(sparse columns)
• ایندکس های فیلتر شده(filtered indexes)

برای مشاهده فیلم های آموزشی مقدماتی تا پیشرفته پایگاه داده SQLServer کلیک کنید.

2- پیدا کردن ایندکس های بدون استفاده یا Unused Index

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

مثال: بعنوان مثال در جدول Users تعریف ایندکس روی فیلد Password یک کار بیهوده است، چون هرگز روی فیلد Password جستجو انجام نخواهد شد.

مثال عملی مربوط به ایندکس های بدون استفاده

USE AdventureWorks2012
GO
SELECT TOP 25
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
GO

در مثال بالا برای پیدا کردن ایندکس های بدون استفاده از یک DMV بنام dm_db_index_usage_stats استفاده شده است.

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

در ادامه بحث Missing Index خواهید دید...!

با خرید جلسه 6 از بسته آموزشی "افزایش کارایی پایگاه داده" مباحث بالا بصورت تکمیلی توضیح داده شده است.

دسترسی به موارد آموزشی بالا در بسته خریداری شده

• شماره جلسه: 6
• نام فایل ویدئو: 01
• فرمت فایل: mp4.

نقطه شروع بحث بالا (Unused Index) در ویدئو: 8:50

برای خرید و دانلود کاملآموزش پیشرفته SQL Server کلیک کنید.