آشنایی با Full Text Search در MySQL

یوسف شیری

6 سال پیش

در این آموزش با گونه‌ای از جستجو در پایگاه‌داده MySQL آشنا می‌شویم که امکانات یک موتور جستجوی پیشرفته را در اختیار ما قرار خواهد داد. این قابلیت با نام Full Text Search شناخته می‌شود. پایگاه‌داده MySQL توانایی جستجوی متن به کمک Like یا Regular Expression را دارد. اما زمانی که منبع مورد جستجو، رکوردهای بسیار زیادی باشد و یا متن موجود در ستون مورد نظر طولانی باشد، استفاده از این قابلیت‌ها برای ما محدودیت‌هایی را به وجود خواهد آورد. این محدودیت‌ها عبارتند از:

Performance یا عملکرد: زمانی که با عملگر Like یا Regular Expression متنی را در جدول جستجو کنیم، MySQL تمام رکوردهای جدول را بررسی می‌کند تا عین عبارتی که نیاز داریم را پیدا کند. این کار زمان‌بر است و کارایی و سرعت را کاهش می‌دهد.
Flexible Search یا انعطاف در جستجو: با عملگر Like یا Regular Expression داشتن یک query منعطف برای جستجو بسیار سخت است. فرض کنید می‌خواهید توضیحات محصولاتی را استخراج کنید که در آن‌ها عبارت Car وجود داشته باشد و عبارت classic نباشد.
Relevant Ranking یا رتبه‌بندی بر اساس مرتبط بودن: با امکانات فوق هیچ راهی ندارید تا رکورد‌های جواب دریافتی از پایگاه‌داده را بر اساس مرتبط‌بودن با query مرتب کنید.

به دلیل وجود محدودیت‌های فوق، MySQL یک قابلیت بسیار عالی را به خود افزوده که به نام Full Text Search معروف است. از نظر فنی، زمانی که این قابلیت بر روی ستونی از جدول اعمال می‌شود، MySQL متون موجود در این ستون‌ها را به کلمات خرد می‌کند و برای هر کدام یک آدرس در نظر می‌گیرد. تمام این آدرس‌ها در محلی به نام index نگهداری خواهند شد. البته متن‌ها بصورت دست نخورده در ستون‌ها باقی خواهند ماند. اما زمانی که به کمک Full Text Search در جدول جستجو می‌کنیم، این جستجو ابتدا بر روی index انجام می‌شود. در این وضعیت، MySQL با یک الگوریتم پیچیده، تعیین می‌کند که چه رکوردهایی با query خواسته شده همخوانی دارند.

برخی از ویژگی‌های مهم Full Text Search به قرار زیر است:

Native SQL-like interface: خواهید دید که query هایی که برای جستجو می‌سازیم بسیار شبیه query های عادی زبان SQL‌ است.
Fully dynamic index: در ستون‌هایی که قابلیت Full Text search فعال است، با تغییر داده در هر رکورد، index بصورت خودکار بروز رسانی خواهد شد.
Moderate index size: حافظه‌ی زیادی برای نگهداری index مصرف نخواهد شد.
پیچیده‌ترین query‌های جستجو، به سرعت اجرا خواهند شد.

نکته‌ای که باید به آن توجه کنید این است که تمام موتورهای ذخیره‌سازی MySQL از قابلیت Full Text Search پشتیبانی نمی‌کنند. از نسخه ۵.۶ به بعد، تنها موتورهای ذخیره‌سازی MyISAM و InnoDB از این قابلیت پشتیبانی می‌کنند.

تعریف قابلیت Full Text Index برای ستون‌های جدول:

قبل از اینکه عملیات Full Text Search را بر روی ستونی از جدول انجام دهید، باید داده‌های این ستون را فهرست‌سازی(index) کنید. دقت کنید که نوع داده ستون باید حتما یکی از انواع CHAR، VARCHAR و یا Text باشد. برای تعریف Full Text Index می‌تونید در زمان ایجاد جدول با دستور Create Table و یا در هنگام تغییر جدول با دستور Alter Table این امکان را به جدول اضافه کنید.

افزودن قابلیت Full Text Search به جدول در زمان ساخت آن

به طور معمول، شما با کمک FULLTEXT می‌توانید برای ستون‌های مورد نیاز خود index تعریف کنید. مثال زیر نحوه‌ی ساخت index را در یک جدول فرضی نمایش می‌دهد:


CREATE TABLE posts (
id int(4) NOT NULL AUTO_INCREMENT,
title varchar(255) NOT NULL,
post_content text,
PRIMARY KEY (id),
FULLTEXT KEY post_content (post_content)
);

برای ایجاد فهرست FULLTEXT، شما یک لیست از ستون‌ها را به دستور FULLTEXT می‌دهید که این ستون‌ها با کاراکتر کاما از هم جدا شده‌اند.

افزودن قابلیت FULLTEXT به جدول از پیش ساخته شده

نمونه کد زیر قابلیت FULLTEXT را به ستون‌های productDescription و productLine از جدولی با نام فرضی products که از پیش ساخته شده است، اضافه می‌کند.


ALTER TABLE products
ADD FULLTEXT(productDescription,productLine)

افزودن قابلیت FULLTEXT به کمک دستور CREATE INDEX

علاوه بر دستور FULLTEXT می‌توان از دستور CREATE INDEX نیز برای افزودن قابلیت Full Text Search به جدول، استفاده کرد.


CREATE FULLTEXT INDEX index_name
ON table_name(idx_column_name,...)

به این نکته توجه داشته باشید که بارگذاری حجم بالایی از اطلاعات در جدولی که قابلیت FULLTEXT ندارد سریعتر از جدولیست که این قابلیت را بر روی خود فعال کرده است.

حذف امکان Full Text Search از جدول

برای حذف این قابلیت تنها کافیست که فهرست را به کمک دستور DROP INDEX حذف کنیم. نمونه کد زیر امکان FULLTEXT را از ستون address در جدول فرضی offices حذف می‌کند.


ALTER TABLE offices
DROP INDEX address;

تا به آینجا در مورد نحوه کارکرد Full Text Search و همچنین افزودن این قابلیت به پایگاه داده‌های MySQL صحبت کردیم. در مطلبی دیگر نحوه پرس و جو از پایگاه داده به کمک این امکان صحبت خواهیم کرد. همچنین مستندات MySQL برای امکان Full Text Search نیز از طریق لینک زیر در دسترس است:

[button color=”blue” size=”medium” link=”https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html” target=”blank” ]مستندات MySQL[/button]

موفق و پیروز باشید…