（“”OR null）在where子句中的VS NOT（！“”AND NOT NULL）

Does anyone know if there is a performace differance between the following we parts of a where SQL string in mysql?

有谁知道在mysql的SQL字符串的下面我们的部分之间是否存在性能差异?

   WHERE ...  AND (field = "" OR field IS NULL);

and

  WHERE ...  AND (NOT (field != "" AND field IS NOT NULL));

5 个解决方案

#1

There is but it is negligibly small, so small I can't tell which would be faster and which slower. It's more of a matter of which is more readable — in this case I would pick the former.

但它有可忽略的小,如此小我无法分辨哪个更快,哪个更慢。这更具有可读性 - 在这种情况下我会挑选前者。

#2

there is no performance difference as such,. its much more about code-readability here,. and of course the first statement is more readable,.

这样没有性能差异。它更多的是关于代码可读性。当然第一个陈述更具可读性。

one more thing i would like to add is when thinking about optimizations,. one should not delve into micro-optimizations because that doesnt really add up to increasing performance because the difference is very negligible and u end up wasting time and resources and making your code less readable, less maintainable and less manageable.,

我想补充的另一件事是在考虑优化时。我们不应该深入研究微优化,因为这并不能真正增加性能,因为差异非常小,最终会浪费时间和资源,使代码可读性降低,可维护性降低,易于管理。

so whenever thinking about optimizations,. profile your code and start out with things like improving ur app design, db design, utilizing caching and so on,. rather then indulging into discussions about whether echo is faster or print (an example)

所以每当想到优化时,描述你的代码,并开始改进你的应用程序设计,数据库设计,利用缓存等等。而不是沉迷于关于回声是否更快或打印的讨论(例子)

#3

There is performance difference. In the way you have written query it depends upon the value of the variable "field".
In the query "WHERE ... AND (field = "" OR field IS NULL);" if field = "" is TRUE then field IS NULL will not be checked whether TRUE or FALSE. But if field = "" is FALSE then field IS NULL will be checked for TRUE or FALSE. Number of comparisons done depend on the value of "field".
In the query WHERE ... AND (NOT (field != "" AND field IS NOT NULL)); whether field != "" is TRUE or FALSE, field IS NOT NULL will be checked for TRUE or FALSE. Hence in either case comparison will be done twice. And finally NOT is checked. Hence overall three comparisons.
I prefer the former one, though the difference is negligible.
Its better not to use "NOT", although there will not be any significant performance difference considering today's compilers and system configurations.

有性能差异。在您编写查询的方式中,它取决于变量“field”的值。在查询“WHERE ... AND(field =”“OR field IS NULL);”如果field =“”为TRUE,则不检查字段IS NULL是TRUE还是FALSE。但是如果field =“”为FALSE,则将检查字段IS NULL是否为TRUE或FALSE。完成的比较次数取决于“字段”的值。在查询WHERE ... AND(NOT(field!=“”AND field IS NOT NULL));无论field!=“”为TRUE还是FALSE,将检查字段IS NOT NULL是否为TRUE或FALSE。因此,在任何一种情况下,比较将进行两次。最后不检查。因此总体上有三个比较。我更喜欢前者,尽管差别可以忽略不计。最好不要使用“NOT”,尽管考虑到今天的编译器和系统配置,不会有任何显着的性能差异。

#4

According to default behavior of NULLs in SQL server (at least MSSQL) is that:

根据SQL服务器(至少MSSQL)中NULL的默认行为是:

VALUE Logical_Operator NULL == FALSE

VALUE Logical_Operator NULL == FALSE

,- that is any Boolean operator with NULL results in FALSE (except special operator "IS NULL"). So in your second expression

, - 任何带NULL的布尔运算符都会导致FALSE(特殊运算符“IS NULL”除外)。所以在你的第二个表达中

"AND field IS NOT NULL"

“AND field IS NOT NULL”

is redundant part, because

是多余的部分,因为

(NULL != "") == FALSE

(NULL!=“”)== FALSE

Now back to your question. First expression should be slower, because when we request sql server to select rows with field which is NULL,- server can't use INDEX on that field, and uses plain table scan (look at execution plan). So because of this my main advice is that never use nulls in any query - better mark empty fields with pre-defined symbol or just empty string.

现在回到你的问题。第一个表达式应该更慢,因为当我们请求sql server选择字段为NULL的行时, - 服务器不能在该字段上使用INDEX,并使用普通表扫描(查看执行计划)。因此,我的主要建议是永远不要在任何查询中使用空值 - 更好地使用预定义符号标记空字段或仅使用空字符串。

EDIT: I've seen 'jokes' on mssql that construct below on some cases would run faster than your first expression (and possibly the second):

编辑:我在mssql上看到'笑话',在某些情况下构造如下将比第一个表达式(可能是第二个)运行得更快:

WHERE ... AND COALESCE(field,"")=""

在哪里...和COALESCE(字段,“”)=“”

My best guess at explaining this is that sql server don't likes OR cases, because by looking at query execution plan (with OR case) it is noticeable that server splits query into several parts and after executing these parts - merges results of both OR part queries. In any case you can add this third candidate for your performance experiments.

我最好的解释是,sql server不喜欢OR情况,因为通过查看查询执行计划(带OR情况),很明显服务器将查询分成几个部分,并在执行这些部分之后 - 合并两个OR的结果部分查询。在任何情况下,您都可以为性能实验添加第三个候选项。

#5

The two are equivalent, and should be executed with exactly the same plan. If they are not then there is no guarantee that just because a) executes faster than b) today then they always will do.

这两者是等价的,应该用完全相同的计划执行。如果它们不存在则不能保证仅仅因为a)比b)执行得更快,那么它们总会这样做。

The equivalence of the two is a direct application of elementary Boolean Algebra. If you have not had the opportunity already it's well worth your time to take a class or read a text covering it. The basics do not constitute a particularly large subject area, and the concepts are not particularly hard (you probably know much already, although probably not formally), but understanding the rules pays off richly once you have them under your thumb.

两者的等价性是基本布尔代数的直接应用。如果你还没有机会,那么值得你花时间去上课或阅读一篇文章。基础知识不是一个特别大的学科领域,概念并不是特别难(你可能已经知道很多,虽然可能不是正式的),但是一旦你掌握了这些规则,理解这些规则就会得到丰厚的回报。

#1