افزایش عملکرد جستجو Wildcard با LIKE در SQLServer

بهینه‌سازی جستجوهای Wildcard با LIKE در SQL Server: راهکارهایی برای افزایش عملکرد

یکی از چالش‌های رایج در بهینه‌سازی عملکرد کوئری‌ها در SQL Server، استفاده از عملگر LIKE به همراه کاراکترهای wildcard است. این نوع جستجوها، به ویژه زمانی که % در ابتدای رشته جستجو قرار می‌گیرد، می‌توانند به شدت بر سرعت اجرای کوئری تأثیر منفی بگذارند و از قابلیت بهره‌گیری از ایندکس‌ها جلوگیری کنند.

هنگامی که شما یک جستجوی LIKE را با یک wildcard پیشرو (مانند ‘%text’ یا ‘%text%’) انجام می‌دهید، موتور پایگاه داده مجبور است تمام ردیف‌های جدول را اسکن کند تا مطابقت‌ها را پیدا کند. این فرآیند، اسکن کامل جدول (Table Scan) نامیده می‌شود که برای جداول بزرگ بسیار ناکارآمد است و منجر به کاهش شدید عملکرد می‌شود. دلیل آن این است که ایندکس‌ها بر اساس ترتیب مرتب شده داده‌ها کار می‌کنند و وجود % در ابتدا، استفاده از این ترتیب را ناممکن می‌سازد.

برای درک بهتر این موضوع، به این کوئری رایج توجه کنید که به دلیل wildcard پیشرو، نمی‌تواند از ایندکس‌های استاندارد بهره ببرد:

SELECT *
FROM YourTable
WHERE YourColumn LIKE '%SearchTerm%';

در این حالت، حتی اگر بر روی ستون YourColumn ایندکس داشته باشید، SQL Server نمی‌تواند از آن استفاده کند زیرا نیاز به بررسی هر مقدار برای یافتن SearchTerm در هر جای رشته وجود دارد. اما، اگر wildcard تنها در انتهای رشته باشد (مانند ‘SearchTerm%’)، SQL Server می‌تواند از ایندکس برای جستجوی کارآمدتر استفاده کند، زیرا می‌تواند به سرعت به شروع رشته‌های مورد نظر در ایندکس پرش کند.

برای مثال، این کوئری می‌تواند از ایندکس بهره ببرد و عملکرد بهتری داشته باشد:

SELECT *
FROM YourTable
WHERE YourColumn LIKE 'SearchTerm%';

برای بهبود عملکرد در سناریوهایی که نیاز به جستجوی wildcard پیشرو وجود دارد، چندین راهکار پیشرفته وجود دارد که در ادامه به آن‌ها می‌پردازیم و می‌توانند به بهینه‌سازی کوئری‌های شما کمک کنند:

استفاده از Full-Text Search (FTS)

یکی از قوی‌ترین و کارآمدترین روش‌ها برای انجام جستجوهای متنی پیچیده، به ویژه با wildcard پیشرو، استفاده از قابلیت Full-Text Search در SQL Server است. FTS به شما اجازه می‌دهد تا جستجوهای بسیار سریع بر روی حجم وسیعی از داده‌های متنی انجام دهید و برای سناریوهایی مانند جستجوی کلمات کلیدی در متن مقالات، توضیحات محصول یا پیام‌های لاگ بسیار مناسب است.

برای استفاده از Full-Text Search، ابتدا باید یک کاتالوگ Full-Text و سپس یک ایندکس Full-Text بر روی جدول و ستون مورد نظر خود ایجاد کنید. این فرآیند یک ایندکس مخصوص ایجاد می‌کند که به SQL Server امکان می‌دهد تا جستجوهای کارآمد را بدون نیاز به اسکن کامل جدول انجام دهد.

مثالی از ایجاد کاتالوگ و ایندکس Full-Text بر روی ستون YourColumn در YourTable:

CREATE FULLTEXT CATALOG MyFullTextCatalog AS DEFAULT;

CREATE FULLTEXT INDEX ON YourTable(YourColumn)
KEY INDEX PK_YourTable ON MyFullTextCatalog;

پس از ایجاد ایندکس Full-Text، می‌توانید از توابع CONTAINS یا FREETEXT برای انجام جستجوهای متنی استفاده کنید. این توابع به گونه‌ای طراحی شده‌اند که از ایندکس Full-Text استفاده کنند و عملکرد بسیار بهتری نسبت به LIKE با wildcard پیشرو ارائه می‌دهند.

مثالی از جستجو با CONTAINS برای یافتن SearchTerm در هر جای ستون YourColumn:

SELECT *
FROM YourTable
WHERE CONTAINS(YourColumn, '"*SearchTerm*"');

نکته: در CONTAINS، برای شبیه‌سازی %text% از “*text*” استفاده می‌شود. همچنین می‌توانید از عملگرهای بولی (مانند AND, OR) و جستجوهای نزدیکی (proximity searches) نیز بهره ببرید.

راهکار خلاقانه: معکوس کردن رشته و ایندکس‌گذاری

برای حالتی که نیاز به جستجوی LIKE ‘%SearchTerm’ (یعنی wildcard در ابتدا و رشته ثابت در انتها) دارید و نمی‌خواهید یا نمی‌توانید از Full-Text Search استفاده کنید، یک راهکار هوشمندانه و مؤثر وجود دارد: معکوس کردن رشته در یک ستون محاسباتی (Computed Column) و سپس ایندکس‌گذاری آن ستون.

با ایجاد یک ستون محاسباتی که حاوی نسخه معکوس شده ستون اصلی است و سپس ایجاد یک ایندکس بر روی این ستون محاسباتی، می‌توانید جستجوی LIKE ‘%SearchTerm’ را به LIKE ‘mretHcraeS%’ (یعنی یک جستجوی wildcard پایانی) تبدیل کنید که می‌تواند از ایندکس بهره ببرد و عملکرد کوئری را به طور قابل توجهی بهبود بخشد.

مراحل پیاده‌سازی این راهکار:

1. افزودن یک ستون محاسباتی به جدول که مقادیر ستون اصلی را معکوس می‌کند:

ALTER TABLE YourTable
ADD ReversedColumn AS REVERSE(YourColumn);

2. ایجاد ایندکس بر روی ستون محاسباتی جدید برای بهینه‌سازی جستجو:

CREATE INDEX IX_YourTable_ReversedColumn
ON YourTable(ReversedColumn);

3. حال برای جستجوی LIKE ‘%SearchTerm’ (یافتن کلماتی که به ‘SearchTerm’ ختم می‌شوند)، رشته جستجو را معکوس کرده و بر روی ReversedColumn با wildcard پایانی جستجو کنید:

SELECT *
FROM YourTable
WHERE ReversedColumn LIKE REVERSE('SearchTerm') + '%';

این روش به SQL Server امکان می‌دهد تا از ایندکس IX_YourTable_ReversedColumn برای یافتن سریع ردیف‌های مطابق استفاده کند و عملکرد را به طور چشمگیری بهبود بخشد.

SQL Server 2022 و بهبود الگوهای تطبیق

SQL Server 2022 بهبودهایی را در زمینه تطبیق الگو (pattern matching) ارائه داده است که می‌تواند در برخی سناریوها به بهینه‌سازی جستجوهای LIKE کمک کند. با این حال، برای سناریوهای پیچیده با wildcard پیشرو و حجم بالای داده، Full-Text Search و راهکارهای مبتنی بر ایندکس همچنان بهترین راهکارها باقی می‌مانند.

استفاده از CHARINDEX و PATINDEX

توابع CHARINDEX و PATINDEX می‌توانند برای یافتن موقعیت یک زیررشته در یک رشته استفاده شوند. اگرچه این توابع می‌توانند به جای LIKE ‘%text%’ استفاده شوند (با CHARINDEX(‘SearchTerm’, YourColumn) > 0)، اما معمولاً عملکرد آن‌ها برای جداول بزرگ بدتر از LIKE است زیرا این توابع هم منجر به اسکن کامل جدول می‌شوند و از ایندکس‌ها بهره‌ای نمی‌برند.

مثالی از استفاده از CHARINDEX برای یافتن SearchTerm در ستون YourColumn:

SELECT *
FROM YourTable
WHERE CHARINDEX('SearchTerm', YourColumn) > 0;

این توابع تنها زمانی توصیه می‌شوند که نیاز به یافتن موقعیت دقیق زیررشته دارید و یا حجم داده‌ها بسیار کم است. در غیر این صورت، استفاده از Full-Text Search یا سایر راهکارهای بهینه‌سازی ذکر شده ارجحیت دارد و عملکرد بهتری را برای جستجوهای wildcard فراهم می‌کنند.

“`