Kolejnym mechanizmem udostępnianym przez MySQL, a który można wykorzystać do przeanalizowania zapytania pod kątem wydajności i zrozumienia co się w czasie jego wykonywania faktycznie dzieje, jest mechanizm statystyk, które dostępne są dzięki zapytaniu:
Wynikiem tego zapytania jest długa lista zmiennych obrazujących stan serwera MySQL w ramach danej sesji – ilość wykonanych zapytań różnych typów, ilość utworzonych tablic tymczasowych, jakiego rodzaju JOIN’y były wykonywane, ile danych zostało przesłanych i tak dalej. Pełna lista tych parametrów wraz z opisem znajduje się w dokumentacji MySQL – http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html
Jak możemy wykorzystać te dane? Czyścimy wszystkie liczniki:
Wykonujemy zapytanie, a następnie sprawdzamy ponownie
To, co się na liście zmieniło, jest wynikiem działania naszego zapytania. Dla jasności – przykład:
Query OK, 0 rows affected (0,00 sec)
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 0 |
+-------------------------+-------+
3 rows in set (0,00 sec)
mysql> SELECT SQL_NO_CACHE first_name, last_name, title FROM film LEFT OUTER JOIN film_actor USING (film_id) LEFT OUTER JOIN actor1 USING (actor_id) WHERE first_name='PENELOPE' AND last_name='GUINESS' ORDER BY title DESC;
+------------+-----------+-----------------------+
| first_name | last_name | title |
+------------+-----------+-----------------------+
| PENELOPE | GUINESS | WIZARD COLDBLOODED |
| PENELOPE | GUINESS | WESTWARD SEABISCUIT |
| PENELOPE | GUINESS | VERTIGO NORTHWEST |
| PENELOPE | GUINESS | SPLASH GUMP |
| PENELOPE | GUINESS | RULES HUMAN |
| PENELOPE | GUINESS | OKLAHOMA JUMANJI |
| PENELOPE | GUINESS | MULHOLLAND BEAST |
| PENELOPE | GUINESS | LANGUAGE COWBOY |
| PENELOPE | GUINESS | LADY STAGE |
| PENELOPE | GUINESS | KING EVOLUTION |
| PENELOPE | GUINESS | HUMAN GRAFFITI |
| PENELOPE | GUINESS | GLEAMING JAWBREAKER |
| PENELOPE | GUINESS | ELEPHANT TROJAN |
| PENELOPE | GUINESS | COLOR PHILADELPHIA |
| PENELOPE | GUINESS | CHEAPER CLYDE |
| PENELOPE | GUINESS | BULWORTH COMMANDMENTS |
| PENELOPE | GUINESS | ANGELS LIFE |
| PENELOPE | GUINESS | ANACONDA CONFESSIONS |
| PENELOPE | GUINESS | ACADEMY DINOSAUR |
+------------+-----------+-----------------------+
19 rows in set (0,00 sec)
mysql> SHOW STATUS LIKE '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0,01 sec)
Jak widać, okazuje się, że wykonanie tego konkretnego zapytania wiąże się między innymi z utworzeniem tablicy tymczasowej.
Nie da się ukryć, że tego typu testowanie jest dosyć czasochłonne – trzeba wyczyścić statystyki, wykonać zapytanie, przeanalizować statystyki. Istnieje rozwiązanie ułatwiające życie – program mk-query-profiler wchodzący w skład znakomitego zestawu narzędzi Maatkit – www.maatkit.org.
Zapisujemy nasze zapytanie do pliku:
Uruchamiamy mk-query-profiler:
+----------------------------------------------------------+
| 1 (0.0004 sec) |
+----------------------------------------------------------+
__ Overall stats _______________________ Value _____________
Total elapsed time 0.000
Questions 1
COMMIT 0
DELETE 0
DELETE MULTI 0
INSERT 0
INSERT SELECT 0
REPLACE 0
REPLACE SELECT 0
SELECT 1
UPDATE 0
UPDATE MULTI 0
Data into server 218
Data out of server 911
Optimizer cost 30.611
__ Table and index accesses ____________ Value _____________
Table locks acquired 3
Table scans 0
Join 0
Index range scans 0
Join without check 0
Join with check 0
Rows sorted 19
Range sorts 0
Merge passes 0
Table scans 1
Potential filesorts 1
__ Row operations ____________________ Handler ______ InnoDB
Reads 83 39
Fixed pos (might be sort) 19
Next row (table scan) 20
Bookmark lookup 24
First in index (full scan?) 0
Next in index 20
Prev in index 0
Writes
Delete 0 0
Update 0 0
Insert 19 0
Commit 1
W ten sposób uzyskujemy dokładne informacje o tym, co się dzieje w trakcie wykonywania zapytania. Wykonywany jest jeden SELECT, nakładane są trzy locki na tabele, posortowano 19 rekordów, wykonano jeden skan tabeli itd. Mamy także podane statystyki na poziomie silnika – ilość odczytów, ilość odczytów sekwencyjnych, ilość odczytów z indeksów, ilość rekordów zmodyfikowanych, utworzonych, skasowanych i tak dalej. Stosunkowo istotnym parametrem jest Optimizer cost – jest to szacowany przez optimizer koszt wykonania zapytania. Jednostką jest odczyt 4 kilobajtowej strony z danymi – im mniej odczytów, tym lepiej. Nie jest to do końca idealne rozwiązanie – nie uwzględnia na przykład faktu, że część danych jest cacheowanych – wszystkie odczyty są traktowane jako odczyty z dysku. Obliczenie kosztu bazuje na statystykach dotyczących indeksów i rekordów – statystyki te mogą nie być aktualne, lub być tylko szacunkowe.
Tego typu dane są niezbędnie konieczne, aby zrozumieć co się dzieje na serwerze bazodanowym. Zrozumienie tego jest z kolei istotne, aby świadomie podejmować pewne decyzje dotyczące pisania zapytań czy też modyfikowania struktury bazy danych.
Komentarze