Przejdź do treści 
Dzięki funkcji „optimizer trace” w MariaDB krok po kroku rozumiem, dlaczego optymalizator wybiera konkretny plan i które warianty odrzuca. Ten ślad w formacie JSON pokazuje mi Decyzje dotyczących kosztów, kolejności połączeń i filtrów, co pozwala mi precyzyjnie dostosowywać zapytania SQL.
Punkty centralne
- Przejrzystość: Ścieżka oparta na formacie JSON wyjaśnia zmiany, koszty i odrzucone plany.
- Koncentracja: join_preparation i join_optimization dostarczają najważniejszych informacji.
- System sterowania: Zmienne sesji ograniczają obciążenie i zużycie pamięci.
- Przepływ pracy: EXPLAIN/ANALYZE w celu uzyskania planu, Trace w celu ustalenia przyczyny.
- Praktyczne korzyści: Przeprowadzać przemyślane dostosowania indeksów, statystyk i kolejności połączeń.
Czym jest ślad optymalizatora MariaDB?
Od wersji 10.4 MariaDB wprowadziła Optymalizator Trace, który dokumentuje w formacie JSON każdy większy etap optymalizacji instrukcji SELECT, UPDATE lub DELETE. Dzięki temu widzę, w jaki sposób silnik rozszerza zapytania, normalizuje warunki i ostatecznie ustala kolejność połączeń (join) wraz z dostępem do indeksów. Ten wgląd jest znacznie głębszy niż w przypadku polecenia EXPLAIN, które pokazuje przede wszystkim plan końcowy, i ujawnia odrzucone alternatywy wraz z uzasadnieniami. Ślad ten jest przechowywany w pamięci dla każdego połączenia i jest dostępny poprzez information_schema.OPTIMIZER_TRACE gotowe. W ten sposób otrzymuję kompletne, nadające się do odczytu maszynowego wyjaśnienie wewnętrznych Kroki, które doprowadziły do opracowania planu realizacji.
Włączenie i odczyt śladu optymalizatora
Włączam tę funkcję selektywnie dla poszczególnych sesji, aby móc przeprowadzać diagnostykę bez ogólnego obciążenia systemowego i zachować pełną kontrolę nad Pamięć mam. Zazwyczaj wpisuję SET SESSION optimizer_trace = 'enabled=on'; i w razie potrzeby SET SESSION optimizer_trace_max_mem_size = 1048576; lub wyższy, jeśli ślad będzie obszerny. Następnie uruchamiam podejrzane zapytanie i odczytuję ślad za pomocą SELECT * FROM information_schema.OPTIMIZER_TRACE LIMIT 1\G;. Uwaga: Tabela zapisuje tylko ostatnie zapytanie z aktywnego połączenia, a ja zwracam uwagę na pola takie jak MISSING_BYTES_BEYOND_MAX_MEM_SIZE lub INSUFFICIENT_PRIVILEGES w celu uzyskania wskazówek diagnostycznych. Taki sposób działania pozwala zachować oszczędność środowiska produkcyjnego i ułatwia analizę dokładny.
| Zmienna/pole | Cel | Przykładowa wartość |
|---|---|---|
optimizer_trace | Włącza śledzenie na sesję | 'enabled=on' |
optimizer_trace_max_mem_size | Maksymalna pojemność pamięci na ślad | 1048576 (1 MB) |
OPTIMIZER_TRACE.QUERY | Oryginalna instrukcja SQL | SELECT ... |
OPTIMIZER_TRACE.TRACE | Dokument JSON dotyczący optymalizacji | Tekst JSON |
MISSING_BYTES_BEYOND_MAX_MEM_SIZE | Utrata bajtów w przypadku zbyt dużego pliku śledzenia | 0 lub liczba |
INSUFFICIENT_PRIVILEGES | Czy uprawnienie do odczytu jest wystarczające? | 0 lub 1 |
Struktura JSON: join_preparation i join_optimization
Struktura JSON dzieli się na następujące bloki: przygotowanie_połączenia oraz optymalizacja połączeń, które przeglądam w pierwszej kolejności, ponieważ są najważniejsze Uwagi dostarczać. W sekcji przygotowanie_połączenia rozpoznaję rozszerzone zapytanie (rozszerzone_zapytanie) i sprawdzam, czy i w jaki sposób silnik przekształcił warunki lub prognozy. Drugi blok optymalizacja połączeń rejestruje szacunki liczby wierszy, analizowane plany, wybraną kolejność połączeń oraz dołączanie selektywnych części klauzuli WHERE do tabel. Szczególnie przydatne są poddrzewa rows_estimation, rozważane_plany_wykonania oraz dodawanie_warunków_do_tabel, ponieważ odnoszą się bezpośrednio do założeń dotyczących kosztów i pozycji filtrów. Dzięki temu szybko dostrzegam, gdzie pojawiły się błędne oceny lub niekorzystne Wskaźniki prowadzą do nieoptymalnych planów.
Porównanie za pomocą EXPLAIN i ANALYZE
Aby uzyskać pełną ocenę, łączę polecenia EXPLAIN, ANALYZE oraz Ślad w ramach ustalonego schematu. Najpierw korzystam z WYJAŚNIENIE lub EXPLAIN FORMAT=JSON, aby wyświetlić wybrany plan i ścieżki kluczowe. Następnie ustawiam WYJAŚNIJ ANALIZĘ w celu uzyskania rzeczywistych danych dotyczących czasu wykonania oraz wartości liczbowych, takich jak pętle i przefiltrowane wiersze. Jeśli pozostają jakieś pytania, włączam śledzenie optymalizatora (Optimizer Trace) i sprawdzam, które warianty optymalizator sprawdził i odrzucił. Zwięzłe wprowadzenie do interpretacji tych danych znajdę w tym artykule na temat Zrozumieć polecenia EXPLAIN i ANALYZE, z którego korzystam w razie potrzeby jako uzupełnienie.
Analiza decyzji dotyczących planów: koszty, kardynalności, filtry
Logika podejmowania decyzji opiera się na kardynalnościach, modelach kosztów oraz rozmieszczeniu Filtr zgodnie z planem. W śladzie widzę dla każdej analizowanej kolejności połączeń, jakich zbiorów wierszy oczekuje silnik i w jaki sposób na tej podstawie oblicza całkowity koszt. Sprawdzam, czy nieaktualne statystyki lub niekorzystne korelacje powodują, że skanowanie zakresów jest niedoszacowane, a preferowane jest skanowanie pełne. Ponadto sprawdzam, czy silnik wystarczająco wcześnie dołącza warunki WHERE do tabeli o największej selektywności, aby zminimalizować kosztowne etapy łączenia. W ten sposób mogę sformułować rzetelne wnioski dotyczące tego, dlaczego wybrano dany plan i jak mogę go zoptymalizować za pomocą Wskaźniki, na które mają wpływ przeróbki lub aktualizacja statystyk.
Praktyka: śledzenie prostego zapytania z filtrem
Na stronie SELECT * FROM t1 WHERE a < 10 sprawdzam pod przygotowanie_połączenia, czy silnik rozszerzył projekcję i ewentualnie skonsolidował warunki, co dało mi pierwsze Wskaźniki dostarcza. Następnie widzę w bloku rows_estimation, ile wierszy silnik przeznacza na skanowanie zakresu w a w porównaniu z pełnym skanowaniem tabeli. Jeśli pojawiają się nierealistyczne wartości, często interpretuję to jako oznakę nieaktualnych statystyk lub braku histogramów. W sekcji rozważane_plany_wykonania Następnie sprawdzam, czy dostęp do indeksu został rzeczywiście wyliczony jako tańszy niż skanowanie pełne. Na koniec pokazuje dodawanie_warunków_do_tabel, czy warunek selektywny dotyczy a realizacja przebiega zgodnie z harmonogramem, co znacznie skraca czas trwania obniżki.
Funkcje JSON: celowe wyodrębnianie fragmentów
Ponieważ ślad jest w formacie JSON, filtruję konkretne poddrzewa za pomocą JSON_EXTRACT i tworzę proste analizy dotyczące powtarzających się Próbka. Na przykład przeglądam tylko listę rozważanych planów, aby sprawdzić, czy określone sekwencje połączeń (join) systematycznie kończą się niepowodzeniem. Podobnie wyodrębniam pola kosztów najlepszych kandydatów i porównuję je z danymi z ANALYZE, aby wykryć błędne założenia. Za pomocą prostych widoków lub procedur przechowywanych automatyzuję te sprawdzania na potrzeby moich sesji diagnostycznych. W ten sposób tworzę sobie proste Monitoring do podejmowania decyzji przez optymalizator bez włączania stałego śledzenia.
Typowe zastosowania i korzyści
Korzystam z funkcji Trace, gdy EXPLAIN wskazuje nieoczekiwane pełne skanowanie i chcę ustalić przyczynę odrzucenia Indeks chcę się dowiedzieć. Ponadto w przypadku wielu tabel log ten dostarcza mi uzasadnienie wybranej kolejności połączeń, co wskazuje mi drogę do alternatywnych planów. W przypadku zmiany wersji zapisuję ślady przed i po aktualizacji, aby ocenić zmiany w zachowaniu optymalizatora. W kwestiach strategicznych związanych z tuningiem ten przegląd pomaga mi w wewnętrzne mechanizmy optymalizatora, które łączę z wynikami śledzenia. W ten sposób w sposób uporządkowany podejmuję decyzję, czy w indeksach, statystykach czy sformułowaniach zapytań należy śruba regulacyjna postawię.
Najlepsze praktyki w zakresie produkcji
Konsekwentnie włączam śledzenie jako Sesja-Zakończę diagnostykę w odpowiedni sposób, gdy tylko zgromadzę wystarczającą ilość danych. W przypadku dużych śladów zwiększam optimizer_trace_max_mem_size tylko na krótki czas, a potem ponownie ustawiam tę wartość na niską. Zanim udostępnię pliki JSON, maskuję wrażliwe stałe, teksty komentarzy lub wskaźniki biznesowe. Korzystam z funkcji śledzenia wyłącznie jako narzędzia diagnostycznego, natomiast do ciągłego monitorowania preferuję logi powolnych zapytań, widoki wydajności lub zewnętrzne profilery. Takie podejście pozwala utrzymać systemy w optymalnej kondycji i zapobiega niepotrzebnemu Nad głową w codziennej działalności.
Ślad optymalizatora w zestawie narzędzi
Aby zapewnić kompleksowe dostosowanie, przedstawiam łańcuch składający się ze zrozumienia planu, analizy przyczyn i pomiaru systemu, a następnie łączę te Ustalenia. Polecenie EXPLAIN pokazuje mi plan, ANALYZE potwierdza rzeczywiste koszty, a ślad dostarcza informacji uzasadniających podjętą decyzję. Równolegle zapoznaję się z koncepcjami planów wykonania zapytań, aby rozpoznać wzorce w doborze kluczy, kardynalnościach i strategiach połączeń. Dobrym uzupełnieniem tej perspektywy jest zwięzły przegląd dotyczący Plany wykonania zapytań, z którego korzystam w kwestiach architektonicznych. Na tej podstawie wywodzę wiarygodne Priorytety dotyczących indeksowania, przeróbek i parametrów.
Bardziej szczegółowo: analiza zakresu i dobór klucza
W śladzie często znajduje się blok analiza zakresu dla każdej tabeli, w której mogę rozpoznać, które indeksy wchodziły w grę dla dostępu typu range, ref lub eq-ref. Optymalizator porównuje tam alternatywy, takie jak „range na idx_a“, „range na idx_b“ lub „pełne skanowanie“, przypisuje im koszty i oczekiwaną liczbę wierszy, a następnie wskazuje zwycięską opcję. Jeśli zauważę, że sensowny indeks został odrzucony ze względu na wysokie koszty, w następnej kolejności sprawdzam leżące u jego podstaw selektywności i statystyki. Jeśli założenia są nieprawidłowe, może ANALYZE TABLE (ewentualnie z trwałymi statystykami) lub utworzenie bardziej ukierunkowanego Wskaźnik pokrycia unieważnić tę decyzję.
Warto również przyjrzeć się podziałom indeksów złożonych: ślad dokumentuje, czy warunek wykorzystuje tylko pierwszą kolumnę indeksu, czy też możliwe jest zastosowanie dodatkowych predykatów, co sprawia, że kolejne kolumny klucza stają się istotne. Na tej podstawie decyduję, czy należy przeformułować predykaty (na przykład unikać funkcji), czy też rozszerzyć indeks tak, aby uwzględniał typowe operacje filtrowania i sortowania.
Szczegóły dotyczące połączeń: półpołączenia, BKA/MRR i bufory połączeń
W przypadku zapytań wielotablicowych fragmenty śladu pokazują, czy i jaką strategię semijoinu uwzględniono (np. FirstMatch, DuplicateWeedout, LooseScan lub Materialization). Widzę tam, dlaczego dana opcja została odrzucona – na przykład z powodu wysokich kosztów materializacji lub zbyt niskiej selektywności. Również Batched Key Access (BKA) oraz Odczyt wielozakresowy (MRR) pojawiają się w śladzie, o ile są włączone. Techniki te grupują operacje wyszukiwania kluczy i poprawiają lokalność pamięci podręcznej. Jeśli BKA/MRR nie pojawiają się w śladzie, sprawdzam optimizer_switch oraz parametry takie jak join_cache_level. W obciążeniach charakteryzujących się dużą liczbą losowych wyszukiwań kluczy pozwala to w zauważalny sposób przyspieszyć fazę łączenia, co można zweryfikować za pomocą polecenia EXPLAIN ANALYZE.
Decydujące znaczenie ma również rozmiar i rodzaj bufora połączeń: ślad pokazuje, czy warianty pętli zagnieżdżonych były uruchamiane z buforem czy bez niego oraz w którym miejscu stosowane są filtry. Oceniam, czy dodanie indeksów na kluczach połączeń lub przepisanie kodu w celu zmniejszenia liczby wyników pośrednich jest bardziej efektywnym rozwiązaniem niż zwiększanie rozmiarów buforów.
Podzapytania, tabele pochodne i widoki
Na stronie przygotowanie_połączenia Uważam, że czy podzapytania w postaci EXISTS/IN w Półpołączenia zostały przekształcone (in_to_exists), czy tabele pochodne zostały scalone (połączone_wynikowe) lub zostały zrealizowane oraz czy Warunek Wypychanie aż do tabel pochodnych. Te kroki mają kluczowe znaczenie, ponieważ brak scalania może prowadzić do kosztownej materializacji. Jeśli w śladzie wielokrotnie dostrzegam decyzje o materializacji o wysokich kosztach, sprawdzam, czy jawne STRAIGHT_JOIN, czy wskazówka lub reorganizacja zapytania (np. wyrażenia typu Common Table Expression z ukierunkowanymi filtrami) skłania silnik do zastosowania korzystniejszej strategii. W przypadku widoków sprawdzam, czy optymalizator wystarczająco rozdziela zawartość widoku lub czy w tabeli bazowej brakuje dodatkowych indeksów.
Podział na partycje i przycinanie
W przypadku tabel podzielonych na partycje ślad pokazuje, które partycje zostały wykluczone na podstawie kluczy partycji i predykatów (Ograniczanie partycji). Jeśli nie dochodzi do oczekiwanego przycinania, jest to sygnał, że należy sformułować filtry wcześniej i w sposób umożliwiający ich zastosowanie do klucza partycji. Zwracam również uwagę na współdziałanie partycjonowania i indeksów: w przypadku braku indeksów lokalnych lub globalnych silnik może, pomimo przycinania, sprawdzać nadmierną liczbę wierszy, co w śladzie jest widoczne jako wysokie koszty skanowania.
Celowa weryfikacja wskazówek, ustawień indeksów i parametru `optimizer_switch`
Korzystam z funkcji Trace, aby sprawdzić działanie wskazówek i przełączników parametrów, aby zajmować. Jeśli na przykład wpiszę. WSKAŹNIK SIŁY lub wskazówkę optymalizatora widzę w śladzie, czy alternatywa została rzeczywiście wymuszona i jak została oceniona. Poprzez optimizer_switch mogę tymczasowo włączać lub wyłączać strategie (np. w przypadku decyzji dotyczących semijoin, index_merge lub derived_merge). Ślad służy mi wówczas jako dowód, czy silnik przyjął te ustawienia, czy też nadal dominują inne ograniczenia (np. kardynalności). Opcjonalnie używam flag formatowania, takich jak one_line lub end_markers na stronie optimizer_trace-ciąg znaków, aby dostosować czytelność do mojego narzędzia analitycznego.
Update/DELETE i ścieżki zapisu
Ślad optymalizatora nie ogranicza się wyłącznie do instrukcji SELECT. W przypadku instrukcji UPDATE i DELETE również widzę, w jaki sposób wybierane są ścieżki dostępu oraz czy filtry są stosowane wystarczająco wcześnie, aby ograniczyć liczbę wierszy objętych operacją. Sprawdzam, czy filtr WHERE nie jest typu „sargable” lub czy brak indeksu nie prowadzi do szerokiego skanowania przed wykonaniem właściwej zmiany. Na podstawie śladu ustalam, czy kompaktowy indeks (np. zawierający tylko niezbędne kolumny) pozwala uniknąć niepotrzebnych wielokrotnych operacji odczytu i zapisu, a tym samym zmniejsza liczbę blokad i objętość dziennika.
Bezpieczeństwo, uprawnienia i przygotowane instrukcje
Aby móc odczytać cały zapis, potrzebuję odpowiednich uprawnień do obiektu – jeśli ich brakuje, pole to sygnalizuje to INSUFFICIENT_PRIVILEGES Ograniczenia. W scenariuszach zbliżonych do środowiska produkcyjnego korzystam zatem z tych samych danych logowania, co aplikacja, lub ze specjalnego konta diagnostycznego z odpowiednimi uprawnieniami. W przypadku przygotowanych instrukcji (Prepared Statements) ślad zazwyczaj pokazuje już zoptymalizowaną formę z powiązanymi parametrami, co pozwala mi ocenić selektywność bez ujawniania wrażliwych stałych. Jeśli muszę udostępniać ślady, maskuję wartości parametrów lub zastępuję je reprezentatywnymi przedziałami, aby zachować zgodność z wymogami dotyczącymi ochrony danych.
Automatyzacja: rejestrowanie, rozróżnianie i dokumentowanie śladów
Aby zapewnić powtarzalność analiz, zapisuję wybrane ślady w tabeli diagnostycznej i opatrzam je metadanymi, takimi jak schemat, wersja, zmienne sesji oraz sygnatura czasowa. Dzięki temu mogę przed i po zmianach indeksów lub aktualizacjach wersji diffen, które decyzje zostały odłożone. W praktyce warto podzielić bloki rozważane_plany_wykonania oraz rows_estimation zapisać osobno, aby móc szybko porównać zmiany kosztów. Mniejsze zapytania pomocnicze wyodrębniają dla mnie wybraną kolejność połączeń oraz obliczone koszty – na przykład za pomocą JSON_EXTRACT(TRACE, '$.join_optimization.considered_execution_plans') – i zapisujemy wynik obok wyników poleceń EXPLAIN i ANALYZE. W ten sposób powstaje rzetelna dokumentacja każdego etapu optymalizacji.
Ograniczenia, specyfika poszczególnych wersji i porównanie z MySQL
Kluczowe struktury zapisu są oparte na MySQL, jednak szczegóły i nazwy pól mogą się nieznacznie różnić w zależności od wersji MariaDB. Dlatego skupiam się na semantyczne Sekcje (Rewrites, Rows-Estimation, rozpatrywane plany, Condition-Attachments), zamiast skupiać się na kosmetycznych różnicach. Ważne: w MariaDB uwaga skupia się na ostatniej instrukcji aktywnego połączenia. Kto analizuje wiele kolejnych instrukcji, powinien odczytywać je bezpośrednio po wykonaniu lub automatycznie za pomocą haka, aby nie nadpisać istotnych śladów. W przypadku bardzo dużych plików JSON uwzględniam zapotrzebowanie na pamięć i rozumiem, że MISSING_BYTES_BEYOND_MAX_MEM_SIZE jako zachętę do tymczasowego podwyższenia limitu i ponownego przeprowadzenia analizy.
Konkretne przykłady ekstrakcji danych z plików JSON w codziennej praktyce
Na koniec kilka zwięzłych fragmentów, z których często korzystam w praktyce, aby szybko przejść do sedna sprawy:
- Wybrana kolejność połączeń i listy kandydatów: Pobieram prefiksy planu oraz odpowiednie dołączone tabele, aby móc prześledzić sekwencję decyzji.
- Alternatywne zakresy i koszty: Wyodrębniam listę ocenionych indeksów dla najbardziej selektywnych tabel, aby precyzyjnie ocenić opłacalność przepisania kodu lub utworzenia nowych indeksów.
- Filtry dodane na wczesnym etapie: Czytam
dodawanie_warunków_do_tabel-sekcje, aby zapewnić, że silne predykaty znajdują się jak najbliżej źródła danych.
Dzięki niewielkiej liczbie widoków dla tych ekstrakcji dysponuję zwięzłym „przewodnikiem“ dotyczącym decyzji optymalizatora, który w razie potrzeby włączam podczas sesji diagnostycznych, a następnie ponownie wyłączam.
Częste przeszkody i rozwiązywanie problemów
Jeśli brakuje histogramów lub statystyki są nieaktualne, szacunki są błędne i powodują Plany z niepotrzebnymi skanami pełnymi. Jeśli w śladzie zauważę znaczne odchylenia w kardynalnościach, aktualizuję statystyki, tworzę odpowiednie indeksy lub modyfikuję filtry tak, by były możliwe do przechowywania w pamięci. Zbyt skąpe ślady rozpoznaję na podstawie MISSING_BYTES_BEYOND_MAX_MEM_SIZE i reaguję, tymczasowo podnosząc limit. Jeśli ANALYZE wykazuje lepsze czasy wykonania dla alternatywnej ścieżki, sprawdzam w śladzie, który czynnik kosztowy zadecydował o wyborze tej opcji. W ten sposób krok po kroku uzupełniam braki w wiedzy i osiągam Przejrzystość o logice podejmowania decyzji.
Krótkie podsumowanie
Ślad optymalizatora MariaDB wyjaśnia mi w dokumencie JSON, w jaki sposób silnik przekształca zapytania, szacuje liczbę wierszy, porównuje plany i ostatecznie wybiera Sekwencja wybiera. Aktywuję go na każdą sesję, odczytuję ślad, sprawdzam przygotowanie_połączenia oraz optymalizacja połączeń i łączę te wnioski z EXPLAIN/ANALYZE. Na podstawie przyczyn odrzucenia indeksów, opóźnionych filtrów lub błędnych oszacowań wyznaczam konkretne działania: lepsze indeksy, bardziej aktualne statystyki i jasne sformułowania zapytań. Korzystając z funkcji JSON, wyodrębniam fragmenty, rozpoznaję wzorce i dokumentuję decyzje w sposób umożliwiający ich odtworzenie. W ten sposób zapewniam niezawodną obsługę nawet rozległych obciążeń SQL. Wydajność i dbaj o to, by decyzje dotyczące tuningu były zrozumiałe.
