Увійти Реєстрація
Вакансії
Вакансії Laravel Компанії Статистика
Підготовка
Як підготуватися Співбесіди Тестування Документація
Блог
Всі статті Серії
Екосистема
Пакети Ресурси Проєкти Інструменти Події Відео
Інше
Про нас Реклама

Що означає «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 може застосовуватися до кожної з таблиць одночасно в багатьох сесіях.

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

Схожі питання