Mechanizmy analizy

MySQL udostępnia różne mechanizmy służące do analizy wydajności i optymalizacji zapytań. Podstawą są tak zwane slowlogi. Jest to log serwera MySQL w którym zapisywane są wszystkie zapytania na wykonanie których serwer potrzebował więcej niż zdefiniowany przedział czasu (np. jedna sekunda).

Przykładowy wpis w slowlogu może wyglądać następująco:

# Time: 070909 5:08:02 # User@Host: xxx[xxx] @ domena.pl [127.0.0.1]
# Query_time: 0.038309 Lock_time: 0.000042 Rows_sent: 0 Rows_examined: 31483
use baza;
select * from tabela where uri='jakis_ciag' AND id_serwisu='1';

Interesują nas głównie dwie rzeczy - Query_time - czas wykonania zapytania w sekundach (im więcej, tym gorzej) i Rows_examined - ilość sprawdzonych rekordów (także im więcej, tym większą pracę musi wykonać serwer MySQL)

Mając podejrzane zapytanie, możemy sprawdzić jak MySQL chce je realizować. Służy do tego polecenie DESCRIBE wydane z linii poleceń klienta MySQL, czy też poprzez phpMyAdmina.

mysql> describe(select * from tabela where uri='jakis_ciag' AND id_serwisu='1');

W efekcie widzimy, że wykonywany jest pełny skan tabeli, sprawdzane są wszystkie istniejące rekordy. Nie wykorzystujemy żadnych mechanizmów optymalizacji MySQL.
Sprawdźmy jeszcze strukturę tabeli:

describe tabela;

Jak widać, na polu id założony jest główny klucz, nie mamy natomiast zdefiniowanych innych indeksów. Tabela wymaga więc poprawy.

Indeksy - co to jest i dlaczego jest takie ważne?

Indeksy wykorzystywane są przez MySQL aby szybko zlokalizować rekordy zawierające pola o konkretnej wartości. Bez indeksu MySQL zmuszony jest przeglądać całą tabelę, co przy większej ilości rekordów jest wybitnie nieopłacalne.
MySQL wykorzystuje indeksy między innymi podczas:
- sprawdzania warunku WHERE
- łączenia tabeli poprzez JOIN
- sortowania tabeli
- wywoływania funkcji MIN() i MAX()

Oczywiście, nie ma róży bez kolców i indeksy posiadają także pewne wady:
- zmniejszają szybkość operacji INSERT, UPDATE i DELETE
- zwiększają zapotrzebowanie na przestrzeń dyskową

Kiedy powinniśmy stosować indeksy? Jak najczęściej. Zalety szybkiego dostępu do danych są ogromne i znacznie przeważają nad wadami. Należy jednak pamiętać żeby nie zakładać indeksów które nie będą potem wykorzystywane.

Wracając do naszego przykładowego zapytania, w warunku WHERE mamy dwa ograniczenia a żadne z nich nie wykorzystuje indeksu. Załóżmy więc indeks na pole id_serwisu.

mysql> alter table tabela add index (`id_serwisu`);
Query OK, 31486 rows affected (0.83 sec)
Rekordów: 31486 Duplikatów: 0 Ostrzeżeń: 0

i sprawdźmy co udało się nam osiągnąć:

mysql> describe(select * from tabela where uri='jakis_ciag' AND id_serwisu='1');

Jak widać, ilość rekordów koniecznych do przeglądnięcia zmniejszyła się o ok. 8 tysięcy.

Struktura tabeli

Kolejną kwestią o której trzeba pamiętać, jest dobranie odpowiedniej struktury tabeli do danych, jakie będziemy w niej przechowywać. Zasadą podstawową powinno być minimalizowanie rozmiaru bazy - stosujmy SMALLINT zamiast INT, VARCHAR(20) zamiast VARCHAR(100).
Drugą zasadą jest dobór typu danych pola do jego zawartości. Nie przechowujmy danych typu INTEGER w polach VARCHAR czy TEXT. Jeśli to możliwe, stosujmy CHAR bądź VARCHAR a nie TEXT. Ważne jest to np. wtedy gdy w zapytaniu mamy warunek:

WHERE tabela1.id=tabela2.id

Jeśli pole tabela1.id jest typu INT a pole tabela2.id typu np. CHAR, może nie być możliwości użycia indeksów. MySQL będzie musiał przeglądnąć całość obydwu tabeli, co jest procesem kosztownym zarówno jeśli chodzi o czas procesora jak i o operacje dyskowe. Podobnie, dane typu BLOB czy TEXT nie są przez MySQL indeksowane i ich zastosowanie uniemożliwia skorzystanie z indeksów.

Wracamy do naszego przykładu. Jak pokazało nam polecenie DESCRIBE, pole uri, drugi element warunku WHERE, jest typu TEXT. Wiemy już że w takim wypadku nie ma możliwości zastosowania indeksów. Zamieńmy typ tego pola na typ VARCHAR, załóżmy na nim indeks (VARCHAR może być indeksowane) i zobaczmy co się stanie:

describe(select * from tabela where uri='jakis_ciag' AND id_serwisu='1');

Zamiast sprawdzania 31,5 tysiąca (w wersji podstawowej) czy też 23,5 tysiąca (po dodaniu indeksu na pole id_serwisu) rekordów sprawdzany jest tylko i wyłącznie jeden.

Podsumowanie

Nie zawsze jesteśmy w stanie zmniejszyć obciążenie generowane na bazie danych praktycznie do zera. Często odwiedzana strona, wykonująca sporą ilość zapytań do bazy będzie w pewnym stopniu generować obciążenie. Ważnym jest to, aby starać się jak najbardziej do tego ideału zbliżyć. W końcu wszyscy chcemy, aby strona działała sprawnie, a użytkownik z niej korzystający nie czekał długich sekund na wygenerowanie kolejnej podstrony.

Poradę dla Czytelników Dziennika Internautów przygotowała firma Kei.pl, dostawca usług hostingowych. Zapraszamy do zadawania pytań i wysyłania próśb o kolejne porady na adres redakcji.