MySQL双名字列含关键词数据最优匹配方案及性能咨询

我的表包含两个name字段（name1、name2），需要根据输入的关键词，将包含该关键词的数据按相似度从高到低排序输出。例如输入ed时，期望输出顺序为ed、Ed Sheeran、Ahmedzidan（后两者顺序可随匹配方式调整），要求完全匹配ed的结果排在首位。

我尝试了两种SQL实现方式，但结果均不符合预期，也不清楚哪种性能更优。此外，我试过使用全文索引，但单字符搜索代价过高，长关键词搜索速度极慢（如搜索ed耗时0.2s，搜索ed Sheeran耗时5s）。现咨询：

1. 实现该需求的最优方案是什么？
2. 若仅从上述两种方法中选择，哪种性能更快？

尝试的两种SQL实现 #

第一种方法 #

select
    ((LENGTH(name1) - LENGTH('ed'))) + ((LENGTH(name) - LENGTH('ed'))) as score
from user
where name like '%ed%' or name2 like '%ed%'
order by score asc

注：原SQL存在字段名错误（name应为name2），且得分计算逻辑无法区分完全匹配与包含匹配的优先级，导致结果不符合预期。

第二种方法 #

select
    (CASE WHEN name1 = 'ed' or name2 = 'ed' THEN 4
        WHEN name1 like 'ed%' or name2 like 'ed%' THEN 3
        WHEN name1 like '%ed' or name2 like '%ed' THEN 2
        WHEN name1 like '%ed%' or name2 like '%ed%' THEN 1
        END
    ) as score
from user
where name like '%ed%' or name2 like '%ed%'
order by score desc

注：原SQL的WHERE条件中name应为name1，逻辑上能区分匹配优先级，但未处理字段名错误，且未考虑两个字段同时匹配的情况，可能导致结果不符合预期。

一、两种方法的性能对比 #

第二种方法性能更快，原因如下：

• 第一种方法需要对每行计算两次字符串长度差并求和，涉及多次字符串长度计算，CPU开销更高。
• 第二种方法仅通过CASE语句做条件判断，都是简单的等值/前缀/后缀/包含匹配，逻辑简洁，CPU计算量更小。
  另外，两种方法的WHERE子句都使用%xxx%模糊匹配，无法利用普通索引，都会触发全表扫描，但第二种方法的排序阶段计算量更低，整体性能更优。

二、最优实现方案 #

要满足需求且兼顾性能，可从以下方向优化：

1. 修正CASE逻辑，确保结果符合预期 #

先修正字段名错误，同时细化匹配规则，处理双字段同时匹配的情况，强化优先级：

SELECT 
    name1, name2,
    CASE
        -- 完全匹配任意字段，优先级最高
        WHEN name1 = 'ed' OR name2 = 'ed' THEN 4
        -- 前缀匹配任意字段
        WHEN name1 LIKE 'ed%' OR name2 LIKE 'ed%' THEN 3
        -- 后缀匹配任意字段
        WHEN name1 LIKE '%ed' OR name2 LIKE '%ed' THEN 2
        -- 包含匹配任意字段
        WHEN name1 LIKE '%ed%' OR name2 LIKE '%ed%' THEN 1
    END AS score
FROM user
WHERE name1 LIKE '%ed%' OR name2 LIKE '%ed%'
ORDER BY score DESC, 
         -- 同得分下，完全匹配的行优先；非完全匹配则按合并字段长度短的优先（更接近关键词）
         CASE WHEN name1 = 'ed' OR name2 = 'ed' THEN 0 ELSE 1 END,
         LENGTH(CONCAT(name1, name2)) ASC;

2. 优化性能：避免全表扫描 #

%xxx%模糊匹配无法利用普通索引，可尝试以下方案：

• 前缀+反向索引：给name1、name2加前缀索引支持ed%匹配；新增反向字段（如reverse_name1、reverse_name2）并加前缀索引，将name1 LIKE '%ed'转换为reverse_name1 LIKE CONCAT(REVERSE('ed'), '%')。
• 优化全文索引：以MySQL为例，调整ft_min_word_len参数降低最小索引词长度（默认4，可改为1），但会增加索引体积；长关键词搜索用布尔模式精确匹配，比如MATCH(name1, name2) AGAINST('"ed Sheeran"' IN BOOLEAN MODE)。
• 引入外部搜索引擎：数据量较大（百万级以上）时，建议用Elasticsearch等专门工具，它能高效处理模糊匹配、相似度排序，支持自定义评分规则，性能远优于数据库模糊查询。

3. 相似度排序的补充优化 #

如果需要更精准的相似度排序（如基于编辑距离），可使用数据库内置函数（如MySQL的LEVENSHTEIN()，需安装UDF），计算关键词与字段值的编辑距离，距离越小相似度越高：

SELECT 
    name1, name2,
    -- 优先完全匹配，再按编辑距离排序
    CASE WHEN name1 = 'ed' OR name2 = 'ed' THEN 0 ELSE LEVENSHTEIN('ed', COALESCE(name1, name2)) END AS similarity
FROM user
WHERE name1 LIKE '%ed%' OR name2 LIKE '%ed%'
ORDER BY similarity ASC;

注意：编辑距离计算CPU开销较高，数据量大时不建议直接使用，结合外部搜索引擎更合适。

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