چند سوال مهم درباره ایندکس ها

[veniz2008]

سلام.

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

جواب تعدادی از این سوالات شاید ساده به نظر بیاد ولی بهرحال واسه من مبهم هستن.

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


اما سوالات :

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

2. در نرم افزارهایی که بصورت شبکه می سازیم و دیتابیس رو بر روی سیسیتم سرور قرار میدهیم،چون ایندکس ها نیاز به پردازش و جابه جایی رکوردها یا بروزرسانی مداوم آمار خود هستند. مشخصات سخت افزاری سرور باید حداقل به چه صورتی باشه؟ (حدودا برای 10 کلاینت فرض کنید و چند حالت مختلف رو از نظر فشار وارده بر سرور از طرف کلاینت ها در نظر بگیرید).

3. در تعیین نوع ایندکس (دسته ای و غیردسته ای)، آیا اولویتی وجود داره یا طبق شرایط نوع فیلدها نوع ایندکس هم تعیین میشه؟. آیا از لحاظ سرعت، بین این دو نوع ایندکس تفاوتی وجود داره؟

4. جدولی رو با تعداد رکورد بالای 1 میلیون در نظر بگیرید که شامل شماره شناسنامه، نام و نام خانوادگی باشه که داده ها بصورت نامرتب درون جدول ذخیره شدن. اگر در برنامه جستجو براساس هم "شماره شناسنامه" انجام بشه و هم "نام و نام خانوادگی". بهترین نوع ایندکس گذاری برای چنین جدولی چی هست؟ (دسته ای یا غیر دسته ای؟)

5. اگر ایندکسی رو بصورت unique تعریف کنیم، آیا اجازه دریافت مقدار null رو داره یا نه؟. اگر داره امکان دریافت دو یا چند ستون null وجود داره یا نه؟

6. استفاده از covering index در چه مواردی کاربرد داره؟. با توجه به اینکه در این حالت، ایندکس بر روی تمامی ستون ها تعریف میشه و در کتاب تاکید شده که ایندکس بر روی بیشتر از 4 ستون خیلی کار درستی نیست.

با توجه به این موضوع، آیا تضادی بوجود نمیاد؟ (بین covering index که روی همه ستون ها تعریف میشه و قرار نگرفتن ایندکس بر روی بیش از 4 ستون)

7. اگر ایندکس بر روی نام خانوادگی (یا کلا هر فیلد فارسی) قرار داشته باشه، آیا در فامیل هایی که با "ی" و "ک" شروع میشن، امکان اشتباه در مرتب سازی وجود داره؟.(همون مشکل همیشگی بین حروف فارسی و عربی)

8. اگر بر روی یک ستون عددی که مقادیر بصورت نامنظم وارد میشوند یک ایندکس دسته ای (clustered index) قرار بگیره. در هنگام درج داده جدید، واسه اینکه رکورد جدید در جای مناسب خودش قرار بگیره، احتمال زیاد نیاز هست که تعداد رکوردهای زیادی شیفت داده بشن تا رکورد جدید در جای مناسب خودش قرار بگیره که این امر باعث کاهش سرعت اجرای درج داده ها میشه. در کتاب گفته شده که برای کاهش تاثیر این کار از sql 2005 به بعد اجازه تعیین عاملی پر کننده به هنگام ایجاد ایندکس وجود داره. من این موضوع رو اصلا درک نکردم.این حرف یعنی چی و کجای تعریف ایندکس باید چنین موردی رو لحاظ کرد؟.

9. ایندکسی رو در نظر بگیرید که بر روی سه ستون شماره شناسنامه، نام و نام خانوادگی ایجاد شده باشه. اگر جستجوی ما فقط براساس شماره شناسنامه یا فقط براساس نام خانوادگی باشه، در چنین حالتی باز هم sql از ایندکس کمک میگیره؟ یا فقط زمانی از ایندکس استفاده میشه که در شرط جستجو، هر سه فیلد قید شده باشند؟

10. با توجه به اینکه ایندکس همیشه مطلوب نیست و بعضی وقت ها نتیجه عکس میده، آیا راهی (یا ابزاری) وجود داره که بتونیم تست کنیم که آیا وجود ایندکس برای اون جدول بهتره یا نداشتن ایندکس؟

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

13. برای بروزرسانی آمار ایندکس از گزینه Statistics Recompute (یا Statistics NoRecompute ) استفاده میشه. در چه مواردی باید این گزینه رو بر روی on و درچه مواردی بر روی off تنظیم کرد؟

14. برای فشردن ایندکس و از بین بردن فضای ایجاد شده در دیسک، گفته شده با استفاده از دستور Drop Existing ، ایندکس رو مجددا ایجاد کنیم. چطور بفهمیم که زمان مناسب برای این کار فرا رسیده؟

15. برای کاهش تکه تکه شدگی ایندکس ( Fragmentation) آیا بهتر است از قسمت Fragmentatin و انتخاب گزینه Reorganiz Index استفاده بشه یا با دستور Drop Existing ، ایندکس رو از نو بسازیم؟. و اینکه چطور بفهمیم که میزان تکه تکه شدگی به حدی رسیده که بر روی کار ایندکس تاثیر منفی گذاشته؟

[tooraj_azizi_1035]

Hi,

1- در قسمت WHERE نام فیلدی که ظاهر میشه SQL Server رو مجبور به جستجو بر اساس اون فیلد میکنه. ستون مورد نظر باید اصطلاحاً Selectivity بالا داشته باشه یعنی مقادیر یکتا در اون زیاد باشند مثل کد ملی که کلاً با بقیه مقادیر فرق می کنه اما اگه جنسیت رو اندیس گذاری کنید بی معنی هست چون از 100 رکورد SQL Server نهایتاً به طور میانگین می تونه 50 رکورد رو کنار بگذاره چون دو مقدار TRUE و FALSE بیشتر نداریم.

2- در مورد SQL Server تنظیماتی به نام max server memory داریم و thread affinity که دست SQL Server رو در پردازش باز میگذاره. یعنی RAM و CPU قدرتمند تر با Performance رابطه مستقیم د اره.

3- شما باید بدونید چه زمانی از کدام یک باید استفاده کنید. هر کدام باید شرایط لازم برای انتخاب رو داشته باشند.


5- بله، نامحدود.

6- در مواردی که نمی خواهیم SQL Server برای یافتن مقادیر داده ای از صفحات اندیس به صفحات داده ای برود. یعنی مانع از انجام عمل Bookmark Lookup که نسبتاً پرهزینه است شویم. نباید خیلی عریض باشه چون در صفحات اندیس باید یعی کنیم تعداد رکورد بیشتری رو جای بدیم چون با عریض شدن اندیس کارایی خودش رو از دست میده.


8- معمولاً روی ستونی که دستخوش درج و بروزرسانی زیاد میشه نباید اندیس گذاشت در ضمن باید Fill Factor و Rebuild کردن اندیس رو هم در نظر گرفت.


10- یکی تست از طریق STATISTICS IO هست که تعداد خواندن های فیزیکی و منطقی رو میده و میشه با وجود یا عدم وجود آمار رو مقایسه کرد و دیگری Missing Index هست که خود SQL وقتی در قسمت WHERE ستون رو میاریم مثلاً Telephone (تست کردم) خودش با رنگ سبز در Execution Plan پیام Missing Index رو میده.


11- زمانی که به دلیل درج و حذف زیاد روی ستون اندیس پراکندگی یا Fragmentation زیاد بشه که البته اون رو هم میشه با Rebuild برطرف کرد.

12- الزاماً نه ولی اگه Unique بشه خاصیت High Selectivity برای او تامین میشه. High Selectivity بالا یعنی با هر بار مقایسه تعداد زیادی از رکوردها کنار گذاشته می شوند.


13-Query Optimizer در SQL Server به کمک Statistics بهترین پلن اجرایی رو تولید میکنه به روز نبودن این آمار یعنی انتخاب پلن اجرای بد.

در ترجمه کتاب SQL Server Performance Tuning Distilled کامل شرح دادم. به زودی قرار میدم.

[veniz2008]

Hi,
سلام به روی ماهت.
خوشحالم که سکوت آزار دهنده این تاپیک رو شکستی.


1- در قسمت WHERE نام فیلدی که ظاهر میشه SQL Server رو مجبور به جستجو بر اساس اون فیلد میکنه. ستون مورد نظر باید اصطلاحاً Selectivity بالا داشته باشه یعنی مقادیر یکتا در اون زیاد باشند مثل کد ملی که کلاً با بقیه مقادیر فرق می کنه اما اگه جنسیت رو اندیس گذاری کنید بی معنی هست چون از 100 رکورد SQL Server نهایتاً به طور میانگین می تونه 50 رکورد رو کنار بگذاره چون دو مقدار TRUE و FALSE بیشتر نداریم.منظور من این نبود. منظورم اینه که معمولا برای چه تعداد رکورد بهتره که از ایندکس استفاده بشه؟. ایا جدولی که میدونیم شاید به 5000 هزار رکورد هم نرسه نیاز به ایندکس داره؟


3- شما باید بدونید چه زمانی از کدام یک باید استفاده کنید. هر کدام باید شرایط لازم برای انتخاب رو داشته باشند.بذارید یه مثال بزنم. جدولی با بیش از یک میلیون رکورد که ایندکس بر روی نام خانوادگی وجود داره رو در نظر بگیرید(دلیل گذاشتن ایندکس روی چنین ستونی با وجود یکتا نبودن مقادیر بخاطر جستجوهای زیادی در برنامه است که روی این فیلد تمرکز داره). حالا اگر روی این ستون ایندکس دسته ای بذارم با هر بار درج کردن یک رکورد جدید احتمال زیاد باید تعداد رکورد زیادی جابه جا بشن تا رکورد جدید در محل ماسب قرار بگیره. از طرفی اگه از ایندکس غیر دسته ای استفاده بشه،عملا باید اول در ناحیه ایندکس جستجو صورت بگیره و بعد از پیدا شدن رکورد در این بخش، اشاره گر به جدول داده ها رجوع و رکورد رو بازیابی کنه که قطعا کندتر از ایندکس دسته ای خواهد بود!. در چنین مواردی بهترین انتخاب چی میتونه باشه؟(دسته ای یا غیر دسته ای).


8- معمولاً روی ستونی که دستخوش درج و بروزرسانی زیاد میشه نباید اندیس گذاشت در ضمن باید Fill Factor و Rebuild کردن اندیس رو هم در نظر گرفت.حرف شما کاملا صحیحه ولی در مواردی که مجبوریم برای افزایش سرعت نتیجه کوئری ها این کار رو انجام بدیم تکلیف چی هست؟. مثل همون نام خانوادگی که در بالا گفتم و ناچارا چون درصد زیادی از جستجوها بر حسب نام خانوادگی هست ایندکس گذاری شده (البته noncluster ).
گزینه های Fill Factor و Rebuild رو بیشتر توضیح میدید(لطفا یه مثال کوچیک بزنید).


13-Query Optimizer در SQL Server به کمک Statistics بهترین پلن اجرایی رو تولید میکنه به روز نبودن این آمار یعنی انتخاب پلن اجرای بد.آیا همیشه باید بر روی on باشه؟. در کتاب برای فیلد customerID که از نوع int و Identity هست این گزینه رو گفته اگر بر روی on باشه مثل این میمونه که off هست. دلیلش اینه که چون هر رکورد جدید دقیقا به انتهای جدول اضافه میشه بنابراین تغییر اساسی در آمار ایندکس رخ نمیده!!. این حرف درسته؟(من به این بخش از ترجمه کتاب مشکوکم!)

[محمد سلیم آبادی]

سلام
جواب سوال 5
http://www.30sharp.com/article/13/242/11/%d8%a7%d8%b3%d8%aa%d9%81%d8%a7%d8%af%d9%87-%d8%a7%d8%b2-filtered-index-%d8%a8%d8%b1%d8%a7%db%8c-%d8%af%d8%a7%d8%b4%d8%aa%d9%86-%d8%b3%d8%aa%d9%88%d9%86-%d9%85%d9%86%d8%ad%d8%b5%d8%b1-%d8%a8%d9%81%d8%b1%d8%af-%d8%a8%d8%a7-%da%86%d9%86%d8%af-%d9%85%d9%82%d8%af%d8%a7%d8%b1-null.aspx

[veniz2008]

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

[tooraj_azizi_1035]

معمولاً از یک تعداد خاصی به بعد وجود اندیس فایده مند میشه با مقایسه آمار I/O هنگام وجود و عدم وجود اندیس می تونید متوجه بشید که اندیس روی جدول تاثیر داره یا خیر. باید physical read ها و logical read ها رو مقایسه کنید.




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

معمولاٌ باید عمل Maintenance روی جدولی که دستخوش تغییرات زیاد قرار میگیره به خوبی انجام بشه تا سرعت کوئری ها افت نکنه.
FillFactor میاد فضای استفاده شده هر صفحه اندیس رو تعیین می کنه و مابقی فضای رزرو باقی می مونه تا اگه تغییرات اعمال شد جابه جائی فیزیکی رو هارد اتفاق نیفته.
Rebuild میاد روباره صفحات داده ای رو روی هارد مرتب میکنه تا سرعت موقع خوندن افت نکنه. چون به ترتیب بودن صفحات روی دیسک مزیت بالای کاهش هزینه از نظر I/O رو داره.

در مورد Statistics این رو باید بگم که جزو عملیات Maintanance میشه و به طور Periodic باید به روز بشه اینکه چطور به Optimizer کمک میکنه تا بهترین پلن رو انتخاب کنه در کتابی که گفتم توضیح داده. فعلاً باید صبر کنید.

[website.expert]

سلام دوست عزیز،
من هم در زمینه indexها چندان کامل نیستم که بتونم راهنمایی کنم،
ولی در لینک زیر یه بسته آموزشی خوب هست:
http://www.nikamooz.com/index.php/%D9%85%D8%AD%D8%B5%D9%88%D9%84%D8%A7%D8%AA/%D8%AD%D9%88%D8%B2%D9%87-sql-server/speed-%E2%80%8B%E2%80%8Bin-sql-server-2012
من خودم بدلیل قیمت بالاش خریداری نکردم اگه شما گرفتید و خوب بود بگید ما هم بگیریم.:لبخندساده:
در ضمن اگه اسم کامل فارسی یا انگلیسی کتاب Robin Dewson رو بگید ممنون میشم.آیا فقط در مورد مبحث ایندکس هاست؟

[حمیدرضاصادقیان]

سلام.
پیشنهاد میکنم برای این سوالاتی که مطرح کردید فصل 4 از کتاب Inside T-SQL Querying 2008 (http://www.amazon.com/Inside-Microsoft%C2%AE-SQL-Server%C2%AE-2008/dp/0735626030/ref=sr_1_1?ie=UTF8&qid=1363033020&sr=8-1&keywords=inside+T-sql+querying+2008) و ساختار مربوط به Index ها در کتاب SQL Server 2008 Internals (http://www.amazon.com/Microsoft%C2%AE-SQL-Server%C2%AE-2008-Internals/dp/0735626243/ref=sr_1_1?ie=UTF8&qid=1363033044&sr=8-1&keywords=sql+server+2008+internals) v رو مطالعه کنید.