Android应用大数据搜索优化：PHP+MySQL快速自动补全实现问题

我明白你的痛点——用LIKE '%...%'做模糊搜索慢得让人抓狂，而默认的全文搜索又只能匹配完整词，没法实现自动补全的前缀/部分匹配。下面给你几个靠谱的优化方案，既能保持搜索速度，又能满足自动补全的需求：

方案1：MySQL全文搜索布尔模式+前缀匹配 #

默认的全文搜索是自然语言模式，只会匹配完整词汇，但切换到布尔模式后，我们可以用*实现前缀匹配，同时利用全文索引的速度优势。

第一步：确保字段有全文索引 #

如果还没给name字段创建全文索引，先执行这条SQL：

ALTER TABLE goods ADD FULLTEXT INDEX idx_fulltext_name (name);

第二步：修改PHP查询代码 #

注意！一定要先做SQL注入防护，直接拼接用户输入非常危险：

<?php
include_once ('db.php');
$search = $_POST['test'];
// 转义用户输入，防止SQL注入
$search = mysqli_real_escape_string($con, $search);

if (mysqli_connect_errno()) {
    echo "Connection failed";
} else {
    $con->query("SET NAMES utf8");
    // 布尔模式+前缀匹配：搜索词后加*，匹配所有以该词开头的内容
    $q = mysqli_query($con, "SELECT * FROM goods WHERE MATCH(name) AGAINST('$search*' IN BOOLEAN MODE)");
    $arr = array();
    while($row = mysqli_fetch_assoc($q)) {
        $arr[] = $row;
    }
    echo json_encode($arr);
}
?>

这个方案能快速匹配前缀一致的内容（比如输入app会匹配apple、application），但无法匹配中间包含搜索词的内容（比如pineapple里的app）。

方案2：前缀索引+LIKE 'xxx%' #

如果你的自动补全只需要前缀匹配（比如用户输入“苹”，要匹配“苹果”“苹果手机”），前缀索引是最简单高效的选择。

第一步：创建前缀索引 #

指定截取字段前N个字符创建索引（N的长度根据你的业务调整，比如中文可以设10）：

ALTER TABLE goods ADD INDEX idx_prefix_name (name(10));

第二步：修改查询代码 #

用LIKE '$search%'（注意只有后缀加%），这样索引会生效，速度和全文搜索差不多：

<?php
include_once ('db.php');
$search = $_POST['test'];
$search = mysqli_real_escape_string($con, $search);

if (mysqli_connect_errno()) {
    echo "Connection failed";
} else {
    $con->query("SET NAMES utf8");
    // 前缀匹配，索引会生效，速度快
    $q = mysqli_query($con, "SELECT * FROM goods WHERE name LIKE '$search%'");
    $arr = array();
    while($row = mysqli_fetch_assoc($q)) {
        $arr[] = $row;
    }
    echo json_encode($arr);
}
?>

方案3：ngram全文解析器（适合中文场景） #

如果你的商品名是中文，默认全文搜索对中文支持极差（因为中文没有空格分词），这时候可以用MySQL的ngram解析器，它会把中文拆分成连续的字符片段，实现灵活的部分匹配。

第一步：创建带ngram解析器的全文索引 #

ALTER TABLE goods ADD FULLTEXT INDEX idx_fulltext_name_ngram (name) WITH PARSER ngram;

第二步：修改查询代码 #

<?php
include_once ('db.php');
$search = $_POST['test'];
$search = mysqli_real_escape_string($con, $search);

if (mysqli_connect_errno()) {
    echo "Connection failed";
} else {
    $con->query("SET NAMES utf8mb4"); // 建议用utf8mb4支持更多特殊字符
    // 用ngram解析器的全文搜索，支持中文部分匹配
    $q = mysqli_query($con, "SELECT * FROM goods WHERE MATCH(name) AGAINST('$search' IN NATURAL LANGUAGE MODE)");
    // 也可以加前缀：AGAINST('$search*' IN BOOLEAN MODE)
    $arr = array();
    while($row = mysqli_fetch_assoc($q)) {
        $arr[] = $row;
    }
    echo json_encode($arr);
}
?>

关键提醒：SQL注入防护 #

你原来的代码直接把用户输入拼进SQL，存在严重的注入风险！更推荐用预处理语句（Prepared Statements），安全性更高：

<?php
include_once ('db.php');
$search = $_POST['test'];

if (mysqli_connect_errno()) {
    echo "Connection failed";
} else {
    $con->query("SET NAMES utf8");
    // 预处理语句，避免SQL注入
    $stmt = $con->prepare("SELECT * FROM goods WHERE MATCH(name) AGAINST(? IN BOOLEAN MODE)");
    $search_with_prefix = $search . '*';
    $stmt->bind_param("s", $search_with_prefix);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $arr = array();
    while($row = $result->fetch_assoc()) {
        $arr[] = $row;
    }
    echo json_encode($arr);
}
?>

内容的提问来源于stack exchange，提问作者raza Amini