Wyłapaliśmy jakieś niewydajne zapytanie, chcemy je poprawić. Wiąże się to oczywiście z testowaniem różnorakich jego wersji – raz dodajemy podzapytanie, raz zmieniamy je na JOIN, raz przesuwamy podzapytanie z WHERE do listy kolumn. Różne sposoby są stosowane w różnych przypadkach. To, co jest wspólne, to nasza chęć do sprawdzenia, czy aby na pewno nowa wersja zapytania jest wydajniejsza niż stara. Post ten traktować będzie o tym, na co należy w takiej sytuacji zwrócić uwagę.

W jaki sposób możemy sprawdzić wydajność nowego zapytania? Zazwyczaj stosuje się sposób najprostszy z możliwych – uruchamiamy go. MySQL podaje z dokładnością do 0,01s czas realizacji danego zapytania. Jeśli taka precyzja nie wystarczy, możemy włączyć opcję profilowania:

SET PROFILING=1;
SELECT 1 FROM tabela;
SELECT 2 FROM tabela;
SET PROFILING=0;

Następnie możemy porównać wydajność takich zapytań przy pomocy polecenia:

mysql> SHOW PROFILES;
+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.06532900 | SELECT * FROM test.tab1 |
+----------+------------+-------------------------+
1 rows in set (0,00 sec)

Jak widać, w kolumne `Duration` mamy podany precyzyjny czas wykonania danego zapytania. To jest wystarczające, aby określić, które zapytanie jest szybsze.

Są jednak trzy kwestie, o których nie zawsze pamiętamy, a które mogą wpłynąć poważnie na czas trwania zapytania – a przez to, mogą wprowadzić nas w błąd jeśli chodzi o faktyczną wydajność zapytania.

Pierwsza z nich – cache zapytań. Różnie z nim jest – czasami query cache jest zbawieniem, czasami serializuje zapytania i jest wąskim gardłem. Na niektórych serwerach jest włączony, na innych nie. Problem w tym, że jeśli cache włączony jest, to potrafi znacząco przyspieszyć działanie naszego zapytania. Zawartość cache będzie usuwana w momencie, gdy dowolna tabela biorąca udział w danym zapytaniu, zostanie zmodyfikowana. Biorąc pod uwagę powyższe, uruchamiając dany SELECT kilka razy pod rząd, możemy dostać niestabilne wyniki: wolno, szybko, szybko, wolno, wolno, szybko… Pierwsze zapytanie jest wykonywane. Drugie i trzecie leci z cache. Kolejne dwa znowu są wykonywane, bo w tle działa jakiś INSERT. Następne znowu korzysta z cache – DBA natomiast zastanawia się o co chodzi i dlaczego tak dziwne wyniki otrzymuje.

Rozwiązaniem jest stosowanie modyfikatora SQL_NO_CACHE w przypadku każdego zapytania, które testujemy. Dzięki temu mamy pewność, że powyżej opisany przypadek nie będzie nas dotyczył.

Druga sprawa – za mały buffer pool. Jeśli trzymamy w bazie dużą ilość danych a aktywny zestaw danych jest większy od dostępnej na serwerze pamięci, możemy natknąć się na spory rozrzut w czasach wykonania zapytań. Przykładowo, mamy następujące dwa zapytania:

SELECT SQL_NO_CACHE * FROM tabela WHERE kolumna IN (0, 1);
SeLECT SQL_NO_CACHE * FROM tabela WHERE kolumna > 2;

Z EXPLAIN wiemy, że pierwsze zapytanie sprawdza 5000 rekordów. Drugie – 100000 rekordów. Dlaczego więc drugie zapytanie jest szybsze? Logika mówi, że nie powinno być.

Ano, wyjaśnieniem jest właśnie brak pamięci. Wykonujemy pierwsze zapytanie – indeks nie znajduje się w pamięci, konieczne jest odczytanie go z dysku. To trwa. Wykonujemy następnie drugie zapytanie. Tym razem czas trwania jest znacznie niższy – indeks został już wczytany podczas wykonywania pierwszego zapytania. Wszystko było w pamięci. Dlatego też, pomimo że rekordów do sprawdzenia było więcej, to jednak całość działała szybciej.

W takiej sytuacji trzeba po prostu każde z zapytań uruchomić kilka razy pod rząd. Dzięki temu mamy sporą szansę, że przy drugim zapytaniu (i kolejnych) dane będą w pamięci i faktycznie będziemy mogli oszacować maksymalną wydajność naszego zapytania.

Drodzy administratorzy – pamiętajcie też, że ten mechanizm działa także w przypadku normalnych zapytań, nie tylko tych testowych. Jeśli w slowlogu widzicie, że co pewien czas jakieś zapytanie wykonuje się znacznie dłużej niż zazwyczaj, a wiecie, że aktywny zestaw danych nie mieści się w pamięci serwera, to może być właśnie objaw opisywane przeze mnie sytuacji.

Trzecia sprawa – jeśli testujemy zapytania na serwerze, który nie jest odcięty od świata (czyli nie jest serwerem slave, nie wykonywane są na nim żadne inne zapytania), sprawdzajcie, czy aby na pewno tabela, którą dotyczą wasze testy, nie jest modyfikowana w tle. Zdarzyło mi się, że prawie straciłem wiarę w logikę, gdy podczas profilowania jakiegoś zapytania co chwila otrzymywałem kompletnie inne wyniki. Logika się odnalazła w momencie, w którym zauważyłem, że jedna z tabel raz ma 1000 rekordów, raz 1500 a raz 300 – w tle działała replikacja, która akurat tą tabelę ostro modyfikowała. Podobnie było w sytuacji, gdy kiedyś chciałem przepisać zapytanie – na logikę wszystko ok, ale raz rekord o id 100 przyjmował wartość ‘aaa’ a raz ‘xxx’. Bez sensu – zapytanie powinno działać ok. Chwikę zajęło mi szukanie błędu, zanim zauważyłem, że replikacja wykonuje właśnie serię UPDATE na tą tabelę – i faktycznie, rekord o id 100 zmieniał co pewien czas swoją zawartość.

Mam nadzieję, że te sugestie przydadzą się komuś w przyszłości – na podstawie analizy danego zapytania podejmujemy dalsze decyzje. Dobrze by było upewnić się, że analiza, dane, na podstawie których decydujemy co dalej, została wykonana poprawnie.