ایندکس 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 ، کمک میکند .
این 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
- SQL Server
- 5k بازدید
- 5 تشکر