Podstawy optymalizacji zapytań MySQL

Jeżeli zabierasz się za optymalizację zapytań w MySQL absolutnym minimum o jakim musisz pamiętać jest "query cache". W skrócie chodzi o to, że serwer MySQL trzyma sobie w pamięci podręcznej wyniki zadanych wcześniej zapytań, więc proste porównanie czasów wykonania zapytań nie jest do końca wiarygodne. Pierwsze wykonanie zapytania może potrwać dużo dłużej niż kolejne. Można jednak zadając zapytanie "poprosić" serwer o nie używanie pamięci podręcznej. Wystarczy w zapytaniu użyć "SQL_NO_CACHE" bezpośrednio po słowie "SELECT", np.:

    SELECT SQL_NO_CACHE * FROM tabela WHERE pole = 'warunek';

Jeżeli pomimo użycia SQL_NO_CACHE nadal nie widzisz różnic w czasach wykonania zapytań, możesz jeszcze spróbować:

1. Zwiększyć ilość danych w tabelach, których używasz do testów.
2. Sprawdzić czy query cache jest włączony na Twoim serwerze (SHOW VARIABLES LIKE '%query_cache%')
3. Ręcznie czyścić zawartość pamięci podręcznej (RESET QUERY CACHE)

Może Wam się przytrafić jeszcze jedna dziwna rzecz. Gdy pierwsze zapytanie do tabeli trwało znacznie dłużej niż inne i nie możecie tego powtórzyć. Należy pamiętać, że przy pierwszym użyciu danej tabeli część informacji dotycząca danej tabeli jest ładowana z plików na dysku do pamięci. Opóźnienie to jest spowodowane właśnie tym pierwszym odczytem.

Więcej informacji znajdziecie tutaj:

• http://dev.mysql.com/doc/refman/5.1/en/query-cache.html
• http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html

Możemy teraz przejść do samej optymalizacji. Poniżej zamieszczamy kilka wskazówek od których warto zacząć (pominęliśmy wskazówki dotyczące konfiguracji samego serwera):

1. Używaj EXPLAIN (http://dev.mysql.com/doc/refman/5.0/en/explain.html)
2. Używaj opcji logowania wolnych zapytań na serwerze MySQL (http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html)
3. Nie używaj ORDER BY RAND() dla tabel o dużej ilości rekordów (więcej niż kilka tysięcy)
4. Jeżeli masz wybór użyj GROUP BY zamiast DISTINCT
5. Pamiętaj o używaniu indeksów, ale nie przesadzaj z ich ilością (zwróć też uwagę, aby się nie duplikowały)
6. Pobieraj tylko te dane, których naprawdę potrzebujesz, zamiast

   SELECT * FROM ...

   użyj

   SELECT nazwy, pol, ktorych, uzywasz FROM ...

7. Zwróć uwagę na dobór typów danych dla pól (np. czy zamiast "int" nie wystarczy użycie "smallint" lub "varchar" zamiast "text")
8. Przeanalizuj wszystkie złożone zapytania - czasem lepiej zadać dwa lub trzy proste zapytania niż jedno skomplikowane.
9. Używaj rozważnie różnych typów tabel (szczególnie MYISAM I INNODB)
10. Przetestuj też zapytania w innym środowisku niż developerskie (np. na specjalnie do tego przeznaczonym serwerze testowym, gdzie dane na których operujesz są bardziej zróżnicowane i bardziej zbliżone do docelowych).