8.4.4 Internal Temporary Table Use in MySQL — MySQL 5.7

8.4.4 Использование внутренних временных таблиц в MySQL

В некоторых случаях сервер создаёт внутренние временные таблицы во время обработки запросов. Пользователи не имеют прямого контроля над моментами их создания.

Сервер создаёт временные таблицы в следующих условиях:

Оценка запросов UNION, за исключением случаев, описанных далее.
Оценка некоторых представлений, таких как те, которые используют алгоритм TEMPTABLE, UNION, или агрегацию.
Оценка производных таблиц (см. Раздел 13.2.10.8, «Производные таблицы»).
Таблицы, созданные для материализации подзапросов или полусоединений (см. Раздел 8.2.2, «Оптимизация подзапросов, производных таблиц и ссылок на представления»).
Оценка запросов, содержащих предложение ORDER BY и другое предложение GROUP BY, или запросов, в которых ORDER BY или GROUP BY содержат столбцы из таблиц, отличных от первой таблицы в очереди соединения.
Оценка DISTINCT в сочетании с ORDER BY может потребовать временную таблицу.
Для запросов, использующих модификатор SQL_SMALL_RESULT, MySQL использует временную таблицу в оперативной памяти, если запрос не содержит элементов (описанных далее), требующих хранения на диске.
Для оценки запросов INSERT ... SELECT, которые выбирают и вставляют данные в одну и ту же таблицу, MySQL создаёт внутреннюю временную таблицу для хранения строк из SELECT, а затем вставляет эти строки в целевую таблицу. См. Раздел 13.2.5.1, «Запрос INSERT ... SELECT».
Оценка запросов обновления UPDATE с несколькими таблицами.
Оценка выражений GROUP_CONCAT() или COUNT(DISTINCT).

Чтобы определить, требует ли запрос временную таблицу, используйте EXPLAIN и проверьте столбец Extra, чтобы увидеть, содержит ли он значение Using temporary (см. Раздел 8.8.1, «Оптимизация запросов с помощью EXPLAIN»). EXPLAIN не обязательно говорит о Using temporary для производных или материализованных временных таблиц.

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

Наличие столбца BLOB или TEXT в таблице. Это включает пользовательские переменные со строковым значением, так как они обрабатываются как столбцы BLOB или TEXT, в зависимости от того, является ли их значение бинарной или небинарной строкой соответственно.
Наличие любого строкового столбца с максимальной длиной более 512 (байт для бинарных строк, символов для небинарных строк) в списке SELECT, если используется UNION или UNION ALL.
Запросы SHOW COLUMNS и DESCRIBE используют BLOB в качестве типа для некоторых столбцов, поэтому временная таблица для результатов будет таблицей на диске.

Сервер не использует временную таблицу для UNION запросов, удовлетворяющих определённым условиям. Вместо этого он сохраняет только необходимые структуры данных для преобразования типов столбцов результата из временной таблицы. Таблица не полностью инициализирована, и данные не записываются в неё и не считываются из неё; строки отправляются непосредственно клиенту. Это уменьшает потребность в памяти и диске, а также задержку перед отправкой первой строки клиенту, так как серверу не нужно ждать выполнения последнего блока запроса. EXPLAIN и вывод отслеживания оптимизатора отражают эту стратегию выполнения: блок запроса UNION RESULT отсутствует, так как этот блок соответствует части, которая считывает данные из временной таблицы.

Эти условия позволяют оценить UNION без временной таблицы:

Объединение является UNION ALL, а не UNION или UNION DISTINCT.
Отсутствует глобальное предложение ORDER BY.
Объединение не является верхним блоком запроса для запроса {INSERT | REPLACE} ... SELECT ....

Двигатель хранения внутренних временных таблиц

Внутренняя временная таблица может храниться в памяти и обрабатываться двигателем хранения MEMORY или храниться на диске двигателем хранения InnoDB или MyISAM.

Если внутренняя временная таблица создана как таблица в оперативной памяти, но становится слишком большой, MySQL автоматически преобразует её в таблицу на диске. Максимальный размер временных таблиц в оперативной памяти определяется значением tmp_table_size или max_heap_table_size, в зависимости от меньшего значения. Это отличается от таблиц MEMORY, явно созданных с помощью CREATE TABLE. Для таких таблиц размер ограничен только переменной max_heap_table_size, и преобразования в формат на диске не происходит.

Переменная internal_tmp_disk_storage_engine определяет, какой двигатель хранения использует сервер для управления временными таблицами на диске. Разрешенные значения: INNODB (по умолчанию) и MYISAM.

Примечание

При использовании internal_tmp_disk_storage_engine=INNODB запросы, генерирующие временные таблицы на диске, превышающие пределы строк или столбцов InnoDB, возвращают ошибки Размер строки слишком большой или Слишком много столбцов. Решением является изменение internal_tmp_disk_storage_engine на MYISAM.

При создании внутренней временной таблицы в памяти или на диске сервер увеличивает значение Created_tmp_tables. При создании внутренней временной таблицы на диске сервер увеличивает значение Created_tmp_disk_tables. Если создаётся слишком много временных таблиц на диске, рассмотрите увеличение параметров tmp_table_size и max_heap_table_size.

Формат хранения внутренних временных таблиц

Временные таблицы в оперативной памяти управляются двигателем хранения MEMORY, который использует формат строк фиксированной длины. Значения столбцов VARCHAR и VARBINARY дополняются до максимальной длины столбца, фактически храня их как столбцы CHAR и BINARY.

Временные таблицы на диске управляются двигателями хранения InnoDB или MyISAM (в зависимости от настройки internal_tmp_disk_storage_engine). Оба двигателя хранят временные таблицы в формате строк переменной ширины. Столбцы занимают только необходимое пространство, что снижает ввод/вывод на диск, требования к пространству и время обработки по сравнению с таблицами на диске, использующими строки фиксированной длины.

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