ON中用LIKE '%xxx%'或REGEXP会触发全表扫描，因左通配/中缀/正则无法走索引，驱动表每行都需被驱动表全扫匹配，1万×1万达1亿次比对；应改用前缀匹配、子查询预过滤、全文索引或预计算映射。

直接在 ON 子句里写 LIKE '%xxx%' 或 RLIKE 做关联，性能基本不可控——不是“慢一点”，而是数据量过万就卡死。根本原因不是写法不对，而是数据库引擎压根没法为这类动态、非等值条件选索引路径。

为什么 ON 里用 LIKE/REGEXP 会触发全表扫描

数据库优化器通常只能把等值条件，或者前缀范围这类条件（比如 name LIKE 'abc%'）有效下推到索引层；可一旦条件里出现左通配（'%abc'）、中缀匹配（'%abc%'）或正则（name RLIKE t2.pattern），情况就完全不一样了。此时没法走这类索引下推，驱动表每吐出一行数据，被驱动表的数据基本都得老老实实完整扫一遍，再逐条做字符串比对。

• 1万 × 1万 = 1亿次比对，CPU 和 I/O 都扛不住
• EXPLAIN 里看到 type: ALL、Using join buffer (Block Nested Loop) 就是典型信号
• 哪怕 name 字段有索引，只要被函数包裹（如 UPPER(name)）或开头带 %，索引就失效
• MySQL 的 REGEXP 每次都要重编译模式，PostgreSQL 的 ~ 同样无缓存，开销远高于 LIKE

把模糊逻辑从 ON 挪到子查询预过滤

这是最简单、最通用、效果最稳的绕过方式：先用索引快速筛出右表候选集，再 JOIN。不依赖版本特性，MySQL 5.7 也能跑。

• 写法示例：

  SELECT o.*, u.name FROM orders o JOIN ( SELECT id, name FROM users WHERE name LIKE '张%' ) u ON o.user_id = u.id

• 关键点：子查询里用 LIKE '张%' 可走索引，右表只扫几百行，而非几万行
• 如果必须中缀匹配（如 '%丽%' ），MySQL 8.0+ 可改用全文索引：MATCH(name) AGAINST('丽' IN NATURAL LANGUAGE MODE)，但字段得是 TEXT 或 VARCHAR，且需提前建 FULLTEXT 索引
• PostgreSQL 用户优先启用 pg_trgm 扩展：CREATE EXTENSION pg_trgm，再建 GIN 索引：CREATE INDEX idx_name_trgm ON users USING GIN (name gin_trgm_ops)，之后 WHERE name LIKE '%丽%' 自动命中索引

LEFT JOIN 场景下 ON 和 WHERE 放模糊条件的区别

这不只是性能问题，更是语义陷阱：放错位置会让 LEFT JOIN 退化成 INNER JOIN，悄无声息丢数据。

• 写在 ON 里：LEFT JOIN users u ON o.user_id = u.id AND u.name LIKE '%北京%' → 若某订单没匹配到北京用户，该订单记录仍保留，u.* 全为 NULL
• 写在 WHERE 里：LEFT JOIN users u ON o.user_id = u.id WHERE u.name LIKE '%北京%' → 先完成所有关联，再过滤；不满足条件的订单整行被剔除，LEFT JOIN 失效
• 更危险的是动态关键词：ON u.name LIKE CONCAT('%', t2.keyword, '%')，若 t2.keyword 是 NULL，CONCAT 返回 NULL，整个条件判为 UNKNOWN，该行直接不参与关联（LEFT JOIN 下也丢）
• 务必加 COALESCE(t2.keyword, '') 防空，但空字符串会导致 LIKE '%%'，全匹配——要加长度判断：AND LENGTH(COALESCE(t2.keyword, '')) > 0

真正需要模糊关联时，优先用预计算映射代替运行时匹配

硬扛字符串运算永远是最差解。业务上绝大多数“模糊”需求，本质是别名归一、简称标准化、品牌合并，完全可前置固化。

• 建一张 alias_map 表：canonical_name（标准名）和 variant（别名），例如 ('Apple', '苹果公司')、('Apple', 'AAPL')
• JOIN 改为等值：JOIN alias_map am ON u.company_name = am.variant，速度提升百倍以上
• 短文本相似度（如地址、人名）可用 SOUNDEX()（MySQL）或 levenshtein()（PostgreSQL），但必须加 WHERE LENGTH(u.name) BETWEEN 2 AND 20 限制长度，否则计算开销爆炸
• MySQL 5.7 不支持函数索引，但可冗余字段：ALTER TABLE users ADD COLUMN name_md5 CHAR(32) AS (MD5(name)) STORED，再建索引，JOIN 时用 = 匹配

很多人排查性能时，最容易漏掉的恰恰是字符集和排序规则不一致这个细节。举个很典型的情况：左表用 utf8mb4_unicode_ci，右表却是 utf8mb4_general_ci，一到 JOIN，MySQL 就会偷偷隐式调用 CONVERT()，结果就是索引当场失效。先用 SHOW FULL COLUMNS 查清楚，确认两边字段的 collation 完全一致，这件事的优先级其实比调优还高。

《夸克》非常好用的免费AI浏览器

下载APP查看