Кто тестировал Оператор IN в MySQL? Насколько он быстр и есть ли альтернативы?

Question

[Gambik]

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

Получается, что в оператор IN загоняется около 1000 значений через запятую. Чую, что-то тут не так)

Что посоветуете лучше по производительности?

Answer 1

[edogs]

Подойдите к проблеме с чуть более раннего места.
Откуда берется массив? Из базы? Тогда может больше смысла подумать о сложном запросе?
Или можно массив во временную таблицу загнать и ссылаться на нее.

Answer 2

[Iliapan]

Использовать IN категорически не советую, начинает безбожно тормозить… Это костыль только для единичных случаев.

Answer 3

[Nail]

Avoid using IN(...) when selecting on indexed fields
Я думаю это только для составных индексов: fields — во множественном числе.

Подробнее описано здесь:
www.mysqldiary.com/optimizing-the-mysql-in-comparison-operations-which-include-the-indexed-field/

Если делать ID IN (...) — ничего плохого в этом нет.

Comments

[Nail]

Дальше

> Получается, что в оператор IN загоняется около 1000 значений через запятую

Если таблица очень большая, не помещается в буфере, то выборка 1000 строк из нее будет требовать до 1000 io-операций.
На sata-диске это займет примерно 10 секунд.

То есть проблема тут только в io, и это будет также во всех субд, mysql тут не при чем.

Answer 4

[Vampiro]

Вообще, запросы крайне сложно оптимизировать не имея перед глазами вообще ничего.
Покажите create table, explain extended… Ну да ладно, поворчали, и будет.

Если вы используете простые числовые значения в IN и сам запрос простой, как пуля, например

select * from test1 where ID in (1,2,5,3);

То можно оставить как есть. На сколько я в курсе, мускуль будет всяко сам сортировать этот список, чтобы потом по нему искать. И даже индексы для этого будет использовать.

Если запросы сложнее (join, order by, union), то вас ждет печаль, а нас — более подробное описание задачи. Зачастую быстрее загрузить все ID во временную таблицу, и потом уже с ней работать. Чем ждать пока запрос с заменой обычного условия на IN отработает.

Answer 5

[strib]

А откуда берется массив?
Загнать его во временну таблицу и использовать EXISTS не быстрее будет?
Если массив в базе — то однозначно EXISTS.

Comments

[Анатолий]

sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/ так, на всякий случай =)

[strib]

Эх, значит что-то поменялось. Надо повторить, т.к. условия не идентичны указанным в вопросе, но я видел отличия в плане выполнения, при том в разных базах. Пойду попробую…

[whats]

@taliban Так на всякий случай, на дворе 2014. И на 2012 год тоже много в ядре поменялось. И производительность в том числе

[Анатолий]

@whats я понимаю что некропостеры никогда не умрут, но вы не пробовали проверить то что написано в той статье перед тем как написать каментарий? Ато вдруг ничего не изменилось а вы уже наехали на мой камент =)

Answer 6

[Zerstoren]

В случае потребности IN, добавляю LIMIT с DISTINCT. В добавок если IN приходит как список, то можно сразу-же и узнать длину списка для LIMIT.

Но выбирать строки будет долго, лучше цифры и то с индексами.

Собственно сравнение двух вариантов, при холодном запуске.
В базе храниться 4 миллиона записей.

SELECT DISTINCT * FROM `map` WHERE id IN ( 4, 5291, 12356, 256783, 1234, 1654, 57572 ) LIMIT 7
~0.0008 — сек

SELECT * FROM `map` WHERE id IN(4,5291,12356,256783,1234,1654,57572)
~0.0012 — сек

Answer 7

[Sergey]

Вики куда-то затерли, но в кэше Googl'a осталась копия.

Avoid using IN(...) when selecting on indexed fields, It will kill the performance of SELECT query.

Собственно, у вас может есть все-таки возможность использовать диапазоны для поиска? Или LIKE?

Comments

[Gambik]

Ни диапазоны ни LIKE не подходят. Нужно четкое числовое соответствие.
Как же любимые соцсети живут с таким кол-вом отношений «many to many» между таблицами?..

[antarx]

Как же любимые соцсети живут с таким кол-вом отношений «many to many» между таблицами?

Денормализация + NoSQL где можно. Крупные проекты не могут работать с единой БД — так или иначе надо шардить.

Answer 8

[keltanas]

Если уж необходимо выбирать 1000 записей по их ID, может загнать их в Redis и дергать оттуда? Скорее всего это будет быстрее, и мускуль не будет виснуть. Если данных очень много, их можно расшардить по нескольким Редисам.

Попробуйте проанализировать ключи на вхождения в непрерывные диапазоны, как советовал Ualde. Например: записи с ключами 1, 2, 3, 5, 6, 7 можно получить в виде условия из 2х диапазонов.

Answer 9

[EugeneOZ]

Была похожая задача, только UPDATE нужен был. Цифр не засекал, но IN срабатывал медленно (да и длина запроса неприятная), поэтому я сделал prepare и в цикле сделал execute. Сработало это мгновенно, в отличит от IN.

Answer 10

[Gambik]

Всем спасибо за указанные направления к размышлению!
Буду думать и пробовать. Скорее всего пересмотрю архитектуру или буду использовать временную таблицу.

Если еще будут появляться идеи — с радостью впитаю! Спасибо!

Comments

[freeek]

Не нашел статью, но читал, что:

SELECT * FROM table WHERE field IN ('name1', 'name2', 'name3'); //nameN
//Лучше менять по возможности на:
SELECT * FROM table WHERE field = 'name1' OR field = 'name2' OR field = 'name3'; //nameN

Протестировать нет возможности, попробуйте, расскажите нам :)

[Анатолий]

Это синонимы, ин будет действовать именно как OR

[freeek]

На базе с которой работаю.
MySQL 5.1.48.
Для четырёх вариантов, 7274 записей:
Первый запрос: 0,27 sec.
Второй запрос: 0,24 sec.

Во как!

[Анатолий]

У меня на 50000 записях оба запроса выполняются одинаково 0,06 =) Во как!

[freeek]

Может артефакты MySQL, не знаю. Но, есть вот такое дело, раз пишут, значит не только у меня :)

Answer 11

[Nail]

Тут примеры приводят какие-то удивляющие, я решил проверить.

Версия 5.5.25a-27.1-log Percona Server
В таблице 26 миллионов строк, размер на диске 4.5G.

FLUSH STATUS; select * from table where id in (1000,100000,1000000,3000000,5000000,7000000,10000000); SHOW SESSION STATUS LIKE 'Handler_read%'; 

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 7     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

FLUSH STATUS; select * from table where id in (1000,100000,1000000,3000000,5000000,7000000,10000000) limit 4; SHOW SESSION STATUS LIKE 'Handler_read%'; 

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 4     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

Сами запросы выполняются за 0.00 sec

Вывод:
Проверяйте индексы и статистику, апгрейдьте mysql.