問題描述
我想知道以下在性能方面是否有任何差異
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 性能的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！