10.4.4 Internal Temporary Table Use in MySQL — MySQL 8.4

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

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

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

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

Чтобы определить, требует ли запрос временную таблицу, используйте EXPLAIN и проверьте столбец Extra, чтобы увидеть, написано ли там Using temporary (см. Раздел 10.8.1, «Оптимизация запросов с помощью EXPLAIN»). EXPLAIN не обязательно указывает на необходимость Using temporary для производных или материализованных временных таблиц. Для запросов, использующих оконные функции, EXPLAIN с FORMAT=JSON всегда предоставляет информацию о шагах оконного анализа. Если оконные функции используют временные таблицы, это указывается для каждого шага.

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

Наличие столбца BLOB или TEXT в таблице. Слой хранения TempTable, который является стандартным слоем хранения для внутренних временных таблиц в MySQL 8.4, поддерживает типы больших двоичных объектов. См. Слой хранения временных таблиц.
Наличие любого строкового столбца с максимальной длиной более 512 (байтов для бинарных строк, символов для небинарных строк) в списке SELECT, если используется UNION или UNION ALL.
Команды SHOW COLUMNS и DESCRIBE используют BLOB в качестве типа для некоторых столбцов, поэтому временная таблица для результатов — таблица на диске.

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

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

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

END_OF_DOCUMENT_MARKER

Механизм хранения внутренних временных таблиц

Внутренняя временная таблица может храниться в оперативной памяти и обрабатываться механизмом хранения TempTable или MEMORY, или храниться на диске с помощью механизма хранения InnoDB.

Механизм хранения для внутренних временных таблиц в памяти

Переменная internal_tmp_mem_storage_engine определяет механизм хранения, используемый для внутренних временных таблиц в оперативной памяти. Разрешённые значения — TempTable (по умолчанию) и MEMORY.

Примечание

Для настройки сессионного параметра internal_tmp_mem_storage_engine требуется привилегия SESSION_VARIABLES_ADMIN или SYSTEM_VARIABLES_ADMIN.

Механизм хранения TempTable обеспечивает эффективное хранение столбцов типа VARCHAR и VARBINARY, а также других типов бинарных больших объектов.

Следующие переменные контролируют ограничения и поведение механизма хранения TempTable:

tmp_table_size: Определяет максимальный размер любой отдельной внутренней временной таблицы в памяти, созданной с помощью механизма хранения TempTable. Когда достигается предел, определенный параметром tmp_table_size, MySQL автоматически преобразует внутреннюю временную таблицу в оперативной памяти во внутреннюю временную таблицу на диске. Значение по умолчанию — 16777216 байтов (16 МБ).

Ограничение tmp_table_size предназначено для предотвращения чрезмерного потребления отдельных запросов глобальных ресурсов TempTable, что может повлиять на производительность одновременных запросов, требующих таких ресурсов. Глобальные ресурсы TempTable контролируются параметрами temptable_max_ram и temptable_max_mmap.

Если tmp_table_size меньше temptable_max_ram, то временная таблица в памяти не может использовать больше, чем tmp_table_size. Если tmp_table_size больше суммы temptable_max_ram и temptable_max_mmap, временная таблица в памяти не может использовать больше, чем сумма ограничений temptable_max_ram и temptable_max_mmap.
temptable_max_ram: Определяет максимальный объём оперативной памяти, который может быть использован механизмом хранения TempTable, прежде чем он начнёт выделять место из файлов с отображением памяти или прежде чем MySQL начнёт использовать InnoDB на диске внутренних временных таблиц, в зависимости от вашей конфигурации. Если явно не задано, значение temptable_max_ram составляет 3% от общего объёма доступной памяти на сервере, с минимальным значением 1 ГБ и максимальным 4 ГБ.

Примечание

temptable_max_ram не учитывает выделенный каждому потоку локальный блок памяти, использующий механизм хранения TempTable. Размер локального блока памяти потока зависит от размера первого запроса выделения памяти потоком. Если запрос меньше 1 МБ, что обычно и есть, размер локального блока памяти потока составляет 1 МБ. Если запрос больше 1 МБ, размер локального блока памяти потока примерно равен размеру начального запроса выделения памяти. Локальный блок памяти потока хранится в локальном хранилище потока до выхода потока.
temptable_use_mmap: Управляет тем, выделяет ли механизм хранения TempTable место из файлов с отображением памяти или MySQL использует InnoDB на диске внутренних временных таблиц, когда предел, определённый параметром temptable_max_ram, превышен. Значение по умолчанию — OFF.

Примечание

temptable_use_mmap устарело; ожидается, что поддержка будет удалена в будущей версии MySQL. Установка temptable_max_mmap=0 эквивалентна установке temptable_use_mmap=OFF.
temptable_max_mmap: Устанавливает максимальный объём памяти, который механизм хранения TempTable разрешено выделить из файлов с отображением памяти, прежде чем MySQL начнёт использовать InnoDB на диске внутренних временных таблиц. Значение по умолчанию — 0 (выключено). Ограничение предназначено для решения проблем с использованием файлов с отображением памяти для слишком большого пространства в временном каталоге (tmpdir). temptable_max_mmap = 0 отключает выделение из файлов с отображением памяти, фактически отключая их использование, независимо от значения temptable_use_mmap.

Использование файлов с отображением памяти механизмом хранения TempTable регулируется следующими правилами:

Временные файлы создаются в каталоге, определённом переменной tmpdir.
Временные файлы удаляются сразу после создания и открытия, и поэтому не остаются видимыми в каталоге tmpdir. Занимаемое пространство временных файлов сохраняется операционной системой, пока временные файлы открыты. Пространство освобождается, когда временные файлы закрываются механизмом хранения TempTable или когда процесс mysqld завершается.
Данные никогда не перемещаются между ОЗУ и временными файлами, внутри ОЗУ или между временными файлами.
Новые данные сохраняются в ОЗУ, если освобождается место в пределах ограничения, определённого параметром temptable_max_ram. В противном случае новые данные сохраняются во временных файлах.
Если в ОЗУ освобождается место после записи части данных таблицы во временные файлы, то оставшиеся данные таблицы могут быть сохранены в ОЗУ.

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

Механизм хранения для внутренних временных таблиц на диске

MySQL 8.4 использует только механизм хранения InnoDB для внутренних временных таблиц на диске. (Механизм хранения MYISAM больше не поддерживается для этой цели.)

Внутренние временные таблицы на диске InnoDB создаются в сессионных временных таблицах, которые по умолчанию находятся в каталоге данных. Дополнительную информацию см. в Разделе 17.6.3.5, «Временные табличные пространства».

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

Когда внутренние временные таблицы в оперативной памяти управляются движком хранения TempTable, строки, содержащие столбцы VARCHAR, столбцы VARBINARY и другие столбцы типа бинарных больших объектов, представляются в памяти массивом ячеек, каждая из которых содержит флаг NULL, длину данных и указатель на данные. Значения столбцов размещаются в последовательном порядке после массива, в одном регионе памяти, без заполнения. Каждая ячейка массива использует 16 байт памяти. Тот же формат хранения применяется, когда движок хранения TempTable выделяет память из файлов с отображением памяти.

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

Внутренние временные таблицы на диске всегда управляются InnoDB.

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

Мониторинг создания внутренних временных таблиц

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

Примечание

Из-за известного ограничения, Created_tmp_disk_tables не учитывает временные таблицы на диске, созданные в файлах с отображением памяти. По умолчанию механизм переполнения движка TempTable создает внутренние временные таблицы в файлах с отображением памяти. См. Движок хранения внутренних временных таблиц.

Инструменты memory/temptable/physical_ram и memory/temptable/physical_disk Performance Schema могут использоваться для мониторинга выделения памяти и дискового пространства TempTable. memory/temptable/physical_ram сообщает о количестве выделенной оперативной памяти. memory/temptable/physical_disk сообщает о количестве выделенного дискового пространства, когда файлы с отображением памяти используются в качестве механизма переполнения TempTable. Если инструмент physical_disk сообщает значение отличное от 0 и файлы с отображением памяти используются в качестве механизма переполнения TempTable, в какой-то момент был достигнут предел памяти TempTable. Данные можно запросить в таблицах сводного отчета памяти Performance Schema, таких как memory_summary_global_by_event_name. См. Раздел 29.12.20.10, «Таблицы сводного отчета памяти».