جستجوی فارسی در MySQL: چرا FULLTEXT جواب نمی‌دهد و نیم‌فاصله همه چیز را خراب می‌کند

جستجوی فارسی یکی از آن مسائلی است که تا وقتی خودتان پیاده نکرده‌اید ساده به نظر می‌رسد. کاربر یک کلمه تایپ می‌کند، شما با LIKE می‌گردید، تمام. تا وقتی که یک نفر بنویسد «می‌شود» و شما در دیتابیس «میشود» ذخیره کرده باشید و هیچ نتیجه‌ای برنگردد.

این نوشته الگویی است که برای جستجوی این سایت ساختیم، به‌علاوه‌ی یک اشتباه تشخیصی که دو ساعت وقت گرفت و درس اصلی همان است.

مسئله: یک کلمه، چند نگارش#

فارسی چند ویژگی دارد که برای پایگاه داده دردسر است.

نیم‌فاصله#

کاربر «می‌شود» را ممکن است سه شکل تایپ کند:

• می‌شود با نیم‌فاصله — کاراکتر U+200C
• میشود بدون هیچ فاصله‌ای
• می شود با فاصله‌ی معمولی

برای MySQL این‌ها سه رشته‌ی کاملاً متفاوت‌اند.

حروف عربی#

کیبوردهای مختلف حروف متفاوتی تولید می‌کنند:

خیلی از کاربران اصلاً نمی‌دانند کیبوردشان کدام را می‌فرستد.

اعداد#

۱۴۰۵ و 1405 برای انسان یکی‌اند و برای پایگاه داده نه. علاوه بر این، ارقام عربی هم شکل سومی دارند.

اعراب و کاراکترهای نامرئی#

فتحه و کسره و تشدید ممکن است در متن باشند یا نباشند. کاراکترهای کنترل جهت متن هم گاهی از کپی و پیست وارد می‌شوند و کاملاً نامرئی‌اند.

چرا FULLTEXT راه‌حل نیست#

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

۱. حداقل طول واژه. موتور InnoDB پیش‌فرض واژه‌های کوتاه‌تر از سه کاراکتر را ایندکس نمی‌کند:

SHOW VARIABLES LIKE 'innodb_ft_min_token_size';

روی هاست اشتراکی این متغیر را نمی‌توانید عوض کنید — تغییرش نیاز به دسترسی سراسری و ری‌استارت سرویس دارد. یعنی واژه‌های کوتاه فارسی برای همیشه از جستجو می‌افتند.

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

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

نتیجه‌گیری ما: هزینه‌اش کامل بود و مزیتش نزدیک صفر. پس کنارش گذاشتیم.

راه‌حل: دو لایه‌ی متن#

ایده ساده است. متن را در دو شکل نگه دارید:

• ستون نمایشی که دقیقاً همان چیزی است که کاربر نوشته، با نیم‌فاصله‌ی سالم.
• ستون قابل جستجو که همه چیز در آن یکدست شده: نیم‌فاصله حذف، حروف عربی به فارسی، ارقام به لاتین، اعراب حذف.

چرا دو ستون و نه یکی؟ چون حذف نیم‌فاصله املا را خراب می‌کند. اگر متن نمایشی را هم نرمال کنید، کاربر «میشود» می‌بیند که غلط است. و اگر متن جستجو را نرمال نکنید، تطبیق کار نمی‌کند. این دو هدف با هم جمع نمی‌شوند، پس دو ستون.

نرمال‌ساز#

یک کلاس کوچک با سه متد و سه هدف متفاوت:

class Persian
{
    // برای نمایش: حروف را یکدست می‌کند ولی نیم‌فاصله را نگه می‌دارد
    public static function normalize(?string $text): string
    {
        $text = str_replace(['ي', 'ك', 'ة'], ['ی', 'ک', 'ه'], (string) $text);
        $text = preg_replace('/[\x{064B}-\x{0652}]/u', '', $text);

        return trim(preg_replace('/[ \t]+/u', ' ', $text));
    }

    // برای تطبیق: نیم‌فاصله هم حذف می‌شود
    public static function searchable(?string $text): string
    {
        $text = self::normalize($text);
        $text = str_replace("\u{200C}", '', $text);
        $text = strtr($text, ['۰' => '0', '۱' => '1', '۲' => '2', '۳' => '3', '۴' => '4',
                              '۵' => '5', '۶' => '6', '۷' => '7', '۸' => '8', '۹' => '9']);

        return mb_strtolower($text);
    }
}

نکته‌ی مهم: همان تابعی که ستون را پر می‌کند باید عبارت جستجوی کاربر را هم پردازش کند. اگر این دو با هم نخوانند، هیچ‌وقت چیزی پیدا نمی‌شود.

در مدل#

public function scopeSearch(Builder $query, ?string $term): Builder
{
    $term = Persian::searchable($term);

    if ($term === '') {
        return $query;
    }

    return $query->where(function (Builder $q) use ($term) {
        $q->where('searchable', 'like', "%{$term}%")
          ->orWhere('search_body', 'like', "%{$term}%");
    });
}

و ستون‌ها موقع ذخیره پر می‌شوند، نه موقع خواندن. رندر یک بار انجام می‌شود و صفحه‌ی عمومی هیچ پردازشی نمی‌کند.

اشتباهی که کردیم و درس اصلی همین است#

وقتی جستجو کار نمی‌کرد، گزارش این بود: «جستجو با نیم‌فاصله کار نمی‌کند.»

دو ساعت روی نرمال‌سازی نیم‌فاصله وقت گذاشتیم. تست نوشتیم، خروجی را چاپ کردیم، همه چیز درست بود. «می‌زنید» به درستی به «میزنید» تبدیل می‌شد.

علت واقعی جای دیگری بود: ستون قابل جستجو فقط از عنوان و خلاصه پر می‌شد، نه از متن مقاله. هر واژه‌ای که فقط در متن بود و در عنوان نیامده بود، اصلاً در هیچ ستون قابل جستجویی وجود نداشت. نیم‌فاصله از روز اول سالم بود.

قاعده‌ای که از این ماجرا ماند: وقتی علامت و علت نمی‌خوانند، اول فرض کنید علامت گمراه‌کننده است. کاربر گزارش می‌دهد چه دیده، نه چه اتفاقی افتاده. دو ساعت صرف بررسی چیزی شد که از اول سالم بود.

راه تشخیص درست هم ساده بود و زودتر باید انجام می‌شد: به‌جای حدس‌زدن، مستقیم به خود ستون نگاه کنید:

SELECT id, LEFT(searchable, 120) FROM articles WHERE id = 5;

اگر متن مقاله آن‌جا نیست، مشکل نرمال‌سازی نیست.

ستون متن کامل و یک تله‌ی حذف نرم#

برای جستجو در متن، یک ستون جدا از نوع mediumText اضافه کردیم که از HTML رندرشده پر می‌شود:

public static function searchText(?string $html): string
{
    $text = strip_tags((string) $html);
    $text = html_entity_decode($text, ENT_QUOTES | ENT_HTML5, 'UTF-8');

    return Persian::searchable($text);
}

چرا از HTML رندرشده و نه از متن خام؟ چون strip_tags تگ را با همه‌ی ویژگی‌هایش برمی‌دارد، پس آدرس تصویرها و لینک‌ها — که نویز جستجو هستند — خودکار حذف می‌شوند، ولی متن داخل بلوک کد می‌ماند که در یک بلاگ فنی محتوای واقعی است.

ترتیب مهم است: اول strip_tags بعد رمزگشایی موجودیت‌ها. برعکسش یعنی یک تگ که داخل بلوک کد نوشته شده اول به تگ زنده تبدیل و بعد حذف می‌شود.

و یک تله که یک بار خوردیم: وقتی ستون تازه‌ای اضافه می‌کنید و برای ردیف‌های قدیمی پرش می‌کنید، ردیف‌های حذف‌نرم را فراموش نکنید. یک مقاله‌ی حذف‌شده در بک‌فیل جا افتاد و وقتی بازیابی شد، بدون متن قابل جستجو برگشت — چون بازیابی از مسیر ذخیره‌ی معمولی رد نمی‌شود.

Article::withTrashed()->chunkById(100, function ($rows) {
    // ...
});

هزینه‌ی کارایی را بشناسید#

الگوی LIKE '%عبارت%' ایندکس را بی‌اثر می‌کند. وایلدکارت ابتدایی یعنی پایگاه داده نمی‌داند از کجای ایندکس شروع کند، پس کل جدول را می‌خواند.

با EXPLAIN ببینیدش:

EXPLAIN SELECT * FROM articles WHERE searchable LIKE '%تست%';

اگر key برابر NULL بود یعنی ایندکسی استفاده نشده. برای چند هزار ردیف کاملاً قابل قبول است. با رشد داده باید سراغ راه دیگری بروید.

نکته‌ی مرتبط: اگر ایندکسی روی این ستون دارید که هیچ‌وقت استفاده نمی‌شود، آن ایندکس فقط طول ستون شما را محدود کرده. این‌جا توضیح داده‌ایم که چرا گاهی راه‌حل درست برداشتن ایندکس است نه کوتاه‌کردن ستون.

رتبه‌بندی ساده بدون موتور جستجو#

اگر می‌خواهید نتیجه‌ی عنوان بالاتر از نتیجه‌ی متن بیاید، لازم نیست موتور جستجو بیاورید:

$query->orderByRaw(
    'CASE WHEN searchable LIKE ? THEN 0 ELSE 1 END',
    ["%{$term}%"]
);

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

مرتب‌سازی فارسی، مسئله‌ی جداگانه‌ای است#

جستجو یک بحث است و مرتب‌سازی الفبایی بحث دیگر. اگر فهرستی از نام‌های فارسی را با ORDER BY مرتب کنید، ترتیب معمولاً درست از آب درنمی‌آید — چون مرتب‌سازی بر اساس مقدار عددی کاراکترهاست، نه ترتیب الفبای فارسی.

دو نکته‌ی عملی:

۱. حروف عربی و فارسی مقدار عددی متفاوتی دارند. پس «کتاب» با کاف فارسی و «كتاب» با کاف عربی در دو جای کاملاً متفاوت فهرست می‌نشینند. اگر داده را با همان نرمال‌ساز بالا یکدست کرده باشید، این مشکل خودبه‌خود حل می‌شود.

۲. برای ترتیب دقیق الفبایی، مرتب‌سازی را در PHP انجام دهید. اگر افزونه‌ی intl در دسترس باشد:

$collator = new Collator('fa_IR');
$collator->sort($names);

اگر نبود — که روی هاست اشتراکی محتمل است — می‌توانید یک ستون کمکی بسازید که در آن حروف به ترتیب دلخواه شما بازنویسی شده‌اند. برای فهرست‌های کوچک ارزشش را دارد؛ برای فهرست‌های بزرگ باید هزینه‌اش را بسنجید.

نکته‌ی مهم درباره‌ی طول ستون#

ستونی که با نرمال‌سازی پر می‌شود از عنوان و متن مقاله می‌آید، پس طولش قابل پیش‌بینی نیست. اگر varchar(191) بگذارید — عدد آشنایی که برای جا شدن در ایندکس انتخاب می‌شود — به‌محض اینکه مقاله‌ای با خلاصه‌ی بلند بیاید خطا می‌گیرید.

ما دقیقاً همین را خوردیم و نتیجه‌اش خطای ۱۴۰۶ و یک صفحه‌ی ۵۰۰ در پنل بود. شرح کامل و درسش این‌جاست، ولی خلاصه‌اش این است: چون ایندکس روی این ستون با الگوی جستجوی ما هیچ‌وقت استفاده نمی‌شد، ایندکس را برداشتیم و ستون را بزرگ کردیم — نه برعکس.

نرمال‌سازی برای نامک هم لازم است، ولی متفاوت#

اگر آدرس صفحه‌هایتان فارسی است، یک لایه‌ی سوم هم می‌خواهید: نامک.

قواعدش با دو تای قبلی فرق دارد:

• نیم‌فاصله باید به خط تیره تبدیل شود، نه حذف. می‌شود باید می-شود بشود نه میشود.
• فاصله هم خط تیره می‌شود.
• کاراکترهای غیرمجاز آدرس حذف می‌شوند.
• خط تیره‌های پشت سر هم یکی می‌شوند.

تابع نامک‌ساز پیش‌فرض لاراول برای فارسی جواب نمی‌دهد — حروف فارسی را کاملاً حذف می‌کند و شما یک رشته‌ی خالی می‌گیرید. باید تابع خودتان را بنویسید:

public static function slug(?string $text, int $limit = 180): string
{
    $text = self::normalize($text);
    $text = str_replace("\u{200C}", '-', $text);
    $text = preg_replace('/[^\p{L}\p{N}]+/u', '-', $text);
    $text = trim(preg_replace('/-+/', '-', $text), '-');

    return mb_substr(mb_strtolower($text), 0, $limit);
}

نکته‌ی مهم: نامک فارسی در آدرس کاملاً معتبر است و مرورگرها آن را رمزگذاری می‌کنند. در نقشه‌ی سایت و پیوندهای متعارف حتماً شکل رمزگذاری‌شده را بنویسید، چون ابزارهای اعتبارسنجی روی شکل خام سخت‌گیرند.

و برای نمایش نامک فارسی در رابط کاربری، این قاعده‌ی CSS جهت متن را درست می‌کند:

.slug { unicode-bidi: plaintext; }

بدون آن، نامکی که هم فارسی دارد هم انگلیسی، در نمایش تکه‌تکه و جابه‌جا دیده می‌شود، در حالی که مقدار واقعی سالم است.

چک‌لیست#

• دو لایه‌ی متن داشته باشید: نمایشی با نیم‌فاصله، جستجویی بدون آن.
• عبارت کاربر را با همان تابعی نرمال کنید که ستون را ساخته.
• حروف عربی، اعراب و ارقام را هم یکدست کنید، فقط نیم‌فاصله کافی نیست.
• ستون‌های مشتق را فقط از یک مسیر پر کنید و بک‌فیل را با ردیف‌های حذف‌نرم بزنید.
• قبل از عیب‌یابی نرمال‌سازی، یک بار محتوای واقعی ستون را نگاه کنید.