Optymalizacja MySQL
Dla każdego serwera wirtualnego prowadzone są pomiary liczby zapytań MySQL trwających powyżej jednej sekundy. Zapytania, które są wykonywane tak długo są niekorzystne - wydłużają czas ładowania witryny oraz niepotrzebnie wykorzystują zasoby serwera. Prosimy o okresowe sprawdzanie liczby nieoptymalnych zapytań. Do osób, które obciążają bazę danych w znacznym stopniu będą wysyłane prośby o optymalizację. Najczęściej wystarczy dopisanie stosownych indeksów do bazy lub usunięcie zbędnych danych.
Wykres z liczbą nieoptymalnych zapytań do bazy MySQL jest widoczny w "informacjach" o serwerze wirtualnym w panelu klienta. Logi z treścią zapytań pojawiają się w pliku slow.log umieszczonym w głównym katalogu konta. Plik ten jest uaktualniany codziennie, między godziną 23:30 a 24:00. Po zakończeniu optymalizacji plik można usunąć lub zmienić jego nazwę.
Nieoptymalne zapytania to najczęściej:
- zapytania typu SELECT ... WHERE dla tabel bez indeksów
- zapytania typu INSERT/UPDATE dla tabel z indeksami i/lub dużą ilością danych
- zapytania z konstrukcją JOIN których warunki dotyczą kolumn bez indeksów
Jak działają indeksy w bazie?
Indeksy pozwalają na znacznie szybsze wyszukiwanie rekordów w bazie - w trakcie zapytania serwer nie musi przeszukiwać całej tabeli, a jedynie specjalnie przygotowany w tym celu indeks. Jeśli zawartość tabeli jest zmieniana (np. poleceniami INSERT, UPDATE, DELETE), indeksy są tworzone na nowo - tworzenie indeksów dla tabel o często zmieniającej się zawartości może być zupełnie nieopłacalne.
Zapytania typu SELECT
Jeśli nieoptymalne zapytania stanowią zapytania typu SELECT z konstrukcją WHERE a tabela jest częściej wykorzystywana do odczytu niż do zapisu, warto ustawić dla tabeli dodatkowe indeksy dla tych kolumn, które są zawarte w warunkach WHERE.
SELECT * FROM pracownicy WHERE nazwisko='Kowalski'
Dla powyższego zapytania powinien zostać ustawiony indeks dla kolumny "nazwisko".
SELECT * FROM pracownicy WHERE imie='Jan' AND nazwisko='Kowalski'
Dla powyższego zapytania powinien zostać ustawiony indeks złożony dla kolumn "imie" i "nazwisko".
Indeksy zakładamy również dla tych kolumn, które przeszukujemy za pomocą klauzuli LIKE.
Zapytania typu INSERT i UPDATE
Najczęściej spotykanym problemem jest zbyt duża ilość danych w tabeli, do której często dopisywane są nowe rekordy. Tabele te zwykle zawierają dane o niewielkim znaczeniu - logi, statystyki odwiedzin itp. Warto ustawić zainstalowaną aplikację w taki sposób, aby przechowywała logi jedynie przez krótki okres czasu lub w ogóle ich nie przechowywała.
Jeśli w tabeli o często zmienianej zawartości są ustawione indeksy, warto rozważyć ich usunięcie.
Zapytania z konstrukcją JOIN
Objęte indeksami powinny zostać te kolumny, które występują jako warunek złączenia. Na przykład:
SELECT imie, nazwisko, pensja FROM pracownicy INNER JOIN wynagrodzenia
ON pracownicy.id = wynagrodzenia.pracownik_id
Dla powyższego zapytania należy ustawić indeksy dla kolumn "id" w tabeli "pracownicy" oraz "pracownik_id" w tabeli "wynagrodzenia".
Jak zakładać indeksy?
Zakładanie indeksów jest zupełnie proste za pomocą narzędzia phpMyAdmin:
Wystarczy zaznaczyć kolumnę, lub kilka kolumn (ich lista się wyświetla w karcie "Struktura"), które mają zostać zaindeksowane, oraz kliknąć na ikonę "Indeks".
Założone indeksy możemy przeglądać klikając na link "Szczegóły" znajdujący się pod formularzem dodawania nowej kolumny:
Testowanie zapytań
PhpMyAdmin w czytelny sposób wyświetla wyjaśnienia i informacje o zapytaniach. Zapytanie należy wprowadzić do pola zapytania SQL w karcie "SQL", następnie kliknąć na link "Wyjaśnij SQL".
Zostanie wyświetlona informacja o zapytaniu, w której szczególnie jest interesująca kolumna "possible_keys" zawierająca nazwy kolumn, do których użyto indeksy.
Warto także zaznaczyć opcję "Profilowanie", która wyświetli szczegółową informację na temat czasu wykonywania zapytania przydatną przy testowaniu skuteczności indeksów.