SQL-Ex blog

Что такое временные таблицы?

В MySQL временная таблица представляет собой специальный тип таблицы, которая (как вы можете догадаться) содержит временные данные. Этот тип таблиц обычно создается автоматически и, как правило, в случае возникновения определенного типа проблем - например, когда выполняются операторы ALTER TABLE на огромных наборах данных.

Скажем, мы выполняем запрос ALTER TABLE для добавления индекса к таблице с 100 миллионами или более записей. MySQL создает временную таблицу (назовем ее temp_table) и копирует туда все данные из исходной таблицы (назовем ее demo_table). Затем воссоздаются данные из исходной таблицы (demo_table) во временной таблице (temp_table), и создаются все индексы, необходимые для demo_table в temp_table, прежде чем поменять их местами. Сбивает с толку? Так не должно быть. Видите ли, MySQL выполняет все эти операции, чтобы добиться максимальной эффективности! Эффективность - часто одна из главных причин, почему администраторы MySQL упоминают временных таблицы в разговоре со своими коллегами-разработчиками, некоторые из которых замечают, что нет единого способа узнать, когда MySQL создаст временные таблицы, что не совсем неверно.

Когда создаются временные таблицы?

В MySQL временные таблицы создаются, когда:

1. Выполняются операторы ALTER TABLE для огромных наборов данных (обратитесь к примеру выше).


2. Выполняются операторы UPDATE сразу на нескольких таблицах.


3. Вам потребуются некоторые уникальные (DISTINCT) значения, и потребуется упорядочить их определенным образом.


4. Потребуется подсчитать число уникальных (DISTINCT) значений, существующих в таблице.


5. Если обратиться к документации MySQL, мы увидим, что MySQL использует временные таблицы в некоторых других сценариях.

Теперь, когда вы знаете, когда используются временные таблицы, рассмотрим несколько примеров. Обычно временные таблицы будут использоваться MySQL, когда выполняются, например, такие запросы:

1. ALTER TABLE demo_table ADD INDEX demo_idx(demo_column);




2. UPDATE [LOW_PRIORITY] [IGNORE] demo_table, demo_table2 SET demo_table.demo_column = 'Demo Value', demo_table2.column = 'Demo'




3. SELECT DISTINCT demo_column ORDER BY id;




4. SELECT id, COUNT(DISTINCT order) FROM demo_table;

Всякий, кто работал с базой данных MySQL, наверняка использовал хотя бы один из подобных запросов.

Избежать создания временных таблиц?

Некоторые инженеры MySQL могли бы сказать, что было бы неплохой идеей вообще предотвратить создание временных таблиц. Однако это проще сказать, чем сделать - особенно, если вы запускаете экземпляр базы данных на медленных дисках и (или) с большим количеством данных. Тем не менее, все же имеется пара вещей, которые вы можете сделать. Например, если вы хотите разобраться с этим, то можете использовать диск, назначенный как «RAM-диск», и сказать MySQL, чтобы он помещал туда все свои временные данные. Поскольку объем диска должен быть больше, чем объем имеющейся у вас памяти, операции, как правило, выполняются быстрее. Укажите в этом параметре путь к размещаемому RAM-диску:

tmpdir = /var/bin/mysql/temp

Другой способ — использовать только необходимые данные перед выполнением каких-либо операций, требующих использования временных таблиц. Например, если у вас имеется сотня миллионов или более записей, и вы уверены, что не будете использовать некоторые из них (скажем, вы не будете использовать данные из конкретного столбца, но вы не слишком уверены, как пропустить эту операцию, поэтому вы так или иначе загружаете данные в столбец). Тут, вероятно, было бы целесообразно загружать данные только в определенный столбец, а не во все сразу - для этого вы могли бы использовать функцию, предлагаемую LOAD DATA INFILE, и загрузить данные только в один или пару столбцов, например, так:

LOAD DATA INFILE '/directory/here/file.txt' IGNORE INTO TABLE demo_table FIELDS TERMINATED BY ':' (demo_column);

Обратите внимание на части запроса, выделенные жирным: ключевое слово IGNORE будет игнорировать любые ошибки, а часть demo_column позволит загружать данные только в один столбец: demo_column.

И, наконец, если временные таблицы действуют вам на нервы, вы можете также создать пустую таблицу, переместить данные в нее из вашей таблицы, удалить старую таблицу и переименовать вашу новую таблицу. Например, если ваша исходная таблица называется demo_table:

1. Создайте новую таблицу с именем demo_table_new.


2. Вручную переместите данные из исходной таблицы в demo_table_new. Для более быстрого и массового импорта вы можете использовать также LOAD DATA INFILE.


3. Удалите demo_table: DROP TABLE demo_table.


4. Переименуйте demo_table_new в demo_table:
   RENAME demo_table_new TO demo_table.

Эти шаги позволят MySQL выполнять подобные операции быстрей.