Що означає «Using temporary» в EXPLAIN і коли внутрішня тимчасова таблиця йде на диск?

Для деяких запитів MySQL створює внутрішню тимчасову таблицю - проміжне сховище результату. В EXPLAIN це Using temporary в Extra, у TREE-форматі - вузли на кшталт Aggregate using temporary table чи Materialize.

Коли вона з'являється:

• GROUP BY, який не можна виконати по порядку індексу;
• GROUP BY і ORDER BY за різними колонками (згрупувати, потім пересортувати);
• DISTINCT разом із ORDER BY;
• UNION (з дедуплікацією; UNION ALL у більшості випадків обходиться без неї);
• матеріалізація похідних таблиць, CTE, підзапитів;
• віконні функції;
• багатотабличний UPDATE, що читає з таблиці, яку змінює.

Де вона живе. За замовчуванням внутрішні тимчасові таблиці в пам'яті створює рушій TempTable. Ліміт - temptable_max_ram (у MySQL 8.4 за замовчуванням 3% оперативної пам'яті в межах 1-4 ГБ) на весь сервер, а не на запит. Коли він вичерпаний, дані йдуть на диск - у внутрішні таблиці InnoDB.

Також на окрему таблицю діє tmp_table_size - перевищення переводить на диск саме її.

Як побачити, що тимчасові таблиці йдуть на диск:

SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-- Created_tmp_tables       - усього внутрішніх тимчасових таблиць
-- Created_tmp_disk_tables  - з них на диску

SELECT * FROM sys.statements_with_temp_tables
ORDER BY disk_tmp_tables DESC LIMIT 10;   -- які запити винні

Зростання частки дискових таблиць - сигнал розібратися з конкретними запитами.

Як позбутися тимчасової таблиці (краще, ніж збільшувати ліміти):

• індекс під GROUP BY: якщо групування йде за колонками індексу після рівностей з WHERE, MySQL групує на льоту в порядку індексу;
• однакові GROUP BY і ORDER BY: від MySQL 8.0 GROUP BY не сортує неявно, тож ORDER BY за тими самими колонками дешевий;
• менше колонок у проміжному результаті: SELECT * з TEXT-полями роздуває тимчасову таблицю;
• UNION ALL замість UNION, коли дублікати неможливі чи не важливі;
• агрегувати до з'єднання: спершу згрупувати велику таблицю в похідній таблиці, потім з'єднати з довідниками.

Коли збільшувати ліміти: якщо аналітичні запити на репліці регулярно йдуть на диск і переписати їх не вдається. Підвищувати варто разом із контролем загальної пам'яті: ліміт TempTable глобальний, а tmp_table_size може застосовуватися до кожної з таблиць одночасно в багатьох сесіях.

Докладніше в документації: Внутрішні тимчасові таблиці