MySQL带通配符LIKE查询优化：350万行数据查询慢如何提速？

我维护着一张包含350万行数据的B_DATA表，执行以下SQL查询耗时2.8秒，无法满足业务性能要求：

select distinct 
        b.ID, 
        b.PK, 
        b.PN, 
        b.REV, 
        b.I_USE, 
        b.STATUS 
from B_DATA b 
WHERE b.PN like '________1'  

执行EXPLAIN的结果显示查询走全表扫描（未使用PN字段的普通索引）。

已尝试的优化：

• 为PN字段创建普通索引：仅在PN = 'exact_value'精确匹配时生效，耗时仅0.002秒；
• 为PN字段创建FULLTEXT索引：对后缀通配符查询有一定优化，但无法支持前缀匹配场景。

业务需求：PN字段值长度为9或10字符，需要支持多种通配符模式，例如A__B__CC、ABCD%或%ABC等，需加速这类带通配符的LIKE查询。

1. 前缀索引适配左匹配场景 #

对于ABCD%这类前缀固定的左匹配查询，普通索引可直接生效。如果PN字段前缀重复率低，现有普通索引即可满足；若前缀重复率高，可创建指定长度的前缀索引平衡索引大小与查询效率：

CREATE INDEX idx_pn_prefix ON B_DATA (PN(6));

2. 反向存储+索引适配后缀匹配 #

针对%ABC这类后缀固定的右匹配查询，新增反向存储字段并创建索引：

1. 添加反向存储列：

ALTER TABLE B_DATA ADD COLUMN PN_REVERSE VARCHAR(10) GENERATED ALWAYS AS (REVERSE(PN)) STORED;

2. 为反向列创建索引：

CREATE INDEX idx_pn_reverse ON B_DATA (PN_REVERSE);

3. 查询时将匹配模式反向，将WHERE PN LIKE '%ABC'改为：

WHERE PN_REVERSE LIKE 'CBA%'

3. 生成列+联合索引适配固定位置通配符 #

对于A__B__CC这类固定位置有明确字符的匹配，拆分固定位置字符为生成列并创建联合索引：

1. 添加对应位置的生成列（示例为第1位、第4位、最后两位）：

ALTER TABLE B_DATA 
ADD COLUMN PN_POS1 CHAR(1) GENERATED ALWAYS AS (SUBSTRING(PN, 1, 1)) STORED,
ADD COLUMN PN_POS4 CHAR(1) GENERATED ALWAYS AS (SUBSTRING(PN, 4, 1)) STORED,
ADD COLUMN PN_LAST2 CHAR(2) GENERATED ALWAYS AS (RIGHT(PN, 2)) STORED;

2. 创建联合索引：

CREATE INDEX idx_pn_fixed_pos ON B_DATA (PN_POS1, PN_POS4, PN_LAST2);

3. 查询时先通过生成列过滤，再用LIKE校验：

SELECT DISTINCT ID, PK, PN, REV, I_USE, STATUS
FROM B_DATA
WHERE PN_POS1 = 'A' 
  AND PN_POS4 = 'B' 
  AND PN_LAST2 = 'CC'
  AND PN LIKE 'A__B__CC';

4. 调整全文索引参数适配包含匹配（MySQL） #

若使用MySQL，修改全文索引的最小分词长度为1，使其支持短字符匹配：

1. 修改配置文件（或临时设置）：

SET GLOBAL ft_min_word_len = 1;

2. 重启MySQL后重新创建全文索引：

DROP INDEX idx_pn_fulltext ON B_DATA;
CREATE FULLTEXT INDEX idx_pn_fulltext ON B_DATA (PN);

3. 使用布尔模式查询包含指定字符的记录：

SELECT DISTINCT ID, PK, PN, REV, I_USE, STATUS
FROM B_DATA
WHERE MATCH(PN) AGAINST('"ABC"' IN BOOLEAN MODE);

注：此方式更适合包含某段字符的模糊查询，对固定位置匹配效果有限。

5. 引入外部全文搜索引擎 #

如果复杂模糊查询场景较多，可引入Elasticsearch、Solr等专业搜索引擎：

• 将B_DATA表的PN字段同步至搜索引擎；
• 利用搜索引擎原生支持的前缀、后缀、通配符、正则等查询能力，大幅提升查询性能；
• 业务系统直接调用搜索引擎接口获取结果，再关联业务表获取其他字段（或同步全量需要的字段）。

6. 覆盖索引优化DISTINCT查询 #

当前查询使用DISTINCT，创建包含所有返回字段的覆盖索引，避免回表扫描：

CREATE INDEX idx_pn_covering ON B_DATA (PN) INCLUDE (ID, PK, REV, I_USE, STATUS);

即使走索引全扫描，也比全表扫描效率更高，因为索引数据量远小于全表。

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