قاتلین ایندکس در SQL Server [بخش اول]

1398/09/06

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

قاتل شماره ۱ – عملگر LIKE
همان‌طور که می‌دانید با استفاده از عملگر LIKE می‌توان بر روی مقادیر فیلدهای موجود در جداول، عملیات جستجو را بر اساس کاراکترها و یا الگوهای موردنظر انجام داد. فرض کنید از جدول Address به دنبال اطلاعات مشتریانی هستیم که یکی از نشانی‌های آن‌ها که در فیلد AddressLine1 ذخیره شده است، با کاراکتر a آغاز شده باشد. در این حالت عملگر LIKE نهایت استفاده را از ایندکس‌های احتمالیِ موجود بر روی این فیلد خواهد برد.
10-(1).jpg
شما با اجرای کوئری بالا و مراجعه به آمار و اطلاعات I/O از بخش Messages خواهید دید که کم‌ترین تعداد Page برای بازیابی چنین رکوردهایی مورد بررسی قرار گرفته شده است. هم‌چنین با مشاهده Plan اجرایی آن نیز خواهید دید که از عملیات Index Seek استفاده شده است. در این مثال عملگر LIKE به‌خوبی از قابلیت‌های ایندکس‌ها استفاده کرده است.

اما چالش اساسی از زمانی آغاز خواهد شد که به دنبال مقادیری باشیم که کاراکترها یا الگوی مورد ارزیابی به‌عنوان بخشی از مقادیر فیلدها باشد؛ به‌عبارت دیگر آن‌ها در ابتدای مقادیر فیلدها قرار نگرفته باشند. در این حالت دیگر مزیت مرتب‌سازی توسط ایندکس‌ها چندان اهمیتی نخواهد داشت و Engine مربوط به SQL Server نمی‌تواند از این قابلیت استفاده کند چرا که در این‌جا مرتب‌سازی بر اساس چپ‌ترین کاراکترِ مقادیر موجود در فیلدها انجام شده است. (توجه داشته باشید که در این‌جا نوع‌داده‌ی فیلد مورد‌نظر برابر با VARCHAR می‌باشد.)
به عنوان مثال در کوئری زیر به‌دنبال رکوردهایی هستیم که مقدار فیلد AddressLine1 آن حاوی عبارت Longbrook باشد.

پس از اجرای کوئری بالا و با مراجعه به آمار و اطلاعات I/O از بخش Messages خواهید دید که برای بازیابی چنین رکوردهایی تعداد Page های خوانده شده نسبت به حالت قبل به‌شدت افزایش پیدا کرده است. هم‌چنین با مشاهده Plan اجرایی آن نیز خواهید دید که دیگر خبری از عملیات Index Seek نیست و SQL Server برای بازیابی رکوردها مجبور شده است تا رکوردهای جدول را Scan کند.

راه‌حل اول:

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

مجددا این بار هر دو کوئری را با هم اجرا می‌کنیم. کوئری اول با استفاده از عملگر LIKE و کوئری دوم با استفاده از قابلیت FULLTEXT Index عملیات جستجو را انجام خواهند داد.

در تصویر زیر مقایسه آمار و اطلاعات I/O و میزان Cost مربوط به Plan اجرایی هر دو کوئری نمایش داده شده است و شما می‌بینید که چگونه با استفاده از قابلیت FULLTEXT INDEX زمینه‌های افزایش عملکرد اجرایی کوئری و کاهش استفاده از منابع را فراهم کرده‌ایم.