如何优化多条件LIKE联合MySQL查询性能并实现指定排序规则？

阿华AIGC实验室

2026-4-27

优化MySQL查询性能并实现指定排序的方案

嘿，针对你的查询需求，我整理了几个关键的优化点和排序实现方式，帮你提升性能同时满足排序要求：

一、先解决排序问题：按指定优先级排序

你的原查询用UNION合并结果，但没法保证按(leo, le, eo, l, e, o)的顺序返回，因为UNION的结果顺序是不确定的。我们可以给每个匹配条件标记优先级权重，然后按权重排序：

重构查询（替代多个UNION）

用CASE WHEN在单表查询中直接标记优先级，避免多次扫描表：

SELECT DISTINCT u.*,
  CASE
    WHEN name LIKE '%leo%' THEN 1  -- 最高优先级
    WHEN name LIKE '%le%' THEN 2
    WHEN name LIKE '%eo%' THEN 3
    WHEN name LIKE '%l%' THEN 4
    WHEN name LIKE '%e%' THEN 5
    WHEN name LIKE '%o%' THEN 6  -- 最低优先级
  END AS priority
FROM users u
WHERE 
  name LIKE '%leo%' 
  OR name LIKE '%le%' 
  OR name LIKE '%eo%' 
  OR name LIKE '%l%' 
  OR name LIKE '%e%' 
  OR name LIKE '%o%'
ORDER BY priority ASC  -- 按优先级升序，即你要的顺序
LIMIT 5 OFFSET 5;

这样只需要扫描一次表，比原查询多次UNION的效率高很多，同时DISTINCT保证结果不重复（和原UNION的去重效果一致）。

二、性能优化：解决模糊查询的索引失效问题

原查询中USE INDEX(names)其实是无效的——因为LIKE '%xxx%'是前缀模糊匹配，普通的B-tree索引（比如names索引）无法被利用，MySQL会做全表扫描，数据量大时速度会很慢。这里有两个优化方向：

方案1：改用全文索引（推荐）

如果你的MySQL版本支持（InnoDB从5.6开始支持全文索引），可以给name字段创建全文索引：

-- 创建全文索引
ALTER TABLE users ADD FULLTEXT INDEX ft_idx_name (name);

然后用全文搜索替代LIKE，性能会大幅提升：

SELECT u.*,
  CASE
    WHEN MATCH(name) AGAINST('"leo"' IN BOOLEAN MODE) THEN 1
    WHEN MATCH(name) AGAINST('"le"' IN BOOLEAN MODE) THEN 2
    WHEN MATCH(name) AGAINST('"eo"' IN BOOLEAN MODE) THEN 3
    WHEN MATCH(name) AGAINST('"l"' IN BOOLEAN MODE) THEN 4
    WHEN MATCH(name) AGAINST('"e"' IN BOOLEAN MODE) THEN 5
    WHEN MATCH(name) AGAINST('"o"' IN BOOLEAN MODE) THEN 6
  END AS priority
FROM users u
WHERE MATCH(name) AGAINST('leo le eo l e o' IN BOOLEAN MODE)
ORDER BY priority ASC
LIMIT 5 OFFSET 5;

注意：默认情况下，InnoDB的全文索引最小词长是4，单个字符l/e/o可能无法被索引。你需要修改配置文件：
对于InnoDB：设置innodb_ft_min_token_size=1
对于MyISAM：设置ft_min_word_len=1
修改后重启MySQL，然后重建全文索引。