MYSQL OR 与 IN 性能 [英] MYSQL OR vs IN performance

问题描述

我想知道以下在性能方面是否有任何差异

I am wondering if there is any difference in regards to performance between the following

SELECT ... FROM ... WHERE someFIELD IN(1,2,3,4)

SELECT ... FROM ... WHERE someFIELD between  0 AND 5

SELECT ... FROM ... WHERE someFIELD = 1 OR someFIELD = 2 OR someFIELD = 3 ... 

或者 MySQL 会像编译器优化代码一样优化 SQL 吗?

or will MySQL optimize the SQL in the same way compilers will optimize code ?

将 AND 更改为 OR 的原因在评论中说明.

Changed the AND's to OR's for the reason stated in the comments.

推荐答案

接受的答案没有解释原因.

The accepted answer doesn't explain the reason.

以下引用自高性能 MySQL，第 3 版.

Below are quoted from High Performance MySQL, 3rd Edition.

在许多数据库服务器中，IN() 只是多个 OR 子句的同义词，因为两者在逻辑上是等价的.在 MySQL 中不是这样，它对 IN() 列表中的值进行排序并使用快速二进制搜索来查看某个值是否在列表中.这是列表大小的 O(Log n)，而等效的一系列 OR 子句在列表的大小上是 O(n)(即，对于大列表要慢得多)

In many database servers, IN() is just a synonym for multiple OR clauses, because the two are logically equivalent. Not so in MySQL, which sorts the values in the IN() list and uses a fast binary search to see whether a value is in the list. This is O(Log n) in the size of the list, whereas an equivalent series of OR clauses is O(n) in the size of the list (i.e., much slower for large lists)

这篇关于MYSQL OR 与 IN 性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！