Home Blog technologiczny Jak dobrze znasz indeksy w MySQL?

Jak dobrze znasz indeksy w MySQL?

Małgorzata Luchter

10.12.2025

Wprowadzenie

W tym artykule przyjrzę się tematowi indeksów w MySQL. W tym celu posłużę się pozornie prostymi przykładami zapytań SQL wykonanymi na tabeli z określonymi indeksami. Artykuł ten jest szczególnie dedykowany osobom, które posiadają podstawową wiedzę o indeksach i chcą ją zweryfikować lub uporządkować. Niemniej na początku artykułu znajduje się krótkie wprowadzenie do kluczowych zagadnień i terminologii, ułatwiające zrozumienie przykładów zamieszcoznych w jego dalszej części.

Indeks, Explain oraz Explain Analyze

Indeksy to struktury mające na celu przyspieszanie znajdywania wierszy zawierających określone dane. Najcześciej używane typy indeksów czyli klucz główny (primary key), unikalny (unique), a także zwykły indeks pojedynczy bądź wielopoziomowy są przechowywane w strukturze zwanej B-drzewo (B-tree)[1]. B-tree to samo-balansująca się struktura drzewiasta, przechowująca dane w posortowanej kolejności umożliwiająca efektywne wyszukiwanie, wstawianie i usuwanie danych — wszystko to w czasie logarytmicznym. Klucz główny najczęściej jest jednocześnie tzw. indeksem klastrowanym (clustered index). Oznacza to, że dane tabeli są fizycznie przechowywane w strukturze B-Drzewa razem z kluczem głównym. W przypadku pozostałych indeksów — indeksów wtórnych (secondary indexes) — węzły drzewa zawierają wartość indeksu oraz wskaźnik do oryginalnej tabeli. Możliwe jest tworzenie indeksów wielokolumnowych (wielopoziomowych), w których wartość w węźle B-drzewa jest uporządkowana leksykograficznie, zgodnie z kolejnością kolumn zdefiniowanych w indeksie[2].

Indeksy najlepiej spełniają swoje zadanie w przypadku kolumn zawierających dane o wysokim poziomie unikalności oraz przy korzystaniu z zapytań o dużej selektywności. Warto jednak pamiętać, że wprowadzenie indeksów wiąże się z dodatkowym obciążeniem podczas operacji zapisu i modyfikacji, co sprawia, że indeksowanie większości kolumn nie jest optymalnym rozwiązaniem. Dodatkowo, w sytuacjach, gdy zapytanie zwraca większość wierszy, bardziej efektywne okazuje się sekwencyjne odczytywanie danych bezpośrednio z tabeli[3]. Istnieją dwa przydatne polecenia SQL, które służą do uzyskiwania informacji o tak zwanym planie wykonania zapytania (execution plan) wybranym przez optymalizator bazy danych. Nazywają się one: „EXPLAIN” oraz „EXPLAIN ANALYZE”. Główna różnica między nimi polega na tym, że to drugie polecenie faktycznie wykonuje zapytanie, dlatego zawiera również rzeczywisty czas wykonania oraz liczbę przeszukiwanych wierszy. Zachęcam do zapoznania się z załączonymi linkami na końcu tego artykułu [4,5,6].

Komenda EXPLAIN zwraca miedzy innymi: type: określa sposób dostępu do danych podczas wykonywania zapytania, np:

const: korzysta z klucza głównego bądz indeksu unikalnego korzystając z operatora „=”.
Ref: dostęp z wykorzystaniem innego typu indeksu i operatora „=”.
Range: dostep z wykorzystaniem indeksu i operatorów oznaczających pewien zakres danych np.: „>”,”<„, „IN”, „BETWEEN”, „LIKE”.
Index: w wyniku zapytania przeskanowano cała strukturę b-drzewa danego indksu.
All: dane uzyskano skanując bezpośrednio całą tabelę.

possible keys oraz key oznacza rozważane klucze (indeksy) oraz wybrany klucz rows estymowana liczba wierszy do przeskanowania

EXPLAIN ANALYZE pokazuje na jakie etapy zostało rozłożone wykonywanie zapytania. Wynik ma postać drzewa, gdzie pierwszy poziom zawiera całkowity czas wykonania. Przykładowy wynik i wyjaśnienie poniżej:

-> Filter: (student.id < 4)  (cost=0.861 rows=3) (actual time=0.0165..0.0182 rows=3 loops=1)
    -> Covering index range scan on student using PRIMARY over (id < 4)  (cost=0.861 rows=3) (actual time=0.0153..0.0165 rows=3 loops=1)

(cost=0.861 rows=3)– przewidywany koszt oraz liczba wierszy do przejrzenia.
(actual time=0.0165..0.0182 rows=3 loops=1)– 0.0165 time czas po którym rozpoczęto przeglądanie tabeli dla etapu „Filter’, 0.0182– całkowity czas wykonania zapytania, rows– przejrzane wiersze.

Konfiguracja

W artykule wykorzystano MySQL w wesji 9.2.0 z silnikiem InnoDb. Zapytania wykonane były na tabeli pt. „student”, zawierającej 5000 wierszy, stworzonych z wykorzystaniem biblioteki „Datafaker” dla języka Java[7].

 CREATE TABLE `student` (
   `id` bigint NOT NULL,
   `last_name` varchar(50) NOT NULL,
   `first_name` varchar(50) NOT NULL,
   `birthday` date DEFAULT NULL,
   `is_promoted` bit(1) DEFAULT NULL,
   `words` varchar(255) DEFAULT NULL,  
    PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Zapytania z wykorzystaniem pojedynczego indeksu
Na początek przyjrzyjmy się dośc prostemu przypadkowi.

Utworzono następujący indeks na kolumnie „first_name”:

ALTER TABLE student ADD INDEX (first_name);

Dążymy do zwrócenia tych studentów, których imię nie zaczyna się na literę „S”. Czy w przypadku uzycia znaku „%” oraz ograniczenia „NOT LIKE” MySQL skorzysta z utworzonego indeksu? Poniżej zapytanie brane pod uwagę:

SELECT first_name, last_name FROM student WHERE first_name NOT LIKE "S%";

Wykonując komende EXPLAIN dla tego zapytania dostajemy odpowiedź odmowną:

+--+-----------+-------+----------+----+-------------+----+-------+----+----+--------+-----------+
|id|select_type|table  |partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra      |
+--+-----------+-------+----------+----+-------------+----+-------+----+----+--------+-----------+
|1 |SIMPLE     |student|null      |ALL |null         |null|null   |null|4995|88.89   |Using where|
+--+-----------+-------+----------+----+-------------+----+-------+----+----+--------+-----------+

Type ALL oznacza, że w przypadku tego zapytania zostanie przeszukana oryginalna tabela. Czy konstruując przeciwne zapytanie, MySQL zdecyduje się na takie samo posunięcie? Oto zapytanie:

SELECT first_name, last_name FROM student WHERE first_name  LIKE "S%";

oraz wynik wykonania polecenia EXPLAIN:

+--+-----------+-------+----------+-----+-------------+------------+-------+----+----+--------+---------------------+
|id|select_type|table  |partitions|type |possible_keys|key         |key_len|ref |rows|filtered|Extra                |
+--+-----------+-------+----------+-----+-------------+------------+-------+----+----+--------+---------------------+
|1 |SIMPLE     |student|null      |range| first_name  | first_name |202    |null|330 |100     |Using index condition|
+--+-----------+-------+----------+-----+-------------+------------+-------+----+----+--------+---------------------+

W tym przypadku MySQL zdecydował się wykorzystać indeks utworzony dla kolumny „first_name”, symbol „%” nie był tu przeszkodą. Natomiast w poprzednim zapytaniu optymalizator przewidział, że konieczne będzie przeglądnięcie i zwrócenie danych z większości wierszy oryginalnej tabeli. Dlatego w tej sytuacji bardziej efektywne okazało się sekwencyjne odczytywanie danych bezpośrednio z tabeli, niż składanie wyniku przeszukując indeksy oraz tabelę. Gdyby symbol „%” został użyty na początku wyszukiwanej frazy to korzystanie z indeksu także byłoby bezcelowe. Typ Range w tym przypadku oznacza wykorzystanie indeksu do pobrania danych w określonym przez operator „LIKE” zakresie.

W kolejnej zagadce dalej korzystamy z indeksu na kolumnie „first_name”. Mając do dyspozycji następujące dwa zapytania, które z nich będzie szybsze?

SELECT first_name, count(first_name) FROM student  WHERE first_name like "S%" GROUP BY first_name;

Czy

SELECT first_name, count(first_name)  FROM student WHERE first_name LIKE "S%" AND is_promoted=1 GROUP BY first_name;

Teoretycznie, przy dodatkowym zawężeniu pola wyszukiwań dzięki „is_promoted” (które w naszym przykładzie usuwa około 50% więcej wierszy), zapytanie mogłoby działać szybciej. Jednak „is_promoted” nie jest częścią istniejącego indeksu.

Pierwsze zapytanie może być uruchomione wyłącznie na strukturze indeksu (COVERING index scan). Natomiast w przypadku drugiego zapytania, konieczność sprawdzenia wartości dla „is_promoted” wymusza odwołanie się do oryginalnej tabeli.

W tym przykładzie, dla pierwszego przypadku czas wykonania zapytania wyniósł 0.39 ms a dla drugiego 1.23 ms, więc różnica jest znacząca. Warto zauważyć, że wartości używane do agregacji znajdują się w indeksie i są posortowane, co sprawia, że ta faza jest znacznie szybsza i nie wymaga tymczasowej tabeli do zbierania wyników.

Dla porównania rezultaty zapytania EXPLAIN ANALYZE dla obu zapytań:

-> Group aggregate: count(first_name)  (cost=150 rows=330) (actual time=0.0323..0.39 rows=192 loops=1)
    -> Filter: (student.`first_name` like 'S%')  (cost=74.2 rows=330) (actual time=0.0282..0.288 rows=330 loops=1)
        -> Covering index range scan on student using first_name over ('S' <= first_name <= 'S??????????????????????????????????????????????????????')  
        (cost=74.2 rows=330) (actual time=0.0261..0.204 rows=330 loops=1)

-> Group aggregate: count(first_name)  (cost=225 rows=330) (actual time=0.162..1.23 rows=120 loops=1)
    -> Filter: (student.is_promoted=1)  (cost=149 rows=330) (actual time=0.154..1.15 rows=174 loops=1)
        -> Index range scan on student using first_name over ('S' <= first_name <= 'S??????????????????????????????????????????????????????'), 
        with index condition: (student.`name` like 'S%')  (cost=149 rows=330) (actual time=0.152..1.1 rows=330 loops=1)

Kończąc już z indeksem dla „first_name”, co stanie się w przypadku takiego użycia funkcji w klauzuli „WHERE”?

SELECT last_name  FROM student WHERE upper(first_name)="VINCENT";

Niestety w przypadku użycia funkcji po lewej stronie warunku w klauzuli „WHERE” nie ma możliwości skorzystania z indeksu utworzonego na kolumnie „first_name” i nastąpi przeszukanie oryginalnej tabeli wiersz po wierszu.

Zapytania z wykorzystaniem indeksu wielokolumnowego

Dla przykładów z następnej cześci artykułu należy usunąć poprzedni indeks i utworzyć nowy, złożony z kolumn „first_name” i „last_name”.

DROP INDEX first_name ON student;
ALTER TABLE student ADD INDEX multi (first_name, last_name);

W celu przypomnienia podstawowej własności indeksów wielokolumnowych(wielopoziomowych) sprawdźmy takie zapytanie:

SELECT first_name, last_name, birthday FROM student  WHERE student.last_name LIKE "Collins";

W dokumentacji bazy MySQL można wyczytać, że do wyszukiwania pasujących wierszy, optymalizator może skorzystać z każdego lewostronnego prefiksu indeksu wielkolumnowego[8]. Tutaj filtrowanie następuje tylko po prawej części indeksu. Jak widać ten przypadek wyklucza jego użycie. Zauważmy, że w tym przypadku MySQL pracuje na oryginalnej tabeli, gdyż chcemy wyciągnąć również dane z kolumny „birthday”.

-> Filter: (student.last_name like 'Collins')  (cost=506 rows=555) (actual time=0.179..3.41 rows=8 loops=1)
    -> Table scan on student  (cost=506 rows=4995) (actual time=0.14..2.71 rows=5000 loops=1)

Przejdźmy teraz do następnego zagadnienia.

Czy kolejność łączenia filtrów w klauzuli „where” z użyciem „AND” wpływa na decyzję o użyciu indeksu wielokolumnowego?

SELECT first_name, last_name FROM student  WHERE student.last_name = "Collins" AND student.first_name="Russ";

Na szczęście nie:

+--+-----------+-------+----------+----+-------------+-----+-------+-----------+----+--------+-----+
|id|select_type|table  |partitions|type|possible_keys|key  |key_len|ref        |rows|filtered|Extra|
+--+-----------+-------+----------+----+-------------+-----+-------+-----------+----+--------+-----+
|1 |SIMPLE     |student|null      |ref |multi        |multi|404    |const,const|1   |100     |null |
+--+-----------+-------+----------+----+-------------+-----+-------+-----------+----+--------+-----+

Typ ref potwierdza użycie przeszukiwania z wykorzystaniem indeksu.

A co w przypadku zmiany „AND” na „OR”?

SELECT first_name, last_name FROM student  WHERE student.last_name = "Collins" OR student.first_name="Russ";

Ponieważ nie da się wykorzystać prawej części indeksu, filtrowanie po kolumnie „last_name” wymusza pełny skan po strukturze indeksu, czyli skan typu „covering index scan”.

+--+-----------+-------+----------+------+-------------+----+-------+----+----+--------+-----------+
|id|select_type|table  |partitions|type  |possible_keys|key |key_len|ref |rows|filtered|Extra      |
+--+-----------+-------+----------+------+-------------+----+-------+----+----+--------+-----------+
|1 |SIMPLE     |student|null      |index |multi        |null|null   |null|4995|10.03   |Using where|
+--+-----------+-------+----------+------+-------------+----+-------+----+----+--------+-----------+

W tym wypadku utworzenie osobnych indeksów dla każdej z kolumn przyspieszyłoby zapytanie.

Zakończmy nasze zmagania z indeksami nieco bardziej złożonym zapytaniem. Najpierw usuniemy poprzedni indeks i zastąpimy innym:

DROP INDEX multi ON student;

ALTER TABLE student ADD INDEX multi (last_name, is_promoted);

W jaki sposób zostanie wykorzystany indeks w poniższym, grupującym zapytaniu?

SELECT last_name, count( last_name) FROM student WHERE student.is_promoted=1 GROUP BY  last_name ORDER BY last_name;

Wiemy, że MySQL najpierw przetwarza warunki z klauzuli WHERE, a dopiero później agreguje dane według GROUP BY. W tym przypadku klauzula WHERE korzysta z prawej częsci indeksu a GROUP BY z lewej. Czy zatem zapytanie może w jakikolwiek sposób wykorzystać wielokolumnowy indeks, biorąc pod uwagę zasadę lewostronnego prefiksu?

Odpowiedzi jak zwykle dostarcza polecenie EXPLAIN ANALYZE:

-> Group aggregate: count(student.last_name)  (cost=1082 rows=473) (actual time=0.124..2.83 rows=468 loops=1)
    -> Filter: (student.is_promoted=1)  (cost=506 rows=2498) (actual time=0.115..2.24 rows=2523 loops=1)
        -> Covering index scan on student using multi  (cost=506 rows=4995) (actual time=0.112..1.68 rows=5000 loops=1)

W MySQL można do zapytania dodać wskazówki wymuszające użycie bądź zignorowanie konkretnego indeksu. Wykonajmy więc zapytanie z pomocą IGNORE INDEX by porównać poprzedni wynik z zapytaniem nie korzystającym z indeksu[9]:

SELECT last_name, count( last_name) FROM student IGNORE INDEX (multi) WHERE student.is_promoted=1 GROUP BY last_name order by last_name;

Oraz odpowiedź z EXPLAIN ANALYZE:

-> Sort: student.last_name  (actual time=4.5..4.53 rows=468 loops=1)
    -> Table scan on <temporary>  (actual time=4.2..4.3 rows=468 loops=1)
        -> Aggregate using temporary table  (actual time=4.2..4.2 rows=468 loops=1)
            -> Filter: (student.is_promoted=1)  (cost=506 rows=2498) (actual time=0.139..2.51 rows=2523 loops=1)
                -> Table scan on student  (cost=506 rows=4995) (actual time=0.137..1.93 rows=5000 loops=1)

Tutaj widzimy, że bez indeksu MySQL został zmuszony do pełnego skanowania tabeli, użycia tymczasowej tabeli pomocniczej do etapu grupowania danych oraz dodatkowego kroku sortowania. Ostatecznie powoduje wydłużenie przetwarzania zapytania o prawie 2 ms.

W dokumentacji MySQL znajduje się nawet cały akapit poświęcony optymalizacji zapytań z użyciem GROUP BY.[10]

Podsumowanie

Jak widać MySQL ma całkiem przemyślany, aczkolwiek momentami złożony mechanizm optymalizacji zapytań z użyciem indeksów. W niniejszym artykule przypomniałam najważniejsze własności indeksów a przytoczone przykłady powinny ułatwić ich zapamiętanie. W przypadku jakichkolwiek wątpliwości polecam sprawdzić czy nasze przewidywania są zgodne z rezultatami zwracanymi przez polecenia EXPLAIN oraz/lub EXPLAIN ANALYZE.