Wolno działający ekran nie dowodzi, że problemem jest baza danych ani że rozwiązaniem jest indeks. To samo odczucie może wynikać z kodu PHP, wyczerpania puli połączeń, zewnętrznego wywołania HTTP, serializacji odpowiedzi, blokad albo zapytania zwracającego zbyt dużo danych. Wiedza o tym, jak badać wolne zapytania w PHP, polega na budowaniu dowodów przed zmianą schematu lub dodaniem optymalizacji, które mogłyby zwiększyć koszt zapisów.
Oddzielanie opóźnień aplikacji od opóźnień danych
Zacznij od rozłożenia całkowitego czasu żądania. Rejestruj możliwą do zidentyfikowania trasę lub polecenie, czas rozpoczęcia i zakończenia, wykonane zapytania, ich czasy trwania oraz zewnętrzne zależności. Sam pomiar średniego czasu nie wystarczy: API może wyglądać na zdrowe, a mimo to zawodzić dla określonych filtrów, klientów lub głębokich stron.
Dla każdego incydentu warto rozróżnić:
- Czas w PHP: transformację kolekcji, pętle, serializację JSON, generowanie dokumentów lub nadmierne użycie pamięci.
- Czas bazy danych: czas trwania każdego zapytania, oczekiwanie na blokady, otwieranie połączenia i liczbę przesłanych wierszy.
- Czas sieci i zależności: zdalne pamięci podręczne, API stron trzecich, przechowywanie plików, kolejki lub usługi tożsamości.
- Czas w kolejce: żądania oczekujące na workery PHP, dostępne połączenia lub zasoby bazy danych.
Używaj śledzenia lub ustrukturyzowanych logów z identyfikatorem żądania. Pojedyncze zapytanie trwające 20 ms może stać się problemem liczonym w sekundach, jeśli zostanie wykonane setki razy w tej samej odpowiedzi. I odwrotnie, zapytanie trwające 500 ms może nie być główną przyczyną, jeśli proces pozostaje przez kilka sekund w oczekiwaniu na usługę zewnętrzną.
Zbieranie dowodów przed zmianą kodu
Przechwytuj sparametryzowane zapytanie oraz osobno reprezentatywne parametry. Unikaj rejestrowania sekretów, pełnych danych osobowych lub wartości, które nie są potrzebne do odtworzenia przypadku. Wyszukiwanie według częstego statusu nie zachowuje się tak samo jak wyszukiwanie według unikalnego identyfikatora; ocena wyłącznie wygodnego przypadku prowadzi do błędnych decyzji.
Minimalny zestaw dowodów powinien obejmować:
- Trasę, zadanie asynchroniczne lub polecenie, którego dotyczy problem, oraz jego częstotliwość.
- Zaobserwowany czas trwania, wysokie percentyle i moment występowania.
- SQL, parametry z określonymi typami i liczbę wykonań na żądanie.
- Zwrócone wiersze oraz, gdy to możliwe, odczytane lub zbadane wiersze.
- Przybliżony rozmiar tabel i rozkład filtrowanych wartości.
- Współbieżność, równoczesne operacje zapisu i istotne blokady.
Logi wolnych zapytań silnika pomagają wykrywać kandydatów, ale nie zastępują śledzenia aplikacji: zazwyczaj nie wskazują, który endpoint zbudował zapytanie ani ile razy zostało ono powtórzone. W MySQL i PostgreSQL połącz te informacje z metrykami połączeń, CPU, I/O i czasów oczekiwania, aby nie pomylić słabego planu z tymczasowo przeciążoną infrastrukturą.
Wykrywanie wzorców mnożących koszt
Przed analizą złożonej instrukcji poszukaj częstych wzorców. N+1 występuje wtedy, gdy lista pobiera główne wiersze, a następnie wykonuje dodatkowe zapytanie dla każdej relacji. Nawet jeśli każde pojedyncze zapytanie jest szybkie, liczba podróży do bazy danych, pracy planowania i kontencji rośnie wraz z rozmiarem strony.
Podejrzane są także filtry o niskiej selektywności, takie jak bardzo częste statusy; funkcje zastosowane do filtrowanych kolumn; niejawne konwersje typów; wyszukiwania z początkowym symbolem wieloznacznym; sortowania dużych zbiorów i paginacja z wysokim OFFSET. Żądanie SELECT * może zwiększać transfer, zużycie pamięci i pracę odczytową, nawet gdy plan już wykorzystuje indeks.
Naprawa nie zawsze oznacza jedno zapytanie. Kontrolowane ładowanie relacji może rozwiązać N+1, ale ładowanie zachłanne bez ograniczeń może utworzyć ogromne zapytanie lub odpowiedź. Określ, jakich relacji trasa rzeczywiście potrzebuje, ogranicz ich kolumny i zmierz efekt przy oczekiwanym rozmiarze strony.
Interpretowanie planu wykonania na rzeczywistych danych
Uruchom EXPLAIN, aby poznać proponowany plan, i użyj wariantu uwzględniającego rzeczywiste wykonanie, gdy jest to bezpieczne i odpowiednie dla środowiska. W PostgreSQL EXPLAIN ANALYZE wykonuje zapytanie; instrukcji modyfikującej nie należy analizować w ten sposób bez zrozumienia jej skutku. W MySQL dostępne tryby zależą od wersji i konfiguracji, ale cel jest ten sam: porównać estymacje z rzeczywistą pracą.
Skan sekwencyjny nie jest automatycznie zły. Jeśli zapytanie potrzebuje dużej części małej tabeli lub tabeli o niskiej selektywności, jej przeszukanie może kosztować mniej niż przechodzenie między indeksem a tabelą. Zbadaj natomiast przypadki, gdy plan pokazuje znacznie więcej rzeczywistych wierszy niż oszacowanych, kosztowne sortowania, odczyty tymczasowe, joiny na dużych zbiorach lub wewnętrzne pętle wykonywane wiele razy.
Przydatne pytania podczas przeglądu planu
- Ilu wierszy oczekiwał optymalizator i ile rzeczywiście przetworzył?
- Który węzeł pochłania najwięcej czasu, odczytów lub iteracji?
- Czy filtr jest stosowany wcześnie, czy po połączeniu dużych zbiorów?
- Czy sortowanie obejmuje więcej wierszy, niż potrzebuje odpowiedź?
- Czy statystyki odzwierciedlają aktualny rozkład danych?
Rozbieżności między estymacją a rzeczywistością mogą wymagać aktualizacji statystyk lub przeglądu typów i warunków, a nie natychmiastowego utworzenia indeksu. Plan jest wyjaśnieniem wykonania przy określonych parametrach i obciążeniu, a nie automatycznym poleceniem wprowadzenia zmiany.
Wybór między przepisaniem, paginacją, dostępem do danych a indeksem
Najpierw ogranicz nieuniknioną pracę. Wybieraj tylko potrzebne kolumny, stosuj rozsądne limity, usuwaj nieużywane relacje i unikaj przenoszenia całych historii do PHP w celu późniejszego filtrowania. Jeśli przypadek użycia wymaga przeglądania rosnącej historii, zastąp głęboką paginację paginacją opartą na kursorze lub kluczu: na przykład kontynuuj od stabilnej kombinacji daty i identyfikatora zamiast odrzucać tysiące wierszy za pomocą OFFSET.
Przejrzyj joiny i filtry, aby porównywały zgodne kolumny i jasno wyrażały warunek. Czasami warto odpytywać relację partiami; innym razem lepsze jest jedno, dobrze ograniczone zapytanie. Decyzja zależy od kardynalności, rozmiaru odpowiedzi i częstotliwości, a nie od uniwersalnej reguły.
Indeks złożony pomaga, gdy odpowiada wzorcowi dostępu. Jego kolejność ma znaczenie: zazwyczaj kolumny równościowe i selektywne powinny ułatwiać filtrowanie przed kolumnami używanymi do zakresu lub sortowania, ale wynik określają konkretne zapytanie i silnik. Indeks może również pomóc uniknąć sortowania, jeśli pokrywa zgodny porządek, choć nie każda kombinacja WHERE i ORDER BY na to pozwala.
Unikaj indeksowania kolumn tylko dlatego, że pojawiają się w warunku. Indeksy zajmują miejsce, zużywają pamięć i dodają pracy operacjom INSERT, UPDATE oraz DELETE. Nadmiarowe indeksy lub indeksy o niskiej użyteczności mogą pogorszyć działanie systemu z intensywnym zapisem. Sprawdź także, czy indeks pozwala uzyskać wymagane kolumny bez dodatkowych odczytów, ale nie dodawaj kolumn pokrywających bez zmierzenia kosztu.
Hipotetyczny przykład: historia operacji
Załóżmy listę pokazującą operacje według konta, statusu i daty. W miarę rozrastania się historii strona 200 działa coraz gorzej. Pierwszą hipotezą mogłoby być utworzenie indeksu na dacie. Śledzenie ujawnia jednak główne zapytanie, po którym następuje jedno zapytanie dla każdej operacji w celu pobrania odpowiedzialnego użytkownika: występuje N+1. Plan głównego zapytania dodatkowo odczytuje wiele wierszy, aby odrzucić wcześniejsze za pomocą OFFSET.
Rozsądna sekwencja polegałaby na grupowym ładowaniu odpowiedzialnych użytkowników lub zastosowaniu joina ograniczonego do potrzebnych kolumn, zastąpieniu głębokiej paginacji kursorem opartym na created_at i id, a następnie ponownym pomiarze. Dopiero wtedy ocenia się indeks zgodny z filtrem konta, stabilnym porządkiem i kursorem. Zmiana dostępu może ograniczyć więcej pracy niż pojedynczy indeks, a końcowy indeks należy również sprawdzić pod kątem tworzenia nowych operacji.
Walidacja pod obciążeniem i monitorowanie regresji zapisu
Porównaj stan przed i po zmianie przy reprezentatywnych parametrach, rozkładzie danych i współbieżności. Mierz czas trwania, przetworzone wiersze, odczyty, użycie CPU, pamięć w PHP, rozmiar odpowiedzi i liczbę zapytań na żądanie. Dla nowego indeksu zmierz również opóźnienie i przepustowość objętych nim operacji zapisu.
Określ jawne limity akceptacji: na przykład weryfikowalne obniżenie wysokiego percentyla trasy bez niedopuszczalnego wzrostu czasu tworzenia lub aktualizacji. Przetestuj puste przypadki, bardzo częste wartości, rzadkie filtry, pierwsze i ostatnie strony oraz uprawnienia zmieniające zakres danych.
Wdrażanie zmian w sposób obserwowalny i odwracalny
Gdy to możliwe, oddziel wdrożenie kodu od utworzenia indeksu. Tworzenie indeksów może konkurować o zasoby lub uzyskiwać blokady w zależności od silnika, operacji i środowiska. Zaplanuj termin, sprawdź metodę obsługiwaną przez bazę danych oraz monitoruj czas trwania, błędy i presję na I/O.
Wprowadzaj nowe zapytanie stopniowo, jeśli architektura na to pozwala, i utrzymuj jasną ścieżkę wycofania: przywrócenie poprzedniego zapytania, wyłączenie alternatywnej ścieżki dostępu lub usunięcie indeksu, który okaże się szkodliwy. Wycofanie nie zastępuje kopii zapasowych ani przeglądu migracji, ale skraca czas ekspozycji na nieoczekiwane zachowanie.
Checklist dla powtarzalnego usprawnienia
- Zidentyfikuj trasę, objaw i parametry, które go odtwarzają.
- Oddziel czas PHP, bazy danych, sieci i zewnętrznych zależności.
- Określ liczbę powtórzeń, zwrócone wiersze i przetworzone wiersze.
- Szukaj N+1, głębokiej paginacji, mało selektywnych filtrów i sortowań.
- Przejrzyj plan i porównaj jego estymacje z rzeczywistym wykonaniem.
- Przetestuj redukcję danych, przepisanie i zmianę paginacji przed indeksowaniem.
- Zaprojektuj indeks zgodnie z pełnym wzorcem filtrowania, sortowania i zapisu.
- Zweryfikuj odczyty i zapisy przy reprezentatywnym obciążeniu, obserwowalności i możliwości wycofania.
