10.4.4 Internal Temporary Table Use in MySQL — MySQL 9.2

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».
Оценивание запросов 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 9.2, поддерживает бинарные объекты большого размера. См. Движок хранения временных таблиц.
Наличие любого строкового столбца с максимальной длиной более 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 автоматически преобразует внутреннюю временную таблицу в памяти во внутреннюю временную таблицу на диске InnoDB. Значение по умолчанию составляет 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 9.2 использует только двигатель хранилища 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, «Таблицы сводки памяти».

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

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

TempTable_count_hit_max_ram увеличивается, когда достигается предел temptable_max_ram. Это специфично для движка хранения TempTable и является глобальной переменной состояния.
Count_hit_tmp_table_size увеличивается в следующих условиях:
- Движок хранения TempTable: если достигается предел tmp_table_size.
- Движок хранения MEMORY: если достигается меньшее из предельных значений tmp_table_size или max_heap_table_size.
Это глобальная и сессионная переменная состояния.