Tabele tymczasowe tworzone przez użytkownika
Tabele tymczasowe kojarzą się głównie z przeciętną, żeby nie powiedzieć kiepską wydajnością zapytań. MySQL tworzy takie tabele w momencie gdy konieczne jest np. posortowanie wyników w sposób wykluczający użycie indeksów, jeśli wyniki są grupowane bez użycia indeksów, jeśli wykorzystywane są podzapytania i w wielu innych przypadkach. Tabele tymczasowe są tworzone przez MySQL automatycznie, istnieje też możliwość samodzielnego utworzenia takiej tabeli przez użytkownika. O tabelach generowanych przez MySQL, o ich cechach i o tym kiedy i dlaczego są tworzone pisałem już wcześniej. Dziś parę słów o tabelach “na żądanie”.
Jedna uwaga, której zabrakło w poprzednim poście. W przypadku automatycznie tworzonych tabel nie ma możliwości sterowania indeksami. MySQL samodzielnie decyduje o tym, jakie indeksy są tworzone (o ile w ogóle jakiekolwiek). Konkretnie, tworzone są indeksy na kolumny wykorzystywane w funkcji agregacyjnej, w GROUP BY, DISTINCT. Tego typu indeksy są tworzone zawsze, nie zależnie czy tabela tymczasowa utworzona została w pamięci czy na dysku.
Ok, teraz przejdźmy do dzisiejszego tematu. Drugim rodzajem tabel tymczasowych są tabele tworzone przez użytkownika. Tworzone są przy pomocy zapytania typu:
CREATE TEMPORARY TABLE tab (a INT);
Tabele takie tworzone są na domyślnym silniku, można także samodzielnie zdefiniować jaki silnik chcemy wykorzystać:
mysql> CREATE TEMPORARY TABLE tab (a INT); Query OK, 0 rows affected (0.00 sec) mysql> CREATE TEMPORARY TABLE tab1 (a INT) ENGINE=MyISAM; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TEMPORARY TABLE tab2 (a INT) ENGINE=MEMORY; Query OK, 0 rows affected (0.00 sec) Ile i jakie tabele tymczasowe zostały utworzone w danej sesji można sprawdzić zapytaniem (przynajmniej w MySQL 5.5): mysql> SELECT * FROM information_schema.TEMPORARY_TABLES\G *************************** 1. row *************************** SESSION_ID: 5849 TABLE_SCHEMA: test TABLE_NAME: tab2 ENGINE: MEMORY NAME: #sql7383_16d9_2 TABLE_ROWS: 0 AVG_ROW_LENGTH: 8 DATA_LENGTH: 0 INDEX_LENGTH: 0 CREATE_TIME: NULL UPDATE_TIME: NULL *************************** 2. row *************************** SESSION_ID: 5849 TABLE_SCHEMA: test TABLE_NAME: tab1 ENGINE: MyISAM NAME: #sql7383_16d9_1 TABLE_ROWS: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 0 INDEX_LENGTH: 1024 CREATE_TIME: 2011-08-08 08:42:50 UPDATE_TIME: 2011-08-08 08:42:50 *************************** 3. row *************************** SESSION_ID: 5849 TABLE_SCHEMA: test TABLE_NAME: tab ENGINE: InnoDB NAME: #sql7383_16d9_0 TABLE_ROWS: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 16384 INDEX_LENGTH: 0 CREATE_TIME: NULL UPDATE_TIME: NULL 3 rows in set (0.00 sec)
Jak widać, tabela tab działa w oparciu o InnoDB – domyślny silnik dla MySQL 5.5.
Tabele tymczasowe tworzone przez użytkownika funkcjonują tylko przez czas trwania sesji. Po zerwaniu połączenia są kasowane.
Ważną cechą tego typu tabel jest to, że istnieje możliwość definiowania dla nich dowolnych indeksów – tak samo jak dla każdej innej “normalnej” tabeli.
mysql> CREATE TEMPORARY TABLE tab_idx (a INT, b VARCHAR(100), c INT); Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE tab_idx ADD INDEX idx_a (a); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE tab_idx ADD INDEX idx_a_b (a, b(30)); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE tab_idx ADD INDEX idx_b_c (b, c); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW INDEXES FROM tab_idx; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tab_idx | 1 | idx_a | 1 | a | A | 0 | NULL | NULL | YES | BTREE | | | | tab_idx | 1 | idx_a_b | 1 | a | A | 0 | NULL | NULL | YES | BTREE | | | | tab_idx | 1 | idx_a_b | 2 | b | A | 0 | 30 | NULL | YES | BTREE | | | | tab_idx | 1 | idx_b_c | 1 | b | A | 0 | NULL | NULL | YES | BTREE | | | | tab_idx | 1 | idx_b_c | 2 | c | A | 0 | NULL | NULL | YES | BTREE | | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 5 rows in set (0.00 sec)
Ta cecha bywa szczególnie przydatna. Są pewne zapytania, w których nie ma możliwości aby uniknąć utworzenia tabeli tymczasowej – choćby zapytania z JOINem, w których wyniki chcemy sortować po kolumnach należących do obydwu tabel. W takiej sytuacji może się okazać, że szybsze będzie samodzielne utworzenie tabeli tymczasowej, wrzucenie do niej danych z JOINa, dodanie odpowiednich indeksów i następnie wyciągnięcie danych przy pomocy indeksów właśnie.
Przykładowo, dla danych z bazy Sakila, mogłoby to wyglądać następująco:
mysql> EXPLAIN SELECT first_name, rating FROM actor LEFT JOIN film_actor USING(actor_id) LEFT JOIN film USING(film_id) WHERE title='ELEPHANT TROJAN' ORDER BY first_name, rating\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: ref possible_keys: PRIMARY,idx_title key: idx_title key_len: 767 ref: const rows: 1 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: ref possible_keys: PRIMARY,idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: sakila.film.film_id rows: 1 Extra: Using where; Using index *************************** 3. row *************************** id: 1 select_type: SIMPLE table: actor type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 2 ref: sakila.film_actor.actor_id rows: 1 Extra: 3 rows in set (0,00 sec)
JOIN trochę bez sensu, ale nie o sens tu chodzi tylko o zasadę. Sortujemy po kolumnach first_name i rating – należą do dwóch tabel, konieczne jest utworzenie tabeli tymczasowej. Wyniki są sortowane przy pomocy algorytmu filesort. Możemy to zrobić też tak:
mysql> CREATE TEMPORARY TABLE temp (first_name VARCHAR(20), rating VARCHAR(6)); mysql> ALTER TABLE temp ADD INDEX idx_first_name_rating (first_name, rating); mysql> INSERT INTO temp SELECT first_name, rating FROM actor LEFT JOIN film_actor USING(actor_id) LEFT JOIN film USING(film_id) WHERE title='ELEPHANT TROJAN';
W tym momencie EXPLAIN wygląda tak:
mysql> EXPLAIN SELECT * FROM temp ORDER BY first_name, rating\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: temp type: index possible_keys: NULL key: idx_first_name_rating key_len: 32 ref: NULL rows: 5 Extra: Using index 1 row in set (0,00 sec)
Wyniki są sortowane przy pomocy indeksu. Co prawda tabelę tymczasową musieliśmy utworzyć, ale już sortowanie jest realizowane w wydajniejszy sposób. W przypadku pięciu rekordów sensu robić tego nie ma, ale w przypadku pięciuset tysięcy przeprowadzenie takiej kombinacji może okazać się dobrym pomysłem.
Da się to też doskonale wykorzystać w sytuacji gdy aplikacja nie koniecznie musi dostawać najświeższe i najbardziej aktualne dane. Jeśli udałoby się taką tabelę tymczasową użyć np. do obsłużenia kilkunastu albo i więcej zapytań (np. tworzymy zestawienie komentarzy na stronie i odświeżamy je co kilka minut – taki cache po stronie bazy danych – przez ten czas wyniki zamiast być generowane na bieżąco, są serwowane z poindeksowanej tabeli tymczasowej), to jest bardzo prawdopodobne że koszty tworzenia tabeli i indeksów zwrócą się z nawiązką.
Źródło
Aby dodawać komentarze musisz być zalogowany!