php - 优化 MySQL 或 PHP 的搜索功能

Question

运行一些测试后，我意识到，如果查询的某些单词很短(2~3 个字母)，我的搜索方法执行效果不佳。

我进行搜索的方法是对访问者输入的字符串中的每个单词进行 MySQL 查询，然后过滤每个单词的结果以查看是否每个单词都有该结果。一旦所有单词返回一个结果，它就是一个匹配项，并且会向访问者显示该结果。

但我想知道这是否是一种有效的方法。在保持相同功能的同时还有更好的方法吗？

目前，我的代码需要大约 0.7 秒来进行 MySQL 查询。其余的内容都在 0.1 秒以内。通常我不会太在意我的搜索需要 0.7 秒，但我想创建一个“LiveSearch”，并且加载速度比这更快是至关重要的。

这是我的代码

    public static function Search($Query){
        $Querys = explode(' ',$Query);
        foreach($Querys as $Query)
        {
            $MatchingRow = \Database\dbCon::$dbCon -> prepare("
                SELECT
                    `Id`
                FROM
                    `product_products` as pp
                WHERE
                    CONCAT(
                        `Id`,
                        ' ',
                        (SELECT `Name` FROM `product_brands` WHERE `Id` = pp.BrandId),
                        ' ',
                        `ModelNumber`,
                        ' ',
                        `Title`,
                        IF(`isFreeShipping` = 1 OR `isFreeShippingOnOrder` = 1, ' FreeShipping', '')
                    )
                LIKE :Title;
                ");
            $MatchingRow -> bindValue(':Title','%'.$Query.'%');
            try{
                $MatchingRow -> execute();
                foreach($MatchingRow -> fetchAll(\PDO::FETCH_ASSOC) as $QueryInfo)
                    $Matchings[$Query][$QueryInfo['Id']] = $QueryInfo['Id'];
            }catch(\PDOException $e){
                echo 'Error MySQL: '.$e->getMessage();
            }
        }
        $TmpMatch = $Matchings;
        $Matches = array_shift(array_values($TmpMatch));
        foreach($TmpMatch as $Query)
        {
            $Matches = array_intersect($Matches,$Query);
        }
        foreach($Matches as $Match){
            $Products[] = new Product($Match);
        }
        return $Products;
    }

Answer 1

正如其他人已经建议的那样，fulltext search是你的 friend 。

逻辑应该或多或少是这样的。

• 向“product_products”表添加一个名为“FtSearch”的文本列
• 编写一个仅运行一次的小脚本，在其中为每个现有产品编写必须在“FtSearch”列中搜索的文本。当然，这是您在查询中撰写的文本(id + 品牌名称 + 标题等，包括 FreeShipping 部分)。您可能可以使用单个 SQL 语句来完成此操作，但我手头没有 mysql，无法为您提供代码...您也许可以自己找到它。
• 在“FtSearch”列上创建全文索引(在填充 FtSearch 列之后执行此操作可以节省一点执行时间)。
• 现在您必须添加必要的代码，以确保每次插入/更新搜索字符串中涉及的任何字段时，也插入/更新搜索字符串。这里要注意，因为这不仅包括“product_product”的“Title”、“ModelNumber”和“FreeShipping”，还包括“product_brand”的“Name”。这意味着，如果更新了product_brand的名称，您将必须重新生成具有该品牌的所有产品的所有搜索字符串。这可能会有点慢，具体取决于有多少产品绑定(bind)到该品牌。不过，我认为品牌更改名称的情况不会太频繁，即使发生，也肯定会发生在某种管理界面中，这种情况通常是可以接受的。
• 此时，您可以使用 MATCH 构造查询表，这比使用当前方法更快。