Wstęp

Od ostatniego mojego artykułu na temat optymalizacji zapytań do bazy MySQL minęło sporo czasu. Nadszedł czas na kontynuację tematu. Tak samo jak poprzednio, tematem będzie wpływ indeksów na wydajność (czas wykonywania) zapytań. Jednak tym razem, zamiast skupiać się na problemie odczytu z bazy, napiszę kilka słów na temat zapisów i jaki wpływ na nie ma liczba indeksów.
Jak udowodniłem w poprzednim artykule, właściwie założone indeksy i właściwie sformułowane zapytania są w stanie wielokrotnie zmniejszyć czas odpowiedzi serwera MySQL. W niektórych przypadkach, nawet setki razy. A jak wygląda sprawa indeksów przy zapisach do tabel? No cóż, trochę inaczej...

Co się dzieje przy zapisie

Indeks jako taki, można nazwać uporządkowaną strukturą przechowująca wartości pól tabeli wraz z odnośnikami do zawierających je rekordów. Jeśli baza danych dostanie polecenie znalezienia rekordów spełniających określony warunek na indeksowanej kolumnie, nie będzie musiała skanować wszystkich rekordów w tabeli aby odszukać te spełniające warunek. Wystarczy, że odwoła się do indeksu, odpowiednim algorytmem odnajdzie właściwy wpis w indeksie (pamiętajmy, że indeks jest posortowany, więc odszukanie wartości może być bardzo szybkie) i na podstawie odnośników odszuka właściwe rekordy w tabeli. Jest to dużo szybsze niż skanowanie wszystkich wpisów w tabeli.
Pewne niedogodności związane z indeksami zaczynają pojawiać się, gdy zaczynamy dokonywać zapisów w tabeli. W szczególności w polach, które zawierają się w dowolnym indeksie. Jak wspominałem, indeks zawiera posortowane wartości z pól. Jeśli zmodyfikujemy wartość pola (lub dodamy nowy rekord), indeksy z nim powiązane będą musiały być odtworzone. To znaczy: musi nastąpić odbudowa odnośników pomiędzy wartościami indeksu i przyporządkowanymi im polami oraz posortowanie wartości w indeksie. Przy dużej ilości rekordów lub duże liczbie indeksów może to być czasochłonne. Co więcej, wykonywane przy każdym zapisie. No i mamy problem. Bez indeksów wolno przy odczycie, z indeksami wolniej przy zapisie. A o ile wolniej? Postanowiłem to sprawdzić...

Testy

Jako środowisko testowe wykorzystałem standardowo skonfigurowany serwer MySQL 5.5.8 i pojedynczą tabelę na silniku InnoDB:

CREATE TABLE IF NOT EXISTS `indexes` (
`ID` int(10) unsigned NOT NULL,
`X` int(10) unsigned NOT NULL,
`Y` int(10) unsigned NOT NULL,
`Z` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Środowiska uruchomieniowego nie będę opisywał, gdyż testy miały wykazać, czy wraz ze wzrostem liczby i złożoności indeksów rośnie czas wykonywania zapisów, a nie ile dokładnie wynosi. Dla każdej konfiguracji i implementacji ten czas będzie inny, będę więc tylko porównywał różnice, a nie konkretne czasy.

Testy przebiegały w następujący sposób:

  1. Czas wstawienie 1000 rekordów z losowymi wartościami pól X, Y, Z
    1. Kiedy tabela nie ma założonych indeksów i kluczy
    2. Tabela posiada klucz główny ID, wypełniany przez auto_increment
    3. Klucz główny, plus indeks na kolumnie X
    4. Klucz główny, plus indeks złożony na kolumnach X i Y
    5. Klucz główny, plus indeks złożony na kolumnach X, Y i Z
    6. Klucz główny, plus indeks złożony na kolumnach X, Y i Z oraz osobne (nadmiarowe) klucze proste na kolumnach X, Y i Z (łącznie 5 indeksów wraz z kluczem głównym)
  2. Czas zmiany 1000 rekordów z losowymi wartościami pól X, Y i Z, przy wyborze rekordów na podstawie klucza głównego na kolumnie ID
    1. Tabela posiada tylko klucz główny
    2. Klucz główny, plus indeks na kolumnie X
    3. Klucz główny, plus indeks złożony na kolumnach X i Y
    4. Klucz główny, plus indeks złożony na kolumnach X, Y i Z
    5. Klucz główny, plus indeks złożony na kolumnach X, Y i Z oraz osobne (nadmiarowe) klucze proste na kolumnach X, Y i Z (łącznie 5 indeksów wraz z kluczem głównym)

Dla każdego przypadku testowego wykonywano 5 prób.

Wyniki

Wstawienia

Średni czas wykonania [s] Łączny czas wykonania [s] Przyrost średniego czasu wykonania [%]
Bez indeksów 1,73347496034 8,6673748017 100%
Klucz główny 1,73200125694274 8,6600062847137 99,91%
Klucz główny, indeks na kolumnie X 2,1850034236908 10,925017118454 126,05%
Klucz główny, indeks złożony na kolumnie X i Y 2,3132972240448 11,566486120224 133,45%
Klucz główny, indeks złożony na kolumnie X, Y i Z 2,49690279960634 12,4845139980317 144,04%
Klucz główny, plus indeks złożony na kolumnach X, Y i Z oraz klucze proste na kolumnach X, Y i Z 4,03492860794778 20,1746430397389 232,77%

Modyfikacje

Średni czas wykonania [s] Łączny czas wykonania [s] Przyrost średniego czasu wykonania [%]
Klucz główny (kolumna klucza głównego nie jest modyfikowana) 0,74041466712 3,7020733356 100,00%
Klucz główny, indeks na kolumnie X 0,7640826702 3,820413351 103,20%
Klucz główny, indeks złożony na kolumnie X i Y 0,77085061264 3,8542530632 104,11%
Klucz główny, indeks złożony na kolumnie X, Y i Z 0,77389435768 3,8694717884 104,52%
Klucz główny, plus indeks złożony na kolumnach X, Y i Z oraz klucze proste na kolumnach X, Y i Z 0,8969789982 4,484894991 121,15%

Omówienie

Szybkie spojrzenie na tabelę z wynikami i wniosek jest prosty: wraz ze wzrostem liczby indeksów w tabeli, wzrasta czas jaki silnik bazy danych musi poświęcić na wykonanie zapisu. Szczególnie widoczne jest to w przypadku wstawień (INSERT), gdzie już pierwszy dodatkowy indeks (poza kluczem głównym) zwiększył czas o ponad 26%. Co ciekawe, klucz główny (na kolumnie auto_increment) nie zwiększał czasu zapytania (spadek czasu był związany raczej z błędem pomiaru).
Modyfikacje (UPDATE) okazały się dużo bardziej tolerancyjne na zbędne indeksy niż wstawienia. O ile w przypadku wstawień i największej liczby indeksów czas zapytań wzrósł ponad 2,3 raza, to przy identycznej strukturze tabeli i poleceniach UPDATE ten czas wzrósł ‘tylko’ 1,2 raza.
Za różnice pomiędzy wstawieniami i modyfikacjami można obwiniać operację odbudowy indeksu. UPDATE nie zmienia ilość wpisów w tabeli, więc nie jest konieczna pełna odbudowa drzewa indeksu. W przypadku operacji INSERT DBMS nie ma tego komfortu i musi odbudować całe drzewo.

Podsumowanie

O ile w poprzednim aktykule z tej serii udowodniłem, że indeksy są dobre i potrafią wielokrotnie zwiększyć wydajność SELECTów, to teraz sytuacja uległa skomplikowaniu. Zbyt dużo, czy nie przemyślane indeksy potrafią znacząco zmniejszyć wydajność zapisów. W zasadzie, dla każdej tabeli i każdego zastosowania powinno przeprowadzić się badania aby wybrać najlepszą strategie indeksowania. Niestety, nie zawsze mamy taką możliwość. Z tego powodu, można stosować się do poniższych zasad i zdać się na zdrowy rozsądek:

  • zakładamy tylko indeksy o dużej mocy (duża ilość unikalnych wartości w kolumnie). Jeśli moc indeksu jest mała (na przykład w polu przechowujemy tylko ‘0’ i ‘1’) indeks może się nie opłacać. Narzut przy zapisie pozostaje, a zysk przy odczycie jest niewielki,
  • tabele o małej liczbie odczytów (w stosunku do zapisów) zwykle nie wymagają indeksowania,
  • indeksy na polach tekstowych z których wyszukujemy za pomocą LIKE ‘%aaa%’ nie są wykorzystywane przy odczycie. Nie mają więc sensu,
  • powielanie kolumn w indeksach złożonych prowadzi do spadku wydajności wydajności. MySQL dla jednej tabeli i tak użyje tylko jednego indeksu. Lepiej popracować nad samym zapytaniem.