JOIN中直接用REGEXP或RLIKE做关联条件慢，因优化器无法为动态正则建立索引路径，导致全表扫描；LEFT JOIN时NULL或非法模式会使匹配结果为UNKNOWN而丢行；应改用WHERE阶段固定正则、IN组合或pg_trgm预过滤等优化方式。
JOIN中直接用REGEXP或RLIKE做关联条件为什么慢
MySQL里写 ON t1.name RLIKE t2.pattern 或 PostgreSQL 里用 ON t1.name ~ t2.pattern，表面看语法通，实际几乎必然触发全表扫描。优化器无法对动态正则模式建立索引路径，哪怕 t1.name 上有索引也无效——它得对左表每一行，都实时编译并执行右表当前行的正则表达式。
更危险的是 LEFT JOIN 场景：t2.pattern 若为空、含非法语法（如未转义的 \ 或 [），整行匹配结果为 UNKNOWN，该行直接被丢弃，不是“没匹配上”，而是“条件不成立”，容易漏数据。
- MySQL 中
RLIKE NULL返回 NULL，导致 ON 条件求值为 UNKNOWN - PostgreSQL 中
~ NULL同样返回 NULL，JOIN 行为等价于 INNER JOIN - 正则引擎无缓存，
t2.pattern每变一次，就得重编译一次，CPU 开销陡增
MySQL用REGEXP匹配多个固定关键词的替代写法
当右表是小规模关键词集（比如几十条姓氏、品牌名），别在 ON 里硬套 RLIKE，改用 IN + REGEXP 组合，把模糊逻辑压到 WHERE 阶段，并确保左表能走索引。
例如：查用户姓名以“张”或“王”开头的订单，不要这样写：
SELECT u.name, o.product FROM users u JOIN family_info f ON u.name RLIKE CONCAT('^', f.family_name)而应写成：
SELECT u.name, o.product FROM users u JOIN orders o ON u.id = o.user_id WHERE u.name REGEXP '^(张|王)';
关键点：
-
REGEXP '^(张|王)'是前缀匹配，MySQL 可利用name字段的 B-Tree 索引快速定位 - 把关键词硬编码进正则，避免 JOIN 时动态拼接；若关键词来自表，先用
GROUP_CONCAT拼成字符串再传入（注意长度限制） - 若关键词太多超出
REGEXP长度上限，拆成多个OR条件，比动态正则更稳
PostgreSQL中用pg_trgm+GIN索引加速JOIN模糊匹配
PostgreSQL 的 pg_trgm 扩展配合 GIN 索引，能让 LIKE '%abc%' 和 ILIKE 走索引，但注意：它不能直接用于 JOIN 的 ON 子句右侧字段——因为 GIN 索引只加速单表扫描，不参与 JOIN 选择率估算。
正确姿势是子查询预过滤右表：
SELECT o.*, u.name FROM orders o JOIN ( SELECT id, name FROM users WHERE name % 'john' -- % 是 pg_trgm 提供的相似性操作符 ) u ON o.user_id = u.id;
前提是已启用扩展并建索引：
CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);
要点：
-
%操作符基于 trigram 相似度，不是正则，但对拼写近似、错字场景更实用 - 若真需正则，用
~但必须加WHERE name ~ '^John'这类前缀模式，否则索引失效 - JOIN 前先用 CTE 物化右表结果（
WITH filtered_users AS (...) SELECT ...），防止优化器把模糊过滤下推失败
跨数据库通用策略：把正则挪到应用层做关联
当关键词集不大（
例如 Pandas 场景：
df_orders = pd.read_sql("SELECT * FROM orders", conn)
df_users = pd.read_sql("SELECT id, name FROM users", conn)
merged = df_orders.merge(df_users, left_on="user_id", right_on="id", how="left")
merged = merged[merged["name"].str.contains(r"^(张|王)", na=False)]优势明显：
- 数据库压力小，JOIN 仅靠主键或外键索引，毫秒级完成
- 正则逻辑完全可控：可处理空值、编码问题、回溯爆炸风险
- 便于调试——你能在 DataFrame 里直接
.head()看中间结果，SQL 里很难 inspect 每一行的正则匹配过程
真正难的不是写对正则，而是让数据库在 JOIN 时理解你的模糊意图；多数时候，它根本不打算帮你理解——你得主动绕开它的执行模型。
