ایندکس‌ Full Text در SQL Server

یکشنبه 25 مهر 1395

در این مقاله مطلابی را در مورد Full Text Index ارائه خواهیم داد . جستجوی Full Text در SQL Server ، این اجازه را به کاربران و برنامه ها می‌دهد که Queryهای Full Text را برای داده های character-base در جداول SQL Server ، اجرا کنند . ایندکس‌ Full Text به ما برای انجام Queryهای پیچیده بر روی داده های Character-base ، کمک می‌کند .

ایندکس‌ Full Text در SQL Server

این Queryها میتوانند شامل کلمه یا عبارتی برای جستجو باشند . قبل از این که ما قادر به اجرای Query های Full Text روی یک جدول باشیم ، ابتدا ما نیاز به ایجاد یک اندیس Full Text در جدول داریم . برای هر جدول فقط توانایی ایجاد یک ایندکس‌ Full Text را داریم ، که این اندیس میتواند بالای 1024 ستون داشته باشد . اندیس Full Text ، میتواند در جدول دارای یک یا چند ستون Chatacter-base باشد . این ستونها میتوانند شامل هر typeای از داده ها باشند : char, varchar, char, nvarchar, ntext, image, xml .

 در اندیس Full Text بوسیله انجام عملیات بر روی کلمات و عبارات بر مبنای قانون های یک زبان خاص همانند انگلیسی یا فارسی ، Queryهای Full Text جستجوهایی را روی text data انجام میدهند . Queryهای Full Text میتوانند شامل کلمات و عبارات ساده یا کلمات و عبارات چند وجهی باشند . یک کوئری Full-Text همه ی اسنادی که حداقل دارای یک کلمه match باشد را باز میگرداند ( که به hit معروف و شناخته می‌شود) . Match زمانی رخ میدهد که یک سوژه از اسناد دارای تمامی termهای مشخص شده در کوئری Full-Text باشد . .

با وجود کلمه کلیدی "like" در SQL ، به چه دلیل ما از  (Full Text Index (FTI ها استفاده میکنیم :

اجازه دهید یک سناریو را در نظر بگیریم ، ما یک جدول داریم که دارای ستونی با نام data است . کاری که ما برای جستجوی نام خاصی همانند 'Smith' انجام میدهیم ، همانطور که در زیر نمایش داده شده است ، استفاده از like است .

کوئری بالا برای سناریوی بالا کارآمد است ، اگر ما دنبال داده ای میگشتیم که دقیقا همخوانی نداشته باشد ، چه کاری انجام میدهیم . FTS الگوریتم بهتری برای تطابق نام نسبت FTI دارد . از این رو  ، در زمان جستجوی smith کارایی بهتری دارد و Smith, Smythe, Smithers و ... را نمایش میدهد . در این جور موارد FTS نتایج بهتری را نسبت به روش قدیمی استفاده از like باز میگرداند . 

چه زمانی از FTI به جای عبارت like استفاده میکنیم :
• یک کلمه یا عبارت نزدیک به کلمه یا عبارت جستجو شده را می‌خواهیم . 
• زمانی که اندازه نتایج به صدها و هزاران میرسد .
• میلیون ها سطر که هر کدام شامل یک دسته همانند "wordAwordBwordC..." هستند .
•هر کلمه مشتق شده از یک کلمه را بخواهیم برای مثال (run و ran و running) .

 ایجاد Full Text Index :
حال ، به سراغ چگونگی ایجاد Full Text Indx می‌رویم .با دو روش برای ایجاد Full Text Index آشنا خواهید شد . ایجاد بصورت دستی (manually) و استفاده از دستورات SQL .

ایجاد دستی  Full Text Index :

برای ایجاد manually مراحل زیر را باید دنبال کنیم :
1. ایجاد یک Full Text Catalog
2. ایجاد Full Text Index
3.  پر کردن Index 

مرحله 1 : ایجاد Full Text Catalog 

Full Text Catalog برای Full Text Index مورد استفاده قرار میگیرد . اگر ما Full Text Catalog را مشخص نکنیم ، آنگاه SQL Server از Catalog پیش فرض استفاده میکند . حال ما به سراغ چگونگی ایجاد full text catalog میرویم .

برای ایجاد Full Text Catalog ، ابتدا پایگاه داده خود را انتخاب کنید ، سپس به فولدر storage بروید و روی
Full Text Catalog  راست کلیک کرده و گزینه New Full-Text Catalog را انتخاب کنید :


 

حال ، یک نام برای full text catalog خود ، انتخاب کنید :

همانطور که مشاهده میکنید یک new catalog در فولدر storage ایحاد شده است .


مرحله دوم : ایجاد Full Text Index 

برای ایجاد Full Text Index روی جدول مورد نظر خود راست کلیک کرده و Define Full-Text Index را انتخاب کنید ، تصویر زیر را مشاهده کنید :

  
حال 
Unique Index را انتخاب کنید . این اجباری است که برای جدول “Full Text Index” حداقل باید یک 
 
Unique Index داشته باشید . 

نام ستون را انتخاب کنید و نوع زبان مورد نظر را مشخص کنید . شما میتوانید فقط character-base و
image-base را انتخاب کنید .  


change tracking را انتخاب کنید :

حال full-text catalog را برای Index انتخاب کنید :

تصویر آخر بیان دارد که full text index با موفقیت ایجاد شده است . حال این Index را پر میکنیم .

مرحله سوم : پر کردن Index 

برای پر کردن Index ، روی جدول راست کلیک کرده و گزینه “
Start Full Population” را انتخاب کنید . 

ایجاد Full Text Index با استفاده از دستورات SQL :
از دستورات زیر برای ایجاد Full Text Index اسفاده میکنیم .

 Syntax :

CREATE FULLTEXT INDEX ON table_name  
[ ( { column_name  
[ TYPE COLUMN type_column_name ]  
[ LANGUAGE language_term ]   
[ STATISTICAL_SEMANTICS ]  
} [ ,...n]   
) ]  
KEY INDEX index_name  
[ ON<catalog_filegroup_option> ]  
[ WITH [ ( ] <with_option> [ ,...n] [ ) ] ]  
[;]  
  
<catalog_filegroup_option>::=  
{  
fulltext_catalog_name  
| ( fulltext_catalog_name, FILEGROUP filegroup_name )  
| ( FILEGROUP filegroup_name, fulltext_catalog_name )  
| ( FILEGROUP filegroup_name )  
}  
  
<with_option>::=  
{  
CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [, NO POPULATION ] }   
| STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }  
| SEARCH PROPERTY LIST [ = ] property_list_name  
}  

مثال :

CREATE FULLTEXTCATALOG New_CatalogASDEFAULT;  
CREATE FULLTEXTINDEX ON dbo.Employee(EmployeeName TYPECOLUMN varchar LANGUAGE 1033,EmpSalary TYPECOLUMN varchar LANGUAGE 1033)  
KEY INDEX UN_Pankaj  
ON  
New_Catalog  


بعد از ایجاد full text query و full text index ، حال به سراغ آموزش چگونگی استفاده از اینها برای بالا بردن کارایی در هنگام جستجو ، میرویم . در T-SQL چهار عمل اصلی وجود دارد که اجازه فعل و انفعالات با اندیس های full text
شما را میدهد :
 
متد CONTAINS و FREETEXT :

اعمال CONTAINS و FREETEXT  یک مقدار bool را بازمیگردانند و این بدان معناست که ما میتوانیم بصورت مستقیم از اینها در WHERE استفاده میکنیم :

FREETEXT :
FREETEXT کلماتی که از نظر معنایی نیز با کلمه جستجو شده ، تطابق دارد را پیش بینی میکند . زمانی که FREETEXT مورد استفاده قرار میگیرد ، engine کوئری full-text این اعمال را بصورت داخلی بر روی freetext_string میدهد ، و به هر term وزنی را انتساب میدهد ، سپس matchها را پیدا می‌کند :

• جداسازی رشته ها به کلمات فردی 
• تولید فرم صرفی (inflectional) کلمات 
• شناسایی لیستی از توسعه گراها یا تعویض کنندها برای termها بر مبنای matchها  

مثال :

SELECT TOP 10 tmski.Keyword_TextFROMdbo.TblMaster_Searching_Keyword_Infotmski  
WHERE  
FREE TEXT(Keyword_Text,'Hotel Above')

خروجی :


CONTAINS :

CONTAINS برای کلمات و عباراتی که دقیقا match هستند ، جستجو میکند . این شبیه freetext است فقط با این تفاوت که این یک کلمه کلیدی را با رکورد ها تطابق میدهد ، و اگر ما قصد ترکیب دیگر کلمات را در جستجوی خود  داشته باشیم ما باید در search از "and" و "or" استفاده کنیم .

CONTAINS  میتواند برای جستجوهای زیر مورد استفاده قرار بگیرد :

• یک کلمه یا عبارت 
• پیشوندی از یک کلمه یا یک عبارت 
• لغتی که نزدیک به کلمه دیگری است 
• ریشه کلمات و مشتقات کلمات دیگر
• یک کلمه که از نظر معنایی با کلمه مورد نظر مترادف باشد (برای مثال ، آهن و آلمینیوم هر دو فلز هستند)

مثال :

SELECT TOP 10 tmski.Keyword_TextFROMdbo.TblMaster_Searching_Keyword_Infotmski  
WHERE  
CONTAINS(Keyword_Text,'Hotel OR Above')  



نتیجه گیری :

از جستجوی Full Text برای زمانی که سطح داده ها بزرگ است و شما قصد انجام جستجوی یک کلمه یا عبارت مشخص را دارید ، میتوانید استفاده کنید . Full Text Index نیز میتواند با استفاده از peratorهای 
FREETEXT (), CONTAINS () , “and” یا “or”  برای جستجوی کلمات و عبارات مورد استفاده قرار گیرد .


آموزش نصب sql server 2017

برنامه نویسان

نویسنده 3355 مقاله در برنامه نویسان

کاربرانی که از نویسنده این مقاله تشکر کرده اند

در صورتی که در رابطه با این مقاله سوالی دارید، در تاپیک های انجمن مطرح کنید