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

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

drop temporary table if exists some_temporary_table1;  create temporary table some_temporary_table1   select t1.someColumn, max(t2.someOtherColumn) as someMax   from some_table1 t1   join some_table2 t2 using(someJoinColumn)   where t1.someConditionColumn = 'someConditionValue'   group by t1.someColumn  ;   drop temporary table if exists some_temporary_table2;  create temporary table some_temporary_table2   select tmp1.someColumn, tmp1.someMax, min(t3.someCompletelyOtherColumn) as someMin   from some_temporary_table1 tmp1   join some_table3 t3 using(someJoinColumn)   where t3.someOtherConditionColumn = 'someOtherConditionValue'   group by tmp1.someColumn, tmp1.someMax  ;   select *  from some_temporary_table2 tmp2  join some_table3 t3 using(someDifferentJoinColumn)  where t3.someFancyConditionColumn = 'someFancyConditionValue'      

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

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

drop temporary table if exists some_temporary_table2;  create temporary table some_temporary_table2   select tmp1.someColumn, tmp1.someMax, min(t3.someCompletelyOtherColumn) as someMin   from (  select t1.someColumn, max(t2.someOtherColumn) as someMax   from some_table1 t1   join some_table2 t2 using(someJoinColumn)   where t1.someConditionColumn = 'someConditionValue'   group by t1.someColumn   ) tmp1   join some_table3 t3 using(someJoinColumn)   where t3.someOtherConditionColumn = 'someOtherConditionValue'   group by tmp1.someColumn, tmp1.someMax  

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

select *  from (  select tmp1.someColumn, tmp1.someMax, min(t3.someCompletelyOtherColumn) as someMin   from (  select t1.someColumn, max(t2.someOtherColumn) as someMax   from some_table1 t1   join some_table2 t2 using(someJoinColumn)   where t1.someConditionColumn = 'someConditionValue'   group by t1.someColumn   ) tmp1   join some_table3 t3 using(someJoinColumn)   where t3.someOtherConditionColumn = 'someOtherConditionValue'   group by tmp1.someColumn, tmp1.someMax  ) tmp2  join some_table3 t3 using(someDifferentJoinColumn)  where t3.someFancyConditionColumn = 'someFancyConditionValue'      

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

 $subQuery1 = "   select t1.someColumn, max(t2.someOtherColumn) as someMax   from some_table1 t1   join some_table2 t2 using(someJoinColumn)   where t1.someConditionColumn = 'someConditionValue'   group by t1.someColumn  ";$ subQuery2 = "   select tmp1.someColumn, tmp1.someMax, min(t3.someCompletelyOtherColumn) as someMin   from ({ $subQuery1}) tmp1   join some_table3 t3 using(someJoinColumn)   where t3.someOtherConditionColumn = 'someOtherConditionValue'   group by tmp1.someColumn, tmp1.someMax  ";$ mainQuery = "   select *   from ({$subQuery2}) tmp2   join some_table3 t3 using(someDifferentJoinColumn)   where t3.someFancyConditionColumn = 'someFancyConditionValue'  ";  

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

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


dba.stackovernet.com

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

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

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


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

    id mysql

    У нас это 207

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

    tmpfs /var/lib/mysql/tmp_table tmpfs rw,gid=207,uid=207,size=14G,nr_inodes=10k,mode=0700 0 0
  • После успешного сообщение выполняем монтирование нашего нового раздела:

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

    tmpdir=/var/lib/mysql/tmp_table

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


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

artkiev.com

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

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

CREATE TABLE SELECT и CREATE TABLE.

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

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

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

    CREATE TEMPORARY TABLE core.my_tmp_table  (INDEX my_index_name (tag, time), UNIQUE my_unique_index_name (order_number)) SELECT * FROM core.my_big_table WHERE my_val = 1 

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

    CREATE TEMPORARY TABLE core.my_tmp_table  (PRIMARY KEY my_pkey (order_number), INDEX cmpd_key (user_id, time)) SELECT * FROM core.my_big_table 
  • Создать дополнительные столбцы

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

    CREATE TEMPORARY TABLE core.my_tmp_table  (my_new_id BIGINT NOT NULL AUTO_INCREMENT,  PRIMARY KEY my_pkey (my_new_id), INDEX my_unique_index_name (invoice_number)) SELECT * FROM core.my_big_table 
  • Переопределение типов данных для столбцов с помощью SELECT

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

    CREATE TEMPORARY TABLE core.my_tmp_table  (tag BIGINT, my_time DATETIME,  INDEX my_unique_index_name (tag) ) SELECT * FROM core.my_big_table 

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

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

    CREATE TEMPORARY TABLE core.my_tmp_table  (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, value BIGINT UNSIGNED NOT NULL DEFAULT 0 UNIQUE, location VARCHAR(20) DEFAULT "NEEDS TO BE SET", country CHAR(2) DEFAULT "XX" COMMENT "Two-letter country code",  INDEX my_index_name (location)) ENGINE=MyISAM  SELECT * FROM core.my_big_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

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

CREATE TEMPORARY TABLE new_t LIKE old_t;
CREATE TEMPORARY TABLE 123_avto AS (  SELECT *  FROM avto  );

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

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

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

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

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

Выбор IP-адреса каждого посетителя и времени посещения  Устанавливаем соединение с базой данных  include "config.php";  // Формируем SQL-запрос  $query ="SELECT DISTINCT(id_user) AS id_user,  MAX(puttime) AS max_time  FROM ips  GROUP BY id_user";  // Выполняем SQL-запрос  $first = mysql_query($query);  if($first)  {  while($res = mysql_fetch_array($first))  {  // В цикле формируем второй SQL-запрос  $query ="SELECT * FROM ips  WHERE id_user = ". $res[`id_user`]."  AND puttime=".$res[`max_time`];  $final = mysql_query($query);  $result = mysql_fetch_array($final);  echo $result[`ip`]."-".$result[`puttime`]." ";  }  }  ?>

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

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

 $query =&quot;CREATE TEMPORARY TABLE temp_table  SELECT id_user, puttime, ip FROM ips  ORDER BY id_user,time DESC&quot;;  if(mysql_query($query))  {  $sel =&quotSELECT id_user, puttime, ip  FROM temp_table GROUP BY id_user";  $res = mysql_query($sel);  if ($res)  {  while($result = mysql_fetch_array($res))  {  echo $result["id user"]." ".$resultf"puttime"]." ".$result["ip"].&quot ”;  }  }  }  ?>

Как видно из листинга, выполнение второго 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. Обычно эти две переменные конфигурации имеют одинаковое значение.

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

mysql> show global status like 'Created%';  +-------------------------+-------+  | Variable_name | Value |  +-------------------------+-------+  | Created_tmp_disk_tables | 20 |  | Created_tmp_files | 6 |  | Created_tmp_tables | 43 |  +-------------------------+-------+  

Примечание В этом примере, были созданы 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 для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.

Adblock
detector