在业务系统中，我们经常需要通过SQL的JOIN操作把多张表关联起来，并且关联条件不是精确相等，而是带有模糊特征，例如根据用户名的部分关键字去匹配日志表中的操作用户。这类需求如果写法不当，很容易让数据库放弃索引，造成全表扫描。
为什么普通LIKE会导致JOIN变慢
当我们在JOIN的ON子句中使用形如 LEFT JOIN b ON a.name LIKE '%' || b.keyword || '%' 的写法时，大多数关系型数据库无法利用B+树索引来加速这种前后模糊的匹配，只能对驱动表每一行都去被驱动表做遍历，时间复杂度接近笛卡尔积级别。
常见优化方案
1. 使用函数索引或生成列
如果模糊搜索的模式固定，比如总是用某一列去包含另一列，可以为关联键建立函数索引或派生列，再基于该列做等值JOIN。
-- 为被关联表的关键字建立反向函数索引（以PostgreSQL为例） CREATE INDEX idx_b_keyword_lower ON b (lower(keyword)); -- 查询时先缩小范围再做模糊JOIN SELECT a.id, b.info FROM a JOIN b ON lower(a.name) LIKE '%' || lower(b.keyword) || '%' WHERE b.keyword IS NOT NULL;
2. 借助全文索引
对于文本搜索场景，使用数据库自带的全文检索（如MySQL的FULLTEXT、PostgreSQL的tsvector）比LIKE更高效。
-- 建立全文索引
ALTER TABLE b ADD COLUMN keyword_ts tsvector;
UPDATE b SET keyword_ts = to_tsvector('simple', keyword);
CREATE INDEX idx_b_ts ON b USING gin(keyword_ts);
-- 使用全文匹配完成JOIN
SELECT a.id, b.info
FROM a
JOIN b ON b.keyword_ts @@ plainto_tsquery('simple', a.name);
3. 先过滤再关联
把能走索引的精确条件先写好，减少参与模糊JOIN的数据量，是简单有效的办法。
SELECT a.id, b.info FROM (SELECT * FROM a WHERE create_time >= '2023-01-01') a LEFT JOIN b ON a.name LIKE '%' || b.code || '%';
效率与灵活性的平衡建议
| 方案 | 模糊能力 | 索引利用 | 适用场景 |
|---|---|---|---|
| 前后LIKE | 强 | 差 | 小数据量临时查询 |
| 函数索引 | 中 | 较好 | 模式固定的模糊关联 |
| 全文索引 | 强 | 好 | 文本搜索类业务 |
| 先过滤 | 不变 | 依赖其他索引 | 有明确范围条件的查询 |
实际开发中，建议先确认模糊匹配是否必须前后都模糊，如果可以改为右模糊（LIKE 'abc%'），则能直接命中最左前缀索引。若无法避免，优先考虑全文索引或生成列方案，并配合查询条件过滤来降低数据规模。
注意：在JOIN中写模糊条件时，数据库优化器往往难以预估行数，建议用EXPLAIN查看执行计划，确认没有发生隐式类型转换导致索引失效。
小结
SQL在JOIN里做模糊搜索并不是禁止项，关键在于把模糊逻辑和索引结构结合起来。通过合理设计索引、控制参与关联的数据量，以及选用合适的文本检索机制，可以在满足业务模糊匹配需求的同时，保持查询效率稳定在可接受范围内。