如何加速含编码/参考号字段的LIKE模糊查询？

方案1：调整MySQL全文索引配置，使用ngram分词（推荐） #

因为你的列存储的是无空格的编码、参考号，默认全文索引按空格分词无法匹配中间子串，但MySQL 5.7及以上支持ngram全文解析器，它能将字符串拆分为指定长度的连续字符片段（n-gram），刚好适配这类编码的子串查询需求：

• 创建全文索引时指定ngram解析器：

  CREATE FULLTEXT INDEX ft_idx ON your_table(col1, col2, col3) WITH PARSER ngram;
  

• 调整ngram的最小分词长度（默认是2，若需匹配更短关键词，比如单个数字，可修改配置）：
  在MySQL配置文件中添加ngram_token_size = 1，重启服务生效。
• 查询时使用布尔模式确保关键词被包含：

  SELECT * FROM your_table WHERE MATCH(col1, col2, col3) AGAINST('+1234' IN BOOLEAN MODE);
  

  这种方式能快速定位包含指定子串的记录，性能远优于LIKE '%xxx%'。

方案2：生成函数索引（适配特定规则的编码） #

如果你的编码有固定格式（比如前缀是字母、后续是数字，像REF12345），可以提取核心匹配部分（比如数字部分）生成虚拟列，再给虚拟列建普通索引：

• 创建虚拟列并添加索引：

  ALTER TABLE your_table 
  ADD COLUMN num_col1 VARCHAR(255) GENERATED ALWAYS AS (REGEXP_REPLACE(col1, '[^0-9]', '')) STORED,
  ADD COLUMN num_col2 VARCHAR(255) GENERATED ALWAYS AS (REGEXP_REPLACE(col2, '[^0-9]', '')) STORED,
  ADD COLUMN num_col3 VARCHAR(255) GENERATED ALWAYS AS (REGEXP_REPLACE(col3, '[^0-9]', '')) STORED;
  
  CREATE INDEX idx_num_cols ON your_table(num_col1, num_col2, num_col3);
  

• 查询时直接针对数字列做模糊匹配：

  SELECT * FROM your_table 
  WHERE num_col1 LIKE '%1234%' OR num_col2 LIKE '%1234%' OR num_col3 LIKE '%1234%';
  

  这种方式减少了匹配的字符串长度，且索引能部分提速，适合编码规则固定的场景。

方案3：使用第三方全文搜索引擎（大数据量场景） #

如果表数据量达百万级以上，MySQL全文索引性能仍不够，建议用Elasticsearch这类专业搜索引擎：

• 将表中三列内容同步到ES索引，自定义分词器为ngram模式（或按字符拆分），确保子串能被正确分词。
• 通过ES的match或wildcard查询快速定位目标记录，再关联原表获取完整数据。
  ES的分布式架构和高效分词能力，能轻松处理大量数据的模糊查询需求。

方案4：覆盖索引优化（小数据量过渡方案） #

如果暂时不想改动架构，可尝试给三列建联合覆盖索引，减少查询时的回表操作：

CREATE INDEX idx_cover_cols ON your_table(col1, col2, col3);

查询时若仅需判断存在性或返回这三列数据，数据库可直接通过索引完成查询，无需访问主表，相比无索引的LIKE查询能提升不少速度。

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