Baza wiedzy

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 liczby nieoptymalnych zapytań

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:

Wstawianie indeksów w 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:

Przeglądanie indeksów w phpMyAdmin

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".

Link do wyjaśnień 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.

Oceń przydatność:


Zobacz także: