LIKE '%xxx%' 在 JOIN 的 ON 子句中必然触发全表扫描，因 B+ 树索引不支持左通配，优化器无法下推索引查找，导致逐行字符串比对，EXPLAIN 显示 type: ALL 和 Using join buffer。
LIKE '%xxx%' 在 JOIN 的 ON 子句里必然触发全表扫描
因为 B+ 树索引只支持最左前缀匹配，LIKE '%xxx%' 这种左通配写法，数据库优化器根本没法下推为索引查找。它只能对驱动表的每一行，在被驱动表里逐行做字符串比对——1 万 × 1 万就是 1 亿次扫描，EXPLAIN 里必现 type: ALL 和 Using join buffer (Block Nested Loop)。
常见错误现象：
-
LEFT JOIN t2 ON t1.name LIKE CONCAT('%', t2.keyword, '%')中，只要t2.keyword是NULL，CONCAT('%', NULL, '%')返回NULL，整行匹配失败，且不报错、无声丢数据 - 字符集不一致（比如
utf8mb4_unicode_civsutf8mb4_general_ci）会触发隐式CONVERT()，索引直接失效 - 字段上已有索引，但因
UPPER(name)、DATE(create_time)等函数包裹，索引完全失效
ON 里写模糊条件和 WHERE 里写，结果可能完全不同
语义差异直接影响结果集结构：放在 ON 是定义“怎么连”，放在 WHERE 是定义“连完怎么筛”。这对 LEFT JOIN 尤其致命。
实操区别：
-
LEFT JOIN t2 ON t1.id = t2.t1_id AND t1.name LIKE '%北京%'：只有满足模糊条件的t1行才会尝试关联t2，不满足的t1行仍保留，但t2字段全为NULL -
LEFT JOIN t2 ON t1.id = t2.t1_id WHERE t1.name LIKE '%北京%'：先完成全部关联，再过滤——不满足条件的整行被丢弃，LEFT JOIN退化成INNER JOIN - 动态关键词（如
t2.keyword）绝对不要放ON里做LIKE，除非你手动控制驱动表顺序，且确认t2.keyword内容干净、非空、不含%或_
全文索引不能直接用于 JOIN 的 ON 条件
MySQL 的 FULLTEXT 索引只允许在 WHERE 子句中用 MATCH() AGAINST()，写在 ON 里会直接报错；PostgreSQL 的 pg_trgm + GIST 同样无法在 ON 中直接生效。
可行路径只有两步：
- 先子查询提取关键词集合：
(SELECT id FROM logs WHERE MATCH(message) AGAINST('张三' IN NATURAL LANGUAGE MODE)) AS filtered_logs - 再用
IN或EXISTS关联主表，避免嵌套扫描 - 注意
CONCAT()拼接会导致参数化失效，预编译时可能缓存低效执行计划
真正稳定的替代方案是把模糊逻辑“摘出来”
绝大多数业务其实不需要在 JOIN 阶段实时做模糊匹配。硬扛 LIKE 或 REGEXP，等于主动放弃性能底线。
轻量可行路径：
- 应用层过滤：先用精确条件（如
city_id = 101）缩小结果集，再用 Python 的str.contains()或 JS 的includes()做最终筛选 - 冗余字段法：对
name预计算MD5(name)或SOUNDEX(name)存为新列，用等值JOIN替代模糊 - 映射表法：建
alias_map表，人工或规则生成canonical_name → variant映射，用=关联代替LIKE - 子查询预过滤右表：
JOIN (SELECT * FROM users WHERE name LIKE '张%') u ON o.user_id = u.id，让数据库先走索引扫一次右表，而不是每行都扫
容易被忽略的一点：即使你用了 REGEXP_LIKE() 或函数索引，只要模式含 .*abc.* 这类通用通配，索引依然无效——它只对极简、固定前缀的模式有加速效果。
