Dlaczego wydajność w e‑commerce to priorytet

Sklepy z dużą liczbą produktów potrzebują przewidywalnie szybkich odpowiedzi bazy, inaczej każdy filtr, sortowanie czy paginacja mogą zabić konwersję.

slow queries MySQL w sklepach z dużą ilością produktów potrafią wybić z rytmu nawet najlepiej zaprojektowany frontend. Z perspektywy użytkownika liczą się milisekundy – strona kategorii, która zamiast 300 ms ładuje się 3 sekundy, powoduje porzucenia. Z perspektywy biznesu to realne straty. Dobra wiadomość: większość powolnych zapytań daje się skutecznie zdiagnozować i przyspieszyć przy pomocy właściwych indeksów oraz kilku metodycznych kroków analizy.

W praktyce najczęściej winne są filtry (cena, dostępność, cechy), sortowania (po popularności, cenie, dacie), złożone łączenia wielu tabel (kategorie, stany magazynowe, ceny promocyjne) i paginacja z wysokimi OFFSET-ami. W tym artykule przeprowadzę Cię przez proces: od włączenia logów, przez czytanie planów wykonania, po zaprojektowanie indeksów, które realnie skracają czas odpowiedzi.

Jak włączyć log i znaleźć slow queries MySQL

Zacznij od dowodów: rejestruj wolne zapytania, a nie zgaduj.

Żeby pracować na faktach, włącz log powolnych zapytań. W MySQL 5.7/8.0 zrobisz to dynamicznie:

• SET GLOBAL slow_query_log = ON;
• SET GLOBAL long_query_time = 0.2; (na start 200 ms; dopasuj do swojego SLA)
• SET GLOBAL log_output = ‘FILE’;
• SET GLOBAL log_queries_not_using_indexes = ON; (na krótko, do diagnostyki)

Upewnij się, gdzie zapisuje się plik slow log (zmienna slow_query_log_file). Na środowiskach produkcyjnych pilnuj rotacji logów i nie zostawiaj log_queries_not_using_indexes włączonego na stałe.

Kiedy zbierzesz godzinę–dwie ruchu, przesuń się od surowych wierszy do agregatów. Ręczne czytanie logów mija się z celem, bo interesują Cię wzorce, a nie pojedyncze przypadki.

Agregacja logów: znajdź zapytania, które bolą najbardziej

Skup się na „ciężkich hitterach”: wysokie łączne czasy i częstotliwość.

Dwa narzędzia, które po prostu działają:

• mysqldumpslow slow.log – szybki przegląd, które zapytania najczęściej są wolne.
• pt-query-digest slow.log – szczegółowy raport: sumaryczny czas, 95. percentyl, przykładowe bindy parametrów, momenty szczytu.

Kluczowe metryki, na które warto patrzeć:

• total time (całkowity czas dla danego wzorca SQL),
• count (ile razy wystąpiło),
• avg i p95 latency (średnia i 95 percentyl),
• rows examined vs rows sent (ile wierszy MySQL musiał „przeskanować” vs ile zwrócił).

To pokaże, które zapytania priorytetowo zoptymalizować. Często jedna–dwie konstrukcje SQL odpowiadają za 60–80% opóźnień.

EXPLAIN i EXPLAIN ANALYZE — jak czytać plany wykonania

Dobry indeks to nie magia, tylko konsekwencja zrozumienia planu.

Zanim dodasz jakikolwiek indeks, uruchom:

• EXPLAIN SELECT …;
• EXPLAIN ANALYZE SELECT …; (w MySQL 8.0 — podaje realne czasy etapów wykonania)

Na co patrzeć:

• type: ALL (pełen skan) jest złe w listingu; chcemy range, ref, eq_ref.
• key i key_len: który indeks używa optymalizator i do jakiej głębokości kolumn.
• rows i filtered: szacunek liczby badanych wierszy; gigantyczne liczby to wskazówka, że brakuje selektywnego indeksu.
• Extra: Using temporary, Using filesort — przy ORDER BY/GROUP BY to znak, że warto zbudować indeks wspierający sortowanie/grupowanie.
• W formacie TREE (EXPLAIN FORMAT=tree) zobaczysz przepływ planu; to bardzo pomaga przy wielotabelowych JOIN-ach.

Jeśli optymalizator uparcie nie wybiera właściwego indeksu, upewnij się, że statystyki są aktualne (ANALYZE TABLE), rozważ histogramy (MySQL 8.0) i sprawdź selektywność kolumn.

Dobre indeksy dla listingu i filtrów produktów

Indeks musi odzwierciedlać sposób, w jaki użytkownik filtruje i sortuje.

Typowe zapytanie listingu w e‑commerce:

SELECT p.id, p.name, p.price
FROM products p
JOIN product_category pc ON pc.product_id = p.id
WHERE pc.category_id = ?
AND p.status = ‘active’
AND p.stock > 0
AND p.price BETWEEN ? AND ?
ORDER BY p.price ASC
LIMIT 24 OFFSET 0;

Co tu pomoże:

• Na product_category: indeks z kategorią na początku, bo po niej filtrujesz.
  CREATE INDEX idx_pc_category_product ON product_category(category_id, product_id);

• Na products: indeks z równoważnymi filtrami na początku, potem kolumna zakresowa, a na końcu sortowanie.
  CREATE INDEX idx_products_status_stock_price ON products(status, stock, price);

Jeśli często sortujesz po price ASC i filtrujesz po status/stock, ten indeks pozwala uniknąć Using filesort. Gdy sortujesz po created_at lub popularity, rozważ osobny indeks:
CREATE INDEX idx_products_status_stock_created ON products(status, stock, created_at);

Pamiętaj: jeden „uniwersalny” indeks rzadko istnieje. Lepiej mieć 2–3 precyzyjnie dobrane, niż jeden ogromny, który wszystko robi przeciętnie.

Dodatkowo, jeśli listing pobiera tylko id, name, price, możesz zbudować indeks pokrywający (covering), który zawiera kolumny z SELECT:
CREATE INDEX idx_products_cover ON products(status, stock, price, id, name);

Wtedy MySQL może odpowiedzieć z samego indeksu, bez dotykania danych (InnoDB), co bywa ogromnym przyspieszeniem. Zwróć uwagę na rozmiar indeksu—nie przesadzaj z kolumnami tekstowymi; dla VARCHAR rozważ prefiksy (np. name(30)), jeśli naprawdę musisz.

Kolejność kolumn w indeksie ma znaczenie

Najpierw równości, potem zakres, na koniec sortowanie.

Złota zasada budowy indeksów złożonych:

• Kolumny używane w warunkach równości (=, IN) na początku.
• Potem kolumny zakresowe (>, <, BETWEEN).
• Na końcu kolumna, po której sortujesz (ORDER BY), jeśli kierunek sortowania jest zgodny.

Przykład: WHERE store_id = ? AND category_id = ? AND price BETWEEN ? AND ? ORDER BY price
Proponowany indeks: (store_id, category_id, price)

Łamanie tej zasady zwykle skutkuje Using filesort i wyższym kosztem.

Indeksy w tabelach relacyjnych (kategorie, atrybuty)

Tabele łącznikowe to rdzeń filtrowania — daj im właściwe klucze.

• product_category: (category_id, product_id) oraz często również (product_id, category_id), jeśli często szukasz kategorii po produkcie (np. do reindeksacji).
• product_attribute (EAV): gdy filtrujesz po atrybucie, potrzebujesz indeksu zaczynającego się od attribute_id, dalej value i na końcu product_id:
  CREATE INDEX idx_attr_filter ON product_attribute(attribute_id, value, product_id);

Dzięki temu JOIN do produktów po product_id będzie tani, a filtrowanie po value selektywne. Jeśli wartości są długie (VARCHAR), rozważ indeksy prefiksowe (value(20)) — testuj selektywność, by nie przesadzić.

Tekstowe wyszukiwanie a indeksy

LIKE ‘%fraza%’ nie jest skalowalne — używaj FULLTEXT lub silnika wyszukiwarki.

• Dla wyszukiwania po nazwie/opisie w MySQL 8.0 włącz FULLTEXT (InnoDB):
  ALTER TABLE products ADD FULLTEXT ft_name_desc (name, description);
  SELECT id FROM products WHERE MATCH(name, description) AGAINST (‘rower górski’ IN NATURAL LANGUAGE MODE);

• Unikaj LIKE z wiodącym wildcardem (np. ‘%górski’), bo zabija indeksy B‑Tree. Jeśli musisz, rozważ dodatkowy system (Elasticsearch, OpenSearch), zwłaszcza przy skomplikowanych rankingach.

JSON, EAV i złożone atrybuty — jak je indeksować

Indeksuj to, po czym filtrujesz, nawet jeśli dane siedzą w JSON.

MySQL 8.0 pozwala indeksować wyrażenia i kolumny generowane. Przykład dla JSON:

ALTER TABLE products
ADD COLUMN color_v GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, ‘$.color’))) STORED,
ADD INDEX idx_color (color_v);

Podobnie z liczbami:
ALTER TABLE products
ADD COLUMN wheel_v INT GENERATED ALWAYS AS (JSON_EXTRACT(attributes, ‘$.wheel’)) STORED,
ADD INDEX idx_wheel (wheel_v);

To często porządkuje chaos EAV bez migracji całego modelu. Kluczowe jest precyzyjne zidentyfikowanie najpopularniejszych filtrów i zapewnienie im indeksów.

slow queries MySQL: kiedy indeks to za mało

Czasem problem leży w samej konstrukcji zapytania albo strategii.

• Zbyt duże OFFSET-y (np. OFFSET 5000) są drogie. Zamiast tego użyj paginacji kluczowej (keyset pagination):
  SELECT … WHERE (price, id) > (?, ?) ORDER BY price, id LIMIT 24;

• Zmniejsz zakres pracy: nie łącz od razu 10 tabel; najpierw wyznacz zbiór id produktów (subselect z indeksu), potem dołącz resztę.

• Uprość warunki OR — często lepiej rozbić na UNION ALL dwóch dobrze „sargowalnych” zapytań z indeksami.

• Dane pochodne (popularność, dostępność) aktualizuj asynchronicznie i trzymaj w zdenormalizowanych kolumnach, jeśli to najczęstsze kryteria sortowania/filtru. To nie jest grzech, to pragmatyzm.

Dodawanie indeksów bez przestojów

Zaplanuj zmiany tak, by nie wstrzymać sprzedaży.

MySQL 8.0 wspiera bezbolesne operacje:

• ALTER TABLE … ADD INDEX … ALGORITHM=INPLACE, LOCK=NONE;
• Często CREATE INDEX na InnoDB jest ekwiwalentem powyższego i wykona się online.

Jeśli tabela jest ogromna, rozważ:

• pt-online-schema-change (Percona Toolkit) — buduje cień tabeli, przenosi dane i przełącza bez przestoju.
• Indeksy niewidoczne (invisible indexes) do testów planu:
  ALTER TABLE products ALTER INDEX idx_test INVISIBLE;
  EXPLAIN zapytanie;
  ALTER TABLE … VISIBLE;

Przed wdrożeniem na produkcję przetestuj na kopii o podobnej skali. Monitoruj IO i wpływ na replikację.

Statystyki, histogramy i „uczenie” optymalizatora

Czasem masz indeks, ale plan i tak jest zły — pomóż optymalizatorowi.

• ANALYZE TABLE products; — odświeża statystyki.
• Histogramy (MySQL 8.0) na nietypowych rozkładach:
  ANALYZE TABLE products UPDATE HISTOGRAM ON price WITH 100 BUCKETS;
• Utrzymuj STATISTICS_PERSISTENT włączone, aby statystyki nie znikały po restarcie.
• Unikaj mieszania kolacji/porównań, które utrudniają użycie indeksów (np. porównania o różnych collation).

Najczęstsze błędy, które spowalniają

Niektóre grzechy powtarzają się wszędzie — wyłap je zawczasu.

• Brak indeksu na obcych kluczach (JOIN po product_id, category_id, brand_id bez indeksu).
• Indeksy, które zaczynają się od kolumny mało selektywnej (np. status), a potem dopiero kategoria/filtr.
• Użycie funkcji po lewej stronie warunku (np. DATE(created_at) = ?) — zrób warunek sargowalny: created_at >= ? AND created_at < ?.
• LIKE ‘%fraza%’ na dużych tabelach — bez FULLTEXT to dławi serwer.
• Za dużo indeksów „na wszelki wypadek” — każdy insert/update je aktualizuje. Usuwaj duplikujące się i nieużywane (sys.schema_unused_indexes pomaga znaleźć).
• Zbyt niski long_query_time — zalewasz się logami, tracisz obraz. Dobierz próg do SLA i ruchu.

Jak mierzyć efekt: przed i po

Bez pomiaru to tylko wrażenia.

• Zanim wdrożysz indeks, zapisz metryki: p50/p95 czasu odpowiedzi dla endpointów listingu i wyszukiwarki, obciążenie CPU/IO, Rows_examined.
• Po wdrożeniu porównaj w tych samych oknach obciążenia (np. 24 h, ten sam dzień tygodnia).
• performance_schema i sys schema mają gotowe widoki (np. sys.statements_with_full_table_scans, sys.user_summary_by_statement_type).
• Patrz nie tylko na średnią, ale na ogon rozkładu (p95/p99). To ogon zabija UX w szczycie.

Szybka checklista do wdrożenia

Krok po kroku, bez magicznych skrótów.

• Włącz slow log, zbierz próbkę ruchu, przeanalizuj pt-query-digest.
• Dla top 3 zapytań uruchom EXPLAIN i EXPLAIN ANALYZE.
• Zaprojektuj indeksy: równość → zakres → sortowanie; osobno dla różnych scenariuszy ORDER BY.
• Dodaj indeksy online; na bardzo dużych tabelach rozważ pt-online-schema-change.
• Sprawdź plany po dodaniu indeksu; usuń duplikaty/zbędne indeksy.
• Uporządkuj filtry: unikaj funkcji po stronie kolumn, OR rozbijaj na UNION, wprowadź keyset pagination.
• Dla fulltext — użyj FULLTEXT albo dedykowanego wyszukiwania.
• Odśwież statystyki, rozważ histogramy dla kolumn o nietypowych rozkładach.
• Monitoruj p95 i Rows_examined przed/po; iteruj.

Na koniec najważniejsze: indeksy nie są celem samym w sobie. Celem jest szybsza odpowiedź dla użytkownika. Jeśli dodanie dwóch dobrze przemyślanych indeksów skróciło czas generowania listingu z 2,3 s do 180 ms — to jest sukces. Jeśli zmiana SQL i paginacji kluczowej „zjadła” 90% opóźnienia bez jednego ALTER TABLE — też. Analizuj, mierz, działaj małymi krokami. To najlepsza droga, by „slow queries MySQL” stały się u Ciebie rzadkim wyjątkiem, a nie codziennością.