MySQL不使用全文搜索按优先级排序查询过慢如何优化？

1 先消除现有SQL的冗余开销 #

• 去掉重复的优先级计算：你现在的优先级得分在SELECT和WHERE里各算一遍，相当于每行多做了20次LIKE匹配，抽成子查询只计算1次即可
• 把UNION改为UNION ALL：你两个UNION分支的结果结构完全不重叠（一个分支activityActionId有值、creatorFeedId为空，另一个反过来），UNION默认的全局去重逻辑完全多余，替换后可直接省去大量排序去重开销
• 去掉不必要的DISTINCT：只要activity.actionId和action.id是主键/外键关联，不存在重复行，DISTINCT的排序开销可直接砍掉
• 过滤停用词：你当前匹配的关键词里包含the、for这类高频无意义停用词，几乎每条内容都会命中，既不影响优先级排序有效性，还增加了冗余计算，直接删掉这些停用词可减少30%以上的匹配计算量

2 替换全表LIKE的低效匹配逻辑 #

你当前的LIKE '%xxx%'属于前导通配符匹配，完全无法命中索引，每次查询都要扫描action表的所有行逐字段匹配，数据量过万后性能会指数级下降，无法用内置全文索引的前提下可以自己构建轻量倒排索引：

• 新增action_keywords索引表，字段设为action_id（关联action表主键）、keyword（存储拆分后的单词），给keyword字段加普通索引
• 业务侧在新增/修改action.data内容时，提前把内容拆分单词、过滤停用词后写入这个索引表，不需要查询时临时拆分
• 查询时直接关联该索引表，统计匹配到的关键词数量作为优先级，走索引查询的性能比全表LIKE高10~100倍

3 新增覆盖索引减少回表开销 #

给相关表加针对性的联合索引，避免查询时回表扫全表数据：

• activity表加联合索引：INDEX idx_act_action_time (actionId, timeStamp)，关联action表和取时间戳排序时可直接走索引返回数据
• activity表加第二组联合索引：INDEX idx_act_user_type (userId, activityType, actionId)，适配你UNION第二部分的查询条件，这部分查询可直接走索引拿到结果，不需要扫全表

优化后的过渡版本SQL（无需修改表结构的前提下直接生效） #

SELECT b.activityActionId,b.creatorFeedId,b.priority
FROM (
    -- 匹配关键词的查询分支，优先级仅计算1次
    SELECT 
        activity.actionId as activityActionId, 
        NULL as creatorFeedId, 
        activity.timeStamp, 
        t.priority
    FROM `activity` activity    
    INNER JOIN (
        SELECT 
            id,
            (CASE WHEN data like '%you%' THEN 1 ELSE 0 END)
            +(CASE WHEN data like '%don%' THEN 1 ELSE 0 END)
            +(CASE WHEN data like '%see%' THEN 1 ELSE 0 END)
            +(CASE WHEN data like '%this%' THEN 1 ELSE 0 END)
            +(CASE WHEN data like '%email%' THEN 1 ELSE 0 END)
            +(CASE WHEN data like '%your%' THEN 1 ELSE 0 END)
            +(CASE WHEN data like '%inbox%' THEN 1 ELSE 0 END)
            +(CASE WHEN data like '%within%' THEN 1 ELSE 0 END)
            +(CASE WHEN data like '%minutes%' THEN 1 ELSE 0 END)
            +(CASE WHEN data like '%look%' THEN 1 ELSE 0 END)
            +(CASE WHEN data like '%junk%' THEN 1 ELSE 0 END)
            +(CASE WHEN data like '%mail%' THEN 1 ELSE 0 END)
            +(CASE WHEN data like '%folder%' THEN 1 ELSE 0 END)
            +(CASE WHEN data like '%find%' THEN 1 ELSE 0 END)
            +(CASE WHEN data like '%there%' THEN 1 ELSE 0 END)
            +(CASE WHEN data like '%please%' THEN 1 ELSE 0 END)
            +(CASE WHEN data like '%mark%' THEN 1 ELSE 0 END)
            +(CASE WHEN data like '%Not%' THEN 1 ELSE 0 END)
            +(CASE WHEN data like '%Junk%' THEN 1 ELSE 0 END) as priority
        FROM `action`
        WHERE id != "56"
    ) t ON t.id = activity.actionId
    WHERE t.priority > 0
    UNION ALL
    -- 用户创建内容的查询分支
    SELECT 
        NULL as actionId, 
        `actionId`, 
        null as timeStamp, 
        null as priority    
    FROM `activity`             
    WHERE `activityType` = "create" and `userId` = "1" and `actionId` != "56"
) b
ORDER BY b.priority desc, b.`timeStamp` DESC 
LIMIT 10 

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