Я думаю, ни для кого не секрет, что JOIN считается достаточно дорогой операцией, и многих начинающих программистов (которые юзают MySQL) любят запугивать, что JOIN — это плохо, и лучше всего обойтись без них, если есть возможность.
Давайте исследуем этот вопрос более подробно и посмотрим, действительно ли JOIN — это плохо, и когда вообще стоит задумываться об этом.
Для начала я бы хотел сразу сказать, что я не буду делать:
— тюнинг MySQL: все настройки берутся по умолчанию (в том числе innodb_buffer_pool_size = 8 Мб и прочее)
— интеграцию с языками программирования: все запросы будут делаться через MySQL клиент Sequel Pro, и время будет замеряться исходя из его показаний
— очевидные вещи, вроде джойна при выборке 3х строк: вопрос, экономить на спичках, или нет, я рассматривать не хочу — мы будем рассматривать экономию в десятки раз, а не десятки процентов
У нас будет две простых до безобразия таблички (таблицы были сделаны просто для примера и заполнены случайными данными):
Calls — 10 млн строк:
Users — 100 тыс строк:
Названия, я думаю, говорят сами за себя, индексы есть только по первичному ключу (id). Чисто в принципе, мы бы могли создать какие-нибудь индексы, которые бы нам помогли выполнять те запросы, которые мы будем исследовать, но у нас цель другая, а именно — исследовать, насколько быстро работает JOIN.
Для целей исследования, таблица Calls бралась двух видов — MyISAM и InnoDB, а таблица Users, к которой мы делаем JOIN, трёх видов — MyISAM, InnoDB и MEMORY
Все тесты проводились на моём ноутбуке, с MySQL версии 5.5.9 на Mac OS X с дефолтными настройками от MAMP. Все таблицы вмещались в память полностью, запросы прогонялись несколько раз, чтобы убедиться, что всё попадает в кеш.
Для начала, давайте просто посмотрим на скорость просмотра строк в MyISAM и InnoDB, выполнив такой запрос (напомню, что индексов ни по цене, ни по user_id нет — мы измеряем скорость FULL SCAN в MySQL):
И такой запрос:
Результаты (погрешность менее 5%):
Не хочу заниматься более подробным изучением, почему на аггрегирующие выборки в MyISAM так влияет кол-во попавших под WHERE строк, но факт остается фактом — при полном последовательном просмотре таблицы, MyISAM быстрее InnoDB в 4.5 раза. Отсюда и мнение о том, что InnoDB «тормоз», и о том, что сама MySQL (с MyISAM) очень шустра.
Мини-вывод: при полном последовательном просмотре MyISAM в 5 раз быстрее InnoDB
Давайте теперь подключим к делу таблицу Users — не зря же мы её создавали.
Мы будем исследовать запросы такого плана:
Параметр для cost подбирается таким образом, чтобы под выборку попал определенный процент записей в таблице Calls
Если мы сделаем запрос, приведенный выше, к мускулю, с параметром cost, который будет соответствовать N% строк, то MySQL сделает джойн всего-лишь в N% случаев, а для остальных (100-N)% строк он JOIN делать не будет. Вот такой MySQL умница.
Давайте же приступим к результатам тестирования с JOIN:
Пока что времена отличаются очень несущественно от FULL SCAN. Оно и понятно — ведь JOIN делается для мизерного количества строк.
Забавно, да? Всего-лишь 1% строк джойнится, а результаты для MyISAM + MyISAM больше в 2 раза, чем для MyISAM + InnoDB. Довольно забавно, что JOIN к InnoDB в данном случае оказывается быстрее, чем JOIN к MyISAM. И это мы ещё не начали тестировать :)!
За державу (MyISAM) обидно, а что поделать… Выходит, MyISAM не такой уж шустрый… Или нет? Давайте посмотрим на результаты финального тестирования
Обратите внимание на чудовищные (!) времена выборок при JOIN с MyISAM. А вот InnoDB приятно удивил — благодаря своей архитектуре, JOIN не является слишком дорогой операцией для InnoDB. Если говорить честно, то я был сильно удивлен, когда получил такой результат, что второй по скорости JOIN вариант — это когда к MyISAM джойнят InnoDB.
Ну а с MEMORY, я думаю, всё ясно — MEMORY дает оверхед в 525% (4 435 ms) на джойн по PK, InnoDB дает оверхед в 1 380% (11 655 ms), а про MyISAM стыдно говорить.
Зоркий глаз мог заметить, что для нашего сценария (когда мы делаем JOIN к users, чтобы отсеять всех женщин из таблицы звонков) есть способ без JOIN, а с простым перечислением всех user_id в IN():
Такой запрос к таблице типа MyISAM отработает за 3 730 мс, а к InnoDB — за 8 290 мс. Зоркий глаз может заметить, что этот способ быстрее, чем JOIN к MEMORY, хоть и не намного. Этот способ подходит в случаях, если у вас очень быстрое подключение к MySQL (например UNIX socket). В остальных случаях, ИМХО, при таких количествах записей очевидно, что гонять огромное количество трафика между MySQL сервером и сервером приложений — не самая лучшая идея.
Выводы можете сделать сами: если нужно много джойнить, и почему-то нужна высокая производительность, используйте комбинацию MyISAM + InnoDB, или просто InnoDB + InnoDB, если джойнов больше одного. Ну а если вы — экстремал, можете использовать MyISAM + MEMORY для получения максимальной производительности при любых сценариях. Есть ещё вариант с MEMORY+MEMORY, но для большого количества записей я бы не стал так делать ;).
UPD: Хочется поблагодарить хабраюзера homm за очень полезные комментарии, например за этот. В общем, очень рекомендую прочитать комментарии, в них разъясняется много вещей, которые почему-то для читающих не были очевидны:
— кэш запросов отключен
— JOIN делается по первичному ключу
— индексы на таблицу Calls не создаются, потому что мы не ставим задачу оптимизировать какой-то конкретный запрос
Давайте исследуем этот вопрос более подробно и посмотрим, действительно ли 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 не создаются, потому что мы не ставим задачу оптимизировать какой-то конкретный запрос
