ایجاد Full Text Search در SQL Server

[ali]

ایجاد فول تکست سرچ در اس کیو ال سرور
Full Text Search امکان index کردن و جستجوی دیتای متنی را بر مبنای لغات کلیدی در SQL Server فراهم می کند . بر خلاف LIKE که محدوده عمل آن روی کاراکترهاست ، Full Text Search بر روی کلمات بر مبنای زبان ِ متن ، پرس و جو انجام می دهد . اختلاف سرعت Full Text Search و LIKE در زمانی که می خواهیم یک Query را بر روی چند میلیون رکورد اجرا کنیم بسیار مشهود است . اگر با LIKE دنبال یک ترکیب بگردیم ، تهیه گزارش ممکن است چندین دقیقه طول بکشد در حالی که استفاده از Full Text Search آن را به چند ثانیه کاهش می دهد. دستور LIKE نمی تواند درون ستون های باینری جستجو کند. query های حاوی LIKE در متن های با حجم بالا بسیار کندتر از Full-Text Search اجرا می شود.
برای جستجو در متنهای حجیم از Full-Text Search استفاده می شود. Full-Text Search از قابلیتهای برجسته SQL Server می باشد. جستجوی یک عبارت در پایگاه داده امری ضروری برای اکثر برنامه‌های تحت وب است. این امر به سادگی با یک کوئری شبیه:

کد:

SELECT * FROM `myTable` WHERE `text` LIKE '%زیتون%'

قابل انجام است.
اما زمانی که تعداد رکوردهای ما زیاد باشد و دیتابیس ما سنگین باشد (مثلا 5 میلیون رکورد در یک جدول داشته باشیم) آنگاه جستجوی یک عبارت ساده نیز بسیار زمان‌بر خواهد بود. برای این منظور از قابلیت Full Text Search در SQL Server استفاده می نماییم که مراحل فعال سازی و انجام تنظیمات آن به شرح ذیل می باشد.
ابتدا یک جدول ایجاد می نماییم و تعدادی رکورد تصادفی در آن درج می نماییم.

کد:

CREATE TABLE [dbo].[tblBooks0](
    [BookIS] [int] IDENTITY(1,1) NOT NULL,
    [BooksTitle] [nvarchar](255) NOT NULL,
CONSTRAINT [PK_tblBooks] PRIMARY KEY CLUSTERED
(
[BookIS] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

در مرحله بعدی نوبت به ایجاد FULLTEXT STOPLIST می باشد. برای این منظور هم می توان از طریق کد نویسی عمل نمود و هم از طریق ویزارد.

کد:

CREATE FULLTEXT STOPLIST StopListName;
GO
مثال:
CREATE FULLTEXT STOPLIST BooksStopList;
GO

پس از ایجاد Full text Stoplist خالی بایستی کلمه هایی که می خواهیم در جستجو اعمال نشوند به لیست اضافه نماییم. برای این منظور نیز دو راه وجود دارد. یکی از طریق کد نویسی SQL و دیگری از طریق ویزاردهای خود برنامه.

شکل کلی دستور برای درج کلمه ای که نمی خواهیم در جستجو مورد توجه قرار گیرد:

کد:

ALTER FULLTEXT STOPLIST [StopListName] ADD 'کلمه مورد نظر' LANGUAGE 'Arabic';

شکل کلی دستور برای حذف یک کلمه که در Stop List از قبل موجود می باشد:

کد:

ALTER FULLTEXT STOPLIST [StopListName] DROP 'کلمه مورد نظر' LANGUAGE 'Arabic';

شکل کلی دستور برای حذف کلیه کلمه های موجود در Stop List:

کد:

ALTER FULLTEXT STOPLIST [StopListName] DROP ALL LANGUAGE 'Arabic';

با استفاده از این دستور کلیه لغتهایی که با زبان عربی در Stop List درج شده اند حذف می شوند.
درج یک سری کلمه در Stop List مربوط به دیتا بیس کتابخانه با عنوان tblBooks0

کد:

ALTER FULLTEXT STOPLIST [BooksStopList] ADD 'به' LANGUAGE 'Arabic';
ALTER FULLTEXT STOPLIST [BooksStopList] ADD 'است' LANGUAGE 'Arabic';
ALTER FULLTEXT STOPLIST [BooksStopList] ADD 'کجا' LANGUAGE 'Arabic';
ALTER FULLTEXT STOPLIST [BooksStopList] ADD 'و' LANGUAGE 'Arabic';
ALTER FULLTEXT STOPLIST [BooksStopList] ADD 'شد' LANGUAGE 'Arabic';

برای مشاهده تعداد Stop List های موجود در یک پایگاه داده از کد SQL زیر استفاده می شود:

کد:

select * from sys.fulltext_stoplists

[ali]

و برای مشاهده کلمه های موجود در Stop List از کد SQL زیر استفاده می شود:

کد:

select * from sys.fulltext_stopwords

در مرحله بعدی نوبت به ایجاد یک Full Text Catalog می باشد. برای این منظور نیز دو راه وجود دارد. از طریق کد نویسی و از طریق ویزارد.

شکل کلی دستور:

کد:

CREATE FULLTEXT CATALOG CatalogName;
GO

مثال:

کد:

CREATE FULLTEXT CATALOG BooksFtCatalog;
GO

پس از ایجاد Full Text Catalog باید یک سری تنظیمات بر روی آن انجام داد.

[ali]

و یا از طریق اسکریپت:

کد:

USE [DbBooks]
GO
CREATE FULLTEXT INDEX ON [dbo].[tblBooks0] KEY INDEX [PK_tblBooks] ON ([BooksFtCatalog]) WITH (CHANGE_TRACKING AUTO)
GO
USE [DbBooks]
GO
ALTER FULLTEXT INDEX ON [dbo].[tblBooks0] ADD ([BooksTitle] LANGUAGE [Arabic])
GO
USE [DbBooks]
GO
ALTER FULLTEXT INDEX ON [dbo].[tblBooks0] ENABLE
GO

و برای تخصیص Stoplist به جدول مورد نظر از کد زیر استفاده می نماییم:

کد:

ALTER FULLTEXT INDEX ON [dbo].[tblBooks0] SET  stoplist = BooksStopList

پس از این مرحله باید مجددا Full Text Catalog را Rebuild نمود تا تغییرات مورد نیاز بر روی جدول اعمال شود.

پس از این مرحله می توان لیستی که توسط SQL Server برای جستجو ایجاد کرده به همراه تعداد نتیجه های بدست آمده با استفاده از کد زیر مشاهده نمود:

کد:

SELECT * FROM sys.dm_fts_index_keywords  
(DB_ID('DbBooks'), OBJECT_ID('tblBooks0')) d
order by d.document_count desc

البته از کلمه هایی که تعداد نتایج بیشتری دارند نیز می توان صرفنظر نمود. (مانند 'های' و 'ها' در لیست بالا)

تفاوت در دو نوع جستجو دستورهای Like و Contains

کد:

select * from tblBooks0 b where b.BooksTitle like '%آکس%'
select * from tblBooks0 b where Contains(b.BooksTitle , '"*آکس*"')

ضمنا با استفاده از کد SQL زیر می توانید کلمه های با نتایج با لاتر را استخراج نموده و آنها را به STOP List WORD اضافه نمایید.

کد:

SELECT *  ,
'ALTER FULLTEXT STOPLIST [BooksStopList] ADD '  
+ '''' + d.display_term + '''' + ' LANGUAGE  ''Arabic'';'
SELECT *  ,
'ALTER FULLTEXT STOPLIST [BooksStopList] ADD '  
+ '''' + d.display_term + '''' + ' LANGUAGE  ''Arabic'';'
FROM sys.dm_fts_index_keywords  
(DB_ID('DbBooks'), OBJECT_ID('tblBooks0')) d
where ISNUMERIC(d.display_term) = 0 and document_count >= 500  
and d.display_term <> 'END OF FILE'
order by d.document_count desc

این کد در حقیقت کد مربوط به درج کلمه های مورد نظر به StoplistWords را تولید می کند. (مثال زیر)

کد:

ALTER FULLTEXT STOPLIST [BooksStopList] ADD 'های' LANGUAGE  'Arabic';
ALTER FULLTEXT STOPLIST [BooksStopList] ADD 'ها' LANGUAGE  'Arabic';
ALTER FULLTEXT STOPLIST [BooksStopList] ADD 'با' LANGUAGE  'Arabic';

پس از درج این کلمه ها که نتایج جستجوی بالایی دارند ، مجددا Catalog را Rebuild می نماییم تا تغییرات مورد نظر در لیست اعمال شود.
برای Rebuild کردن مجدد می توان از اسکریپت زیر استفاده نمود:

کد:

alter fulltext catalog FtName rebuild