clustered or non-clustered index

سلام. يه سوال برام پيش اومده در مورد ايندكس هاي clustered و non-clustered :
توي كتابي كه من دارم مي خونم نوشته كه توي جدول clustered توي سطح گره هاي برگ (leaf level) داده هاي واقعي قرار دارند نه اشاره گر به داده ها! اين يعني اينكه داده ها به صورت مرتب (بر اساس ستوني كه ايندكس روي اون تعريف شده) ذخيره شدند. در حالي كه در non-clustered در leaf levelداده ها قرار نمي گيرند و اشاره گر هايي به فرم RID در اين سطح نگهداري مي شوند و توضيح داده كه سرعت اجراي پرس وجو در clustered بيشتر از non-clustered است. كه خب طبيعي هم هست. اما سوال من اينه كه : با اين كه سرعت جستجو در clustered بالاتر از non-clustered اما insert كردن در clustered دردسر بيشتري داره. يعني يه سري شيفت بايد انجام بشه روي داده ها كه به صورت فيزيكي مرتب بمونن. حالا سوال اينجاست كه آيا اين موضوع نگران كننده نيست؟ البته نا گفته نمونه كه وضع non-clustered خيلي بدتر از clustered به نظر مي رسه . وقتي مي خواد يه سري ركورد پشت سر هم رو بخونه ممكنه ركورد ها توي چند صفحه مختلف قرار بگيرند و براي مرتب خوندن اونها بايد چند تا I/O انجام بشه! در حالي كه توي clustered وقتي يك صفحه رو مي خونيم بقيه ركوردها هم توي همون صفحه قرار دارند. يه جايي خوندم كه نوشته بود توي ايران همه از non-clustered استفاده مي كنن و فقط چند تا از شركت هاي بزرگ با clustered كار مي كنن. كسي مي دونه علتش چيه؟

سلام
ببخشید که دیر جواب میدم ...
برای ذخیره اطلاعات توی clustered چون از B-Tree استفاده می شه موقع insert کردن فقط اطلاعات همون Page شیفت داده میشن که بسته به سایز اون پیج می تونه وقت گیر باشه اما نه اون قدر زیاد که شما گفتین
اما این که از چه جور ایندکسی باید استفاده کرد کاملا بستگی به شرایط داره
اگر جدول شما با جدول دیگه ای join میشه و تعداد رکورد هاش بالاس خیلی خوبه که روی ستون Key ایندکس Clustered تعریف کنین مثلا جدولی که کد کشور و نام کشور رو نگهداری می کنه و Update نداره اما خیلی زیاد Join میشه
بعضی اوقات از جدول ها برای نگهداری log استفاده می کنیم و چون Log ها تاریخ دارن و به ترتیب تاریخ وارد System میشن یه Clustered Index روی فیلد تاریخ چیز خوبی به نظر می رسه چون هم سرعت Insert بالایی داره (چون به ترتیب تاریخ داده ها وارد میشن) و هم موقع بازیابی معمولا نیاز به جستجو روی تاریخ داریم . البته ایندکس روی فیلد تاریخ نیاز به بررسی دقیق تر داره
اما گاهی اوقات یک جدول تعداد Update های اون بیشتر از بازیابی اون هست و سرعت update کردن برای ما خیلی مهم تره
تو این شرایط Clustered index حتما باید روی فیلد PK تعریف بشه وPK هم به صورت خودکار افزایشی مقدار بگیره و تا پایان عمر رکورد تغییری نکنه اما توی همین جدول اگر نیاز به سرچ برای رکوردی با مقدار خاص برای فیلد String هستیم یه Non-Clustered سرعت Update رو خیلی کم نمی کنه ولی سرچ رو از درجه n به درجه Log n کاهش میده که توی تعداد رکورد بالا خیلی خیلی خیلی مهمه
اما .....
چیزی که مهمه این ها نیست
Non-Clustered Index چیزی جدا از جدول هست و جای دیگه ای جدا از خود جدول ذخیره میشه و فقط اشاره گر هایی به جدول داره. این باعث میشه قابلیت هایی داشته باشه مثل Partitioning یا Filtered index ها که توی دیتا بیس های بزرگ خیلی مهم هستن .
از نظر سرعت هم هر دو مجانب log n هستند و تفاوت ها فقط توی موارد خیلی خاص خودش رو نشون میده.
در ضمن روی هر جدول فقط یک Clustered Index می تونیم داشته باشیم در حالی که حتی روی View ها می تونیم Non-Clustered Index داشته باشیم
از Clustered Index میشه گاهی اوقات به عنوان Sort پیش فرض برای داده ها استفاده کرد.
ولی معمولا انتخاب PK به عنوان Clustered Index و فیلد های مورد جست و جوی دیگه به عنوان Non-Clustered Index کار پسندیده ای هست که در اکثر موارد خوب جواب میده

به صورت کلی یکی از مهم ترین مباحث در Optimize کردن دیتابیس ایندکس ها هستن و میشه خیلی در موردشون صحبت کرد

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

سلام.
ممنون از اينكه جواب داديد. توضيحاتتون واقعا خوب بود.
يه سوال برام پيش اومده :شما نوشتيد كه سرعت هر دو log n هست. منظورتون Clustered Index و Non-Clustered Index است؟ مگه سرعت سرچ در Clustered Index از Non-Clustered Index بيشتر نيست؟ و همون طور كه توضيح داده بودم : " البته نا گفته نمونه كه وضع non-clustered خيلي بدتر از clustered به نظر مي رسه . وقتي مي خواد يه سري ركورد پشت سر هم رو بخونه ممكنه ركورد ها توي چند صفحه مختلف قرار بگيرند و براي مرتب خوندن اونها بايد چند تا I/O انجام بشه! در حالي كه توي clustered وقتي يك صفحه رو مي خونيم بقيه ركوردها هم توي همون صفحه قرار دارند." ميشه بگيد چرا گفتيد سرعتشون برابره؟

هر دو مجانب Log n هستند یعنی ممکنه برای پیدا کردن یک رکورد از بین 10000 رکورد برای یکی 10log طول بکشه که اینجا میشه 40 و برای یکی دیگه 100log n که میشه 400 و در حالتی که ایندکس نداریم 10n طول بکشه که میشه 100000
این دو عدد (40و400) در مقایسه با حالتی که ایندکس نداریم (100000) به هم نزدیکن در حالی که ممکنه یکی ده برابر کند تر از اون یکی باشه !

خيلي ممنون از توضيحاتتون. حرفتون كاملا درسته به هر حال داشتن ايندكس به هر صورتي بهتر از نداشتن است .(وقتي تعداد داده ها بالاست)
شرمنده ، يه اشتباهي شده اينكه گفتم يه جايي توي اينترنت نوشته بود توي ايران از cluster استفاده نميشه. من اشتباه متوجه شده بودم . وقتي بيشتر سرچ كردم ، متوجه شدم منظورشون از clustered index ، cluster نبوده ، بلكه failover cluster بوده . كه در واقع ربطي به اين بحث نداشته. و مربوط به sql server availability ميشه ، كه نوشته بودن به خاطر نياز به سخت افزارهاي گرون قيمت ازش استفاده نميشه.

Fail Over Cluster راه اندازیش زیاد مشکل نیست سخت افزار خاصی هم نمی خواد فقط به جای یک سرور حداقل 2 تا لازمه ولی چون تو ایران به Availability کلا اهمیت نمیدن ازش کم استفاده می شه