等于(=)和IN的性能差异(具有一个文字值) [英] Performance differences between equal (=) and IN with one literal value
问题描述
当我们使用等号且IN运算符具有相同的值时，SQL引擎有何不同?执行时间会改变吗?
How does SQL engines differ when we use equal sign and IN operator have same value? Does execution time changes?
第一个使用相等性检查运算符的人
1st one using equality check operator
WHERE column_value = 'All'
使用OR运算符和单个值的第二个
2nd one using OR operator and single value
WHERE column_value IN ('All')
如果只有一个值，SQL引擎是否将IN更改为=?
Does SQL engine changes IN to = if only one value is there?
在MySQL和PostgreSQL中是否有相同之处?
Is there any difference for same in MySQL and PostgreSQL?
推荐答案
这两个语句之间没有区别，并且当IN中只有一个元素时，优化程序会将IN转换为= .
There is no difference between those two statements, and the optimiser will transform the IN to the = when IN have just one element in it.
尽管您有这样的问题，但只需运行两个语句，运行其执行计划并查看差异即可.在这里-您什么都找不到.
Though when you have a question like this, just run both statements, run their execution plan and see the differences. Here - you won't find any.
在网上进行大量搜索之后，我发现了有关此支持的SQL文档(我认为它适用于所有DBMS):
After a big search online, I found a document on SQL to support this(I assume it applies to all DBMS):
如果括号内只有一个值，则此表述等同于
If there is only one value inside the parenthesis, this commend is equivalent to
"column_name" ='value1
WHERE "column_name" = 'value1
这是Oracle中两个查询的执行计划(大多数DBMS将以相同的方式处理):
Here is the execution plan of both queries in Oracle (Most DBMS will process this the same) :
EXPLAIN PLAN FOR
select * from dim_employees t
where t.identity_number = '123456789'
Plan hash value: 2312174735
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| DIM_EMPLOYEES |
| 2 | INDEX UNIQUE SCAN | SYS_C0029838 |
-----------------------------------------------------
对于IN():
EXPLAIN PLAN FOR
select * from dim_employees t
where t.identity_number in('123456789');
Plan hash value: 2312174735
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| DIM_EMPLOYEES |
| 2 | INDEX UNIQUE SCAN | SYS_C0029838 |
-----------------------------------------------------
如您所见，两者是相同的.这是在索引列上.对于未索引的列也是如此(只是全表扫描).
As you can see, both are identical. This is on an indexed column. Same goes for an unindexed column (just full table scan) .
这篇关于等于(=)和IN的性能差异(具有一个文字值)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
