SQL Select IN 与 WHERE 子句中的 LIKE
哪一个性能更快？
SELECT field1, field2, field3, field4
FROM MyTable
WHERE field5 LIKE '%AL'
与
SELECT field1, field2, field3, field4
FROM MyTable
WHERE field5 IN ('AAL', 'IAL')
还是没有任何区别？
Which one is faster in performance?
SELECT field1, field2, field3, field4
FROM MyTable
WHERE field5 LIKE '%AL'
versus
SELECT field1, field2, field3, field4
FROM MyTable
WHERE field5 IN ('AAL', 'IAL')
or it doesn't make any difference?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
您的里程可能会有所不同，但第二个应该更快，因为它是两个索引支持的查找，而不是完整索引扫描。如果 field5 上没有索引，那应该没有关系（两种情况下都进行全表扫描）。
Your mileage may vary, but the second one should be faster, because it is two index-backed lookups, versus a full index scan. Without an index on field5 it should not matter (full table scan in both cases).
如果您没有覆盖索引（或至少在 field5 上有索引），那么两者都需要表扫描，因此效果同样较差。
关于查询...第二个与
WHERE field5 = 'AAL' OR field5 = 'IAL'相同，它是要查找的 2 个精确值（例如可能会进行查找）。 LIKE 和前导通配符意味着“我不知道要查找多少个值”搜索永远不会发生关于索引...如果您确实仅在 field5 上有一个索引，那么第二个索引可能需要 2 个关键查找来获取其余数据。第一个可能会忽略该索引，因为它有一个前导通配符。因此，假设事情按照我的预期进行，第二个更好。
使用覆盖索引，然后第二个再次位无键查找
关于搜索参数...如果将 IN 更改为变量，则计划将再次更改。使用常量的查询比使用变量的查询更快，因为使用常量时，值是预先已知的。
但是，你尝试过吗...
If you don't have a covering index (or at least an index on field5) then both will require table scans so will be equally poor.
About the queries... The second one is the same as
WHERE field5 = 'AAL' OR field5 = 'IAL'which is 2 precise values to look for (eg a seek is likely). The LIKE and leading wildcard implies "I don't know how many values to look for" seek will never happenAbout indexes... If you do have an index on field5 only, then the second one will probably have 2 key lookups to get the rest of the data. The first will probably ignore this index because it has a leading wildcard. So the 2nd is better assuming things behave as I'd expect.
With a covering index, then the 2nd one again bit no key lookup
About search arguments... If you change the IN to variables then the plan will change again. Queries with constants are quicker then queries with variables because with constants the values are known up front.
But, have you tried it...
我肯定会在你们的平台上进行测试。一些 RDBMS 在 IN 逻辑方面很糟糕，即比您预期的慢得多。 Mysql有这个问题，你没有在你的问题中指定。
I would definitely test on your platform. Some RDBMS are horrible at IN logic, i.e. much slower than you'd expect. Mysql has this issue, you don't specify in your question.
正如其他所述取决于您选择的 RDBMS，如果您使用 MS SQL，则这两个语句在性能方面是相同的
更新：根据 Martin 评论，当没有索引存在时，上述情况是正确的
field5，这是问题的原始假设。as other stated depends on your choice of RDBMS, if you are using MS SQL, both statements are identical in terms of performance
UPDATE: As per Martin comment, the above is true when there are no indexes present for
field5, which was the original assumption for the question.IN比LIKE命令更快...INis faster than theLIKEcommand...