如何优化SQL中带有LIKE模糊匹配的分组查询_利用全文索引或前缀索引-SQL-PHP中文网

like模糊匹配与group by联用易触发全表扫描，因前导%使索引失效，导致group by需扫描全部数据再聚合；前缀索引仅对'abc%'有效，虚拟列+函数索引可解后缀匹配难题。

如何优化sql中带有like模糊匹配的分组查询_利用全文索引或前缀索引

LIKE 模糊匹配 + GROUP BY 一起用，基本等于主动触发全表扫描——除非你提前把索引结构和查询写法对齐。

为什么 `GROUP BY` 遇上 `LIKE` 更容易慢

不是 GROUP BY 本身慢，而是当它依赖 LIKE 过滤时，数据库往往无法复用索引完成两件事：先快速定位行、再按分组字段聚合。尤其当 LIKE 带前导 %（如 LIKE '%abc'），优化器会直接放弃走索引，EXPLAIN 显示 type: ALL、key: NULL，rows 等于总行数——此时 GROUP BY 只能扫完所有行再分组，内存和 CPU 压力都陡增。

即使字段上有普通 B+ 树索引，username LIKE '%张' 也几乎不走索引
GROUP BY username 在没过滤前提下，可能要对百万级唯一值做哈希或排序，开销远超单条 SELECT
如果 SELECT 中还有聚合函数（如 COUNT(*)、MAX(created_at)），且没被覆盖索引包含，还会引发大量回表

`FULLTEXT` 索引在分组场景下的真实限制

全文索引不是 LIKE 的“加速版”，它压根不支持 GROUP BY 直接依赖其结果做分组统计。你不能写 GROUP BY MATCH(title) AGAINST('xxx')，也不能用 AGAINST() 返回的 relevance 值来分组。

MATCH(col) AGAINST('关键词') 只能作为 WHERE 条件，返回的是布尔命中或浮点相关度，不是可分组的原始字段值
想按匹配到的关键词归类？得靠应用层聚合，或用 UNION + 多个 MATCH 查询分别取数再合并
中文需启用 ngram 插件（MySQL）或 zhparser（PostgreSQL），否则 '数据库优化' 可能被拆成单字或忽略停用词，导致漏匹配
ft_min_word_len（MyISAM）或 innodb_ft_min_token_size（InnoDB）默认为 4，'Go'、'AI' 这类短词不会被索引——别指望它们出现在 AGAINST() 结果里

真正能提速的前缀索引写法

前缀索引只对 LIKE 'abc%' 类左模糊有效，但必须确保查询模式与索引定义一致。常见错误是建了前缀索引却仍写 LIKE '%abc%'，白搭。

建索引时指定长度：例如 CREATE INDEX idx_username_prefix ON users(username(10))，仅对前 10 字符有效
查询必须以固定前缀开头：WHERE username LIKE 'admin%' 才能走索引；'%min%' 或 'ad%in' 都不行
前缀越短，索引越小，但选择性越差——如果前 3 位全是 'usr'，那 username(3) 索引几乎无区分度
若业务允许，把 GROUP BY 字段和 LIKE 字段分离：比如按邮箱域名分组，就提前用 SUBSTRING_INDEX(email, '@', -1) 提取并建索引，查 domain = 'example.com' 而非 email LIKE '%@example.com'

MySQL 8.0+ 虚拟列 + 函数索引是后缀匹配的解药

当必须查“以某字符串结尾”（如 filename LIKE '%.pdf'），又不想改业务逻辑，虚拟列是最轻量的破局点。它不存物理数据，但支持建索引，且查询侧无需改写函数调用。

添加虚拟列：ALTER TABLE docs ADD COLUMN ext VARCHAR(10) AS (SUBSTRING_INDEX(filename, '.', -1)) STORED;
建索引：CREATE INDEX idx_ext ON docs(ext);
查询直接写：SELECT ext, COUNT(*) FROM docs WHERE ext = 'pdf' GROUP BY ext; —— 完全走索引，无函数计算开销
注意：STORED 表示值物化存储，查询更快；VIRTUAL 每次读取实时计算，节省空间但增加 CPU
别在 WHERE 里对原字段用函数：WHERE SUBSTRING(filename, -4) = '.pdf' 仍全表扫，函数必须出现在索引定义侧

最易被忽略的一点：COLLATION 隐式转换会让索引失效。比如字段是 utf8mb4_unicode_ci，而你用 LIKE 'ABC%' 查询，但实际数据含大小混排，MySQL 可能放弃索引做逐行比较。统一用 _bin 校对集或显式加 BINARY 强制二进制比较，才能稳住索引路径。