Есть ли разница в производительности между BETWEEN и IN с MySQL или SQL в целом?

У меня есть набор последовательных строк, которые я хочу получить на основе их первичного ключа, который является автоматически увеличивающимся целым числом. Предполагая, что нет отверстий, существует ли какая-либо производительность между:

SELECT * FROM `theTable` WHERE `id` IN (n, ... nk);

и

SELECT * FROM `theTable` WHERE `id` BETWEEN n AND nk;

Ответы

Ответ 1

BETWEEN должен превзойти IN в этом случае (но также измерять и проверять планы выполнения!), тем более, что n растет, а статистика по-прежнему точна. Предположим, что

  • m - размер вашей таблицы.
  • n - размер вашего диапазона.

Индекс может использоваться (n является крошечным по сравнению с m)

  • Теоретически, BETWEEN может быть реализовано с одним "сканированием диапазона" (Oracle говорят) по индексу первичного ключа, а затем пересекает не более n узлы листа индекса. Сложность будет O(n + log m)

  • IN обычно реализуется как серия (цикл) сканирования n "диапазона индекса первичного ключа. Если размер m равен размеру таблицы, всегда будет O(n * log m)..., что всегда хуже (небрежно для очень маленьких таблиц m или очень малых диапазонов n)

Индекс не может быть использован (n является значительной частью m)

В любом случае вы получите полное сканирование таблицы и оцените предикат в каждой строке:

  • BETWEEN необходимо оценить два предиката: один для нижнего и один для верхней границы. Сложность O(m)

  • IN необходимо оценить не более префиксов n. Сложность O(m * n)... которая всегда всегда хуже или возможно O(m), если база данных может оптимизировать список IN как хэш-карту, а не список предикатов.

Ответ 2

a between b and c - это макрос, который расширяется до b <= a and a <= c.

a in (b,c,d) - это макрос, который расширяется до a=b or a=c or a=d.

Предполагая, что ваши n и nk являются целыми, оба должны совпадать. Вариант between должен быть намного быстрее, потому что он сравнивает только два сравнения по сравнению с nk - n для варианта in.

Ответ 3

Я сделал исследование для этого вопроса. У меня в таблице 11M строк. Я выполнил два запроса:

Запрос 1: SELECT * FROM PLAYERS WHERE SCORE BETWEEN 10 TO 20

Запрос 2: SELECT * FROM PLAYERS WHERE SCORE IN (10,11,...,20)

В то время как время выполнения оба запроса переводятся как Andomar, указанные выше.

Среди обоих запросов Query 1 работает быстрее, чем Query 2.

Чтобы узнать больше, перейдите по этой ссылке:

Производительность BETWEEN VS IN() в MySQL

Спасибо.