LIKE模糊匹配导致JOIN变慢时，仅前缀匹配（LIKE 'xxx%'）加普通索引有效；中间/后缀匹配需用函数索引、全文索引或子查询预过滤，且全文索引不可用于ON子句。
LIKE模糊匹配导致JOIN变慢，怎么加索引才有效
直接在 ON 或 WHERE 里对 JOIN 字段用 LIKE '%xxx%'，基本等于放弃索引——数据库没法走 B+ 树索引做前缀匹配，只能全表扫描。哪怕加了普通索引也白搭。
真正能起效的方案只有两个：用前缀匹配（LIKE 'xxx%'）配合常规 B-Tree 索引，或改用全文索引（FULLTEXT）+ MATCH...AGAINST。但后者只支持 MySQL MyISAM/InnoDB（5.6+），且不适用于 JOIN 条件中的右表字段。
- 左表字段用
LIKE 'abc%'→ 给该字段加普通索引即可生效 - 想搜中间或后缀（如
'%abc%'或'%abc'）→ 普通索引无效，必须换方案 - PostgreSQL 用户可考虑
pg_trgm扩展 +GIN索引，但 JOIN 中仍需USING或子查询绕开限制
用函数索引（MySQL 8.0+/PostgreSQL）绕过LIKE限制
如果业务真要查中间匹配，又不能改逻辑，函数索引是更可控的选择。它把模糊逻辑“固化”成一个可索引的计算列，避免每次查询都实时计算。
比如想对 user_name 做子串搜索，可以建一个生成列：
ALTER TABLE users ADD COLUMN name_trigram VARCHAR(100) GENERATED ALWAYS AS (SUBSTRING(user_name, 1, 100)) STORED;
再给它加索引：
CREATE INDEX idx_name_trigram ON users(name_trigram);
然后 JOIN 时改写条件为 ON u.name_trigram LIKE '%john%' —— 这样能命中索引，但要注意：
- 生成列内容必须覆盖所有可能的搜索长度，否则截断后漏匹配
- MySQL 函数索引不支持
LIKE表达式本身作为索引表达式，必须显式建生成列 - PostgreSQL 可直接用
CREATE INDEX ON t ((lower(name)))，但模糊搜索仍需配合pg_trgm
JOIN中用子查询预过滤，比直接模糊JOIN更稳
很多场景其实不是非要在 ON 里写模糊条件。把模糊筛选提前到子查询里，先缩小右表数据集，再 JOIN，性能和可读性都更好。
例如关联订单和用户，按用户名模糊找人：
SELECT o.*, u.user_name FROM orders o JOIN ( SELECT id, user_name FROM users WHERE user_name LIKE '%alice%' ) u ON o.user_id = u.id;
这种写法的关键优势在于：
- 数据库能先走
users表的索引（如果是前缀匹配）或至少只扫一次右表 - 避免因右表膨胀导致 JOIN 结果笛卡尔积式放大
- 在 PostgreSQL 中还能结合
MATERIALIZED（12+）或 CTE 强制物化，防止优化器重排执行计划
全文索引在JOIN中实际能用吗？哪些坑必须避开
MySQL 的 MATCH...AGAINST 不能直接出现在 ON 子句里，语法报错：ERROR 1210 (HY000): Incorrect arguments to AGAINST。必须拆成子查询或 WHERE 后置。
可行写法是：
SELECT o.*, u.user_name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE MATCH(u.user_name) AGAINST('john' IN NATURAL LANGUAGE MODE);
但要注意：
- 全文索引只对单表生效，JOIN 后无法跨表构建虚拟文档
-
IN BOOLEAN MODE支持+/-但不支持纯通配符（jo*需开启ft_min_word_len并重建索引） - 全文索引默认忽略停用词和短词（
ft_min_word_len=4），搜 “Li” 这种姓氏大概率查不到
模糊搜索和 JOIN 天然有冲突，没有银弹。最常被忽略的是：你以为加了索引就安全，其实只是把全表扫描从右表挪到了左表，或者靠内存硬扛。上线前务必看 EXPLAIN 的 rows 和 type 列，别信感觉。
