Podczas rutynowych prac natknąłem się na bardzo ciekawe zapytanie o następującym planie wykonania:

+----+--------------------+-----------------+------+---------------+------+---------+------+-------+----------------------------------------------+
| id | select_type        | table           | type | possible_keys | key  | key_len | ref  | rows  | Extra                                        |
+----+--------------------+-----------------+------+---------------+------+---------+------+-------+----------------------------------------------+
|  1 | PRIMARY            | <derived2>      | ALL  | NULL          | NULL | NULL    | NULL |   185 |                                              |
|  2 | DERIVED            | tab1            | ALL  | NULL          | NULL | NULL    | NULL |  2869 | Using where; Using temporary; Using filesort |
|  2 | DERIVED            | tab2            | ALL  | NULL          | NULL | NULL    | NULL |  2869 |                                              |
|  2 | DERIVED            | tab3            | ALL  | NULL          | NULL | NULL    | NULL |  2869 |                                              |
|  2 | DERIVED            | tab4            | ALL  | NULL          | NULL | NULL    | NULL | 10483 |                                              |
|  4 | DEPENDENT SUBQUERY | tab5            | ALL  | NULL          | NULL | NULL    | NULL |  5613 | Using where                                  |
|  3 | DEPENDENT SUBQUERY | tab6            | ALL  | NULL          | NULL | NULL    | NULL |  2311 | Using where                                  |
+----+--------------------+-----------------+------+---------------+------+---------+------+-------+----------------------------------------------+
7 rows in set (2 min 12.76 sec)

Przyznam, że jest to swojego rodzaju rekord. Sam EXPLAIN wykonywał się kilka minut, zapytanie analizowało prawie 7 milionów rekordów. Co lepsze, zapytanie to do poindeksowania było banalne – SELECT z jednym podzapytaniem, w którego skład wchodziły OUTER JOINy. Skoro na załączonym przykładzie widać, że indeksowanie JOINów nie jest rzeczą oczywistą, to popatrzmy jak to można zrobić.

Tabele i ich zawartość to tabele z bazy danych ‚Sakila’, tyle tylko że pozbawione jakiegokolwiek indeksowania. Weźmy przykładowe zapytanie:

mysql> EXPLAIN SELECT first_name, last_name, title FROM actor_new LEFT OUTER JOIN film_actor_new USING(actor_id) LEFT OUTER JOIN film_new USING(film_id) WHERE last_name='ALLEN';
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | actor_new      | ALL  | NULL          | NULL | NULL    | NULL |  200 | Using where |
|  1 | SIMPLE      | film_actor_new | ALL  | NULL          | NULL | NULL    | NULL | 5462 |             |
|  1 | SIMPLE      | film_new       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |             |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
3 rows in set (0,00 sec)

Jak widać na powyższym planie, wykonywany jest JOIN trzech tabel, nie ma żadnych indeksów. Podstawową sprawą jest dodanie indeksów na kolumny, które wykorzystywane są do łączenia tabel, a także na kolumnę ‚last_name’, która występuje w warunku WHERE.

mysql> ALTER TABLE film_actor_new ADD INDEX idx_actor_id (actor_id);
Query OK, 5462 rows affected (0,05 sec)
Rekordów: 5462  Duplikatów: 0  Ostrzeżeń: 0

mysql> ALTER TABLE film_new ADD INDEX idx_film_id (film_id);
Query OK, 1000 rows affected (0,01 sec)
Rekordów: 1000  Duplikatów: 0  Ostrzeżeń: 0

mysql> ALTER TABLE actor_new ADD INDEX idx_last_name (last_name);
Query OK, 200 rows affected (0,05 sec)
Rekordów: 200  Duplikatów: 0  Ostrzeżeń: 0

Po tych operacjach plan wygląda optymalnie:

mysql> EXPLAIN SELECT first_name, last_name, title FROM actor_new LEFT OUTER JOIN film_actor_new USING(actor_id) LEFT OUTER JOIN film_new USING(film_id) WHERE last_name='ALLEN';
+----+-------------+----------------+------+---------------+---------------+---------+-------------------------------+------+-------------+
| id | select_type | table          | type | possible_keys | key           | key_len | ref                           | rows | Extra       |
+----+-------------+----------------+------+---------------+---------------+---------+-------------------------------+------+-------------+
|  1 | SIMPLE      | actor_new      | ref  | idx_last_name | idx_last_name | 137     | const                         |    2 | Using where |
|  1 | SIMPLE      | film_actor_new | ref  | idx_actor_id  | idx_actor_id  | 2       | sakila.actor_new.actor_id     |   27 |             |
|  1 | SIMPLE      | film_new       | ref  | idx_film_id   | idx_film_id   | 2       | sakila.film_actor_new.film_id |    1 |             |
+----+-------------+----------------+------+---------------+---------------+---------+-------------------------------+------+-------------+
3 rows in set (0,01 sec)

Co się jednak stanie, jeśli zmienimy warunki w tym zapytaniu?

mysql> EXPLAIN SELECT first_name, last_name, title FROM actor_new LEFT OUTER JOIN film_actor_new USING(actor_id) LEFT OUTER JOIN film_new USING(film_id) WHERE title='AFRICAN EGG';
+----+-------------+----------------+------+---------------+--------------+---------+-------------------------------+------+-------------+
| id | select_type | table          | type | possible_keys | key          | key_len | ref                           | rows | Extra       |
+----+-------------+----------------+------+---------------+--------------+---------+-------------------------------+------+-------------+
|  1 | SIMPLE      | actor_new      | ALL  | NULL          | NULL         | NULL    | NULL                          |  200 |             |
|  1 | SIMPLE      | film_actor_new | ref  | idx_actor_id  | idx_actor_id | 2       | sakila.actor_new.actor_id     |   27 | Using where |
|  1 | SIMPLE      | film_new       | ref  | idx_film_id   | idx_film_id  | 2       | sakila.film_actor_new.film_id |    1 | Using where |
+----+-------------+----------------+------+---------------+--------------+---------+-------------------------------+------+-------------+
3 rows in set (0,00 sec)

Konieczny jest pełny skan tabeli ‚actor_new’. Dodajmy indeks na kolumnę ‚title’ i zobaczmy efekt:

mysql> ALTER TABLE film_new ADD INDEX idx_title (title);
Query OK, 1000 rows affected (0,02 sec)
Rekordów: 1000  Duplikatów: 0  Ostrzeżeń: 0

mysql> EXPLAIN SELECT first_name, last_name, title FROM actor_new LEFT OUTER JOIN film_actor_new USING(actor_id) LEFT OUTER JOIN film_new USING(film_id) WHERE title='AFRICAN EGG';
+----+-------------+----------------+------+-----------------------+--------------+---------+---------------------------+------+-------------------+
| id | select_type | table          | type | possible_keys         | key          | key_len | ref                       | rows | Extra             |
+----+-------------+----------------+------+-----------------------+--------------+---------+---------------------------+------+-------------------+
|  1 | SIMPLE      | film_new       | ref  | idx_film_id,idx_title | idx_title    | 767     | const                     |    1 | Using where       |
|  1 | SIMPLE      | actor_new      | ALL  | NULL                  | NULL         | NULL    | NULL                      |  200 | Using join buffer |
|  1 | SIMPLE      | film_actor_new | ref  | idx_actor_id          | idx_actor_id | 2       | sakila.actor_new.actor_id |   27 | Using where       |
+----+-------------+----------------+------+-----------------------+--------------+---------+---------------------------+------+-------------------+
3 rows in set (0,00 sec)

Liczba przeszukiwanych rekordów pozostała bez zmian, ale tym razem w przypadku tabeli ‚film_new’ wykorzystywany indeks to nowo dodany ‚idx_title’. Jak widać, zmiana warunku WHERE poskutkowała zupełnie innym sposobem podejścia optimizera do JOINa, przez co zapytanie stało się nieoptymalne. Właśnie z takich względów, w przypadku JOINów zalecane jest indeksowanie nie tylko tych kolumn, które w danym, konkretnym wypadku są wykorzystywane, ale też tych kolumn, które potencjalnie mogłyby być wykorzystane do połączenia tabel, gdyby ich kolejność była inna. Oczywiście, jeśli jesteśmy pewni, że żadna inna modyfikacja JOINa nie będzie wykorzystywana, to teoretycznie można dodatkowe indeksy pominąć. Nie będą one wykorzystywane i tylko zmniejszą wydajność zapytań modyfikujących dane. W praktyce jednak ja bym te indeksy założył. Zazwyczaj okazuje się, że taka modyfikacja JOINa, z innymi warunkami WHERE, prędzej czy później się pojawia. Tyle że wtedy o nałożeniu dodatkowych indeksów mało kto pamięta. Lepiej zrobić to za jednym zamachem.

Dodajmy więc brakujące indeksy i zobaczmy efekt:

mysql> ALTER TABLE actor_new ADD INDEX idx_actor_id (actor_id);
Query OK, 200 rows affected (0,81 sec)
Rekordów: 200  Duplikatów: 0  Ostrzeżeń: 0

mysql> ALTER TABLE film_actor_new ADD INDEX idx_film_id (film_id);
Query OK, 5462 rows affected (0,03 sec)
Rekordów: 5462  Duplikatów: 0  Ostrzeżeń: 0

mysql> EXPLAIN SELECT first_name, last_name, title FROM actor_new LEFT OUTER JOIN film_actor_new USING(actor_id) LEFT OUTER JOIN film_new USING(film_id) WHERE title='AFRICAN EGG';
+----+-------------+----------------+------+--------------------------+--------------+---------+--------------------------------+------+-------------+
| id | select_type | table          | type | possible_keys            | key          | key_len | ref                            | rows | Extra       |
+----+-------------+----------------+------+--------------------------+--------------+---------+--------------------------------+------+-------------+
|  1 | SIMPLE      | film_new       | ref  | idx_film_id,idx_title    | idx_title    | 767     | const                          |    1 | Using where |
|  1 | SIMPLE      | film_actor_new | ref  | idx_actor_id,idx_film_id | idx_film_id  | 2       | sakila.film_new.film_id        |    5 | Using where |
|  1 | SIMPLE      | actor_new      | ref  | idx_actor_id             | idx_actor_id | 2       | sakila.film_actor_new.actor_id |    1 |             |
+----+-------------+----------------+------+--------------------------+--------------+---------+--------------------------------+------+-------------+
3 rows in set (0,01 sec)

W tym momencie optimizer posiada wszystkie indeksy, które potrzebuje a zapytanie jest optymalne.

Drodzy administratorzy, drodzy programiści. Nie bójcie się indeksów, one na prawdę nie gryzą. Pamiętajmy aby każde zapytanie poprawnie poindeksować, w szczególności JOINy – w ich wypadku brak indeksów to zbrodnia. Jak widać na powyższych przykładach, idea jest na prawdę prosta. Mam nadzieję, że dzięki temu postowi, choćby tylko w kilku przypadkach, uda się uniknąć takich koszmarów administratora MySQL, jaki przytoczyłem na początku tego wpisu.