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

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

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

Что посоветуете лучше по производительности?
  • Вопрос задан
  • 16193 просмотра
Решения вопроса 1
@edogs
Подойдите к проблеме с чуть более раннего места.
Откуда берется массив? Из базы? Тогда может больше смысла подумать о сложном запросе?
Или можно массив во временную таблицу загнать и ссылаться на нее.
Ответ написан
Комментировать
Пригласить эксперта
Ответы на вопрос 10
Iliapan
@Iliapan
Использовать IN категорически не советую, начинает безбожно тормозить… Это костыль только для единичных случаев.
Ответ написан
Комментировать
@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 (...) — ничего плохого в этом нет.
Ответ написан
@Vampiro
Вообще, запросы крайне сложно оптимизировать не имея перед глазами вообще ничего.
Покажите create table, explain extended… Ну да ладно, поворчали, и будет.

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

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


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

Если запросы сложнее (join, order by, union), то вас ждет печаль, а нас — более подробное описание задачи. Зачастую быстрее загрузить все ID во временную таблицу, и потом уже с ней работать. Чем ждать пока запрос с заменой обычного условия на IN отработает.
Ответ написан
Комментировать
strib
@strib
А откуда берется массив?
Загнать его во временну таблицу и использовать EXISTS не быстрее будет?
Если массив в базе — то однозначно EXISTS.
Ответ написан
Zerstoren
@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 — сек
Ответ написан
Комментировать
@Ualde
Вики куда-то затерли, но в кэше Googl'a осталась копия.
Avoid using IN(...) when selecting on indexed fields, It will kill the performance of SELECT query.

Собственно, у вас может есть все-таки возможность использовать диапазоны для поиска? Или LIKE?
Ответ написан
keltanas
@keltanas
Software Developer
Если уж необходимо выбирать 1000 записей по их ID, может загнать их в Redis и дергать оттуда? Скорее всего это будет быстрее, и мускуль не будет виснуть. Если данных очень много, их можно расшардить по нескольким Редисам.

Попробуйте проанализировать ключи на вхождения в непрерывные диапазоны, как советовал Ualde. Например: записи с ключами 1, 2, 3, 5, 6, 7 можно получить в виде условия из 2х диапазонов.
Ответ написан
Комментировать
EugeneOZ
@EugeneOZ
Была похожая задача, только UPDATE нужен был. Цифр не засекал, но IN срабатывал медленно (да и длина запроса неприятная), поэтому я сделал prepare и в цикле сделал execute. Сработало это мгновенно, в отличит от IN.
Ответ написан
Комментировать
Gambik
@Gambik Автор вопроса
Всем спасибо за указанные направления к размышлению!
Буду думать и пробовать. Скорее всего пересмотрю архитектуру или буду использовать временную таблицу.

Если еще будут появляться идеи — с радостью впитаю! Спасибо!
Ответ написан
@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.
Ответ написан
Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы
Teamlead Краснодар
от 90 000 до 150 000 ₽
Омхоум Москва
от 100 000 до 150 000 ₽
RollerAds Санкт-Петербург
от 4 000 до 6 000 $
08 авг. 2022, в 23:58
10000 руб./за проект
08 авг. 2022, в 23:09
5000 руб./за проект
08 авг. 2022, в 22:26
15000 руб./за проект