Krótki wpis o optymalizacji cudzych zapytań
Otrzymałem niedawno „w spadku” zlecenie. Właściwie nic szczególnego, kolejna strona wymagająca dokończenia i wprowadzenia brakujących funkcjonalności. Jednym z punktów było dodanie tagów do wyszukiwarki i właśnie ten punkt zainspirował mnie do napisania poniższej notatki.
Krok 1.
Załóżmy, że właściciel serwera nie ma ochoty udostępniać logów z MySql. W takim wypadku trzeba będzie podejść do zagadnienia od strony samego skryptu. Każdy programista ma swój sposób wywoływania zapytań do bazy. Od tego jaki to sposób będzie zależało dalsze postępowanie.
- Zapytania wywoływane poprzez mysql_query.
Masz pecha. To oznacza, że będziesz musiał podmienić we wszystkich skryptach wywołanie tej funkcji na funkcję pośredniczącą (np. wywolaj_zapytanie). Możesz także skupić się wyłącznie na podstronach, które w widoczny sposób przymulają (prawdopodobnie będą to strony zawierające wyszukiwarkę, rozbudowaną podstronę z prezentacją danych, podstrony z drzewiastym menu). - Zapytania wywoływane przez klasę lub funkcję pośredniczącą.
Jest dobrze. Wystarczy, że znajdziesz miejsce, w którym jest odpowiednia definicja. Być może logowanie zapytań będzie już dostępne. Jeśli nie ma, to dodaj odpowiednią funkcję.
Masz już zapisywanie zapytań, ale przydałaby się odpowiednia „oprawa” dla nich. Potrzebne będą informacje o tym, w którym miejscu zostało wywołane zapytanie (debug_backtrace) oraz ile trwało. Dobrze jest też zmienić odpowiednie zapytania na EXPLAIN SELECT. To ułatwi dalsze kroki. Zapytania należy zmienić według następującej formuły:
SELECT X FROM Y WHERE Z => EXPLAIN SELECT X FROM Y WHERE Z
UPDATE X SET Y WHERE Z => EXPLAIN SELECT * FROM X WHERE Z
DELETE FROM X WHERE Y => EXPLAIN SELECT *FROM X WHERE Y
Zapytania typu INSERT można pominąć. Właściwie, jeśli poprawisz samo pobieranie danych (o ile wymaga poprawiania), to cała reszta powinna zacząć chodzić także z większą szybkością.
Gdy już masz listę zapytań możesz przystąpić do następnego kroku.
Krok 2.
Odpal phpmyadmina i uruchom najdłużej wykonujące się zapytania. Przy każdym zapytaniu otrzymasz tabelkę przedstawiającą propozycję ze strony jednego z modułów MySQL (całe wyliczenie opiera się głównie na prawdopodobieństwie, więc nie zawsze jest zgodne z rzeczywistością; mimo wszystko daje to pewien obraz zapytania). Zwróć uwagę na kolumny: type, rows i extra. Pierwsza z nich określa „mechanizm” pobierania danych z tabeli. Jeśli jest ALL, to znaczy, że baza odwoła się do każdego rekordu jaki znajduje się w tabeli. Kolumna „rows” określa liczbę rekordów branych pod uwagę. Im mniejsza liczba w każdym wierszu tabelki, tym lepiej. Idealnie będzie, jeśli przy głównej tabeli będzie liczba zgodna z liczbą elementów wyświetlanych na ekranie, a przy pozostałych widnieć będą same jedynki. Ostania kolumna „extra” zawiera dodatkowe informacje odnośnie wykonanego zapytania. W kolumnie tej mogą się pojawić hasła: using where, using filesort, using temporary, itd. Pomagają one określić z czym mamy do czynienia. Przykładowo „using where” oznacza, że do ograniczenia liczby wyników w zapytaniu MySQL użyło warunków podanych w where.
Najwolniejsze zapytania będą prawdopodobnie składały się głównie z ALL, using where, using filesort i using temporary (najgorsze kombo jest wtedy, gdy wystąpią wszystkie na raz przy tabeli z milionami rekordów).
Krok 3.
Masz już wszystkie potrzebne informacje. Teraz pozostało dodanie kluczy lub przepisanie zapytań. Dodanie kluczy będzie wymagało odrobiny kombinowania. Zbyt wiele indeksów może wpłynąć na działanie całości strony (wolniejsze wstawianie nowych rekordów do bazy). Ogólnie rzecz biorąc sprawa wygląda tak:
- najlepiej jest stosować klucze, które będą wykorzystywane przez większość zapytań działających na danej tabeli,
- klucze mogą obejmować więcej niż jedną kolumnę, przy czym na początku powinna być umieszczona kolumna o największym rozrzucie danych,
- indeksy powinny być nałożone na kolumny, które są używane przy złączeniach ( tabela1.a = tabela2.b, using (a) ) lub na te, które występują w warunku WHERE
Same zmiany najlepiej wykonywać na kopii bazy (lub jeśli tabele są zbyt wielkie, to na kopii struktury i losowo wygenerowanych danych).
Niekiedy bywa tak, że samo dodanie kluczy nie wystarcza. Na szczęście masz na liście zapytań także informacje o tym skąd zostały one wywołane. I podobnie jak wcześniej najlepiej jest testować nowe zapytania na kopii. Pamiętaj, że nowe zapytanie musi zwrócić te same dane przyporządkowane do kolumny nazwanych tak samo. Upewnij się także, że poprzedni programista nie zrobił sobie „jaj”. Dane mogą być pobierane poprzez mysql_fetch_row (lub poprzez mysql_fetch_array) i przetwarzane dalej przez skrypt jako tablice z indeksami liczbowymi (echo $row[1].’ ’.$row[3]; ). W takim przypadku (po rzuceniu kilku przekleństw) zrób nowe zapytania tak, żeby także kolejność kolumn zgadzała się ze starą wersją.
Krok 4.
Gdy już tabele zostały poprawione, a zapytania przepisane, pora porozglądać się po stronie. Sprawdź czy wszystko wyświetla się tak jak wcześniej (patrz nie tylko na elementy widoczne, ale także na to, co siedzi w kodzie strony). Jeżeli wszystko jest jak trzeba, to możesz odetchnąć z ulgą i zająć się kolejnym zleceniem.
Dodaj komentarz