Временные таблицы mysql


Иногда я использую временные таблицы для создания сложных отчетов. Относительно простой (и бессмысленно) примером может быть что-то вроде

Тот же результат может быть достигнут без временных таблиц, устраняя их шаг за шагом.

Шаг 1 — устранить some_temporary_table1, заменив его появление с его создающего код:

Шаг 2 — исключить some_temporary_table2

Если больше временных таблиц вовлечены, это может в конечном итоге в очень нечитаемый (и недостижимый) код. Для того, чтобы сохранить свой код читаемым вы можете создать запрос в PHP с использованием строковых переменных для подзапросов:

, который похож на оригинальный временное решение таблицы.

С точки зрения производительности — это обычно не имеет большого значения. Иногда временные таблицы-решения быстрее. Иногда это однозадачное решение. Но в основном это не более 10%. В старом примере, который я только что протестировал, одно-заявление-решение было немного быстрее, , хотя я дал несколько советов, как использовать меньшие типы данных (smallint вместо bigint), создание индексов на временных таблицах для использования следующее соединение и использование временной таблицы дважды. В другом примере, который я помню с работы, временные таблицы-решения были быстрее, , хотя я не использовал никаких намеков.


dba.stackovernet.com

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

В одном проекте, требовалось создать выборку, которая создавалась огромным запросом с подзапросами. Вес временной таблицы был 2ГБ и если одновременно два администратора начинали строить нужную им выборку — сайт «замирал». Проанализировав данный запрос, сделали вывод, что ускорить эту выборку можно разбитием на несколько мелких и несколько крупных запросов и остальную работу переложить на PHP. Действительно, на некоторое время оптимизация успешно удалась. Но через полгода данные накопились приличные и все вернулось на тоже место.
После недолгих раздумий и анализа самого железа, решили установить на сервер ssd-винчестер специально под базу данных и немного изменить использование памяти. Удалось осободить 14ГБ свободной оперативки и было решено переместить временные таблицы в эти самые 14ГБ неиспользованной оперативной памяти.

Итак, что бы сервер MySQL хранил временные таблицы в оперативной памяти нужно:


  • Создаем папку для хранения временных файлов /var/lib/mysql/tmp_table
  • Делаем mysql владельцем этой папки:
  • Узнаем uid-пользователя и gid-группы mysql:

    У нас это 207

  • В файле fstab добавляем следующую запись:

  • После успешного сообщение выполняем монтирование нашего нового раздела:

  • Вносим правки в файл конфигурации MySQL. По умолчанию это my.cnf (у вас может быть другой)

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


Мы после этих манипуляций, добились того, что выборка строилась в 6 раз быстрее, при этом не зажирая все ресурсы под себя и таким образом удалось избавиться от «зависания сайта». Спустя 2 месяца работы — временные таблицы уже достигали почти 8ГБ и нельзя сказать, что скорость заметно упала по сравнению с 6ГБ. В ближайшее время сервер увеличит свою оперативную память и на ближайших 2-3 года его хватит с головой.

artkiev.com

Я долго боролся с правильным синтаксисом для CREATE TEMPORARY TABLE SELECT. Выяснив несколько вещей, я хотел поделиться ответами с остальной частью сообщества.

Основная информация об этом операторе доступна в следующих ссылках MySQL:

CREATE TABLE SELECT и CREATE TABLE.

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

  • Добавить несколько индексов

    Этот оператор показывает, как добавить несколько индексов (обратите внимание, что имена индексов — в нижнем регистре — являются необязательными):


  • Добавить новый первичный ключ:

  • Создать дополнительные столбцы

    Вы можете создать новую таблицу с большим количеством столбцов, чем указано в инструкции SELECT. Укажите дополнительный столбец в определении таблицы. Столбцы, указанные в определении таблицы и не найденные в select, будут первыми столбцами в новой таблице, за которыми следуют столбцы, вставленные оператором SELECT.

  • Переопределение типов данных для столбцов с помощью SELECT

    Вы можете переопределить тип данных для столбца SELECT. В приведенном ниже примере тег столбца является MEDIUMINT в core.my_big_table, и я переопределяю его на BIGINT в файле core.my_tmp_table.


  • Расширенные определения полей при создании

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

qaru.site

Временные таблицы создаются с помощью оператора CREATE TEMPORARY TABLE. Синтаксис оператора ничем не отличается от оператора CREATE TABLE. Заполнить временную таблицу можно обычным способом, но чаще всего временные таблицы заполняют с помощью вложенных запросов. Например, временные таблицы используются для реализации дополнительного поиска в результатах выполнения запроса. Следует помнить, что имя временной таблицы действительно только в течение текущего соединения с сервером. По завершении соединения с сервером временная таблица автоматически удаляется.

 

В качестве примера создадим таблицу user_table в базе данных tests.
CREATE TABLE `user_table` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255),
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;


 
В поле name мы будем хранить фамилию и имя пользователя. Добавим в таблицу несколько записей:
INSERT INTO `user_table` VALUES (NULL, ‘Иванов Сергей’);
INSERT INTO `user_table` VALUES (NULL, ‘Иванов Николай’);
INSERT INTO `user_table` VALUES (NULL, ‘Иванов Иван’);
INSERT INTO `user_table` VALUES (NULL, ‘Петров Александр’);
INSERT INTO `user_table` VALUES (NULL, ‘Петров Николай’);
INSERT INTO `user_table` VALUES (NULL, ‘Иванов Максим’);

 
А теперь инсценируем ситуацию поиска в найденном с помощью временных таблиц. Предположим, что первоначальный запрос пользователя выводит клиентов с фамилией Иванов:
SELECT `id`, `name` FROM `user_table` WHERE `name` LIKE ‘%Иванов%’;

 
Сохраним результат запроса во временной таблице, а затем выведем клиентов только с именем Николай:
CREATE TEMPORARY TABLE `temp`
SELECT `id`, `name` FROM `user_table` WHERE `name` LIKE ‘%Иванов%’;
SELECT `name` FROM `temp` WHERE `name` LIKE ‘%Николай%’;
/* Выведет: Иванов Николай */

 
Обратите внимание: при использовании вложенных запросов не нужно определять структуру временной таблицы. По умолчанию структура временной таблицы будет такой же, как и в результирующей таблице. Посмотреть структуру временной таблицы можно с помощью оператора DESCRIBE:
CREATE TEMPORARY TABLE `temp2`
SELECT `id`, `name` FROM `user_table` WHERE `name` LIKE ‘%Иванов%’;
DESCRIBE `temp2`;


 
Удалить временную таблицу можно следующими способами:
□ с помощью оператора DROP TABLE:
DROP TABLE <Имя временной таблицы>;
□ по завершении соединения с сервером временная таблица будет удалена автоматически.

xoops.ws

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

Многие разработчики рано или поздно при работе с 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_id имеют тип INT и занимают по 4 байта. Столбцы src и src имеют тип varchar(64) и занимают в среднем по 40 байт. А столбец wiki_info имеет тип varchar(1024) и занимает в среднем 500 байт. В таблице image 1 миллион строк, а в таблице type 3 строки. Нетрудно посчитать что image занимает на диске порядка 50 Мб пространства, а type около полутора килобайт.

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

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

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

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

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

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

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

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

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

Время выполнения — ~ 11,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 на более короткие промежутки времени и только по одной.

my2i.ru

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

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

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

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

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

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

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

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

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

adminunix.ru

Today’s:

  • Linux Смена пользователя в консоли (терминал) — Как зайти под пользователем (перелогиниться)
  • [!] Паскаль — Онлайн Учебник для начинающих изучать программирование с нуля. Справочник. Примеры кода. [Pascal]
  • "Черта над" символом — что означает в математике. Значения
  • #3 Типы данных в языке Pascal. Объявление переменных. Целые числа integer. Оператор присваивания. Инициализация в Паскаль
  • telegram Сделать админом в группе (чате) — как дать права
  • [!] LaTeX СПРАВОЧНИК — Примеры символов, кода, обозначений и команд [латекс, латех]
  • #12.1 Цикл Repeat/Until "с постусловием" в Паскаль
  • #10 Цикл For в Паскаль. Цикл с известным числом повторений (цикл с параметром). Тело цикла.
  • latex Cистема уравнений. Фигурная скобка — пример команд
  • Деплой — что это в программировании (deploy)
  • .bat комментарии — как комментировать код, примеры
  • latex Матрица и скобки: квадратные, фигурные, круглые скобками, двойной модуль (норма) — определитель латех
  • python Число элементов в списке ("массиве") — длина списка — len — как узнать
  • Рынок земли. Спрос и предложение земли. Рента и ее формы. Цена земли.
  • #14 Процедуры и функции в Паскаль. Как объявлять и использовать
  • Умножения матрицы на вектор, матрицы на матрицу формула — пример (правило с пояснением)
  • Буквы греческого алфавита латекс LaTeX — таблица всех букв греческого алфавита
  • Преобразовать строку в число. Пример использования процедуры Val() в Паскаль
  • Latex размер шрифта текста
  • Урок #3 — Ввод/Вывод данных в Pascal — Write(). Writeln(), Read(), Readln() — отличия, примеры использования
  • latex Больше или равно Меньше или равно — команды, "как выводится" (с прямой и обычной чертой равенства)
  • latex Черта над (линия сверху) — "вектор", замыкание — пример комады, кода
  • Стругацкие список произведений
  • [!] Select2 по-русски. Справочник и примеры работы со знаменитым JQuery-плагином
  • latex Символ эквивалентности — тройное равно, тройной знак равенства — три черты
  • #13.1 Вложенные циклы в Паскаль. Примеры кода, решения задач — For, While, Repeat/Until
  • #1 Начало программирования. Минимальная программа. Комментарии в коде. Тело программы и операторные скобки. Паскаль
  • Pascal Получить случайное число в диапазоне — пример
  • #9 Условный оператор IF (оператор ветвления if else) в Паскаль. Полная и неполная формы условного оператора. Вложенность условий
  • linux Как подключиться по SSH к компьютеру (серверу) по сети (через командную строку терминал)

fkn.ktu10.com

«Использование временного» в EXPLAIN отчет не говорит нам о том, что таблица температура была на диске. Это говорит только о том, что в запросе предполагается создать временную таблицу.

Таблица временных температур останется в памяти, если ее размер меньше, чем tmp_table_size и меньше, чем max_heap_table_size.

Max_heap_table_size — самая большая таблица, которая может находиться в механизме хранения MEMORY, независимо от того, является ли эта таблица временным или нетемпинговой таблицей.

Tmp_table_size — самая большая таблица, которая может быть в памяти, когда она создается автоматически по запросу. Но в любом случае это не может превышать max_heap_table_size. Таким образом, нет никакой пользы для установки tmp_table_size больше, чем max_heap_table_size. Обычно эти две переменные конфигурации имеют одинаковое значение.

Вы можете отслеживать, как были созданы много временных таблиц, и сколько на диске, как это:

Примечание В этом примере, были созданы 43 временных таблиц, но только 20 из них были на диске.

Когда вы увеличиваете пределы tmp_table_size и max_heap_table_size, вы позволяете создавать большие временные таблицы в памяти.

Вы можете спросить, насколько вам это необходимо сделать? Вам не обязательно нужно сделать его достаточно большим, чтобы каждая отдельная таблица темпов соответствовала памяти. Вы можете захотеть, чтобы 95% ваших временных таблиц соответствовали памяти, и только оставшиеся редкие таблицы выходят на диск. Те последние 5% могут быть очень большими — намного больше, чем объем памяти, который вы хотите использовать для этого.

Так что моя практика заключается в том, чтобы сохранить tmp_table_size и max_heap_table_size консервативно. Затем посмотрите из Created_tmp_disk_tables в Created_tmp_tables, чтобы узнать, выполнила ли я свою задачу: 95% из них остаются в памяти (или независимо от того, какое отношение я хочу видеть).

К сожалению, у MySQL нет хорошего способа рассказать вам, насколько велики таблицы temp. Это будет зависеть от каждого запроса, поэтому переменные состояния не могут показать это, они могут показывать только количество попыток. И EXPLAIN фактически не выполняет запрос, поэтому он не может точно предсказать, сколько данных он будет соответствовать.

Альтернатива Percona Server, которая представляет собой распределение MySQL с улучшениями. Один из них — log extra information in the slow-query log. В дополнительные поля входит размер любых временных таблиц, созданных данным запросом.

stackoverrun.com


You May Also Like

About the Author: admind

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.