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

مقدمه

شاید خواندن چنین جمله‌ای برای‌تان عجیب باشد؛ ” بسیاری از توسعه‌دهندگان نرم‌افزارهای کاربردی و یا مدیران دیتابیس‌ها اهمیتی چندانی به مقوله ایندکس‌ها نمی‌دهند! ” برخی از آن‌ها صرفا ایجاد محدودیت‌های 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 زمینه‌های افزایش عملکرد اجرایی کوئری و کاهش استفاده از منابع را فراهم کرده‌ایم.

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