Optimizer MySQL to kawałek kodu, który przy pomocy wbudowanych w niego algorytmów i na podstawie posiadanych przez serwer MySQL informacji o tabelach i ich zawartości, przepisuje zapytania do postaci najbardziej optymalnej dla serwera. Tyle teoria. W praktyce nie jest tak różowo. Podstawowym problemem jest to, że zawartość tabel się zmienia. Zmienia się wybiórczość danych, zmienia się ich lokalizacja – część danych została pobrana do pamięci, część jest na dysku. Serwer MySQL zakłada zawsze, że dane znajdują się na dysku. W praktyce, w przypadku baz danych mniejszych niż dostępna pamięć na serwerze fizycznym, może być tak, że cały zestaw używanych danych jest w pamięci. Jak wiadomo, czas dostępu do pamięci i do dysku trochę się różni. Administrator, wiedząc że czas dostępu będzie krótszy, może na przykład preferować zapytanie, które dokona skanu tabeli, ale za to nie będzie generowało tabeli tymczasowej. Optimizer zawsze wybierze tą opcję, która zagwarantuje konieczność odczytania mniejszej ilości danych. Statystyki tabeli także nie są uaktualniane na bieżąco (dlatego też trzeba co pewien czas wykonywać operacje ANALYZE TABLE), przez co optimizer może podejmować decyzje na podstawie błędnych danych. Na szczęście, w niektórych przypadkach można trochę poprawić efekty jego pracy.

Jak dokładnie wygląda zapytanie, które będzie wykonywane przez serwer MySQL można się przekonać wykonując polecenie EXPLAIN EXTENDED, a następnie sprawdzając wynik polecenia SHOW WARNINGS. Wynik ten to właśnie efekt pracy optimizera MySQL. Przykładowo może wyglądać to tak:

mysql> EXPLAIN EXTENDED SELECT * FROM rental INNER JOIN customer USING (customer_id) WHERE staff_id=1 ORDER BY inventory_id;
+----+-------------+----------+--------+------------------------------------+-----------------+---------+---------------------------+------+----------+-----------------------------+
| id | select_type | table    | type   | possible_keys                      | key             | key_len | ref                       | rows | filtered | Extra                       |
+----+-------------+----------+--------+------------------------------------+-----------------+---------+---------------------------+------+----------+-----------------------------+
|  1 | SIMPLE      | rental   | ref    | idx_fk_customer_id,idx_fk_staff_id | idx_fk_staff_id | 1       | const                     | 8149 |   100.00 | Using where; Using filesort |
|  1 | SIMPLE      | customer | eq_ref | PRIMARY                            | PRIMARY         | 2       | sakila.rental.customer_id |    1 |   100.00 |                             |
+----+-------------+----------+--------+------------------------------------+-----------------+---------+---------------------------+------+----------+-----------------------------+
2 rows in set, 1 warning (0,01 sec)

mysql> SHOW WARNINGS;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `sakila`.`rental`.`customer_id` AS `customer_id`,`sakila`.`rental`.`rental_id` AS `rental_id`,`sakila`.`rental`.`rental_date` AS `rental_date`,`sakila`.`rental`.`inventory_id` AS `inventory_id`,`sakila`.`rental`.`return_date` AS `return_date`,`sakila`.`rental`.`staff_id` AS `staff_id`,`sakila`.`rental`.`last_update` AS `last_update`,`sakila`.`customer`.`store_id` AS `store_id`,`sakila`.`customer`.`first_name` AS `first_name`,`sakila`.`customer`.`last_name` AS `last_name`,`sakila`.`customer`.`email` AS `email`,`sakila`.`customer`.`address_id` AS `address_id`,`sakila`.`customer`.`active` AS `active`,`sakila`.`customer`.`create_date` AS `create_date`,`sakila`.`customer`.`last_update` AS `last_update` from `sakila`.`rental` join `sakila`.`customer` where ((`sakila`.`customer`.`customer_id` = `sakila`.`rental`.`customer_id`) and (`sakila`.`rental`.`staff_id` = 1)) order by `sakila`.`rental`.`inventory_id` |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

W końcówce poprzedniego posta pisałem o zapytaniu:

SELECT actor_id, title FROM sakila.film_actor INNER JOIN sakila.film USING(film_id) ORDER BY actor_id;

że potencjalnie mogłoby wykorzystać indeksy do sortowania wyników. Jak łatwo sprawdzić przy pomocy polecenia EXPLAIN nie stosuje – wykorzystywany jest algorytm filesort.

mysql> EXPLAIN SELECT actor_id, title FROM sakila.film_actor INNER JOIN sakila.film USING(film_id) ORDER BY actor_id;
+----+-------------+------------+-------+----------------+----------------+---------+---------------------+------+----------------------------------------------+
| id | select_type | table      | type  | possible_keys  | key            | key_len | ref                 | rows | Extra                                        |
+----+-------------+------------+-------+----------------+----------------+---------+---------------------+------+----------------------------------------------+
|  1 | SIMPLE      | film       | index | PRIMARY        | idx_title      | 767     | NULL                | 1055 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | film_actor | ref   | idx_fk_film_id | idx_fk_film_id | 2       | sakila.film.film_id |    2 | Using index                                  |
+----+-------------+------------+-------+----------------+----------------+---------+---------------------+------+----------------------------------------------+
2 rows in set (0,00 sec)

Bierze się to z tego, że sortowanie jest po kolumnie `actor_id`, która znajduje się w tabeli `film_actor`, a tabela ta nie jest pierwszą tabelą w JOINie. Możemy to zmienić przy pomocy STRAIGHT_JOIN. Opcja ta wymusza na optimizerze łączenie tabel JOINem w dokładnie takiej kolejności, jak zostało to zrobione w zapytaniu.

mysql> EXPLAIN SELECT STRAIGHT_JOIN actor_id, title FROM sakila.film_actor INNER JOIN sakila.film USING(film_id) ORDER BY actor_id;
+----+-------------+------------+--------+----------------+---------+---------+---------------------------+------+-------------+
| id | select_type | table      | type   | possible_keys  | key     | key_len | ref                       | rows | Extra       |
+----+-------------+------------+--------+----------------+---------+---------+---------------------------+------+-------------+
|  1 | SIMPLE      | film_actor | index  | idx_fk_film_id | PRIMARY | 4       | NULL                      | 5525 | Using index |
|  1 | SIMPLE      | film       | eq_ref | PRIMARY        | PRIMARY | 2       | sakila.film_actor.film_id |    1 |             |
+----+-------------+------------+--------+----------------+---------+---------+---------------------------+------+-------------+
2 rows in set (0,00 sec)

Jak widać, zastosowanie tej opcji poskutkowało zmianą kolejności łączenia tabel. Jako że pierwszą tabelą jest tabela `film_actor`, możliwe jest wykorzystanie indeksu aby posortować wyniki. Nie jest już konieczne tworzenie tablicy tymczasowej i stosowanie filesortu. Skutkiem ubocznym jest ok. pięciokrotny wzrost ilości rekordów, które MySQL musi sprawdzić aby wykonać to zapytanie. Sprawdzenie rekordu wiąże się z dostępem do dysku (a przynajmniej tak uważa optimizer MySQL), tak więc wzrasta koszt wykonania zapytania. Możemy to z resztą sprawdzić przy pomocy zapytania:

SHOW STATUS LIKE 'last_query_cost';

Zmienna ‚last_query_cost’ przechowuje szacowany przez optimizer koszt ostatniego zapytania. Wartość ta wyrażona jest w ilości stron, które muszą zostać odczytania z dysku aby zapytanie mogło zostać zrealizowane. Z dwóch wariantów zawsze wybierane jest to zapytanie, którego koszt jest mniejszy. W naszym przypadku pierwsze zapytanie wiązało się z odczytem ok. 3600 stron, drugie natomiast to odczyt 12167 stron. Niestety, tego typu analiza czasami prowadzi do błędnych wyborów. Na szczęście, do pewnego stopnia można na te wybory wpływać.