www.dntips.ir
مطالب ۳۶۹۴
نویسندگان ۲۷۶
گروه‌های مطالب ۱۰۲۴
نقشه‌های راه ۱۱۹
دوره‌ها ۱۴
اشتراک‌ها ۱۷۹۱۴
عنوان:

SQL Indexing


نویسنده: امیر هاشم زاده
تاریخ: ۱۳۹۴/۱۲/۱۰ ۰:۵۰
آدرس: www.dntips.ir

دلیل استفاده از ایندکس چیست؟

این سوالی است که ممکن است هر توسعه دهنده‌ای به آن در ابتدا پاسخ دهد: «جهت بالابردن سرعت و کارآیی!» حال اگر بپرسیم چگونه؟ توضیحات چندان دقیقی ارائه نمی‌شود.

ایندکس چیست؟

ایندکس شیءای از دیتابیس است می‌تواند برروی یک یا چند ستون ایجاد شود (تا 16 ستون). هنگامیکه ایندکسی ایجاد می‌گردد، ساختار داده‌ای (BTree) جهت بهینه سازی عملیات مقایسه نیز ایجاد می‌شود. اس کیو ال سرور بدون داشتن ایندکس، برای دریافت اطلاعات درخواستی مجبور است کل ردیف‌های جدول را جستجو نماید. این کار مانند این است که شما بدون اطلاع از شماره صفحه (محل) عنوان درخواستی، به دنبال آن در صفحات یک کتاب باشید. حال اگر به ایندکس (فهرست) کتاب مراجعه کنید به سرعت و حداقل اتلاف وقت می‌توانید محل یا شماره صفحه‌ی عنوان مورد نظر را، بدون جستجوی کلیه‌ی صفحات کتاب، پیدا کنید و به آن مراجعه کنید. ایندکس جدول نیز اجازه می‌دهد بدون جستجوی کلیه رکوردها، رکورد مورد نظر را دریافت نمایید.
مثال:
SELECT [computer_id],[nic_device_id],[nic_vendor_id],[nic_desc]
FROM [eXpress].[dbo].[nics]

فرض کنید در جدول بالا ایندکس گذاری انجام نشده باشد و قصد داشته باشید رکوردهایی را دریافت نمایید که در آن‌ها computer_id>5100 باشد. اس کیو ال سرور مجبور است کلیه رکوردهای جدول را جهت اعمال شرط بررسی نماید.

حال، برروی ستون computer_id ایندکسی را اعمال می‌نماییم و شرط computer_id>5100 را مجدد بررسی می‌کنیم. اس کیو ال از محل رکوردهای با مقادیر بزرگتر از 5100 اطلاع دارد و از جستجوی کل جدول اجتناب می‌کند. چرا؟ بدلیل اینکه براساس این ستون مرتب شده است.

انواع ایندکس

دو نوع ایندکس اصلی وجود دارد: ایندکس خوشه‌ای و ایندکس غیرخوشه‌ای

ایندکس خوشه‌ای

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

بوسیله ایندکس زیر تمام رکوردها براساس ستون computer_id مرتب شده ذخیره می‌گردند.
CREATE CLUSTERED INDEX [IX_CLUSTERED_COMPUTER_ID] 
ON [dbo].[nics] ([computer_id] ASC)

همانطور که اشاره شد، رکوردها بصورت مرتب شده براساس ستون انتخاب شده‌ی در جدول نگهداری می‌شوند. اما این مرتب سازی توسط ساختار BTree به‌شرح زیر انجام خواهد شد. جدول زیر را در نظر داشته باشید:

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

و سپس کوئری‌های زیر را صادر می‌کنید: 

Select * from student where studid = 103;
Select * from student where studid = 107;
بدون ایندکس گذاری، کوئری اول، بعد از 3 عمل مقایسه و کوئری دوم بعد از 8 عمل مقایسه پیدا می‌شود.
با ایندکس گذاری، کوئری اول، بعد از اولین عمل مقایسه و کوئری دوم بعد از 3 عمل مقایسه پیدا می‌شود؛ به‌شرح زیر:
  1. مقایسه 107 با 103 و انتقال به گره سمت راست
  2. مقایسه 107 با 106 و انتقال به گره سمت راست
  3. مقایسه 107 با 107 و یافتن مقدار درخواستی و بازگشت رکورد

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

ایندکس غیرخوشه‌ای

این نوع ایندکس، تغییری در نحوه‌ی ذخیره سازی رکوردها انجام نمی‌دهند. ولی شیء دیگری را که شامل ستون/هایی که قرار است ایندکس شوند و اشاره‌گر به رکورد (RID) هستند، در جدول ایجاد می‌کند. برای مثالی از ایندکس غیرخوشه‌ای در دنیای واقعی، می‌توان به فهرست انتهای کتاب‌ها که شامل عناوین و شماره صفحه‌ی مربوطه می‌باشد، اشاره کرد.

نکته: RID به موقعیت فیزیکی رکورد اشاره خواهد کرد و شامل شناسه، شماره صفحه و تعداد رکوردهای در یک صفحه می‌باشد.

برای درک بهتر به سناریوی زیر دقت کنید:

کتابی داریم که شامل 1200 صفحه می‌باشد و فهرست مطالب آن شامل عناوین و شماره صفحات عناوین می‌باشد. حال اگر عنوان درخواستی A در صفحات 700، 300، 800 قرار داشته باشد، برای رفتن به این صفحات، مراحل زیر را برای هر یک طی خواهید کرد:

  1. یافتن شماره صفحه عنوان درخواستی با مراجعه به فهرست انتهای کتاب.
  2. در ادامه شما صفحه‌ای را در میانه‌ی کتاب، باز می‌کنید؛ چون عدد 700 مقداری از نصف 1200 برزگتر است.
  3. چند صفحه به جلو رفته، شماره صفحه 750 خواهد بود و هنوز به شرط مورد نظر نرسیده‌اید.
  4. پس مجددا چند صفحه به عقب بازگشته تا به صفحه‌ی مورد نظر، 700، برسید.

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

این نوع ایندکس‌ها جهت ستون هایی مفید هستند که مقادیر آن تکرار خواهد شد؛ مانند جدولی با بیش از چند میلیون رکورد که دارای ستون نوع حساب است، ولی تعداد نوع حساب منحصر بفرد محدودی را خواهد داشت. فرض کنید مقادیر منحصر بفرد، ستون نوع حساب A، B، C باشد. زمانیکه برروی این ستون ایندکس گذاری غیرخوشه‌ای انجام می‌شود، فهرست ما دارای سه عنوان خواهد بود که هر عنوان به صفحات مربوط به همان عنوان اشاره خواهد کرد. به این ترتیب هنگامیکه برروی نوع حساب عملیات جستجو انجام شود، اس کیو ال می‌داند رکوردهای نوع حساب مثلا A در کدام صفحات قرار دارد و به‌سرعت رکوردهای متناظر را پیدا می‌نماید.

A: 300, 700, 800
B: 100, 110
C: 600, 1200

ایندکس غیرخوشه ای توسط دستور زیر ایجاد می‌گردد:

CREATE NONCLUSTERED INDEX [IX_NONCLUSTERED_COMPUTER_ID] 
ON [dbo].[nics] ([computer_id] ASC)

نکته: یک جدول می‌تواند بیش از یک ایندکس غیرخوشهای و فقط و فقط یک ایندکس خوشهای داشته باشد.

ارتباط ایندکس خوشه‌ای و غیر خوشه‌ای

اشاره‌گر به رکورد (RID) در یک جدول دارای ایندکس خوشه‌ای، کلید ایندکس خوشه‌ای خواهد بود.

مزایا و معایب ایندکس

مزایا:
جدولی بدون ایندکس خوشه‌ای، heap table شناخته می‌شود. یک جدول هیپ، داده‌ی مرتب شده نخواهد داشت و به منظور دریافت اطلاعات، اس کیو ال سرور مجبور است کل ردیف‌های جدول را بررسی نماید که این عملیات Scan نامیده می‌شود. ولی در صورت استفاده از ایندکس خوشه‌ای برروی یک ستون، اس کیو ال، جهت یافتن اطلاعات مورد جستجو با توجه به BTree عملیات جستجو را از ریشه شروع، از شاخه‌ها عبور کرده و به برگ که همان اطلاعات درخواستی است می‌رسد که این عملیات Seek نامیده می‌شود. عملیات Seek طبیعتا از Scan سریعتر است.
ایندکس غیرخوشه‌ای، شامل مجموعه‌ای از ستون‌ها و ارجاعاتی به رکوردها یا کلید ایندکس خوشه‌ای است (ارتباط بین ایندکس غیر خوشه‌ای با خوشه‌ای). به‌دلیل حجم کم این نوع ایندکس، می‌تواند ردیف‌ها یا کلیدهای ایندکس خوشه ای بیشتری در صفحه‌ی ایندکس وجود داشته باشد که باعث افزایش کارآیی I/O می‌گردد.

معایب:
ایندکس گذاری، در طی عملیات درج، ویرایش و حذف، باعث سربار می‌گردد. هنگامیکه تغییری بر روی رکوردهای جدول انجام می‌شود، سبب تغییراتی نیز بر روی ایندکس‌ها می‌گردد (هنگامیکه برگه‌ای از کتابی جدا شود، نیاز است شماره صفحات و فهرست انتهایی کتاب مجددا به‌روز گردد) که این تغییرات باعث ایجاد هزینه می‌شود. بنابراین خیلی اهمیت دارد که هنگام طراحی ایندکس گذاری به سربارها نیز توجه کنید. به‌عنوان مثال هنگامیکه توسط دستور Delete رکوردی را از جدولی حذف نمایید، نیاز است رکوردها مجددا مرتب شوند که این یک سربار است.
ایندکس گذاری ، سرباری بنام bookmark lookup دارد. bookmark lookup فرآیندی جهت یافتن سایر ستون‌هایی است که در ایندکس گذاری وجود ندارند و براساس RID هستند.
مطالب مشابه