Indeksowanie jest podstawowym mechanizmem wykorzystywanym w celu optymalizacji baz danych MySQL. Gdyby porównać bazę danych do książki, indeksy są czymś w rodzaju spisu treści. Już w samej terminologii można odkryć tę metaforę a użyjemy jej jeszcze nie raz.

Indeksy są zatem pomocniczymi strukturami danych, które znacząco wpływają na szybkość wykonywania się zapytań SQL. Z technicznego punktu widzenia (i mocno uogólniając) indeksy to zbiór wartości typu „klucz – lokalizacja”. Dzięki temu, na podstawie konkretnego klucza czyli parametrów zapytania jest możliwe bardzo szybkie zwrócenie odpowiednich danych. Ponieważ są to dane pomocnicze, indeksy to redundantne (czyli nadmiarowe) dane, których używa RDBMS (system zarządzania relacyjną bazą danych).

Jak działają indeksy w bazach danych?

Problem z przeszukiwaniem baz danych polega na tym, że… tabele w MySQL (a także w innych RDBMS) nie są posortowane według kolumn, dzięki którym wyciągamy odpowiednie dane. Dane ulegają aktualizacji, usuwaniu i ciągle dodawane są nowe. Jedyna „kolejność” to często klucz główny PRIMARY_KEY, który jest de facto indeksem. Nie jest to przydatna kolejność kiedy szukamy danych nie po kluczu a po jakimś innym polu np:

SELECT nazwa_produktu FROM produkty WHERE cena = 128;

Jeżeli mielibyśmy te nasze produkty posortowane według ceny, to znalezienie było by banalne. W przypadku kiedy dane są posortowane po kluczu głównym trzeba „sprawdzić” wszystkie rekordy i nie mamy możliwości ułatwienia sobie zadania bo produkty mogą mieć przecież różną cenę. Mówi wtedy że dokonujemy pełnego skanu tabeli (ang. full table scan), który działa niekorzystnie na wydajność – zabiera po prostu za wiele czasu. Sztuka optymalizacji za pomocą indeksowania polega na unikaniu tego pełnego skanu.

Działanie indeksu polega na tym, że zawiera w przystępnej postaci informację, w których komórkach pamięci znajdują się produkty o cenie 128. Z tego korzysta RDBMS – najpierw uderza do indeksu a kiedy już wie skąd ma pobrać dane to od razu przechodzi do odpowiednich miejsc bez przeszukiwania całej tabeli i zwraca wynik użytkownikowi.

Jak MySQL używa indeksów?

Silnik MySQL używa indeksów na wiele sposobów, między innymi:

  • szybko znajduje wiersze pasujące do klauzuli WHERE,
  • ignoruje pewne wiersze (jeżeli MySQL ma do dyspozycji wiele indeksów używa tego najmniejszego) co przyspiesza skanowanie,
  • szybciej zwraca zapytania w przypadku złączania wielu tabel,
  • szybciej zwraca zapytania MIN() i MAX()

Jakie tabele i kolumny należy indeksować?

Korzyść wydajnościowa ze stosowania indeksów jest największa w przypadku dużych tabel (zawierających najwięcej rekordów) oraz zapytań, które wykonywane są najczęściej. W MySQL i w innych RDBMS zaleca się indeksować następujące kolumny:

  • kolumny najczęściej padające po słowie WHERE,
  • kolumny dwóch tabel, które często łączymy,
  • kolumny, według których sortujemy dane w raportach (kolumny padające po słowie ORDER BY i GROUP BY),
  • kolumny które często zliczamy (SUM(), AVG(), MIN(), MAX(), COUNT())
  • klucze obce i kolumny, których będziemy używać tak jak kluczy obcych,
  • klucze niepowtarzalne UNIQUE_KEY (typu NIP, PESEL itd…),
  • FULLTEXT w przypadku częstej potrzeby przeszukiwania tekstu.

Można się kierować prostą zasadą, polegającą na tym, że nie tworzymy indeksu jeżeli nie jesteśmy przekonani, że faktycznie będziemy z niego korzystać.

Których kolumn nie należy indeksować?

  • często aktualizowanych danych tym bardziej w tabelach InnoDB.

Nadmiar indeksów – dlaczego jest szkodliwy?

Należy pamiętać, że indeks drastycznie spowalnia dodawanie, modyfikowanie i usuwanie danych, ponieważ indeksy muszą być aktualizowane za każdym razem, gdy tabela ulega nawet najmniejszej modyfikacji. Najlepszą praktyką jest dodanie indeksu dla wartości, które są często używane do wyszukiwania, ale nie ulegają częstym zmianom.

Zaawansowana optymalizacja baz danych za pomocą indeksowania

Dość ciekawym rozwiązaniem jest stosowanie różnych indeksów w przypadku replikacji MySQL. Biorąc pod uwagę, że nadmiar indeksów szkodzi a w przypadku replikacji baz danych możemy „specjalizować” poszczególne bazy i serwery, możemy stosować różne strategie indeksowania dla baz, które są replikowane. Jeżeli pobieramy dane z bazy SLAVE możemy na niej optymalizować indeksy pod odczytywanie. Analogicznie, w bazach do których zapisujemy dane indeksy mogą być zoptymalizowane aby maksymalizować wydajność zapisów. Indeksy to nie jedyny mechanizm, który możemy dostrajać dla poszczególnych węzłów replikacji baz danych. Podobny „trik” możemy wykorzystać dostosowując i zmieniając wewnętrzne mechanizmy składowania danych MySQL (ang storage engine).

Podsumowanie

Indeksowanie to podstawowa i niejako wbudowana w systemy zarządzania relacyjnymi bazami danych technika optymalizacji baz danych MySQL. Ich umiejętne użycie umożliwi skalowanie i minimalizację kosztów utrzymywania sporych rozmiarów baz danych.

Źródła

https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html

https://dev.mysql.com/doc/refman/5.5/en/optimization-indexes.html

Schwartz B., Zaitsev P., Tkachenko V., High Performance MySQL. Optimization, Backups, Replication, and More, O’Reilly 2012

Oceń artykuł na temat: Indeksowanie baz danych MySQL
Średnia : 4.8 , Maksymalnie : 5 , Głosów : 35