Все подряд О чем здесь

Оптимизация JOIN с помощью временных таблиц

Когда тормозит база данных

Многие разработчики рано или поздно при работе с MySQL сталкиваются с проблемами производительности. Одна из частых причин - много запросов с блокировкой ресурсов в очереди с долгой отработкой запросов, либо из-за deadlocks.

Обычно проблем с простыми SELECT-запросами не возникает. Они выплоняются довольно быстро, а если не быстро - то оптимизируются с помощью добавления правильных индексов или переопределения логики. А вот запросы с использованием JOIN довольно часто начинают необъяснимо тормозить, даже если использовать исключительное присоединение таблиц по Primary Key. Давайте посмотрим подробнее как происходит выполнение JOIN-запроса в MySQL.

Как выполняется JOIN

Любой SELECT-запрос начинает выполняться именно с открытия учавствующих в запросе таблиц и их соединения по JOIN и WHERE условиям. При соединении учавствующих таблиц MySQL создает новую TEMPORARY таблицу, подходящую под все условия. После соединения к TEMPORARY таблице применяются остальные части запроса - GROUP BY, ORDER BY, HAVING, LIMIT, SELECT (выборка определеных столбцов).

Теперь графически на примере.


Как выполняется JOIN таблиц в MySQL.

Имеется две таблицы - таблица image со столбцами id, src, type_id и таблица type со столбцами id, name, wiki_info. Столбцы id (в обеих таблицах), type_id имеют тип INT и занимают по 4 байта. Столбцы src и src имеют тип varchar(64) и занимают в среднем по 40 байт. А столбец wiki_info имеет тип varchar(1024) и занимает в среднем 500 байт. В таблице image 1 миллион строк, а в таблице type 3 строки. Нетрудно посчитать что image занимает на диске порядка 50 Мб пространства, а type около полутора килобайт.

Теперь предположим что мы хотим выгрузить список источников (image.src) изображений и рядом с каждым - его название типа (type.name). Любой разработчик сделает это с легкостью одним запросом:

SELECT i.src, t.name FROM image i JOIN type t ON t.id = i.type_id

Как обработает такой запрос MySQL? Как и было описано выше - первым выплоняется JOIN по условиям и создает новую таблицу склеивая две другие по условию t.id = i.type_id. Получится такая таблица:


TEMPORARY таблица после выполнения JOIN.

Таким образом после соединения у MySQL будет таблица длиной в 1 миллион строк и шириной в 6 столбцов. Нетрудно посчитать что в среднем каждая строчка имеет длину в 550 байт. А значит суммарный объем таблицы составит ~ 670 Мб. После этого из таблицы будут выбраны столбцы i.src и t.name и информация объемом в ~ 100 Мб отправлена клиенту. В том случае если значение tmp_table_size будет больше чем размер временной таблицы - запрос отработает достаточно быстро, но если же значение tmp_table_size будет недостаточным - MySQL эту же таблицу будет создавать на диске. А запись такого количества информации на диск - весьма медленная операция, получим долгий запрос, к тому же бесполезно нагружающий дисковую систему. Согласитесь, было бы правильно сначала выделить нужные столбцы, а потом уже соединить таблицы.

Оптимизация JOIN путем уменьшения потребления памяти

Первым шагом создадим вспомогательную таблицу с типами с требуемыми нам столбцами и индексами. Далее выполним сам запрос:

#Создаем вспомогательную таблицу CREATE TEMPORARY TABLE tmp_type (PRIMARY KEY (id)) SELECT id, name FROM type; #Выполняем непосредственно запрос SELECT i.src, t.name FROM image i JOIN tmp_type t ON t.id = i.type_id

Созданная во время выполнения SELECT вспомогательная таблица будет иметь теперь всего 5 столбцов, а вся таблица будет иметь размер около 110 Мб, из которых 100 Мб будет отправлено клиентскому приложению. При таком выполнении запроса мы сильно снизили вероятность выхода за рамки tmp_table_size, но если даже и вышли - работа с диском отнимет у MySQL в 6 раз меньше времени чем в случае неоптимального варианта.

Проверка на реальных данных

Для проверки возьмем аналог приведенную выше таблицу, которая используется в реальном проекте. Количество строк - 48 млн. Вторая таблица будет аналогичная той что в рассматриваемом выше примере. Добавим инструкцию LIMIT чтоб увеличить сложность довольно простого запроса и показать какой потенциальный выигрыш можно получить.

SELECT i.src, t.name FROM image i JOIN type t ON t.id = i.type_id LIMIT 20000000, 100;

Время выполнения - ~ 19,3 сек. При том что в это время у нас залочено 2 таблицы одновременно. Итого 38,6 условных "таблицо-секунд" блокировки.

CREATE TEMPORARY TABLE tmp_type (PRIMARY KEY (id)) SELECT id, name FROM type; SELECT i.src, t.name FROM image i JOIN tmp_type t ON t.id = i.type_id LIMIT 20000000, 100;

Время выполнения - ~ 11,6 сек

Выигрыш очевиден, хоть и условия не выглядят как реальные. Но на реальных задачах, на более широких (множество столбцов) таблицах с множеством JOIN-ов можно достичь многократного роста скорости выполнения запросов и превратить те запросы, которые выполняются по несколько секунд в те, которые выполняются почти мгновенно. Кроме того, несомненным плюсом будет и отсутствие каскадных блокировок таблиц, т.к. они не будут участвовать одновременно в одном запросе, а будут блокироваться последовательно на маленькие промежутки времени.

Оптимизация JOIN с группировкой

Предположим, нам надо выгрузить список тысячи клиентов с максимальной суммой заказа. Напишем несложный запрос:

SELECT u.id, u.name, sum(p.price) order_sum FROM user u JOIN product p ON p.user_id = u.id GROUP BY p.user_id ORDER BY order_sum DESC LIMIT 1000;

На тестовом стенде из нескольких миллионов user и несколько десятков миллионов product запрос выполняется ~50 сек. Оптимизируем с помощью временной таблицы, чтоб MySQL проводил группировку до JOIN с использованием более узкой таблицы:

CREATE TEMPORARY TABLE t1 (PRIMARY KEY (user_id)) SELECT user_id, sum(price) order_sum FROM product GROUP BY user_id; SELECT SQL_NO_CACHE u.id, u.name, p.order_sum FROM user u JOIN t1 p ON p.user_id = u.id ORDER BY order_sum DESC LIMIT 1000;

Суммарный итог: 10 сек. Выигрыш по скорости в 5 раз.

Оптимизация JOIN с подзапросами

Еще один тип проблемных JOIN-ов - это запросы с наличием подзапросов. Например, вам требуется выгрузить список клиентов и напротив каждого отобразить, количество и сумму его заказов и количество привлеченных им других клиентов по клиентской программе. В наличии две большие таблицы - user и order, в user есть внешний ключ user_id к самой себе.

Типичный запрос который составит разработчик будет выглядеть вот так:

SELECT u.id, u.name, t1.order_sum, t1.order_count, t2.new_users FROM user u LEFT JOIN ( SELECT user_id, sum(price) order_sum, count(*) order_count FROM product GROUP BY user_id ) t1 ON t1.user_id = u.id LEFT JOIN ( SELECT user_id, count(*) new_users FROM user WHERE user_id != 0 GROUP BY user_id ) t2 ON t2.user_id = u.id;

MySQL при выполнении такого запроса сперва создаст две временные таблицы из подзапросов, потом создаст третью временную таблицу из JOIN-ов. Потом вернет результат клиенту. Проблема в том что таблицы из подзапросов не имеют индексов, поэтому для присоединения каждой таблицы движку придется выполнить очень много сравнений. Например если в таблице user около 1000 записей из них 400 человек привлечены другими, а заказы имеют 500 человек, то MySQL сделает 1000*400*500 = 200млн сравнений прежде чем будет готова итоговая таблица. Так же на всех учавствующих в запросе таблицах будет висать read lock всё время выплонения запроса.

Между тем, можно сделать вот так:

CREATE TEMPORARY TABLE t1 (PRIMARY KEY (user_id)) SELECT user_id, sum(price) order_sum, count(*) order_count FROM product GROUP BY user_id; CREATE TEMPORARY TABLE t2 (PRIMARY KEY (user_id)) SELECT user_id, count(*) new_users FROM user WHERE user_id != 0 GROUP BY user_id; SELECT u.id, u.name, t1.order_sum, t1.order_count, t2.new_users FROM user u LEFT JOIN t1 ON t1.user_id = u.id LEFT JOIN t2 ON t2.user_id = u.id;

В этом случае все JOIN будут проходить по уникальным ключам и достаточно быстро. Кроме того таблицы будут получать read lock на более короткие промежутки времени и только по одной.

Проверка на реальных данных

На тестовом стенде была сгенерирована таблица user со 100000 записей, внешний ключ user_id есть у ~70000 записей и ссылается (с неравномерным распределением) на ~30000 записей из user. Таблица product имееет 300000 записей и ее внешний ключ user_id ссылается (с неравномерным распределением) на ~60000 записей из user.

Тесты на SSD дисках, ненагруженной MySQL версии 5.5 и идеальных только что созданных таблицах показали 10% рост производительности. На HDD дисках оптимизация более заметна - 9сек на варианте с временнными таблицами и 14 секунд без них. Немного если смотреть с точки зрения скорости, но отстуствие длогих lock-операций может дать приличный суммарный выигрыш в случае большого количества парралельных запросов. Еще одним неоспоримым приемуществом будет возможность в рамках одной MySQL-сессии использовать эти данные несколько раз.

Кстати, если у Вас есть большие статичные таблицы, то Вы можете легко ускорить работу сделав из них compressed-таблицы.

Вообще, с помощью временных таблиц можно еще, например, оптимизировать случайную выборку или ускорить выборку при больших LIMIT.