Tabele tymczasowe są nie raz bardzo przydatnym narzędziem dla programisty – udostępniają dodatkowe możliwości przetwarzania danych jeszcze po stronie serwera bazy danych. W tabeli tymczasowej można sobie założyć indeks, można wykonać serię rożnych zapytań na tym samym zestawie danych, już bez konieczności pisania skomplikowanych JOIN’ów itp. Problemem jest to, że tabele tymczasowe tworzone są także automatycznie, jeśli tylko MySQL uzna, że jest to konieczne do zrealizowania danego zapytania. Dlaczego jest to problem? Dlatego, że często programista lub świeżo upieczony administrator MySQL, piszący zapytania, nie jest świadomy tego, kiedy i dlaczego są one tworzone. Jeśli nie jest świadomy, nie jest też w stanie kontrolować zachowania serwera baz danych – prowadzi to często do sytuacji, w której baza danych, z nieznanych przyczyn, zaczyna poważnie zwalniać. Co gorsza, często zdarza się to po pewnym czasie od wdrożenia danego serwisu.

Co to są te automatycznie tworzone tabele tymczasowe? Są to tabele tworzone w oparciu o silnik MEMORY lub MyISAM, które zakładane i zapełniane danymi są przez serwer MySQL. Tworzone są na potrzeby danego, konkretnego zapytania i usuwane są w momencie, gdy przestają być potrzebne. Jak widać, mamy dwa rodzaje tabel – tworzone w pamięci (silnik MEMORY) i na dysku (silnik MyISAM). Z oczywistych względów, szybsze i mniej obciążające serwer są te pierwsze i z tego też względu, jeśli to tylko jest możliwe, to tabela zakładana jest właśnie w pamięci.

Tabela tymczasowa tworzona jest na dysku jeśli:
– jej wielkość przekracza wartości zmiennej max_heap_table_size lub tmp_table_size
– jej zawartość uniemożliwia utworzenie jej przy pomocy silnika MEMORY – w szczególności jeśli zawiera kolumny typu TEXT lub BLOB

Kiedy tworzona jest tabela tymczasowa? Zazwyczaj jest to efekt zastosowania funkcji agregującej, lub też konieczność posortowania wyniku w sposób taki, że nie da się skorzystać z innych metod sortowania. Najprostszym sposobem na stwierdzenie, czy tworzona jest tabela tymczasowa, jest sprawdzenie wyniku zapytania EXPLAIN. Jeśli w kolumnie Extra pojawi się wpis „using temporary” – tabela jest tworzona. W statystykach serwera MySQL znajdują się informacje ile i jakie tabele zostały utworzone:

show global status like  'Created_tmp%tables';
+-------------------------+-------+
|  Variable_name           | Value |
+-------------------------+-------+
|  Created_tmp_disk_tables | 379   |
| Created_tmp_tables      | 1312  |
+-------------------------+-------+
2  rows in set (0,00 sec)

Można także wykorzystać do tego narzędzie mk-query-profiler.

Kilka przykładów na to, kiedy tworzą się tabele tymczasowe i jak tego uniknąć. Tworzymy bazę test i dwie tabele będące kopiami tabel `film` i `film_text` z bazy `sakila`, tyle że bez jakichkolwiek indeksów.

mysql> create database  test;
Query OK, 1 row affected (0,00 sec)

mysql> use test;
Database  changed
mysql> create table tab1 select * from sakila.film;
Query  OK, 1000 rows affected (0,02 sec)
Rekordów: 1000  Duplikatów: 0   Ostrzeżeń: 0

mysql> create table tab2 select * from  sakila.film_text;
Query OK, 1000 rows affected (0,03 sec)
Rekordów:  1000  Duplikatów: 0  Ostrzeżeń: 0

Wykonujemy najprostszego JOIN’a:

mysql> EXPLAIN SELECT * FROM tab1  LEFT OUTER JOIN tab2 USING (film_id) ORDER BY tab1.title;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|   1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |  1000 | Using temporary; Using filesort |
|  1 | SIMPLE      | tab2  |  ALL  | NULL          | NULL | NULL    | NULL | 1000  |                                 |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
2  rows in set (0,00 sec)

Jak widać, wynik JOIN’a wrzucany jest to tabeli tymczasowej, a następnie tabela ta jest sortowana po kolumnie `title`. Stwórzmy w obydwu tabelach klucze główne na kolumnie `film_id` i wykonajmy ponownie zapytanie:

mysql> ALTER  TABLE tab1 ADD PRIMARY KEY (film_id);
Query OK, 1000 rows affected  (0,02 sec)
Rekordów: 1000  Duplikatów: 0  Ostrzeżeń: 0

mysql>  ALTER TABLE tab2 ADD PRIMARY KEY (film_id);
Query OK, 1000 rows  affected (0,02 sec)
Rekordów: 1000  Duplikatów: 0  Ostrzeżeń: 0

mysql>  EXPLAIN SELECT * FROM tab1 LEFT OUTER JOIN tab2 USING (film_id) ORDER  BY tab1.title;
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+----------------+
|  id | select_type | table | type   | possible_keys | key     | key_len |  ref               | rows | Extra          |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+----------------+
|   1 | SIMPLE      | tab1  | ALL    | NULL          | NULL    | NULL    |  NULL              | 1000 | Using filesort |
|  1 | SIMPLE      |  tab2  | eq_ref | PRIMARY       | PRIMARY | 2       | test.tab1.film_id  |    1 |                |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+----------------+
2  rows in set (0,01 sec)

Okazuje się, że stworzenie tablicy tymczasowej nie jest konieczne. Dzieje się tak dlatego, że dzięki kluczowi głównemu optimizer wie, że każdemu rekordowi z tabeli `tab1` odpowiada jeden rekord z tabeli `tab2`. Wystarczy więc posortować tabelę `tab1` (stąd w Extra pojawia się filesort), a tabela `tab2` będzie automatycznie posortowana tak samo.
Z tego też względu, jeśli sortować będziemy po kolumnie w drugiej tabeli, okaże się, że tablica tymczasowa jest konieczna:

mysql> EXPLAIN SELECT * FROM  tab1 LEFT OUTER JOIN tab2 USING (film_id) ORDER BY tab2.title;
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+---------------------------------+
|  id | select_type | table | type   | possible_keys | key     | key_len |  ref               | rows | Extra                           |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+---------------------------------+
|   1 | SIMPLE      | tab1  | ALL    | NULL          | NULL    | NULL    |  NULL              | 1000 | Using temporary; Using filesort |
|  1 |  SIMPLE      | tab2  | eq_ref | PRIMARY       | PRIMARY | 2       |  test.tab1.film_id |    1 |                                 |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+---------------------------------+
2  rows in set (0,00 sec)

Niestety, MySQL potrafi skorzystać z więcej niż jednego indeksu na raz (index merge) tylko w specyficznych sytuacjach. W tym konkretnym przykładzie taka sytuacja nie zachodzi i dodanie indeksu na kolumnę `title` w tabeli `tab2` nic nie zmieni. Rozwiązaniem problemu jest natomiast zamiana kolejności łączenia JOIN’em tabel:

mysql> EXPLAIN SELECT * FROM tab2 LEFT  OUTER JOIN tab1 USING (film_id) ORDER BY tab2.title;
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+----------------+
|  id | select_type | table | type   | possible_keys | key     | key_len |  ref               | rows | Extra          |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+----------------+
|   1 | SIMPLE      | tab2  | ALL    | NULL          | NULL    | NULL    |  NULL              | 1000 | Using filesort |
|  1 | SIMPLE      |  tab1  | eq_ref | PRIMARY       | PRIMARY | 2       | test.tab2.film_id  |    1 |                |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+----------------+
2  rows in set (0,00 sec)

Na koniec kilka uwag. Jeśli zapytanie realizuje losowe sortowanie – ORDER BY RAND() – tabela tymczasowa tworzona jest zawsze. Jest to wystarczający powód, aby tego typu sortowania nigdy nie stosować. Jeśli dodatkowo konieczne będzie utworzenie ciut większej tabeli na dysku (bo np. w wyniku mamy kolumnę typu TEXT), taki SELECT wykonywać się będzie w tempie zaspanego żółwia… Tego typu sortowanie najlepiej realizować po stronie aplikacji – zazwyczaj będzie to operacja znacznie bardziej wydajna.
W przypadku JOIN’ów większych tabel bardzo często zdarza się, że tabela tymczasowa jest spora. Jeśli okazuje się, że musi ona zostać utworzona na dysku, dobrze jest przemyśleć, czy nie lepiej i wydajniej będzie rozbić zapytanie na kilka mniejszych. Utworzenie na dysku tabeli wielkości 1,5GB to dla niego spore obciążenie. Jeśli tego typu zapytań nałoży się kilka w tym samym czasie, serwer po prostu stanie w miejscu mieląc dyskiem. Jest to szczególnie prawdziwe dla najbardziej popularnych, tanich serwerów fizycznych z dyskami SATA w RAID 1. Fizyki nie da się przeskoczyć i taki podsystem IO po prostu musi się zapchać. W przypadku serwerów z kilkoma dyskami SAS czy SSD w RAID 10 sprawa wygląda trochę lepiej, ale i one mają jakąś ograniczoną przepustowość.
Jeśli serwer bazodanowy ma nadmiar pamięci, można część z niej przeznaczyć na dysk wirtualny (tmpfs, ramfs) i na tym dysku tworzyć tabele tymczasowe. Co prawda, rozwiązanie to ma swoje wady, zaletą jest na pewno szybkość działania. Nie jest tak wydajne jak tablica tymczasowa oparta na silniku MEMORY, ale o wiele szybsze niż korzystanie ze zwykłego dysku twardego.

Podsumowując, należy wiedzieć które konkretnie zapytania idące do serwera MySQL tworzą tabele tymczasowe. Staramy się wyeliminować jak najwięcej z nich (indeksy), a w przypadku tych, które muszą już takie zostać, staramy się ograniczyć ich szkodliwość np. poprzez trzymanie wyniku zapytania w cache.