IN、LIKE ANY等语法的性能对比：哪种实现方式最优？

嘿，这个问题问到点子上了——SQL里这类匹配语法的性能差异，确实是很多人在查询调优时容易卡壳的点。结合我平时做项目的经验和对数据库底层逻辑的了解，给你拆解下：

先直接说结论：精确匹配选IN，模糊匹配尽量绕开LIKE ANY，正则匹配是最后的选择，具体原因如下：

• IN子句：这是精确匹配场景下的绝对首选。数据库对IN的优化非常成熟——它会自动把列表里的值去重排序，然后直接利用列上的B-tree索引快速定位（只要列上有合适的索引）。比如WHERE id IN (1,2,3)，基本就是毫秒级的索引查找。唯一要注意的是，如果IN的列表太长（比如几千上万条），性能会略有衰减，但数据库通常会自动把它转换成JOIN来处理，整体还是比模糊匹配高效得多。
• LIKE ANY（或类似多模式模糊匹配）：这类语法是用来处理多个模糊匹配条件的，比如WHERE name LIKE ANY ('%foo%', '%bar%')。性能上天生就比IN差：如果是前后带%的通配符，普通B-tree索引完全用不上，数据库只能做全表扫描；就算是前缀匹配（'foo%'），多模式下的索引利用率也不如单个LIKE。而且每条数据要检查多个匹配模式，开销会叠加。
• 其他语法（比如REGEXP）：正则匹配的性能比LIKE ANY还要糟糕，因为正则的匹配逻辑更复杂，数据库几乎没有优化空间，除非是极简单的正则表达式，否则全表扫描的开销会非常大。另外提一句EXISTS，它和IN在很多场景下性能相当，甚至在子查询结果集大的时候更优——因为EXISTS是半连接，找到匹配就停止，不用把所有值都取出来。

性能最优永远是结合场景选工具，给你分场景说：

1. 精确匹配场景

   • 优先用IN，确保匹配列上有B-tree索引（字符串列要注意字符集、排序规则和索引一致）。
   • 如果IN列表过长（比如超1000个值），把这些值放到临时表/永久表，用JOIN替代IN：

     SELECT t.*
     FROM target_table t
     JOIN temp_match_values tv ON t.id = tv.id;
     

     这种方式利用两个表的索引做连接，比长列表IN的性能更稳定。

2. 模糊匹配场景

   • 前缀匹配：给列创建前缀索引（比如CREATE INDEX idx_name_prefix ON user(name(10))，长度按需调整），这样LIKE ANY ('foo%', 'bar%')就能用上索引，性能提升明显。
   • 后缀/中间匹配：普通索引没用，换这两个思路：
     • 用全文索引：主流数据库（MySQL、PostgreSQL、SQL Server）都支持。比如PostgreSQL里用全文搜索替代LIKE ANY：

       SELECT * FROM user 
       WHERE to_tsvector('english', name) @@ to_tsquery('english', 'foo | bar');
       

       全文索引专门针对文本搜索优化，数据量越大，比LIKE的优势越明显。
     • 预处理数据：如果经常要匹配某些关键词，提前把关键词提取到关联表，用JOIN替代模糊匹配。比如维护一个user_name_keywords表，存每个用户名对应的关键词，查询时JOIN这个表，就能用索引快速匹配。

3. 混合场景
   如果既有精确匹配又有模糊匹配，把精确条件放前面，让数据库先过滤掉大部分数据，再做模糊匹配。比如WHERE category_id = 5 AND name LIKE ANY ('%foo%', '%bar%')，先通过category_id的索引缩小范围，再在小数据集里做模糊匹配，开销会小很多。

最后提个小技巧：不管用哪种语法，都要用EXPLAIN看执行计划——数据库的优化器虽然聪明，但不同版本、不同配置的表现可能不一样，看执行计划能帮你精准判断索引是否生效、有没有全表扫描，再针对性调优。

内容的提问来源于stack exchange，提问作者Lucas Gabriel Pastoriza