SQL 效率 - [=] vs [in] vs [like] vs [matches]
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3353084/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL efficiency - [=] vs [in] vs [like] vs [matches]
提问by CheeseConQueso
Just out of curiosity, I was wondering if there are any speed/efficiency differences in using [=] versus [in] versus [like] versus [matches] (for only 1 value) syntax for sql.
出于好奇，我想知道在sql中使用 [ =] 与 [ in] 与 [ like] 与 [ matches]（仅 1 个值）语法是否存在任何速度/效率差异。
select field from table where field = value;
versus
相对
select field from table where field in (value);
versus
相对
select field from table where field like value;
versus
相对
select field from table where field matches value;
采纳答案by Damian Leszczyński - Vash
I will add to that also existsand subquery.
我将添加到也存在和子查询。
But the performance depends on the optimizer of the given SQL engine.
但是性能取决于给定 SQL 引擎的优化器。
In oracle you have a lot of differences between IN and EXISTS, but not necessarily in SQL Server.
在 oracle 中，IN 和 EXISTS 之间有很多差异，但在 SQL Server 中不一定。
The other thing that you have to consider is the selectivity of the column that you use. Some cases show that IN is better.
您必须考虑的另一件事是您使用的色谱柱的选择性。一些案例表明 IN 更好。
But you have to remember that INis non-sargable(non search argument able) so it will not use the index to resolve the query, the LIKEand =are sargableand support the index
但是你必须记住，IN是不可 sargable（不可搜索参数）所以它不会使用索引来解析查询，LIKE和=是sargable并支持索引
The best ? You should spend some time to test it in your environment
最好的 ？您应该花一些时间在您的环境中对其进行测试
回答by Steven A. Lowe
it depends on the underlying SQL engine. In MS-SQL, for example (according to the query planner output), IN clauses are converted to =, so there would be no difference
它取决于底层的 SQL 引擎。例如在 MS-SQL 中（根据查询计划器的输出），IN 子句被转换为 =，所以不会有区别
回答by Scott M.
normally the "in" statement is used when there are several values to be compared. The engine walks the list for each value to see if one matches. if there is only one element then there is no difference in time vs the "=" statement.
通常在要比较多个值时使用“in”语句。引擎遍历每个值的列表以查看是否匹配。如果只有一个元素，那么与“=”语句在时间上没有区别。
the "like" expression is different in that is uses pattern matching to find the correct values, and as such requires a bit more work in the back end. For a single value, there wouldn't be a significant time difference because you only have one possible match, and the comparison would be the same type of comparison that would occur for "=" and "in".
“like”表达式的不同之处在于它使用模式匹配来查找正确的值，因此需要在后端做更多的工作。对于单个值，不会有显着的时间差异，因为您只有一个可能的匹配项，并且比较将与对“=”和“in”进行的比较类型相同。
basically, no, or at least the difference is so insignificant that you wouldn't notice.
基本上，不，或者至少差异是如此微不足道，以至于您不会注意到。
回答by Ned Batchelder
The best practice for any question about what would be faster, is to measure. SQL engines are notoriously difficult to predict. You can look at the output of EXPLAIN PLAN to get a sense of it, but in the end, only measuring the performance on real data will tell you what you need to know.
任何关于什么会更快的问题的最佳实践是测量。众所周知，SQL 引擎难以预测。您可以查看 EXPLAIN PLAN 的输出以了解它，但最终，只有测量真实数据的性能才会告诉您需要了解的内容。
In theory, a SQL engine could implement all three of these exactly the same, but they may not.
理论上，SQL 引擎可以完全相同地实现所有这三个，但它们可能不会。
