会崩，且比预想更快；因ON中动态LIKE/REGEXP使索引失效，触发嵌套循环全表扫描，1万×1万达1亿次比对，LEFT JOIN下更危险。

JOIN 的 ON 子句里写 LIKE 或 REGEXP 会崩吗

会的，而且速度往往比你想象中更快——只要右表行数超过几百行，查询就很可能会卡住或者超时。对于像 ON t1.name LIKE CONCAT('%', t2.pattern, '%') 这样的动态拼接条件，数据库优化器无法选择索引路径，即便 t1.name 上存在B+树索引，也会完全失效。这其中的本质原因在于：它需要针对左表的每一行，都用右表当前行的pattern去进行一次全表扫描式的匹配。

• MySQL 5.7/8.0、PostgreSQL 默认拒绝在 ON 中使用列拼接的 LIKE（报错 Unknown column），除非你显式加表别名且确认 t2.pattern 非 NULL
• LEFT JOIN 下更危险：t2.pattern 为空或含非法字符（如 %、_、未转义的 ）时，整个 ON 条件求值为 UNKNOWN，该行直接被丢弃，不是“没匹配上”，而是“不满足关联条件”
• SQL Server 允许语法通过，但执行计划几乎必然退化为嵌套循环 + 右表全扫；Oracle 需函数索引配合，且仅限常量模式

模糊逻辑该放在 WHERE 还是子查询里

优先放子查询预过滤右表，而不是 WHERE 后过滤。区别在于：子查询能提前缩小右表数据集，避免 JOIN 阶段产生爆炸性中间结果；而 WHERE 是关联完成后才过滤，若左表 1 万行、右表 1 万行，先生成 1 亿行临时结果再筛，内存和 CPU 都扛不住。

• 正确姿势：把右表关键词先收拢，用 WHERE 筛出候选集，再等值 JOIN
  例如：SELECT * FROM orders o JOIN (SELECT id, name FROM users WHERE name LIKE '张%') u ON o.user_id = u.id
• 如果右表是小配置表（几十到几百行），可用 CROSS JOIN + WHERE 拼接，但必须加 MATCH() AGAINST() 或 REGEXP 的阈值过滤（如 > 0.1），否则笛卡尔积直接爆掉
• 千万别写 LEFT JOIN ... ON ... WHERE t2.field IS NOT NULL——这会让 LEFT JOIN 变成事实上的 INNER JOIN，漏掉左表无匹配的记录

前缀匹配（LIKE 'abc%'）怎么让索引生效

只有 LIKE '固定前缀%' 能走普通 B-Tree 索引；'%abc' 和 '%abc%' 都不行。关键不是写法，而是字段是否已建索引，以及前缀是否真正“固定”。

• 给 name 字段加普通索引：CREATE INDEX idx_users_name ON users(name)
• 查询必须用字面量前缀：WHERE name LIKE '王%' 或 WHERE name REGEXP '^王'，不能是 WHERE name LIKE CONCAT(@prefix, '%')（用户变量在预编译阶段不可知）
• MySQL 8.0+ 支持函数索引，可建 CREATE INDEX idx_name_lower ON users((LOWER(name)))，然后用 WHERE LOWER(name) LIKE 'zhang%'，但注意：函数索引不加速 LIKE '%zhang%'
• PostgreSQL 用户可直接用 pg_trgm + GIN 索引加速 ILIKE '%abc%'，但仅限单表扫描，不能用于 ON 右侧字段

时间戳最接近匹配怎么避免全表扫描

别在 ON 里写 ABS(TIMESTAMPDIFF(...))，这是性能黑洞。它强制对每一对组合实时计算差值，无法利用 ts 字段上的索引。

• 第一步永远是时间窗预过滤：ON b.ts BETWEEN DATE_SUB(a.ts, INTERVAL 10 MINUTE) AND DATE_ADD(a.ts, INTERVAL 10 MINUTE)，确保 b.ts 索引能命中
• 第二步用窗口函数取最近一条：ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY ABS(TIMESTAMPDIFF(SECOND, a.ts, b.ts)))，但必须在外层加 rn = 1 条件
• PostgreSQL 用户直接用 LATERAL + ORDER BY b.ts <-> a.ts LIMIT 1，比窗口函数更省内存，且天然支持索引下推
• 所有方案都依赖 b.ts 上有 B-tree 索引，没索引的话，时间窗也白搭

实际操作后才发现，最困难的并非语法正确，而是判断哪部分应在数据库中计算，哪部分应交给应用层处理。比如，面对10万行的用户表和50行的品牌词表，与其在SQL中强行使用JOIN进行模糊匹配，倒不如将品牌词提取到Python中，使用str.contains()进行批量筛选，这样反而更快速、更稳定。

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

下载APP查看