بسم الله الرحمن الرحیم

آموزش ساخت Full Text Catalog و Full Text Search در SQL Server

در این آموزش مفاهیم زیر را بررسی خواهیم کرد

• ساخت Full-Text Search

  • ساخت Full-Text Catalog

  • ساخت Full-Text Index

• نوشتن Query با استفاده از Full-Text Search

•  استفاده از Full-Text Search  در پروژه های ASP.NET MVC  به کمک LINQ

تصور کنید وب سایتی دارید و میخواهید بر روی حجم زیادی از اطلاعات جستجو انجام دهید. وب سایت شما یک وب سایت آموزشی مانند ABLY.ir است و در آن متن های زیادی وجود دارد. شما میخواهید یک فرم جستجو برای جستجو در تمامی قسمت های وب سایت ایجاد کنید.
چطور می توانید این جستجو را با این ویژگی ها پیاده سازی کنید؟
1- جستجو در کلمات کلیدی
2- جستجو در میان نویسندگان
3- جستجو در مطالب
4- جستجو در عنوان فایل ها
و..

اولین راه حلی که انتخاب می شود JOIN کردن تمام جداول و نوشتن یک دستور SELECT با انجام WHERE بر روی همه ی ستون ها است!.  این راه حل بدترین راه حل ممکن است زیرا در صورتی که حجم اطلاعات شما افزایش یابد عملا سیستم جستجو شما دیگر پاسخگو نخواهد بود زیرا طبیعتا برای جستجو در متن ها باید از دستور LIKE استفاده کنید که این دستور خود به دلیل مقایسه تمامی عبارت های ذخیره شده به صورت بخش به بخش با متن درخواستی کاربر بسیار سرعت کمی دارد، همچنین هزینه JOIN بسیار سنگین خواهد بود و باعث می شود عملا این ویژگی غیر قابل استفاده شود.
SQL Server با ارائه دادن ابزاری به نام Full Text Search به شما این امکان را می دهد که به سادگی و با بهترین کارایی جستجحو های خود را بر روی متن ها انجام دهید.

Full Text Search

Full Text Search ابزاری است که به شما برای ایجاد انواع کوئری های پیچده بر روی داده های از نوع کاراکتر (Character) کمک می کند. این کوئری ها می توانند بر روی کلمات، جمله ها و عبارت ها مورد استفاده قرار گیرند. ما می توانیم یک full-text index را بر روی یک Table و یا یک indexed view ایجاد کنیم. full-text Search تنها می تواند بر روی یک Table و یا یک indexed view مورد استفاده قرار گیرد. Index می تواند تا 1024 ستون (Column) را در بر گیرد.

Full Text Index

Full Text Index از Token هایی تشکیل می شود که این Token ها در واقع همان کلمات درون یک فیلد شما هستند و این امر باعث می شود که بتوان بر روی متون index گذاری کرد و سرعت جستجو را افزایش داد.
مثال: من اسماعیل شیدایی هستم.
اگر مقدار یکی از رکوردهای ما به صورت بالا باشد پس از انجام عملیات full-text index این متن به صورت زیر ذخیره خواهد شد.
"من"
"اسماعیل"
"شیدایی"
"هستم"
این کار باعث می شود تا شما در سریعترین حالت ممکن بتوانید به اطلاعات مورد نظر خود برسید زیرا SQL Server اطلاعات شما را Index گذاری می کند در نتیجه به سرعت می تواند محل قرار گیری (شماره رکورد) هر کلمه را در جدول بازیابی کند.

طبیعتا راه حل پیش فرض برای جستجو در متن ها استفاده از دستور LIKE می باشد.  دستور Like بر روی کاراکترها جستج خود را انجام می دهد و مقدار درخواستی شما را با تمام متن ها مقایسه می کند اما در Full Text Search مقدار درخواستی شما با کلمات مقایسه می شود به همین دلیل تفاوت سرعت اجرای این دو دستور در کوئری های پیچیده بسیار مشخص خواهد بود.

برای ساخت Index می بایست ما مراحل زیر را انجام دهیم.

1.    Create a Full-Text Catalog

2.    Create a Full-Text Index

3.    Populate the Index

Create a Full-Text Catalog

ابتدا دیتابیس خود را انتخاب کرده، سپس از بخش Storage بر روی Full Text Catalog راست کلیک کرده و گزینه New Full-Text-Catalog را انتخاب می کنیم.

در پنجره باز شده نامی را برای Full Text Catalog خود انتخاب می کنیم و سپس Ok را انتخاب کنید. Full Text Catalog ما با موفقیت ایجاد شد.

Create a Full-Text Index

حال بر روی جدولی که قصد دارید اطلاعات درون آن را جستجو کنید کلیک راست کرده و از گزینه Full-Text Index زیر منو Define Full-Text Index را انتخاب کنید.

گزینه Next را انتخاب کنید.

در این مرحله فیلدی از جدول که Index گذاری شده است را باید انتخاب کنید. همانطور که می دانید کلید های اصلی هر جدول Index نیز هستند، در این جا کلید اصلی جدول که تنها فیلد Index گذاری شده است را انتخاب می کنیم.

در این مرحله فیلدهایی که قصد جستجو کردن اطلاعات بر روی آنها را دارید را انتخاب کنید.
انتخاب زبان باعث می شود تا فرایند جستجو تسریع شود.

Statistics semantics

گزینه Statistics semantics برای جستجو کردن عبارت هایی است که هم معنا و هم واژه عبارت کاربر هستندو مثلا کاربر SQL را در سایت ABLY.ir جستجو می کند. در صورتی که بخواهید کاربر به اطلاعات بهتری دست پیدا کند شما باید علاوه بر کلمه SQL کلمات دیگری همچون SQL Server, MSSQL را برای او جستجو کنید. گوگل نیز دقیقا از همین روش برای بهتر شدن نتایج جستجو استفاده می کند. برای انجام اینکار می توانید از Statistics semantics که تا 100 کلمه را پیشتیبانی می کند استفاده کنید.
برای استفاده از این ویژگی می بایست ابتدا آن را بر روی SQL Server خود نصب و تنظیم نمایید. در مورد این ویژگی در این مقاله صحبت نخواهیم کرد.

فیلد های مورد نظر خود را انتخاب کنید و سپس گزینه Next را انتخاب کنید.

 در این مرحله شما می توانید نحوه انجام تغییرات را برای  Indexگذاری مشخص کنید. بهترین گزینه برای Changing Track گزینه Automatic است، ما در اینجا نیز این گزینه را انتخاب می کنیم و به مرحله بعد می رویم.

Catalog ایی را که در مرحله ابتدایی ساختید می توانید همین جا انتخاب کنید و یا Catalog جدیدی را ایجاد کنید. همچنین محل ذخیره سازی Index را می توانید با استفاده از Create Partition در یک فایل جدا قرار دهید. برای اینکار کافی است محل ذخیره سازی را توسط گزینه Select Index filegroup مشخص کنید.

Stopwords and Stoplists

Stoplist ویژگی است که در نسخه های SQL Server 2008 و بعد از آن با عنوان Stopwords and Stoplists مورد استفاده قرار می گیرد، این ویژگی در واقع به منظور حذف کلماتی است که در جستجو شما تاثیری ندارند مانند and, is, to, the و..
همانطور که می دانید Google نیز در جستجوها کلمات ربطی را حذف می کند شما نیز می توانید این کار را برای جتسجو های خود انجام دهید.
 بر روی گزینه Next کلیک کنید.

در این مرحله می توانید زمانبندی انتشار Catalog خود را انتخاب کنید. بر روی Next کلیک کنید.

بر روی گزینه Finish کلیک کنید تا فرایند ایجاد Index به اتمام برسد.

حال بر روی جدول مربوطه مجددا راست کلیک کرده و از گزینه Full-Text Index زیر منو start full population را انتخاب کنید تا عملیات ایندکس گذاری آغاز شود.

از این لحظه می توانید بر روی Table خود دستورات مربوط به full text search را اجرا کنید.

فرایند ساخت Full-Text Search  در این مرحله به پایان رسید.

جستجو با استفاده از Full-Text Search

()FREETEXT

برای جستجو بر روی ستون هایی که دارای محتوای کاراکتر هستند (character-based data types) از تابع  ()FREETEXT استفاده می کنیم. این تابع کلمات درون یک رکورد را بررسی و سپس رکوردهایی که دارای این کلمه باشند را بازیابی می کند.

()CONTAINS

تابع ()CONTAINS مشابه تابع ()FREETEXT است با این تفاوت که شما می توانید چندین کلمه را برای جستجو با استفاده از دستورات AND , OR ترکیب کنید.

در زیر ما 3 مثال را بررسی کرده ایم

در این مثال ما به دنبال کلمه Ernst در کل این جدول و تمامی فیلدهایی که full text index حضور دارند می گردیم.

SELECT * FROM [Customers] WHERE FREETEXT(*,'Ernst')

در این مثال ما به دنبال کلمه Ernst یا preparadas در فیلد [CompanyName] می گردیم.

SELECT * FROM [Customers] WHERE CONTAINS([CompanyName],'Ernst OR preparadas')

در این مثال ما به دنبال کلمه Ernst و preparadas در فیلد [CompanyName] می گردیم.

SELECT * FROM [Customers] WHERE CONTAINS([CompanyName],'Ernst AND preparadas')

آموزش کار با Full-Text Search در ASP.NET MVC به کمک LINQ

همانطور که در ابتدا گفتیم ممکن است شما بخواهیم جستجو خود را بر روی چندین جدول انجام دهید. برای اینکار می بایست Index View ایجاد کنید برای انجام اینکار کافی است تنها به جای انتخاب یک Table بر روی View مورد نظر خود راست کلیک کرده و سپس تمامی مراحل بالا را مجددا انجام دهید.

نحوه استفاده از Full Text Search در LINQ و Entity Framework

LINQ to SQL اجازه می دهد شما دستورات LINQ خود را درون NET. بنویسید و سپس این دستورات به SQL Server تبدیل خواهند شد. LINQ بسیاری از افعال و دستورات SQL Server را پشتیبانی می کند اما متاسفانه هنوز برخی از دستورات SQL Server را نمی توان در LINQ به کار برد.

یکی از دستوراتی که هنوز در LINQ پشتیبانی نمی شوند دستورات مربوط به Full-Text-Search هستند.

مانند:  CONTAINS, CONTAINSTABLE,FREETEXT, FREETEXTTABLE.


بدین معنا که شما نمی توانید دستور زیر را اجرا کنید:

var mySearchList = from s in new SQLBitsDataContext().sessions
                  where s.Contains("Description,Title","simon")
                 select s;

اما شما می توانید دستور زیر را اجرا کنید:

var mySearchList = from s in new SQLBitsDataContext().sessions
                  where s.description.Contains("simon")
                 select s;

با توجه به دستور بالا ممکن است شما فکر کنید، دستورات Full-Text-Search توسط LINQ پشتیبانی شوند. متاسفانه دستور Contains بالا به دستور ساده LIKE تبدیل می شود. حاصل تبدیل کد LINQ بالا کد SQL Server زیر خواهد بود.

select * from sessions where description like '%simon%'

راه حل این مشکل استفاده از دستور زیر می باشد.

استفاده از Full-Text Search در LINQ

var list = _context.Database.SqlQuery<customer>
(" SELECT * FROM [Customers] WHERE FREETEXT(*,@keywords)",
 new SqlParameter("keywords", searchString)).ToList();

در قسمت SqlQuery<customer> مقدار customer مشخص کننده نوع خروجی این دستور یا در واقع نوع entity type ایی است که بازیابی می شود.

روش های دیگری نیز وجود دارند اما این ساده ترین و سریعترین روش ممکن برای استفاده از full text search در LINQ همین روش می باشد.