Podstawową zasadą pisania zapytań jest to, że każde z nich gruntownie testujemy pod względem wydajności. Do koniecznego minimum należy sprawdzenie planu, jaki MySQL stworzył dla danego zapytania. Służy do tego polecenie EXPLAIN.

Przykładowy wynik działania takiego zapytania może wyglądać na przykład tak.

mysql> EXPLAIN SELECT first_name, last_name, title FROM film LEFT OUTER JOIN film_actor USING (film_id) LEFT OUTER JOIN actor USING (actor_id) WHERE first_name='PENELOPE' AND last_name='GUINESS' ORDER BY title DESC;
+----+-------------+------------+--------+-----------------------------+---------------------+---------+---------------------------+------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys               | key                 | key_len | ref                       | rows | Extra                                        |
+----+-------------+------------+--------+-----------------------------+---------------------+---------+---------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | actor      | ref    | PRIMARY,idx_actor_last_name | idx_actor_last_name | 137     | const                     |    3 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | film_actor | ref    | PRIMARY,idx_fk_film_id      | PRIMARY             | 2       | sakila.actor.actor_id     |    1 | Using where; Using index                     |
|  1 | SIMPLE      | film       | eq_ref | PRIMARY                     | PRIMARY             | 2       | sakila.film_actor.film_id |    1 |                                              |
+----+-------------+------------+--------+-----------------------------+---------------------+---------+---------------------------+------+----------------------------------------------+
3 rows in set (0.00 sec)

Jak widać, wykonywany jest LEFT JOIN pomiędzy tabelami `film` a `film_actor` przy użyciu kolumny `film_id`. Kolejnym etapem będzie wykonanie LEFT JOIN pomiędzy tabelami `film_actor` a `actor` przy wykorzystaniu pola `actor_id`. Na całej tabeli odfiltrowywane są wyniki na podstawie warunków WHERE, następnie zaś wyniki są sortowane przy użyciu tabeli tymczasowej.

Dokładny opis tego, co oznacza zawartość poszczególnych kolumn znajduje się w dokumentacji MySQL (http://dev.mysql.com/doc/refman/5.1/en/using-explain.html), natomiast to co jest najistotniejsze to:

  • type – typ JOIN’u. Dokładny opis poszczególnych typów znajduje się w dokumentacji i zajmuje tam sporo miejsca. Staramy się, aby nie pojawiło się tu słowo ALL, oznaczające konieczność wykonania pełnego skanu zawartości tabeli.
  • possible_keys – klucze i indeksy, które teoretycznie mogły zostać zastosowane do danej tabeli.
  • key – klucze i indeksy, które zostały wykorzystane – optimizer uznał je za najbardziej korzystne w danej sytuacji. Jeśli nie wykorzystany został żaden, należałoby zastanowić się dlaczego tak się stało. Nie było to możliwe w przypadku danego zapytania, czy też po prostu nie mamy odpowiedniego indeksu założonego?
  • rows – ilość rekordów, która będzie konieczna do sprawdzenia w danej tabeli – jest to tylko oszacowanie optimizera, nie są to dokładne dane. Im mniej, tym lepiej.
  • extra – dodatkowe informacje na temat tego, jak MySQL będzie wykonywał to zapytanie.

Dodatkowe przydatne informacje możemy uzyskać jeśli zastosujemy zapytanie EXPLAIN EXTENDED:

mysql> EXPLAIN EXTENDED SELECT first_name, last_name, title FROM film LEFT OUTER JOIN film_actor USING (film_id) LEFT OUTER JOIN actor USING (actor_id) WHERE first_name='PENELOPE' AND last_name='GUINESS' ORDER BY title DESC;
+----+-------------+------------+--------+-----------------------------+---------------------+---------+---------------------------+------+----------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys               | key                 | key_len | ref                       | rows | filtered | Extra                                        |
+----+-------------+------------+--------+-----------------------------+---------------------+---------+---------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | actor      | ref    | PRIMARY,idx_actor_last_name | idx_actor_last_name | 137     | const                     |    3 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | film_actor | ref    | PRIMARY,idx_fk_film_id      | PRIMARY             | 2       | sakila.actor.actor_id     |   13 |   100.00 | Using where; Using index                     |
|  1 | SIMPLE      | film       | eq_ref | PRIMARY                     | PRIMARY             | 2       | sakila.film_actor.film_id |    1 |   100.00 |                                              |
+----+-------------+------------+--------+-----------------------------+---------------------+---------+---------------------------+------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

Widać tu (o ile wykorzystujemy MySQL w wersji 5.1.12 lub nowszej)  dodatkową kolumnę – filtered – jest to oszacowanie ile procent rekordów zostanie odrzuconych na podstawie warunków (WHERE, USING itp.) zapytania. Jeśli sięga ono 100%, znaczy to tyle, że każdy z rekordów zostanie wykorzystany przy generowaniu ostatecznego wyniku zapytania.

Jeśli bezpośrednio po wykonaniu zapytania EXPLAIN EXTENDED wydamy zapytanie SHOW WARNINGS, będziemy mieli możliwość sprawdzenia w jakiej dokładnie postaci nasze zapytanie będzie wykonywane. W praktyce wykonywane jest nie to zapytanie, które podaliśmy, ale zapytanie przerobione i zoptymalizowane przez optimizera – zapytanie dające oczywiście ten sam wynik. W powyższym przypadku dużej różnicy nie ma, natomiast zdarza się, że porównanie tych dwóch wersji zapytania – użytkownika i optimizera jest ciekawą lekturą.

Co możemy zobaczyć na naszym przykładzie? Widzimy, że wykorzystane będą indeksy w każdej tabeli – to dobrze. Wyniki są sortowane przy pomocy jednego z dwóch algorytmów filesort,  do sortowania nie będą wykorzystane żadne indeksy. Widzimy też, że tworzona będzie tablica tymczasowa – efekt sortowania po kolumnie `title`. O ile tablica tymczasowa nie jest tworzona na dysku, a ilość sortowanych rekordów jest niewielka, nie stanowi to znacznego problemu wydajnościowego.

Tablica tymczasowa tworzona jest na dysku jeśli:

  • jej wielkość przekroczy wielkość mniejszej ze zmiennych tmp_table_size i max_heap_table_size
  • w zapytaniu wystąpią kolumny typu BLOB, TEXT i ich odmiany – te typy danych nie są obsługiwane przez silnik MEMORY, który jest wykorzystywany do tworzenia tablic tymczasowych w pamięci

Sprawdziliśmy plan zapytania, wiemy już jak będzie wykonywane, jakie mechanizmy będą wykorzystywane. Czy można coś poprawić? Tak na pierwszy rzut oka widzimy, że w zapytaniu mamy dwa warunki WHERE – sprawdzamy kolumnę `first_name` i kolumnę `last_name`. Indeks nałożony jest tylko na kolumnie `last_name`. Możemy więc spróbować nałożyć indeks na obie te kolumny:

ALTER TABLE actor ADD INDEX idx_first_name_last_name (first_name, last_name);

Sprawdzamy ponownie plan zapytania:

mysql> EXPLAIN EXTENDED SELECT first_name, last_name, title FROM film LEFT OUTER JOIN film_actor USING (film_id) LEFT OUTER JOIN actor USING (actor_id) WHERE first_name='PENELOPE' AND last_name='GUINESS' ORDER BY title DESC;
+----+-------------+------------+--------+------------------------------------------------------+--------------------------+---------+---------------------------+------+----------+-----------------------------------------------------------+
| id | select_type | table      | type   | possible_keys                                        | key                      | key_len | ref                       | rows | filtered | Extra                                                     |
+----+-------------+------------+--------+------------------------------------------------------+--------------------------+---------+---------------------------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | actor      | ref    | PRIMARY,idx_actor_last_name,idx_first_name_last_name | idx_first_name_last_name | 274     | const,const               |    1 |   100.00 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | film_actor | ref    | PRIMARY,idx_fk_film_id                               | PRIMARY                  | 2       | sakila.actor.actor_id     |   13 |   100.00 | Using where; Using index                                  |
|  1 | SIMPLE      | film       | eq_ref | PRIMARY                                              | PRIMARY                  | 2       | sakila.film_actor.film_id |    1 |   100.00 |                                                           |
+----+-------------+------------+--------+------------------------------------------------------+--------------------------+---------+---------------------------+------+----------+-----------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

Jak widać, optimizer wykorzystał nowy indeks, ilość rekordów koniecznych do sprawdzenia w tabeli `actor` zmniejszyła się z 3 do 1. Pytanie czy tego typu operacja się opłaca? Może być różnie, zależy to od sprzętu na jakim działa baza, od wielkości samej bazy.

Każda zmiana struktury bazy danych lub modyfikacja zapytania, powinna wiązać się z testami wydajnościowymi.

Osobom zainteresowanym tematem analizy obciążenia proponuję zapoznać się także z kolejnymi postami opisującymi sposoby przeprowadzenia analizy obciążenia: „Profiling” i „Profiling – ciąg dalszy„, a także z innymi, zebranymi pod tagiem ‚analiza‚.