Pull to refresh

Исследуем производительность JOIN в MySQL

MySQL *
Я думаю, ни для кого не секрет, что JOIN считается достаточно дорогой операцией, и многих начинающих программистов (которые юзают MySQL) любят запугивать, что JOIN — это плохо, и лучше всего обойтись без них, если есть возможность.

Давайте исследуем этот вопрос более подробно и посмотрим, действительно ли JOIN — это плохо, и когда вообще стоит задумываться об этом.

О чём я не буду писать


Для начала я бы хотел сразу сказать, что я не буду делать:

— тюнинг MySQL: все настройки берутся по умолчанию (в том числе innodb_buffer_pool_size = 8 Мб и прочее)
— интеграцию с языками программирования: все запросы будут делаться через MySQL клиент Sequel Pro, и время будет замеряться исходя из его показаний
— очевидные вещи, вроде джойна при выборке 3х строк: вопрос, экономить на спичках, или нет, я рассматривать не хочу — мы будем рассматривать экономию в десятки раз, а не десятки процентов

Начальные условия


У нас будет две простых до безобразия таблички (таблицы были сделаны просто для примера и заполнены случайными данными):

Calls — 10 млн строк:
id INT PRIMARY KEY AUTO_INCREMENT
user_id INT
cost INT
call_dt DATETIME
tariff_id INT

Users — 100 тыс строк:
id INT PRIMARY KEY AUTO_INCREMENT
birthdate DATE
name VARCHAR(10)
sex ENUM('M','F')


Названия, я думаю, говорят сами за себя, индексы есть только по первичному ключу (id). Чисто в принципе, мы бы могли создать какие-нибудь индексы, которые бы нам помогли выполнять те запросы, которые мы будем исследовать, но у нас цель другая, а именно — исследовать, насколько быстро работает JOIN.

Типы таблиц


Для целей исследования, таблица Calls бралась двух видов — MyISAM и InnoDB, а таблица Users, к которой мы делаем JOIN, трёх видов — MyISAM, InnoDB и MEMORY

First blood


Все тесты проводились на моём ноутбуке, с MySQL версии 5.5.9 на Mac OS X с дефолтными настройками от MAMP. Все таблицы вмещались в память полностью, запросы прогонялись несколько раз, чтобы убедиться, что всё попадает в кеш.

Для начала, давайте просто посмотрим на скорость просмотра строк в MyISAM и InnoDB, выполнив такой запрос (напомню, что индексов ни по цене, ни по user_id нет — мы измеряем скорость FULL SCAN в MySQL):

SELECT MAX(cost) FROM calls WHERE user_id = 5000; -- запрос №1

И такой запрос:

SELECT MAX(cost) FROM calls; -- запрос №2


Результаты (погрешность менее 5%):
InnoDB, ms MyISAM, ms
1 5 360 862
2 5 390 1 150

Не хочу заниматься более подробным изучением, почему на аггрегирующие выборки в MyISAM так влияет кол-во попавших под WHERE строк, но факт остается фактом — при полном последовательном просмотре таблицы, MyISAM быстрее InnoDB в 4.5 раза. Отсюда и мнение о том, что InnoDB «тормоз», и о том, что сама MySQL (с MyISAM) очень шустра.

Мини-вывод: при полном последовательном просмотре MyISAM в 5 раз быстрее InnoDB

JOIN


Давайте теперь подключим к делу таблицу Users — не зря же мы её создавали.
Мы будем исследовать запросы такого плана:

SELECT MAX(calls.cost) FROM calls
JOIN users ON calls.user_id = users.id
WHERE calls.cost > %d AND users.sex = 'M'

Параметр для cost подбирается таким образом, чтобы под выборку попал определенный процент записей в таблице Calls

Если мы сделаем запрос, приведенный выше, к мускулю, с параметром cost, который будет соответствовать N% строк, то MySQL сделает джойн всего-лишь в N% случаев, а для остальных (100-N)% строк он JOIN делать не будет. Вот такой MySQL умница.

Давайте же приступим к результатам тестирования с JOIN:

FULL SCAN + JOIN 0.1% строк

Users \ Calls InnoDB, ms (только JOIN, ms) MyISAM, ms (только JOIN, ms)
InnoDB 5 450 (~0) 857 (~0)
MyISAM 5 450 (~0) 937 (~100)
MEMORY 5 350 (~0) 845 (~100)

Пока что времена отличаются очень несущественно от FULL SCAN. Оно и понятно — ведь JOIN делается для мизерного количества строк.

FULL SCAN + JOIN 1% строк

Users \ Calls InnoDB, ms (только JOIN, ms) MyISAM, ms (только JOIN, ms)
InnoDB 5 660 (300) 999 (140)
MyISAM 6 530 (1 200) 1 810 (950)
MEMORY 5 460 (100) 911 (65)

Забавно, да? Всего-лишь 1% строк джойнится, а результаты для MyISAM + MyISAM больше в 2 раза, чем для MyISAM + InnoDB. Довольно забавно, что JOIN к InnoDB в данном случае оказывается быстрее, чем JOIN к MyISAM. И это мы ещё не начали тестировать :)!

FULL SCAN + JOIN 10% строк

Users \ Calls InnoDB, ms MyISAM, ms
InnoDB 7 230 (1 900) 2 190 (990)
MyISAM 16 100 (8 800) 10 200 (9 000)
MEMORY 6 080 (700) 1 440 (580)

За державу (MyISAM) обидно, а что поделать… Выходит, MyISAM не такой уж шустрый… Или нет? Давайте посмотрим на результаты финального тестирования

FULL SCAN + JOIN 100% строк

Users \ Calls InnoDB, ms MyISAM, ms
InnoDB 18 000 (14 650) 12 500 (11 655)
MyISAM 100 000 (96 650) 91 600 (90 750)
MEMORY 10 500 (7 150) 5 280 (4 435)

Обратите внимание на чудовищные (!) времена выборок при JOIN с MyISAM. А вот InnoDB приятно удивил — благодаря своей архитектуре, JOIN не является слишком дорогой операцией для InnoDB. Если говорить честно, то я был сильно удивлен, когда получил такой результат, что второй по скорости JOIN вариант — это когда к MyISAM джойнят InnoDB.

Ну а с MEMORY, я думаю, всё ясно — MEMORY дает оверхед в 525% (4 435 ms) на джойн по PK, InnoDB дает оверхед в 1 380% (11 655 ms), а про MyISAM стыдно говорить.

Замена JOIN на IN(...)


Зоркий глаз мог заметить, что для нашего сценария (когда мы делаем JOIN к users, чтобы отсеять всех женщин из таблицы звонков) есть способ без JOIN, а с простым перечислением всех user_id в IN():


SET group_concat_max_len = 10000000;
SELECT GROUP_CONCAT(id) FROM users WHERE sex = 'M'; -- запрос отработает за 50 мс, выдав много-много килобайт текста
SELECT MAX(cost) FROM calls WHERE user_id IN(%s); -- %s из предыдущего запроса


Такой запрос к таблице типа MyISAM отработает за 3 730 мс, а к InnoDB — за 8 290 мс. Зоркий глаз может заметить, что этот способ быстрее, чем JOIN к MEMORY, хоть и не намного. Этот способ подходит в случаях, если у вас очень быстрое подключение к MySQL (например UNIX socket). В остальных случаях, ИМХО, при таких количествах записей очевидно, что гонять огромное количество трафика между MySQL сервером и сервером приложений — не самая лучшая идея.

Выводы можете сделать сами: если нужно много джойнить, и почему-то нужна высокая производительность, используйте комбинацию MyISAM + InnoDB, или просто InnoDB + InnoDB, если джойнов больше одного. Ну а если вы — экстремал, можете использовать MyISAM + MEMORY для получения максимальной производительности при любых сценариях. Есть ещё вариант с MEMORY+MEMORY, но для большого количества записей я бы не стал так делать ;).

UPD: Хочется поблагодарить хабраюзера homm за очень полезные комментарии, например за этот. В общем, очень рекомендую прочитать комментарии, в них разъясняется много вещей, которые почему-то для читающих не были очевидны:
— кэш запросов отключен
— JOIN делается по первичному ключу
— индексы на таблицу Calls не создаются, потому что мы не ставим задачу оптимизировать какой-то конкретный запрос
Tags:
Hubs:
Total votes 103: ↑81 and ↓22 +59
Views 33K
Comments Comments 90