Przejdź do treści 
Histogramy MySQL dostarczają optymalizatorowi rzeczywistych danych dotyczących rozkładu, dzięki czemu może on prawidłowo oszacować selektywność i generować szybsze plany zapytań – często nawet bez dodatkowego indeksu. Pokażę, jak w MySQL 8+ konfiguruję i sprawdzam histogramy za pomocą polecenia ANALYZE TABLE oraz jak wykorzystuję je do podejmowania lepszych decyzji dotyczących połączeń, filtrowania i skanowania.
Punkty centralne
W skrócie: Poniższe punkty pokazują, na co zwracam szczególną uwagę podczas korzystania z histogramów.
- Selektywność zamiast intuicji: bardziej realistyczne szacunki kardynalności
- Bez indeksu szybciej: lepszy dobór planu w przypadku rozkładów asymetrycznych
- typy Zrozumieć: celowe wykorzystanie singletonów i equi-height
- Wiadra podatki: rozważyć likwidację w kontekście kosztów związanych z metadanymi
- Opieka W skrócie: aktualizować, sprawdzać, w razie potrzeby usuwać
Dlaczego histogramy bez indeksu są skuteczne
Używam Histogramy, ponieważ w przeciwnym razie optymalizator często zakłada równomierny rozkład i w związku z tym wybiera nieodpowiednie plany. Histogram przedstawia Rozkład wartości w przybliżeniu na podstawie jednej kolumny, dostarczając w ten sposób realistyczne oszacowania selektywności dla predykatów takich jak =, >, BETWEEN, IN lub IS NULL. Optymalizator decyduje następnie, czy korzystniejsze jest skanowanie zakresu indeksu, skanowanie tabeli czy strategia połączenia z zagnieżdżonymi pętlami. Jeśli na przykład warunek dotyczy tylko 0,1 % wierszy, preferuję ukierunkowany dostęp zamiast szerokiego skanowania. Jeśli natomiast filtr obejmuje prawie wszystkie wiersze, rezygnuję z kosztownych operacji na indeksach, które nie przynoszą korzyści, i w ten sposób zwiększam Wydajność każdego planu.
Rodzaje histogramów w MySQL 8.0
Rozróżniam dwa typy: Singleton i Equi-Height. Histogramy typu Singleton grupują często występujące pojedyncze wartości w oddzielnych przedziałach – idealne rozwiązanie dla kolumn z niewielką liczbą dominujących kategorii, takich jak „aktywny“, „nieaktywny“ lub „zarchiwizowany“. Histogramy typu „Equi-Height” dzielą zakres wartości w taki sposób, że każdy przedział zawiera podobną liczbę Linie ; nadaje się to do rozkładów ciągłych lub nieregularnych, takich jak ceny, sygnatury czasowe czy „niekompletne“ zakresy identyfikatorów. Oba warianty dostarczają optymalizatorowi dokładniejsze wskaźniki trafności dla filtrów. Typ wybieram zawsze na podstawie właściwości danych, a nie osobistych preferencji.
Podstawy techniczne: sterowanie wyborem typu danych w MySQL
MySQL określa konkretną Wariant histogramu automatycznie na podstawie rozkładu danych. W praktyce oznacza to, że jeśli liczba różnych wartości (NDV) jest wystarczająco mała w stosunku do liczby przedziałów, powstaje w efekcie histogram typu singleton; w przeciwnym razie generowany jest histogram typu equi-height. Dlatego „wybieram“ ten typ pośredni, wybierając odpowiednią kolumnę i odpowiednią liczbę przedziałów. W przypadku kolumn zawierających bardzo niewielką liczbę, ale silnie dominujących kategorii, celowo ustalam niewielką liczbę przedziałów, aby uzyskać precyzję zbliżoną do singletonów dla tych wartości. W przypadku danych o drobnym rozrzucie i charakterze ciągłym stopniowo zwiększam liczbę przedziałów, aż EXPLAIN wskaże pożądaną Selektywność odzwierciedla.
Ważne: Histogramy to jednosłupcowy. Nie można bezpośrednio odzwierciedlić zależności między kolumnami (np. status i country). W takich przypadkach pomocne jest utworzenie histogramu dla kolumny o największej selektywności oraz odpowiednie dostosowanie kolejności połączeń.
Jak właściwie dobrać wiadra
MySQL domyślnie wykorzystuje 100 Wiadra, ale za pomocą opcji WITH N BUCKETS można ustawić wartość od 1 do 1024. Większa liczba przedziałów zwiększa rozdzielczość, jednak powoduje to wzrost ilości metadanych i nakładu pracy związanego z analizą. Zazwyczaj zaczynam od ostrożnego ustawienia, sprawdzam wpływ na wynik EXPLAIN i stopniowo zwiększam liczbę przedziałów, jeśli plan nadal wydaje się nieodpowiedni. W przypadku silnie skoncentrowanych wartości (np. 90 przypadków % w jednym statusie) często wystarcza kilka segmentów; w przypadku drobno rozrzuconych cen lub znaczników czasu warto zastosować więcej segmentów. Celem jest sensowna Ziarnistość, co w znacznym stopniu ograniczyło błędne oceny, nie powodując przy tym niepotrzebnego wzrostu obciążenia administracyjnego.
Praktyka: Przebieg pracy z funkcją ANALYZE TABLE
Postępuję zgodnie z jasnymi Przepływ pracy: Najpierw identyfikuję kolumny, które często pojawiają się w warunkach WHERE lub JOIN i wykazują wyraźnie asymetryczny rozkład. Następnie generuję histogram za pomocą polecenia ANALYZE TABLE tbl UPDATE HISTOGRAM ON col WITH N BUCKETS; i sprawdzam go za pomocą INFORMATION_SCHEMA.COLUMN_STATISTICS. Po przeniesieniu danych ponownie aktualizuję za pomocą polecenia ANALYZE TABLE. Jeśli statystyka jest nieodpowiednia, usuwam ją za pomocą polecenia ANALYZE TABLE tbl DROP HISTOGRAM ON col;. Aby ocenić wpływ na plan, sprawdzam Interpretacja poleceń EXPLAIN i ANALYZE oraz te same szacunki w porównaniu z rzeczywistymi Linie od.
Konkretne polecenia i kontrola
Pracuję w sposób powtarzalny, wykonując kilka jasnych kroków, a następnie sprawdzam wygenerowane statystyki w formacie JSON.
-- Tworzenie histogramów dla poszczególnych kolumn
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 32 BUCKETS;
ANALYZE TABLE orders UPDATE HISTOGRAM ON created_at WITH 128 BUCKETS;
-- Kilka kolumn w jednym przebiegu z tą samą liczbą przedziałów
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, payment_method WITH 64 BUCKETS;
-- Selektywne usuwanie histogramów
ANALYZE TABLE orders DROP HISTOGRAM ON status;
-- Kontrola wizualna statystyk
SELECT
SCHEMA_NAME, TABLE_NAME, COLUMN_NAME,
JSON_PRETTY(HISTOGRAM) AS histogram
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE SCHEMA_NAME = DATABASE()
AND TABLE_NAME = 'orders'
AND COLUMN_NAME IN ('status','created_at');
Oceniam wpływ bezpośrednio za pomocą polecenia EXPLAIN ANALYZE:
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE status = 'canceled'
AND created_at >= NOW() - INTERVAL 7 DAY;
Czy oszacowanie ulega poprawie? wiersze Jeśli różnica jest zauważalna i plan zmienia się np. z pełnego skanowania na skanowanie zakresu indeksu lub zmienia się kolejność połączeń, oznacza to, że działanie zakończyło się sukcesem. Jeśli odchylenie pozostaje duże, zwiększam lub zmniejszam liczbę segmentów i ponownie porównuję wyniki.
Przykład: Status zamówienia i rzadkie wartości
W tabeli zamówień często dominuje status „zrealizowane“, podczas gdy status „oczekujące“ występuje dość często, a „anulowane“ – bardzo rzadko; to niewyważenie bez histogramu łatwo prowadzi do błędnych wartości selektywności. Jeśli interfejs API wysyła zapytanie o „canceled“, optymalizator może błędnie wybrać pełne skanowanie tabeli, mimo że wystarczyłby dostęp do wąskiego indeksu. Dzięki histogramowi singletonowemu MySQL rozpoznaje, że „canceled“ stanowi jedynie niewielką część danych, i przechodzi na skanowanie zakresu indeksu lub optymalizuje kolejność połączeń. W ten sposób zmniejsza się opóźnienie, a ja nie potrzebuję dodatkowego indeksu dla każdego Wariant filtra. W pulpitach nawigacyjnych z rygorystycznymi wskaźnikami SLO ta korekta często przynosi zauważalne korzyści w zakresie szybkości reakcji.
Szeregi czasowe i znaczniki czasu
W przypadku szeregów czasowych występuje wiele Dostępy na podstawie aktualnych danych; starsze przedziały czasowe pozostają zazwyczaj nieaktywne. Histogram Equi-Height dla pól `created_at` lub `updated_at` pozwala odróżnić intensywnie wykorzystywane przedziały czasowe od rzadko używanych. Optymalizator prawidłowo ocenia wówczas, czy skanowanie zakresu ma sens, czy też skanowanie tabeli szybciej doprowadzi do celu. Szczególnie w przypadku częściowych filtrów czasowych w dużych tabelach dostrzegam wyraźne zmiany planu i niższe koszty operacji wejścia/wyjścia. Uważam, że Statystyki tutaj częściej aktualizowane, ponieważ główny punkt ciężkości przesuwa się wraz z bieżącą działalnością.
Partycje, typy danych i kolacje
Analizuję rozkład danych w tabelach podzielonych na partycje we wszystkich partycjach. Duże różnice (np. w ujęciu miesięcznym) mogą spowodować wygładzenie histogramów globalnych. Jeśli poszczególne partycje są wyjątkowo selektywne lub wyjątkowo szerokie, dodatkowo sprawdzam za pomocą filtrów przycinania partycji w klauzuli WHERE, czy jakość planu jest mimo to odpowiednia. Ogólnie staram się formułować filtry w taki sposób, aby MySQL mógł wcześnie wykluczyć Puszka.
Histogramy najlepiej sprawdzają się w przypadku typów danych skalarnych, które można porównać (liczby, wartości daty i czasu, VARCHAR/CHAR z odpowiednią kolacją). W przypadku Dane LOB/JSON stawiam raczej na Wygenerowane kolumny z wyodrębnionymi, typizowanymi wartościami i w razie potrzeby uzupełnij je histogramami lub wskaźnikami. W przypadku ciągów znaków określa Kollacja logika porównywania; w zależności od kolacji wartości mogą się pokrywać (np. wielkość liter). Utrzymuję spójność kolacji z zapytaniami, aby uzyskać realistyczne wyniki selekcji.
Granice i błędy
Histogramy służą przede wszystkim do szacowania wartości w poszczególnych kolumnach za pomocą Stałe Dobrze; jednak w ograniczonym stopniu odzwierciedlają zależności między wieloma kolumnami. W przypadku silnie skorelowanych kolumn lub parametrów dynamicznych (np. wypełnianych po stronie aplikacji) napotykają na ograniczenia. Pola boolowskie lub kolumny o niemal równomiernym rozkładzie rzadko czerpią korzyści z dodatkowych statystyk. Z kolei zbyt duża liczba przedziałów i nadmierna konserwacja mogą wydłużyć czas poświęcany na zarządzanie i analizę. Dlatego stosuję histogramy w sposób celowy i regularnie sprawdzam Efekt na rzeczywiste realizacje.
Kontrola i aktualizacja Optimizera
Sprawdzam Użycie od histogramów, poprzez ANALYZE TABLE, po odpowiednie opcje optymalizatora, tak aby planer mógł sensownie wykorzystać statystyki. W systemach o dużym obciążeniu planuję aktualizację w spokojnych przedziałach czasowych lub w trybie wsadowym po większych operacjach ładowania danych. Przed i po aktualizacji porównuję wyniki poleceń EXPLAIN i EXPLAIN ANALYZE, aby ocenić zmienioną kolejność połączeń, etapy filtrowania i modele kosztów. W przypadku negatywnych skutków reaguję natychmiast i cofam statystykę. W celu dalszego sterowania Opcje optymalizatora dbam o to, by zależności z innymi statystykami nie powodowały niezauważalnych błędów Założenia wytwarzać.
Monitorowanie, ochrona przed regresją i podręcznik postępowania
Buduję sobie lekki Podręcznik taktyczny w trybie produkcyjnym:
- Ustalanie wartości bazowej: przed wprowadzeniem zmian należy wykonać polecenia EXPLAIN ANALYZE, zmierzyć czas wykonania, liczbę „rows examined“ oraz liczbę handlerów.
- Tworzenie/modyfikacja histogramu: ukierunkowane na kolumny filtrów, konserwatywne przedziały.
- Zmierzyć zaraz po tym: plan, szacowane vs. rzeczywiste wiersze; odchylenie o współczynniku >10 jest dla mnie sygnałem ostrzegawczym.
- Precyzyjna regulacja: przesuń segmenty w górę/w dół; w razie potrzeby dostosuj kolejność filtrów w zapytaniu.
- Przygotować cofnięcie: DROP HISTOGRAM, jeśli wzrosną opóźnienia.
- Automatyzacja: po załadunkach ETL lub większych falach DML – ANALYZE w oknach serwisowych.
Do analizy przyczyn wykorzystuję Ślady optymalizatora oraz EXPLAIN ANALYZE, aby sprawdzić, czy planer na podstawie histogramów wybiera właściwą tabelę selekcyjną jako pierwszą. W przypadku testów A/B ustalam na próbę kolejność połączeń (STRAIGHT_JOIN) lub wymuszam/blokuję poszczególne indeksy, aby w izolacji ocenić wpływ statystyki.
Z organizacyjnego punktu widzenia sprawdzają się krótkie Dziennik zmian W każdej tabeli: kolumna, liczba przedziałów, czas, wartości pomiarowe przed i po. Ułatwia to późniejsze korekty i zapobiega niejasnym interakcjom.
Aspekty operacyjne: blokady, koszty, przenoszenie numerów
Funkcja ANALYZE TABLE pobiera Blokada metadanych w tabeli, ale nie blokuje na stałe standardowych operacji odczytu/zapisu. W przypadku bardzo dużych tabel przewiduję wystarczającą ilość czasu; generowanie histogramu opiera się na próbkach i jest ograniczone pod względem pamięci (słowo kluczowe: wewnętrzna pamięć robocza do obliczeń). Zajmowana przestrzeń przez same statystyki pozostaje umiarkowana: realistyczną wartością orientacyjną jest od kilkudziesięciu do kilkuset kilobajtów na kolumnę zawierającą 100–256 przedziałów. Niemniej jednak obliczam sumę, ponieważ wiele kolumn pomnożone przez wiele tabel daje widoczne metadane.
Na stronie Zrzuty logiczne (mysqldump) histogramy nie są przenoszone wraz z danymi; po przywróceniu danych celowo tworzę je od nowa. W przypadku aktualizacji na miejscu (in-place upgrade) są one zachowywane. Jeśli chodzi o uprawnienia, potrzebuję wystarczających uprawnień do wykonania polecenia ANALYZE TABLE na odpowiednich obiektach; w ściśle regulowanych środowiskach włączam tę czynność do procesów konserwacyjnych.
Kiedy histogramy nie są przydatne
Oszczędzam sobie Histogramy w przypadku kolumn, które zawierają bardzo niewiele wartości i które i tak można dobrze oszacować. Nawet tam, gdzie dobry indeks obejmuje już minimalne zbiory wyników, histogram rzadko przynosi dodatkowe korzyści. Rozkłady równomierne nie wymagają skomplikowanej szczegółowości. W systemach o dużej dynamice i intensywnym zapisie konserwacja może generować niepotrzebne obciążenie, jeśli uruchamiam ją zbyt często. W takich sytuacjach stosuję Energia raczej w strategiach indeksowania, projektowaniu zapytań i buforowaniu.
Ściągawka w formie tabeli
Korzystam z poniższego Przegląd do szybkiego podejmowania decyzji: jaki typ histogramu jest odpowiedni, jak ustawić przedziały oraz jakie koszty się z tym wiążą. Tabela ta służy jako pomoc pamięciowa podczas analizy zapytań problematycznych. Aktualizuję ją w oparciu o wnioski wyciągnięte z EXPLAIN ANALYZE oraz wskaźników produkcyjnych. Biorę przy tym pod uwagę, że rozkłady danych ulegają zmianom, a historyczne założenia tracą na aktualności. Kluczowe znaczenie ma Jakość planu potwierdzić to za pomocą rzeczywistych pomiarów.
| Aspekt | Zalecenie | Korzyści | kompromis | Przykład |
|---|---|---|---|---|
| Typ | Singleton w przypadku niewielkiej liczby dominujących wartości | Dokładne wskaźniki trafności dla popularnych kategorii | Niezbyt pomocne w przypadku obszarów ciągłych | status_zamówienia |
| Typ | Equi-Height w przypadku danych zniekształconych i ciągłych | Lepsze oszacowanie w całym zakresie wartości | Więcej metadanych w przypadku dużej liczby segmentów | created_at, cena |
| Wiadra | Zacznij od 100, a następnie dostosuj | Zrównoważona rozdzielczość | Większe obciążenie związane z analizą i przechowywaniem danych w zakresie 512–1024 | Z 100 WIADERKAMI |
| Opieka | Po wprowadzeniu większych zmian w danych ANALYZE | Aktualne selektywności | Zaplanowanie okna serwisowego | ANALYZE TABLE … UPDATE HISTOGRAM |
| Kontrola | Sprawdź za pomocą COLUMN_STATISTICS | Przejrzystość i audyt | Wymagana interpretacja JSON | INFORMATION_SCHEMA.COLUMN_STATISTICS |
Wpisanie się w ogólny obraz tuningu
Traktuję Histogramy jako element składowy obok indeksów, projektowania zapytań, buforowania i parametrów sprzętowych. Często dobrze skonstruowany histogram zmienia kolejność połączeń, zmniejsza obciążenie we/wy i zapewnia stałe czasy odpowiedzi. Niemniej jednak nie zastępuje on przemyślanych strategii indeksowania ani wydajnego schematu. Kto przyjrzy się dokładniej decyzjom dotyczącym planowania, skorzysta na Zrozumieć plany realizacji i porównuje modele kosztowe z rzeczywistymi czasami realizacji. Regularnie sprawdzam, czy Obciążenia czy nadal pasują do statystyk, czy też konieczne są pewne korekty.
Zaawansowane scenariusze połączeń
Histogramy są szczególnie przydatne, gdy mamy do czynienia z wieloma tabelami z filtrami. Przykład:
SELECT o.id, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'DE'
AND o.status = 'canceled'
AND o.created_at >= NOW() - INTERVAL 30 DAY;
Bez histogramów optymalizator może w niektórych przypadkach zaniżać selektywność warunku o.status=’canceled‘ lub zawyżać odsetek niemieckich użytkowników. Przy użyciu histogramu na i kraj oraz o.status (w razie potrzeby również na o.created_at) projektant zazwyczaj zdaje sobie sprawę, że ta kombinacja jest wyjątkowo selektywna. W praktyce widzę wtedy, że MySQL najpierw określa mniejszy podzbiór (np. poprzez indeks na users(country) lub orders(status, created_at)), a dopiero potem wykonuje połączenie – zamiast skanować dużą tabelę. Oszczędza to operacje wejścia/wyjścia, bufory i obciążenie procesora, a także stabilizuje opóźnienia nawet pod obciążeniem.
Ponieważ histogramy tylko jednosłupcowy Strategie indeksowe nadal odgrywają ważną rolę: indeks złożony na (status, created_at) może jeszcze bardziej przyspieszyć operację Range-Scan. Histogram zapewnia tutaj przede wszystkim, że optymalizator ten Strategia uznaje za korzystną.
Podsumowanie dla praktyki
Ustawiłem MySQL-Wykorzystuję histogramy, gdy optymalizator popełnia błędy przy użyciu standardowych statystyk, a asymetryczne rozkłady powodują generowanie błędnych planów. Za pomocą polecenia ANALYZE TABLE tworzę, aktualizuję i usuwam statystyki w sposób ukierunkowany dla kolumn, które dominują w filtrach i połączeniach. Wybór między opcją „Singleton” a „Equi-Height” dokonuję na podstawie danych, a liczbę segmentów dostosowuję na podstawie pomiarów. Za pomocą polecenia EXPLAIN ANALYZE sprawdzam, czy kolejność połączeń, pozycje filtrów i skanowania zmieniają się zgodnie z oczekiwaniami. W ten sposób osiągam przy niewielkim Nad głową zauważalnie szybsze zapytania – często bez konieczności tworzenia dodatkowych indeksów.
