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

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

شاخص های سرور SQL را می توان به دو نوع اصلی تقسیم کرد:

1. شاخص های خوشه ای
2. شاخص های غیر خوشه ای

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

بیایید ابتدا با یک شاخص خوشه ای شروع کنیم.

شاخص خوشه دار

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

شاخص های خوشه ای پیش فرض

بیایید یک جدول ساختگی با ستون کلید اصلی ایجاد کنیم تا شاخص پیش فرض خوشه ای را ببینیم. فیلمنامه زیر را اجرا کنید:

بیمارستان پایگاه داده ایجاد کنید

ایجاد بیماران جدول (id int کلید اصلی ، نام varchar (50) نه تهی ، جنسیت varchar (50) نه تهی ، سن int null)

اسکریپت فوق یک بیمارستان پایگاه داده ساختگی ایجاد می کند. این پایگاه داده دارای 4 ستون است: شناسه ، نام ، جنسیت ، سن. ستون ID ستون اصلی کلید است. هنگامی که اسکریپت فوق اجرا می شود ، یک شاخص خوشه ای به طور خودکار در ستون ID ایجاد می شود. برای دیدن تمام فهرست ها در یک جدول ، می توانید از روش ذخیره شده "SP_HELPINDEX" استفاده کنید.

از بیماران SP_HELPINDEX از بیمارستان استفاده کنید

در اینجا خروجی است:

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

شاخص های خوشه ای سفارشی

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

استفاده از بیمارستان های تغییر یافته در جدول Drop Drop Pk__Patients__3213E83F3Dfafaad GO

اسکریپت زیر یک فهرست سفارشی "ix_tblpatient_age" در ستون سنی جدول بیماران ایجاد می کند. با توجه به این شاخص ، تمام سوابق موجود در جدول بیماران به ترتیب صعودی سن ذخیره می شود.

استفاده از بیمارستان ایجاد شاخص خوشه ای ix_tblpatient_age در بیماران (ASC ASC)

بیایید اکنون چند سوابق ساختگی را در جدول بیماران اضافه کنیم تا ببینیم آیا آنها واقعاً در ترتیب صعودی سن درج شده اند:

وارد بیماران شوید

ارزش ها (1 ، "سارا" ، "زن" ، 34) ، (2 ، "جان" ، "مرد" ، 20) ، (3 ، "مایک" ، "مرد" ، 54) ، (4 ، "آنا" ،"زن" ، 10) ، (5 ، "نیک" ، "زن" ، 29)

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

* از بیماران انتخاب کنید

در اینجا خروجی است:

می بینید که سوابق به ترتیب صعودی مقادیر در ستون سن سفارش داده می شوند.

شاخص های غیر خوشه ای

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

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

استفاده از بیمارستان ایجاد شاخص غیر خوشه ای ix_tblpatient_name در بیماران (نام ASC)

اسکریپت فوق شاخصی ایجاد می کند که شامل نام بیماران و آدرس سوابق مربوطه آنها همانطور که در زیر آمده است:

در اینجا ، "آدرس ضبط" در هر سطر ، اشاره به سوابق جدول واقعی برای بیماران با نام های مربوطه است.

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

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

چه زمانی از شاخص های خوشه ای یا غیر خوشه ای استفاده کنید

اکنون که تفاوت های بین یک خوشه ای و یک فهرست غیر خوشه ای را می دانید ، اجازه دهید سناریوهای مختلف را برای استفاده از هر یک از آنها مشاهده کنیم.

1. تعداد فهرست ها

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

2. عملیات را انتخاب کنید

اگر می خواهید فقط مقدار شاخصی را که برای ایجاد و فهرست بندی استفاده می شود ، انتخاب کنید ، شاخص های غیر خوشه ای سریعتر هستند. به عنوان مثال ، اگر شما یک فهرست در ستون "Name" ایجاد کرده اید و می خواهید فقط نام را انتخاب کنید ، شاخص های غیر خوشه ای به سرعت نام را برمی گردانند.

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

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

3. عملیات را وارد/به روز کنید

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

4. فضای دیسک

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

5. حکم نهایی

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

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