Оптимизация JOIN с помощью временных таблиц
Когда тормозит база данных
Как выполняется JOIN
Теперь графически на примере.
Таким образом после соединения у MySQL будет таблица длиной в 1 миллион строк и шириной в 6 столбцов. Нетрудно посчитать что в среднем каждая строчка имеет длину в 550 байт. А значит суммарный объем таблицы составит
670 Мб. После этого из таблицы будут выбраны столбцы i.src и t.name и информация объемом в
Оптимизация JOIN путем уменьшения потребления памяти
Первым шагом создадим вспомогательную таблицу с типами с требуемыми нам столбцами и индексами. Далее выполним сам запрос:
Проверка на реальных данных
19,3 сек. При том что в это время у нас залочено 2 таблицы одновременно. Итого 38,6 условных «таблицо-секунд» блокировки.
Выигрыш очевиден, хоть и условия не выглядят как реальные. Но на реальных задачах, на более широких (множество столбцов) таблицах с множеством JOIN-ов можно достичь многократного роста скорости выполнения запросов и превратить те запросы, которые выполняются по несколько секунд в те, которые выполняются почти мгновенно. Кроме того, несомненным плюсом будет и отсутствие каскадных блокировок таблиц, т.к. они не будут участвовать одновременно в одном запросе, а будут блокироваться последовательно на маленькие промежутки времени.
Оптимизация JOIN с группировкой
Предположим, нам надо выгрузить список тысячи клиентов с максимальной суммой заказа. Напишем несложный запрос:
На тестовом стенде из нескольких миллионов user и несколько десятков миллионов product запрос выполняется
50 сек. Оптимизируем с помощью временной таблицы, чтоб MySQL проводил группировку до JOIN с использованием более узкой таблицы:
Суммарный итог: 10 сек. Выигрыш по скорости в 5 раз.
Оптимизация JOIN с подзапросами
Типичный запрос который составит разработчик будет выглядеть вот так:
MySQL при выполнении такого запроса сперва создаст две временные таблицы из подзапросов, потом создаст третью временную таблицу из JOIN-ов. Потом вернет результат клиенту. Проблема в том что таблицы из подзапросов не имеют индексов, поэтому для присоединения каждой таблицы движку придется выполнить очень много сравнений. Например если в таблице user около 1000 записей из них 400 человек привлечены другими, а заказы имеют 500 человек, то MySQL сделает 1000*400*500 = 200млн сравнений прежде чем будет готова итоговая таблица. Так же на всех учавствующих в запросе таблицах будет висать read lock всё время выплонения запроса.
Между тем, можно сделать вот так:
В этом случае все JOIN будут проходить по уникальным ключам и достаточно быстро. Кроме того таблицы будут получать read lock на более короткие промежутки времени и только по одной.
Проверка на реальных данных
На тестовом стенде была сгенерирована таблица user со 100000 записей, внешний ключ user_id есть у
70000 записей и ссылается (с неравномерным распределением) на
Кстати, если у Вас есть большие статичные таблицы, то Вы можете легко ускорить работу сделав из них compressed-таблицы.
Исследуем производительность JOIN в 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):
Результаты (погрешность менее 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
Давайте теперь подключим к делу таблицу Users — не зря же мы её создавали.
Мы будем исследовать запросы такого плана:
Параметр для 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 ( |
100)
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():
Такой запрос к таблице типа MyISAM отработает за 3 730 мс, а к InnoDB — за 8 290 мс. Зоркий глаз может заметить, что этот способ быстрее, чем JOIN к MEMORY, хоть и не намного. Этот способ подходит в случаях, если у вас очень быстрое подключение к MySQL (например UNIX socket). В остальных случаях, ИМХО, при таких количествах записей очевидно, что гонять огромное количество трафика между MySQL сервером и сервером приложений — не самая лучшая идея.
Выводы можете сделать сами: если нужно много джойнить, и почему-то нужна высокая производительность, используйте комбинацию MyISAM + InnoDB, или просто InnoDB + InnoDB, если джойнов больше одного. Ну а если вы — экстремал, можете использовать MyISAM + MEMORY для получения максимальной производительности при любых сценариях. Есть ещё вариант с MEMORY+MEMORY, но для большого количества записей я бы не стал так делать ;).
UPD: Хочется поблагодарить хабраюзера homm за очень полезные комментарии, например за этот. В общем, очень рекомендую прочитать комментарии, в них разъясняется много вещей, которые почему-то для читающих не были очевидны:
— кэш запросов отключен
— JOIN делается по первичному ключу
— индексы на таблицу Calls не создаются, потому что мы не ставим задачу оптимизировать какой-то конкретный запрос
Форум пользователей MySQL
Задавайте вопросы, мы ответим
Страниц: 1
#1 03.07.2008 17:41:24
LEFT JOIN оптимизация.
Добрый день.
Есть таблица с кол-вом записей
SELECT * FROM table1 LEFT JOIN table2 ON table1.t2_id=table2.id LEFT JOIN table3 ON table1.t3_id=table3.id WHERE table2.name=’russia’ AND table3.name=’moscow’ ORDER BY table1.name
#2 03.07.2008 17:55:26
Re: LEFT JOIN оптимизация.
P.S. по name тоже должен быть индекс
Отредактированно Shopen (03.07.2008 17:56:35)
#3 04.07.2008 10:13:38
Re: LEFT JOIN оптимизация.
#4 04.07.2008 10:25:19
Re: LEFT JOIN оптимизация.
Приведите EXPLAIN Вашего запроса. WHERE во многих случаях можно оптимизировать, используя составные ключи
#5 04.07.2008 12:09:53
Re: LEFT JOIN оптимизация.
Какие составные ключи можно использовать, если условия в WHERE затрагивает разные таблицы?
Реальный EXPLAIN привести не могу, т.к. запросы строятся в зависимости от потребности пользователя. Вернее в условии WHERE могут использоваться разные условия из разных таблиц.
#6 04.07.2008 12:20:54
Re: LEFT JOIN оптимизация.
Для каждого WHERE потребуются разные ключи, чтобы оптимизировать запросы. Порядок обработки таблиц также может зависеть от WHERE
#7 04.07.2008 13:11:24
Re: LEFT JOIN оптимизация.
ivsher написал:
— выделить запрос, который «тормозит»,
— сделать его explain и привести здесь
— сделать SHOW CREATE TABLE для всех таблиц входящих в запрос и привести результаты сюда.
— еще неплохо бы конфигурацию оборудования, если вы ее знаете (на Pentium Pro может много чего притормаживать 
)
Отредактированно Shopen (04.07.2008 13:12:34)
#8 29.07.2008 11:45:42
Re: LEFT JOIN оптимизация.
Забыл написать самого главного. Вернее ошибся в написании тестового запроса.
Переписываю запрос + его EXPLAIN
SELECT * FROM A LEFT JOIN B ON A.t1=B.t1 AND B.t2=const WHERE B.t1 is not null ORDER BY A.t2
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE B ref PRIMARY,t2 t2 12 const 361 Using where; Using temporary; Using filesort
1 SIMPLE A ref t1 t1 3 B.t1 49448
Выполняется запрос довольно долго. Можно ли его оптимизировать?
#9 29.07.2008 12:11:45
Re: LEFT JOIN оптимизация.
Индексы для такого запроса нужны на B(t2) и A(t1,t2).
Очень неприятно выражение B.t1 is not null. Возможно, нужна денормализация.
#10 29.07.2008 14:34:44
Re: LEFT JOIN оптимизация.
Можно убрать B.t1 IS NOT NULL и заменить LEFT JOIN на INNER JOIN
#11 29.07.2008 14:44:48
Re: LEFT JOIN оптимизация.
Логично
#12 19.03.2009 11:44:04
Re: LEFT JOIN оптимизация.
Подскажите в какой последовательности выполняется запрос следующего вида и есть ли варианты его оптимизации?
Select table1.*, table2.*, table3.*
from table1
left join table2 on (table2.id = table1.id)
left join table3 on (table3.id = table1.id)
where table1.idx in (1,2,3)
order by table1.chartext
limit 0, 5;
Правильно ли я понимаю что сначала выполняется связка left join, потом идет ограничение по условию where, сортировка и после лимит?
Вообще проблема состоит в filesort’е при выборке, т.к. используется сортировка по текстовому полю (varchar), составные индексы использовать никак т.к. версия 5.0.
В настоящем решение состоит в подзапросе из table1 c лимитом и сортировкой и последующяя связка.
Select t.*, table2.*, table3.*
from
(select * from table1 where idx in (1,2,3) order by table1.chartext limit 0, 5) as t
left join table2 on (table2.id = t.id)
left join table3 on (table3.id = t.id)
есть ли еще какие-то варианты решения?
#13 19.03.2009 13:31:53
Re: LEFT JOIN оптимизация.
chg написал:
Select t.*, table2.*, table3.*
from
(select * from table1 where idx in (1,2,3) order by table1.chartext limit 0, 5) as t
left join table2 on (table2.id = t.id)
left join table3 on (table3.id = t.id)
есть ли еще какие-то варианты решения?
При такой оптимизации возникает проблема с получением общего количества записей (как, если бы было без LIMIT). Если Вам это актуально, готов рассказать об этой проблеме подробнее.
#14 19.03.2009 14:37:27
Re: LEFT JOIN оптимизация.
Magz написал:
При такой оптимизации возникает проблема с получением общего количества записей (как, если бы было без LIMIT). Если Вам это актуально, готов рассказать об этой проблеме подробнее.
Расскажите конечно, интересно.
Проблема с LIMIT’ом существует, т.к. записей
200000, но мне кажется она не решабельна, так и так будет перебор по всему индексу.
#15 20.03.2009 12:49:06
Re: LEFT JOIN оптимизация.
. Но это, повторюсь, силно зависит от структуры данных.
Так же, такая оптимизация будет неверной, если нужно поставить условие на table2 или table3.
#16 23.03.2009 18:13:53
Re: LEFT JOIN оптимизация.
Спасибо за разъяснения, как-то я сильно и не задумывался насчет листалки, а ведь тоже свой кусок «жрёт», будем исправляться
#17 26.10.2009 14:47:39
Re: LEFT JOIN оптимизация.
Magz спсасибо помогло.
Страниц: 1
Board footer
Работает на PunBB
© Copyright 2002–2008 Rickard Andersson
MySql медленно работает с join – как его ускорить
Я должен экспортировать 554 тыс. Записей из нашей базы данных MySQL. По текущему курсу экспорт данных займет 5 дней, и медлительность в основном вызвана запросом ниже. Структура данных состоит из
Для контактов у нас есть индекс company_id. В таблице действий у нас есть индексы для contact_id и company_id, которые отображаются на соответствующие таблицы контактов и компаний.
Мне нужно захватить каждый контакт и дату последней активности, которая у них есть. Это запрос, который я выполняю, и его выполнение занимает около 0,5 секунды.
Если я удаляю соединение и просто выбираю * из контактов, где company_id = 20, запрос выполняется за 0,016 сек.
Если я использую Объяснение для информации по запросу соединения, я получаю это 
Любые идеи о том, как я могу ускорить это?
Изменение: вот определения таблицы.
Это запрос greatest-n-per-group, который часто возникает при переполнении стека.
Вот решение, которое использует оконную функцию MySQL 8.0:
Вот решение, которое должно работать на версиях до 8.0:
Было бы полезно создать новый индекс для действий (contact_id, seen_at).
Если вы хотите одну строку для contact_id :
Я чувствую, что что-то упускаю из виду, насколько сложны другие ответы, но я думаю, это все, что вам нужно.
Расширение комментариев к вашему вопросу; если вы не запрашиваете каждую компанию отдельно по определенной причине (распределение нагрузки, обработка структуры кода, это также обрабатывает другие компании по компаниям, что угодно), вы можете настроить вышеуказанный запрос, чтобы получить все свои результаты в одном запросе.
Как ускорить left join
Но у вас и результат должен быть разный
Так проще понять и связи и остальное
Потому что первый вообще какой-то «левый»
INNER JOIN table2 AS t2 ON t1.object_id = t2.id AND t1.object_group = ‘com_group’
ЗАчем? если t1.object_group не в видимости t2
LEOnidUKG, Но это никак не объясняет разницы в быстродействии
Дайте угадаю, таблица table2 у вас маленькая?
INNER JOIN обычно быстрее LEFT JOIN, у вас либо не хватает индексов либо хз что. То что вам тут дали картинку, это хорошо, но это не объясняет такого поведения. По картинке, кстати, ясно, что INNER JOIN абсолютно не эквивалент LEFT JOIN.
P.S. Пока писал, появился пост выше, почти такой-же.
остальные мизер. На всех полях что участвуют в объединении есть индексы. Результат запроса одинаков абсолютли
Загрузите на SQL Fiddle схемы, можно будет подумать как оптимизировать.
Я привёл информацию, что это разные инструменты и результаты будут разные. Просто так заменить слова и радоваться не получится.
Я вообще стараюсь избегать таких запросов, лучше несколько простых и перебор, чем вот такие конструкции и потом ищи гадай, что и зачем, почему это сжирает память и ещё по 4-6 секунд выполняется.
тут можно экспериментировать сколько угодно
Например меняем порядок таблиц
LEOnidUKG:
Я привёл информацию, что это разные инструменты и результаты будут разные. Просто так заменить слова и радоваться не получится.
Я вообще стараюсь избегать таких запросов, лучше несколько простых и перебор, чем вот такие конструкции и потом ищи гадай, что и зачем, почему это сжирает память и ещё по 4-6 секунд выполняется.
Та нормальный запрос, То вы, наверное не видели джойнов по 10 таблиц, и вложенных запросов.
