美团面试：like 多个字段如何优化？

上周，一位小伙伴分享了他前往美团面试的经历。在面试过程中，他被问到一道关于Mysql调优的问题：在Mysql关键词查询中，使用%关键词%对三个字段进行查询，而数据量大概有一亿条，该如何优化？小伙伴回答得吞吞吐吐，面试官对此并不满意，最终他面试未通过。

业务场景

某餐饮平台用户评论表 comments 已累积 1亿条数据，其中包含以下核心字段：

• title（评论标题，VARCHAR 255类型）
• content（评论内容，TEXT类型）
• tags（标签，JSON数组类型，例如 ["网红店","火锅","适合拍照"]）

用户期望筛选出所有标题中含有“ 火锅”、内容里提到“ 服务好”且标签包含“ 网红店”的评论。对应的SQL语句如下：

SELECT * FROM comments WHERE title LIKE '%火锅%'   AND content LIKE '%服务好%'   AND tags LIKE '%网红店%';

核心难点：

1. 全模糊查询： LIKE '%xx%' 会致使索引失效，进而触发全表扫描。B+树按值 有序存储， LIKE '%火锅%' 需遍历所有可能的字符组合（诸如“ 重庆火锅”“ 火锅店”等），无法借助前缀进行定位，这等同于随机查找。
2. 多字段组合：三个字段交叉过滤，传统索引对此无能为力。即便创建联合索引 INDEX(title, content, tags)，由于查询条件均为范围匹配，索引最多仅能使用第一个字段，后续字段仍需回表过滤。倘若针对三个字段分别单独建立索引，优化器可能会选择 index_merge 策略，但这需要多次回表合并结果集，反而会降低效率。
3. 性能灾难：对1亿条数据进行全表扫描，耗时会超过 2分钟，数据库CPU将达到100%。全扫描1亿行数据大约需占用 20GB内存（InnoDB缓冲池默认远小于该数值），这会触发频繁的磁盘换页操作，从而形成“内存不足→磁盘IO暴增→查询更慢”的恶性循环。每个查询都会占用一个数据库连接，在高并发情况下，连接池会迅速耗尽，新请求将被拒绝，业务会出现大量超时告警。

青铜方案：强行建索引（加速崩盘）

-- 错误示范：为每个字段建普通索引
CREATE INDEX idx_title ON comments(title);
CREATE INDEX idx_content ON comments(content);
CREATE INDEX idx_tags ON comments(tags);

问题分析：

1. 索引完全失效： LIKE '%xx%' 无法利用B+树索引（只有 LIKE 'xx%' 才有可能命中）。
2. 合并扫描灾难：三个索引合并查询需要回表30万次。
3. 存储浪费：三个索引占用2.1GB空间，而数据量总共仅5GB。

黄金方案：倒排索引+分词优化（P7级解法）

第一步：使用全文搜索引擎（如Elasticsearch）

核心逻辑：

• 将 title、 content、 tags 字段内容进行 分词 存储（例如把“网红火锅店”拆分为“网红”“火锅”“店”）。
• 建立 倒排索引（记录每个词出现在哪些文档中）。

ES索引配置示例：

PUT /comments
{
    "mappings": {
        "properties": {
            "title": { "type": "text", "analyzer": "ik_max_word" },
            "content": { "type": "text", "analyzer": "ik_max_word" },
            "tags": { "type": "keyword" }  // 标签字段不分词
        }
    }
}

查询DSL：

GET /comments/_search
{
    "query": {
        "bool": {
            "must": [
                { "match": { "title": "火锅" } },
                { "match": { "content": "服务好" } },
                { "term": { "tags": "网红店" } }
            ]
        }
    }
}

白银方案：MySQL折中优化（低成本方案）

适用场景：在预算有限，无法引入ES的情况下适用。

1. N - gram分词索引

-- 修改表结构支持中文分词
ALTER TABLE comments 
ADD COLUMN title_ngram VARCHAR(255),
ADD COLUMN content_ngram TEXT;
-- 创建N - gram分词索引（以2 - gram为例）
CREATE INDEX idx_title_ngram ON comments(title_ngram(20));
CREATE INDEX idx_content_ngram ON comments(content_ngram(20));
-- 查询转换（将"火锅"拆分为"火","火锅","锅"）
SELECT * FROM comments 
WHERE title_ngram LIKE '%火锅%'  
  AND content_ngram LIKE '%服务好%';

2. 冗余组合字段

-- 新增组合字段（缩短模糊匹配长度）
ALTER TABLE comments 
ADD COLUMN title_content_tags VARCHAR(500) AS (CONCAT(title, '|', content, '|', tags));
-- 前缀索引优化
CREATE INDEX idx_combined_prefix ON comments(title_content_tags(20));

查询优化

SELECT * FROM comments 
WHERE title_content_tags LIKE '%火锅%服务好%网红店%';

王者方案：二级缓存+布隆过滤器（抗亿级并发）

1. 缓存热点查询（Redis+Lua脚本）

核心逻辑：将高频模糊查询的 结果集 或 特征值 缓存起来，以此避免重复计算。

实现细节

（1）缓存键设计：模糊查询的“指纹”

• 原始查询：LIKE '%火锅%服务好%网红店%
• 规范化处理：
  • 去除多余空格，得到 %火锅%服务好%网红店%
  • 统一字母大小写，结果仍为 %火锅%服务好%网红店%
  • 提取关键词组合，即 火锅+服务好+网红店
• 生成缓存Key：cache:模糊查询:火锅_服务好_网红店

（2）缓存内容优化

• 结果集缓存（适用于结果量小的场景）：

  -- 缓存匹配的评论ID列表（JSON数组）
  redis.call('SET', key, '[1001,1002,1003]', 'EX', 300)

• 特征值缓存（适用于结果量大的场景）：

  -- 缓存哈希摘要（MD5结果集）
  local result_md5 = md5(results)
  redis.call('SET', key, result_md5, 'EX', 600)

为什么需要特征值缓存？

当模糊查询的结果集极大时（例如返回10万条评论）：

• 直接缓存结果集：占用内存高（10万条×1KB = 100MB），网络传输速度慢。
• 特征值缓存：仅存储结果集的哈希摘要（固定32字节），体积相较于直接缓存结果集减少 99.97%。

适用场景

• 结果集内容变化频率低（如历史数据查询）。
• 客户端（如APP/浏览器）具备本地缓存能力。

（3）缓存淘汰策略

• 冷热分离：
  • 热数据（Top 10%查询）：TTL = 1小时。
  • 冷数据（长尾查询）：TTL = 5分钟。
• 主动预热：每日凌晨扫描日志，预加载前一日高频查询结果。

2. 布隆过滤器：拦截“不可能存在”的查询

核心逻辑：通过位图能够快速判断某个模糊查询是否 绝对不存在匹配结果，从而避免无效的数据库扫描。

1）布隆过滤器工作流程

2）布隆过滤器设计要点

预训练阶段：初始化时遍历全表数据，针对所有可能的模糊查询组合（如 title+content 关键词组合）生成哈希值并存入过滤器。

// 预训练代码示例
for (Comment comment : allComments) {
    String title = comment.getTitle();
    String content = comment.getContent();
    // 提取关键词组合（如"火锅_服务好"）
    String queryKey = extractKeywords(title) + "_" + extractKeywords(content);
    filter.put(queryKey);
}

动态更新：新增评论时，实时更新布隆过滤器。

public void addComment(Comment comment) {
    // 插入数据库
    commentDao.insert(comment);
    // 更新布隆过滤器
    String queryKey = generateQueryKey(comment);
    filter.put(queryKey);
}

性能对比（1亿数据实测）

避坑指南：三个模糊查询的禁忌

1. 禁止全模糊LIKE：LIKE '%xxx%' 必须转换为分词或前缀匹配，否则极易导致索引失效，触发全表扫描，严重影响查询性能。
2. 避免大字段索引：对于 TEXT 类型字段建索引，需采用前缀或分词的方式，否则不仅会占用大量存储空间，还可能无法有效利用索引提升查询效率。
3. 防缓存击穿：即使是空结果也要进行缓存（TTL设置较短），以此避免恶意攻击。若不缓存空结果，在高并发下，针对不存在数据的查询可能会瞬间压垮数据库。

总结

在处理Mysql模糊查询优化问题时，不同方案适用于不同的业务场景和预算条件。青铜方案虽简单却不可行，白银方案作为低成本的MySQL内部优化手段，在预算有限时可一定程度提升性能。黄金方案借助全文搜索引擎，对大规模数据的模糊查询优化效果显著。王者方案通过二级缓存和布隆过滤器，能有效应对亿级数据和高并发场景。同时，牢记避坑指南中的禁忌，可避免在优化过程中引入新的性能问题。开发者应根据实际情况综合考量，选择最适合的优化方案来提升系统的整体性能。