No Image

Mysql создание временной таблицы

СОДЕРЖАНИЕ
204 просмотров
10 марта 2020

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

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

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

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

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

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

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

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

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

Таким образом после соединения у MySQL будет таблица длиной в 1 миллион строк и шириной в 6 столбцов. Нетрудно посчитать что в среднем каждая строчка имеет длину в 550 байт. А значит суммарный объем таблицы составит

670 Мб. После этого из таблицы будут выбраны столбцы i.src и t.name и информация объемом в

100 Мб отправлена клиенту. В том случае если значение tmp_table_size будет больше чем размер временной таблицы – запрос отработает достаточно быстро, но если же значение tmp_table_size будет недостаточным – MySQL эту же таблицу будет создавать на диске. А запись такого количества информации на диск – весьма медленная операция, получим долгий запрос, к тому же бесполезно нагружающий дисковую систему. Согласитесь, было бы правильно сначала выделить нужные столбцы, а потом уже соединить таблицы.

Читайте также:  1С сортировка в форме списка

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

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

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

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

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

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

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

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

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

На тестовом стенде из нескольких миллионов user и несколько десятков миллионов product запрос выполняется

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

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

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

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

Читайте также:  Идентификация пользователей вай фай

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

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

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

В этом случае все 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-таблицы.

чтобы создать временную таблицу, копию уже существующей, используйте выражение (несколько примеров) =

где
new_t = это имя новой таблицы
а
old_t = это имя старой (уже существующей в вашей базе данных)таблицы

База данных MySQL допускает создание временных таблиц.

Поскольку mysql не поддерживает вложенные SELECT, можно разбить один сложный SELECT на несколько простых с использованием временных таблиц.

В данном примере мы будем использовать временную таблицу для хранения IP-адресов посетителей из четырех полей:

id_ip — первичный ключ таблицы;
ip — IP-адрес посетителя;
id_user— вторичный ключ, устанавливающий дополнительную связь с таблицей посетителей users. В таблице users этот ключ выступает в качестве первичного;
puttime — время посещения.
Допустим, нам надо выбрать последний IP-адрес для каждого посетителя и время, когда он с него зашел. Можно поступить в два этапа. Сначала выбрать вторичные ключи пользователей id_user и соответствующее им время последнего посещения, а затем в цикле выбрать соответствующие им IP-адреса.

Читайте также:  Полезные виджеты для ios

Недостатком такого подхода является выполнение второго запроса в цикле,
что может создать серьезную нагрузку на сервер MySQL. Выходом из этой
ситуации будет создание временной таблицы с результатами первого запроса,
как это показано ниже.

Создание временной таблицы с результатами запроса

Как видно из листинга, выполнение второго SQL-запроса вынесено из тела
цикла. После выполнения скрипта таблица temp_table, созданная в памяти, будет
уничтожена автоматически.

Читайте другие интересные статьи

Понравилась статья, расскажи о ней друзьям, нажми кнопку!

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

Временные таблицы в MySQL версии 3.23 добавлена, если ваша версия MySQL ранее, чем версия 3.23 не может использовать временные таблицы MySQL. Но теперь есть еще один редко используют такую ​​низкую версию сервера баз данных MySQL.

MySQL временные таблицы видны только в текущем соединении, если вы используете PHP MySQL скрипт для создания временной таблицы, а затем, когда сделал выполнение PHP скрипт завершается, временная таблица автоматически уничтожается.

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

примеров

Ниже показан простой пример использования временной таблицы MySQL, следующий код SQL может быть применен к PHP скрипт mysql_query () функцию.

При использовании SHOW TABLES команду для отображения списка таблицы данных, вы не увидите SalesSummary таблицу.

Если выйти из текущего сеанса MySQL, а затем использовать команду SELECT , чтобы прочитать данные , первоначально создали временную таблицу, то вы обнаружите , что база данных не существует в таблице, так как при выходе из временной таблицы была уничтожена.

Удаление временных таблиц MySQL

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

Ниже приводится вручную удалить временный Пример таблицы:

Комментировать
204 просмотров
Комментариев нет, будьте первым кто его оставит

Это интересно
No Image Компьютеры
0 комментариев
Компьютеры
0 комментариев
No Image Компьютеры
0 комментариев
Adblock detector