Co to jest JOIN, jakie są jego rodzaje, jak działa i cała reszta teorii – pozwolę sobie pominąć. W internecie można znaleźć setki stron z informacjami o takich podstawach języka SQL. Dziś chciałbym zwrócić uwagę osobom piszącym zapytania na jedną, bardzo istotną kwestię dotyczącą JOINów. Domyślam się, że dla sporej części czytelników ten post nie będzie odkrywał nic nowego. W praktyce jednak często okazuje się, że sporo twórców aplikacji nie jest świadomych konsekwencji zapytań, które piszą. Liczę po cichu że choć kilka takich osób trafi na ten post i zrozumie dlaczego z wydajnością JOINów bywa różnie.

Ok, wstęp trochę zagmatwany, ale teraz pora na konkretny. W czym problem? JOIN to kombinacja rekordów z kilku różnych tabel. MySQL bierze rekordy z pierwszej tabeli i dla każdego z nich wyszukuje w drugiej tabeli rekordy pasujące do warunku połączenia tabel. Potem bierze kolejne rekordy z kolejnej tabeli i tak dalej i tak dalej. W praktyce wygląda to tak na przykładzie danych z bazy sakila:

mysql>  EXPLAIN SELECT * FROM actor LEFT JOIN film_actor ON actor.actor_id =  film_actor.actor_id LEFT JOIN film ON film_actor.film_id =  film.film_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 200
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.actor.actor_id
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: film
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.film_actor.film_id
rows: 1
Extra:
3 rows in set (0,00 sec)

Dla każdego z 200 rekordów z tabeli ‘actor’ sprawdzany jest jeden rekord z bazy ‘film_actor’, a dla każdej kombinacji tych rekordów sprawdzany jest jeden rekord z tabeli ‘film’. Podsumowując, w tym przypadku sprawdzanych jest 200*1*1=200 rekordów. Ok, to nie jest tak dużo. Stało się tak dzięki temu, że tabele ‘film_actor’ i ‘film’ są poprawnie poindeksowane a kolumny stosowane w JOINie są unikalne – nie ma sytuacji że w tabeli film pojawi się większa ilość rekordów o tej samej wartości film_id.
Zobaczmy co się stanie jeśli doprowadzimy do takiej sytuacji. Tworzymy następującą tabelę:

mysql> SHOW CREATE TABLE film_nieunikalny\G
*************************** 1. row ***************************
Table: film_nieunikalny
Create Table: CREATE TABLE `film_nieunikalny` (
`film_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`title` varchar(255) CHARACTER SET utf8 NOT NULL,
`description` text CHARACTER SET utf8,
`release_year` year(4) DEFAULT NULL,
`language_id` tinyint(3) unsigned NOT NULL,
`original_language_id` tinyint(3) unsigned DEFAULT NULL,
`rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',
`rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
`length` smallint(5) unsigned DEFAULT NULL,
`replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
`rating` enum('G','PG','PG-13','R','NC-17') CHARACTER SET utf8 DEFAULT 'G',
`special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') CHARACTER SET utf8 DEFAULT NULL,
`last_update` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
KEY `idx_film_id` (`film_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2
1 row in set (0,00 sec)

i wgrywamy do niej 8 razy kopię danych z tabeli ‘film’:

INSERT INTO film_nieunikalny SELECT * FROM film;
INSERT INTO film_nieunikalny SELECT * FROM film;
INSERT INTO film_nieunikalny SELECT * FROM film;
INSERT INTO film_nieunikalny SELECT * FROM film;
INSERT INTO film_nieunikalny SELECT * FROM film;
INSERT INTO film_nieunikalny SELECT * FROM film;
INSERT INTO film_nieunikalny SELECT * FROM film;
INSERT INTO film_nieunikalny SELECT * FROM film;

Jak się ma teraz nasze zapytanie?

mysql>  EXPLAIN SELECT * FROM actor LEFT JOIN film_actor ON actor.actor_id =  film_actor.actor_id LEFT JOIN film_nieunikalny ON film_actor.film_id =  film_nieunikalny.film_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 200
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.actor.actor_id
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: film_nieunikalny
type: ref
possible_keys: idx_film_id
key: idx_film_id
key_len: 2
ref: sakila.film_actor.film_id
rows: 8
Extra:
3 rows in set (0,00 sec)

Jak widać, ilość kombinacji rekordów do sprawdzenia znacznie nam wzrosła. Dokładnie jest to 200*1*8=1600 rekordów.

Co by się stało jeśli tabela film_actor nie byłaby poprawnie poindeksowana? Utwóżmy taką tabelę:

mysql> CREATE TABLE film_actor_noindex SELECT * FROM film_actor;
Query OK, 5462 rows affected (0,01 sec)
Rekordów: 5462  Duplikatów: 0  Ostrzeżeń: 0

Jak widać, nie zawiera ona żadnych indeksów:

mysql> SHOW CREATE TABLE film_actor_noindex\G
*************************** 1. row ***************************
Table: film_actor_noindex
Create Table: CREATE TABLE `film_actor_noindex` (
`actor_id` smallint(5) unsigned NOT NULL,
`film_id` smallint(5) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin2
1 row in set (0,00 sec)

Sprawdźmy zachowanie naszego zapytania:

mysql>  EXPLAIN SELECT * FROM actor LEFT JOIN film_actor_noindex ON  actor.actor_id = film_actor_noindex.actor_id LEFT JOIN film_nieunikalny  ON film_actor_noindex.film_id = film_nieunikalny.film_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 200
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor_noindex
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5462
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: film_nieunikalny
type: ref
possible_keys: idx_film_id
key: idx_film_id
key_len: 2
ref: sakila.film_actor_noindex.film_id
rows: 8
Extra:
3 rows in set (0,00 sec)

Teraz ilość kombinacji rekordów do sprawdzenia jest następująca: 200*5462*8=8739200.
Do czego ten post zmierza? Wyobraźmy sobie że SELECT składa się z 8 JOINów. Tabele są spore. Nie mamy żadnego dodatkowego warunku WHERE, czyli najmniejsza tabela będzie skanowana w całości. Załóżmy że ma ona 300 rekordów. Kolejna tabela jest poindeksowana poprawnie, wartości w indeksie są unikalne – do kombinacji bierzemy 1 rekord. Następna – 30 rekordów (indeks nie jest unikalny), kolejna 120, piąta jest bez poprawnego indeksu – 422 rekordów, szósta 3 rekordy, siódma 5, ostatnia, ósma – 1. Łącznie JOIN sprawdza 300*1*30*120*422*3*5*1=6836400000, sześć miliardów, osiemset trzydzieści sześć milionów i czterysta tysięcy kombinacji rekordów. To jest już spore obciążenie dla serwera MySQL.

Pisząc zapytania na kilka JOINów trzeba zawsze pamiętać o tym, żeby tabele były poprawnie poindeksowane. Idealnie by było, żeby indeksy były unikalne. Jeśli tego zabraknie, może szybko się okazać że zapytanie wykonuje się koszmarnie długo. Każda tabela to kolejne mnożenie kombinacji. Nawet jeśli pojawia się konieczność sprawdzenia dodatkowego 1000 rekordów to w przypadku JOINa skutkuje to skokiem ilości kombinacji o trzy rzędy wielkości. Na przykład z milionów w miliardy -twórca zapytania musi być tego świadomy. O ile dobrze poindeksowane JOINy są stosunkowo szybkie, to brak nawet jednego indeksu może po prostu oznaczać tyle, że zapytanie przestaje być możliwe do wykonania w sensownym przedziale czasowym.