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

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

نوشته شده توسط: مهدی شیشه بری
۲۸ مرداد ۱۳۹۶
زمان مطالعه: 9 دقیقه
۴.۶
(۹)

مقدمه

شاید خواندن چنین جمله‌ای برای‌تان عجیب باشد؛ ” بسیاری از توسعه‌دهندگان نرم‌افزارهای کاربردی و یا مدیران دیتابیس‌ها اهمیتی چندانی به مقوله ایندکس‌ها نمی‌دهند! ” برخی از آن‌ها صرفا ایجاد محدودیت‌های Primary Key و Unique Key را برای جداول کافی می‌دانند و برخی دیگر حتی نسبت به چنین موضوعی آگاهی نداشته و تنها از روی عادت همیشگی، چنین محدودیت‌هایی را اِعمال می‌کنند. بعید می‌دانم مخاطب این مجموعه مقالات، چنین افرادی باشند.
گروه دیگری هم هستند که نسبت به موضوع ایندکس‌گذاری و نقش آن در افزایش عملکرد کوئری‌ها بسیار حساس هستند اما آیا واقعا به اعتبار ایجاد ایندکس‌های مناسب بر روی جداول، می‌توان ادعا داشت که بر تمامی مشکلات غلبه شده و به‌نهایت کارآیی در دیتابیس رسیده‌ایم؟
شما در این مجموعه مقالات درخواهید یافت که ایندکس‌گذاری مناسب فقط بخشی از مسیر بهینه‌سازی و افزایش کارآیی است چرا که غفلت از درست‌نویسی کوئری و عدم رعایت استاندارها می‌تواند موجب تاثیر منفی عملکرد ایندکس‌ها شود.
نکته مهم: تمامی کوئری‌های این مجموعه مقالات، بر روی دیتابیس AdventureWorks2016 و در محیط SQL Server Management Studio 2016 و ۲۰۱۷ اجرا شده است.

دوره کوئری نویسی نیک آموز

قاتل شماره ۱ عملگر LIKE

همان‌طور که می‌دانید با استفاده از عملگر LIKE می‌توان بر روی مقادیر فیلدهای موجود در جداول، عملیات جستجو را بر اساس کاراکترها و یا الگوهای موردنظر انجام داد. فرض کنید از جدول Address به دنبال اطلاعات مشتریانی هستیم که یکی از نشانی‌های آن‌ها که در فیلد AddressLine1 ذخیره شده است، با کاراکتر a آغاز شده باشد. در این حالت عملگر LIKE نهایت استفاده را از ایندکس‌های احتمالیِ موجود بر روی این فیلد خواهد برد.

USE AdventureWorks2016
GO
SET STATISTICS IO ON;
SELECT
AddressID, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE AddressLine1 LIKE 'a%';

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

SELECT
AddressID, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE AddressLine1 LIKE '%Longbrook%';

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

راه‌حل اول

در سازمان یا شرکت‌مان یک قانون سفت و سخت وضع کنیم که هیچ‌کس حق ندارد به این شکل از عملگر LIKE استفاده کند وگرنه جریمه خواهد شد! به‌نظر من، این قانون بیشتر از طرف مدیران کم‌تجربه یا کم‌دانش ارائه خواهد شد؛ پس اصلا منصفانه نیست.

راه‌حل دوم

به‌جای اِعمال رفتارهای غیر‌مدیریتی بهتر است یادی کنیم از مرحوم سهراب سپهری: ” چشم ها را باید شست، جور دیگر باید دید! “
برای رفع چنین مشکلی می‌توان از FULLTEXT INDEX ها استفاده کرد هر چند که بسیاری از افراد به‌دلیل عدم آشنایی، از چنین قابلیت‌هایی استفاده نمی‌کنند. با استفاده از FULLTEXT INDEX کلمات، درون یک یا چند فیلد به‌همراه موقعیت‌شان در جدول، فهرست‌بندی می‌شوند که همین موضوع در هنگام اجرای کوئری موجب افزایش سرعت شده و دیگر نیازی به پیمایش تمامی رکوردها نخواهد بود.
برای این کار می‌بایست ابتدا یک FULLTEXT CATALOG تعریف کنیم:

CREATE FULLTEXT CATALOG IndexKiller AS DEFAULT;

سپس FULLTEXT INDEX ای را بر روی فیلد موردنظر تعریف می‌کنیم:

CREATE FULLTEXT INDEX ON Person.Address(AddressLine1)
KEY INDEX PK_Address_AddressID;
GO

در نهایت تغییراتی کوچکی را بر روی کوئری انجام می‌دهیم. در این‌جا می‌بایست در بخش WHERE یکی از توابع مربوط به FULLTEXT INDEX را با عنوان CONTAINS مورد استفاده قرار دهیم.

SELECT
AddressID, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE CONTAINS (AddressLine1,'Longbrook');

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

-- Query with LIKE operator
SELECT
AddressID, AddressLine1, AddressLine2,
City, StateProvinceID, PostalCode
FROM Person.Address
WHERE
AddressLine1 LIKE '%Longbrook%';
-- Query with LIKE operator
SELECT
AddressID, AddressLine1, AddressLine2,
City, StateProvinceID, PostalCode
FROM Person.Address
WHERE
CONTAINS (AddressLine1,'Longbrook');

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

در قسمت بعدی با یکی دیگر از قاتلین ایندکس‌ها آشنا خواهید شد.

چه رتبه ای می‌دهید؟

میانگین ۴.۶ / ۵. از مجموع ۹

اولین نفر باش

title sign
دانلود مقاله
قاتلین ایندکس در SQL Server [بخش اول]
فرمت PDF
7 صفحه
حجم 1 مگابایت
دانلود مقاله
title sign
معرفی نویسنده
title sign
معرفی محصول
title sign
دیدگاه کاربران

    • عالی بود.
      فقط فکز میکنم تصویر دوم (مربوط به کوئری LIKE ‘%Longbrook%’;) اشتباه گذاشته شده، و عکس مربوط به کوئری فول-تکست هم همان است.
      (آدرس هر دو https://nikamooz.com/wp-content/uploads/2017/08/kill_index_3-e1503163450575.jpg است)

    • سلام
      آیا امکان استفاده از قابلیت FULLTEXT Index در جستجو هایی که روی View انجام میشه، امکان پذیر هست ؟

    • سلام
      آیا امکان استفاده از قابلیت FULLTEXT Index در جستجو هایی که روی View انجام میشه، امکان پذیر هست ؟

    • عالی بود
      سپاس

    • عالی بود
      سپاس

    • دستتون درد نکنه منتظر قسمت های بعدی هستیم

    • در متن ها نوشته شده index seek ولی در عکس index scan نشان داده میشود

      • سلام
        ممنون از دقت بالای شما. اگر اولین کوئری را اجرا کنید، دقیقا در پلن اجرایی آن خواهید دید که از عملیات Index Seek استفاده شده است. فکر می‌کنم تصویر دومین پلن اجرایی در تصویر اول تکرار شده است. با عرض پوزش اصلاح خواهد شد.
        ضمنا تمامی اسکریپت‌های این مقاله و قسمت‌های بعدی را علاوه بر دیتابیس AdventureWorks2016 بر روی AdventureWorks2014 هم می‌توانید اجرا کنید.

    • با سلام و وقت بخیر

      شما می توانید با استفاده از این DM View سیستمی
      و فیلد percent_complete در صد پیشرفت رو بررسی کنید.
      sys.dm_exec_requests
      برای استفاده از این DM View به لینک زیر مراجعه کنید

      https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql?view=sql-server-ver15

    • با سالم
      سپاس از همراهی شما
      لینک دانلود تست شد و بدون خطا فایل قابل دانلود است ولی برای سهولت شما فایل برای شما ایمیل گردید.

    • با سالم

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

    • سلام
      ممنون از دقت بالای شما. اگر اولین کوئری را اجرا کنید، دقیقا در پلن اجرایی آن خواهید دید که از عملیات Index Seek استفاده شده است. فکر می‌کنم تصویر دومین پلن اجرایی در تصویر اول تکرار شده است. با عرض پوزش اصلاح خواهد شد.
      ضمنا تمامی اسکریپت‌های این مقاله و قسمت‌های بعدی را علاوه بر دیتابیس AdventureWorks2016 بر روی AdventureWorks2014 هم می‌توانید اجرا کنید.

  • 1
  • 2
هر روز یک ایمیل، هر روز یک درس
آموزش SQL Server بصورت رایگان
همین حالا فرم زیر را تکمیل کنید
دانلود رایگان جلسه اول
نیک آموز علاوه بر آموزش، پروژه‌های بزرگ در حوزه هوش تجاری و دیتا انجام می‌دهد.
close-link
جشنواره عیدآموز نیک آموز، سال جدید رو با قدرت شروع کن
مشاهده تخفیف ها
close-image