Przy projektowaniu nowej aplikacji korzystającej z bazy MySQL często pada pytanie: który silnik bazodanowy wybrać?

Dwie główne możliwości to zazwyczaj MyISAM oraz InnoDB. MyISAM często jest domyślnym mechanizmem w starszych wersjach MySQL. Dane zapisywane są w plikach (osobno pliki z danymi i z indeksami). InnoDB jest domyślnym silnikiem od MySQL 5.5. W przeciwieństwie do pierwszego posiada on takie mechanizmy jak obsługa transakcji czy klucze obce.

Oczywiście jak to zwykle bywa przed wyborem silnika warto rozważyć wady i zalety każdego z nich. Inny punkt widzenia będzie miał klient, któremu zależy na wydajności, a inny programista lub administrator, któremu będzie zależało na kopiach zapasowych i stabilności całego serwera.

W Internecie można zazwyczaj znaleźć informacje że MyISAM szybciej wykonuje zapytania SELECT, prościej się nim zarządza, wykonuje kopie zapasowe czy odtwarza dane. Natomiast InnoDB uważany był za wolniejszy, jednak wspierający transakcje i klucze obce.

Oczywiście nie ma systemów idealnych – w każdym mogą czasem zdarzyć się usterki, a nawet większe awarie. Jak w takich sytuacjach radzi sobie InnoDB, a jak MyISAM?

Na początek silnik MyISAM. Nie posiada on obsługi transakcji, dlatego może się zdarzyć, że podczas awarii serwera część danych zostanie w bazie dopisana, skasowana lub zmieniona, a część nie. Wyobraźmy sobie, że podczas wykonywania zapytania typu UPDATE doszło do wyłączenia serwera. Po ponownym uruchomieniu, które może trwać nawet wiele godzin baza będzie zawierała częściowo zmienioną zawartość rekordów. Dane w bazie będą niespójne.

A co w przypadku silnika InnoDB? Zazwyczaj po awarii serwer wstanie bardzo szybko. Silnik InnoDB jest silnikiem transakcyjnym, który wspiera ACID czyli atomowość transakcji.

Atomowość transakcji oznacza, że albo wykonujemy je w całości albo wcale. Nie może dojść do sytuacji, w której wykona się część zapytań jak w przypadku MyISAM.

Kopie zapasowe

Jeżeli dojdzie już do awarii serwera i nie da się naprawić tabel, będziemy musieli skorzystać z kopii zapasowej. Z którego silnika prościej jest wykonać taką kopię? Operacje te trwają dość długo w przypadku ogromnych baz danych, jednak import pliku SQL będzie szybszy dla silnika MyISAM. Silnik InnoDB podczas importowania nie potrafi zbudować indeksu przy pomocy sortowania, dlatego operacja importu trwa znaczniej dłużej.

Kolejną metodą jest wykonanie kopii zapasowej plików z danymi. Dla tabel MyISAM, dane przechowywane są w 3 plikach. Silnik InnoDB, w zależności od ustawień serwera, przechowuje dane w plikach .frm (definicja tabel), natomiast dane trzymane są w jednej lub większej ilości plików, które składają się na tablespace.

Skopiowanie odpowiednich plików jest proste i dość szybkie. W przypadku MyISAM musimy zablokować dostęp do zapisu dla tabel lub po prostu zatrzymać serwer. Niestety rozwiązanie to nie jest wydajne bo musimy odciąć dostęp do zapisu do serwera. W przypadku silnika InnoDB możemy wykonać kopię bez zatrzymywania bazy danych.

Wydajność silnika bazodanowego

Bardzo często w Internecie można spotkać się z opinią, że silnik MyISAM jest szybszy od InnoDB. Jeszce kilka lat temu może tak było. Jednak w ostatnim czasie i z każdą nową wersją serwera MySQL, silnik InnoDB jest rozwijany – w przeciwieństwie do MyISAM.

Przy zapytaniach typu select InnoDB wykorzystuje mechanizm klastrowania indeksów, co w niektórych przypadkach znacząco przyspiesza działanie bazy.

Przy zapytaniach typu insert, update, delete w MyISAM mamy do czynienia z blokowaniem na poziomie tabeli. Dodając rekordy do tabeli, blokujemy ją na czas całej operacji. W przypadku InnoDB mamy do czynienia z blokowaniem na poziomie rekordów, dzięki czemu możliwe jest działanie równoległych zapytań na tej samej tabeli.

MyISAM w wielu sytuacjach potrafi zdecydowanie działać szybciej niż InnoDB, ale dzieje się to kosztem integralności danych. W nim po prostu nie ma takiej funkcjonalności.

Podsumowanie: jaki silnik wybrać dla bazy danych? MyISAM czy InnoDB?

Wszystko zależy do jakich celów będziemy wykorzystywać bazę danych, czy będzie nam zależało na integralności danych czy raczej na wydajności podczas pobierania rekordów.

MyISAM

Zalety

• zazwyczaj szybszy odczyt z tabel (zapytania select)
• prostsze wykonywanie kopii zapasowych
• odpowiedni dla tabel z małą ilością danych

Wady

• brak obsługi transakcji
• brak mechanizmów odpowiedzialnych za integralność danych
• przy dużych tabelach, długie czasy wykonywania REPAIR TABLE po awarii serwera (nawet kilka godzin)

InnoDB

Zalety

• obsługa transakcji
• gwarantuje integralność danych
• domyślny silnik od MySQL 5.5
• lepiej sprawuje się podczas replikacji typu master – slave

Wady

• zazwyczaj nieznacznie wolniejszy odczyt danych
• trudniejsze wykonywanie kopii zapasowych